如何为目标DataFrame添加各Metric指标的列均值汇总行？

原始DataFrame #

_BETTER   _SAME     _WORSE    ___dataset     Metric
0.373802  0.816794  0.568783      Train      precision
0.391304  0.865229  0.519324      Train      recall
0.382353  0.840314  0.542929      Train      f1-score
0.500000  1.000000  0.583333      Val        precision
0.333333  1.000000  0.736842      Val        recall
0.400000  1.000000  0.651163      Val        f1-score
0.000000  0.000000  0.666667      Test       precision
0.000000  0.000000  0.500000      Test       recall
0.000000  0.000000  0.571429      Test       f1-score

需求说明 #

需要为上述DataFrame添加三行汇总数据：

• 每行分别对应precision_avg、recall_avg、f1_score_avg
• 每行的_BETTER、_SAME、_WORSE值为对应指标在Train、Val、Test三个数据集下的均值
• ___dataset列统一填All

完整实现代码 #

import pandas as pd

# 构建原始DataFrame
data = [
    [0.373802, 0.816794, 0.568783, 'Train', 'precision'],
    [0.391304, 0.865229, 0.519324, 'Train', 'recall'],
    [0.382353, 0.840314, 0.542929, 'Train', 'f1-score'],
    [0.500000, 1.000000, 0.583333, 'Val', 'precision'],
    [0.333333, 1.000000, 0.736842, 'Val', 'recall'],
    [0.400000, 1.000000, 0.651163, 'Val', 'f1-score'],
    [0.000000, 0.000000, 0.666667, 'Test', 'precision'],
    [0.000000, 0.000000, 0.500000, 'Test', 'recall'],
    [0.000000, 0.000000, 0.571429, 'Test', 'f1-score']
]
df = pd.DataFrame(data, columns=['_BETTER', '_SAME', '_WORSE', '___dataset', 'Metric'])

# 计算各指标的三列均值
avg_metrics = df.groupby('Metric')[['_BETTER', '_SAME', '_WORSE']].mean().reset_index()

# 重命名Metric列的标识为需求格式
avg_metrics['Metric'] = avg_metrics['Metric'].map({
    'precision': 'precision_avg',
    'recall': 'recall_avg',
    'f1-score': 'f1_score_avg'
})

# 设置汇总行的数据集标识
avg_metrics['___dataset'] = 'All'

# 合并原始数据与汇总行
final_df = pd.concat([df, avg_metrics], ignore_index=True)

# 输出结果
print(final_df.to_string(index=False))

代码说明 #

1. 分组计算均值：用groupby('Metric')直接按指标分组，一次性计算每个指标在三个数据集下_BETTER、_SAME、_WORSE的均值，替代拆分多个子DataFrame的冗余操作
2. 重命名指标：通过map方法将原始指标名替换为带_avg后缀的汇总标识
3. 合并数据：用pd.concat将原始DataFrame和汇总行合并，生成最终结果

输出结果 #

_BETTER   _SAME     _WORSE ___dataset        Metric
0.373802 0.816794 0.568783      Train     precision
0.391304 0.865229 0.519324      Train        recall
0.382353 0.840314 0.542929      Train    f1-score
0.500000 1.000000 0.583333        Val     precision
0.333333 1.000000 0.736842        Val        recall
0.400000 1.000000 0.651163        Val    f1-score
0.000000 0.000000 0.666667       Test     precision
0.000000 0.000000 0.500000       Test        recall
0.000000 0.000000 0.571429       Test    f1-score
0.291301 0.605598 0.606261        All precision_avg
0.241546 0.621743 0.585389        All    recall_avg
0.260784 0.613438 0.588507        All f1_score_avg

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