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 to_excel() function

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.

Examples

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]
}

# create dataframe from data
df = pd.DataFrame(data)
# display the dataframe
df

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

1. Save dataframe to an excel file with default parameters

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.

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 the 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.

2. Save dataframe to an excel file with custom sheet name

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")

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

3. Save to multiple sheets in the same workbook

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.

In the above example, an ExcelWriter object is 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.

You might also be interested in –

• Write a Pandas DataFrame to a JSON File
• Copy Pandas DataFrame to the Clipboard
• Save Pandas DataFrame to a CSV file

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