How to get first and second max using agg?

Question

I have a dataframe:

id   type1           val
a    main             3
a    main             5
a    main             4
b    second           80
b    second           90

I want to do groupby on columns id type1 and max() by val and also get the corresponding index of the max observation. I do this:

df.groupby(["id","type1"])["val"].agg("max", "idxmax")

I get:

id   type1        max   idxmax   

a    main          5    1
b   second        90    4

But i want to get first and second max:

id   type1        max   idxmax   

a    main          5    1
a    main          4    2
b   second        90    4
b    second       80    3

How to do that?

user_3284713 · Answer

Use nlargest(n) (here the docs)

df.groupby(["id","type1"])["val"].agg("nlargest", 2)

id  type1    
a   main    1     5
            2     4
b   second  4    90
            3    80