How to flag groups in pandas that don't meet specific criteria of separate columns?

user_10145261 0
Viewed 40

I have a df that looks like this:

Name  Letter  Period  Amount
123   H       PRE     11
123   H       PRE     14
123   H       PRE     12 
123   H       DURING  5
123   H       POST    100
456   H       PRE     9
456   H       DURING  50
456   H       POST    600
789   J       PRE     8
789   J       PRE     17
789   J       PRE     11
789   J       DURING  9
789   J       POST    201
789   J       POST    202
789   J       POST    200

I need to be able to remove values from Name where the count of PRE is not >=3 or POST is not >=3. This would mean that only Name 789 would exist after applying this logic to the df above. 123 has 3 PRE periods but only 1 POST so it is not included.

Expected output:

Name  Letter  Period  Amount
789   J       PRE     8
789   J       PRE     17
789   J       PRE     11
789   J       DURING  9
789   J       POST    201
789   J       POST    202
789   J       POST    200
pandas dataframe python
2 Answers

Try with filter

out = df.groupby('Name').filter(lambda x : (x['Period'].eq('PRE').sum()>=3) &
                                           (x['Period'].eq('POST').sum()>=3))
    Name Letter  Period  Amount
8    789      J     PRE       8
9    789      J     PRE      17
10   789      J     PRE      11
11   789      J  DURING       9
12   789      J    POST     201
13   789      J    POST     202
14   789      J    POST     200
user_79645270

An alternative, that might be a bit faster: Get the conditions where "PRE" and "POST" are greater than or equal to 3 and filter the dataframe with the resulting booleans:

cond1 = df.Period.eq("PRE").groupby(df.Name).transform("sum").ge(3)
cond2 = df.Period.eq("POST").groupby(df.Name).transform("sum").ge(3)
df.loc[cond1 & cond2]

    Name    Letter  Period  Amount
8   789     J       PRE     8
9   789     J       PRE     17
10  789     J       PR E    11
11  789     J       DURING  9
12  789     J       POST    201
13  789     J       POST    202
14  789     J       POST    200
user_71757130
Related