Pandas merge 2 dataframes based on multiple matching column values

Question

First dataframe 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

and my second dataframe df2 :

   seq              a1     p1    p2     
   12              Funds  5.71  1.09  
   12        Free_Income  2.18  3.17  
   12             Hybrid  2.88  3.70
   13        Free_Income  2.53  2.64  
   13              Funds  7.08  3.09 
   13             Hybrid  7.28  3.99  
   14        Free_Income  4.53  2.25  
   14             Hybrid  1.89  2.45  
   14              Funds  1.13  2.35  

Now I want the output in below format

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

The mapping is

df1.seq = df2.seq

df1.a1=df2.a1

df1.a2=df2.a1

Answer 1

You want to merge twice. The first merge focus on a1 in the left dataframe and a1 in the right dataframe. The second merge you switch focus to a2 from the left dataframe.

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

---