Use argmax() idxmax() to get the index of the max value. Then you can use loc

df.loc[df['favcount'].idxmax(), 'sn']

Edit: argmax() is now deprecated, switching for idxmax()

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.

Assume that the source DataFrame contains:

   A  B
0  1  4
1  7  5
2  3  6
3  9  8

Then, to find the column name holding the max value in each row (not only row 0), run:

result = df.apply('idxmax', axis=1)

The result is:

0    B
1    A
2    B
3    A
dtype: object

But if you want to get the integer index of the column holding the max value, change the above code to:

result = df.columns.get_indexer(df.apply('idxmax', axis=1))

This time the result is:

array([1, 0, 1, 0], dtype=int64)

Use np.argmax

NumPy's argmaxcan be helpful:

>>> df.stack().index[np.argmax(df.values)]
(0, 'A')

In steps

df.values is a two-dimensional NumPy array:

>>> df.values
array([[100,   9,   1,  12,   6],
       [ 80,  10,  67,  15,  91],
       [ 20,  67,   1,  56,  23],
       [ 12,  51,   5,  10,  58],
       [ 73,  28,  72,  25,   1]])

argmax gives you the index for the maximum value for the "flattened" array:

>>> np.argmax(df.values)
0

Now, you can use this index to find the row-column location on the stacked dataframe:

>>> df.stack().index[0]
(0, 'A')

Fast Alternative

If you need it fast, do as few steps as possible. Working only on the NumPy array to find the indices np.argmax seems best:

v = df.values
i, j = [x[0] for x in np.unravel_index([np.argmax(v)], v.shape)]
[df.index[i], df.columns[j]]

Result:

[0, 'A']

Timings

Timing works best for lareg data frames:

df = pd.DataFrame(data=np.arange(int(1e6)).reshape(-1,5), columns=list('ABCDE'))

Sorted slowest to fastest:

Mask:

%timeit df.mask(~(df==df.max().max())).stack().index.tolist()
33.4 ms ± 982 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)

Stack-idmax

%timeit list(df.stack().idxmax())
17.1 ms ± 139 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

Stack-argmax

%timeit df.stack().index[np.argmax(df.values)]
14.8 ms ± 392 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

Where

%%timeit
i,j = np.where(df.values == df.values.max())
list((df.index[i].values.tolist()[0],df.columns[j].values.tolist()[0]))

4.45 ms ± 84.7 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

Argmax-unravel_index

%%timeit

v = df.values
i, j = [x[0] for x in np.unravel_index([np.argmax(v)], v.shape)]
[df.index[i], df.columns[j]]

499 µs ± 12 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

Compare

d = {'name': ['Mask', 'Stack-idmax', 'Stack-argmax', 'Where', 'Argmax-unravel_index'],
     'time': [33.4, 17.1, 14.8, 4.45, 499],
     'unit': ['ms', 'ms', 'ms', 'ms', 'µs']}


timings = pd.DataFrame(d)
timings['seconds'] = timings.time * timings.unit.map({'ms': 1e-3, 'µs': 1e-6})
timings['factor slower'] = timings.seconds / timings.seconds.min()
timings.sort_values('factor slower')

Output:

                   name    time unit   seconds  factor slower
4  Argmax-unravel_index  499.00   µs  0.000499       1.000000
3                 Where    4.45   ms  0.004450       8.917836
2          Stack-argmax   14.80   ms  0.014800      29.659319
1           Stack-idmax   17.10   ms  0.017100      34.268537
0                  Mask   33.40   ms  0.033400      66.933868

So the "Argmax-unravel_index" version seems to be one to nearly two orders of magnitude faster for large data frames, i.e. where often speeds matters most.

Here is an alternative way. It is not a one-liner, but it avoids the need to effectively calculate the maximum twice.

import pandas as pd

df = pd.DataFrame({'yield': [0, 1, 3, 5, 1, 9, 5]})

s = df.loc[df['yield'] == df['yield'].max(), 'yield']

res = next(zip(s.index, s))

print(res)

# (5, 9)

Alternatively:

s = df.loc[df['yield'].argmax()]

res = (s.name, s.iloc[0])