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 the to_excel() method.

The to_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 about Reading 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 the to_excel() method, exporting multiple sheets, appending data, and memory-based operations with examples.

Overview of The Pandas to_excel() Method

The Pandas to_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 existing ExcelWriter object. The ExcelWriter 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 the DataFrame.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 the DataFrame.to_excel() method.

import pandas as pd

# Create a DataFrame
df = pd.DataFrame([[5, 2], [4, 1]],index=["One", "Two"],columns=["Rank", "Subjects"])

# Display the DataFrame
print("DataFrame:\n", df)

# Export DataFrame to Excel
df.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:

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 using ExcelWriter class.

Example

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

import pandas as pd

df1 = 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:

Appending Data to an Existing Excel File

Appending the contents of a DataFrame to an existing Excel file is possible by using ExcelWriter with mode='a'. The ExcelWriter 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 DataFrame
df3 = pd.DataFrame([[51, 11], [21, 38]],index=["One", "Two"],columns=["Rank", "Subjects"])

# Append the DataFrame to an existing Excel file
with 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 using BytesIO or StringIO along with ExcelWriter.

Example

The following example demonstrates how to write an Excel file to a memory object using the BytesIO and the ExcelWriter class.

import pandas as pd
from io import BytesIO

df = pd.DataFrame(
[[5, 2], [4, 1]],
index=["One", "Two"],
columns=["Rank", "Subjects"])

print("Input DataFrame :\n", df)

# Create a BytesIO object
bio = BytesIO()

# Write the DataFrame to the BytesIO buffer
df.to_excel(bio, sheet_name='Sheet1')

# Get the Excel file from memory
bio.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 :

Choosing an Excel Writer Engine in Pandas

Pandas supports multiple engines for writing Excel files, such as openpyxl and xlsxwriter. You can specify the engine explicitly as you need using the engine parameter of the DataFrame.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 the engine parameter of the DataFrame.to_excel() method.

import pandas as pd
from io import BytesIO

df = pd.DataFrame(
   [[5, 2], [4, 1]],
   index=["One", "Two"],
   columns=["Rank", "Subjects"]
)

# Write DataFrame using xlsxwriter engine
df.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..