pandas get sheet names contains a keyword

I want to combine all sheets that have the string ‘BOOKING’ in them. I’ve already looked for such documents, but there aren’t any available samples. Can somebody help me? I only know how to read the name of a given sheet.

import pandas as pd

file = 'test.xlsx'

df = pd.read_excel(File,sheet_name = 'JANUARY BOOKING')

print(df)

Answer

You can try this:

import pandas as pd


file = 'test.xlsx'
keyword = 'BOOKING'

xls_file = pd.ExcelFile(file)
sheet_names = [sheet for sheet in xls_file.sheet_names if keyword.upper() in sheet.upper()]

dfs = pd.read_excel(file, sheet_name = sheet_names)
df = pd.concat([df.assign(sheet_name=name) for name, df in dfs.items()])

print(df)

Output:

enter image description here

Make sure have openpyxl installed in your environment. If don’t then use this command pip install openpyxl