pd.ExcelFile to refer to all sheets-CodePudding

I've got a xlsx file with a number of sheets that I'd like the code to refer to but I can't seem to get this to work

xlsx = pd.ExcelFile('Path to xlsx file')
xlsx.sheet_names
df1 = pd.read_excel('file name.xlsx', sheet_name='name')
df2 = pd.read_excel('file name.xlsx', sheet_name='name')
df3 = pd.read_excel('file name.xlsx', sheet_name='name')
df4 = pd.read_excel('file name.xlsx', sheet_name='name')
df5 = pd.read_excel('file name.xlsx', sheet_name='name')

I'm still pretty new to Pandas and Python so sorry if this seems like a silly question. I've also already read the documentation but can't seem to figure it out. Thanks in advance.

CodePudding user response：

Use a list of dataframes.

xls = pd.ExcelFile('Path to xlsx file')
dfs=[]
for sheet in xls.sheet_names:
    dfs.append(pd.read_excel('Path to xlsx file',sheet_name=sheet))

print(dfs[0])
print(dfs[1])

EDIT

A great solution would be to use dict, if you know the name of the sheets, you can call whatever df you like.

xls = pd.ExcelFile('Path to xlsx file')
dfs={}
for sheet in xls.sheet_names:
    dfs[sheet] = pd.read_excel('Path to xlsx file',sheet_name=sheet)

print(dfs['put the name of sheet you want to use'])

CodePudding user response：

This helps:

1.To read all the sheets from an excel file:

df = pd.read_excel('input.xlsx', sheet_name = None)

2.To know the list of sheets from an excel file:

f = pd.ExcelFile('input.xlsx')

f.sheet_names

it gives the list of sheets as a list

['sheet1','sheet2','sheet3']

then iterate over this list to read each sheet

df1 = f.parse(sheet_name = f[0])
df2 = f.parse(sheet_name = f[1])
df3 = f.parse(sheet_name = f[2])