如何在Python中拆解球员奖项数据为奖项列与赛季年份值？

步骤1：展开嵌套的奖项数据 #

先把每个球员的awards字典拆解为（球员名、标准化奖项名称、赛季）的结构化数据：

import pandas as pd

# 模拟已完成JSON转换的players_personal（替换为你的实际DataFrame）
players_personal = pd.DataFrame({
    'player_name': ['Sergei Belov', 'Rafael Khakimov'],
    'awards': [
        {'2009-2010': ['Russia2 Silver Medal'], '2013-2014': ['VHL Silver Medal']},
        {'2010-2011': ['MHL All-Star Game','MHL Best GAA  (1.79)','MHL Best Goaltender','MHL Goaltender of the Month  (February)','MHL Goaltender of the Month  (September)'],
         '2017-2018': ['VHL Playoffs Best GAA  (1.39)'], 
         '2021-2022': ['VHL Goaltender of the Month  (November)']}
    ]
})

# 遍历拆解嵌套字典
expanded_rows = []
for _, row in players_personal.iterrows():
    player = row['player_name']
    for season, awards_list in row['awards'].items():
        for award in awards_list:
            # 标准化奖项名称为合法列名：小写+空格/括号替换为下划线
            standard_award = award.lower().replace(' ', '_').replace('(', '').replace(')', '')
            expanded_rows.append({
                'player_name': player,
                'award': standard_award,
                'season': season
            })

expanded_df = pd.DataFrame(expanded_rows)

步骤2：透视生成目标宽表 #

将展开后的长表转换为「奖项为列、赛季为值」的结构，同时保留所有球员：

# 透视表自动填充缺失奖项为NaN
pivot_df = expanded_df.pivot(
    index='player_name',
    columns='award',
    values='season'
).reset_index()

# 与原表左连接，确保所有球员都被保留
final_df = players_personal[['player_name']].merge(pivot_df, on='player_name', how='left')

最终效果 #

final_df的结构与需求完全匹配，示例片段如下：

关键说明 #

• 奖项名称标准化：避免列名出现空格、特殊字符，符合DataFrame列名规范
• 透视表自动处理缺失值：未获得的奖项自动填充NaN
• 左连接保证完整性：即使球员无对应奖项，也不会被过滤

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