How to convert Python Pandas DataFrame to Excel [6 Examples]

Introduction

Pandas is a powerful data manipulation library in Python, widely used for handling data in tabular form. Exporting Pandas DataFrames to Excel is a common task, and in this article, we will explore various methods and scenarios to achieve this.

Prerequisites

Before we dive in, ensure you have the following:

• Python installed on your system
• Pandas library (pip install pandas)
• Openpyxl library (pip install openpyxl) for handling Excel files

Examples explaining Pandas Dataframe to Excel [6 Examples]

Example 1: Basic DataFrame to Excel Export

import pandas as pd

# Creating a sample DataFrame
data = {'Name': ['John', 'Jane', 'Jim', 'Jill'],
        'Age': [30, 25, 35, 28]}
df = pd.DataFrame(data)

# Exporting to Excel
df.to_excel('example1.xlsx', index=False)

Explanation: In this example, we create a basic DataFrame with names and ages. The to_excel method is used to export it to an Excel file named ‘example1.xlsx’. The index=False argument ensures that the index is not included in the Excel file.

Example 2: Export Multiple DataFrames to Different Sheets

Step 1

First, run this command.

pip install xlsxwriter

Step 2

import pandas as pd
from xlsxwriter import Workbook

# Creating sample DataFrames
data1 = {'Name': ['John', 'Jane', 'Jim', 'Jill'],
         'Age': [30, 25, 35, 28]}
data2 = {'City': ['New York', 'San Francisco', 'Chicago', 'Boston'],
         'Population': [8175133, 884363, 2716000, 691729]}

df1 = pd.DataFrame(data1)
df2 = pd.DataFrame(data2)

# Exporting to Excel with multiple sheets
with pd.ExcelWriter('example2.xlsx', engine='xlsxwriter') as writer:
    df1.to_excel(writer, sheet_name='Sheet1', index=False)
    df2.to_excel(writer, sheet_name='Sheet2', index=False)

Explanation: This example demonstrates how to export multiple DataFrames into a single Excel file with different sheets. We use the ExcelWriter class from the pandas library to manage the writing process. The sheet_name parameter specifies the sheet name for each DataFrame.

Output: This will generate a file named ‘example2.xlsx’ with two sheets, ‘Sheet1’ and ‘Sheet2’, each containing its respective DataFrame.

Example 3: Handling Date Formats

import pandas as pd
from datetime import datetime

# Creating a sample DataFrame with dates
data = {'Date': [datetime(2023, 10, 1), datetime(2023, 10, 2), datetime(2023, 10, 3)],
        'Value': [100, 200, 300]}

df = pd.DataFrame(data)

# Exporting to Excel with date format
df['Date'] = df['Date'].dt.strftime('%d-%m-%Y')  # Formatting date
df.to_excel('example3.xlsx', index=False)

Explanation: In this example, we create a DataFrame with dates. To handle date formatting, we convert the date column using dt.strftime() to the desired format (‘%d-%m-%Y’ in this case). This ensures that the dates are exported in the specified format.

Output: The resulting Excel file ‘example3.xlsx’ will have dates formatted as ‘dd-mm-yyyy’.

Example 4: Exporting Specific Columns

# Creating a sample DataFrame
data = {'Name': ['John', 'Jane', 'Jim', 'Jill'],
        'Age': [30, 25, 35, 28],
        'City': ['New York', 'San Francisco', 'Chicago', 'Boston']}

df = pd.DataFrame(data)

# Exporting specific columns to Excel
df[['Name', 'City']].to_excel('example4.xlsx', index=False)

Explanation: This example showcases how to export specific columns from a DataFrame to an Excel file. We use double square brackets ([['Name', 'City']]) to select the desired columns.

Output: The resulting Excel file ‘example4.xlsx’ will contain only the ‘Name’ and ‘City’ columns.

Example 5: Exporting with Conditional Formatting

# Creating a sample DataFrame
data = {'Value': [10, 20, 30, 40, 50]}
df = pd.DataFrame(data)

# Adding conditional formatting
writer = pd.ExcelWriter('example6.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', index=False)
workbook = writer.book
worksheet = writer.sheets['Sheet1']
# Add a conditional format rule to highlight cells greater than 25
format1 = workbook.add_format({'bg_color': '#FFC7CE',
                               'font_color': '#9C0006'})
worksheet.conditional_format('B2:B6', {'type': 'cell',
                                       'criteria': '>',
                                       'value':  25,
                                       'format': format1})

Explanation:

This example demonstrates how to apply conditional formatting to an Excel file using Pandas and the xlsxwriter engine.

1. DataFrame Creation:
   • A sample DataFrame is created with a single column named ‘Value’ containing five integer values.
2. Excel Writer Initialization:
   • An Excel writer (writer) is set up to create a new Excel file named ‘example6.xlsx’ using the xlsxwriter engine.
3. DataFrame Export:
   • The DataFrame is exported to a sheet named ‘Sheet1’ in the Excel file. The DataFrame’s index is excluded from the export.
4. Workbook and Worksheet Access:
   • Access to the workbook and worksheet objects within the Excel file is obtained.
5. Conditional Formatting Definition:
   • A conditional format rule (format1) is defined. This rule specifies that if a condition is met, the cell background color will be light red (#FFC7CE) and the font color will be a dark red (#9C0006).
6. Conditional Formatting Application:
   • The conditional format rule (format1) is applied to a specified range of cells in column ‘B’. The condition is set to highlight cells where the value is greater than 25.

The outcome is an Excel file named ‘example6.xlsx’ with conditional formatting applied to cells in column ‘B’ based on the specified condition.

Output: The Excel file ‘example6.xlsx’ will be created. Check its data.

Example 6: Exporting Large DataFrames with Chunking

import pandas as pd
import numpy as np

# Creating a large sample DataFrame
data = {'Value': range(1, 10001)}
df = pd.DataFrame(data)

# Exporting in chunks
with pd.ExcelWriter('example7.xlsx', engine='xlsxwriter') as writer:
    for i, chunk in enumerate(np.array_split(df, 10)):
        chunk.to_excel(writer, sheet_name=f'Sheet_{i+1}', index=False)

Explanation: When dealing with large DataFrames, it’s advisable to export them in chunks. In this example, we create a large DataFrame and split it into ten chunks. Each chunk is then exported to a separate sheet in the Excel file.

Output: The resulting ‘example7.xlsx’ will have ten sheets, each containing a portion of the original DataFrame.

Conclusion

Exporting Pandas DataFrame to Excel is a fundamental skill for any data scientist or analyst. With the examples provided in this article, you should be well-equipped to handle a variety of scenarios, from basic exports to more complex tasks like conditional formatting and chunking for large datasets. Experiment with these examples to further solidify your understanding and enhance your data manipulation capabilities.

Do visit my other articles on Pandas Dataframe as well for more detailed information. Thank you for reading it.