Find min value column and min value column name in Python DataFrame

Question

I have a matrix like below and I need to create 2 more columns with minimum value from columns COL01-04 and name of that column (exluding NaN):

In[1]: matrix
Out[1]: 
     ID    COL01  COL02   COL03    COL04
0  0001      NaN   1662    1583   1697.4
1  0002      NaN   1006    1476  1018.44
2  0003     1452   1487  2197.5  1516.27
3  0004      NaN   1554    2298  1585.62

like this:

     ID    COL01  COL02   COL03    COL04  Min_val  Min_col
0  0001      NaN   1662    1583   1697.4     1583    COL03
1  0002      NaN   1006    1476  1018.44     1006    COL02
2  0003     1452   1487  2197.5  1516.27     1452    COL01
3  0004      NaN   1554    2298  1585.62     1554    COL02

I've already tried

for i in range(0, len(matrix)):
    matrix['Min_val'] = matrix[['COL01', 'COL02', 'COL03', 'COL04']].min()

but the result is NaN everywhere, type numpy.float64.

Does this answer your question? How to get the max/min value in Pandas DataFrame when nan value in it — GoodDeeds
– GoodDeeds, Commented Jan 23, 2020 at 13:30

jezrael · Accepted Answer · 2020-01-23 13:37:38Z

8

Use DataFrame.min and DataFrame.idxmin with axis=1 for check values per rows:

c = ['COL01', 'COL02', 'COL03', 'COL04']
matrix[c] = matrix[c].apply(lambda x: pd.to_numeric(x, errors='coerce'))

matrix['Min_val'] = matrix[c].min(axis=1)
matrix['Min_col'] = matrix[c].idxmin(axis=1)

Or for new columns use DataFrame.assign:

matrix = matrix.assign(Min_val = matrix[c].min(axis=1), Min_col=matrix[c].idxmin(axis=1))

print (matrix)
   ID   COL01  COL02   COL03    COL04  Min_val Min_col
0   1     NaN   1662  1583.0  1697.40   1583.0   COL03
1   2     NaN   1006  1476.0  1018.44   1006.0   COL02
2   3  1452.0   1487  2197.5  1516.27   1452.0   COL01
3   4     NaN   1554  2298.0  1585.62   1554.0   COL02

edited Jan 23, 2020 at 13:37

answered Jan 23, 2020 at 13:30

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bartek Nowakowski Over a year ago

For the first option it is still only NaN and for the second one with assign I get TypeError: reduction operation 'argmin' not allowed for this dtype

jezrael Over a year ago

@BartekNowakowski - It means columns are not numeric, so first step is matrix[c] = matrix[c].apply(lambda x: pd.to_numeric(x, errors='coerce')) - convert to numeric if possible, else NaN

yganalyst · Accepted Answer · 2020-01-23 13:39:26Z

4

you can try this :

def get_col(sr):
    name=sr.idxmin()
    value = sr[name]
    return pd.Series([value, name])
df[['Min_val','Min_col']] = df[['COL01','COL02','COL03','COL04']].apply(lambda x : get_col(x), axis=1)

df

     ID   COL01  COL02   COL03    COL04  Min_val Min_col
0  0001     NaN   1662  1583.0  1697.40   1583.0   COL03
1  0002     NaN   1006  1476.0  1018.44   1006.0   COL02
2  0003  1452.0   1487  2197.5  1516.27   1452.0   COL01
3  0004     NaN   1554  2298.0  1585.62   1554.0   COL02

answered Jan 23, 2020 at 13:39

yganalyst

4942 silver badges7 bronze badges

Collectives™ on Stack Overflow

Find min value column and min value column name in Python DataFrame

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related