Pandas to_excel(): Write an object to an Excel Sheet

Pandas To Excel Cover Image

Pandas is a popular Python library used for working with data. It has a data structure called a data frame which represents data in the form of rows and columns or tables. It is fast and efficient to use data frames in terms of computation.

A Pandas data frame can be exported to an excel file using the pandas.to_excel() function.

In this article, you will learn about the pandas.to_excel() function which lets you pass a file object to save it as an Excel sheet.

Also read: How to save a Pandas data frame as a CSV file?


Prerequisites of to_excel()

You need to have Python and Pandas installed on your computer and your favorite IDE set up to start coding. 

If you don’t have Pandas installed, you can install it using the command:

pip install pandas

If you are using the Anaconda distribution use the command:

conda install pandas

Syntax of pandas.to_excel()

DataFrame.to_excel(excel_writer, 
sheet_name='Sheet1', 
na_rep='', 
float_format=None, 
columns=None, 
header=True, 
index=True, 
index_label=None, 
startrow=0, 
startcol=0, 
engine=None, 
merge_cells=True, 
encoding=_NoDefault.no_default, 
inf_rep='inf', 
verbose=_NoDefault.no_default, 
freeze_panes=None, 
storage_options=None)

Some of the important parameters are:

ParameterDescription
excel_writerFile path or existing ExcelWriter.
na_repExpression to represent missing data.
columnsColumns that are to be included in the output file.
headerDefault TrueTakes boolean values or a list of strings. If True, the header names are included else not. If a list of strings is given, it is used as an alias for column names and its length should be the same as the number of columns desired in the output file.
indexDefault True. Takes boolean values. If True, writes index in the output else no index is included in the output CSV file.
index_labelString or a sequence of strings. Label for index column(s).

Exporting a data frame as an Excel Sheet

Let us first create a data frame.

import pandas as pd

# creating a data frame
data = {
    "fruit": ["apple", "banana", "orange", "watermelon"],
    "color": ["red", "yellow", "orange", "green"],
    "price": [120, 40, 80, 150]
}

df = pd.DataFrame(data)

df
To Excel Df

Now, you can export this data frame as an Excel sheet by using the pandas.to_excel() as shown below.

df.to_excel("output.xlsx")

By default, the above statement saves the data frame as an excel sheet in the same directory where the code file resides. If you want to save the resulting excel sheet in some other directory, you can do so by specifying the file path in the excel_writer parameter.

Output:

To Excel Op1

Selecting only specific columns for the Excel sheet

The ‘columns‘ parameter enables you to provide the names of only those columns that you want in your resulting excel sheet.

The below code shows that only the columns ‘fruit’ and ‘color’ are included in the Excel file.

df.to_excel("output_specific_cols.xlsx", columns=['fruit', 'color'])

Output:

To Excel Op2

Specifying headers for the Excel output file

You can specify if you want the headers of the columns to be there in the Excel output file or not. You can also change the existing column headers/names to new headers by providing them as a list of strings to the function using the ‘header’ parameter.

Removing headers:

df.to_excel("output_header.xlsx", header=False)

Output:

To Excel Op3

Providing new headers to the output Excel file:

df.to_excel("output_header_list.xlsx", header=['New fruit', 'New color', 
'New price'])

Output:

To Excel Op4

The above output shows new headers in the resulting Excel file.


Specifying index for the Excel Output File

Just like the ‘header’ parameter, the ‘index’ parameter lets you specify if you want to include the index in the output or not. You can also specify a custom index for the output using the ‘index_label’ parameter as shown below.

Removing the index:

df.to_excel('output_no_index.xlsx', index=False)

Output:

To Excel Op5

Specifying custom index:

df.to_excel('output_custom_index.xlsx', index_label=['Sr. No.'])

Output:

To Excel Op6

The above output shows the new index as ‘Sr. No.’ as specified in the ‘index_label’ parameter in the pandas.to_excel() function.


Specifying NA representation

In case the data frame contains some missing data, you can specify how to represent it in the Excel sheet using the ‘na_rep’ parameter of the to_excel() function. You can specify any character, string, symbol, etc. to represent the missing data.

Let us first create a new data frame with some missing data.

import pandas as pd
import numpy as np

# creating a data frame with missing data
data = {
    "fruit": ["apple", "banana", np.nan, "watermelon"],
    "color": ["red", np.nan, "orange", "green"],
    "price": [120, 40, 80, np.nan]
}

df2 = pd.DataFrame(data)
df2
To Excel Df2

The below code is used to represent missing data by the symbol ‘##’ in the output.

df2.to_excel("output_nan.xlsx", na_rep='##')

Output:

To Excel Op7

Conclusion

The Pandas to_excel() function lets you convert any file object or data frame into the Excel sheet format. It also provides you with many options to customize your Excel output based on your requirements by using the different parameters discussed earlier in this article.


Reference