将 Pandas DataFrame 附加到现有的 Excel 文档

Question

根据 https://github.com/pandas-dev/pandas/pull/21251/files/09e5b456e1af5cde55f18f903ab90c761643b05a ，我们应该能够将数据帧附加到新的 XLSX 工作表。

根据文档，我尝试了以下操作：

 >>> import pandas as pd
>>>
... d1 = pd.DataFrame({"A":['Bob','Joe', 'Mark'],
...                "B":['5', '10', '20']})
>>> d2 = pd.DataFrame({"A":['Jeffrey','Ann', 'Sue'],
...                "B":['1', '2', '3']})
>>>
>>> # Create XLSX document for ticker
... writer = pd.ExcelWriter('test.xlsx',engine='openpyxl')
>>> d1.to_excel(writer,sheet_name='d1')
>>> writer.save()
>>>
>>> writer = pd.ExcelWriter('test.xlsx',engine='openpyxl', mode='a')
>>> d2.to_excel(writer,sheet_name='d2')
>>> writer.save()
>>>
>>> pd.__version__
'0.23.4'     # Just updated this per a comment
>>>
>>>

结果是一个名为“test.xlsx”的工作簿，带有一个选项卡“d2”。

如何防止工作簿/表格被覆盖？

原文由 enter_display_name_here 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

您可以使用 with ：

 with pd.ExcelWriter('test.xlsx', engine='openpyxl', mode='a') as writer:
    d1.to_excel(writer,sheet_name='d1')
    d2.to_excel(writer,sheet_name='d2')
    writer.save()

writer.close()

更新

这应该可以工作，只需要注意需要事先创建一个空白文件。如果需要，您可以使用 python 创建一个空白文件。我创建了一个简单的循环，以某种方式模仿您要完成的任务的本质：

 import pandas as pd
from openpyxl import load_workbook

d1 = pd.DataFrame({"A":['Bob','Joe', 'Mark'],
               "B":['5', '10', '20']})
d2 = pd.DataFrame({"A":['Jeffrey','Ann', 'Sue'],
                "B":['1', '2', '3']})

dfs = [d1,d2]

for i in range(len(dfs)):
    sheet = 'd'+str(i+1)
    data = dfs[i]
    writer = pd.ExcelWriter('atest.xlsx',engine='openpyxl', mode='a')
    writer.book = load_workbook('atest.xlsx') # here is the difference
    data.to_excel(writer,sheet_name=sheet)
    writer.save()
    writer.close()

或者这里是修改后的第一个例子：

 d1 = pd.DataFrame({"A":['Bob','Joe', 'Mark'],
               "B":['5', '10', '20']})
d2 = pd.DataFrame({"A":['Jeffrey','Ann', 'Sue'],
                "B":['1', '2', '3']})

writer = pd.ExcelWriter('atest.xlsx', engine='openpyxl', mode='w')
d1.to_excel(writer,sheet_name='d1')
writer.save()
writer.close()

writer = pd.ExcelWriter('atest.xlsx', engine='openpyxl', mode='a')
writer.book = load_workbook('atest.xlsx')
d2.to_excel(writer,sheet_name='d2')
writer.save()
writer.close()

原文由 It_is_Chris 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

将熊猫导入为 pd

 writer = pd.ExcelWriter(wk_path + save_file)
# ....
# build sc_files DataFrame and save. sc_files includes
# a column called OS.

sc_file.to_excel(writer, sheet_name='test')

# build data frame of OS counts out of sc_file
counts_os = sc_file.OS.value_counts()

# To append to 'test' sheet, use startcol=x1, startrow=y
# To append counts_OS to the end of the current 'test' sheet
y = len(sc_file)
y += 1
counts_os.to_excel(writer, sheet_name='test',
    startcol=1, startrow=y)

# write counts_os to sheet test2
counts_os.to_excel(writer, sheet_name='test2')
writer.save()
writer.close()

原文由 Adam Safier 发布，翻译遵循 CC BY-SA 4.0 许可协议