How to perform addition of two dataframe columns based on condition-CodePudding

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.

CodePudding user response：

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