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')
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)


## 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:

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: