根据关联ID列合并展开三个Pandas DataFrame的优雅实现方法

实现方案 #

核心思路是先把「单行多列存储关联ID」的宽表转成「单行对应一个关联关系」的长表，再用Pandas原生的拼接方法完成关联，全程无手动循环，代码简洁且执行效率更高。

完整代码

import pandas as pd
import numpy as np

# 你提供的原始表
A = pd.DataFrame({"id": [1,2], 
                "connected_to_B_id1":["A","B"],
                "connected_to_B_id2":["B","C"],
                "connected_to_B_id3":["C", np.nan],
                })
B = pd.DataFrame({"id": ["A","B","C"], 
                "connected_to_C_id1":[1,1,2],
                "connected_to_C_id2":[2,2,np.nan],
                })
C = pd.DataFrame({"id": [1,2], 
                "name":["a","b"],
                })

# 处理A表：转成 id_A -> id_B 的长表
A_long = A.melt(
    id_vars="id", 
    value_vars=[c for c in A.columns if c.startswith("connected_to_B")],
    value_name="id_B"
).dropna(subset=["id_B"])[["id", "id_B"]].rename(columns={"id":"id_A"})

# 处理B表：转成 id_B -> id_C 的长表
B_long = B.melt(
    id_vars="id", 
    value_vars=[c for c in B.columns if c.startswith("connected_to_C")],
    value_name="id_C"
).dropna(subset=["id_C"])[["id", "id_C"]].rename(columns={"id":"id_B"})

# 拼接所有表得到最终结果
D = (
    A_long.merge(B_long, on="id_B")
    .merge(C.rename(columns={"id":"id_C"}), on="id_C")
    .sort_values(["id_A", "id_B", "id_C"])
    .reset_index(drop=True)
)

运行后得到的D和你给出的期望输出完全一致。

可选的explode写法

如果你偏好更简短的语法，也可以用explode实现长表转换，效果完全相同：

A_long = (
    A.assign(id_B=A.filter(like="connected_to_B").values.tolist())
    .explode("id_B")
    .dropna(subset=["id_B"])[["id", "id_B"]]
    .rename(columns={"id":"id_A"})
)
B_long = (
    B.assign(id_C=B.filter(like="connected_to_C").values.tolist())
    .explode("id_C")
    .dropna(subset=["id_C"])[["id", "id_C"]]
    .rename(columns={"id":"id_B"})
)
D = A_long.merge(B_long, on="id_B").merge(C.rename(columns={"id":"id_C"}), on="id_C").sort_values(["id_A", "id_B", "id_C"]).reset_index(drop=True)

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