Read the sheet name which contains specific string as sheet name from multiple files and concatenate in pandas

Question

I have multiple files which are in the form of xlsx and which contain multiple sheets.

I want to read only those sheets which contain "video games" as the sheet name and append it.

Ex: In 1st file among multiple files 1 of the sheet name is 2.Video Games, in another file sheet is 4.VIDEO GAMES, in another file sheet name as video games etc. And all these sheets needs to be considered.

How to write code in pandas which reads the sheet name which contains specific string as sheet name from multiple files and concatenate/append those sheets into 1 sheet.

Answer 1

You'll have to read each excel file individually. But if all the files you need are in the same folder you can then just list the all items/files in that folder and use a for loop to loop through the file names and open them using the os module:

import os
import pandas as pd

output = pd.DataFrame()
for files in os.listdir('path to folder where your excel files are'): 
    data = pd.ExcelFile(files)
    names = data.sheet_names
    for name in names:
        if ('video games' in name.lower()):
            df = data.parse(name)
            output = pd.concat([output, df], ignore_index = True)
output.to_excel('output.xlsx')

This should work for your intended problem.