1
\$\begingroup\$

I'd like, from a DataFrame, to retrieve a Series which contains the index of the minimal value per row (so axis=1). From the documentation it seems idxmin may be useful

Let' take

import pandas as pd
import numpy as np
df = pd.DataFrame({
    'col1': [0, 10, 2, 30, 4],
    'col2': [2,  3, 4,  5, 6],
    'col3': [9,  7, 5,  3, 1],
})
  1. I've managed to get column names of minimal value

    > df.idxmin(axis=1)
    0    col1
    1    col2
    2    col1
    3    col3
    4    col3
    dtype: object
    
  2. I've managed to get the index of column of minimal vlaue

    df.apply(np.argmin, axis=1)
    0    0
    1    1
    2    0
    3    2
    4    2
    dtype: int64
    

Is there any better way to obtain the 2. result using maybe a full-pandas code ?

\$\endgroup\$
1
  • 1
    \$\begingroup\$ What is this for? Some context would help find the most appropriate solution. \$\endgroup\$ – AMC Nov 26 '20 at 21:49
1
\$\begingroup\$

df.apply over axis 1 is generally avoid for performance issues. When should I (not) want to use pandas .apply() in my code

1. pd.Index.get_indexer

cols = df.idxmin(axis=1)
idx = df.columns.get_indexer(cols) # return a numpy array
idx_series = pd.Series(idx, index=df.index)
# Here, index=df.index is important when your DataFrame has custom index values

print(idx_series)
0    0
1    1
2    0
3    2
4    2
dtype: int64

2. df.to_numpy and np.ndarray.argmin

idx = df.to_numpy().argmin(axis=1) # returns numpy array
idx_series = pd.Series(idx, index=df.index)
print(idx_series)

0    0
1    1
2    0
3    2
4    2
dtype: int64

Some timeit benchs

from datetime import timedelta
from timeit import timeit

if __name__ == '__main__':

    setup = """
import pandas as pd
import numpy as np
from random import randrange
total = 10000
df = pd.DataFrame({
        'col1': [randrange(10000) for i in range(total)],
        'col2': [randrange(10000) for i in range(total)],
        'col3': [randrange(10000) for i in range(total)],
        'col4': [randrange(10000) for i in range(total)],
        'col5': [randrange(10000) for i in range(total)],
        'col6': [randrange(10000) for i in range(total)],
        'col7': [randrange(10000) for i in range(total)],
    })
    """

    for stmt in ["pd.Series(df.to_numpy().argmin(axis=1), index=df.index)",
                 "pd.Series(df.columns.get_indexer(df.idxmin(axis=1)), index=df.index)",
                 "df.apply(np.argmin, axis=1)"]:
        t = timeit(stmt=stmt, setup=setup, number=20)
        print(f"{str(timedelta(seconds=t)):20s}{stmt}")


# Results
0:00:00.012226      pd.Series(df.to_numpy().argmin(axis=1), index=df.index)
0:00:00.408151      pd.Series(df.columns.get_indexer(df.idxmin(axis=1)), index=df.index)
0:00:23.908125      df.apply(np.argmin, axis=1)
\$\endgroup\$
3
  • \$\begingroup\$ Would you like to add some timeit examples in your code ? I've made them, you code is amazingly faster \$\endgroup\$ – azro Nov 28 '20 at 10:14
  • \$\begingroup\$ @azro df.apply over axis 1 is not vectorized it's slow and in general it should be used as last resort. Thank you the timeits ;) \$\endgroup\$ – Ch3steR Nov 28 '20 at 12:45
  • \$\begingroup\$ I noticed that I don't need the pd.Series cast, I can dircetly do df['newCol'] = df.to_numpy().argmin(axis=1) , which is about 5% faster than the same with Series cast :) \$\endgroup\$ – azro Nov 28 '20 at 13:40

Not the answer you're looking for? Browse other questions tagged or ask your own question.