Pandas多列透视表添加分组行小计的实现方法

通过Django QuerySet的from_records方法构建DataFrame后，完成双列透视生成仪表板视图，已实现全局行列总和，但需要为第一个透视列（每个fund分组）添加行小计。尝试过循环列等方法未成功，求实现插入小计列并计算分组行小计的方案。

原始DataFrame #

type                    amount      source  fund
0   Ressource Humaine CDD   -36470.36   Expense fund2
1   Mission                 -1686.47    Expense fund2
2   Fonctionnement          -817465.91  Expense fund1
3   Fonctionnement          1118691.65  Budget  fund1
4   Fonctionnement          -6000       Expense fund3
5   Fonctionnement          -23621.83   Expense fund2
6   Frais de Gestion        -53499      Expense fund2
7   Fonctionnement          15000       Budget  fund3
8   Frais de Gestion        53499       Budget  fund2
9   Fonctionnement          186718.78   Budget  fund2
10  Mission                 1686.47     Budget  fund2
11  Ressource Humaine CDD   38676.53    Budget  fund2

当前透视代码 #

piv = pd.pivot_table(index="type", columns=["fund","source"], values="amount", aggfunc='sum', margins=True, margins_name='Sum')

当前透视结果 #

fund                    fund1                   fund2                       fund3
source                  Budget      Expense     Budget      Expense         Budget      Expense
type
Fonctionnement          1118691.65  -817465.91  186718.78   -23621.83       15000.00    -6000.00
Frais de Gestion        NaN         NaN         53499.00    -53499.00       NaN         NaN
Mission                 NaN         NaN         1686.47     -1686.47        NaN         NaN
Ressource Humaine CDD   NaN         NaN         38676.53    -36470.36       NaN         NaN
Sum                     1118691.65  -817465.91  280580.78   -119277.66      15000.00    -6000.00

期望结果 #

fund                    fund1                                       fund2                                   fund3
source                  Budget      Expense         total fund1     Budget      Expense     total fund2     Budget      Expense     total fund3
type
Fonctionnement          1118691.65  -817465.91      301225.74€     186718.78   -23621.83   163096.95€     15000.00    -6000.00    9000.00€
Frais de Gestion        NaN         NaN             NaN             53499.00    -53499.00   0.00€           NaN         NaN         NaN 
Mission                 NaN         NaN             NaN             1686.47     -1686.47    0.00€           NaN         NaN         NaN
Ressource Humaine CDD   NaN         NaN             NaN             38676.53    -36470.36   2206.17€        NaN         NaN         NaN

通过按fund分组计算行小计，再将小计列插入对应分组末尾，最后整理列顺序即可实现需求：

步骤1：计算每个fund的行小计 #

按fund层级对透视表分组，计算每行的Budget与Expense合计：

# 按fund分组，计算每行小计
subtotals = piv.groupby(level='fund', axis=1).sum()
# 重命名小计列为多级索引格式
subtotals.columns = pd.MultiIndex.from_tuples([(fund, f'total {fund}') for fund in subtotals.columns])

步骤2：合并透视表与小计列并调整顺序 #

将小计列与原透视表合并，然后重新排列列，确保每个fund的Budget、Expense、小计列连续排列：

# 合并原表与小计列
combined = pd.concat([piv, subtotals], axis=1)

# 重新整理列顺序
new_columns = []
for fund in piv.columns.get_level_values('fund').unique():
    # 取出当前fund的原始列
    fund_raw_cols = [col for col in piv.columns if col[0] == fund]
    # 添加对应小计列
    fund_raw_cols.append((fund, f'total {fund}'))
    new_columns.extend(fund_raw_cols)

# 应用新列顺序
final_piv = combined[new_columns]

步骤3：金额格式化（可选） #

如果需要像期望结果那样添加欧元符号并格式化千分位，可使用以下代码：

def format_currency(x):
    if pd.isna(x):
        return 'NaN'
    return f'{x:,.2f}€'.replace(',', ' ')

final_piv = final_piv.applymap(format_currency)

运行以上代码后，即可得到带fund分组行小计的透视表，与期望结果一致。

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