pythonpandasdataframerank

Column with ranking based on other columns but like tie breaker


I've got a dataframe:

df = pd.DataFrame({
    "DScore": [2, 2, 3, 4, 5],
    "EScore": [6, 7, 9, 9, 10],
    "Total Score": [17, 15, 15, 23, 25]
})

I want to write the code that will create a ranking column containing the classification of rows in the table based on the 'Total Score' column. If these values ​​are equal - you should pay attention to the values ​​​​of EScore points, if they are equal, then it will be based on the values ​​​​from the DScore column, and if these are also equal - we will assign them the same value. Expected result:

df = pd.DataFrame({
    "DScore": [2, 2, 4, 4, 5],
    "EScore": [6, 7, 9, 9, 10],
    "Total Score": [17, 15, 23, 23, 25],
    "Rank": [3,4,2,2,1]
})

Solution

  • For example, you can multiply the EScore by 0.01 and the DScore by 0.001 to weight them more lightly. Then, you can add these values to Total Score and calculate the rank.

    rank with method=dense & ascending=False

    df['Rank'] = df['Total Score'].add(df['EScore'].mul(0.01)).add(df['DScore'].mul(0.0001)).rank(ascending=False, method='dense').astype('int')
    

    df

       DScore  EScore  Total Score  Rank
    0       2       6           17     3
    1       2       7           15     4
    2       4       9           23     2
    3       4       9           23     2
    4       5      10           25     1
    

    The example you provided is sufficient with 0.01 and 0.0001, but these numbers should be adjusted to fit the dataset.