pandas column value update from another dataframe value

Question

I have following 2 dataframe

df_a = 
   id    val 
0  A100  11
1  A101  12
2  A102  13
3  A103  14
4  A104  15


df_b = 
   id    loc  val 
0  A100  12
1  A100  23
2  A100  32
3  A102  21
4  A102  38
5  A102  12
6  A102  18
7  A102  19
.....

desired result:

df_b = 
   id    loc  val 
0  A100  12   11
1  A100  23   11 
2  A100  32   11
3  A102  21   12
4  A102  38   12 
5  A102  12   12
6  A102  18   12
7  A102  19   12 
.....

When I try to update df_b's 'val' column by df_a's 'val' column like this,

for index, row in df_a.iterrows():
    v = row['val']
    seq = df_a.loc[df_a['val'] == v] 
    df_b.loc[df_b['val'] == v, 'val'] = seq['val']

or

df_x = df_b.join(df_a, on=['id'], how='inner', lsuffix='_left', rsuffix='_right')

However I could not solve this... How can I resolve this tricky things?

Thank you

jezrael · Accepted Answer · 2017-06-28 05:25:39Z

7

You can use map by Series created by set_index:

df_b['val'] = df_b['id'].map(df_a.set_index('id')['val'])
print (df_b)
     id  loc  val
0  A100   12   11
1  A100   23   11
2  A100   32   11
3  A102   21   13
4  A102   38   13
5  A102   12   13
6  A102   18   13
7  A102   19   13

Or merge with left join:

df = pd.merge(df_b,df_a, on='id', how='left')

print (df)
     id  loc  val
0  A100   12   11
1  A100   23   11
2  A100   32   11
3  A102   21   13
4  A102   38   13
5  A102   12   13
6  A102   18   13
7  A102   19   13

If only one common column id used for joining in both df is possible omi it.

df = pd.merge(df_b,df_a, how='left')
print (df)
     id  loc  val
0  A100   12   11
1  A100   23   11
2  A100   32   11
3  A102   21   13
4  A102   38   13
5  A102   12   13
6  A102   18   13
7  A102   19   13

answered Jun 28, 2017 at 5:25

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gabor Over a year ago

I know it is an old post, but even tough I like the map based solution it does not work as expected, as long as you only want to upgrade some of the rows. In my case all other rows of the left side not included in the right side selection is put tu NaN

Collectives™ on Stack Overflow

pandas column value update from another dataframe value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related