Python - Remove rows if criteria does not exist

user_11891941 0
Viewed 51

Provided the three columns, I am looking to remove all IDs that do not have an indicator of Y that is equal to or less than 28 DateDiff. The Datediff column is the number of days from an initial incident.

df = pd.DataFrame({'ID':[111, 111, 111, 112, 112, 112, 113, 114, 114, 115,
                                                                 115],
                   'Indicator':['N', 'Y', 'N', 'N', 'N', 'N', 'N', 'Y',
                                                         'N', 'Y', 'N'],
                   'Dateddiff': [0, 10, 32, 0, 19, 37, 29, 0, 28, 30, 34]})

Input

ID   Indicator Datediff
111  N         0
111  Y         10
111  N         32
112  N         0
112  N         19
112  N         37
113  N         29  
114  Y         0
114  N         28
115  Y         30
115  N         34

Output

ID   Indicator Datediff
111  N         0
111  Y         10
111  N         32
114  Y         0
114  N         28
jupyter-notebook pandas python
1 Answers

Use GroupBy.transform to performance a boolean indexing

new_df = df.loc[(df['Dateddiff'].le(28) &
                 df['Indicator'].eq('Y')).groupby(df['ID'])
                                         .transform('any')]
print(new_df)

    ID Indicator  Dateddiff
0  111         N          0
1  111         Y         10
2  111         N         32
7  114         Y          0
8  114         N         28
user_118842370
Related