Filtering column value based on unique value, but not repeated for different value of the same column on the same unique value

user_10919766 0
Viewed 23

Looking for ways to filter unique values with inactive status, but not repeated as active status under the same unique value.

df:

Unique_value    Status
1               Active        <- Has both active and inactive, must be inactive only
1               Active        <- Has both active and inactive, must be inactive only
1               Inactive      <- Has both active and inactive, must be inactive only
1               Inactive      <- Has both active and inactive, must be inactive only
2               Inactive      <- Has inactive only
2               Inactive      <- Has inactive only
2               Inactive      <- Has inactive only
3               Inactive      <- Has inactive only (cancelled okay to be filtered out)
3               Cancelled     <- Has inactive only (cancelled okay to be filtered out)
3               Inactive      <- Has inactive only (cancelled okay to be filtered out)

Desired output:

Unique_value    status
2               Inactive
3               Inactive

What I tried so far, but I don't think this is correct.

p = ['Inactive', 'Active']
df.groupby('Unique_value')['Status'].apply(lambda x: (x =='Inactive') != set(p))
pandas apply dataframe python-3.x lambda
2 Answers

Lets try

g=df[df.groupby('Unique_value')['Status'].transform(lambda x: ~(x.eq('Active').any()))]

g[g['Status'].eq('Inactive')].drop_duplicates()
user_89869750

First check if any of the values in each group are Active or Inactive. Then get rid of the groups where both conditions are true:

m1 = df["Status"].eq("Active").groupby(df["Unique_value"]).transform("any")
m2 = df["Status"].eq("Inactive").groupby(df["Unique_value"]).transform("any")
df[~(m1 & m2)].groupby("Unique_value", as_index=False).first()


   Unique_value    Status
0             2  Inactive
1             3  Inactive
user_90812670
Related