用pandas对EXCEL数据进行聚合汇总

 表格处理实际应用之去重累加汇总

1.使用场景

朋友小k有一个如下表1的数据

isbn	title	price	total
7536041526	西游记	22	1
7536041526	西游记	22	1
7536041523	水浒传	31	3

-表1-

他希望将isbn和title相同的数据合并，并累加total，其他列数据不变。

isbn	title	price	total
7536041526	西游记	22	2
7536041523	水浒传	31	3

-表2-

2.实现思路

1).EXCEL直接使用数据透视表功能,但是我不熟总觉的用起来麻烦，略过

2).EXCEL导入mysql数据库，使用sql查询

select *,sum(total) as '总计' from sheet1 group by isbn,title

3).使用duckdb直接读取操作EXCEl，使用是上面的sql查询。ps: sql语句用上面的会报错，需要把*改成anyvalue(isbn),anyvalue(title),anyvaule(price),anyvaule(total)。mysql操作好像是要修改sql_mode那个groupby，duckdb没有找到相关原因分析。

4).pandas的groupby聚合汇总

3.最终实现

ExcelWriter对多sheet表操作写入

import os
import pandas as pd

#原始文件路径
filepath=r'a.xls'
#生成文件路径
respath=r"test2.xlsx"
#格式化书号问文本类型
def func_num(number):
    if isinstance(number, int):
        return number
    if isinstance(number, float):
        number = '%.2f' % number
        number = str(number).rstrip('0')
        number = int(number.rstrip('.')) if number.endswith('.') else float(number)
        return number
#格式定价区间为小数点2位
def func_str(number):
    if isinstance(number, int):
        return str(number)
    else:
        return number

io = pd.io.excel.ExcelFile(filepath)
x1 = pd.ExcelFile(filepath)
all_name = x1.sheet_names
df = pd.read_excel(io, sheet_name=all_name, index_col=None)
writer = pd.ExcelWriter(respath)
# 操作每个sheet页
for sheet in all_name:
    try:
        print(sheet)
        df_new = df[sheet]
        # df_new=pd.read_excel(filepath, index_col=None)
        df_new['total'] = df_new.groupby(['isbn','title'],sort=False)['total'].transform('sum')
        df_new.drop_duplicates(['isbn','title'], inplace=True)

       # df_new['售价'] = df_new['售价'].map(func_num)
        df_new['isbn'] = df_new['isbn'].map(func_str)
        df_new.to_excel(writer,sheet_name=sheet, index=False)
    except KeyError as e:
        pass
writer.save()
writer.close()

参考：

Python中groupby的简单使用