This tutorial shows how you can use read_excel() read Excel files with multiple sheets. 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