keep only lowest value per row in a Python Pandas dataset
Question: In a Pandas dataset I only want to keep the lowest value per line. All other
values should be deleted. I need the original dataset intact. Just remove all
values (replace by NaN) which are not the minimum.

What is the best way to do this - speed/performance wise.

I can also transpose the dataset if the operation is easier per column.

Thanks Robert


Answer: Since the operation you are contemplating does not rely on the columns or
index, it might be easier (and faster) to do this using NumPy rather than
Pandas.

You can find the location (i.e. column index) of the minimums for each row
using

    
    
    idx = np.argmin(arr, axis=1)
    

You could then make a new array filled with NaNs and copy the minimum values
to the new array.

* * *
    
    
    import numpy as np
    import pandas as pd
    
    def nan_all_but_min(df):
        arr = df.values
        idx = np.argmin(arr, axis=1)
        newarr = np.full_like(arr, np.nan, dtype='float')
        newarr[np.arange(arr.shape[0]), idx] = arr[np.arange(arr.shape[0]), idx]
        df = pd.DataFrame(newarr, columns=df.columns, index=df.index)
        return df
    
    df = pd.DataFrame(np.random.random((4,3)))
    print(df)
    #           0         1         2
    # 0  0.542924  0.499702  0.058555
    # 1  0.682663  0.162582  0.885756
    # 2  0.389789  0.648591  0.513351
    # 3  0.629413  0.843302  0.862828
    
    df = nan_all_but_min(df)
    print(df)
    

yields

    
    
              0         1         2
    0       NaN       NaN  0.058555
    1       NaN  0.162582       NaN
    2  0.389789       NaN       NaN
    3  0.629413       NaN       NaN
    

* * *

Here is a benchmark comparing `nan_all_but_min` vs `using_where`:

    
    
    def using_where(df):
        return df.where(df.values == df.min(axis=1)[:,None])
    
    In [73]: df = pd.DataFrame(np.random.random(100*100).reshape(100,100))
    
    In [74]: %timeit using_where(df)
    1000 loops, best of 3: 701 µs per loop
    
    In [75]: %timeit nan_all_but_min(df)
    10000 loops, best of 3: 105 µs per loop
    

* * *

Note that `using_where` and `nan_all_but_min` behave differently if a row
contains the same min value more than once. `using_where` will preserve all
the mins, `nan_all_but_min` will preserve only one min. For example:

    
    
    In [76]: using_where(pd.DataFrame([(0,0,1), (1,2,1)]))
    Out[76]: 
       0   1   2
    0  0   0 NaN
    1  1 NaN   1
    
    In [77]: nan_all_but_min(pd.DataFrame([(0,0,1), (1,2,1)]))
    Out[77]: 
       0   1   2
    0  0 NaN NaN
    1  1 NaN NaN
    

