Pandas – Save DataFrame to an Excel file

Excel files can be a great way of saving your tabular data particularly when you want to display it (and even perform some formatting to it) in a nice GUI like Microsoft Excel. In this tutorial, we’ll look at how to save a pandas dataframe to an excel .xlsx file.

Note: The terms “excel file” and “excel workbook” are used interchangeably in this tutorial.

The pandas DataFrame to_excel() function is used to save a pandas dataframe to an excel file. It’s like the to_csv() function but instead of a CSV, it writes the dataframe to a .xlsx file. The following is its syntax:

df.to_excel("path\file_name.xlsx")

Here, df is a pandas dataframe and is written to the excel file file_name.xlsx present at the location path. By default, the dataframe is written to Sheet1 but you can also give custom sheet names. You can also write to multiple sheets in the same excel workbook as well (See the examples below).

Note that once the excel workbook is saved, you cannot write further data without rewriting the whole workbook.

First, we’ll create a sample dataframe that we’ll be using throughout this tutorial.

import pandas as pd

data = {
    'Name': ['Microsoft Corporation', 'Google, LLC', 'Tesla, Inc.',\
             'Apple Inc.', 'Netflix, Inc.'],
    'Symbol': ['MSFT', 'GOOG', 'TSLA', 'AAPL', 'NFLX'],
    'Shares': [100, 50, 150, 200, 80]
}

df = pd.DataFrame(data)
df
The dataframe to be saved to an excel file.

Now, let’s look at examples of some of the different use-cases where the to_excel() function might be useful.

df.to_excel("portfolio.xlsx")

If you just pass the file name to the to_excel() function and use the default values for all the other parameters, the resulting Excel file gets saved in your current working directory with the given file name. Here’s a snapshot of the file when opened in Excel.

Snapshot of the excel file saved

You can see that by default, the dataframe is saved to the sheet Sheet1. Also, note that the index of the dataframe is saved as a separate column. Pass index=False if you don’t want index as a separate column in the excel file.

# to not include index as a column
df.to_excel("portfolio.xlsx", index=False)

Here’s how the saved excel file looks now.

Snapshot of the saved pandas dataframe without index in excel.

You can specify the name of the worksheet using the sheet_name parameter.

# with custom sheet name
df.to_excel("portfolio.xlsx", sheet_name="stocks")
Snapshot of the saved dataframe with custom worksheet name

You can see in the above snapshot that the resulting excel file has stocks as its sheet name.

You can also save dataframes to multiple worksheets within the same workbook using the to_excel() function. For this, you need to specify an ExcelWriter object which is a pandas object used to write to excel files. See the example below:

# write to multiple sheets
df2 = df.copy()
with pd.ExcelWriter("portfolio.xlsx") as writer:
    df.to_excel(writer, sheet_name="stocks1")
    df2.to_excel(writer, sheet_name="stocks2")

Here’s how the saved excel file looks.

Snapshot of excel file with dataframes saved to two worksheets

In the above example, an ExcelWriter object was used to write the dataframes df and df2 to the worksheets stocks1 and stocks2 respectively.

Note that creating an ExcelWriter object with a file name that already exists will result in the contents of the existing file being erased.

For more on the pandas dataframe to_excel() function, refer to its official documentation.

With this, we come to the end of this tutorial. The code examples and results presented in this tutorial have been implemented in a Jupyter Notebook with a python (version 3.8.3) kernel having pandas version 1.0.5


Subscribe to our newsletter for more informative guides and tutorials.
We do not spam and you can opt out any time.