Skip to content
Advertisement

How to Subtract rows after group by in Python?

I had a dataframe and after applying groupby().sum, I got this outcome.

What I have

Market     Type      Amount                                   
A          Buy        46.56
B          Buy       100.00
           Sell      100.00
C          Buy       697.20
D          Buy       100.00 <--
           Sell       40.00 <--
...        ...        ...

What I want now

Market     Type      Amount                                   
A          Buy        46.56
C          Buy       697.20
D          Buy        60.00 <--
...        ...         ...

Things to consider

  • B should remove from the dataframe because 100.00 - 100.00 = 0
  • Always Buy Amount > Sell Amount

How can I achieve this result?

Advertisement

Answer

I guess it is not optimized way, but you can try this code block;

idx = df[df["Type"] == 'Sell'].index
df.loc[idx, 'Amount'] = df.loc[idx, 'Amount'] * (-1) 

df = df.groupby(['Market'], as_index=False).agg({'Type': 'min', 'Amount': 'sum'})
df = df.loc[~((df['Amount'] == 0))] 
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement