基于列的行重复项检测逻辑代码改造需求

• 重复判定规则：同一参考编号（FARM REF NUMBER）下，只要某一行的任意一列的值，在同参考编号的其他行的同行列中存在重复，就标记该行存在重复。
• 标记格式：True(证书编号1/证书编号2/.../批次1/批次2/.../重复列名1,重复列名2...)，无重复则标记为False。
• 示例逻辑：参考编号PAP123的SNO1和SNO3行在CUTTING列值重复，因此这两行的DUPLICATION列标记为True(证书号1/证书号3/批次1/批次3/CUTTING)；SNO2和SNO4行虽在WEEDING列值重复，但参考编号不同，不标记重复。

原代码 #

duplicate_mask = combined_data.duplicated(subset=[
                'SLASHING BEFORE LINING & PEGGING', 'CUTTING OF PEGS', 'LINING & PEGGING',
                'PLANTAIN /ECO TREE-HOLING', 'COCOA-HOLING', 'PLANTAIN /ECO TREE-PLANTING',
                'COCOA-PLANTING', 'Portage of plantain seedlings'
            ], keep=False)

            for index, row in combined_data.iterrows():
                same_values = combined_data[
                    (combined_data['SLASHING BEFORE LINING & PEGGING'] == row['SLASHING BEFORE LINING & PEGGING']) &
                    (combined_data['CUTTING OF PEGS'] == row['CUTTING OF PEGS']) &
                    (combined_data['LINING & PEGGING'] == row['LINING & PEGGING']) &
                    (combined_data['PLANTAIN /ECO TREE-HOLING'] == row['PLANTAIN /ECO TREE-HOLING']) &
                    (combined_data['COCOA-HOLING'] == row['COCOA-HOLING']) &
                    (combined_data['PLANTAIN /ECO TREE-PLANTING'] == row['PLANTAIN /ECO TREE-PLANTING']) &
                    (combined_data['COCOA-PLANTING'] == row['COCOA-PLANTING']) &
                    (combined_data['Portage of plantain seedlings'] == row['Portage of plantain seedlings'])
                ]

                same_farm_ref = same_values[same_values['FARM REF NUMBER'] == row['FARM REF NUMBER']]

                if len(same_farm_ref) > 1:
                    certs = '/'.join(str(cert) for cert in combined_data.loc[same_values, 'CERT NUMBER'])
                    batches = '/'.join(str(batch) for batch in combined_data.loc[same_values, 'Batch'])
                    combined_data.loc[index, 'Duplicates'] = f"True({certs}/{batches})"
                else:
                    false_value = False
                    combined_data.loc[index, 'Duplicates'] = false_value

改造后的代码 #

# 定义需要检查重复的目标列
check_columns = [
    'SLASHING BEFORE LINING & PEGGING', 'CUTTING OF PEGS', 'LINING & PEGGING',
    'PLANTAIN /ECO TREE-HOLING', 'COCOA-HOLING', 'PLANTAIN /ECO TREE-PLANTING',
    'COCOA-PLANTING', 'Portage of plantain seedlings'
]

# 初始化Duplicates列为False
combined_data['Duplicates'] = False

# 按FARM REF NUMBER分组，仅在同一参考编号内检测重复
for farm_ref, group in combined_data.groupby('FARM REF NUMBER'):
    # 遍历分组内的每一行
    for idx, row in group.iterrows():
        duplicate_cols = []
        # 检查每个目标列是否在分组内存在重复值（排除当前行）
        for col in check_columns:
            same_col_count = len(group[(group[col] == row[col]) & (group.index != idx)])
            if same_col_count > 0:
                duplicate_cols.append(col)
        
        if duplicate_cols:
            # 获取同分组内所有相关重复行的证书号和批次号
            duplicate_rows = group[
                group.apply(lambda g_row: any(g_row[col] == row[col] for col in duplicate_cols), axis=1)
            ]
            certs = '/'.join(str(cert) for cert in duplicate_rows['CERT NUMBER'].unique())
            batches = '/'.join(str(batch) for batch in duplicate_rows['Batch'].unique())
            cols_str = ','.join(duplicate_cols)
            # 按要求格式生成标记内容
            combined_data.loc[idx, 'Duplicates'] = f"True({certs}/{batches}/{cols_str})"

代码说明 #

1. 按FARM REF NUMBER分组，严格限定仅在同一参考编号范围内检测重复
2. 逐行逐列检查，收集当前行所有存在重复值的列名
3. 若存在重复列，汇总所有相关重复行的证书号、批次号，按规则生成标记文本
4. 无重复列的行保持False标记

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