如何在Pandas多级DataFrame中为每年列后添加总计列？

已生成如下多级透视表：

>>> out
Year  2021                   2022              2023            
Month  Feb Mar  Sep  Oct Dec  Jan Jun Aug  Oct  Jun Sep Nov Dec
ID                                                             
1        0   8  1.5  6.5   6    8   8   2  7.0    9   9   3   0
2        4   4  0.0  0.0   0    0   0   2  8.5    0   0   0   3

生成透视表的代码：

import pandas as pd
import numpy as np

months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',
          'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
months = pd.CategoricalDtype(months, ordered=True)

rng = np.random.default_rng(2023)
df = pd.DataFrame({'ID': rng.integers(1, 3, 20),
                   'Year': rng.integers(2021, 2024, 20),
                   'Month': rng.choice(months.categories, 20),
                   'Value': rng.integers(1, 10, 20)})
out = (df.astype({'Month': months})
        .pivot_table(index='ID', columns=['Year', 'Month'], values='Value',
                     aggfunc='mean', fill_value=0))

期望在每个年份的列之后添加总计列，得到如下结果：

Year  2021          Total  2022                  Total  2023                  Total
Month  Feb Mar  Sep        Oct Dec  Jan Jun Aug         Oct  Jun Sep Nov Dec
ID                                                             
1        0   8  1.5  9.5   6.5   6    8   8   2   31.5  7.0    9   9   3   0   28
2        4   4  0.0   8    0.0   0    0   0   2   2     8.5    0   0   0   3   11.5

可以通过按年份分组计算小计+重新排列列顺序的方式实现，步骤如下：

步骤1：计算每个年份的总计列 #

对透视表按第一级列（Year）分组，计算每行的总和（透视表已完成mean聚合，直接sum即可），并将总计列的结构调整为与原表匹配的多级索引：

# 按Year分组计算每行总计
year_totals = out.groupby(level='Year', axis=1).sum()
# 转换为多级列，匹配原表的(Year, Month)结构
year_totals.columns = pd.MultiIndex.from_tuples(
    [(y, 'Total') for y in year_totals.columns], 
    names=['Year', 'Month']
)

步骤2：合并原表与总计列，重新排序列 #

将原表和总计列合并后，把每个年份的原有列和对应的Total列放在一起，按年份顺序排列：

# 合并原表和总计列
combined = pd.concat([out, year_totals], axis=1)
# 生成新的列顺序：每个年份的月份列 + 该年份的Total列
new_columns = []
for year in out.columns.get_level_values('Year').unique():
    # 获取当前年份的所有月份列
    year_cols = [col for col in out.columns if col[0] == year]
    # 添加当前年份的列和对应的Total列
    new_columns.extend(year_cols + [(year, 'Total')])
# 按新顺序重新排列列
result = combined[new_columns]

完整整合代码 #

import pandas as pd
import numpy as np

# 生成原始数据和透视表
months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',
          'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
months = pd.CategoricalDtype(months, ordered=True)

rng = np.random.default_rng(2023)
df = pd.DataFrame({'ID': rng.integers(1, 3, 20),
                   'Year': rng.integers(2021, 2024, 20),
                   'Month': rng.choice(months.categories, 20),
                   'Value': rng.integers(1, 10, 20)})
out = (df.astype({'Month': months})
        .pivot_table(index='ID', columns=['Year', 'Month'], values='Value',
                     aggfunc='mean', fill_value=0))

# 计算年份总计并合并
year_totals = out.groupby(level='Year', axis=1).sum()
year_totals.columns = pd.MultiIndex.from_tuples(
    [(y, 'Total') for y in year_totals.columns], 
    names=['Year', 'Month']
)
combined = pd.concat([out, year_totals], axis=1)

# 重新排列列顺序
new_columns = []
for year in out.columns.get_level_values('Year').unique():
    year_cols = [col for col in out.columns if col[0] == year]
    new_columns.extend(year_cols + [(year, 'Total')])
result = combined[new_columns]

# 查看结果
print(result)

运行后即可得到期望的带每年总计列的透视表。

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