Elegant Way To Replace Values In Pandas.DataFrame From Another DataFrame

I have a data frame that I want to replace the values in one column, with values from another dataframe. df = pd.DataFrame({'id1': [1001,1002,1001,1003,1004,1005,1002,1006],

Solution 1:

try merge():

merge = df.merge(dfReplace, left_on='id1', right_on='id2', how='left')
print(merge)

merge.ix[(merge.id1 == merge.id2), 'value1'] = merge.value2
print(merge)

del merge['id2']
del merge['value2']
print(merge)

Output:

    id1 value1 value3   id2 value2
0  1001      a    yes  1001   rep1
1  1002      b     no  1002   rep2
2  1001      c    yes  1001   rep1
3  1003      d     no   NaN    NaN
4  1004      e     no   NaN    NaN
5  1005      f     no   NaN    NaN
6  1002      g    yes  1002   rep2
7  1006      h     no   NaN    NaN

    id1 value1 value3   id2 value2
0  1001   rep1    yes  1001   rep1
1  1002   rep2     no  1002   rep2
2  1001   rep1    yes  1001   rep1
3  1003      d     no   NaN    NaN
4  1004      e     no   NaN    NaN
5  1005      f     no   NaN    NaN
6  1002   rep2    yes  1002   rep2
7  1006      h     no   NaN    NaN

    id1 value1 value3
0  1001   rep1    yes
1  1002   rep2     no
2  1001   rep1    yes
3  1003      d     no
4  1004      e     no
5  1005      f     no
6  1002   rep2    yes
7  1006      h     no

Solution 2:

This is a little cleaner if you already have the indexes set to id, but if not you can still do in one line:

>>> (dfReplace.set_index('id2').rename( columns = {'value2':'value1'} )
                               .combine_first(df.set_index('id1')))

     value1 value3
1001   rep1    yes
1001   rep1    yes
1002   rep2     no
1002   rep2    yes
1003      d     no
1004      e     no
1005      f     no
1006      h     no

If you separate into three lines and do the renaming and re-indexing separately, you can see that the combine_first() by itself is actually very simple:

>>> df = df.set_index('id1')
>>> dfReplace = dfReplace.set_index('id2').rename( columns={'value2':'value1'} )

>>> dfReplace.combine_first(df)