Python pandas实现同Parent下Bucket首次出现后重复项置为N/A

原始数据结构 #

Parent      Child     Overall Risk            Bucket
0  CW12345  CW34565          Low       Low to High
1  CW12345  CW28394         High               N/A
2  CW12345  CW77646     Moderate  Moderate to High
3  CW12345  CW09871         High               N/A
4  CW12345  CW66542          Low   Low to Critical
5  CW12345  CW13356     Critical               N/A
6  CW12345  CW98452          Low   Low to Critical
7  CW12345  CW78899     Critical               N/A

需求说明 #

全量数据包含约1000个Parent，每个Parent对应若干Child。需在同一Parent分组内，仅保留首次出现的**"x to y"格式**的Bucket值，后续同类格式的重复取值替换为N/A，最终生成New Bucket列，结果写入Excel。

期望输出 #

Parent    Child       Overall Risk            Bucket       New Bucket
0  CW12345  CW34565          Low       Low to High      Low to High
1  CW12345  CW28394         High               N/A              N/A
2  CW12345  CW77646     Moderate  Moderate to High              N/A
3  CW12345  CW09871         High               N/A              N/A
4  CW12345  CW66542          Low   Low to Critical  Low to Critical
5  CW12345  CW13356     Critical               N/A              N/A
6  CW12345  CW98452          Low   Low to Critical              N/A
7  CW12345  CW78899     Critical               N/A              N/A

实现方案 #

以下是基于Pandas的Python代码实现：

import pandas as pd

# 加载数据：若数据来自Excel，取消注释下方代码并替换路径
# df = pd.read_excel("your_input_file.xlsx")

# 标记符合"x to y"格式的Bucket行
df['is_valid_bucket'] = df['Bucket'].str.contains(r'\b\w+ to \w+\b', na=False)

# 分组处理每个Parent，保留首次有效Bucket值
def process_parent_group(group):
    # 获取分组内第一个有效Bucket的索引
    first_valid_pos = group[group['is_valid_bucket']].index.min()
    # 初始化New Bucket为N/A
    group['New Bucket'] = 'N/A'
    # 若存在有效行，为首次出现的行赋值原Bucket值
    if pd.notna(first_valid_pos):
        group.loc[first_valid_pos, 'New Bucket'] = group.loc[first_valid_pos, 'Bucket']
    return group

# 应用分组逻辑
df = df.groupby('Parent', group_keys=False).apply(process_parent_group)

# 移除临时辅助列（可选操作）
df = df.drop(columns='is_valid_bucket')

# 将结果写入Excel
df.to_excel("processed_output.xlsx", index=False)

代码逻辑说明 #

1. 标记有效行：用正则表达式匹配包含" to "的Bucket值，生成临时列is_valid_bucket标记符合格式的行。
2. 分组处理：按Parent分组后，找到每组内第一个有效行的索引，仅为该行的New Bucket赋值原Bucket值，其余行设为N/A。
3. 输出结果：移除临时列后，将处理好的数据写入Excel文件。

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