Exporting Multiple Pandas Dataframes to Excel

It can be tricky to export dataframes to multiple sheets, especially due to the nuances of dealing with any existing sheets.

#HowTo
#Python
#Excel

This post will take you through exporting to a single sheet, multiple sheets, and multiple sheets without overwriting existing sheets.

First let’s import Pandas and create three sample dataframes df1, df2 and df3.

import pandas as pd

df1 = pd.DataFrame({'col1': ['Hello', 'World!'], 'col2': [1, 2]})
df2 = pd.DataFrame({'col1': ['Bye', 'World!'], 'col2': [1, 2]})
df3 = pd.DataFrame({'col1': ['Hi again', 'World!'], 'col2': [1, 2]})

Exporting one dataframe

df1.to_excel("myexcel.xlsx", sheet_name='Sheet_name_1')

If myexcel.xlsx already exists then the entire workbook will be overwritten.

Exporting more than one dataframe

To include more than one dataframe in different sheets, you need to use the code below.

Note that this will also overwrite all existing sheets in the workbook:

with pd.ExcelWriter('myexcel.xlsx') as writer:  
     df1.to_excel(writer, sheet_name='Sheet_name_1')
     df2.to_excel(writer, sheet_name='Sheet_name_2')

Keep existing sheets

If you don’t want to delete any existing sheets then you need to use the options mode='a' (where a = append), engine='openpyxl' and if_sheet_exists='replace' in the ExcelWriter class.

with pd.ExcelWriter('myexcel.xlsx', mode='a', engine='openpyxl', if_sheet_exists='replace') as writer:
     df3.to_excel(writer, sheet_name='Sheet_name_3')

You’ll see that both sheets from the previous step are still included in the workbook.

Note that if_sheet_exists='replace' can be set to 'new' to create a new sheet if it already exists. The new sheet’s name will be determined by the engine openpyxl.