Pandas >> Read Multiple Sheets in an Excel

Table of Contents

This tutorial will explain how to read multiple sheets, worksheets in an Excel, workbook. (pd.read_excel, pd.ExcelFile)

Use pd.read_excel for multiple times

You can use the pandas.read_excel() function to read multiple sheets in a same Excel file. To do this, you will need to specify the sheet names or sheet indices in the sheet_name parameter of the read_excel() function.

Here is an example of how you can use the read_excel() function to read multiple sheets in a same Excel file:

import pandas as pd

# Read multiple sheets from the same Excel file
df1 = pd.read_excel("file.xlsx", sheet_name="Sheet1")
df2 = pd.read_excel("file.xlsx", sheet_name="Sheet2")
df3 = pd.read_excel("file.xlsx", sheet_name="Sheet3")

# Alternatively, you can specify the sheet indices instead of the sheet names
df1 = pd.read_excel("file.xlsx", sheet_name=0)
df2 = pd.read_excel("file.xlsx", sheet_name=1)
df3 = pd.read_excel("file.xlsx", sheet_name=2)

Use pd.read_excel for one time

You can also use the sheet_name parameter to specify a list of sheet names or sheet indices to read all the sheets in the Excel file:

# Read all sheets in the Excel file
df = pd.read_excel("file.xlsx", sheet_name=None)

# df will be a dictionary, where the keys are the sheet names and the values are the corresponding dataframes
print(df.keys())
print(df["Sheet1"])
print(df["Sheet2"])
print(df["Sheet3"])

You can also use the skiprows parameter to skip a certain number of rows at the top of the sheet. This can be useful if the sheet contains some metadata or other information that you don’t want to include in the dataframe.

Use combination of ExcelFile and read_excel

You can use the pd.ExcelFile class in combination with the read_excel() function to read multiple sheets in a same Excel file.

The ExcelFile class can be used to represent an Excel file in memory, and the read_excel() function can be used to read a sheet from the Excel file.

Here is an example of how you can use the ExcelFile class and read_excel() function to read multiple sheets in a same Excel file:

import pandas as pd

xls = pd.ExcelFile('file.xlsx')

df1 = pd.read_excel(xls, 'Sheet1')
df2 = pd.read_excel(xls, 'Sheet2')
df3 = pd.read_excel(xls, 'Sheet3')

df1 = pd.read_excel(xls, sheet_name=0)
df2 = pd.read_excel(xls, sheet_name=1)
df3 = pd.read_excel(xls, sheet_name=2)

Using the ExcelFile class has the advantage of allowing you to keep the Excel file open in memory and read multiple sheets from it without the need to re-open the file for each sheet. This can be more efficient if you need to read multiple sheets from the same Excel file.