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 1Column 2
0C1 Row1C2 Row1
1C1 Row2C2 Row2
2C1 Row3C2 Row3
3C1 Row4C2 Row 4
4C1 Row5C2 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.

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

Leave a Comment