CODEWITHSUNDEEP
python-3.xpandasdataframenormalization
Marnix
Marnix

Reputation: 779

Add columns with normalised rankings to a pandas dataframe

I would like to add a column with normalized rankings to a pandas dataframe. The process is as follows:

Import the pandas package first. 

#import packages
import pandas as pd

Define a pandas dataframe.

# Create dataframe
data = {'name': ['Jason', 'Jason', 'Tina', 'Tina', 'Tina'],
        'reports': [4, 24, 31, 2, 3],
        'coverage': [25, 94, 57, 62, 70]}
df = pd.DataFrame(data)

After the dataframe is created, I want to add an extra column to the dataframe. This column contains the rank based on the values in the coverage column for every name seperately.

df['coverageRank'] = df.groupby('name')['coverage'].rank()
print (df)
   coverage   name  reports  coverageRank
0        25  Jason        4           1.0
1        94  Jason       24           2.0
2        57   Tina       31           1.0
3        62   Tina        2           2.0
4        70   Tina        3           3.0

I now want to normalize the values in the ranking column.

The desired output is 

   coverage   name  reports  coverageRank
0        25  Jason        4      0.500000
1        94  Jason       24      1.000000
2        57   Tina       31      0.333333
3        62   Tina        2      0.666667
4        70   Tina        3      1.000000

Does someone know a way to do this without using an explicit for-loop?

Upvotes: 1

Views: 894

Answers (1)

jezrael
jezrael

Reputation: 862691

You can use transform for Series with same size as original df and then divide by div:

a = df.groupby('name')['coverage'].transform('size')
print (a)
0    2
1    2
2    3
3    3
4    3
Name: coverage, dtype: int64

df['coverageRank'] = df.groupby('name')['coverage'].rank().div(a)
print (df)
   coverage   name  reports  coverageRank
0        25  Jason        4      0.500000
1        94  Jason       24      1.000000
2        57   Tina       31      0.333333
3        62   Tina        2      0.666667
4        70   Tina        3      1.000000

Another solution with apply:

df['coverageRank'] = df.groupby('name')['coverage'].apply(lambda x: x.rank() / len(x))
print (df)
   coverage   name  reports  coverageRank
0        25  Jason        4      0.500000
1        94  Jason       24      1.000000
2        57   Tina       31      0.333333
3        62   Tina        2      0.666667
4        70   Tina        3      1.000000

Upvotes: 2

Related Questions