I have two dataframes with different sizes and I am trying to perform an addition of values from a common column found in both dataframes based on matching values in another common column. My first dataframe looks like this:
df1
ID name region earnings 101 Joseph A 100 102 Adam A 200 103 Clint C 150 104 Mark C 60 105 Michael A 0
df2 looks like this
ID earnings 101 20 103 40 105 60
My expected results is
ID name region earnings 101 Joseph A 120 102 Adam A 200 103 Clint C 190 104 Mark C 60 105 Michael A 60
How would I go about this please? My first thought was to iterate over every line but then I read that this is discouraged as it has poor performance.
Advertisement
Answer
You can use map
+ set_index
+ fillna
:
df1['earnings'] += df1['ID'].map(df2.set_index('ID')['earnings']).fillna(0).astype(int)
Output:
>>> df1 ID name region earnings 0 101 Joseph A 120 1 102 Adam A 200 2 103 Clint C 190 3 104 Mark C 60 4 105 Michael A 60