CODEWITHSUNDEEP
pythonexcelpandas
11l
11l

Reputation: 73

Using Pandas (Python) with Excel to loop through multiple worksheets to return all rows where a value in a list appears in a column

I have a list of values, if they appear in a the column 'Books' I would like that row to be returned. I think I have achieved this with the below code:

def return_Row():
   file = 'TheFile.xls'
   df = pd.read_excel(file)
   listOfValues = ['A','B','C']
   return(df.loc[df['Column'].isin(listOfValues)])

This currently only seems to work on the first Worksheet as there are multiple worksheets in 'TheFile.xls' how would I go about looping through these worksheets to return any rows where the listOfValues is found in the 'Books' column of all the other sheets?

Any help would be greatly appreciated.

Thank you

Upvotes: 0

Views: 852

Answers (1)

Ismail Shaheen
Ismail Shaheen

Reputation: 103

The thing is, pd.read_excel() returns a dataframe for the first sheet only if you didn't specify the sheet_name argument. If you want to get all the sheets in excel file without specifying their names, you can pass None to sheet_name as follows:

df = pd.read_excel(file, sheet_name=None)

This will give you a different dataframe for each sheet on which you can loop and do whatever you want. For example you can append the results that you need to a list and return the list:

def return_Row():
   file = 'TheFile.xls'
   results = []
   dfs = pd.read_excel(file, sheet_name=None)
   listOfValues = ['A','B','C']
   for df in dfs.values():
       results.append(df.loc[df['Column'].isin(listOfValues)])
   return(results)

Upvotes: 1

Related Questions