如何在Pandas透视表中添加item_group维度的分组总计行

原始数据 #

item_group item_code  total_qty  total_amount        cost_center
0           Drink   IC06-1P          1         3.902  Cafe II
1           Drink    IC09-1          1         2.927  Cafe II 
2       BreakFast    FS04-2          1         6.463  Cafe II
3           Drink    IC08-1          1         2.927  Cafe II
4           Drink    DT05-1          1         2.561  Cafe II
..            ...       ...        ...           ...                ...
79  Standard Food    FS01-2         12        83.412  Cafe II
80  Standard Food    FS01-1         13       101.465  Cafe II
81          Drink    IC05-1         14        54.628   Cafe I
82  Standard Food    FS01-2         35       243.285   Cafe I
83  Standard Food    FS01-1         44       343.420   Cafe I

已实现代码 #

# 导入pandas库
import pandas as pd

# 读取CSV文件
df = pd.read_csv("data.csv")
pd.set_option('display.max_rows', None)
pd.set_option('display.max_columns', None)

# 生成透视表并调整列层级
df1 = df.pivot_table(index=['item_group','item_code'],
                    columns=['cost_center'],
                    values=['total_qty','total_amount'],fill_value=0,
                    aggfunc='sum').swaplevel(axis=1).sort_index(level=0, axis=1)
    
# 添加总计列
out = df1.join(pd.concat({'total': df1.groupby(axis=1, level=1).sum()}, axis=1))
print(out)

当前输出结果 #

cost_center             Cafe I - 103cafe            ...        total          
                            total_amount total_qty  ... total_amount total_qty
item_group    item_code                             ...                       
Add Food      AD001                1.952         4  ...        1.952       4.0
              AD002                0.976         2  ...        0.976       2.0
              AD003                1.952         4  ...        1.952       4.0
              AD004                5.000         5  ...        5.000       5.0
              AD007                0.976         2  ...        0.976       2.0
...                                  ...       ...  ...          ...       ...
Standard Food FS01-2             243.285        35  ...      326.697      47.0
              FS01-2P             20.853         3  ...       27.804       4.0
              FS01-6               6.951         1  ...        6.951       1.0
              FS02-1              19.389         3  ...       25.852       4.0
              FS02-2               6.463         1  ...       25.852       4.0

需求 #

需要在每个item_group分组的末尾添加->group_total行，汇总该分组下所有item_code的total_amount和total_qty数据，期望效果如下：

cost_center                       Cafe I                   Cafe II \...
                            total_amount total_qty      total_amount   
item_group    item_code                                                
Add Food      AD001                1.952         4             0.000   
              AD002                0.976         2             0.000   
->group_total                      2.928         6             0.000
BreakFast     FN10-1               5.000         1             0.000   
              FN10-1P              5.000         1             0.000   
->group_total                      10.00         2             0.000

解决方案 #

通过分组计算汇总、调整索引、合并排序三个步骤实现：

import pandas as pd

df = pd.read_csv("data.csv")
pd.set_option('display.max_rows', None)
pd.set_option('display.max_columns', None)

# 生成透视表并调整列层级
df1 = df.pivot_table(index=['item_group','item_code'],
                    columns=['cost_center'],
                    values=['total_qty','total_amount'],fill_value=0,
                    aggfunc='sum').swaplevel(axis=1).sort_index(level=0, axis=1)

# 添加总计列
out = df1.join(pd.concat({'total': df1.groupby(axis=1, level=1).sum()}, axis=1))

# 按item_group分组计算汇总值
group_totals = out.groupby(level='item_group').sum()
# 调整汇总行的索引格式，设置item_code为->group_total
group_totals.index = pd.MultiIndex.from_tuples(
    [(group, '->group_total') for group in group_totals.index],
    names=['item_group', 'item_code']
)

# 合并原始数据与汇总行，排序确保汇总行在对应分组末尾
final_out = pd.concat([out, group_totals]).sort_index(level=['item_group', 'item_code'], sort_remaining=False)

print(final_out)

代码说明 #

1. groupby(level='item_group').sum()：按item_group层级汇总所有数值列的总和
2. 重新构造MultiIndex：将每个分组的汇总行item_code设置为->group_total，保持索引结构与原始数据一致
3. pd.concat+sort_index：合并后排序，sort_remaining=False保证同一item_group下的原始行在前，汇总行在后（字符串排序中->group_total会排在普通编码之后）

内容的提问来源于stack exchange，提问作者tree em