Pandas Groupby and Compare rows to find maximum value

I’ve a dataframe

I want to apply groupby at column a and then find the highest value in column c, so that, the highest value gets flagged, i.e.

Compare value 11&12, then

Compare value 23&14, then

Finally resulting in:

I/P DF:

df = pd.DataFrame({
    'a':["one","one","two","two","three","three"]
    , 'b':[6,7,8,9,10,20]
    , 'c':[11,12,23,14,15,25]
    # , 'flag': ['no', 'yes', 'yes', 'no', 'no', 'yes']
})
df

>Solution :

You can use groupby.transform to get the max value per group, and numpy.where to map the True/False to 'yes'/'no':

df['flag'] = np.where(df.groupby('a')['c'].transform('max').eq(df['c']), 'yes', 'no')

output:

       a   b   c flag
0    one   6  11   no
1    one   7  12  yes
2    two   8  23  yes
3    two   9  14   no
4  three  10  15   no
5  three  20  25  yes

Intermediates:

df.groupby('a')['c'].transform('max')

0    12
1    12
2    23
3    23
4    25
5    25
Name: c, dtype: int64

df.groupby('a')['c'].transform('max').eq(df['c'])
0    False
1     True
2     True
3    False
4    False
5     True
Name: c, dtype: bool