How to keep column but not include in groupby

user_17378883 0
Viewed 57

I have a dataframe:

id   type1         type2    val
a    main       regular      3
a    main       unregular    5
a    main       large        3
b    second     regular      80
b    second     unregular    90

I want to do groupby by columns id type1 and max() by val. but when I do that:

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

I get:

id   type1        val

a    main          5
b   second        90

But I want:

id   type1         type2    val

a    main       unregular    5
b    second     unregular    90

How to do that?

dataframe python-3.x group-by python
2 Answers

You could use the idxmax function to find the index where the maximum val occurs and then map the value of type2 -

out = df.groupby(["id","type1"])["val"].agg(["max", "idxmax"])
out.loc[:, 'type2'] = out['idxmax'].map(df['type2'])

Output

           max  idxmax      type2
id type1                         
a  main      5       1  unregular
b  second   90       4  unregular
user_42488420

I updated the answer. How about this

df.groupby(['id', 'type1']).aggregate({'val':'max'}).merge(df, on=['id', 'type1', 'val'], how='inner')

result looks like this

  id   type1  val      type2
0  a    main    5  unregular
1  b  second   90  unregular
user_82828980
Related