Pandas基于多个匹配的列值合并2个数据帧

Question

第一个数据帧df1：

seq  id                a1        a2   
12  209981             None    None
12  209982            Funds    None
13  209983      Free_Income    None
13  209984      Free_Income    None
14  209985      Free_Income  Hybrid

我的第二个数据帧df2：

^{pr2}$

现在我想要以下格式的输出

seq  id                a1          a2    p1    p2   p3   p4
12  209981             None      None   None  None  None None 
12  209982            Funds      None   5.71  1.09  None None 
13  209983      Free_Income      None   2.53  2.64  None None
13  209984      Free_Income      None   2.53  2.64  None None
14  209985      Free_Income    Hybrid   4.53  2.25  1.89 2.45

映射是

df1.seq=df2.seq

df1.a1=df2.a1

df1.a2=df2.a1

Answer 1

你想合并两次。第一个合并集中在左数据帧中的a1，右数据帧中的a1。第二次合并将焦点从左数据帧切换到a2。在

df1.merge(
    df2,
    left_on=['seq', 'a1'],
    right_on=['seq', 'a1'],
    how='left'
).join(
    df1.merge(
        df2,
        left_on=['seq', 'a2'],
        right_on=['seq', 'a1'],
        how='left'
    )[['p1', 'p2']].rename(columns=dict(p1='p3', p2='p4'))
)

   seq      id           a1      a2    p1    p2    p3    p4
0   12  209981         None    None   NaN   NaN   NaN   NaN
1   12  209982        Funds    None  5.71  1.09   NaN   NaN
2   13  209983  Free_Income    None  2.53  2.64   NaN   NaN
3   13  209984  Free_Income    None  2.53  2.64   NaN   NaN
4   14  209985  Free_Income  Hybrid  4.53  2.25  1.89  2.45

---