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:
| Parameter | Description |
| excel_writer | File path or existing ExcelWriter. |
| na_rep | Expression to represent missing data. |
| columns | Columns that are to be included in the output file. |
| header | Default True. Takes 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. |
| index | Default True. Takes boolean values. If True, writes index in the output else no index is included in the output CSV file. |
| index_label | String 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
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:
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:
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:
Providing new headers to the output Excel file:
df.to_excel("output_header_list.xlsx", header=['New fruit', 'New color',
'New price'])
Output:
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:
Specifying custom index:
df.to_excel('output_custom_index.xlsx', index_label=['Sr. No.'])
Output:
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
The below code is used to represent missing data by the symbol ‘##’ in the output.
df2.to_excel("output_nan.xlsx", na_rep='##')
Output:
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.