如何在Pandas多级透视表中为顶层列添加子列差值列

我有一个带多级列的Pandas透视表，想要为每个顶层PLAYER列，计算其下两个YEAR列的差值（2022 - 2020），并把结果作为第三列DELTA添加进去。

原始数据与透视表代码 #

import pandas as pd

stats = { 
    'YEAR' : [2020, 2020, 2020, 2020, 2022, 2022, 2022, 2022], 
    'PLAYER' : ['BILL','BOB', 'BILL','BOB','BILL','BOB','BILL','BOB'],
    'STAT' : ['SINGLES', 'SINGLES', 'HOME RUNS', 'HOME RUNS','SINGLES', 'SINGLES', 'HOME RUNS', 'HOME RUNS'],
    'COUNT' :  [101, 135, 24, 42, 144, 135, 28, 34]
}

df = pd.DataFrame(stats) 
df_piv = df.pivot_table(values='COUNT', columns=['PLAYER','YEAR'], index='STAT')

生成的透视表：

现有尝试及问题 #

我尝试用以下代码计算差值：

df_delta = df_piv.groupby(level='PLAYER', axis=1).diff()

得到的结果：

但这不是我想要的，我期望的结果是在每个PLAYER的列组后添加DELTA列：

解决方案 #

可以通过以下步骤实现需求：

1. 计算每个PLAYER的差值，明确指定用2022年数据减去2020年数据，避免diff()产生的NaN：

# 计算差值，构造新的多级列
delta_data = df_piv.xs(2022, level='YEAR', axis=1) - df_piv.xs(2020, level='YEAR', axis=1)
delta_df = delta_data.rename(columns={col: (col, 'DELTA') for col in delta_data.columns})

2. 将原透视表和差值表合并，然后重新排列列的顺序，保证每个PLAYER的列按2020、2022、DELTA的顺序排列：

# 合并原表和差值表
combined = pd.concat([df_piv, delta_df], axis=1)

# 重新排序列，确保每个PLAYER的列组内顺序正确
sorted_columns = []
for player in df_piv.columns.get_level_values('PLAYER').unique():
    sorted_columns.extend([(player, 2020), (player, 2022), (player, 'DELTA')])

final_df = combined[sorted_columns]

运行上述代码后，final_df就是你期望的结果。

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