如何使用Pandas基于含NaN的ig_handle/email列识别重复并合并数据？

问题描述 #

现有包含重复人员记录的DataFrame，需通过ig_handle或email识别重复项（例如同名但ig_handle不同的john属于不同个体，不合并），合并同一组内的互补非空数据，消除重复行，得到目标结果。

原始数据 #

import pandas as pd
import numpy as np

colnames = ["person", "ig_handle", "email", "phone_number"]
data = [
    ["emma", '@emma', 'emma@gmail.com', np.nan],
    ["emma", '@emma', np.nan, "09-emma-number"],
    ["jean", '@jean', "jean@gmail.com", np.nan],
    ["jean", '@jean', np.nan, np.nan],
    ["kate", '@kate', "kate@gmail.com", np.nan],
    ["john", '@john1', "john1@gmail.com", "09-john-number"],
    ["john", '@john1', np.nan, "09-john-number-new"],
    ["john", np.nan, "john1@gmail.com", np.nan],
    ["john", '@john2', np.nan, np.nan],
    ["lily", np.nan, np.nan, np.nan],
]

df = pd.DataFrame(data, columns=colnames)

解决方案 #

方法1：利用连通分量分组（需networkx） #

通过将ig_handle和email视为图的节点，共享节点的记录属于同一连通分量，以此为分组依据合并数据：

import networkx as nx

# 构建图，关联ig_handle和email节点
G = nx.Graph()
for idx, row in df.iterrows():
    nodes = []
    if pd.notna(row['ig_handle']):
        nodes.append(row['ig_handle'])
    if pd.notna(row['email']):
        nodes.append(row['email'])
    if nodes:
        G.add_nodes_from(nodes)
        if len(nodes) == 2:
            G.add_edge(nodes[0], nodes[1])
    else:
        # 处理全空记录，分配唯一节点
        empty_node = f"empty_{idx}"
        G.add_node(empty_node)
        G.add_edge(empty_node, empty_node)

# 为每条记录匹配连通分量ID
def get_component_id(row):
    if pd.notna(row['ig_handle']):
        return ','.join(sorted(nx.node_connected_component(G, row['ig_handle'])))
    elif pd.notna(row['email']):
        return ','.join(sorted(nx.node_connected_component(G, row['email'])))
    else:
        return ','.join(sorted(nx.node_connected_component(G, f"empty_{row.name}")))

df['group_key'] = df['person'] + '_' + df.apply(get_component_id, axis=1)

# 定义聚合函数：取列中最后一个非空值
def last_non_null(series):
    non_null = series.dropna()
    return non_null.iloc[-1] if not non_null.empty else np.nan

# 分组聚合合并数据
result_df = df.groupby('group_key').agg({
    'person': 'first',
    'ig_handle': last_non_null,
    'email': last_non_null,
    'phone_number': last_non_null
}).reset_index(drop=True)[colnames]

方法2：仅用Pandas分组填充 #

无需额外库，通过分组填充缺失的ig_handle和email，再合并数据：

def last_non_null(series):
    non_null = series.dropna()
    return non_null.iloc[-1] if not non_null.empty else np.nan

def process_person(group):
    # 用同email的ig_handle填充缺失值
    group['ig_handle'] = group.groupby('email')['ig_handle'].transform(lambda x: x.ffill().bfill())
    # 用同ig_handle的email填充缺失值
    group['email'] = group.groupby('ig_handle')['email'].transform(lambda x: x.ffill().bfill())
    # 生成分组键：非空用(ig_handle, email)，全空用索引
    group['subgroup'] = group.apply(
        lambda row: (row['ig_handle'], row['email']) if pd.notna(row['ig_handle']) or pd.notna(row['email']) else row.name,
        axis=1
    )
    return group.groupby('subgroup').agg(last_non_null).reset_index(drop=True)

# 按person分组处理后合并
result_df = df.groupby('person').apply(process_person).reset_index(drop=True)[colnames]

最终结果 #

运行上述代码后，result_df即为目标输出：

person ig_handle            email      phone_number
0   emma     @emma  emma@gmail.com  09-emma-number
1   jean     @jean  jean@gmail.com             NaN
2   kate     @kate  kate@gmail.com             NaN
3   john    @john1  john1@gmail.com  09-john-number-new
4   john    @john2              NaN             NaN
5   lily       NaN              NaN             NaN

内容的提问来源于stack exchange，提问作者Ye' Thura Ag