Replacing column value under a Group in Pandas

Question

Hi I have a dataframe as below, I need to replace the column value in each group based on the condition using pandas. Please help

Input Data:

Name    Thing   type    flag
Steve   Car     High    NULL
Steve   Car     Low     NULL
Steve   Bike    Low     NULL
Steve   Bike    Avg     X
Steve   Plane   High    NULL
Steve   Plane   Low     NULL

the condition will be :

1. for a given name and thing if type is Low,Avg update the flag as X

2. for a given name and thing if type is High,Avg update the flag as X

Expected output :

Name    Thing   type    flag
Steve   Car     High    NULL
Steve   Car     Low     NULL
Steve   Bike    Low     X
Steve   Bike    Avg     X
Steve   Plane   High    NULL
Steve   Plane   Low     NULL

Tried so far :

df['flag'] = df['flag'].mask((df['type'] == 'Low') | (df['type'] == 'Avg'), 'X').groupby(df(['name','thing'])).transform('any')

Answer 1

Code

g = df.groupby(['Name', 'Thing'])['type']
cond = g.transform(lambda x: (set(x) == {'Low', 'Avg'}) | (set(x) == {'High', 'Avg'}))
df.loc[cond, 'flag'] = 'X'

df:

    Name  Thing  type flag
0  Steve    Car  High  NaN
1  Steve    Car   Low  NaN
2  Steve   Bike   Low    X
3  Steve   Bike   Avg    X
4  Steve  Plane  High  NaN
5  Steve  Plane   Low  NaN

Answer 2

Another approach using python sets with groupby.transform and any:

targets = [{'High', 'Avg'}, {'Low', 'Avg'}]

df.loc[df.groupby(['Name', 'Thing'])['type']
         .transform(lambda x: any(s.issuperset(x) for s in targets)),
       'flag'] = 'X'

Output:

    Name  Thing  type flag
0  Steve    Car  High  NaN
1  Steve    Car   Low  NaN
2  Steve   Bike   Low    X
3  Steve   Bike   Avg    X
4  Steve  Plane  High  NaN
5  Steve  Plane   Low  NaN