如何在多级索引DataFrame中对特定组的Pandas列做差值计算？

我希望将每个组的平均值行与其对应的子列做减法运算：

• 对Basketball类别，计算Player Statistics下Dribbling_Speed_Team_Blue/Dribbling_Speed_Team_Red的平均值，与Best Player Statistics下的Dribbling_Speed求差值，生成Difference行
• 对Football类别，计算Player Statistics下Kicking_Power_Team_Blue/Kicking_Power_Team_Red的平均值，与Best Player Statistics下的Kicking_Power求差值，生成Difference行

最终需要在现有DataFrame中新增Difference行，显示对应列的计算差值。

import pandas as pd
nested_dict = {
    'Game':{
    'Basketball': {
        'Player Statistics': {
            'Dribbling_Speed_Team_Blue': {
                'Player_A': 1,
                'Player_B': 3
            },
              'Dribbling_Speed_Team_Red': {
                'Player_A': 2,
                'Player_B': 4
            }
        },
       'Best Player Statistics': {
            'Dribbling_Speed': {
                'Player': 20,
            }
        }

    },

        'Football': {
        'Best Player Statistics': {
            'Kicking_Power': {
                'Player_A': 12,
                'Player_B': 8
            }
        },
       'Player Statistics': {
            'Kicking_Power_Team_Blue': {
                'Player': 40,
            },
            'Kicking_Power_Team_Red': {
                'Player': 40,
            }
        }

    },
    }
}

out = pd.json_normalize(nested_dict)
out.columns = out.columns.str.split('.', expand=True)

sum_data = out.groupby(level=[0, 1,2,3], axis = 1).sum()
count_data = out.groupby(level=[0, 1,2,3], axis = 1).count()
result_df = pd.concat([sum_data, count_data], axis=0, keys=['Sum', 'Count'])
result_df.index = result_df.index.droplevel(-1)
result_df.loc['avg'] = result_df.loc['Sum']/result_df.loc['Count']

核心思路是先提取Best Player Statistics的基准值，再匹配对应团队统计项的平均值做减法，具体实现代码如下：

# 提取Best Player Statistics的基准值并整理
best_player_vals = result_df.loc['avg'].filter(like='Best Player Statistics').unstack(level=[1,2])
benchmark = {}

# 处理Basketball的基准值
benchmark['Dribbling_Speed'] = best_player_vals[('Game', 'Basketball', 'Best Player Statistics', 'Dribbling_Speed')].values[0]
# 处理Football的基准值（取两个Player的平均值）
benchmark['Kicking_Power'] = best_player_vals[('Game', 'Football', 'Best Player Statistics', 'Kicking_Power')].mean()

# 计算差值并构建Difference行
diff_data = {}
for col in result_df.columns:
    game, stat_type, stat_name, player = col
    if stat_type == 'Player Statistics':
        # 提取统计项的核心名称（去除团队后缀）
        base_stat = stat_name.rsplit('_', 2)[0]
        # 计算差值：团队平均值 - 最佳球员基准值
        diff_data[col] = result_df.loc['avg', col] - benchmark[base_stat]
    else:
        # 非团队统计项设为NaN
        diff_data[col] = None

# 将差值行添加到DataFrame
result_df.loc['Difference'] = pd.Series(diff_data)

执行后，result_df会新增Difference行，显示每个团队统计值与对应最佳球员基准值的差值，符合预期需求。

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