Summing two values from different dataframes if certain criteria is matched python

Question

I would like to sum two columns, each in different frame if certain criteria is met.

Dataframe 1:

desk    Type   total_position
desk1   ES     786.0
desk1   ES1    100
desk2   ES1    0
desk2   ES2    10
desk3   ES     0
desk4   ES1    0
desk5   ES     -757

Dataframe 2:

desk    Type total_position
desk1   ES   -758.0
desk2   ES    0
desk3   ES   -29
desk4   ES    0.0
desk5   ES    786.0

I would like to sum both the positions if only the type is "ES" in the first dataframe and it is the same desk.

How do i do that?

Expected Answer

desk    Type   total_position
desk1   ES     29
desk2   ES1    0
desk3   ES     -29
desk4   ES1    0
desk5   ES     29

Answer 1

I would map and then add:

df1['total_position'] = (df1['total_position'].add(
               df1['desk'].map(df2.set_index('desk')['total_position']))
print(df1)

---

    desk Type  total_position
0  desk1   ES            28.0
1  desk2  ES1             0.0
2  desk3   ES           -29.0
3  desk4  ES1             0.0
4  desk5   ES            29.0

EDIT for type:

m = (df1['desk'].map(df2.set_index('desk')['total_position'])
    .where(df1['Type'].eq('ES')).fillna(0))

df1['total_position'] = df1['total_position'].add(m)
print(df1)

    desk Type  total_position
0  desk1   ES            28.0
1  desk2  ES1             0.0
2  desk3   ES           -29.0
3  desk4  ES1             0.0
4  desk5   ES            29.0

Answer 2

Try this :

add via the index, and update the missing values from df1 using combine_first

df1.set_index('desk').add(df2.set_index('desk')).combine_first(df1.set_index('desk'))

NB: this works on the naive assumption that desk in df1 is same in df2.

      Type  total_position
desk        
desk1   ES      28.0
desk2   ES1     0.0
desk3   ES     -29.0
desk4   ES1     0.0
desk5   ES      29.0