Pandas merge 2 data frames 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 df2 data frame:

   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 result in the format below

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

Display

df1.seq = df2.seq

df1.a1 = df2.a1

df1.a2 = df2.a1

Answer 1

You want to combine twice. The first merge focuses on a1the left data frame and a1the correct data frame. Second merge: you switch focus to a2from the left frame.

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

---