使用 pandas 将图纸添加到现有的excelfile中 [英] Adding sheets to existing excelfile with pandas

问题描述

我有两个Excel文件.一名参与者完成了实验，结果应在另一张纸上的两个Excel文件中提供.每个新参与者都应该发生这种情况.工作表名称为001,002，...，具体取决于参与者的编号.但是excelfile只会不断被覆盖.

I have two excel files. A participant completes the experiment and the results should be given in into the two excel files on another sheet. This should happen for every new participant. The sheetname is 001,002, ..., dependent on what the participantnumber is. But the excelfiles just keep getting overwritten.

我正在使用的代码:

import pandas
allmeans = numpy.array([[(meanreactiontimeinputsusercongruentpositief),(meanreactiontimeinputsusercongruentnegatief),(meanreactiontimeinputsuserincongruentposneg), (meanreactiontimeinputsuserincongruentnegpos), (meanvalueinputusercongruentpositief), (meanvalueinputusercongruentnegatief), (meanreactiontimeinputsuserincongruentposneg), (meanvalueinputuserincongruentnegpos)]])
to_write = pandas.DataFrame({'Age': age, 'Gender': gender, 'MeanReactionTimeCongruentPos': allmeans[:,0], 'MeanReactionTimeCongruentNeg': allmeans[:,1], 'MeanReactionTimeIncongruentPosNeg': allmeans[:,2], 'MeanReactionTimeIncongruentNegPos': allmeans[:,3], 'MeanValueInputCongruentPos': allmeans[:,4], 'MeanValueInputCongruentNeg': allmeans[:,5], 'MeanValueInputIncongruentPosNeg': allmeans[:,6], 'MeanValueIncongruentNegPos': allmeans[:,7]})
to_write.to_excel('MeansOfUsers.xlsx',sheet_name = str(participantnumber), index = False, startrow = 3, startcol = 2)

allresults= numpy.array([listofrandomizedconditions,inputsuser,reactiontimesuser])
to_write2 = pandas.DataFrame({'Conditions': allresults[0,:], 'Inputs': allresults[1,:], 'Reactionstimes': allresults[2,:]})
to_write2.to_excel('ResultsofUsers.xlsx',sheet_name = str(participantnumber), index = False, startrow=3,startcol=2)

因此，基本上，它总是使用正确的工作表名称创建这2个excelfile，但是除了添加新工作表之外，现有工作表只会被新工作表覆盖.我该如何解决?

So basicly it always creates those 2 excelfiles with the correct sheet name but except of adding a new sheet, the existing sheet just gets overwritten by the new sheet. How do I solve that?

我发现使用openpyxl工作簿，我可以在加载工作簿后使用create_sheet在其中添加新工作表，但是他们被困在如何使用pandas.DataFrame编辑该确切工作表的过程中.我创建.

I found out that using a workbook of openpyxl, I can use create_sheet after loading in a workbook to get a new sheet in it but them I'm stuck on how I can edit that exact sheet with the pandas.DataFrame I created.

推荐答案

您应该创建一个ExcelWriter对象，并将其用于保存数据框.您需要调用其save()方法，以便实际保存excel文件.

You should create an ExcelWriter object and use it to save the dataframes. You will need to call its save() method for the excel file to be actually saved.

import pandas as pd

ew = pd.ExcelWriter('a.xlsx')
pd.DataFrame({'a':[1,2]}).to_excel(ew, sheet_name='a')
pd.DataFrame({'a':[1,2]}).to_excel(ew, sheet_name='b')
ew.save() # don't forget to call save() or the excel file won't be created

这篇关于使用 pandas 将图纸添加到现有的excelfile中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！