Extract rows with maximum values in pandas dataframe

Question

We can use .idxmax to get the maximum value of a dataframe(df). My problem is that I have a df with several columns (more than 10), one of a column has identifiers of same value. I need to extract the identifiers with the maximum value:

>df

id  value
a   0
b   1
b   1
c   0
c   2
c   1

Now, this is what I'd want:

>df

id  value
a   0
b   1
c   2

I am trying to get it by using df.groupy(['id']), but it is a bit tricky:

df.groupby(["id"]).ix[df['value'].idxmax()]

Of course, that doesn't work. I fear that I am not on the right path, so I thought I'd ask you guys! Thanks!

Jeff · Accepted Answer · 2013-10-22 16:22:35Z

5

Close! Groupby the id, then use the value column; return the max for each group.

In [14]: df.groupby('id')['value'].max()
Out[14]: 
id
a     0
b     1
c     2
Name: value, dtype: int64

Op wants to provide these locations back to the frame, just create a transform and assign.

In [17]: df['max'] = df.groupby('id')['value'].transform(lambda x: x.max())

In [18]: df
Out[18]: 
  id  value  max
0  a      0    0
1  b      1    1
2  b      1    1
3  c      0    2
4  c      2    2
5  c      1    2

edited Oct 22, 2013 at 16:22

answered Oct 22, 2013 at 15:57

Jeff

130k21 gold badges223 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Extract rows with maximum values in pandas dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related