基于同组已有值填充Pandas DataFrame的Code列

原始数据 #

给出的Pandas DataFrame如下：

import pandas as pd

df = pd.DataFrame({'Make': ['Tesla','Tesla','Tesla','Toyota','Ford','Ford','Ford','BMW','BMW','BMW','Mercedes','Mercedes','Mercedes'],
                   'Type': ['Model X','Model X','Model X','Corolla','Bronco','Bronco','Mustang','3 Series','3 Series','7 Series','C-Class','C-Class','S-Class'],
                   'Year': [2015, 2015, 2015, 2017, 2018, 2018, 2020, 2015, 2015, 2017, 2018, 2018, 2020],
                   'Price': [85000, 90000, 95000, 20000, 35000, 35000, 45000, 40000, 40000, 65000, 50000, 50000, 75000],
                   'Color': ['White','White','White','Red','Blue','Blue','Yellow','Silver','Silver','Black','White','White','Black'],
                   'Code'  : ['TSLA_BG','TSLA','-','TYTA','FRD','-_BG','-','-','BMW','BMW','Mercedes_BG','Mercedes_BG','Mercedes_BG']
                  })

原始DataFrame展示：

Make      Type  Year  Price   Color          Code
0    Tesla   Model X  2015  85000   White       TSLA_BG
1    Tesla   Model X  2015  90000   White         TSLA
2    Tesla   Model X  2015  95000   White            -
3   Toyota   Corolla  2017  20000     Red          TYTA
4     Ford    Bronco  2018  35000    Blue           FRD
5     Ford    Bronco  2018  35000    Blue        -_BG
6     Ford   Mustang  2020  45000  Yellow            -
7      BMW  3 Series  2015  40000  Silver            -
8      BMW  3 Series  2015  40000  Silver           BMW
9      BMW  7 Series  2017  65000   Black           BMW
10 Mercedes  C-Class  2018  50000   White  Mercedes_BG
11 Mercedes  C-Class  2018  50000   White  Mercedes_BG
12 Mercedes  S-Class  2020  75000   Black  Mercedes_BG

需求说明 #

• 当Code列值为"-"时，用同Make组内的其他有效Code值填充
• 若同组内存在带"_BG"后缀的Code值，该组所有Code值都需添加"_BG"后缀
• 若组内无"_BG"后缀的Code（如BMW组），填充后不加该后缀

解决方案 #

通过分组处理结合自定义函数实现：

def process_group(group):
    # 提取组内非"-"的Code值
    valid_codes = group[group['Code'] != '-']['Code']
    # 判断组内是否有带_BG后缀的Code
    has_bg = any(code.endswith('_BG') for code in valid_codes)
    
    # 提取基础Code（去除_BG后缀，排除无效的"-_BG"）
    base_code = None
    for code in valid_codes:
        cleaned = code.replace('_BG', '')
        if cleaned != '-':
            base_code = cleaned
            break
    
    # 生成最终Code
    final_code = f"{base_code}_BG" if has_bg else base_code
    # 替换组内所有Code值
    group['Code'] = final_code
    return group

# 按Make分组处理
df_updated = df.groupby('Make', group_keys=False).apply(process_group)
print(df_updated)

代码解释 #

1. 分组处理：按Make列分组，对每个组单独处理
2. 筛选有效Code：过滤掉组内Code为"-"的行，得到有效Code集合
3. 判断后缀需求：检查有效Code中是否存在带"_BG"后缀的项
4. 提取基础标识：从有效Code中去除"_BG"后缀，排除"-_BG"这类无效值，得到组的基础Code
5. 统一替换：根据是否需要加后缀生成最终Code，替换组内所有行的Code值

预期输出 #

Make      Type  Year  Price   Color          Code
0    Tesla   Model X  2015  85000   White       TSLA_BG
1    Tesla   Model X  2015  90000   White       TSLA_BG
2    Tesla   Model X  2015  95000   White       TSLA_BG
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  Mercedes_BG
11 Mercedes  C-Class  2018  50000   White  Mercedes_BG
12 Mercedes  S-Class  2020  75000   Black  Mercedes_BG

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