Python Pandas Write To Excel

Are you working with data in Python and need to export your analysis results to Excel? I do this almost daily in my data analysis workflow.

Pandas makes it incredibly easy to write DataFrames to Excel files with just a few lines of code.

In this tutorial, I’ll show you multiple ways to write Pandas DataFrames to Excel files, along with tips and tricks I’ve learned over my years of Python development.

Table of Contents

Python Pandas Write to Excel

Let me show you the important methods to write to Excel with Python Pandas.

Read a CSV to the dictionary using Pandas in Python

1. Use Pandas to_excel()

Let’s start with the simplest method that I use most often.

import pandas as pd

# Create a sample DataFrame with US sales data
data = {
    'State': ['California', 'Texas', 'New York', 'Florida', 'Illinois'],
    'Sales': [125000, 93000, 87500, 68000, 53200],
    'Profit': [42500, 31200, 29800, 23500, 18700]
}

df = pd.DataFrame(data)

# Write DataFrame to Excel
df.to_excel('us_sales_data.xlsx', index=False)

Output:

Excel file 'us_sales_data.xlsx' created successfully.

I executed the above example code and added the screenshot below

The to_excel() method is easy and useful. I’ve set index=False to avoid including the DataFrame index in the Excel file, which is usually what you want unless you’re specifically using meaningful indices.

Check out Python Dataframe Update Column Value

2. Use ExcelWriter for Multiple Sheets

When I need to write multiple DataFrames to different sheets in the same Excel file, I use the ExcelWriter class:

import pandas as pd

# Create sample DataFrames with US regional sales data
west_data = {
    'State': ['California', 'Washington', 'Oregon', 'Nevada'],
    'Sales': [125000, 62000, 45800, 38700],
    'Profit': [42500, 21800, 16200, 13200]
}

east_data = {
    'State': ['New York', 'Massachusetts', 'New Jersey', 'Pennsylvania'],
    'Sales': [87500, 58200, 52100, 47600],
    'Profit': [29800, 19700, 18100, 16500]
}

df_west = pd.DataFrame(west_data)
df_east = pd.DataFrame(east_data)

# Write to multiple sheets
with pd.ExcelWriter('regional_sales.xlsx') as writer:
    df_west.to_excel(writer, sheet_name='West Region', index=False)
    df_east.to_excel(writer, sheet_name='East Region', index=False)

I executed the above example code and added the screenshot below

I prefer using the context manager (with statement) as it ensures the Excel file is properly closed even if an error occurs.

Read Convert Pandas Dataframe to Tensor Dataset

3. Customize Excel Output with Formatting

One thing I love about Pandas’ Excel export capabilities is how easily you can customize the output:

import pandas as pd

# Create sample DataFrame
data = {
    'Product': ['Laptops', 'Smartphones', 'Tablets', 'Headphones'],
    'Q1_Sales': [320000, 450000, 210000, 105000],
    'Q2_Sales': [350000, 470000, 190000, 125000],
    'Q3_Sales': [380000, 510000, 200000, 130000],
    'Q4_Sales': [420000, 550000, 230000, 145000]
}

df = pd.DataFrame(data)

# Create Excel writer with xlsxwriter engine
with pd.ExcelWriter('quarterly_sales.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='Quarterly Sales', index=False)

    # Get workbook and worksheet objects
    workbook = writer.book
    worksheet = writer.sheets['Quarterly Sales']

    # Add a format for the header
    header_format = workbook.add_format({
        'bold': True,
        'text_wrap': True,
        'valign': 'top',
        'bg_color': '#D7E4BC',
        'border': 1
    })

    # Apply the header format to the header row
    for col_num, value in enumerate(df.columns.values):
        worksheet.write(0, col_num, value, header_format)

    # Set column widths
    worksheet.set_column('A:A', 15)
    worksheet.set_column('B:E', 12)

I executed the above example code and added the screenshot below

For this method, you’ll need to install xlsxwriter with:

pip install xlsxwriter

Read How to Get Index Values from DataFrames in Pandas Python

4. Write a DataFrame with Conditional Formatting

When presenting data in Excel, conditional formatting can make insights jump out visually:

import pandas as pd
import numpy as np

# Create sample US stock market data
np.random.seed(42)
dates = pd.date_range('2023-01-01', periods=10, freq='B')
stocks = ['AAPL', 'MSFT', 'AMZN', 'GOOGL', 'META']

data = {}
for stock in stocks:
    # Generate random daily returns between -3% and +3%
    data[stock] = np.random.uniform(-0.03, 0.03, len(dates))

df = pd.DataFrame(data, index=dates)

# Calculate cumulative returns
df_cum = (1 + df).cumprod() - 1

# Write to Excel with conditional formatting
with pd.ExcelWriter('stock_returns.xlsx', engine='xlsxwriter') as writer:
    df_cum.to_excel(writer, sheet_name='Cumulative Returns')

    workbook = writer.book
    worksheet = writer.sheets['Cumulative Returns']

    # Add a format for positive returns (green)
    positive_format = workbook.add_format({'bg_color': '#C6EFCE'})

    # Add a format for negative returns (red)
    negative_format = workbook.add_format({'bg_color': '#FFC7CE'})

    # Apply conditional formatting to all data cells
    worksheet.conditional_format('B2:F11', {
        'type': 'cell',
        'criteria': '>',
        'value': 0,
        'format': positive_format
    })

    worksheet.conditional_format('B2:F11', {
        'type': 'cell',
        'criteria': '<',
        'value': 0,
        'format': negative_format
    })

Check out Convert a DataFrame to JSON in Python

5. Add Charts to Excel Output

Adding charts can transform raw data into visual insights:

import pandas as pd

# Create sample monthly revenue data for a US tech startup
months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun']
data = {
    'Month': months,
    'Subscription Revenue': [15000, 17500, 19800, 22300, 25700, 28900],
    'Ad Revenue': [5200, 5800, 6100, 7300, 7900, 8500],
    'Merchandise': [2300, 1900, 2700, 3100, 3600, 4200]
}

df = pd.DataFrame(data)

# Write to Excel and add a chart
with pd.ExcelWriter('startup_revenue.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='Revenue Data', index=False)

    workbook = writer.book
    worksheet = writer.sheets['Revenue Data']

    # Create a chart
    chart = workbook.add_chart({'type': 'line'})

    # Configure the series of the chart from the dataframe data
    for i in range(1, 4):
        chart.add_series({
            'name':       ['Revenue Data', 0, i],
            'categories': ['Revenue Data', 1, 0, 6, 0],
            'values':     ['Revenue Data', 1, i, 6, i],
        })

    # Configure chart title and axis labels
    chart.set_title({'name': 'Monthly Revenue Trends'})
    chart.set_x_axis({'name': 'Month'})
    chart.set_y_axis({'name': 'Revenue ($)'})

    # Insert the chart into the worksheet
    worksheet.insert_chart('H2', chart)

Tips for Writing Excel Files with Pandas

Over my years of working with Pandas and Excel, I’ve gathered these useful tips:

Check out Convert a DataFrame to JSON Array in Python

1. Handle Large DataFrames

When working with large datasets, you might encounter memory issues. I recommend:

# For large files, use the 'openpyxl' engine
df.to_excel('large_data.xlsx', index=False, engine='openpyxl')

2. Preserve Data Types

Excel sometimes changes data types. To preserve them:

# For preserving numeric formats
with pd.ExcelWriter('data_types.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, index=False)
    workbook = writer.book
    worksheet = writer.sheets['Sheet1']

    # Format columns with specific number formats
    # This preserves numbers like ZIP codes that start with 0
    worksheet.set_column('B:B', 12, workbook.add_format({'num_format': '00000'}))

3. Password Protection

If you’re handling sensitive data, you might want to password-protect your Excel file:

# Using openpyxl for password protection
from openpyxl import load_workbook
from openpyxl.workbook.protection import WorkbookProtection

# First write the file
df.to_excel('confidential_data.xlsx', index=False, engine='openpyxl')

# Then add protection
wb = load_workbook('confidential_data.xlsx')
wb.security = WorkbookProtection(workbookPassword='your_password', lockStructure=True)
wb.save('confidential_data.xlsx')

I hope you found this article helpful!

Being able to effectively export your Pandas DataFrames to Excel is a crucial skill for any data professional working in Python. The methods that I explained in this tutorial are: use Pandas to_excel(), ExcelWriter for multiple sheets, customize Excel output with formatting, write a DataFrame with conditional formatting, and add charts to excel output.

Python Pandas Write to Excel

Python Pandas Write to Excel

1. Use Pandas to_excel()

2. Use ExcelWriter for Multiple Sheets

3. Customize Excel Output with Formatting

4. Write a DataFrame with Conditional Formatting

5. Add Charts to Excel Output

Tips for Writing Excel Files with Pandas

1. Handle Large DataFrames

2. Preserve Data Types

3. Password Protection

51 PYTHON PROGRAMS PDF FREE

Aspiring to be a Python developer?

Let’s be friends