5 Best Ways to Integrate Python, pandas, and XlsxWriter

💡 Problem Formulation: Working with data is a common task in various industries and Python is a powerful tool in this respect. Often, data scientists and analysts need to manipulate data using pandas and export the results to an Excel format for reporting. XlsxWriter is a Python module that helps with writing to Excel files. This article demonstrates how to combine pandas’ data manipulation capabilities with XlsxWriter’s advanced Excel writing features to create and enhance spreadsheets programmatically. Imagine you have a dataframe that needs custom formatting in the resulting Excel sheet.

Method 1: Writing DataFrames to Excel

With pandas and XlsxWriter combined, writing a DataFrame to an Excel file is straightforward. Use pandas to create the DataFrame and XlsxWriter as the Excel writer backend. This pairing allows you to export data directly to Excel format without losing the structure of the DataFrame.

♥️ Info: Are you AI curious but you still have to create real impactful projects? Join our official AI builder club on Skool (only $5): SHIP! - One Project Per Month

Here’s an example:

import pandas as pd

# Create a DataFrame
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})

# Create a Pandas Excel writer using XlsxWriter as the engine
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')

# Write the DataFrame data to XlsxWriter
df.to_excel(writer, sheet_name='Sheet1')

# Close the Pandas Excel writer and output the Excel file
writer.save()

The output is an Excel file named pandas_simple.xlsx with one worksheet titled ‘Sheet1’ that contains the data from the DataFrame.

This code snippet is the foundation for working with pandas and XlsxWriter. It takes the ‘Data’ from our DataFrame and exports it to a newly created Excel file, with each DataFrame row and column properly placed into the Excel worksheet.

Method 2: Applying Cell Formats

Advanced Excel formatting is possible by creating format objects using XlsxWriter and applying them to cells. This way, you can customize text format, cell color, borders, and more to enhance the visual aspects of your Excel reports.

Here’s an example:

import pandas as pd

# Create a DataFrame
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})

# Create a Pandas Excel writer using XlsxWriter as the engine
writer = pd.ExcelWriter('pandas_formatted.xlsx', engine='xlsxwriter')

# Convert the DataFrame to an XlsxWriter Excel object
df.to_excel(writer, sheet_name='Sheet1')

# Get the XlsxWriter objects
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Add a format. Light red fill with dark red text
format1 = workbook.add_format({'bg_color': '#FFC7CE',
                               'font_color': '#9C0006'})

# Apply a conditional format to the cell range
worksheet.conditional_format('B2:B8', {'type': '3_color_scale',
                                       'format': format1})
# Close the Pandas Excel writer and output the Excel file
writer.save()

The output is an Excel file named pandas_formatted.xlsx with custom formatting applied to the cells containing data.

This code demonstrates how to not only write data to an Excel file but also apply formats to cells using XlsxWriter. The ‘3_color_scale’ conditional format changes the cell background based on the cell value, creating a visual scale of colors.

Method 3: Adding Charts to Excel Files

Another powerful feature is adding charts to Excel files. XlsxWriter can add various chart types to your spreadsheet, turning raw data into insightful visuals.

Here’s an example:

import pandas as pd

# Create a sample DataFrame
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})

# Create a Pandas Excel writer using XlsxWriter as the engine
writer = pd.ExcelWriter('pandas_with_charts.xlsx', engine='xlsxwriter')

# Convert DataFrame to an XlsxWriter Excel object
df.to_excel(writer, sheet_name='Sheet1')

# Get XlsxWriter workbook and worksheet objects
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

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

# Configure the series of the chart from the DataFrame data
chart.add_series({
    'values': '=Sheet1!$B$2:$B$8',
    'gap': 2,
})

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

# Close the Pandas Excel writer and output the Excel file
writer.save()

The output is an Excel file named pandas_with_charts.xlsx containing a column chart that represents the DataFrame’s data visually.

The code snippet creates a column chart from the values in the DataFrame and places it in the worksheet. The ‘chart’ object is configured with the DataFrame’s data range, and then the ‘insert_chart’ method places the column chart in the specified location in the worksheet.

Method 4: Inserting Images

Inserting images into Excel reports is a useful way to include logos or visual aids. With XlsxWriter, you can easily insert images into your Excel workbooks.

Here’s an example:

import pandas as pd

# Create a simple DataFrame
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})

# Create a Pandas Excel writer using XlsxWriter as the engine
writer = pd.ExcelWriter('pandas_images.xlsx', engine='xlsxwriter')

# Convert DataFrame to an XlsxWriter Excel object
df.to_excel(writer, sheet_name='Sheet1')

# Get XlsxWriter workbook and worksheet objects
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Insert an image
worksheet.insert_image('D2', 'logo.png')

# Close the Pandas Excel writer and output the Excel file
writer.save()

The output is an Excel file named pandas_images.xlsx with an image inserted alongside the DataFrame data.

In this example, ‘insert_image’ method is used to add an image called ‘logo.png’ into the worksheet. This method places the image at the specified cell location, adding a visual element to the Excel report.

Bonus One-Liner Method 5: Auto-Adjusting Column Widths

Adjusting column widths can be a hassle when working with multiple data columns. The pandas and XlsxWriter libraries can work together to automatically calculate and set the column widths based on the longest text in each column.

Here’s an example:

import pandas as pd

# Create a DataFrame with longer text
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45], 
                   'Comments': ['Good results', 'Average performance', 'Excellent!', 'Average performance', 'Could be better', 'Excellent!', 'Outstanding achievement']})

# Create a Pandas Excel writer using XlsxWriter as the engine
writer = pd.ExcelWriter('pandas_auto_width.xlsx', engine='xlsxwriter')

# Convert DataFrame to an XlsxWriter Excel object
df.to_excel(writer, sheet_name='Sheet1')

# Get XlsxWriter workbook and worksheet objects
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Auto-adjust columns' width
for column in df:
    column_width = max(df[column].astype(str).map(len).max(), len(column))
    col_idx = df.columns.get_loc(column)
    writer.sheets['Sheet1'].set_column(col_idx, col_idx, column_width)
    
# Close the Pandas Excel writer and output the Excel file
writer.save()

The output is an Excel file pandas_auto_width.xlsx with column widths automatically adjusted to fit the contents.

This one-liner (wrapped in a loop for each column) intelligently adjusts the width of each column to accommodate the longest piece of text in that column. This is essential for readability, especially when dealing with varied lengths of data.

Summary/Discussion

Method 1: Writing DataFrames to Excel. Simplicity. Limited styling options.
Method 2: Applying Cell Formats. Enhanced presentation. Requires familiarity with XlsxWriter formatting.
Method 3: Adding Charts to Excel Files. Visual data representation. Chart options can be complex.
Method 4: Inserting Images. Personalize reports. Requires images to be appropriately sized and formatted.
Bonus One-Liner Method 5: Auto-Adjusting Column Widths. Improves readability. May not always provide perfect sizing.