Replace dataframe column values based on matching id in another dataframe

Question

I'm trying to replace values in a dataframe based on values in a different dataframe. Please assume there are other columns in both dataframes. A simple example:

df1

id    name  ......
123   city a
456   city b
789   city c
789   city c
456   city b
123   city a

so on and so forth

df2

id    name  ......
123   City A
456   City B
789   City C

So the resulting df should be:

id    name  ......
123   City A
456   City B
789   City C
789   City C
456   City B
123   City A

I tried out with a few merge options in pandas with no luck. Is there an easy way to replace all the values in the first dataframe with the values from the second dataframe based on a match on id?

Any help is appreciated

BENY · Accepted Answer · 2020-06-10 02:05:52Z

3

You can do

df1['New name']=df1.id.map(df2.set_index('id').name)

Update

namediff=df1.columns.difference(df2.columns).tolist()+['id']
df3=df2.drop(df2.columns.difference(df1.columns),axis=1)
df1=df1[namediff].merge(df3,on='id')

Or

df1=df1.set_index('id')
df1.update(df2.set_index('id'))
df1.reset_index(inplace=True)

edited Jun 10, 2020 at 2:05

answered Jun 10, 2020 at 1:35

BENY

324k22 gold badges176 silver badges250 bronze badges

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

10 Comments

mlenthusiast Over a year ago

I forgot to mention, there are other columns in both the dataframes, but all I'm concerned about is just matching on the id and the column names

BENY Over a year ago

@codingenthusiast check with merge

mlenthusiast Over a year ago

this will bring other columns into the dataframe as well

mlenthusiast Over a year ago

if df2 has other columns, wouldn't this merge into df1?

BENY Over a year ago

@codingenthusiast if that happen , check the update

|

Collectives™ on Stack Overflow

Replace dataframe column values based on matching id in another dataframe

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related