如何在Pandas中按Genre分组转换分层游戏数据为行式结构

需求概述 #

初始数据量最高可达20000条，每个Genre下游戏数量不超过100款。需将指定结构的游戏数据按Genre分组，实现各Tier层级游戏的映射关联，最终输出需包含Tier_1_Game至Tier_5_Game共5列（无数据时留空）。

示例初始数据 #

data = {'Game':  ['NFS', 'Forza', 'Wreckfest','Dirt Rally','Burnout','Project Cars','Grid 2','GTA','Saints Row','Persona 5','COD','Battlefield','Counter Strike'],
        'Game Tier' : ['Tier 1', 'Tier 2', 'Tier 2','Tier 2','Tier 3','Tier 3','Tier 4','Tier 1','Tier 3','Tier 2','Tier 1','Tier 1','Tier 2'],
        'Genre' : ['Racing', 'Racing', 'Racing','Racing','Racing','Racing','Racing','Open World','Open World','RPG','Shooter','Shooter','Shooter']}

df = pd.DataFrame(data, columns=['Game', 'Game Tier', 'Genre'])

生成的DataFrame：

Game           Game Tier  Genre
0   NFS            Tier 1     Racing
1   Forza          Tier 2     Racing
2   Wreckfest      Tier 2     Racing
3   Dirt Rally     Tier 2     Racing
4   Burnout        Tier 3     Racing
5   Project Cars   Tier 3     Racing
6   Grid 2         Tier 4     Racing
7   GTA            Tier 1     Open World
8   Saints Row     Tier 3     Open World
9   Persona 5      Tier 2     RPG
10  COD            Tier 1     Shooter
11  Battlefield    Tier 1     Shooter
12  Counter Strike Tier 2     Shooter

转换规则 #

按Genre分组后，将每个Tier的游戏与上一层级的所有组合进行关联：

• 先取Genre下Tier 1的游戏作为基础行
• 依次将后续Tier的游戏与已有行进行笛卡尔积关联，生成新行
• 最终输出需包含Tier_1_Game至Tier_5_Game共5列，无对应数据则留空

期望输出示例 #

Tier_1_Game  Tier_2_Game  Tier_3_Game  Tier_4_Game  Tier_5_Game  Genre
0   NFS          Forza        Burnout      Grid 2                    Racing
1   NFS          Wreckfest    Burnout      Grid 2                    Racing
2   NFS          Dirt Rally   Burnout      Grid 2                    Racing
3   NFS          Forza        Project Cars Grid 2                    Racing
4   NFS          Wreckfest    Project Cars Grid 2                    Racing
5   NFS          Dirt Rally   Project Cars Grid 2                    Racing
6   GTA                       Saints Row                             Open World
7                Persona 5                                           RPG
8   COD          Counter Strike                                      Shooter
9   Battlefield  Counter Strike                                      Shooter

环境要求 #

• Python 3.6
• Pandas 1.0.1

实现代码 #

import pandas as pd

def transform_game_data(df):
    # 定义输出所需的所有Tier列名
    tier_columns = [f'Tier_{i}_Game' for i in range(1, 6)]
    result_list = []
    
    # 按Genre分组处理每个类别
    for genre_name, group_df in df.groupby('Genre'):
        # 提取当前Genre下各Tier对应的游戏列表
        tier_game_map = group_df.groupby('Game Tier')['Game'].apply(list).to_dict()
        
        # 初始化当前Genre的结果DataFrame：优先用Tier1数据，无则创建空行
        if 'Tier 1' in tier_game_map:
            current_result = pd.DataFrame(tier_game_map['Tier 1'], columns=[tier_columns[0]])
        else:
            current_result = pd.DataFrame([[None]], columns=[tier_columns[0]])
        
        # 依次处理Tier2到Tier5，执行笛卡尔积关联
        for tier_idx in range(2, 6):
            tier_key = f'Tier {tier_idx}'
            target_col = tier_columns[tier_idx - 1]
            
            if tier_key in tier_game_map:
                # 构造当前Tier的游戏DataFrame
                tier_df = pd.DataFrame(tier_game_map[tier_key], columns=[target_col])
                # 通过临时键实现笛卡尔积合并
                current_result['_tmp_key'] = 1
                tier_df['_tmp_key'] = 1
                current_result = current_result.merge(tier_df, on='_tmp_key').drop('_tmp_key', axis=1)
        
        # 填充空值为空白字符串，添加Genre列
        current_result = current_result.fillna('')
        current_result['Genre'] = genre_name
        result_list.append(current_result)
    
    # 合并所有Genre的结果，重置索引并调整列顺序
    final_output = pd.concat(result_list, ignore_index=True)
    final_output = final_output[tier_columns + ['Genre']]
    return final_output

# 测试执行
if __name__ == '__main__':
    # 构造测试数据
    test_data = {'Game':  ['NFS', 'Forza', 'Wreckfest','Dirt Rally','Burnout','Project Cars','Grid 2','GTA','Saints Row','Persona 5','COD','Battlefield','Counter Strike'],
                 'Game Tier' : ['Tier 1', 'Tier 2', 'Tier 2','Tier 2','Tier 3','Tier 3','Tier 4','Tier 1','Tier 3','Tier 2','Tier 1','Tier 1','Tier 2'],
                 'Genre' : ['Racing', 'Racing', 'Racing','Racing','Racing','Racing','Racing','Open World','Open World','RPG','Shooter','Shooter','Shooter']}
    input_df = pd.DataFrame(test_data, columns=['Game', 'Game Tier', 'Genre'])
    
    # 转换并打印结果
    output_df = transform_game_data(input_df)
    print(output_df.to_string(index=False))

代码说明 #

1. 分组提取：按Genre拆分数据后，提取每个Genre下各Tier对应的游戏列表，便于后续关联
2. 基础行初始化：以Tier1游戏作为基础数据，若无Tier1则创建空行保证结构完整
3. 笛卡尔积关联：对Tier2到Tier5依次执行笛卡尔积合并，实现层级间的全组合关联
4. 结果规整：统一填充空值、添加Genre列并调整列顺序，确保输出符合要求

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