pandas columns

Question

我现在有个excel 里面有20个sheet,想要将这20个sheet 里的指定列对应的值sum 计算出总数
例如
sheet1
class name score price address
01 dou 23 17.5 shanghai
sheet2
address name score gender
beijing lin 60 man
sheet3
company name score email store
google wang 36 yaho open

Answer 1

import pandas as pd

df=pd.read_excel('xxx.xlsx',sheet_name=None)##传递sheet_name=None读取所有的sheet

All_data=pd.DataFrame(columns=['sheet-name','name','score'])
total=0
for name,sheet in df.items():
    total+=sheet['score'].sum()
    newdf=sheet[["name","score"]]
    newdf.insert(0,'sheet-name','')
    newdf.loc[0,'sheet-name']=name
    All_data=pd.concat([All_data,newdf])

All_data.loc[len(All_data.index)] = ['total','',total]

print(All_data)