CODEWITHSUNDEEP
pythonpandasdataframe
Debayan
Debayan

Reputation: 854

Read excel sheet in pandas with different sheet names in a pattern

I am trying to read multiple excel files in a loop using read_excel :

Different excel files contain sheet names which contain the word "staff" eg Staff_2013 , Staff_list etc

Is there a way to read all these files dynamically using some wild card concept ?

Something like the code below :

df = pd.read_excel(folder,col_names=True,sheet_name='Staff*')

Upvotes: 1

Views: 5253

Answers (1)

rjg
rjg

Reputation: 579

You can list the sheets and select the ones you want to read one by one.

For instance:

xls_file = pd.ExcelFile('my_excel_file.xls')
staff_fnames = [sheet for sheet in xls.sheet_names if sheet.startswith('Staff')]
for staff_fname in staff_fnames:
    df = pd.read_excel('my_excel_file.xls'), sheet_name=staff_fname)

Or, if you don't mind loading all the sheets, you can also use sheet_name=None to load all sheets in a dict and filter afterwards:

dfs_dict = pd.read_excel('my_excel_file.xls', sheet_name=None)
dfs_dict = {s: df for s, df in dfs_dict.items() if s.startswith('Staff')}

Upvotes: 3

Related Questions