使用Pandas检测提取基于邮箱的重复客户ID并去重

步骤1：准备数据与拆分邮箱 #

先将原始DataFrame中的邮箱列拆分并展开，方便后续识别重复邮箱对应的客户：

import pandas as pd

# 构造示例DataFrame
data = {
    'customer id': [1,2,3,4,5,6],
    'emails': ['john@email.com,john12@email.com', 'sara_john@email.com', 'sam@email.com,sam1900@email.com', 'sara@email.com', pd.NA, 'sam1900@email.com'],
    'country': ['US', 'CA', 'UK', 'US', pd.NA, 'UK']
}
df = pd.DataFrame(data)

# 拆分邮箱并展开，保留原始客户的所有信息
df_expanded = df.assign(emails=df['emails'].str.split(',')).explode('emails')
# 处理空值，避免后续筛选出错
df_expanded['emails'] = df_expanded['emails'].fillna('')

步骤2：识别重复邮箱及关联客户 #

找出所有被多个客户共享的邮箱，以及这些邮箱对应的客户ID：

# 筛选出现次数超过1的邮箱（即重复邮箱）
duplicate_emails = df_expanded['emails'].value_counts()[lambda x: x>1].index.tolist()
# 提取涉及重复邮箱的所有客户记录
df_duplicate_cust = df_expanded[df_expanded['emails'].isin(duplicate_emails)]

# 按邮箱分组，获取每个重复邮箱对应的客户ID列表
email_cust_map = df_duplicate_cust.groupby('emails')['customer id'].apply(list).reset_index(name='customer_ids')
# 只保留关联多个客户的邮箱记录
email_cust_map = email_cust_map[email_cust_map['customer_ids'].str.len() > 1]

步骤3：生成重复记录表 #

根据邮箱关联关系，构建记录重复客户的DataFrame：

duplicate_records = []
for _, row in email_cust_map.iterrows():
    shared_email = row['emails']
    cust_ids = row['customer_ids']
    # 取第一个客户作为基准，其余标记为重复项
    main_cust_id = cust_ids[0]
    main_cust_info = df[df['customer id'] == main_cust_id].iloc[0]
    
    for dup_cust_id in cust_ids[1:]:
        duplicate_records.append({
            'customer id': main_cust_id,
            'emails': main_cust_info['emails'],
            'country': main_cust_info['country'],
            'duplicate_id': dup_cust_id,
            'duplicate_email': shared_email
        })

duplicate_df = pd.DataFrame(duplicate_records)

步骤4：生成去重后主表 #

排除所有涉及重复邮箱的客户，得到最终去重后的主表：

# 提取所有存在重复关联的客户ID
cust_to_exclude = df_duplicate_cust['customer id'].unique()
# 筛选主表数据
main_df = df[~df['customer id'].isin(cust_to_exclude)].reset_index(drop=True)

验证输出 #

• 去重后主表（main_df）：

customer id                           emails country
0            1  john@email.com,john12@email.com      US
1            2              sara_john@email.com      CA
2            4                   sara@email.com      US
3            5                              NaN     NaN

• 重复记录表（duplicate_df）：

customer id                           emails country  duplicate_id duplicate_email
0            3  sam@email.com,sam1900@email.com      UK             6  sam1900@email.com

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