Match a pandas Data frame (df1) row value to another Data frame (df2) column and update a rows of different column in Data frame (Df1)

Question

I have 2 data frame 1st (DF1) look like below.

    JID      JRSubUsageLabel_16 SUB_USAGE_16
22  6223    JR_BOne_CY16              NaN
26  6510    JR_S_CY16                 NaN
59  11932   JR_B_CY16                 NaN
70  14242   JR_B_CY16                NaN

2nd Data Frame (df2) looks like this

             JID    JR1_B_CY16  JR_CY16
1           1457    NaN             NaN
2           1530    NaN             NaN
3           1535    5               NaN
4           2035    NaN             NaN
5           6223    5               NaN
6           6510    1.0             6
39         11932    1.0             NaN
40         12021    NaN             NaN
41         12056    NaN             NaN
42         14234    2               1.0

I want to update my DF1 data frame base on the "JID" column and JR1SubUsageLabel_16 column value. JID is the matching column in both data frames. In DF1 "JR1SubUsageLabel_16" column values become DF2 columns. So, the DF2 one of the columns matches the DF1 "JR1SubUsageLabel_16" column values too. It should look like below.

    JID      JRLabel_16           SUB_USAGE_16
22  6223    JR1_B_CY16            5
26  6510    JR1_S_CY16            6
59  11932   JR1_B_CY16            1
70  14242   JR1_B_CY16            2

I was trying to update this using lambda and mapping couldn't figure exactly how to update this. Could anyone help me??

Thanks in advance

Answer 1

One way is merge:

s = df2.melt('JID',value_name='SUB_USAGE_16',var_name='JR1SubUsageLabel_16')
df1.drop('SUB_USAGE_16', axis=1).merge(s, on = ['JID','JR1SubUsageLabel_16'], how='left')

Output:

     JID JR1SubUsageLabel_16  SUB_USAGE_16
0   6223     JR1_BioOne_CY16           5.0
1   6510   JR1_Springer_CY16           6.0
2  11932     JR1_BioOne_CY16           1.0
3  14242     JR1_BioOne_CY16           NaN