Pandas pivot_table实现分层透视表并添加Division、Region层级小计

import pandas as pd

# 加载示例数据
d2 = {
    'Division': ['DIV1', 'DIV2', 'DIV1', 'DIV3', 'DIV2'],
    'Region': ['DIV1-South', 'DIV2-North', 'DIV1-North', "DIV3-East", "DIV2-South"],
    'MD': ["Susie", 'Martha', "Jane", "Nichole", "Randall"],
    'Month': ['JAN', 'JAN', 'FEB', 'MAR', "APR"]
}
df2 = pd.DataFrame(d2)

# 1. 生成基础明细透视表
pivoted = df2.pivot_table(
    index=['Division', 'Region', 'MD'],
    columns='Month',
    aggfunc='size',
    fill_value=0
)

# 2. 计算Region层级小计
region_subtotal = pivoted.groupby(level=[0, 1]).sum()
region_subtotal = region_subtotal.assign(
    MD=lambda x: x.index.get_level_values(1) + ' SubTotal'
).set_index('MD', append=True)

# 3. 计算Division层级总计
div_total = pivoted.groupby(level=0).sum()
div_total = div_total.assign(
    Region=lambda x: x.index.get_level_values(0) + ' TOTAL',
    MD=''
).set_index(['Region', 'MD'], append=True)

# 4. 拼接所有表并按层级排序，调整月份列顺序匹配需求
final = pd.concat([pivoted, region_subtotal, div_total]).sort_index(level=[0, 1])
final = final.reindex(['JAN', 'FEB', 'MAR', 'APR'], axis=1)

# 输出结果
print(final)

运行后输出的final对象完全匹配你需要的分层小计效果。如果需要处理更多层级的汇总，只需要按照相同逻辑逐层聚合后拼接即可。

内容的提问来源于stack exchange，提问作者John Taylor