CODEWITHSUNDEEP
pythonpandasdataframe
lrh09
lrh09

Reputation: 587

pandas groupby and use numbers between the different types

Let say I have a df like this:

client   order_type    amount
John     Buy           100
John     Sell          100
Jeff     Buy           100
Jeff     Buy           100
Aaron    Buy           100
Aaron    Sell          100
Aaron    Buy           100

if I do:

df.groupby(['client','order_type'])['amount'].sum()

I would get something like:

John    Buy   100
        Sell  100
Jeff    Buy   100
        Sell  100
Aaron   Buy   200
        Sell  100

How to get something like Buy - Sell column in new dataframe:

Name      NetBuy
John      0
Jeff      200
Aaron     100

Upvotes: 2

Views: 66

Answers (2)

Yuca
Yuca

Reputation: 6091

Just map your order_type to a sign, there are many ways to do this, but the easiest to read in my opinion would be this:

df['sign'] = [1 if x == 'Buy' else -1 for x in df.order_type]
df['amount_adj'] = df.sign*df.amount
df.groupby(['client'])['amount_adj'].sum()

Output:

client
Aaron    100
Jeff     200
John       0

Same result using a one-liner and the faster np.where:

df = df.assign(amount=np.where(df.order_type.eq('Sell'), 
          df.amount*-1, df.amount)).groupby(['client'])['amount'].sum()

output:

client
Aaron    100
Jeff     200
John       0

Upvotes: 1

Erfan
Erfan

Reputation: 42916

First cast your sell values to negative, then use groupby.sum:

df['amount'] = np.where(df['order_type'].eq('Sell'), -df['amount'], df['amount'])

df.groupby('client', as_index=False)['amount'].sum()

  client  amount
0  Aaron     100
1   Jeff     200
2   John       0

Upvotes: 1

Related Questions