Sort each row absolute value independent of columns along with column names

Question

I have a data frame of similar format:

df = pd.DataFrame({
 'p1': [0, 0, 1, 1, -2],
 'p2': [9, 2, 3, -5, 3],
 'p3': [1, 3, 10, 3, 7],
 'p4': [4, 4, 7, 1, 10]})

    p1  p2  p3  p4
0   0   9   1   4
1   0   2   3   4
2   1   3   10  7
3   1   -5  3   1
4   -2  3   7   10

Expected output:

top1    top2
p2:9    p4:4
p4:4    p3:3
p3:10   p4:7
p2:-5   p3:3
p4:10   p3:7

With a lot of research, I was able to sort and obtain the indices of the sorted array. I was also able to replace the indices with columns. But I am unable to concatenate them with row values.

nlargest = 3
order = np.argsort(-df.abs().values, axis=1)[:, :nlargest]
result = pd.DataFrame(df.columns[order], 
                      columns=['top{}'.format(i) for i in range(1, nlargest+1)])

  top1 top2 top3
0   p2   p4   p3
1   p4   p3   p2
2   p3   p4   p2
3   p2   p3   p1
4   p4   p3   p2

Using the above method I tried to sort the rows in a different DataFrame and later thought of concatenating them. But I couldn't find the right way to do that. I know this is not an optimal way though.

result2 = pd.DataFrame(np.sort(df.values, axis=0), index=df.index, columns=df.columns)
result2 = result2.iloc[:, 0:nlargest]
result2.columns = columns=['top{}'.format(i) for i in range(1, nlargest+1)]

   top1  top2  top3
0    -2    -5     1
1     0     2     3
2     0     3     3
3     1     3     7
4     1     9    10

Please help me correct the sorting and the shortest way to get the expected format.

Optimal way means the best performance? – jezrael Jun 15 '20 at 10:46 — jezrael, Jun 15 '20 at 10:46

score 3 · Answer 1 · answered Jun 11 '20 at 10:17

Use, DataFrame.transform along with DataFrame.lookup:

result = result.transform(lambda s: s + ':' + df.lookup(s.index, s).astype(str))

# print(result)
    top1  top2  top3
0   p2:9  p4:4  p3:1
1   p4:4  p3:3  p2:2
2  p3:10  p4:7  p2:3
3  p2:-5  p3:3  p1:1
4  p4:10  p3:7  p2:3

jezrael · Accepted Answer · 2020-06-15T10:46:55.670

Use for best performance only numpy solution:

nlargest = 3
arr = df.to_numpy()
order = np.argsort(-np.abs(arr), axis=1)[:, :nlargest]
print (order)
[[1 3 2]
 [3 2 1]
 [2 3 1]
 [1 2 0]
 [3 2 1]]

Idea is change order of original data in numpy array arr by order array like this solution:

a = arr[np.arange(arr.shape[0])[:, None], order]
print (a)
[[ 9  4  1]
 [ 4  3  2]
 [10  7  3]
 [-5  3  1]
 [10  7  3]]

So you can add values converted to strings:

result = pd.DataFrame(df.columns[order] + ':' + a.astype(str), 
                      columns=['top{}'.format(i) for i in range(1, nlargest+1)])

print (result)
    top1  top2  top3
0   p2:9  p4:4  p3:1
1   p4:4  p3:3  p2:2
2  p3:10  p4:7  p2:3
3  p2:-5  p3:3  p1:1
4  p4:10  p3:7  p2:3

Thanks for the effort @jezrael – supreeth2812 Jun 15 '20 at 14:51 — supreeth2812, Jun 15 '20 at 14:51

score 1 · Answer 3 · answered Jun 11 '20 at 10:27

1

Since you need to create strings we can use .stack, .groupby and .cumcount to grab the top n values.

largest_n = 3

s = (
    df.stack()
    .sort_values(ascending=False)
    .groupby(level=0)
    .head(largest_n)
    .reset_index(1)
    .astype(str)
    .agg(":".join, axis=1)
    .to_frame("vals")
)

df1 = (
    (
        s.assign(key=s.groupby(level=0).cumcount() + 1)
        .set_index("key", append=True)
        .unstack()
    )
    .add_prefix("Top_")
    .droplevel(level=0, axis=1)
)

print(df1)

key  Top_1 Top_2 Top_3
0     p2:9  p4:4  p3:1
1     p4:4  p3:3  p2:2
2    p3:10  p4:7  p2:3
3     p3:3  p1:1  p4:1
4    p4:10  p3:7  p2:3

answered Jun 11 '20 at 10:27

Umar.H

22,559
7
39
74

1

@supreeth2812 no problemo, I'd go with Jezrael's solution as its better for larger datasets and will be quicker than using a lambda – Umar.H Jun 15 '20 at 10:44
1

You were right! @jazrael solution was actually faster and made sense to use it since the data set was huge and had to run it multiple times. – supreeth2812 Jun 15 '20 at 14:54

Sort each row absolute value independent of columns along with column names

3 Answers3