影刀RPA Excel数据透视表分组统计基础作者林焱什么情况用什么有一张几万行的销售明细表需要按地区统计销售额、按月份统计订单数、按产品类别统计占比——手动做透视表每次都要重新拖拽。在影刀RPA里用pandas的pivot_table可以自动完成分组统计比Excel透视表更灵活、更快还能批量处理多个文件。适用场景销售数据按地区/产品/时间汇总、考勤数据按部门统计天数、财务数据按科目汇总金额、多维度交叉分析。怎么做基础分组统计importpandasaspd# 读取销售明细dfpd.read_excel(rC:\Data\sales_detail.xlsx)# 按地区统计销售额和订单数resultdf.groupby(地区).agg(订单数(订单ID,count),[video(video-dTWvvdps-1783501587232)(type-csdn)(url-https://live.csdn.net/v/embed/525000)(image-https://v-blog.csdnimg.cn/asset/23da3fe1f67a47106d725406cfde9a97/cover/Cover0.jpg)(title-拼多多店群自动化上架方案)]总金额(金额,sum),平均金额(金额,mean),最大金额(金额,max)).reset_index()result.to_excel(rC:\Data\sales_by_region.xlsx,indexFalse)透视表交叉分析# 按地区行× 产品类别列交叉统计销售额pivotpd.pivot_table(df,values金额,# 要统计的值index地区,# 行分组columns产品类别,# 列分组aggfuncsum,# 聚合方式fill_value0,# 空值填充0marginsTrue,# 添加合计行/列margins_name总计)pivot.to_excel(rC:\Data\sales_pivot.xlsx)多维度分组# 按地区月份产品类别三级分组df[月份]pd.to_datetime(df[日期]).dt.to_period(M).astype(str)resultdf.groupby([地区,月份,产品类别]).agg(订单数(订单ID,count),总金额(金额,sum)).reset_index()# 生成多层表头Excelresult.to_excel(rC:\Data\multi_dim_report.xlsx,indexFalse)在影刀RPA中的完整流程1. 【打开Excel文件】→ 读取销售明细 2. 【执行Python代码】→ pandas分组统计 3. 【执行Python代码】→ 生成透视表 4. 【写入Excel文件】→ 保存结果 5. 【发送邮件】→ 自动发送报表完整代码importpandasaspdimportopenpyxlfromopenpyxl.stylesimportFont,PatternFill,Alignment,Border,Sidedefgenerate_pivot_report(input_path,output_path):生成数据透视报表# 1. 读取数据dfpd.read_excel(input_path)# 2. 数据预处理df[日期]pd.to_datetime(df[日期],errorscoerce)df[月份]df[日期].dt.to_period(M).astype(str)df[金额]pd.to_numeric(df[金额],errorscoerce).fillna(0)# 3. 生成多个统计表withpd.ExcelWriter(output_path,engineopenpyxl)aswriter:# 表1按地区统计by_regiondf.groupby(地区).agg(订单数(订单ID,count),总金额(金额,sum),平均金额(金额,mean)).round(2).reset_index()by_region.to_excel(writer,sheet_name按地区统计,indexFalse)# 表2按月份统计趋势by_monthdf.groupby(月份).agg(订单数(订单ID,count),总金额(金额,sum)).reset_index()by_month.to_excel(writer,sheet_name按月趋势,indexFalse)# 表3交叉透视表pivotpd.pivot_table(df,values金额,index地区,columns产品类别,aggfuncsum,fill_value0,marginsTrue,margins_name合计)pivot.to_excel(writer,sheet_name交叉分析)# 表4TOP10产品top10df.groupby(产品名称).agg(总金额(金额,sum),订单数(订单ID,count)).sort_values(总金额,ascendingFalse).head(10).reset_index()top10.to_excel(writer,sheet_nameTOP10产品,indexFalse)# 4. 美化格式wbopenpyxl.load_workbook(output_path)forwsinwb.worksheets:# 表头样式forcellinws[1]:cell.fontFont(name微软雅黑,size11,boldTrue,colorFFFFFF)cell.fillPatternFill(start_color1F4E79,end_color1F4E79,fill_typesolid)cell.alignmentAlignment(horizontalcenter,verticalcenter)# 列宽自适应forcolinws.columns:max_lengthmax(len(str(cell.valueor))forcellincol)ws.column_dimensions[col[0].column_letter].widthmin(max_length4,30)wb.save(output_path)returnoutput_path# 在影刀【执行Python代码】节点中调用generate_pivot_report(rC:\Data\sales_detail.xlsx,rC:\Data\sales_report.xlsx)动态分组统计不固定分组字段根据配置文件动态选择defdynamic_groupby(df,group_cols,agg_config): 动态分组统计 agg_config: {列名: 聚合方式} 如 {金额: sum, 订单ID: count} agg_dict{}forcol,funcinagg_config.items():iffunccount:agg_dict[col](col,count)eliffuncsum:agg_dict[col](col,sum)eliffuncmean:agg_dict[col](col,mean)resultdf.groupby(group_cols).agg(**agg_dict).reset_index()returnresult# 使用dfpd.read_excel(rC:\Data\sales.xlsx)resultdynamic_groupby(df,group_cols[地区,产品类别],agg_config{金额:sum,订单ID:count})有什么坑坑1分组后索引变成MultiIndexgroupby多个列后结果的索引变成多层索引直接to_excel格式很乱# 问题多列分组后索引变成MultiIndexresultdf.groupby([地区,月份]).agg({金额:sum})# result的索引是(地区, 月份)的MultiIndex# 解决加reset_index()resultdf.groupby([地区,月份]).agg({金额:sum}).reset_index()# 现在地区和月份是普通列坑2agg函数列名混乱TEMU店群如何管理运营聚合后列名变成元组或多层列名不好处理# 问题这种方式生成的列名是(金额, sum)这种多层列名resultdf.groupby(地区).agg({金额:[sum,mean,count]})# 解决用命名聚合pandas 0.25resultdf.groupby(地区).agg(总金额(金额,sum),平均金额(金额,mean),订单数(金额,count)# 或 (订单ID, count)).reset_index()坑3透视表的合计行排序pivot_table加了marginsTrue后合计行排在最后是按字母排序的中文合计可能排到中间# 问题margins_name合计但合计按拼音排序可能不在最后pivotpd.pivot_table(df,values金额,index地区,columns产品,aggfuncsum,fill_value0,marginsTrue,margins_name合计)# 解决生成后手动调整行顺序row_order[rforrinpivot.indexifr!合计][合计]pivotpivot.loc[row_order]坑4日期分组时NaT导致报错日期列有空值时转period会生成NaTgroupby会报错或产生意外分组# 问题日期列有空值df[日期]pd.to_datetime(df[日期],errorscoerce)df[月份]df[日期].dt.to_period(M)# 空值变NaT# groupby时NaT会被忽略但可能导致分组数变少resultdf.groupby(月份).agg({金额:sum})# 解决先处理空值dfdf.dropna(subset[日期])# 删掉日期为空的行# 或者df[月份]df[月份].fillna(未知月份).astype(str)坑5大数据量内存溢出几十万行数据做透视表pandas默认会把所有数据加载到内存可能内存不够# 方案1只读取需要的列dfpd.read_excel(file_path,usecols[地区,产品,金额,日期])# 方案2分块读取后合并chunkspd.read_excel(file_path,chunksize50000)results[]forchunkinchunks:rchunk.groupby(地区).agg({金额:sum})results.append(r)finalpd.concat(results).groupby(level0).sum()# 合并各块的统计结果# 方案3用dtype优化内存dfpd.read_excel(file_path,dtype{地区:category,产品:category})