groupby and trim some rows based on condition

user_10506347 0
Viewed 40

I have a data frame something like this:

df = pd.DataFrame({"ID":[1,1,2,2,2,3,3,3,3,3], 
               "IF_car":[1,0,0,1,0,0,0,1,0,1],
               "IF_car_history":[0,0,0,1,0,0,0,1,0,1],  
               "observation":[0,0,0,1,0,0,0,2,0,3]})

I want output where I can trim rows in groupby with ID and condition on "IF_car_history" == 1

tried_df =  df.groupby(['ID']).apply(lambda x: x.loc[:(x['IF_car_history'] == '1').idxmax(),:]).reset_index(drop = True)

I want to drop rows in a groupby by after i get ['IF_car_history'] == '1'

expected output:

expected output

Thanks

pandas-groupby pandas python-3.x
1 Answers

First compare values for mask m by Series.eq and then use GroupBy.cumsum, and for values before 1 compare by 0, last filter by boolean indexing, but because id necesary remove after last 1 is used swapped values by slicing with [::-1].

m = df['IF_car_history'].eq(1).iloc[::-1]
df1 = df[m.groupby(df['ID']).cumsum().ne(0).iloc[::-1]]
print (df1)
   ID  IF_car  IF_car_history  observation
2   2       0               0            0
3   2       1               1            1
5   3       0               0            0
6   3       0               0            0
7   3       1               1            2
8   3       0               0            0
9   3       1               1            3
user_29010020
Related