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.
Table of Contents
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 sheetlist
– to read multiple sheetsint
– 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.