How do you parse out data from a dataframe for each ID when an adjacent column contains a certain value?

user_9686164 0
Viewed 78

I have a large dataframe in the following format. I need to parse out only the values where values ==1 and through the remaining id. This should reset on each ID so that it takes the first value in a unique id that contains the value 1 and ends when the id number terminates.

d={'ID':[1,1,1,1,1,2,2,2,2,2,3,3,3,3,4,4,4,4,4,4,4,4,4,5,5,5,5,5] \
        ,'values':[0,0,0,1,0,1,0,1,1,1,0,1,0,0,0,0,0,0,1,1,0,1,0,1,1,1,1,1,] }
df=pd.DataFrame(data=d)
df=pd.DataFrame(data=d)
df



ND = {'ID':[1,1,2,2,2,2,2,3,3,3,4,4,4,4,4,5,5,5,5,5],\
 'values':[1,0,1,0,1,1,1,1,0,0,1,1,0,1,0,1,1,1,1,1]}

    
df_final=pd.DataFrame(ND)
df_final

'''

pandas parsing python
2 Answers

IIUC,

df[df.groupby('ID')['values'].transform('cummax')==1]

Output:

    ID  values
3    1       1
4    1       0
5    2       1
6    2       0
7    2       1
8    2       1
9    2       1
11   3       1
12   3       0
13   3       0
18   4       1
19   4       1
20   4       0
21   4       1
22   4       0
23   5       1
24   5       1
25   5       1
26   5       1
27   5       1

Details, use cummax to keep the value of 1 after first found. Then use equal to 1 to create a boolean series, which then is used to do boolean indexing.

user_63615310

if your column values is only 0 and 1, you can use groupby.cummax that will replace 0 by 1 if they are after a 1 per ID, then use this as a boolean mask:

df_ = df[df.groupby('ID')['values'].cummax().astype(bool).to_numpy()]
print(df_)
    ID  values
3    1       1
4    1       0
5    2       1
6    2       0
7    2       1
8    2       1
9    2       1
11   3       1
12   3       0
13   3       0
18   4       1
19   4       1
20   4       0
21   4       1
22   4       0
23   5       1
24   5       1
25   5       1
26   5       1
27   5       1
user_92747320
Related