How to Read Excel Multiple Sheets in Python Pandas
Last Updated :
26 Mar, 2024
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
Share your thoughts in the comments
Please Login to comment...