Multi Level Pandas Groupby

I need to maintain position of 'each scrip per team per account'. So I think pandas groupby might be used. I have tried to state my problem in comment of dataframe aswell. The posi

Solution 1:

Your problem can be solved easily in two steps:

First Step:

import math
df['some_stuff'] = df.apply(lambda x: -x.qty ifmath.isnan(x.buy_price) else x.qty,axis=1)

this line is creating a new column some_stuff, why I did this is just to introduce some logic of gain and loss in your data.

if you don't want a new column and you like the idea just replace qty column with it like this:

df['qty'] = df.apply(lambda x: -x.qty if math.isnan(x.buy_price) else x.qty,axis=1)

next, I use this new column to create your position column as follows:

df['position'] = df.groupby(['team','account','scrip'])['some_stuff'].cumsum()

which generates this column:

position22201
      -1

bonus:

if you want to delete the extra column some_stuff just use:

del df['some_stuff']

Second Step:

This is the step where you get your final grouped table with this line:

print(df.groupby(['team', 'account', 'scrip']).min())

final output:

time  buy_price  sell_price  qty  position
team  account scrip                                                      
team1 A1      FUT1   06/07/1709:3650.050.011
      A2      FUT1   06/07/1709:46100.0         NaN    22
team2 A3      FUT1   06/07/1709:5610.010.01        -1

I believe this answers your questions.

Documentation:

pandas.DataFrame.apply

pandas.Groupby

pandas.DataFrame.cumsum

pandas.DataFrame.min

Solution 2:

is this what you're looking for?

df.groupby(['team', 'account', 'scrip']).min()

it gives me:

time  buy_price  sell_price  qty  position
team  account scrip                                             
team1 A1      FUT1   09:3650.050.011
      A2      FUT1   09:46100.0         NaN    22
team2 A3      FUT1   09:5610.010.01        -1

that's a few more columns than you wanted but you can subset out what you're looking for.

(groupby by default moves the grouped columns to a multilevel index, but if this isn't what you want you can add as_index=False as an arg in the .groupby())