You can use read_excel(), pd.read_excel(), and pd.ExcelFile() to read Excel files with multiple sheets and this tutorial shows example how to do it.
The following is the illustration for sheet1 and sheet2. They have the same data structure, but in 2 different sheets. You can download the Excel file from Github by clicking this link.
Method 1: Just read the first sheet
Use read_excel() to read the 1st sheet into dataframe. By default, it will ignore other sheets. The following is the Python code example.
# Read the first sheet in the Excel file using read_excel() function in the package Pandas
df = pd.read_excel('Test_sheets.xlsx')
# Print out the dataframe of the first sheet from the Excel file
print(df)Unnamed: 0 Brand Location Year 0 0 Tesla CA 2019 1 1 Tesla CA 2018 2 2 Tesla NY 2020 3 3 Ford MA 2019
Method 2: Read the second sheet in multiple sheets
You can specify sheet name using sheet_name parameter in read_excel(). Thus, we can just read the second sheet, instead of the default first sheet.
# Read the second sheet in Excel as a dataframe:
df_Sheet2 = pd.read_excel('Test_sheets.xlsx', sheet_name="Sheet2")
# Print out the saved sheet
print(df_sheet2)The following is the output of the dataframe of the sheet from Excel file.
Unnamed: 0 Brand Location Year 0 0 Tesla CT 2019 1 1 Tesla CT 2018 2 2 Ford MA 2020 3 3 Ford WA 2019
Method 3: Read all sheets as a dictionary
Step 1: Just save it as a dictionary
You can read all the sheets all together using the parameter of sheet_name=None in pd.read_excel(). However, note that, it will be a dictionary, with sheet names as the keys in the dictionary.
# add Pandas package into the environment
import pandas as pd
# You can use the following Pyton code if Pandas version >= 0.21.0
dic_1 = pd.read_excel('Test_sheets.xlsx', sheet_name=None)
print(dic_1)
{'Sheet1':
Unnamed: 0 Brand Location Year
0 0 Tesla CA 2019
1 1 Tesla CA 2018
2 2 Tesla NY 2020
3 3 Ford MA 2019,
'Sheet2':
Unnamed: 0 Brand Location Year
0 0 Tesla CT 2019
1 1 Tesla CT 2018
2 2 Ford MA 2020
3 3 Ford WA 2019}
To check the data type, we can use the function of type() to do it. Based on the output below, Awe can see, it is a dictionary.
# Python code to check the data type of the saved file from read_excel()
print(type(dic_1))Further, the following is the output, which shows that it is a dictionary.
<class 'dict'>
Step 2: Save one sheet in the dictionary as a dataframe
You can actually save one of the sheets in the dictionary as a dataframe by indexing the key in a dictionary. For instance, you can select the key of “Sheet1” and save it as a dataframe. The following is the Python code.
# Indexing the key of "Sheet1" in the dictionary and save it as a dataframe:
df_new=pd.DataFrame(dic_1["Sheet1"])
# the following is to print out the saved file.
print(df_new)Unnamed: 0 Brand Location Year 0 0 Tesla CA 2019 1 1 Tesla CA 2018 2 2 Tesla NY 2020 3 3 Ford MA 2019
If Pandas is older than 0.21.0, you should use sheetname rather than sheet_name. pd.read_excel('Test_sheets.xlsx', sheetname=None)
Method 4: Use pd.ExcelFile() to read files
Use pd.ExcelFile() to read all the sheets all together. The following code reads all the XLSX sheets as a file and print out all sheet names.
# the following Python code use ExcelFile() function to read XLSX files:
df = pd.ExcelFile('Test_sheets.xlsx')
# the following code prints out all the names of the sheets
print(df.sheet_names)
The following is the output, which shows the sheets names from the Excel XLSX file.
['Sheet1', 'Sheet2']
We can also use pandas.ExcelFile.parse() to select the sheet. The following is the Python code example. Note that, df is saved from the pd.ExcelFile() function shown above.
# selected the first sheet using parse() function
df_Sheet1 = df.parse(0)
# print out the first sheet of the Excel XLSX file
print(df_Sheet1)The following is the print out of the content of the first sheet in the EXCEL file.
Unnamed: 0 Brand Location Year 0 0 Tesla CA 2019 1 1 Tesla CA 2018 2 2 Tesla NY 2020 3 3 Ford MA 2019