基于多条件为GBBO挑战DataFrame添加final_score列

数据集背景 #

我有《英国家庭烘焙大赛》（Great British Baking Show）的挑战数据集，可通过以下代码获取：

pd.read_csv("https://raw.githubusercontent.com/rfordatascience/tidytuesday/master/data/2022/2022-10-25/challenges.csv")

已清理后的数据包含列：series（季数1-10）、episode（集数6-10）、baker（烘焙师姓名）、result（每周结果：淘汰OUT/留任IN）。

需求 #

需要为DataFrame添加final_score列，记录每个系列中烘焙师的最终排名，规则如下：

• 统计每个series的唯一烘焙师总数；
• 同一季中，每一集淘汰的烘焙师共享相同的final_score：
  • 首个淘汰批次的分数等于该季总烘焙师数；
  • 每批次淘汰后，剩余烘焙师数减去该批次淘汰人数，作为下一批次淘汰者的分数。
  • 示例：第一季有10位烘焙师，第一集淘汰2人，二人final_score均为10；第二集淘汰2人，二人final_score均为8。

我的尝试 #

试过窗口函数、apply函数、列表推导式，以下是最接近的两次尝试：

尝试1：定义函数但判断逻辑出错 #

def final_score(df):
    #count the number of bakers per season
    baker_count = df.groupby('series')['baker'].nunique()
    #for each season
    for s in df.series:  
        #create a interable that counts the number of bakers that have been eliminated. Start at 0
        bakers_out = 0
        bakers_remaining = baker_count[int(s)]
        #for each season
        for e in df.episode:
            #does result say OUT for each contestant?
            if df.result =='OUT':
                df['final_score'] = bakers_remaining
                #if so, then we'll add +1 to our bakers_out iterator. 
                bakers_out +=1

                #set the final score category to our baker_count iterator
                df['final_score'] = bakers_remaining

                #subtract the number of bakers left by the amount we just lost
                bakers_remaining -= bakers_out
            else:
                next
    return df

问题：if df.result =='OUT':是对整个Series判断，不是逐行逻辑，导致赋值错误。

尝试2：打印调试接近需求但未实现密集评分 #

baker_count = df.groupby('series')['baker'].nunique()

#for each series
for s in df.series.unique():  
    bakers_out = 0
    bakers_remaining = baker_count[int(s)]
    #for each episode
    for e in df.episode.unique():
        #create a list of results
        data_results = list(df[(df.series==s) & (df.episode==e)].result)
        for dr in data_results:
            if dr =='OUT':
                bakers_out += 1
                print (s,e,dr,';final place:',bakers_remaining,';bakers out:',bakers_out)  
            else:
                print (s,e,dr,'--')
        bakers_remaining -= 1

输出片段：

1.0 1.0 IN --
1.0 1.0 IN --
1.0 1.0 IN --
1.0 1.0 IN --
1.0 1.0 IN --
1.0 1.0 OUT ;final place: 10 ;bakers out: 1
1.0 1.0 OUT ;final place: 10 ;bakers out: 2
1.0 2.0 IN --
1.0 2.0 IN --
1.0 2.0 IN --
1.0 2.0 IN --
1.0 2.0 IN --
1.0 2.0 IN --
1.0 2.0 OUT ;final place: 9 ;bakers out: 3
1.0 2.0 OUT ;final place: 9 ;bakers out: 4

问题：未将分数赋值到DataFrame的final_score列，且每集固定减1，未按该集实际淘汰人数调整剩余数。

解决方案 #

采用分组标记淘汰集数+批量映射分数的方式实现，完整代码如下：

import pandas as pd

# 加载并清理数据（模拟已完成的清理步骤）
df = pd.read_csv("https://raw.githubusercontent.com/rfordatascience/tidytuesday/master/data/2022/2022-10-25/challenges.csv")
df = df[['series', 'episode', 'baker', 'result']].dropna(subset=['result'])

# 步骤1：标记每个烘焙师首次被淘汰的集数，未淘汰的用季内最大集数+1标记
def get_elimination_episode(group):
    out_ep = group[group['result'] == 'OUT']['episode'].min()
    if pd.isna(out_ep):
        out_ep = group['episode'].max() + 1
    return pd.Series({'elim_ep': out_ep})

baker_elim_info = df.groupby(['series', 'baker']).apply(get_elimination_episode).reset_index()

# 步骤2：按季计算每批淘汰者的final_score，生成映射字典
score_mapping = {}
for season, season_group in baker_elim_info.groupby('series'):
    total_bakers = season_group['baker'].nunique()
    remaining_bakers = total_bakers
    # 按淘汰集数排序，确保从早到晚处理淘汰批次
    episode_groups = season_group.groupby('elim_ep').size().sort_index()
    
    for ep, eliminate_count in episode_groups.items():
        # 未淘汰的烘焙师（ep为最大集+1）分数设为1
        if ep > df[df['series'] == season]['episode'].max():
            current_score = 1
        else:
            current_score = remaining_bakers
        # 为该季该批次的所有烘焙师记录分数
        for baker in season_group[season_group['elim_ep'] == ep]['baker']:
            score_mapping[(season, baker)] = current_score
        # 更新剩余人数
        remaining_bakers -= eliminate_count

# 步骤3：将分数映射回原DataFrame
df['final_score'] = df.apply(lambda row: score_mapping[(row['series'], row['baker'])], axis=1)

# 验证结果（以第一季为例）
print(df[(df['series'] == 1) & (df['result'] == 'OUT')][['baker', 'episode', 'final_score']])

代码说明 #

1. 标记淘汰集数：通过分组找到每个烘焙师首次被淘汰的集数，未淘汰的用特殊值标记，保证后续排序处理的顺序正确；
2. 计算批次分数：按季处理，从总烘焙师数开始，为每一批淘汰者分配相同分数，每批结束后按实际淘汰人数更新剩余人数；
3. 映射分数到原表：用字典快速匹配每个烘焙师的分数，完成final_score列的赋值。

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