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?