Extract row with maximum value in a group pandas dataframe

Question

A similar question is asked here: Python : Getting the Row which has the max value in groups using groupby

However, I just need one record per group even if there are more than one record with maximum value in that group.

In the example below, I need one record for "s2". For me it doesn't matter which one.

>>> df = DataFrame({'Sp':['a','b','c','d','e','f'], 'Mt':['s1', 's1', 's2','s2','s2','s3'], 'Value':[1,2,3,4,5,6], 'count':[3,2,5,10,10,6]})
>>> df
   Mt Sp  Value  count
0  s1  a      1      3
1  s1  b      2      2
2  s2  c      3      5
3  s2  d      4     10
4  s2  e      5     10
5  s3  f      6      6
>>> idx = df.groupby(['Mt'])['count'].transform(max) == df['count']
>>> df[idx]
   Mt Sp  Value  count
0  s1  a      1      3
3  s2  d      4     10
4  s2  e      5     10
5  s3  f      6      6
>>>

This answer is the fastest solution I could find: http://stackoverflow.com/a/21007047/778533 — tommy.carstensen, Mar 26 '17 at 02:18

waitingkuo · Accepted Answer · 2013-11-06T18:46:17.050

29

You can use first

In [14]: df.groupby('Mt').first()
Out[14]: 
   Sp  Value  count
Mt                 
s1  a      1      3
s2  c      3      5
s3  f      6      6

Update

Set as_index=False to achieve your goal

In [28]: df.groupby('Mt', as_index=False).first()
Out[28]: 
   Mt Sp  Value  count
0  s1  a      1      3
1  s2  c      3      5
2  s3  f      6      6

Update Again

Sorry for misunderstanding what you mean. You can sort it first if you want the one with max count in a group

In [196]: df.sort('count', ascending=False).groupby('Mt', as_index=False).first()
Out[196]: 
   Mt Sp  Value  count
0  s1  a      1      3
1  s2  e      5     10
2  s3  f      6      6

edited Nov 06 '13 at 18:46

answered Nov 06 '13 at 17:40

waitingkuo

89,478
28
112
118

Thanks, the structure of the dataframe changed. How to get back the same structure (ie, num of rows and columns) – user1140126 Nov 06 '13 at 17:43
@user1140126 this one returns 5 instead of 10 for Mt = s2, I thought that you need max count in each row? – Roman Pekar Nov 06 '13 at 18:09
He said that it doesn't matter which one is his post – waitingkuo Nov 06 '13 at 18:11
@waitingkuo I'm not native speaker, but for me `one record per group even if there are more than one record with maximum value in that group` means one record with maximum value even if there more than one record with this value. Looks like I was wrong :) – Roman Pekar Nov 06 '13 at 18:16
also, the title ot the question is `Extract row with maximum value in a group pandas dataframe` :) – Roman Pekar Nov 06 '13 at 18:17
Seems its my misunderstanding, I've updated my post, sorry for the inconvenience. – waitingkuo Nov 06 '13 at 18:47
How does this compare in speed to the other suggestions? – tommy.carstensen Mar 26 '17 at 00:52

Roman Pekar · Answer 2 · 2013-11-06T18:30:24.413

19

To get first occurence of maximum count you can use pandas.DataFrame.idxmax() function:

>>> df.iloc[df.groupby(['Mt']).apply(lambda x: x['count'].idxmax())]
   Mt Sp  Value  count
0  s1  a      1      3
3  s2  d      4     10
5  s3  f      6      6

edited Nov 06 '13 at 18:30

answered Nov 06 '13 at 17:48

Roman Pekar

107,110
28
195
197

How does this compare in speed to the other suggestions? – tommy.carstensen Mar 26 '17 at 00:52

score 0 · Answer 3 · answered Feb 17 '16 at 21:50

Playing off of Roman Pekar's answer, I found that that the following code would work:

from math import isnan
df.iloc[[int(x) for x in df.groupby(by=df.Mt).apply(lambda x: x['count'].idxmax()).values if not isnan(y)]]

Note the isnan condition, as my application had some nan entries in the column we are maximizing over.

score 0 · Answer 4 · answered Aug 30 '22 at 23:57

The answers already given don't show clearly what's by far the fastest option.
Sort by the row where you want the max value, and then drop duplicates (takes as parameter the name of the rows to take into account for evaluating duplicates)

df.sort_values('count', ascending=False).drop_duplicates(['Mt'])

NB : Yes that answer is already given in a comment on the question but it's very easy to miss it. And it will be up to 10 times faster as groupby.

Extract row with maximum value in a group pandas dataframe

4 Answers4

Update

Update Again

Linked