按行组和列组计算Pandas数据框中各类别的均值

需求说明 #

现有Pandas数据框，包含league（多类别，如iv、v）、team（含长度不同的序列如A、B）、score1、score2等特征列。需要生成仅保留league列，且各score列按规则替换为组内均值的结果数据框：

• 对于team=A的行，score值替换为同league下所有team的score均值的平均值
• 对于team=B的行，score值保留自身team的score均值

示例输入 #

import pandas as pd
df = pd.DataFrame({
                   'league': ['iv', 'iv', 'iv', 'iv', 'iv', 'iv', 'iv'],
                   'team': ['A', 'A', 'A', 'B', 'B', 'B', 'B'],
                   'score1': [1, 1, 1, 2, 2, 2, 2],
                   'score2': [2, 2, 2, 4, 4, 4, 4]})

实现代码 #

# 1. 按league和team分组，计算每个组的score均值
team_grouped = df.groupby(['league', 'team'])[['score1', 'score2']].mean().reset_index()

# 2. 按league分组，计算每个league下所有team的score均值的平均值
league_avg = team_grouped.groupby('league')[['score1', 'score2']].mean().reset_index()
league_avg = league_avg.rename(columns={'score1': 'score1_league_avg', 'score2': 'score2_league_avg'})

# 3. 将原数据与分组均值、league均值合并
merged = df.merge(team_grouped, on=['league', 'team'], suffixes=('', '_team_mean'))
merged = merged.merge(league_avg, on='league')

# 4. 根据team选择对应的均值：A用league平均，B用自身team均值
merged['score1_grouped_mean'] = merged.apply(
    lambda x: x['score1_league_avg'] if x['team'] == 'A' else x['score1_team_mean'], axis=1
)
merged['score2_grouped_mean'] = merged.apply(
    lambda x: x['score2_league_avg'] if x['team'] == 'A' else x['score2_team_mean'], axis=1
)

# 5. 保留需要的列，生成结果数据框
df_result = merged[['league', 'score1_grouped_mean', 'score2_grouped_mean']]
print(df_result)

输出结果 #

league  score1_grouped_mean  score2_grouped_mean
0     iv                  1.5                  3.0
1     iv                  1.5                  3.0
2     iv                  1.5                  3.0
3     iv                  2.0                  4.0
4     iv                  2.0                  4.0
5     iv                  2.0                  4.0
6     iv                  2.0                  4.0

扩展兼容多League、多Team场景 #

如果需要支持更多team类别，只需调整步骤4的判断逻辑即可：

• 若希望除team=B外的所有team都使用league平均：

  merged['score1_grouped_mean'] = merged.apply(
      lambda x: x['score1_league_avg'] if x['team'] != 'B' else x['score1_team_mean'], axis=1
  )
  

• 若希望每个team使用同league下其他team的均值的平均：

  # 先获取每个league的team数量
  team_count = team_grouped.groupby('league')['team'].count().reset_index(name='team_num')
  merged = merged.merge(team_count, on='league')
  
  # 计算其他team的均值平均
  merged['score1_grouped_mean'] = (merged['score1_league_avg'] * merged['team_num'] - merged['score1_team_mean']) / (merged['team_num'] - 1)
  

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