Pandas: replace values in one dataframe with values from another dataframe based on two columns

Question

I have two dataframes:

d1 = {'id_': ['a','b','c','d'],
     'year':['2018','2019','2017','2019']}
d2 = {'id_': ['a','c','e'],
     'year':['2015',NaN,'2012']}
test1 = pd.DataFrame(d1)
test2 = pd.DataFrame(d2)


    id_ year
0   a   2018
1   b   2019
2   c   2017
3   d   2019

    id_ year
0   a   2015
1   c   None
2   e   2012

I need to replace year values in test1 with year values from test2 only when id_ match. If the value is NaN, I'd like to keep the old value.

So the result looks like:

        id_ year
    0   a   2015
    1   b   2019
    2   c   2017
    3   d   2019

All answers I came across were based on index or mapping old values to new values using dictionaries. I will appreciate your help.

@QuangHoang won't update test1 with test2. IanS, will add the unwanted id=e in the result. — cs95
– cs95, Commented Jun 21, 2019 at 16:17
@cs95 assuming the same index test1.fillna(test1,inplace=True) doesn't work? — Quang Hoang
– Quang Hoang, Commented Jun 21, 2019 at 16:40
@QuangHoang No, because that won't replace (for example) 2018 with 2015 for index a? — cs95
– cs95, Commented Jun 21, 2019 at 16:41

cs95 · Accepted Answer · 2019-06-21 16:16:53Z

4

Let's use concat and drop_duplicates here.

test3 = test2[test2['id_'].isin(test1['id_'])].dropna()
pd.concat([test1, test3]).drop_duplicates('id_', keep='last')   

  id_  year
1   b  2019
2   c  2017
3   d  2019
0   a  2015

Here's a merge-based alternative.

test3 = test1.merge(test2, on='id_', how='left')
test3['year'] = test3.pop('year_y').fillna(test3.pop('year_x'))
test3

  id_  year
0   a  2015
1   b  2019
2   c  2017
3   d  2019

answered Jun 21, 2019 at 16:16

cs95

406k106 gold badges744 silver badges797 bronze badges

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

1 Comment

aviss Over a year ago

I guess both answers work but I used concat and drop. Thanks!

BENY · Accepted Answer · 2019-06-21 16:16:48Z

4

Using update

test1=test1.set_index('id_')
test1.update(test2.set_index('id_'))
test1.reset_index(inplace=True)
test1
Out[582]: 
  id_  year
0   a  2015
1   b  2019
2   c  2017
3   d  2019

answered Jun 21, 2019 at 16:16

BENY

324k22 gold badges176 silver badges250 bronze badges

Collectives™ on Stack Overflow

Pandas: replace values in one dataframe with values from another dataframe based on two columns

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related