Count occurrence of a max value within aggregation-CodePudding

I have a table like this:

Column1	Column2
John	2
John	8
John	8
John	8
Robert	5
Robert	5
Robert	1
Carl	8
Carl	7

Now what I want is to aggregate this DataFrame by Column1 and get the max value as well as to count how many times does the given max value occurs for every group.

So the output should look like this:

Column1	Max	Count_of_Max
John	8	3
Robert	5	2
Carl	8	1

I've been trying to do something like this:

def Max_Count(x):
       a = df.loc[x.index]
       return a.loc[a['Column2'] == a['Column2'].max(), 'Column2'].count()

df.groupby(["Column1"]).agg({'Column2': ["max", Max_Count]}).reset_index()

But it's not really working :(

What would be the way get the desired result?

CodePudding user response：

df.groupby('Column1').agg({
    'Column2': [max, lambda x: (x==max(x)).sum()]
}).rename(columns={'max': 'Max', '<lambda_0>': 'Count_of_Max'})