Last Updated on July 14, 2022 by Jay
This short article is part of the Transition from Excel to Python series. Last time we learned how to save one sheet into an Excel file. Now we’ll save multiple sheets to one (the same) Excel file using Python pandas.
Just a reminder:
df stands for
pd is short for
We’ll still use the
df.to_excel() method, but we’ll need help from another class
pd.ExcelWriter(). As its name suggests, this class writes to Excel files. If you read carefully the
pd.to_excel() documentation, the ExcelWriter is actually the first argument.
Let’s create some mock-up dataframes, so we have something to work with. We create 2 dataframes, the first one is a 20 row by 10 columns random numbers; and the second dataframe is 10 rows by 1 column.
import pandas as pd import numpy as np df_1 = pd.DataFrame(np.random.rand(20,10)) df_2 = pd.DataFrame(np.random.rand(10,1))
We’ll go through 2 methods of saving multi-sheets Excel files. The idea is pretty much the same between the 2 methods: we create an
ExcelWriter, then pass it into the
df.to_excel() for saving dataframe into an Excel file. The two methods are slightly different in syntax but they work the same way.
This is the method demonstrated on the official pandas documentation.
with pd.ExcelWriter('mult_sheets_1.xlsx') as writer1: df_1.to_excel(writer1, sheet_name = 'df_1', index = False) df_2.to_excel(writer1, sheet_name = 'df_2', index = False)
This is my personal preferred method. Let me show you how it looks like then tell you why I prefer this over method 1.
writer2 = pd.ExcelWriter('mult_sheets_2.xlsx') df_1.to_excel(writer2, sheet_name = 'df_1', index = False) df_2.to_excel(writer2, sheet_name = 'df_2', index = False) writer2.save()
By now you must think that these two methods are the same! Well, yes and no.
It’s true that both methods do exactly the same thing – saving the two dataframes into a single Excel file. however, the mechanism is quite different.
First of all, because of the with block in method 1, all your dataframes have to be in the same scope. This means if you have a dataframe outside of the current scope, you will have to bring it in first.
Whereas for method 2, the dataframes can be in different scopes and it will still work. This is especially useful when your code is complicated.