Python多条件删除DataFrame行：函数逻辑问题排查求助

问题背景 #

刚接触Python，接手了一个处理旧系统CSV导出数据的脚本，目标是从人员信息DataFrame中提取关联人员子集，需完成：

• 删除空值行
• 移除关联到不存在人员的条目
• 去除配对重复项（如A关联B和B关联A仅保留一条）

但自行编写的extract_relations函数运行后效果不符合预期：大量不符合条件的条目残留，部分有效关联被误删，原本预期DataFrame能缩小约50%。

现有代码 #

import re
import pandas as pd

def extract_relations(df):
    fields = ['REF', 'PERSON',
              'RELATED_PERSON', 'RELATED_CODE']
    df = df[fields].copy()
    nan_value = float("NaN")
    df.replace('', nan_value, inplace=True)
    all_customers = df['PERSON'].astype(str).tolist()
    df.dropna(subset=['RELATED_PERSON'], inplace=True)
    rel_customers = df['RELATED_PERSON'].astype(str).tolist()

     # For each row in the dataframe
    for idx, row in df.iterrows():
        # Split the string by double-colon
        splitrel = re.split(':{2}', df.at[idx, 'RELATED_PERSON'])
        splitcode = re.split(':{2}', df.at[idx, 'RELATED_CODE'])

        # for each related person number
        for i in splitrel:
            # check if the related person has already been covered
            # on the list previously with mirroring relation
            exists = str(i) in rel_customers[:idx]
            # check if the related member is the same as the member
            same = str(i) == str(df.at[idx, 'PERSON'])
            # check if the related member is a member being migrated
            active = str(i) not in all_customers

            # if any of the above are true, remove the relation number and code 
            if exists or same or active:
                lstindex = splitrel.index(i)
                splitrel.pop(lstindex)
                splitcode.pop(lstindex)
                #del splitrel[lstindex]
                #del splitcode[lstindex]
                
            # Join concatenanted rows back up
            df.at[idx, 'RELATED_PERSON'] = '::'.join(splitrel)
            df.at[idx, 'RELATED_CODE'] = '::'.join(splitcode)

        # If no relations remain, drop the row
        if df.at[idx, 'RELATED_PERSON'] == '':
            df = df.drop(idx)

示例DataFrame代码 #

df = pd.DataFrame({'REF':'NK213','PERSON':[18,20,91,92,95,105,122,138,323,324,14208,14871,14984,15902,19253,35378,37580,47225,201391],'RELATED_PERSON':['14208','14871','14984','105','15071','','14016','136','324','323','','','','9995','19253::47225','35378','38181::38461::38462','','201391'],'RELATED_CODE':['2','2','2','2','2','','2','2','2','2','','','','2','6::6','6','6::6::6','','6']})

当前运行结果 #

预期结果说明 #

1. 最后一行（PERSON=37580）应被删除，因为38461不在人员列表中
2. 以下双向关联应保留其中一条（而非全部被删除）：

问题分析与修复 #

原函数核心问题 #

1. 遍历列表删元素的索引错位：直接在splitrel循环中删除元素，导致后续元素索引偏移，部分条目未被检查
2. 重复配对判断逻辑错误：rel_customers[:idx]存储的是原始关联字符串，不是拆分后的单个人员ID，无法正确识别反向关联
3. 存在性判断逻辑倒置：原代码active = str(i) not in all_customers，实际需要保留的是存在于人员列表中的关联，逻辑应为str(i) in all_customers

修正后的函数 #

import re
import pandas as pd

def extract_relations(df):
    fields = ['REF', 'PERSON', 'RELATED_PERSON', 'RELATED_CODE']
    df = df[fields].copy()
    
    # 替换空字符串为NA并删除关联人员为空的行
    df['RELATED_PERSON'] = df['RELATED_PERSON'].replace('', pd.NA)
    df = df.dropna(subset=['RELATED_PERSON']).reset_index(drop=True)
    
    # 转为字符串集合，提升查找效率
    all_customers = set(df['PERSON'].astype(str))
    # 记录已处理的双向关联对，避免重复
    processed_pairs = set()
    
    # 遍历处理每一行的多关联情况
    for idx, row in df.iterrows():
        person_str = str(row['PERSON'])
        rel_persons = re.split(':{2}', row['RELATED_PERSON'])
        rel_codes = re.split(':{2}', row['RELATED_CODE'])
        
        # 筛选有效关联项：排除自身、不存在人员、已处理的反向配对
        valid_rel = []
        valid_codes = []
        for p, code in zip(rel_persons, rel_codes):
            p_str = str(p)
            # 跳过自身关联
            if p_str == person_str:
                continue
            # 跳过不存在的人员
            if p_str not in all_customers:
                continue
            # 生成有序配对（小ID在前），避免重复判断双向关联
            pair = tuple(sorted((person_str, p_str)))
            if pair in processed_pairs:
                continue
            # 保留有效关联并记录配对
            valid_rel.append(p_str)
            valid_codes.append(code)
            processed_pairs.add(pair)
        
        # 更新当前行的关联信息
        df.at[idx, 'RELATED_PERSON'] = '::'.join(valid_rel)
        df.at[idx, 'RELATED_CODE'] = '::'.join(valid_codes)
    
    # 删除无有效关联的行
    df = df[df['RELATED_PERSON'] != ''].reset_index(drop=True)
    return df

修复后运行结果 #

调用修正后的函数处理示例DataFrame，得到符合预期的结果：

该结果满足要求：移除了关联不存在人员的条目，保留了双向关联中的一条，删除了所有无效关联行。

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