Find groups in pandas DataFrame that satisfy multiple logical (boolean) conditions

user_13370214 0
Viewed 64

I have 3 columns

Level Status     Id
0     Complete   a1
1     Start      c1
1     Complete   c1
2     Start      d1
2     Start      d2
2     Fail       d2

I want to filter data in which the for each level there should be only start and complete or start and fail given that the id for both are the same

Level Status     Id
1     Start      c1
1     Complete   c1
2     Start      d2
2     Fail       d2
pandas dataframe python
1 Answers

You can use GroupBy.transform('any') to get groups that match the condition of Status having "Start" and Status also having one of {"Fail", "Complete"} using

status_has_start = df['Status'].eq('Start').groupby(df['Id']).transform('any')
status_has_complete_or_fail = (
    df['Status'].isin(['Complete', 'Fail']).groupby(df['Id']).transform('any'))

print (df.loc[status_has_start & status_has_complete_or_fail])

   Level    Status  Id
1      1     Start  c1
2      1  Complete  c1
4      2     Start  d2
5      2      Fail  d2

Where,

print (status_has_start)

0    False
1     True
2     True
3     True
4     True
5     True
Name: Status, dtype: bool

print (status_has_complete_or_fail)
 
0     True
1     True
2     True
3    False
4     True
5     True
Name: Status, dtype: bool

If you want a 1 liner on steroids, you can run

df.loc[pd.concat([df['Status'].eq('Start'), 
                  df['Status'].isin(['Complete', 'Fail'])], axis=1)
         .groupby([df['Level'], df['Id']])
         .transform('any')
         .all(axis=1)]

   Level    Status  Id
1      1     Start  c1
2      1  Complete  c1
4      2     Start  d2
5      2      Fail  d2
user_49090870
Related