Creating column in python based on the other colums value count

[Bartek635]

Hello everyone!

Can someone help me, what code should I use to create a new column which will display cumulatively the number of preceding rows in which "0" appeared in the column NumEvents. The counting should reset and begin from 0 again every time a new record in the column USERS_PK1 appears.
from:



to smth like this:


Thanks in advance!

[klllmmm]

Hope this helps

import pandas as pd
import numpy as np


df1 = pd.DataFrame(data = {'USERS_PK1':[550395,550395,550395,550395,550395,931192,931192,931192,931192,931192],
                             'ACADEMIC_WEEK':[2,3,4,5,6,2,3,4,5,6],
                             'NumEvents':[0,14,9,0,1,0,0,3,2,0],})


df1['Zero'] = (((df1['USERS_PK1']==df1['USERS_PK1'].shift(1)) | (pd.isnull(df1['USERS_PK1'].shift(1)))) & (df1['NumEvents']==0)).cumsum()

Output:
   USERS_PK1  ACADEMIC_WEEK  NumEvents  Zero
0     550395              2          0     1
1     550395              3         14     1
2     550395              4          9     1
3     550395              5          0     2
4     550395              6          1     2
5     931192              2          0     2
6     931192              3          0     3
7     931192              4          3     3
8     931192              5          2     3
9     931192              6          0     4

[Bartek635]

Hope this helps

import pandas as pd
import numpy as np


df1 = pd.DataFrame(data = {'USERS_PK1':[550395,550395,550395,550395,550395,931192,931192,931192,931192,931192],
                             'ACADEMIC_WEEK':[2,3,4,5,6,2,3,4,5,6],
                             'NumEvents':[0,14,9,0,1,0,0,3,2,0],})


df1['Zero'] = (((df1['USERS_PK1']==df1['USERS_PK1'].shift(1)) | (pd.isnull(df1['USERS_PK1'].shift(1)))) & (df1['NumEvents']==0)).cumsum()

Output:
   USERS_PK1  ACADEMIC_WEEK  NumEvents  Zero
0     550395              2          0     1
1     550395              3         14     1
2     550395              4          9     1
3     550395              5          0     2
4     550395              6          1     2
5     931192              2          0     2
6     931192              3          0     3
7     931192              4          3     3
8     931192              5          2     3
9     931192              6          0     4

It works! Great, thank you a lot!