183

I have a DataFrame like this one:

In [7]:
frame.head()
Out[7]:
Communications and Search   Business    General Lifestyle
0   0.745763    0.050847    0.118644    0.084746
0   0.333333    0.000000    0.583333    0.083333
0   0.617021    0.042553    0.297872    0.042553
0   0.435897    0.000000    0.410256    0.153846
0   0.358974    0.076923    0.410256    0.153846

In here, I want to ask how to get column name which has maximum value for each row, the desired output is like this:

In [7]:
    frame.head()
    Out[7]:
    Communications and Search   Business    General Lifestyle   Max
    0   0.745763    0.050847    0.118644    0.084746           Communications 
    0   0.333333    0.000000    0.583333    0.083333           Business  
    0   0.617021    0.042553    0.297872    0.042553           Communications 
    0   0.435897    0.000000    0.410256    0.153846           Communications 
    0   0.358974    0.076923    0.410256    0.153846           Business
Alex Riley
  • 152,205
  • 43
  • 245
  • 225
markov zain
  • 10,257
  • 13
  • 32
  • 38
  • If the maximum value is not unique for some row and you need all column names of maximum values, then check out [this answer](https://stackoverflow.com/a/70754861). –  Feb 19 '22 at 16:18

3 Answers3

257

You can use idxmax with axis=1 to find the column with the greatest value on each row:

>>> df.idxmax(axis=1)
0    Communications
1          Business
2    Communications
3    Communications
4          Business
dtype: object

To create the new column 'Max', use df['Max'] = df.idxmax(axis=1).

To find the row index at which the maximum value occurs in each column, use df.idxmax() (or equivalently df.idxmax(axis=0)).

Alex Riley
  • 152,205
  • 43
  • 245
  • 225
  • 3
    @SushantKulkarni How did you manage to get the top-3 probabilities instead of the top-1? – Stergios Feb 13 '18 at 09:37
  • # Computing probabilities for all accountsproba = lr.predict_proba(tfidf) MLR_y_p = pd.DataFrame(proba, columns=np.unique(y), index=df.Key.tolist()) – Sushant Kulkarni Mar 05 '18 at 08:23
  • 3
    Just one issue with this: if the value is the same across the columns, idmax pick the first column as default...not ideal.. – Filippo Sebastio Sep 07 '20 at 05:55
  • just dont name the column to 'first'. There is no error thrown and everything works fine including when you display the dataframe, but 'first' is a reserved keyword. It throws error later when you start manipulating the dataframe – Allohvk Feb 03 '21 at 13:20
  • 2
    Is there a way to get the second and third largest value in the same way as the first? – Roger Almengor Jul 05 '21 at 07:19
  • I am trying to do this with dates and for the life of me I can not get it. I have tried to fill in the blanks with 0s or even "0"s even visa versa and make any 0s to blanks "" i get this error every time. TypeError: reduction operation 'argmax' not allowed for this dtype – JQTs Nov 08 '21 at 21:25
44

And if you want to produce a column containing the name of the column with the maximum value but considering only a subset of columns then you use a variation of @ajcr's answer:

df['Max'] = df[['Communications','Business']].idxmax(axis=1)
user1718097
  • 3,770
  • 7
  • 42
  • 53
  • 7
    If you want to exclude all columns except for a subset `df['Max'] = df[df.columns.difference(['Foo','Bar'])].idxmax(axis=1)` – floatingpurr Mar 27 '18 at 15:12
13

You could apply on dataframe and get argmax() of each row via axis=1

In [144]: df.apply(lambda x: x.argmax(), axis=1)
Out[144]:
0    Communications
1          Business
2    Communications
3    Communications
4          Business
dtype: object

Here's a benchmark to compare how slow apply method is to idxmax() for len(df) ~ 20K

In [146]: %timeit df.apply(lambda x: x.argmax(), axis=1)
1 loops, best of 3: 479 ms per loop

In [147]: %timeit df.idxmax(axis=1)
10 loops, best of 3: 47.3 ms per loop
Zero
  • 66,763
  • 15
  • 141
  • 151
  • I love this method. It does seem faster than `apply`. Is there any chance that I can make only small adjustments to this to get the column name with the second largest value? Thanks. – Bowen Liu Oct 21 '20 at 17:48