Pandas按version分组，自定义函数计算overall分支X值

方法一：自定义函数结合groupby.apply #

逻辑直观，适合快速理解需求：

import pandas as pd
import numpy as np

# 构建示例DataFrame（已有数据可跳过此步骤）
df = pd.DataFrame(
    data=np.array(
        [
            [2475.0, 2475.0, 1712.5, 257.5, 392.5, 112.5, 2475.0, 2341.5, 95.0, 38.5, 2475.0, 2000.0, 475.0, 2475.0, 2341.5, 133.5],
            [-1, -1, 1, 2, 2, 3, -1, 1, 2, 2, -1, 1, 2, -1, 1, 1]
        ]
    ).T,
    index=pd.MultiIndex.from_tuples(
        tuples=[
            ('v0', 'overall'),
            ('v1', 'overall'),
            ('v1', 'A'),
            ('v1', 'B'),
            ('v1', 'C'),
            ('v1', 'D'),
            ('v2', 'overall'),
            ('v2', 'A'),
            ('v2', 'B'),
            ('v2', 'C'),
            ('v3', 'overall'),
            ('v3', 'A'),
            ('v3', 'B'),
            ('v4', 'overall'),
            ('v4', 'A'),
            ('v4', 'B'),
        ],
        names=['version', 'branch'],
    ),
    columns=['N', 'X'],
)

# 定义处理每个version分组的函数
def calculate_overall_x(group):
    # 筛选当前组中非overall的分支数据
    other_branches = group[group.index.get_level_values('branch') != 'overall']
    if other_branches.empty:
        # 组内仅含overall分支，返回1
        return 1.0
    # 计算加权和：非分支的N*X之和
    weighted_sum = (other_branches['N'] * other_branches['X']).sum()
    # 获取当前组overall分支的N值
    overall_n = group.loc[group.index.get_level_values('branch') == 'overall', 'N'].iloc[0]
    # 返回加权和除以overall的N值
    return weighted_sum / overall_n

# 计算每个version对应的overall新X值
overall_x_updates = df.groupby('version').apply(calculate_overall_x)

# 将新值赋值回原DataFrame
df.loc[pd.IndexSlice[:, 'overall'], 'X'] = overall_x_updates

# 查看结果
print(df)

方法二：高效向量式操作（适合大数据量） #

避免逐组apply，通过分组聚合直接计算，性能更优：

# 计算每个version的非分支加权和
weighted_sums = df[df.index.get_level_values('branch') != 'overall'].groupby('version').apply(lambda g: (g['N'] * g['X']).sum())
# 获取每个version的overall分支N值
overall_ns = df[df.index.get_level_values('branch') == 'overall'].groupby('version')['N'].first()
# 计算新的X值，空值（仅含overall的version）填充为1
new_overall_x = weighted_sums.div(overall_ns).fillna(1.0)
# 赋值回原DataFrame
df.loc[pd.IndexSlice[:, 'overall'], 'X'] = new_overall_x

结果验证 #

运行后输出与预期完全匹配：

• v0的overall X值为1（仅含overall分支）
• v1的overall X值≈1.35353535354
• v2的overall X值≈1.05393939394
• v3的overall X值≈1.19191919192
• v4的overall X值为1（加权和等于overall的N值）

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