Find max value and the corresponding column/index name in entire dataframe

Question

I want to select the maximum value in a dataframe, and then find out the index and the column name of that value. Is there a way to do it?

Say, in the example below, I want to first find the max value (31), and then return the index and column name of that value (20, R20D)

a = pd.DataFrame({'R05D':[1,2,3],'R10D':[7,4,3],'R20D':[31,2,4]},index=[20,25,30])

Thanks!

Answer 1

If you call a.max(axis=0) you get a series of the max on each column:

R05D     3
R10D     7
R20D    31
dtype: int64

If you call max on that series you get it's maximum so:

a.max(axis=0).max()
#31

gives you the maximum value. Similarly:

a.max(axis=0).idxmax()
#R20D

gives you the column name and

a.max(axis=1).idxmax()
#20

will give you the row.

Answer 2

Turn your dataframe into a MultipleIndex Series and ask for the index of the max element with idxmax function:

coord = a.stack().idxmax()
coord
(20, 'R20D')

To get the value, use the coordinates against loc:

df.loc[coord]
31

To get the numeric index you can use a.stack().argmax()

Answer 3

Since I can't comment with my limited reputation, here is an extra answer to @Boud 's answer: It didn't work for me with argmax(), but using idxmax() solved the problem and returns the correct output of (20, "R20D")

coord = a.stack().idxmax()
coord
(20, 'R20D')

Answer 4

Although this goes beyond what the OP asked for, if you need to find the top n (e.g. 3) values, you can do:

a.stack().sort_values(ascending=False).head(3)

which gives you a new ordered df with the original (index, column) as the new MultiIndex:

20  R20D    31
    R10D     7
25  R10D     4

tail(2) will similarly give you the lowest 2 values.