Import Pandas dataframes to Excel (one or multiple worksheets)

Readers of this blog know that we are pretty big on finding ways to automate mundane and boring tasks. Python is great for automation, specially when it pertains to the Data Analysis domain.

Lately, we are increasingly spending more an more time with the Pandas data Analysis library, and using it for data munging and visualization. We rather work in Python instead of dealing with the complexity of VBA in the area of data manipulation,

That said, there are situations in which we choose to feed data from Pandas back to an Excel workbook. This allows us to combine Python and Pandas flexibility and speed with great visualization capabilities and the obvious ubiquity of Excel.

Today we’ll show you how to export data from a Pandas DataFrame to an Excel file (xlsx). We’ll deal with two scenarios:

  • Save a Pandas df to an Excel file.
  • Exporting Pandas DataFrames to multiple worksheets in a workbook.

Note: This tutorial requires some basic knowledge of Python programming and specifically the Pandas library.

Defining the Dataset

Before we start, we’ll need to import a few libraries into Python as shown below. Go ahead and type this Python 3 code to you favorite Python editor.

import pandas as pd
import openpyxl
import xlsxwriter

Now let’s create the data that we’ll be using in this tutorial

# define data as a dictionary
data = ({"language": [ "Python", "C-Sharp", "Javascript","PHP"] ,
         "avg_salary": [120, 100, 120, 80],
          "applications": [10,15,14,20]})

# Create a Pandas DataFrame out of a Python dictionary
df = pd.DataFrame.from_dict(data)
# look at the Data

Write Pandas DataFrame to Excel

  1. Ensure that you have loaded the pandas and openpyxl libraries into your environment.
  2. Initialize/Load your DataFrame
  3. Use the DataFrame.to_excel method to export your data

Detailed Walk Through

Now, we would like to export the DataFrame that we just created to an Excel workbook. Pandas has a very handy to_excel method that allows to do exactly that. Let’s use it:


The code will create the languages.xlsx file and export the dataset into Sheet1

Note: In case that you don’t have the openpyxl package installed in your Python environment, you’ll get the following error: ModuleNotFoundError: No module named ‘openpyxl’ . The solution is relatively simple: from your terminal or if you are using Anaconda, from the Anaconda Navigator, type: pip install openpyxl. Then re-run your code again.

If you want to get more fancy you can use the parameters of the to_excel method to customize your data transfer. In our case we set the sheet name, chose not to import the df index picked the columns to export and even define frozen panes in the worksheet.

df.to_excel("languages1.xlsx", sheet_name="Languages", index=False, , freeze_panes=(1,1), columns=["avg_salary", "language"])

Python to Export Pandas DataFrame to multiple sheets

So far so good, but what if we would like to import data into several worksheets? That’s possible as well, although a bit more elaborated.

We’ll first split our data to three different DataFrames:

#we define three lists
S1= data["language"]
S2= data["avg_salary"]
S3= data["applications"]

# We then create three dataframes
df1=pd.DataFrame(S1, columns=["language"])
df2=pd.DataFrame(S2 , columns=["avg_salary"])
df3=pd.DataFrame(S3, columns=["applications"])

# We then groups the dataframes into a list for more efficient processing 
dflist= [df1,df2,df3]

Now we’ll import multiple dataframes to Excel (in this case three dfs to three different worksheets).

# We'll define an Excel writer object and the target file
Excelwriter = pd.ExcelWriter("languages_multiple.xlsx",engine="xlsxwriter")

#We now loop process the list of dataframes
for i, df in enumerate (dflist):
    df.to_excel(Excelwriter, sheet_name="Sheet" + str(i+1),index=False)
#And finally save the file

Voi’la, now go ahead to your directory and look into the languages_multiple.xlsx file. You are done :-).