Combine multiple excel files into one master file using Python

Question

I am working with my thesis data where I have multiple people performing the same number of experiments. So person x has experiment 1 to 7 and so on.

I have managed to create 7 seperate excel files for each person. But what I need is one Excel file for person x, with 7 sheets with the results of each experiment.

I have searched quite a lot and I can't seem to find a fitting solution, I hope someone can help me.

counter_scenario = 1

wb = load_workbook(f'ID01_S{counter_scenario}.xlsx')
wb.create_sheet(f'ID01_S{counter_scenario}')
wb.save('ID01_No crash.xlsx')

counter_scenario += 1

What I get now is a file which overwrites the previous sheet. So in the end I only have one sheet with the data from experiment 7. Whilst I run the code the "master file" keeps updating, which (to me) indicates that data is written to it, it just overwrites it.

may be: https://stackoverflow.com/questions/15210173/creating-multiple-csv-sheets-in-python — akhetos, Jul 05 '19 at 11:13

Aleksandr Chernov · Accepted Answer · 2019-07-08T07:39:26.720

First, we will read into pandas all the workbooks for one person

df1 = pd.read_excel('person_1_exp1.xlsx')
df2 = pd.read_excel('person_1_exp2.xlsx')
df3 = pd.read_excel('person_1_exp3.xlsx')
# and so on

Let's create a new xlsx file, that will contain all the experiments for each person separately: "Person_1_results.xlsx"

writer = pd.ExcelWriter(
    save_path.format("Person_1_results.xlsx"),
    engine='xlsxwriter')
# Convert the dataframe to an XlsxWriter Excel object.
df1.to_excel(
    writer,
    sheet_name='Experiment_1',
    startrow=0,
    startcol=0,
    index=False)
df2.to_excel(
    writer,
    sheet_name='Experiment_2',
    startrow=0,
    startcol=0,
    index=False)
df3.to_excel(
    writer,
    sheet_name='Experiment_3',
    startrow=0,
    startcol=0,
    index=False)
# and so on

# Get the xlsxwriter workbook and worksheet objects.
workbook = writer.book
worksheet1 = writer.sheets['Experiment_1']
worksheet2 = writer.sheets['Experiment_2']
worksheet2 = writer.sheets['Experiment_3']
# and so on

If you have a consistent naming of the files, you may modify this solution and add loops for making that repeated parts

Combine multiple excel files into one master file using Python

1 Answers1