Pandas: Read All Sheets in Excel

This tutorial shows how you can read multiple sheets in Excel in Python. The following shows the two major steps.

Step 1: Read all sheets as a dictionary

You can read all sheets in Excel altogether using the parameter of sheet_name=None in pd.read_excel(). Without this, it will only read the first sheet, by default. The basic syntax is as follows.

pd.read_excel(“fiel name”, sheet_name=None)

The following is the Python code.

import pandas as pd
# Read all sheets into a dictionary
dic_1 = pd.read_excel('Test_sheets.xlsx', sheet_name=None)
print(dic_1)

Output:

{'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}

Step 2: Check the structure (optional)

Based on the output shown above, it is a dictionary, with sheet names as the keys in the dictionary. We can confirm that by checking the data type, see below.

# Check the data type of dic_1
print(type(dic_1))

The following is the output. Indeed, it is a dictionary.

<class 'dict'>

Step 3: Save one sheet in the dictionary as a dataframe

You can 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 "Sheet1" and save it as a dataframe.

# Indexing the key of "Sheet1" in the dictionary 
df_new=pd.DataFrame(dic_1["Sheet1"])
print(df_new)

Output:

  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