First time group meets condition in pandas DataFrame-CodePudding

I have the following pandas.DataFrame:

	id	year	x
0	01001	2015	0
1	01001	2016	0.5
2	01001	2017	0
3	01001	2018	0
4	01002	2015	0
5	01002	2016	0
6	01002	2017	0.0667525
7	01002	2018	0.133505

My goal is to create a new column and fill each window with 1's one row after x > 0 for the first time and fill all preceding rows with 0. That is:

	id	year	x	y
0	01001	2015	0	0
1	01001	2016	0.5	0
2	01001	2017	0	1
3	01001	2018	0	1
4	01002	2015	0	0
5	01002	2016	0	0
6	01002	2017	0.0667525	0
7	01002	2018	0.133505	1

How can this be done?

This is what I came up with: Get each group's index the first time x > 0 and fill each window with 1 from that index until the end of the partition. Then, take these indices and replace their rows with 0.

Here's a replicable example of my data:

t = pd.DataFrame({'id':{0:'01001',1:'01001',2:'01001',3:'01001',4:'01002',5:'01002',6:'01002',7:'01002'},
                  'x':{0:0.0,1:0.5,2:0,3:0,4:0.0,
                        5:0.0,6:0.06675245612859726,7:0.13350491651818122},
                  'year':{0:2015,1:2016,2:2017,3:2018,4:2015,5:2016,6:2017,7:2018}})
t

CodePudding user response：

What about checking consecutive occurrence conditionally?

df['y'] = df.groupby('id', as_index=False).apply(lambda p: (p['x'].shift().gt(0)).cumsum()).droplevel(0, axis=0)



    id  year         x   y
0  1001  2015  0.000000  0
1  1001  2016  0.500000  0
2  1001  2017  0.000000  1
3  1001  2018  0.000000  1
4  1002  2015  0.000000  0
5  1002  2016  0.000000  0
6  1002  2017  0.066753  0
7  1002  2018  0.133505  1

CodePudding user response：

Let us try transform:

df['y'] = (df.index > (df['x']>0).groupby(df['id']).transform('idxmax')).astype(int)
df
      id  year         x  out
 0  1001  2015  0.000000    0
 1  1001  2016  0.500000    0
 2  1001  2017  0.000000    1
 3  1001  2018  0.000000    1
 4  1002  2015  0.000000    0
 5  1002  2016  0.000000    0
 6  1002  2017  0.066753    0
 7  1002  2018  0.133505    1

CodePudding user response：

Here is a way using cumprod

df.groupby('id')['x'].transform(lambda x: (~x.eq(0).shift().cumprod().astype(bool)).astype(int))