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.
Hi Jay, this is an excellent website. I am on a similar path to you except I’m more of a beginner.
I was wondering if you knew how to merge all Excel files in one folder into one big file? I.e. not using concatenate but merging when the rows are in different orders.
Thanks for your comment! I’m glad that you found the content helpful.
It sounds you are trying to do something like vlookup based on a common ID and bring all the columns into one file? You can check out the following:
1. Get all files from a folder: http://220.127.116.11/read-multiple-excel-files-into-python/
2. Merge files: http://18.104.22.168/how-to-merge-multiple-excel-files-in-python/
Hope that helps, let me know!
Hi. I’m developing a software that will store some data and automatically add it to the database. I Want to make a features that if I select the transaction number all the data under that will be generate in excel file. I’m open for live teaching I need your help
If you can give me a detailed example of what you want to achieve, I can try to help.