Find the max of multiple colums for a particular row and save the header of the max colum into a new column

user_6711091 0
Viewed 57

I have a dataframe like this

age     col1   col2   col3     max
--------------------------------------
  0     200    150    350             
 16      68    250    50

I want to find max value between col1, col2, col3 for every row and update the last column "max" with the column name of the max value

Example in first row, the max value is 350 the "max" column value will be "col3" result like this:

age      col1   col2   col3      max
------------------------------------------
  0      200    150    350     col3
 16       68    250    50      col2

How can I do this?

pandas dataframe python-3.x
2 Answers

You can do this by using pandas.DataFrame.idxmax:

>>> df['max'] = df.idxmax(axis=1)
>>> df
   age  col1  col2  col3   max
0    0   200   150   350  col3
1   16    68   250    50  col2

NB1: That being shown, I would go with max_ instead of max as new column name since max as such refers to an existing method, i.e. pandas.DataFrame.max.

NB2: Also, to prevent age from being considered when searching for the maximum, it is probably better to explicite/restrict the search space

>>> mxseaspace = ['col1', 'col2', 'col3']
>>> df['max_'] = df.loc[:, mxseaspace].idxmax(axis=1)
>>> df
   age  col1  col2  col3  max_
0    0   200   150   350  col3
1   16    68   250    50  col2

Toy Data

df = pd.DataFrame({
    'age':  [0, 16],
    'col1': [200, 68],
    'col2': [150, 250],
    'col3': [350, 50],
})
user_41940790

You can use the apply method. I think it is faster also.

df['Max'] = df.iloc[:,1:].max(axis=1)

Output:

    age col1    col2    col3    Max
0   0   200 150 350 350
1   16  68  250 50  250
user_162463720
Related