CODEWITHSUNDEEP
pythonexcelnumpypandas
Caeline
Caeline

Reputation: 119

Adding sheets to existing excelfile with pandas

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.

The code I'm using:

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)

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?

EDIT: 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.

Upvotes: 1

Views: 2359

Answers (1)

DeepSpace
DeepSpace

Reputation: 81594

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

Upvotes: 2

Related Questions