Combine Multiple Excel Files Into One Master File Using Python

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 se

Solution 1:

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