将具有多个列标题的数据帧附加/复制到现有excel文件中

Question

我正试图从特定单元格AA2开始，将具有多个列标题的数据帧(类似于下面的数据帧(复制/附加到现有的excel表中

df1 = pd.DataFrame({'sub1': [np.nan,'E',np.nan,'S'],
'sub2': [np.nan,'D',np.nan,'A']})
df2 = pd.DataFrame({'sub1': [np.nan,'D',np.nan,'S'],
'sub2': [np.nan,'C',np.nan,'S']})
df = pd.concat({'Af':df1, 'Dp':df2}, axis=1)
df

我正在考虑一种解决方案，从该特定单元格开始将该数据帧导出到excel，并使用openpyxl逐列将数据从一个单元格复制到另一个单元格。。。但不确定这是否是正确的方法。有什么想法吗？！

(我正在处理的excel表有格式，无法将其转换为数据帧并使用合并(

Answer 1

我过去曾成功地使用xlsxwriter操作Excel文件(您需要首先将其作为依赖项进行pip安装，尽管不需要显式导入(。

import io
import pandas as pd
# Load your file here instead
file_bytes = io.BytesIO()
with pd.ExcelWriter(file_bytes, engine = 'xlsxwriter') as writer:
# Write a DataFrame to Excel into specific cells
pd.DataFrame().to_excel(
writer,
sheet_name = 'test_sheet',
startrow = 10, startcol = 5,
index = False
)
# Note: You can repeat any of these operations within the context manager
# and keep adding stuff...

# Add some text to cells as well:
writer.sheets['test_sheet'].write('A1', 'Your text goes here')
file_bytes.seek(0)
# Then write your bytes to a file...
# Overwriting it in your case?

奖金：您也可以添加绘图-只需将它们写入BytesIO对象，然后调用<your_image_bytes>.seek(0)，然后在insert_image()函数中使用即可。

... # still inside ExcelWriter context manager

plot_bytes = io.BytesIO()

# Create plot in matplotlib here
plt.savefig(plot_bytes, format='png') # Instead of plt.show()
plot_bytes.seek(0)
writer.sheets['test_sheet'].insert_image(
5, # Row start
5, # Col start
'some_image_name.png',
options = {'image_data': plot_bytes}
)

完整的文档也非常有用：https://xlsxwriter.readthedocs.io/working_with_pandas.html