5 Best Ways to Convert Pandas DataFrame to Excel in Python

by

💡 Problem Formulation: Python users often need to export datasets for non-technical stakeholders who prefer Excel spreadsheets. This article demonstrates how to convert a Pandas DataFrame, a primary data structure in Python for data analysis, into an Excel file. We will start with a DataFrame containing sales data and show how to output this information into an Excel-readable format.

Method 1: Using DataFrame.to_excel()

The to_excel() method of the Pandas DataFrame is the most straightforward way to export data to an Excel file. It allows for various customizations, including specifying the sheet name and whether to include the index.

Here’s an example:

import pandas as pd

# Sample DataFrame
df = pd.DataFrame({'Name': ['Apple', 'Banana', 'Cherry'],
                   'Quantity': [10, 20, 15],
                   'Price': [1.50, 0.50, 2.00]})

# Export to Excel
df.to_excel('sales_data.xlsx', index=False, sheet_name='Sales')

The output is an Excel file named “sales_data.xlsx” with a sheet titled “Sales” that contains our DataFrame data.

This method is highly efficient for quickly saving a DataFrame to an Excel file without much hassle. The example above creates an Excel workbook with one sheet named ‘Sales’, excludes the DataFrame’s index, and saves it to the current working directory.

Method 2: Specifying Columns

When exporting a DataFrame to Excel, it may be necessary to specify which columns to include. The to_excel() method can accept a list of column names to export only the desired columns.

Here’s an example:

df.to_excel('selected_columns.xlsx', columns=['Name', 'Price'], index=False)

The output is an Excel file named “selected_columns.xlsx” that includes only the ‘Name’ and ‘Price’ columns from our DataFrame.

This approach is beneficial when dealing with DataFrames containing irrelevant or sensitive data that should not be exported. By specifying columns, we can easily filter out the data that is not needed in the output file.

Method 3: Multiple Sheets

For complex data that requires organization into multiple sheets within a single Excel workbook, Pandas can use the ExcelWriter object. This method allows multiple DataFrames to be written to different sheets.

Here’s an example:

with pd.ExcelWriter('multiple_sheets.xlsx') as writer:
    df.to_excel(writer, sheet_name='Sheet1', index=False)
    df.to_excel(writer, sheet_name='Sheet2', index=False)

This results in an Excel file with two sheets, “Sheet1” and “Sheet2,” each containing the DataFrame’s data.

The ExcelWriter context manager ensures that the workbook is saved properly, preventing file corruption. Multiple sheets are created with the same DataFrame for illustration, but in actual use, you would export different DataFrames.

Method 4: Formatting the Excel Output

Applying custom formatting to the Excel output, such as setting column widths or header styles, can be done through the ExcelWriter object combined with Pandas’ DataFrame styling.

Here’s an example:

with pd.ExcelWriter('formatted_output.xlsx') as writer:
    df.to_excel(writer, index=False)
    workbook  = writer.book
    worksheet = writer.sheets['Sheet1']
    
    # Set the format
    header_format = workbook.add_format({'bold': True, 'text_wrap': True})
    worksheet.set_row(0, None, header_format)

The output is an Excel file where the first row (the header) of ‘Sheet1’ is formatted to be bold and have wrapped text.

This snippet illustrates how to apply basic formatting to Excel exports from Pandas. This allows for greater control over the final appearance of the data when viewed in Excel.

Bonus One-Liner Method 5: Direct Conversion with Index

Sometimes, keeping the DataFrame index while exporting can be useful, especially if it contains meaningful information. Pandas’ to_excel() allows this with minimal code.

Here’s an example:

df.to_excel('with_index.xlsx', sheet_name='Data')

The output is an Excel file named “with_index.xlsx” that includes the DataFrame’s index.

This one-liner is a quick way to export a DataFrame to Excel while preserving the index. It’s useful when the index holds relevant data such as unique identifiers.

Summary/Discussion

  • Method 1: Using DataFrame.to_excel() The most straightforward method. Strengths: Simple and direct. Weaknesses: Limited customization in this form.
  • Method 2: Specifying Columns Allows selective data export. Strengths: Export only necessary data, maintain data privacy. Weaknesses: Requires additional step of specifying columns.
  • Method 3: Multiple Sheets Useful for complex data structures. Strengths: Organizes data into multiple sheets in one workbook. Weaknesses: Slightly more complex syntax.
  • Method 4: Formatting the Excel Output Provides Excel-specific formatting options. Strengths: Increased control over output appearance. Weaknesses: More complex, requires understanding of ExcelWriter and formatting.
  • Method 5: Direct Conversion with Index Keeps DataFrame’s index in the output. Strengths: Preserves index information. Weaknesses: Might not always be necessary or desired.