Exporting Two DataFrames as One Excel File with Multiple Sheets Using Pandas

In this article, we will discuss how to export two dataframes as one excel file with multiple sheets using pandas. This is a common requirement when working with data analysis and visualization tasks.

Introduction

Pandas is a powerful library in Python for data manipulation and analysis. It provides an efficient way to handle structured data, including tabular data such as spreadsheets and SQL tables. One of the key features of pandas is its ability to export data to various file formats, including Excel files.

In this article, we will explore how to use the pandas library to export two dataframes as one excel file with multiple sheets. We will also discuss common errors that may occur during this process and provide solutions for them.

Prerequisites

Before proceeding with this tutorial, make sure you have the following prerequisites:

Python 3.x installed on your system
The pandas library installed (pip install pandas)
The os module installed (which is a standard Python module)

Creating Two Dataframes

To begin, let’s create two sample dataframes that we will use for our example.

import pandas as pd

# Create the first dataframe
data1 = {
    'Date': ['2020-02-01', '2020-02-02', '2020-02-03', '2020-02-04', '2020-02-05',
             '2020-02-06', '2020-02-07', '2020-02-08', '2020-02-09', '2020-02-10',
             '2020-02-11', '2020-02-12', '2020-02-13', '2020-02-14', '2020-02-15',
             '2020-02-16', '2020-02-17', '2020-02-18', '2020-02-19', '2020-02-20',
             '2020-02-21', '2020-02-22', '2020-02-23', '2020-02-24', '2020-02-25',
             '2020-02-26', '2020-02-27'],
    't_factor': [5, 23, 14, 23, 23, 23, 30, 29, 100, 38, 38, 38, 70, 70, 38,
                 38, 70, 70, 38, 70, 70, 70],
    'plan': ['NaN', 'NaN', 'start', 'start', 'start', 'NaN', 'foundation',
             'foundation', 'NaN', 'learn', 'learn', 'learn', 'NaN', 'practice',
             'NaN', 'NaN', 'exam', 'exam', 'exam', 'NaN', 'test', 'test', 'test',
             'NaN', 'NaN', 'NaN'],
    'plan_score': [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 20, 25,
                   10, 20, 30, 10, 10, 10]
}

df1 = pd.DataFrame(data1)

# Create the second dataframe
data2 = {
    'From': ['2020-02-03', '2020-02-07', '2020-02-10', '2020-02-14', '2020-02-15',
             '2020-02-20'],
    'to': ['2020-02-05', '2020-02-08', '2020-02-12', '2020-02-16', '2020-02-21',
          '2020-02-23'],
    'plan': ['start', 'foundation', 'learn', 'practice', 'exam', 'test'],
    'score': [20, 25, 10, 20, 30, 10]
}

df2 = pd.DataFrame(data2)

Exporting the Dataframes as One Excel File with Multiple Sheets

Now that we have our two dataframes, let’s export them as one excel file with multiple sheets using pandas.

import pandas as pd
import os

def save_xls(list_dfs, xls_path):
    with pd.ExcelWriter(xls_path) as writer:
        for n, df in enumerate(list_dfs):
            df.to_excel(writer, 'Sheet' + str(n+1))
    writer.save()

# Call the function with our dataframes
save_xls([df1, df2], os.getcwd() + '/data.xlsx')

This code defines a function save_xls that takes a list of dataframes and an excel file path as arguments. It uses the pandas library to create an Excel writer object, which is then used to write each dataframe to a separate sheet in the excel file.

Troubleshooting Common Errors

Here are some common errors that may occur during this process, along with their solutions:

No engine for filetype: ‘’
This error occurs when pandas is unable to determine which engine to use to create an Excel writer. To solve this issue, make sure you have the openpyxl library installed (pip install openpyxl). You can also specify a default engine using the engine='openpyxl' argument in the pd.ExcelWriter constructor.
OptionError: “No such keys(s): ‘io.excel..writer’”
This error occurs when pandas is unable to determine which option to use for the Excel writer. To solve this issue, make sure you have installed the openpyxl library and that it is compatible with your version of pandas.

Conclusion

In this article, we discussed how to export two dataframes as one excel file with multiple sheets using pandas. We also covered some common errors that may occur during this process and provided solutions for them. By following these steps and troubleshooting tips, you should be able to successfully export your dataframes as an Excel file with multiple sheets.

Step-by-Step Solution

Here is the step-by-step solution:

Import the necessary libraries: pandas, os.
Create two sample dataframes using pandas.
Define a function save_xls that takes a list of dataframes and an excel file path as arguments.
Use the pd.ExcelWriter constructor to create an Excel writer object.
Call the function with our dataframes and specify the excel file path.

I hope this tutorial helps you with exporting two dataframes as one excel file with multiple sheets using pandas.

Last modified on 2023-09-20