将Pandas数据框架追加到现有的Excel文档中[英] Appending Pandas DataFrame to existing Excel document

本文是小编为大家收集整理的关于将Pandas数据框架追加到现有的Excel文档中的处理方法,想解了将Pandas数据框架追加到现有的Excel文档中的问题怎么解决?将Pandas数据框架追加到现有的Excel文档中问题的解决办法?那么可以参考本文帮助大家快速定位并解决问题。

问题描述

per pandas-dev/pandas/pandas/pull/21251/files/09e5b456e1af55555f18f18f903b903ab903ab90c76161643b05a bb05a bb05a" rel="nore="norefithub = nore="norefitpps://pand/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".

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

推荐答案

您可以使用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()

其他推荐答案

我在Github上提交了一篇文章,并收到了贡献者的回复(请参阅下面的突出显示部分).事实证明,该功能在0.24>>中才会发布,因此在0.23.1中不可用.仅供参考 - 我下载了RC,并成功尝试了mode='a'选项.但是,可能有一个不存在的工作簿的错误.我收到FileNotFoundError: [Errno 2] No such file or directory: 'test.xlsx'.

"此功能作为0.24的一部分发布,我们刚刚在过去几天发行了候选人.这不会期望在比"

"的版本上使用.

其他推荐答案

将pandas作为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()

本文地址:https://www.itbaoku.cn/post/1728116.html