Get Row and Column with Minimum value in Entire Pandas DataFrame

Question

The problem is simple and so must be solution but I am not able to find it.

I want to find which row and column in Pandas DataFrame has minimum value and how much is it.

I have tried following code (in addition to various combinations):

df = pd.DataFrame(data=[[4,5,6],[2,1,3],[7,0,5],[2,5,3]], 
                 index = ['R1','R2','R3','R4'], 
                 columns=['C1','C2','C3'])

print(df)

print(df.loc[df.idxmin(axis=0), df.idxmin(axis=1)])

The dataframe (df) being searched is:

    C1  C2  C3
R1   4   5   6
R2   2   1   3
R3   7   0   5
R4   2   5   3

Output for the loc command:

    C1  C2  C2  C1
R2   2   1   1   2
R3   7   0   0   7
R2   2   1   1   2

What I need is:

    C2
R3   0

How can I get this simple result?

Working with some missing values is most important. Then display and then performance. — rnso
– rnso, Commented Nov 14, 2018 at 6:46

jezrael · Accepted Answer · 2018-11-14 06:51:26Z

6

Use:

a, b = df.stack().idxmin()
print(df.loc[[a], [b]])
    C2
R3   0

Another @John Zwinck solution working with missing values - use numpy.nanargmin:

df = pd.DataFrame(data=[[4,5,6],[2,np.nan,3],[7,0,5],[2,5,3]], 
    index = ['R1','R2','R3','R4'], 
    columns=['C1','C2','C3'])

print(df)
    C1   C2  C3
R1   4  5.0   6
R2   2  NaN   3
R3   7  0.0   5
R4   2  5.0   3

#https://stackoverflow.com/a/3230123
ri, ci = np.unravel_index(np.nanargmin(df.values), df.shape)
print(df.iloc[[ri], [ci]])
     C2
R3  0.0

edited Nov 14, 2018 at 6:51

answered Nov 14, 2018 at 6:15

jezrael

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

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

3 Comments

rnso Over a year ago

Great. Forgot to add in question: there are some np.nan values in real df. Will this code work there as well?

jezrael Over a year ago

@rnso - sure, pandas function working with nans nice.

jezrael Over a year ago

@rnso - changed solution for working with missing values.

John Zwinck · Accepted Answer · 2018-11-14 06:18:26Z

1

I'd get the index this way:

np.unravel_index(np.argmin(df.values), df.shape)

This is much faster than df.stack().idxmin().

It gives you a tuple such as (2, 1) in your example. Pass that to df.iloc[] to get the value.

answered Nov 14, 2018 at 6:18

John Zwinck

252k44 gold badges346 silver badges459 bronze badges

2 Comments

rnso Over a year ago

It works but it is not giving row and column names in output. Also will it work if there are some np.nan values in df?

John Zwinck Over a year ago

@rnso: if you want to ignore NANs, simply use nanargmin instead of argmin. If you want the row and column names, you can use df.columns[x] and df.index[y] or df.iloc[[x], [y]] as in jezrael's answer.

U13-Forward · Accepted Answer · 2018-11-14 06:23:58Z

1

Or min+min+dropna+T+dropna+T:

>>> df[df==df.min(axis=1).min()].dropna(how='all').T.dropna().T
     C2
R3  0.0
>>>

answered Nov 14, 2018 at 6:23

U13-Forward

71.8k15 gold badges100 silver badges125 bronze badges

Collectives™ on Stack Overflow

Get Row and Column with Minimum value in Entire Pandas DataFrame

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related