CODEWITHSUNDEEP
pythonpandas
Ahmad Shadid
Ahmad Shadid

Reputation: 51

Aggregation of pandas rows

I am trying to parse and organize a trading history file.
I am trying to aggregate every 3 or 4 rows that has the same Type: BUY or SELL together if they come after each other only. and if they don't then I want to take only one row.
as you can see the example below those multi-buy trades I want them to be aggregated within one row which after it will come another one row of sell trade.
in a new df with aggregated trades prices, and amounts. link for csv: https://drive.google.com/file/d/1GoDRdI7G8uJzuLoFrm5InbDg23mAwW6o/view?usp=sharing enter image description here

Upvotes: 1

Views: 206

Answers (1)

XXavier
XXavier

Reputation: 1226

You can use this to get the results you are looking for. I am using cumulative sum when the previous value is not equal to the current value.

dictionary = { "BUY": 1, "SELL": 0}
df['id1'] = df['Type'].map(dictionary)
df['grp'] = (df['id1']!=df['id1'].shift()).cumsum()

Now you can aggregate the values using a simple groupby like below. This will sum the amount for each consecutive buy and sell df.groupby(['grp'])['Amount'].sum()

This is the output of grp column.

Type    grp
0   BUY 1
1   BUY 1
2   BUY 1
3   BUY 1
4   SELL    2
5   SELL    2
6   SELL    2
7   SELL    2
8   BUY 3
9   SELL    4
10  BUY 5
11  SELL    6
12  BUY 7
13  SELL    8
14  BUY 9
15  BUY 9
16  SELL    10
17  SELL    10

Upvotes: 1

Related Questions