CODEWITHSUNDEEP
pythonpython-3.xpandas
aviss
aviss

Reputation: 2449

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

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.

Upvotes: 2

Views: 93

Answers (2)

cs95
cs95

Reputation: 403050

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

Upvotes: 4

BENY
BENY

Reputation: 323366

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

Upvotes: 4

Related Questions