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.

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

If You’re in Hurry…

Using the pandas library, you can use the sheet_name parameter to read excel with multiple sheets.

Multiple sheets will be read as a dictionary of dataframes.

Code

import pandas as pd

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

df

If You Want to Understand Details, Read on…

While reading excel files, you may want to read multiple sheets from it and also, you may wish to handle header rows and so on.

You’ll learn how to read multiple sheets using the sheet_name parameter.

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.

You can do this by using the sheet_name parameter.

This is an optional parameter.

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

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

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 below code demonstrates how to read multiple files using their sheet names.

The 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

The below code demonstrates how to use the Openpyxl engine to read .xlsx files.

You need to use this when using the pandas version earlier 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}

Conclusion

You’ve learned how to Read Excel with Multiple Sheets in Pandas using the read_excel() method.

Also learned how to use the sheet_name parameter to accept the sheet index or the sheet names.

You May Also Like

• 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