Pandas Export to Excel

September 28, 2023 | by Arround The Web | No comments

In Python, the popular Pandas library is normally utilized for handling or managing data-related tasks. The Pandas module utilizes the DataFrame, which is a two-dimensional data structure and is utilized for storing data. Sometimes we need to export the stored Pandas DataFrame data into an Excel file in Python for different operations.

This article will discuss the following:

How to Export Pandas DataFrame to Python Excel?

To export the DataFrame of Pandas to Excel the “DataFrame.to_excel()” method is utilized in Python. We can use this method to write on single or multiple sheets of Excel files.

Syntax

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

Parameters

In the above code:

The “excel_writer” specifies the file path or existing ExcelWriter object.
The “sheet_name” parameter represents the sheet name which will include/contain the DataFrame.
The “na_rep” parameter indicates the missing representation of data. It will modify any missing values in the DataFrame with the particular value.
The “float_format” parameter represents the format string for floating point numbers.
The “columns” parameter represents the sequence or list of strings that indicates the columns to write.
The “header” parameter is the boolean or list of strings that indicate whether to write out the column names.

For a detailed understanding of the syntax, you can check this documentation.

Return Value

The “DataFrame.to_excel()” method does not retrieve any value. It only writes the DataFrame object to an Excel sheet in the particular file.

Export Pandas DataFrame to Excel Sheet

The following code is used to export the DataFrame of Pandas to an Excel sheet by utilizing the “df.to_excel()” method. Here is an example code that exports this into an Excel sheet:

import pandas
df = pandas.DataFrame({'Id_no':[45, 39, 27, 55], 'Name':['Joseph', 'Anna', 'Lily', 'Henry'],
'Age':[15, 19, 22, 16], 'Height':[4.6, 6.2, 5.2, 4.2]})
df.to_excel("new.xlsx")

The following snippet shows the Excel file content:

The below output verified that the converted file is an Excel file:

We can also modify the name of the sheet using the “sheet_name” parameter of the “df.to_excel()” method. Here is a code that will show you how to do that:

import pandas
df = pandas.DataFrame({'Id_no':[45, 39, 27, 55], 'Name':['Joseph', 'Anna', 'Lily', 'Henry'],
'Age':[15, 19, 22, 16], 'Height':[4.6, 6.2, 5.2, 4.2]})
df.to_excel("new.xlsx", sheet_name='information')

The below snippet verified that the Excel sheet named has been modified successfully:

Here, we are retrieving the Excel sheet from the Pandas DataFrame without an index column. The “index=False” parameter is used to drop the index columns while exporting the Pandas object into an Excel file:

The below snippet shows the Excel file without any index columns:

Export Multiple Pandas DataFrames to Multiple Excel Sheets Using the “DataFrame.to_excel()” Method

We can also export multiple Pandas DataFrame data to multiple sheets on an Excel file. For example, the below code exports the DataFrames “df1” and “df2” to an Excel file named “new.xlsx” on multiple sheets with different sheet names “info1” and “info2”. We can achieve this by utilizing the “df.to_excel()” method multiple times in a program:

import pandas
df1 = pandas.DataFrame({'Id_no':[45, 39, 27, 55], 'Name':['Joseph', 'Anna', 'Lily', 'Henry'],
'Age':[15, 19, 22, 16], 'Height':[4.6, 6.2, 5.2, 4.2]})
df2 = pandas.DataFrame({'Id_no':[25, 19, 17, 15], 'Name':['Jane', 'Jena', 'Smith', 'Cyndy'],
'Age':[25, 29, 12, 26], 'Height':[5.6, 5.2, 4.2, 3.2]})
with pandas.ExcelWriter('new.xlsx') as obj:
df1.to_excel(obj, sheet_name='info1', index=False)
df2.to_excel(obj, sheet_name='info2', index=False)

Here is the content of the Excel sheet named “info1”:

Here is the content of the Excel sheet named “info2”:

Append Pandas DataFrame to Existing Excel File Using the “DataFrame.to_excel()” Method

The “pandas.ExcelWriter()” is used to open the file “new.xlsx” in an append mode “a”. We can use the “df.to_excel()” method to write the DataFrame to an existing/current Excel file:

Here is an example code, that appends the Pandas DataFame data to an existing file:

import pandas
df1 = pandas.DataFrame({'Id_no':[25, 19, 17, 15], 'Name':['Jane', 'Jena', 'Smith', 'Cyndy'],
'Age':[25, 29, 12, 26], 'Height':[5.6, 5.2, 4.2, 3.2]})
with pandas.ExcelWriter('new.xlsx',mode='a') as writer:
df1.to_excel(writer, sheet_name='info2')

The following snippet verified that the new DataFrame has been appended to the existing Excel file in the new sheet:

Export Specific Columns of Pandas DataFrame to Excel Using “DataFrame.to_excel()” Method

The “columns=” parameter can also be used to export specific columns of Pandas DataFrame to an Excel file. Here is a code that utilizes the “df.to_excel()” method with the “columns=” parameter containing the names of columns to export them into an Excel file:

The specified DataFrame columns have been exported to an Excel file:

Conclusion

The “DataFrame.to_excel()” method in Python is utilized to export a single or multiple DataFrame to single or multiple sheets of an Excel file. The “pandas.ExcelWriter()” is used along with multiple “df.to_excel()” methods to export multiple DataFrames data to multiple Excel file sheets. We can also append Pandas DataFrame to an existing Excel file via the append mode and export specific columns of Pandas DataFrame to Excel using the “columns=” parameter value. This article delivered a comprehensive tutorial on exporting Pandas DataFrame to an Excel file using several examples.

Source: linuxhint.com