python 操作excel_Python对Excel的汇总操作

• 需求描述：Python操作Excel表中行和列分别求平均数和总数，保存在新的sheet。
• 代码部分：

#/usr/bin/env python#coding:utf-8import pandas as pdwriter = pd.ExcelWriter('score.xlsx')data = pd.read_excel('score.xlsx', sheet_name='old', encoding='utf-8')# 保存sheetname=olddata.to_excel(writer, sheet_name='old', index=False)#行的平均数(Avg)和总数(Sum)temp1 = data[['Chinese', 'Math', 'English']]data['Avg_row'] = temp1.mean(axis=1)data['Sum_row'] = temp1.sum(axis=1)#列的平均数(Avg)和总数(Sum)temp2 = data[['Chinese', 'Math', 'English', 'Avg_row', 'Sum_row']].mean()temp3 = data[['Chinese', 'Math', 'English', 'Avg_row', 'Sum_row']].sum()temp2['Name'] = 'Avg_col'temp3['Name'] = 'Sum_col'data = data.append(temp2, ignore_index=True)data = data.append(temp3, ignore_index=True)print(data)# 保存sheetname=newdata = pd.DataFrame(data)data.to_excel(writer, sheet_name='new', index=False)#保存并关闭文件writer.save()writer.close()

• 运行结果：