How to Read Excel with Multiple Sheets in Pandas? – Definitive Guide

Pandas allow you to read excel files with a single line of code.

You can read an multiple sheets excel file in Pandas using the pd.read_excel(“testExcel.xlsx”, sheet_name = [‘MY_Sheet_1’, ‘MY_Sheet_2’]) statement.

Basic Example

• Use the sheet_name parameter to read excel with multiple sheets.
• Multiple sheets will be read as a dictionary of dataframes.

import pandas as pd

df = pd.read_excel(
     "testExcel.xlsx", sheet_name = ['MY_Sheet_1', 'MY_Sheet_2']
)

df

In this tutorial, you’ll learn how to read excel with multiple sheets using the Pandas library.

Using Read_excel() with Sheet Name

In this section, you’ll learn how to read excel files with multiple sheets using the read_excel() method.

• Pass the sheet names using the sheet_name parameter

This parameter accepts different types of inputs.

• String – to read a single sheet
• list – to read multiple sheets
• int – to read sheet based on an index, NOT with the sheet name

This is an optional parameter. If you do not pass this parameter, it’ll default to 0. This means the first sheet of the excel file will be read as a dataframe.

Code

import pandas as pd

df = pd.read_excel(
     "testExcel.xlsx", sheet_name = 'MY_Sheet_1'
)

df

Dataframe Will Look Like

	Column 1	Column 2
0	C1 Row1	C2 Row1
1	C1 Row2	C2 Row2
2	C1 Row3	C2 Row3
3	C1 Row4	C2 Row 4
4	C1 Row5	C2 Row 5

You can select rows from the dataframe to check its data.

Using Read_excel() With Sheet Names

The following code demonstrates how to read multiple files using their sheet names.

• Multiple sheet names are passed as a string array to the sheet_name parameter

Code

import pandas as pd

df = pd.read_excel(
     "testExcel.xlsx", sheet_name = ['MY_Sheet_1', 'MY_Sheet_2']
)

df

The sheets are read as a dictionary of dataframes.

Dataframe Will Look Like

    {'MY_Sheet_1':   Column 1  Column 2
     0  C1 Row1   C2 Row1
     1  C1 Row2   C2 Row2
     2  C1 Row3   C2 Row3
     3  C1 Row4  C2 Row 4
     4  C1 Row5  C2 Row 5,
     'MY_Sheet_2':   Column 1  Column 2
     0  C1 Row1   C2 Row1
     1  C1 Row2   C2 Row2
     2  C1 Row3   C2 Row3
     3  C1 Row4  C2 Row 4
     4  C1 Row5  C2 Row 5}

Once the files are read, you can Rename Column in pandas if you want to use custom column headers.

Read Multiple Sheets in Excel using Openpyxl

Openpyxl engine is an engine that supports reading the excel file in Pandas.

• Use this engine when using the pandas version older than 1.2.0. Otherwise, you’ll face the xlrd.biffh.XLRDError: Excel xlsx file; not supported Error.

Code

import pandas as pd

df = pd.read_excel(
     "testExcel.xlsx", sheet_name = ['MY_Sheet_1', 'MY_Sheet_2'],
     engine='openpyxl'
)

df

Output

    {'MY_Sheet_1':   Column 1  Column 2
     0  C1 Row1   C2 Row1
     1  C1 Row2   C2 Row2
     2  C1 Row3   C2 Row3
     3  C1 Row4  C2 Row 4
     4  C1 Row5  C2 Row 5,
     'MY_Sheet_2':   Column 1  Column 2
     0  C1 Row1   C2 Row1
     1  C1 Row2   C2 Row2
     2  C1 Row3   C2 Row3
     3  C1 Row4  C2 Row 4
     4  C1 Row5  C2 Row 5}

Additional Resources

• How to write pandas dataframe to CSV
• How to Drop Column in pandas dataframe
• How to Iterate over Rows in Pandas Dataframe
• How To Read A Text File Using Pandas