Exporting DataFrame to Excel File with Pandas to_excel() in Python

The to_excel() function in Pandas is used to export a DataFrame to an Excel file. In just a few lines of code, we can take our DataFrame and export it to an Excel File. We have to only specify the file name, and Pandas will do the rest for us. It’s like telling our computer, “Hey, save this table to an Excel file” and it does all the work behind the scenes. This function is very useful when we want to share our data or work with other programs that understand Excel files.

Also Read – Export Dataframe Objects to Stata Format with Python Pandas

Syntax of Pandas to_excel() Function

Let us get started by understanding the fundamental constituents of the to_excel() function with the help of its syntax given below.

Syntax:

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:

excel_writer: File path or ExcelWriter object.
sheet_name: Name of the sheet in the Excel file.
na_rep: String representation of NaN values.
index: Write row names (index).
index_label: Column label for index column.
header: Write column names (header).
columns: Columns to write.

Exporting DataFrame to an Excel File

Let’s create a sample DataFrame using the DataFrame() function of the Pandas library.

Sample DataFrame:

import pandas as pd
df = pd.DataFrame([['a','b','c'],['d','e','f']],
                 index=['row 1','row 2'],
                 columns=['col 1','col 2','col 3'])
df

Now let’s export this DataFrame to an Excel file using the to_excel() function.

df.to_excel("out.xlsx")

In the code above we have just passed the Excel file name out.xlsx into which we want to export our DataFrame df.

Output:

We can see that our DataFrame df got exported to an Excel file out.xlsx.

Ignoring Index Column in Exported File

Now if we want to dump data and we don’t want the index column to be printed in Excel, we just need to pass the index parameter as False in the to_excel() method so that the index column will not get printed in the Excel file.

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

Output:

We can see in the output that our index column is not there in the Excel file.

Ignoring Header in Exported File

Let’s say we have a case where we need to export the DataFrame into an Excel file but the condition is we don’t need the column names or header in the file so to do this we just need to simply pass the header parameter as false into the to_excel() function.

df.to_excel('out.xlsx', header=False)

Output:

Here we can see that column names or headers have been removed from the Excel file.

Exporting Specific Columns Only

We can also export specific columns from a DataFrame to an Excel File (not the whole DataFrame) by just mentioning the particular columns that we need to export to a parameter called columns.

df.to_excel('out.xlsx', columns=['col 1','col 3'])

We have passed columns parameter as [‘col 1′,’col 3’] so that we can only export col 1 and col 3 to the Excel file and not all the columns from the DataFrame.

Output:

Exporting to Excel with a Sheet Name

We can also mention the particular sheet name of the Excel File in which we want to export the DataFrame by just giving any sheet name to the parameter sheet_name.

df.to_excel('out.xlsx', sheet_name = "hello")

We have passed sheet_name as hello, which means the DataFrame df will export into the sheet called hello of Excel file out.xlsx.

Output:

Summary

In this tutorial, we have discussed the to_excel() function provided by Python’s Pandas library to export a DataFrame to an Excel file. We have also explored how to export the Pandas DataFrame to an Excel file by ignoring the index and header of the DataFrame. With this, we also exported the specific columns of the DataFrame and then exported DataFrame to Excel with a specific sheet name. CodeForGeek has many other entertaining and equally informative articles that can be of great help to those who want to advance in Python, so be sure to check them out as well.