python - 为 Pandas 数据透视表添加总计和小计
问题描述
df=pd.DataFrame({'Region':['Oceanian','Europe','Asia','America','Europe','America','Asia','Oceanian','America'],'Country':["AU","GB","KR","US","GB","US","KR","AU","US"],'Region Manager':['TL','JS','HN','AL','JS','AL','HN','TL','AL'],'Campaign Stage':['Start','Develop','Develop','Launch','Launch','Start','Start','Launch','Develop'],'Product':['abc','bcd','efg','lkj','fsd','opi','vcx','gtp','qwe'],'Curr_Sales': [453,562,236,636,893,542,125,561,371],'Curr_Revenue':[4530,7668,5975,3568,2349,6776,3046,1111,4852],'Prior_Sales': [235,789,132,220,569,521,131,777,898],'Prior_Revenue':[1530,2668,3975,5668,6349,7776,8046,2111,9852]})
table=pd.pivot_table(df, values=['Curr_Sales', 'Curr_Revenue', 'Prior_Sales', 'Prior_Revenue'], index=['Region','Country', 'Region Manager','Campaign Stage','Product'],aggfunc='sum')
如何将小计添加到每个“区域”并在底部添加总计?
任何想法表示赞赏!谢谢你。
解决方案
与您之前的问题非常相似的解决方案,但是您可以为缺少的级别插入空白字符串(受@piRSquared here启发):
out = pd.concat([d.append(d.sum().rename((k, '', '', '', 'Subtotal'))) for k, d in table.groupby('Region')]).append(table.sum().rename(('Grand', '', '', '', 'Total')))
out.index = pd.MultiIndex.from_tuples(out.index)
产量:
Curr_Revenue ... Prior_Sales
America US AL Develop qwe 4852 ... 898
Launch lkj 3568 ... 220
Start opi 6776 ... 521
Subtotal 15196 ... 1639
Asia KR HN Develop efg 5975 ... 132
Start vcx 3046 ... 131
Subtotal 9021 ... 263
Europe GB JS Develop bcd 7668 ... 789
Launch fsd 2349 ... 569
Subtotal 10017 ... 1358
Oceanian AU TL Launch gtp 1111 ... 777
Start abc 4530 ... 235
Subtotal 5641 ... 1012
Grand Total 39875 ... 4272
推荐阅读
- c++ - C++ 查找已排序字符串中的重复字符
- python - 访问基类变量
- mysql - 在“meta_value”中搜索值时加快 WordPress 查询
- php - Laravel 5.6:创建数据库和几个模式
- javascript - jQuery - 显示/隐藏列表中的项目并显示内容
- java - 找到 findAny 匹配后如何停止并行流?
- javascript - Bootstrap 4标签:获取点击标签的索引号?
- python - 如何生成包含特定条目的 Kaggle 提交 CSV 文件?
- excel-formula - 忽略空白的嵌套 IF 条件
- spring - 春季云流错误通道不起作用