CODEWITHSUNDEEP
pythonpandasdataframerank
OldSport
OldSport

Reputation: 137

Is there a way to rank a value within multiple rows and columns in Pandas Dataframe?

So I have a dataframe with 3 columns and 3 rows (there are more columns but let's ignore them for simplicity) where I want to add additional 3 columns with the rank of the values within this group.

The input is as follows:

        Column1    Column2    Column3
Row1      60         20         10
Row2      40         30         80
Row3      70         50         50

Desired output would be:

        Column1    Column2    Column3   Column1_rank   Column2_rank   Column3_rank
Row1      60         20         10           3              8              9
Row2      40         30         80           6              7              1
Row3      70         50         50           2              4              4

I've been looking at dataframe.rank but it does only seem to be working either on rows or columns, not combined.

Upvotes: 1

Views: 479

Answers (3)

Anurag Dabas
Anurag Dabas

Reputation: 24322

You can use join()+stack()+unstack():

df=df.join(df.stack().rank(ascending=False).unstack().astype(int),rsuffix='_rank')

output of df:

      Column1  Column2  Column3  Column1_rank  Column2_rank  Column3_rank
Row1       60       20       10             3             8             9
Row2       40       30       80             6             7             1
Row3       70       50       50             2             4             4

Upvotes: 4

Epsi95
Epsi95

Reputation: 9047

import pandas as pd
import numpy as np

df = pd.DataFrame([[60,20,10], [40,30,80], [70,50,50]], columns=['Column1','Column2','Column3'])
mapped = dict(map(reversed, enumerate(np.sort(df.values.flatten())[::-1],1)))

for each_column in df.columns:
  df[each_column + '_rank'] = df[each_column].map(mapped)

print(df)

#    Column1  Column2  Column3  Column1_rank  Column2_rank  Column3_rank
# 0       60       20       10             3             8             9
# 1       40       30       80             6             7             1
# 2       70       50       50             2             5             5

Upvotes: 1

U13-Forward
U13-Forward

Reputation: 71620

Try this with join and apply:

lst = sorted(df.values.flatten().tolist())[::-1]
print(df.join(df.apply(lambda x: [lst.index(i) + 1 for i in x]), rsuffix='_rank'))

Output:

      Column1  Column2  Column3  Column1_rank  Column2_rank  Column3_rank
Row1       60       20       10             3             8             9
Row2       40       30       80             6             7             1
Row3       70       50       50             2             4             4

Upvotes: 1

Related Questions