Add values in columns if criteria from another column is met-CodePudding

I have the following DataFrame

import pandas as pd 

d = {'Client':[1,2,3,4],'Salesperson':['John','John','Bob','Richard'], 
     'Amount':[1000,1000,0,500],'Salesperson 2':['Bob','Richard','John','Tom'],
     'Amount2':[400,200,300,500]}
df = pd.DataFrame(data=d)

Client	Salesperson	Amount	Salesperson	Amount2
1	John	1000	Bob	400
2	John	1000	Richard	200
3	Bob	0	John	300
4	Richard	500	Tom	500

And I just need to create some sort of "sumif" statement (the one from excel) that will add the amount each salesperson is due. I don't know how to iterate over each row, but I want to have it so that it adds the values in "Amount" and "Amount2" for each one of the salespersons.

Then I need to be able to see the amount per salesperson.

Expected Output (Ideally in a DataFrame as well)

Sales Person	Total Amount
John	2300
Bob	400
Richard	700
Tom	500

CodePudding user response：

There can be multiple ways of solving this. One option is to use Pandas Concat to join required columns and use groupby

merged_df = pd.concat([df[['Salesperson','Amount']], df[['Salesperson 2', 'Amount2']].rename(columns={'Salesperson 2':'Salesperson','Amount2':'Amount'})])
merged_df.groupby('Salesperson',as_index = False)['Amount'].sum()

either way you get

    Salesperson Amount
0   Bob         400
1   John        2300
2   Richard     700
3   Tom         500