347

How can I find the row for which the value of a specific column is maximal?

df.max() will give me the maximal value for each column, I don't know how to get the corresponding row.

Gonçalo Peres
  • 11,752
  • 3
  • 54
  • 83
Miki Tebeka
  • 13,428
  • 4
  • 37
  • 49
  • Is it possible to get the top 2 values? instead of only the max? – AsheKetchum Mar 15 '17 at 22:04
  • 8
    You can use `sort_values` and get the index: `df.sort_values('col', ascending=False)[:2].index` – Miki Tebeka Mar 17 '17 at 05:07
  • 5
    lazy1: avoid unnecessarily sorting an entire series because it's O(N logN) on average, whereas finding max/idxmax is only O(N). – smci Jul 16 '19 at 23:24
  • @AsheKetchum did you get an answer to this? – yishairasowsky Jul 19 '20 at 11:07
  • If the maximum value is not unique for some column and you need all indices of maximum values, then check out [this answer](https://stackoverflow.com/a/71058418/17521785). –  Feb 20 '22 at 22:41

14 Answers14

368

Use the pandas idxmax function. It's straightforward:

>>> import pandas
>>> import numpy as np
>>> df = pandas.DataFrame(np.random.randn(5,3),columns=['A','B','C'])
>>> df
          A         B         C
0  1.232853 -1.979459 -0.573626
1  0.140767  0.394940  1.068890
2  0.742023  1.343977 -0.579745
3  2.125299 -0.649328 -0.211692
4 -0.187253  1.908618 -1.862934
>>> df['A'].idxmax()
3
>>> df['B'].idxmax()
4
>>> df['C'].idxmax()
1

  • Alternatively you could also use numpy.argmax, such as numpy.argmax(df['A']) -- it provides the same thing, and appears at least as fast as idxmax in cursory observations.

  • idxmax() returns indices labels, not integers.

  • Example': if you have string values as your index labels, like rows 'a' through 'e', you might want to know that the max occurs in row 4 (not row 'd').

  • if you want the integer position of that label within the Index you have to get it manually (which can be tricky now that duplicate row labels are allowed).

HISTORICAL NOTES:

  • idxmax() used to be called argmax() prior to 0.11
  • argmax was deprecated prior to 1.0.0 and removed entirely in 1.0.0
  • back as of Pandas 0.16, argmax used to exist and perform the same function (though appeared to run more slowly than idxmax).
  • argmax function returned the integer position within the index of the row location of the maximum element.
  • pandas moved to using row labels instead of integer indices. Positional integer indices used to be very common, more common than labels, especially in applications where duplicate row labels are common.

For example, consider this toy DataFrame with a duplicate row label:

In [19]: dfrm
Out[19]: 
          A         B         C
a  0.143693  0.653810  0.586007
b  0.623582  0.312903  0.919076
c  0.165438  0.889809  0.000967
d  0.308245  0.787776  0.571195
e  0.870068  0.935626  0.606911
f  0.037602  0.855193  0.728495
g  0.605366  0.338105  0.696460
h  0.000000  0.090814  0.963927
i  0.688343  0.188468  0.352213
i  0.879000  0.105039  0.900260

In [20]: dfrm['A'].idxmax()
Out[20]: 'i'

In [21]: dfrm.iloc[dfrm['A'].idxmax()]  # .ix instead of .iloc in older versions of pandas
Out[21]: 
          A         B         C
i  0.688343  0.188468  0.352213
i  0.879000  0.105039  0.900260

So here a naive use of idxmax is not sufficient, whereas the old form of argmax would correctly provide the positional location of the max row (in this case, position 9).

This is exactly one of those nasty kinds of bug-prone behaviors in dynamically typed languages that makes this sort of thing so unfortunate, and worth beating a dead horse over. If you are writing systems code and your system suddenly gets used on some data sets that are not cleaned properly before being joined, it's very easy to end up with duplicate row labels, especially string labels like a CUSIP or SEDOL identifier for financial assets. You can't easily use the type system to help you out, and you may not be able to enforce uniqueness on the index without running into unexpectedly missing data.

So you're left with hoping that your unit tests covered everything (they didn't, or more likely no one wrote any tests) -- otherwise (most likely) you're just left waiting to see if you happen to smack into this error at runtime, in which case you probably have to go drop many hours worth of work from the database you were outputting results to, bang your head against the wall in IPython trying to manually reproduce the problem, finally figuring out that it's because idxmax can only report the label of the max row, and then being disappointed that no standard function automatically gets the positions of the max row for you, writing a buggy implementation yourself, editing the code, and praying you don't run into the problem again.

Valentin Kuhn
  • 753
  • 9
  • 25
ely
  • 74,674
  • 34
  • 147
  • 228
  • 15
    Based on the second-to-last comment there, it looks like `argmin` and `argmax` will remain part of `DataFrame` and the difference is just whether you want the index or the label. `idxmax` will give you the label of the location where a max occurs. `argmax` will give you the index integer itself. – ely Apr 04 '13 at 19:25
  • 4
    The information provided to explain the difference between `argmax` and `idxmax`, and how to avoid bugs with duplicated index was great ! I haven't notice that until I read your comment in the other answer. Thanks! – renno Oct 07 '16 at 13:21
  • As regards the use you would like to implement, Pandas 0.24.1 points to the following: 'the behavior of `argmax` will be corrected to return the positional maximum in the future. For now, use `series.values.argmax` or `np.argmax(np.array(values))` to get the position of the maximum row.' – Sam Aug 29 '19 at 10:10
  • 1
    similarly, the `.ix` method of the second example has been renamed into `.iloc` – Ma0 Oct 30 '19 at 07:12
  • if your column contains only nan values, this will result in TypeError – Max Segal May 24 '20 at 12:16
  • 1
    This was years out of date, so I updated it: a) stop talking about `argmax()` already b) it was [deprecated prior to 1.0.0 and removed entirely in 1.0.0](https://pandas.pydata.org/pandas-docs/version/1.0.0/whatsnew/v1.0.0.html) c) long time ago, pandas moved from integer indices to labels. This is not a problem. If you need integer indexing, you can use logical indexing with any arbitrary logical expression (or convert logical mask to integers with `np.flatnonzero()`.) d) Really the historical footnotes about `argmax()` are obsolete, confusing and unnecessary, pandas idiom has changed. – smci Jun 03 '20 at 21:42
  • (There are many caveats about duplicate labels, basically "Don't do this, kids"). That's all. If you insist, you can use them, and deal with the all the accompanying breakage. – smci Jun 03 '20 at 21:43
  • @smci I feel the edit is unfortunate becuase it emphasizes idxmax which is incorrectly implermented in pandas flat out, just as the example in the bottom of my answer indicates. We shouldn't be emphasizing idxmax, rather we should be emphasizing `dfrm.iloc[dfrm['A'].idxmax()]` possibly even with more business logic on top. Basically there should be giant caveats of "don't just use `idxmax` and expect it to work" or else the answer is deliberately deceptive. – ely Jun 05 '20 at 15:31
  • What is the advantage of using dfrm.iloc[dfrm['A'].idxmax()] instead of dfrm[dfrm.A == dfrm.A.max()] ? – skan Jul 30 '22 at 18:20
  • 1
    @skan The second of your example involves vectorized equality check over the entire column of A, creating a potentially huge boolean array to be indexed upon. In any real setting, this has to be avoided (usually for OOM and runtime issues). The latter expression will be more correct (because it avoids issues of duplicate indexes as returned by idxmax) but populating the intermediate equality boolean array simple cannot be take seriously as an option except in throwaway small data situations. – ely Jul 31 '22 at 23:55
  • @ely Any alternative without any of these problems? (Simultauneously valid for duplicate maximums and valid for huge columns) – skan Aug 02 '22 at 01:08
100

You might also try idxmax:

In [5]: df = pandas.DataFrame(np.random.randn(10,3),columns=['A','B','C'])

In [6]: df
Out[6]: 
          A         B         C
0  2.001289  0.482561  1.579985
1 -0.991646 -0.387835  1.320236
2  0.143826 -1.096889  1.486508
3 -0.193056 -0.499020  1.536540
4 -2.083647 -3.074591  0.175772
5 -0.186138 -1.949731  0.287432
6 -0.480790 -1.771560 -0.930234
7  0.227383 -0.278253  2.102004
8 -0.002592  1.434192 -1.624915
9  0.404911 -2.167599 -0.452900

In [7]: df.idxmax()
Out[7]: 
A    0
B    8
C    7

e.g.

In [8]: df.loc[df['A'].idxmax()]
Out[8]: 
A    2.001289
B    0.482561
C    1.579985
ayhan
  • 70,170
  • 20
  • 182
  • 203
Wes McKinney
  • 101,437
  • 32
  • 142
  • 108
  • Thanks Wes. Documentation for idxmax() here: http://pandas.pydata.org/pandas-docs/dev/generated/pandas.DataFrame.idxmax.html – Will Feb 19 '14 at 03:51
  • `df.ix[df['A'].idxmax()].values` to grab the array i wanted. still works. – Yojimbo Feb 19 '15 at 18:19
  • 2
    Note that you need to be careful trying to use the output of `idxmax` as a feeder into `ix` or `loc` as a means to sub-slice the data and/or to obtain the positional location of the max-row. Because you can have duplicates in the `Index` - see the update to my answer for an example. – ely May 11 '15 at 02:39
  • I'm getting `AttributeError: 'Series' object has no attribute 'idmax'` – ChrisProsser Jan 10 '22 at 18:45
32

Both above answers would only return one index if there are multiple rows that take the maximum value. If you want all the rows, there does not seem to have a function. But it is not hard to do. Below is an example for Series; the same can be done for DataFrame:

In [1]: from pandas import Series, DataFrame

In [2]: s=Series([2,4,4,3],index=['a','b','c','d'])

In [3]: s.idxmax()
Out[3]: 'b'

In [4]: s[s==s.max()]
Out[4]: 
b    4
c    4
dtype: int64
mxia
  • 449
  • 5
  • 4
22
df.iloc[df['columnX'].argmax()]

argmax() would provide the index corresponding to the max value for the columnX. iloc can be used to get the row of the DataFrame df for this index.

Nafeez Quraishi
  • 5,380
  • 2
  • 27
  • 34
15

A more compact and readable solution using query() is like this:

import pandas as pd

df = pandas.DataFrame(np.random.randn(5,3),columns=['A','B','C'])
print(df)

# find row with maximum A
df.query('A == A.max()')

It also returns a DataFrame instead of Series, which would be handy for some use cases.

Morty
  • 171
  • 1
  • 4
11

Very simple: we have df as below and we want to print a row with max value in C:

A  B  C
x  1  4
y  2  10
z  5  9

In:

df.loc[df['C'] == df['C'].max()]   # condition check

Out:

A B C
y 2 10
Suhas_Pote
  • 3,620
  • 1
  • 23
  • 38
CHLOE
  • 111
  • 1
  • 2
9

If you want the entire row instead of just the id, you can use df.nlargest and pass in how many 'top' rows you want and you can also pass in for which column/columns you want it for.

df.nlargest(2,['A'])

will give you the rows corresponding to the top 2 values of A.

use df.nsmallest for min values.

najeem
  • 1,841
  • 13
  • 29
4

The direct ".argmax()" solution does not work for me.

The previous example provided by @ely

>>> import pandas
>>> import numpy as np
>>> df = pandas.DataFrame(np.random.randn(5,3),columns=['A','B','C'])
>>> df
      A         B         C
0  1.232853 -1.979459 -0.573626
1  0.140767  0.394940  1.068890
2  0.742023  1.343977 -0.579745
3  2.125299 -0.649328 -0.211692
4 -0.187253  1.908618 -1.862934
>>> df['A'].argmax()
3
>>> df['B'].argmax()
4
>>> df['C'].argmax()
1

returns the following message :

FutureWarning: 'argmax' is deprecated, use 'idxmax' instead. The behavior of 'argmax' 
will be corrected to return the positional maximum in the future.
Use 'series.values.argmax' to get the position of the maximum now.

So that my solution is :

df['A'].values.argmax()
AntoineP
  • 3,035
  • 1
  • 19
  • 22
2
mx.iloc[0].idxmax()

This one line of code will give you how to find the maximum value from a row in dataframe, here mx is the dataframe and iloc[0] indicates the 0th index.

nucsit026
  • 652
  • 7
  • 16
Manjula Devi
  • 151
  • 2
  • 8
2

Considering this dataframe

[In]: df = pd.DataFrame(np.random.randn(4,3),columns=['A','B','C'])
[Out]:
          A         B         C
0 -0.253233  0.226313  1.223688
1  0.472606  1.017674  1.520032
2  1.454875  1.066637  0.381890
3 -0.054181  0.234305 -0.557915

Assuming one want to know the rows where column "C" is max, the following will do the work

[In]: df[df['C']==df['C'].max()])
[Out]:
          A         B         C
1  0.472606  1.017674  1.520032
Gonçalo Peres
  • 11,752
  • 3
  • 54
  • 83
2

What worked for me is:

df[df['colX'] == df['colX'].max()]

You then get the row in your df with the maximum value of colX.

Then if you just want the index you can add .index at the end of the query.

GegznaV
  • 4,938
  • 4
  • 23
  • 43
ArieAI
  • 354
  • 1
  • 12
1

The idmax of the DataFrame returns the label index of the row with the maximum value and the behavior of argmax depends on version of pandas (right now it returns a warning). If you want to use the positional index, you can do the following:

max_row = df['A'].values.argmax()

or

import numpy as np
max_row = np.argmax(df['A'].values)

Note that if you use np.argmax(df['A']) behaves the same as df['A'].argmax().

nucsit026
  • 652
  • 7
  • 16
Jonathan
  • 4,847
  • 3
  • 32
  • 37
0

Use:

data.iloc[data['A'].idxmax()]

data['A'].idxmax() -finds max value location in terms of row data.iloc() - returns the row

Naosher Mustakim
  • 1
  • 2
0

If there are ties in the maximum values, then idxmax returns the index of only the first max value. For example, in the following DataFrame:

   A  B  C
0  1  0  1
1  0  0  1
2  0  0  0
3  0  1  1
4  1  0  0

idxmax returns

A    0
B    3
C    0
dtype: int64

Now, if we want all indices corresponding to max values, then we could use max + eq to create a boolean DataFrame, then use it on df.index to filter out indexes:

out = df.eq(df.max()).apply(lambda x: df.index[x].tolist())

Output:

A       [0, 4]
B          [3]
C    [0, 1, 3]
dtype: object