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 sheetlist
– to read multiple sheetsint
– 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}