将Pandas DataFrame追加到现有的Excel文档中 [英] Appending Pandas DataFrame to existing Excel document
问题描述
每个 https://github.com/pandas-dev/pandas/pull/21251/files/09e5b456e1af5cde55f18f903ab90c761643b05a ,我们应该能够将DataFrame附加到新的XLSX工作表中.
Per https://github.com/pandas-dev/pandas/pull/21251/files/09e5b456e1af5cde55f18f903ab90c761643b05a, we should be able to append DataFrames to new XLSX sheets.
根据文档,我尝试了以下操作:
Based on the documentation, I tried the following:
>>> 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'.
The result is a single workbook named 'test.xlsx' with a single tab 'd2'.
如何防止工作簿/工作表表单被覆盖?
How can I prevent the workbook/sheet form being overwritten?
推荐答案
您可以使用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创建一个空白文件.我创建了一个简单的循环,以某种方式模仿您要完成的工作的本质:
update
This should work just note that the a blank file needs to be created before hand. You can just create a blank file using python if you want. I created a simple loop to, in some ways, mimic the essence of what you are trying to accomplish:
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()
或者这是修改后的第一个示例:
or here is the modified first example:
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()
这篇关于将Pandas DataFrame追加到现有的Excel文档中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!