基于A/B/C/E列去重并按规则修改E值及添加REMARKS列的需求

需求说明 #

针对包含A、B、C、D、E列的数据集，需执行以下操作：

• 基于A、B、C、E列识别重复行
• 对每个重复组：
  • 若组内首个唯一行的D值为ABC，将该行E值设为0
  • 组内其余重复行的E值统一设为0，确保每组仅保留1行非0的E值
• 添加REMARKS列，标记E值被修改过的行

输入数据集 #

A           B           C   D   E
ARN-0098    50732536    61  FD  0
ARN-0098    50732536    61  ABC 10456
ARN-0098    65363672    61  FD  10447
ARN-0098    65363672    61  ABC 10447
ARN-0098    65363672    61  FD  10447
ARN-0098    65363672    61  FD  10447
ARN-0098    55297611    61  ABC 10424
ARN-0098    55297611    61  FD  10424
ARN-0098    55297611    61  FD  10424
ARN-0098    57039959    61  FD  10434
ARN-0098    57039959    61  FD  10434
ARN-0098    58224047    61  FD  10429
ARN-0098    58224047    61  FD  10429
ARN-0098    59780609    61  ABC 10423
ARN-0098    59780609    61  ABC 10423
ARN-0098    59780609    61  FD  10423

实现代码（Python Pandas） #

import pandas as pd

# 构造输入数据集
data = pd.DataFrame([
    ["ARN-0098", 50732536, 61, "FD", 0],
    ["ARN-0098", 50732536, 61, "ABC", 10456],
    ["ARN-0098", 65363672, 61, "FD", 10447],
    ["ARN-0098", 65363672, 61, "ABC", 10447],
    ["ARN-0098", 65363672, 61, "FD", 10447],
    ["ARN-0098", 65363672, 61, "FD", 10447],
    ["ARN-0098", 55297611, 61, "ABC", 10424],
    ["ARN-0098", 55297611, 61, "FD", 10424],
    ["ARN-0098", 55297611, 61, "FD", 10424],
    ["ARN-0098", 57039959, 61, "FD", 10434],
    ["ARN-0098", 57039959, 61, "FD", 10434],
    ["ARN-0098", 58224047, 61, "FD", 10429],
    ["ARN-0098", 58224047, 61, "FD", 10429],
    ["ARN-0098", 59780609, 61, "ABC", 10423],
    ["ARN-0098", 59780609, 61, "ABC", 10423],
    ["ARN-0098", 59780609, 61, "FD", 10423],
], columns=["A", "B", "C", "D", "E"])

# 保存原始E值用于对比
data['original_E'] = data['E'].copy()
# 按A、B、C、E分组生成组ID
data['group_id'] = data.groupby(['A', 'B', 'C', 'E']).ngroup()

# 定义组内处理逻辑
def process_group(group):
    group['REMARKS'] = False
    first_row = group.iloc[0]
    
    # 处理首个行D为ABC的情况
    if first_row['D'] == 'ABC':
        group.loc[group.index[0], 'E'] = 0
        group.loc[group.index[0], 'REMARKS'] = True
    
    # 筛选组内非0原始E值的行，优先保留FD类型的行
    non_zero_rows = group[group['original_E'] != 0]
    if len(non_zero_rows) > 0:
        fd_rows = non_zero_rows[non_zero_rows['D'] == 'FD']
        keep_idx = fd_rows.index[0] if len(fd_rows) > 0 else non_zero_rows.index[0]
        
        # 将其余行E设为0并标记修改状态
        modify_idx = group.index != keep_idx
        group.loc[modify_idx, 'E'] = 0
        group.loc[modify_idx, 'REMARKS'] = group.loc[modify_idx, 'original_E'] != 0
    
    return group

# 分组处理后整理结果
processed_data = data.groupby('group_id').apply(process_group).reset_index(drop=True)
processed_data = processed_data.drop(['group_id', 'original_E'], axis=1)

# 打印结果
print(processed_data.to_string(index=False))

输出结果 #

A                 B             C            D      E   REMARKS
ARN-0098        50732536        61          FD      0       False
ARN-0098        50732536        61         ABC  10456       False
ARN-0098        65363672        61          FD  10447       False
ARN-0098        65363672        61         ABC      0        True
ARN-0098        65363672        61          FD      0        True
ARN-0098        65363672        61          FD      0        True
ARN-0098        55297611        61         ABC      0        True
ARN-0098        55297611        61          FD  10424       False
ARN-0098        55297611        61          FD      0        True
ARN-0098        57039959        61          FD  10434       False
ARN-0098        57039959        61          FD      0        True
ARN-0098        58224047        61          FD  10429       False
ARN-0098        58224047        61          FD      0        True
ARN-0098        59780609        61         ABC      0        True
ARN-0098        59780609        61         ABC      0        True
ARN-0098        59780609        61          FD  10423       False

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