write 2 dataframes in 2 different excel sheets

July 1, 2022

I was trying something like this:

output_2df = pd.concat([installs, second_installs], ignore_index=True)
output_2df.to_excel('./installs.xlsx',sheet_name='Answers')

output_qdf = pd.concat([a_questionnaires, b_questionnaires], ignore_index=True)
output_qdf.to_excel('./installs.xlsx',sheet_name='questionnaires')

Although it works without an error, when I open my Excel file, I only see the second sheet. The data from the first dataframe seems to be overwritten.

Then I tried this:

writer = pd.ExcelWriter('./installs.xlsx', engine='openpyxl')

# Append final dfs to one output_df

output_2df = pd.concat([installs, second_installs], ignore_index=True)
output_2df.to_excel(writer, sheet_name='Answers')


output_qdf = pd.concat([a_questionnaires, b_questionnaires], ignore_index=True)
output_qdf.to_excel(writer, sheet_name='Questionnaires')

However, in this case when I open my Excel file manually, I get an error that it cannot be opened since the file type is invalid.

What’s the correct way to write two different dataframes to 2 separate sheets in the same Excel file?

>Solution :

You can use pd.ExcelWriter for this:

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('file_name.xlsx', engine='xlsxwriter')
# Write each dataframe to a different worksheet.
clean_year_examens.to_excel(writer, sheet_name='Examens jaar')
df1.to_excel(writer, sheet_name='sheet_name1')
df2.to_excel(writer, sheet_name='sheet_name2')
# Close the Pandas Excel writer and output the Excel file.
writer.save()