1

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.

Lafexlos
  • 7,618
  • 5
  • 38
  • 53
Caeline
  • 119
  • 3
  • 13
  • Does the following link answer your question? http://stackoverflow.com/questions/20219254/how-to-write-to-an-existing-excel-file-without-overwriting-data-using-pandas' – Dataman Dec 01 '16 at 12:17

1 Answers1

2

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
DeepSpace
  • 78,697
  • 11
  • 109
  • 154
  • I tested it, but it still doesn't create a new sheet in de excelfile, just overwrites the existing sheet; probably because everytime the experiment runs, the whole code runs and ew creates a new excelfile that has the same name of an already existing excelfile. That gets overwritten with a new excel file. What you gave me would help if I could create an ew, run experiment X times and add X dataframes to ew, then save ew. But the experiment runs X times with everytime making an ew and an ew2 (because of 2 excelfiles I need). I should open(or create) an excelfile with name 'x' and add a sheet. – Caeline Dec 24 '15 at 12:54