5 Best Ways to Convert pandas DataFrame to XLSX

by

💡 Problem Formulation: Coders often face the challenge of exporting data for reporting or sharing purposes. This article addresses the conversion of a pandas DataFrame, which may contain intricate data and analysis results, into an XLSX format used by Excel. For instance, converting a DataFrame containing sales statistics with columns for date, revenue, and product category into a neatly formatted Excel spreadsheet that can be shared with non-technical stakeholders.

Method 1: Using DataFrame.to_excel() Method

One of the primary ways to export a DataFrame to an XLSX file is by using the DataFrame.to_excel() method provided by pandas. This method allows specifying the file path, different options for indexing, and the sheet name in the output file.

Here’s an example:

import pandas as pd

# Sample DataFrame
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})
# Convert DataFrame to xlsx
df.to_excel('output.xlsx', index=False, sheet_name='Sheet1')

Output: An XLSX file named ‘output.xlsx’ with the DataFrame data.

This code snippet creates a pandas DataFrame with simple numeric data and exports it to an Excel file, making sure not to include the DataFrame index in the output and naming the Excel sheet ‘Sheet1’.

Method 2: Exporting Multiple DataFrames to One XLSX File

To export multiple DataFrames into a single XLSX file with distinct sheets for each DataFrame, pandas can be used in conjunction with the ExcelWriter object. This approach is useful for grouping related data together.

Here’s an example:

import pandas as pd

# Two DataFrames
df1 = pd.DataFrame({'Data A': [100, 200, 300]})
df2 = pd.DataFrame({'Data B': [400, 500, 600]})

# Create a Pandas Excel writer using XlsxWriter as the engine
with pd.ExcelWriter('multiple_sheets.xlsx') as writer:
    df1.to_excel(writer, sheet_name='Sheet1')
    df2.to_excel(writer, sheet_name='Sheet2')

Output: An XLSX file named ‘multiple_sheets.xlsx’ with two sheets containing the respective DataFrames’ data.

This example demonstrates how to write different DataFrames to separate sheets within the same XLSX file using pandas and the ExcelWriter context manager. The two sheets, named ‘Sheet1’ and ‘Sheet2’, will contain the data from df1 and df2.

Method 3: Applying Formatting to the XLSX File

Within the ExcelWriter context, pandas also allows for significant amounts of formatting to be applied to the Excel file being created. This can include setting column formats, adding filters, and freezing panes.

Here’s an example:

import pandas as pd

# DataFrame with sales data
sales_data = pd.DataFrame({
    'Date': ['2021-01-01', '2021-01-02', '2021-01-03'],
    'Revenue': [200, 150, 300],
    'Product': ['Widget', 'Gizmo', 'Widget']
})

# Using ExcelWriter and XlsxWriter
with pd.ExcelWriter('formatted_sales_data.xlsx') as writer:
    sales_data.to_excel(writer, index=False)
    worksheet = writer.sheets['Sheet1']
    # Apply a number format to the Revenue column
    number_format = writer.book.add_format({'num_format': '#,##0.00'})
    worksheet.set_column('B:B', None, number_format)

Output: An XLSX file named ‘formatted_sales_data.xlsx’ with formatted sales data.

This snippet prepares a DataFrame with sales data and exports it into an Excel file with specific number formatting applied to the ‘Revenue’ column. This illustrates how to enhance the readability and professionalism of the exported Excel document.

Method 4: Including Excel Charts

For a more visual presentation, pandas allows to include charts in the XLSX file. This is done through the ExcelWriter object’s integration with XlsxWriter.

Here’s an example:

import pandas as pd

# Simple DataFrame
df = pd.DataFrame({
    'Categories': ['Category A', 'Category B', 'Category C'],
    'Values': [10, 20, 30]
})

# Create a Pandas Excel writer using XlsxWriter as the engine
with pd.ExcelWriter('chart.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='Sheet1', index=False)
    workbook  = writer.book
    worksheet = writer.sheets['Sheet1']

    # Create a chart object
    chart = workbook.add_chart({'type': 'column'})
    chart.add_series({
        'values': '=Sheet1!$B$2:$B$4',
        'categories': '=Sheet1!$A$2:$A$4'
    })

    worksheet.insert_chart('D2', chart)

Output: An XLSX file named ‘chart.xlsx’ containing a DataFrame and an embedded column chart.

In this example, a pandas DataFrame is exported with an accompanying column chart, enhancing the workbook with a graphical representation of the data contained within the DataFrame.

Bonus One-Liner Method 5: Quick Export Without Index

For an extremely concise way to export a pandas DataFrame to an XLSX file without including the DataFrame index, you can use a simple one-liner call to the to_excel().

Here’s an example:

pd.DataFrame({'Data': [5, 10, 15]}).to_excel('quick_export.xlsx', index=False)

Output: An XLSX file named ‘quick_export.xlsx’ containing the DataFrame data without an index.

This one-liner command generates a lightweight DataFrame and exports it directly to an Excel file, discarding the index to keep the output tidy and focusing only on the data content.

Summary/Discussion

  • Method 1: to_excel() Method. Simple and straightforward, supports basic needs. May not handle more complex scenarios with multiple DataFrames or specialized formatting.
  • Method 2: Exporting Multiple DataFrames. Ideal for consolidating different datasets in one document. Requires some familiarity with ExcelWriter.
  • Method 3: Formatting Excel Output. Offers excellent presentation capabilities. More complex and requires understanding of Excel formatting options within pandas.
  • Method 4: Including Excel Charts. Great for adding a visual element to data. Involves additional steps and a deeper understanding of charting capabilities in Excel.
  • Bonus Method 5: Quick Export One-Liner. The fastest way to get results, best for on-the-fly exporting. Limited by its simplicity and lack of options.