Ranking row values from a multiplus row dataframe-CodePudding

I have a huge df that looks like this:

date	stock1	stock2	stock3	stock4	stock5	stock6	stock7	stock8	stock9	stock10
10/20	0.1	0.2	0.3	0.4	0.5	0.6	0.7	0.8	0.9	0.9
11/20	0.8	0.9	0.3	0.4	0.3	0.5	0.3	0.2	0.4	0.1
12/20	0.3	0.6	0.9	0.5	0.6	0.7	0.8	0.7	0.9	0.1

I want to find, for each row, the 20% higher values of stocks and the 20% lower. The output should be:

date	higher	lower
10/20	stock9, stock 10	stock1, stock 2
11/20	stock1, stock 2	stock8, stock 10
12/20	stock3, stock 9	stock1, stock 10

I do not need to have the comma between the values above, could be one below the other. I have tried df= df.stack() for stacking and later rank the values inside the columns, but I do not know how to proceed.

CodePudding user response：

If you have Python >=3.8, you can do it with a walrus operator:

size = int(0.2 * df.shape[1])
s = df.set_index('date').apply(lambda x: (', '.join((d := x.sort_values(ascending=False).index.tolist())[:size]), 
                                          ', '.join(d[-size:])), axis=1)
out = pd.DataFrame(s.tolist(), index=s.index, columns=['higher','lower']).reset_index()

Output:

    date           higher            lower
0  10/20  stock9, stock10   stock2, stock1
1  11/20   stock2, stock1  stock8, stock10
2  12/20   stock3, stock9  stock1, stock10

CodePudding user response：

Try with nlargest and nsmallest:

df = df.set_index("date")
n = round(len(df.columns)*0.2) #number of stocks in the top/bottom 20%

output = pd.DataFrame()
output["higher"] = df.apply(lambda x: x.nlargest(n).index.tolist(), axis=1)
output["lower"] = df.apply(lambda x: x.nsmallest(n).index.tolist(), axis=1)

>>> output
                  higher              lower
date                                       
10/20  [stock9, stock10]   [stock1, stock2]
11/20   [stock2, stock1]  [stock10, stock8]
12/20   [stock3, stock9]  [stock10, stock1]