python - How to keep the only the top N values in a dataframe

Question

It is pandas/Dataframe, for every row, I want to keep only the top N (N=3) values and set others to nan,

import pandas as pd
import numpy as np

data = np.array([['','day1','day2','day3','day4','day5'],
                ['larry',1,4,4,3,5],
                ['gunnar',2,-1,3,4,4],
                ['tin',-2,5,5, 6,7]])
                
df = pd.DataFrame(data=data[1:,1:],
                  index=data[1:,0],
                  columns=data[0,1:])
print(df) 

output is

       day1 day2 day3 day4 day5
larry     1    4    4    3    5
gunnar    2   -1    3    4    4
tin      -2    5    5    6    7

I want to get

       day1 day2 day3 day4 day5
larry   NaN    4    4  NaN    5
gunnar  NaN  NaN    3    4    4
tin     NaN    5  NaN    6    7

Similar to pandas: Keep only top n values and set others to 0, but I need to keep only N highest available values, otherwise the average is not correct

For the result above I want to keep first 5 only

question from:https://stackoverflow.com/questions/66048632/how-to-keep-the-only-the-top-n-values-in-a-dataframe

Answer 1

Here is another way using df.rank on axis=1 , we reverse the columns and calculate the rank since on a duplicate you want to retain the first value.

df[df.astype(float).iloc[:,::-1].rank(1,'first').ge(3)]

---

       day1 day2 day3 day4 day5
larry   NaN    4    4  NaN    5
gunnar  NaN  NaN    3    4    4
tin     NaN    5  NaN    6    7

---

however as @Allolz rightly directed, for a general usecase based on the shape of the df, one can use:

N=3
n = df.shape[1]-N+1
df[df.astype(float).iloc[:,::-1].rank(1,'first').ge(n)]