How to Read Multiple Sheets to Pandas Dataframe

answered 2022-03-31 11:18:06 -0600

jconto
2701 ●10 ●30 ●41

updated 2022-03-31 15:22:54 -0600

From the pandas.read_excel 'online' manual,

sheet_name str, int, list, or None, default 0
Strings are used for sheet names. Integers are used in zero-indexed sheet positions (chart sheets do not count as a sheet position). Lists of strings/integers are used to request multiple sheets. Specify None to get all worksheets.

Available cases:
Defaults to 0: 1st sheet as a DataFrame
1: 2nd sheet as a DataFrame
"Sheet1": Load sheet with name “Sheet1”
[0, 1, "Sheet5"]: Load first, second and sheet named “Sheet5” as a dict of DataFrame
None: All worksheets.

To read a sheet into a dataframe, try the following:

import os    
import pandas as pd
cwd = os.getcwd()
xlsfile = 'file_excel.xlsx'
df = pd.read_excel(os.path.join(cwd,xlsfile), sheet_name = 'Hourly Market Data')
print(df.head())

To read all sheets into a dictionary of dataframe, try the following:

cwd = os.getcwd()
xlsfile = 'file_excel.xlsx'
dict_df = pd.read_excel(os.path.join(cwd,xlsfile), sheet_name = None)
for key in dict_df:
    print(key)
    print(dict_df[key].head())