Find row-index of highest value in given column of dataframe

Question

I want to order a DataFrame by increasing value of column number, and get the indexof that highest value. (Here it's the second row, so the result should be 'BCD':

    number L-word ID
ABC 1      Lord   ABC works
BCD 25     Land   BCD works
CDE 3      Loft   CDE works

(Is there a solution that is not even remotely as weird as the following hack of mine? I worked around this by adding another column with the same name, just so that I understand how that could work in general) So here is the code I came up with:

numbers_ordered = df.sort_values(['number'], ascending = False, na_position='last')
    df = numbers_ordered[:1]
    a = dict(df.head())
    b = a['ID']
    b = str(b)
    c = b[:2]

This seems to be incredibly awkward and there should be an easy option to do this, however I cannot find it in the documentation of pandas as well as the www. I had the idea of changing the index (something like df = df.reset_index()) and then turning the old index into a new column but that would still not be the ultimate solution since I think there should be an option to just "extract" the index of the top hit of my df?

I'm not sure whether I fully understand. Are you trying to get the index of the maximum value in column" number" what about df.number.max().Index.values? — dleal, Jan 06 '17 at 16:12
I'm thinking that you could first get the index as above and then sort — dleal, Jan 06 '17 at 16:14
Could you post a desired data set? Currently it's not clear what are you trying to achieve... — MaxU - stand with Ukraine, Jan 06 '17 at 16:31
This is called `argmax()`. You don't even need to do a full `argsort()` and store the results, only the arg of the max. And you don't need to `df.set_index('your_sort_column)`. Once you know those search terms it's trivial to find this from the [pandas doc index](http://pandas.pydata.org/pandas-docs/stable/) — smci, Dec 19 '17 at 02:24

score 2 · Accepted Answer · answered Jan 06 '17 at 16:27

2

Try df['number'].argmax()

import pandas
import numpy as np
df = pandas.DataFrame(np.random.randn(10,3),columns=['Col1','Col2','Col3'])
print df
print df['Col1'].argmax()

output

                Col1      Col2      Col3
0  0.583251 -0.014694  1.516529
1  0.274758  0.438513  0.994992
2  0.601611  1.753035  0.864451
3 -0.971775 -1.461290  0.121570
4  2.239460 -1.099298 -1.953045
5  2.314444  0.215336  0.470668
6 -0.138696  0.422923 -0.624436
7  0.602329 -0.015627  0.023715
8  0.594784  0.739058  1.094646
9 -0.104579  0.557339  1.977929

5

answered Jan 06 '17 at 16:27

Shijo

9,313
3
19
31

you were bit faster ;) – MaxU - stand with Ukraine Jan 06 '17 at 16:28
1

@MaxU :) to the same answer – Shijo Jan 06 '17 at 16:32
thanks so much, that was way easier than my quirky workaround :) – Maik Ro Jan 07 '17 at 13:17

MaxU - stand with Ukraine · Answer 2 · 2017-01-06T16:33:54.373

There are quite a few ways to query index in Pandas, but it's not clear what do you need.

Here are a few of them:

In [48]: df['number'].argmax()
Out[48]: 'BCD'

In [49]: df.index
Out[49]: Index(['ABC', 'BCD', 'CDE'], dtype='object')

In [50]: df.index == 'BCD'
Out[50]: array([False,  True, False], dtype=bool)

In [51]: df.query("index in ['BCD','ABC']")
Out[51]:
     number L-word         ID
ABC       1   Lord  ABC works
BCD      25   Land  BCD works

In [52]: df.loc[['ABC','CDE','CDE']]
Out[52]:
     number L-word         ID
ABC       1   Lord  ABC works
CDE       3   Loft  CDE works
CDE       3   Loft  CDE works

Find row-index of highest value in given column of dataframe

2 Answers2