How to obtain the mean of selected columns from multiple sheets within same Excel File

I am working with a large excel file having 22 sheets, where each sheet has the same coulmn headings but do not have equal number of rows. I would like to obtain the mean values (excluding zeros) for columns AA to AX for all the 22 sheets. The columns have titles which I use in my code.

Rather than reading each sheet, I want to loop through the sheets and get as output the mean values. With help from answers to other posts, I have this:

import pandas as pd

xls = pd.ExcelFile('Initial starting prices county_BeWhere.xlsx')
xls.sheet_names
#print(xls.sheet_names)
out_df = pd.DataFrame()

for sheets in xls.sheet_names:
    df = pd.read_excel('Initial starting prices county_BeWhere.xlsx', sheet_names= None)
    df1= df[df[:]!=0]
    df2=df1.loc[:,'Sawlogs final felling.1':'Pellets/refined wood.1'].mean()
    out_df.append(df2)  ## This will append rows of one dataframe to another(just like your expected output)

print(out_df2)

## out_df will have data from all the sheets

The code works so far, but only one of the sheets. How do I get it to work for all 22 sheets?



Read more here: https://stackoverflow.com/questions/64891946/how-to-obtain-the-mean-of-selected-columns-from-multiple-sheets-within-same-exce

Content Attribution

This content was originally published by Neddy at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: