如何将Pandas多级索引DataFrame重塑为带中间总计的Excel式透视表

要实现类似Excel数据透视表的中间总计（行小计），你可以通过分组统计+拼接结果的方式，手动添加各层级的总计行。以下是针对你的数据的完整解决方案：

1. 修正原始数据（修复字符串换行问题） #

你的原始代码中Sub Products列存在换行导致的字符串错误，先修正：

import pandas as pd

df = pd.DataFrame({
    'Products': ['Products A','Products A', 'Products A','Products B', 'Products B', 'Products A', 'Products B', 'Products A'],
    'Sub Products': ['Phone A','Phone B', 'Laptop B','Phone B', 'Laptop B','Phone A','Phone B','Laptop A'],
    'Color' : ['Green',  'Blue','Red', 'Red','Red','Blue','Green','Blue']
})

2. 生成基础透视表 #

先创建行层级为Products+Sub Products、列层级为Color的基础计数透视表：

# 生成基础透视表，统计每个(产品-子产品-颜色)组合的数量
base_pivot = pd.crosstab(
    index=[df['Products'], df['Sub Products']],
    columns=df['Color'],
    aggfunc='size',
    margins=False
).fillna(0).astype(int)

3. 计算各层级总计 #

分别计算子产品级小计、产品级总计和全局总计：

# 1. 子产品级小计：每个产品下所有子产品的颜色数量合计
sub_totals = base_pivot.groupby(level='Products').sum()
# 给小计行重命名索引，方便识别
sub_totals.index = pd.MultiIndex.from_tuples(
    [(prod, '子产品总计') for prod in sub_totals.index],
    names=['Products', 'Sub Products']
)

# 2. 产品级总计：所有产品的颜色数量合计
prod_totals = base_pivot.sum().to_frame().T
prod_totals.index = pd.MultiIndex.from_tuples(
    [('全局总计', '')],
    names=['Products', 'Sub Products']
)

# 3. 添加行总计列：每行的合计值
base_pivot['行总计'] = base_pivot.sum(axis=1)
sub_totals['行总计'] = sub_totals.sum(axis=1)
prod_totals['行总计'] = prod_totals.sum(axis=1)

4. 拼接结果并排序 #

将基础透视表和各层级总计按产品分组拼接，保持顺序一致：

# 按产品分组，将基础行和对应的子产品小计拼接
result_list = []
for prod in base_pivot.index.get_level_values('Products').unique():
    # 提取当前产品的所有子产品行
    prod_group = base_pivot.xs(prod, level='Products')
    # 提取当前产品的子产品小计行
    prod_sub_total = sub_totals.xs(prod, level='Products')
    # 拼接并添加回产品层级索引
    combined = pd.concat([prod_group, prod_sub_total])
    combined = pd.concat([combined], keys=[prod], names=['Products'])
    result_list.append(combined)

# 拼接所有产品组，再添加全局总计
final_result = pd.concat(result_list)
final_result = pd.concat([final_result, prod_totals])

# 调整列顺序，将行总计放在最后
cols = [col for col in final_result.columns if col != '行总计'] + ['行总计']
final_result = final_result[cols]

最终效果 #

运行后final_result的输出格式完全贴合Excel透视表的样式：

Blue  Green  Red  行总计
Products     Sub Products                  
Products A   Phone A      1      1    0     2
             Phone B      1      0    0     1
             Laptop B     0      0    1     1
             子产品总计     2      1    1     4
Products B   Phone B      0      1    1     2
             Laptop B     0      0    1     1
             子产品总计     0      1    2     3
全局总计                   2      2    3     7

关键说明 #

• 用pd.crosstab快速生成基础透视表，aggfunc='size'直接统计行数（比count更高效）
• 通过groupby(level=...)按多级索引的层级计算总计
• 用pd.concat按产品分组拼接基础行和小计行，保证结构与Excel透视表一致

内容的提问来源于stack exchange，提问作者scarface du ghetto