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:

id1value1value3id2value201001      ayes1001   rep111002      bno1002   rep221001      cyes1001   rep131003      dnoNaNNaN41004      enoNaNNaN51005      fnoNaNNaN61002      gyes1002   rep271006      hnoNaNNaNid1value1value3id2value201001   rep1yes1001   rep111002   rep2no1002   rep221001   rep1yes1001   rep131003      dnoNaNNaN41004      enoNaNNaN51005      fnoNaNNaN61002   rep2yes1002   rep271006      hnoNaNNaNid1value1value301001   rep1yes11002   rep2no21001   rep1yes31003      dno41004      eno51005      fno61002   rep2yes71006      hno

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')))value1value31001   rep1yes1001   rep1yes1002   rep2no1002   rep2yes1003      dno1004      eno1005      fno1006      hno

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)