CODEWITHSUNDEEP
pythonpandaspandas-groupby
AmirX
AmirX

Reputation: 2717

groupby two columns in pandas

I have a dataframe like this:

df = pd.DataFrame({'sym': list('aabaabab'), 'dir':[0,0,0,1,0,1,1,1], 'price': [100, 101, 102, 110, 120, 125, 200, 250]})

   dir  price sym
0    0    100   a
1    0    101   a
2    0    102   b
3    1    110   a
4    0    120   a
5    1    125   b
6    1    200   a
7    1    250   b

I want to groupby sym and a set of 0 and 1(not sure if it is the right term to say it ! ). My desired outcome looks like this:

   dir  price sym
0    0    100   a
1    0    101   a
3    1    110   a
   dir  price sym
4    0    120   a
6    1    200   a
   dir  price sym
2    0    102   b
5    1    125   b
7    1    250   b

each time dir become 0 in each sym I want a new group with the 1s after that 0

Upvotes: 1

Views: 161

Answers (1)

BENY
BENY

Reputation: 323226

Using cumsum create another helpkey , then groupby 

df['helpkey']=df.groupby('sym').apply(lambda x : ((x['dir']==1)&(x['dir'].shift(-1)==0)).shift().fillna(0).cumsum()).reset_index(level=0,drop=True)
d={x: y for x , y in df.groupby(['helpkey','sym'])}

for x , y in df.groupby(['helpkey','sym']):
 print(y)

  sym  dir  price helpkey
0   a    0    100       0
1   a    0    101       0
3   a    1    110       0
  sym  dir  price helpkey
2   b    0    102       0
5   b    1    125       0
7   b    1    250       0
  sym  dir  price helpkey
4   a    0    120       1
6   a    1    200       1

Upvotes: 2

Related Questions