Writing Excel Files with Pandas

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.
• sheet_name: This specifies the name of the sheet where the DataFrame will be written.
• 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

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..')

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

Example

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..')

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 using ExcelWriter with mode='a'.

Example

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..')

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

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..')

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 as openpyxl and xlsxwriter. You can specify the engine explicitly using the engine parameter.

Example

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..')

The output_xlsxwriter.xlsx is saved successfully..