基于分组与列条件填充Pandas DataFrame的Code列问题

嘿，我来帮你搞定这个按品牌分组填充Code列的问题～先理清楚你的核心需求，再看看之前代码的问题，最后给你一个能得到预期结果的方案：

需求回顾 #

你需要按Make（品牌）分组处理Code列，规则如下：

• 同一品牌下，如果Code是None，用该品牌已有的有效Code填充
• 后缀优先级：BG > _BG，也就是说如果品牌下有带BG的Code，所有该品牌的Code都要统一成带BG的格式；如果只有_BG，就统一成_BG格式
• 特殊处理：奔驰的Mercedes_BG要改成MercedesBG（去掉下划线）；Jeep有JeepBG和_BG，所以所有Jeep的Code都应该是JeepBG

之前代码的问题 #

你之前用str.split的方式没有正确处理基准Code提取和后缀优先级的逻辑，比如Jeep的情况里，代码只提取到了_BG后缀，却没拿到JeepBG里的基准Jeep，导致最终结果错误。

解决方案代码 #

我们可以写一个分组处理函数，明确按优先级提取基准Code和后缀，再统一填充：

import pandas as pd
import numpy as np

# 先创建你的原始DataFrame
df = pd.DataFrame({'Make': ['Tesla','Tesla','Tesla','Toyota','Ford','Ford','Ford','BMW','BMW','BMW','Mercedes','Mercedes','Mercedes','Jeep','Jeep','Jeep'],
'Type': ['Model X','Model X','Model X','Corolla','Bronco','Bronco','Mustang','3 Series','3 Series','7 Series','C-Class','C-Class','S-Class','Wrangler','Compass','Patriot'],
'Year': [2015, 2015, 2015, 2017, 2018, 2018, 2020, 2015, 2015, 2017, 2018, 2018, 2020,2020,2021,2020],
'Price': [85000, 90000, 95000, 20000, 35000, 35000, 45000, 40000, 40000, 65000, 50000, 50000, 75000,60000,45000,40000],
'Color': ['White','White','White','Red','Blue','Blue','Yellow','Silver','Silver','Black','White','White','Black','Grey','Brown','Green'],
'Code'  : ['TSLABG','TSLA',None,'TYTA','FRD','_BG',None,None,'BMW','BMW','MercedesBG','Mercedes_BG','MercedesBG',None,'_BG','JeepBG']
})

def process_code_group(group):
    # 获取当前分组所有非空的Code值
    valid_codes = group['Code'].dropna().unique()
    
    base_code = None
    suffix = ''
    
    # 第一步：优先找带BG（无下划线）的Code
    bg_codes = [code for code in valid_codes if 'BG' in code and '_BG' not in code]
    if bg_codes:
        example_code = bg_codes[0]
        base_code = example_code.replace('BG', '')
        suffix = 'BG'
    else:
        # 第二步：找带_BG的Code
        underscore_bg_codes = [code for code in valid_codes if '_BG' in code]
        if underscore_bg_codes:
            # 优先找不是纯_BG的有效Code（比如FRD_BG）
            non_empty_bg_codes = [code for code in underscore_bg_codes if code != '_BG']
            if non_empty_bg_codes:
                example_code = non_empty_bg_codes[0]
                base_code = example_code.replace('_BG', '')
                suffix = '_BG'
            else:
                # 只有纯_BG，那找该品牌下其他非BG的基准Code
                non_bg_codes = [code for code in valid_codes if 'BG' not in code and code != '_BG']
                if non_bg_codes:
                    base_code = non_bg_codes[0]
                    suffix = '_BG'
        else:
            # 第三步：没有BG相关后缀，直接用普通Code
            non_bg_codes = [code for code in valid_codes if code != '_BG']
            if non_bg_codes:
                base_code = non_bg_codes[0]
    
    # 特殊处理奔驰：强制把后缀改成BG，基准为Mercedes
    if group.name == 'Mercedes':
        base_code = 'Mercedes'
        suffix = 'BG'
    
    # 拼接最终Code
    final_code = f"{base_code}{suffix}" if base_code else ''
    group['Code'] = final_code
    return group

# 按Make分组应用处理函数
df = df.groupby('Make', group_keys=False).apply(process_code_group)

# 输出结果
print(df)

运行结果 #

执行后就能得到你预期的输出：

Make       Type  Year  Price   Color       Code
0      Tesla    Model X  2015  85000   White    TSLABG
1      Tesla    Model X  2015  90000   White    TSLABG
2      Tesla    Model X  2015  95000   White    TSLABG
3     Toyota    Corolla  2017  20000     Red      TYTA
4       Ford     Bronco  2018  35000    Blue    FRD_BG
5       Ford     Bronco  2018  35000    Blue    FRD_BG
6       Ford    Mustang  2020  45000   Yellow    FRD_BG
7        BMW   3 Series  2015  40000   Silver       BMW
8        BMW   3 Series  2015  40000   Silver       BMW
9        BMW   7 Series  2017  65000    Black       BMW
10  Mercedes    C-Class  2018  50000   White  MercedesBG
11  Mercedes    C-Class  2018  50000   White  MercedesBG
12  Mercedes    S-Class  2020  75000    Black  MercedesBG
13      Jeep   Wrangler  2020  60000     Grey    JeepBG
14      Jeep    Compass  2021  45000    Brown    JeepBG
15      Jeep    Patriot  2020  40000    Green    JeepBG

备注：内容来源于stack exchange，提问作者Sudeep George