Python Pandas - Writing Data to Excel Files

---

---

Pandas is a data analysis library in Python, which is widely used for working with structured data from various formats including CSV, SQL, and Excel files. One of the key features of this library is that it allows you to easily export data from Pandas DataFrames and Series directly into Excel spreadsheets using theto_excel() method.

Theto_excel() method in Pandas allows you to export the data from a DataFrame or Series into an Excel file. This method provides the flexibility in specifying various parameters such as file path, sheet name, formatting options, and more.

In the previous tutorial, we learned aboutReading Excel Files with Pandas, now we will learn how to write Pandas data to Excel files in Python using Pandas. This complete guide will discuss theto_excel() method, exporting multiple sheets, appending data, and memory-based operations with examples.

Overview of The Pandas to_excel() Method

The Pandasto_excel() method is used to write a DataFrame or Series to an Excel file. It allows you to specify various configurations such as the sheet name, columns to write, and more.

Following is the syntax of this method −

DataFrame.to_excel(excel_writer, *, sheet_name='Sheet1', na_rep='', columns=None, header=True, index=True, ...)

Key parameters are −

• excel_writer: This represents the path to the Excel file or an existingExcelWriter object. TheExcelWriter object is used when writing multiple sheets to a file.

• sheet_name: This specifies the name of the sheet where the DataFrame will be written. By default, it's set to 'Sheet1'.

• na_rep: A string to represent missing data in the Excel file. Default is an empty string.

• columns: A sequence or list of column names to write.

Writing a Single DataFrame to an Excel

By simply calling theDataFrame.to_excel() method with the Excel file name, and an optional sheet name, you can directly export the contents of the Pandas DataFrame object into a sheet of an Excel file.

Example

Here is a basic example of writing the contents of a Pandas DataFrame to an Excel file using theDataFrame.to_excel() method.

import pandas as pd# Create a DataFramedf = pd.DataFrame([[5, 2], [4, 1]],index=["One", "Two"],columns=["Rank", "Subjects"])# Display the DataFrameprint("DataFrame:\n", df)# Export DataFrame to Exceldf.to_excel('Basic_example_output.xlsx')print('The Basic_example_output.xlsx file is saved successfully..')

Following is the output of the above code −

DataFrame:

	Rank	Subjects
One	5	2
Two	4	1

The Basic_example_output.xlsx file is saved successfully..

Note: After executing each code, you can find the generated output files in your working directory.

Exporting Multiple DataFrames to Different Sheets

Writing the multiple DataFrames to different sheets within the same Excel file is possible by usingExcelWriter class.

Example

Following is the example of writing the multiple DataFrames to different sheets within the same Excel file usingExcelWriter class and theto_excel() method.

import pandas as pddf1 = pd.DataFrame(   [[5, 2], [4, 1]],   index=["One", "Two"],   columns=["Rank", "Subjects"])df2 = pd.DataFrame(   [[15, 21], [41, 11]],   index=["One", "Two"],   columns=["Rank", "Subjects"])print("DataFrame 1:\n", df1)print("DataFrame 2:\n", df2)with pd.ExcelWriter('output_multiple_sheets.xlsx') as writer:df1.to_excel(writer, sheet_name='Sheet_name_1')df2.to_excel(writer, sheet_name='Sheet_name_2')print('The output_multiple_sheets.xlsx file is saved successfully..')

Following is the output of the above code −

DataFrame 1:

	Rank	Subjects
One	5	2
Two	4	1

DataFrame 2:

	Rank	Subjects
One	15	21
Two	41	11

The output_multiple_sheets.xlsx file is saved successfully..

Appending Data to an Existing Excel File

Appending the contents of a DataFrame to an existing Excel file is possible by usingExcelWriter withmode='a'. TheExcelWriter object helps you to open the existing Excel file in the appending mode and then allows you to add the new data to the existing file.

Example

The following example demonstrates how to append the contents of a DataFrame to the existing Excel file.

import pandas as pd# Create a new DataFramedf3 = pd.DataFrame([[51, 11], [21, 38]],index=["One", "Two"],columns=["Rank", "Subjects"])# Append the DataFrame to an existing Excel filewith pd.ExcelWriter('output_multiple_sheets.xlsm', mode='a') as writer:    df3.to_excel(writer, sheet_name='Sheet_name_3', index=False)print('The output_multiple_sheets.xlsm file is saved successfully with the appended sheet..')

Following is the output of the above code −

The output_multiple_sheets.xlsm file is saved successfully with the appended sheet..

Writing Excel Files to Memory Using Pandas

Writing Excel files to memory (buffer-like objects) instead of saving them to disk is possible by usingBytesIO orStringIO along withExcelWriter.

Example

The following example demonstrates how to write an Excel file to a memory object using theBytesIO and theExcelWriter class.

import pandas as pdfrom io import BytesIOdf = pd.DataFrame([[5, 2], [4, 1]],index=["One", "Two"],columns=["Rank", "Subjects"])print("Input DataFrame :\n", df)# Create a BytesIO objectbio = BytesIO()# Write the DataFrame to the BytesIO bufferdf.to_excel(bio, sheet_name='Sheet1')# Get the Excel file from memorybio.seek(0)excel_data = bio.read()print('\nThe Excel file is saved in memory successfully..')

Following is the output of the above code −

Input DataFrame :

	Rank	Subjects
One	5	2
Two	4	1

The Excel file is saved in memory successfully..

Choosing an Excel Writer Engine in Pandas

Pandas supports multiple engines for writing Excel files, such asopenpyxl andxlsxwriter. You can specify the engine explicitly as you need using theengine parameter of theDataFrame.to_excel() method. And make sure that you have installed the required engine in your system.

Example

This example demonstrates saving an Excel file with a specified engine using theengine parameter of theDataFrame.to_excel() method.

import pandas as pdfrom io import BytesIOdf = pd.DataFrame(   [[5, 2], [4, 1]],   index=["One", "Two"],   columns=["Rank", "Subjects"])# Write DataFrame using xlsxwriter enginedf.to_excel('output_xlsxwriter.xlsx', sheet_name='Sheet1', engine='xlsxwriter')print('The output_xlsxwriter.xlsx is saved successfully..')

Following is the output of the above code −

The output_xlsxwriter.xlsx is saved successfully..