Python - Update NaN values in df with values from other df [duplicate]

i have a table in pandas df

      main_id       p_id_y       score
1       1            123        0.617523
0       2            456        0.617523
0       3            789        NaN
0       4            987        NaN
1       5            654        NaN

also i have another dataframe df2. which has the column's

p_id   score
 123    1.3
 456    4.6
 789    0.4
 987    1.1
 654    3.2

i have to fill all the scores for all p_id_y which is NaN with the respective score of p_id in df2.

my final output should be.

      main_id       p_id_y       score
1       1            123        0.617523
0       2            456        0.617523
0       3            789        0.4
0       4            987        1.1
1       5            654        3.2

Any ideas how to achieve that? i was thinking to use this

df['score'] = df['score'].fillna(something)

Solution 1:

I think you can use combine_first or fillna, but first set_index for align data:

df1 = df1.set_index('p_id_y')
df1['score'] = df1['score'].combine_first(df2.set_index('p_id')['score'])
#df1['score'] = df1['score'].fillna(df2.set_index('p_id')['score'])

print (df1.reset_index())
   p_id_y  main_id     score
0     123        1  0.617523
1     456        2  0.617523
2     789        3  0.400000
3     987        4  1.100000
4     654        5  3.200000

Solution 2:

use fillna and join

df.fillna(df[['p_id_y']].join(df2.set_index('p_id'), on='p_id_y'))