Add values in columns if criteria from another column is met

Question

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

Please also post your expected output.

Mayank Porwal
– Mayank Porwal

2022-02-03 17:27:41 +00:00
Commented Feb 3, 2022 at 17:27 — Mayank Porwal
– Mayank Porwal, Commented Feb 3, 2022 at 17:27
Okay I added it.

Andres Green
– Andres Green

2022-02-03 17:32:31 +00:00
Commented Feb 3, 2022 at 17:32 — Andres Green
– Andres Green, Commented Feb 3, 2022 at 17:32

Vaishali · Accepted Answer · 2022-02-04 17:27:09Z

3

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()

you get

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

Edit: If you have another pair of salesperson/amount, you can add that to the concat

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], 'Salesperson 3':['Nick','Richard','Sam','Bob'],
     'Amount3':[400,800,100,400]}
df = pd.DataFrame(data=d)

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

    Salesperson Amount
0   Bob         800
1   John        2300
2   Nick        400
3   Richard     1500
4   Sam         100
5   Tom         500

Edit 2: Another solution using pandas wide_to_long

df = df.rename({'Salesperson':'Salesperson 1','Amount':'Amount1'}, axis='columns')
reshaped_df = pd.wide_to_long(df, stubnames=['Salesperson','Amount'], i='Client',j='num', suffix='\s?\d+').reset_index(drop = 1)

The above will reshape df,

    Salesperson Amount
0   John    1000
1   John    1000
2   Bob     0
3   Richard 500
4   Bob     400
5   Richard 200
6   John    300
7   Tom     500
8   Nick    400
9   Richard 800
10  Sam     100
11  Bob     400

A simple groupby on reshaped_df will give you required output

reshaped_df.groupby('Salesperson', as_index = False)['Amount'].sum()

edited Feb 4, 2022 at 17:27

answered Feb 3, 2022 at 17:54

Vaishali

38.5k5 gold badges62 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Andres Green Over a year ago

This isn't working, for some reason it's only adding some of the values and not all of them.

Vaishali Over a year ago

What values is it not adding? Is there a mismatch in column names between your actual dataframe and the one you shared here?

Andres Green Over a year ago

1000 for John. It's only giving me 1300. If the actual values I have are floats and not ints could that affect something?

Andres Green Over a year ago

I think I figured out what is happening. If the name is consecutive it won't add it. John appears consecutively in row 1 and row 2, so it ignores the second row for some reason.

Andres Green Over a year ago

The second one works!

|

sammywemmy · Accepted Answer · 2022-02-03 20:59:15Z

One option is to tidy the dataframe into long form, where all the Salespersons are in one column, and the amounts are in another, then you can groupby and get the aggregate.

Let's use pivot_longer from pyjanitor to transform to long form:

# pip install pyjanitor
import pandas as pd
import janitor
(df
.pivot_longer(
     index="Client",
     names_to=".value",
     names_pattern=r"([a-zA-Z]+).*",
     )
.groupby("Salesperson", as_index = False)
.Amount
.sum()
)

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

The .value tells the function to keep only those parts of the column that match it as headers. The columns have a pattern (They start with a text - either Salesperson or Amount - and either have a number at the end ( or not). This pattern is captured in names_pattern. .value is paired with the regex in the brackets, those outside do not matter in this case.

Once transformed into long form, it is easy to groupby and aggregate. The as_index parameter allows us to keep the output as a dataframe.

Collectives™ on Stack Overflow

Add values in columns if criteria from another column is met

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related