How to Read Excel Multiple Sheets in Python Pandas

Reading multiple sheets from an Excel file into a Pandas DataFrame is a basic task in data analysis and manipulation. It allows us to work with data spread across different sheets efficiently within the Pandas framework.

Read Excel Multiple Sheets in Pandas

We use the pd.read_excel() function to read the Excel file. The sheet_name=None parameter reads all sheets from the Excel file into a dictionary of DataFrames. If we want to read specific sheets, we can pass a list of sheet names or specify the sheet index. Then, we can access each DataFrame from the dictionary using the sheet name as the key or by iterating through the dictionary.

Syntax:

pd.read_excel(excel_file, sheet_name=None)

How to Read Excel Multiple Sheets in Pandas

Below, are the code examples of how to read Excel multiple sheets in Pandas.

File Structrue

file.xlsx

file2.xlsx

file3.xlsx

Example 1: Read Multiple Files

In this example, below, Python code utilizes the pandas library to read multiple Excel files (file.xlsx, file3.xlsx, and file2.xlsx) containing multiple sheets. It iterates through each file, printing the contents of each sheet within each file, providing a clear separation between different files for better readability.

Python3

# Importing necessary libraries
import pandas as pd

# List of file names
file_names = ['file.xlsx', 'file3.xlsx', 'file2.xlsx']

# Looping through each file
for file_name in file_names:
    print(f"Contents of file '{file_name}':\n")

    # Reading multiple sheets from an Excel file
    sheets_dict = pd.read_excel(file_name, engine="openpyxl", sheet_name=None)

    # Accessing individual sheets and displaying their contents
    for sheet_name, df in sheets_dict.items():
        print(f"Sheet '{sheet_name}':\n{df}\n")

    print("\n")  # Print an extra newline for clarity

Output

Contents of file 'file.xlsx':
   ID  Name
0  11B    A
1  10C    B
2  12D    C

Contents of file 'file3.xlsx':
     ID Name
0  123A  ABC
1  234B  BCD
2  345C  DEF

Contents of file 'file2.xlsx':
     ID Name
0  101A   AB
1  102G   BC
2  104D   CD

Example 2: Read Multiple Sheets and Print Specific One By Its Name

In this example, below code imports the pandas library, then uses it to read all sheets from an Excel file named ‘file.xlsx’ using the openpyxl engine. After reading, it accesses a specific sheet named ‘Sheet1’ from the Excel file and prints its contents.

Python3

# Importing necessary libraries
import pandas as pd

# Reading multiple sheets from an Excel file
sheets_dict = pd.read_excel('file.xlsx', engine="openpyxl", sheet_name=None)

# Accessing a specific sheet by name
specific_sheet = sheets_dict['Sheet1']
print("Contents of 'Sheet1':\n", specific_sheet)

Output

Contents of sheet 'Sheet1':
   ID  Name
0  11B    A
1  10C    B
2  12D    C