pandas scoring system; show ties as “3-4”

I have a basic sports score at the end and the players ranking what I want to do is if their score is equal instead of one player being 3 and the other 4 I need them both to be 3-4, anyone have any good hints where I could find a solution?

            Name  100 m  Long jump  Shot put  High jump  400 m  110 m hurdles  Discus throw  Pole vault  Javelin throw           1500 m  Total Score  Ranking
1    Edan Daniele  12.61       5.00      9.22       1.50  60.39          16.43         21.60         2.6          35.81  00:05:25.720000       3847.0        1
2      Coos Kwesi  13.75       4.84     10.12       1.50  68.44          19.18         30.85         2.8          33.88  00:06:22.750000       3127.0        2
3  Severi Eileifr  13.43       4.35      8.64       1.50  66.06          19.05         24.89         2.2          33.48  00:06:51.010000       2953.0        3
4     Lehi Poghos  13.04       4.53      7.79       1.55  64.72          18.74         24.20         2.4          28.20  00:06:50.760000       2940.0        4

This is the outcome, and this is the code

import numpy as np
from os import sep
import pandas as pd
df  =   pd.read_csv("Decathlon.csv",sep=";",header=None)
df.reset_index(drop=False)
df.index = np.arange(1, len(df) + 1)
df.columns  =   ["Name","100 m","Long jump","Shot put","High jump","400 m","110 m hurdles","Discus throw","Pole vault","Javelin throw","1500 m"]

df['100m score'] =  round(25.4347*((18-df["100 m"])**1.81))
df["Long jump score"]   =   round(0.14354*(((df["Long jump"]-220)*-1)**1.4))
df["shot put score"]    =  round( 51.39*((df["Shot put"]-1.5)**1.05))
df["high jump score"]   =  round( 0.8465*(((df["High jump"]-75)*-1)**1.42))
df["400m score"]    =  round( 1.53775*((82-df["400 m"])**1.81))
df['110m hurdles score']  =  round( 5.74352*((28.5-df['110 m hurdles'])**1.92))
df['Discus throw score']  =  round( 12.91*((df['Discus throw']-4)**1.1))
df['Pole vault score']  = round(  0.2797*(((df['Pole vault']-100)*-1)*1.35))
df['Javelin throw score'] =  round( 10.14*(((df['Javelin throw']-7)**1.08)))
df['1500 m'] =  pd.to_datetime(df['1500 m'].str.strip(), format='%M.%S.%f')
df['Minute'] = pd.to_datetime(df['1500 m']).dt.minute
df['sekunde'] = pd.to_datetime(df['1500 m']).dt.second
df['milisekunde'] = pd.to_datetime(df['1500 m']).dt.microsecond
df.loc[df['milisekunde']>500000,['sekunde']]   =   df['sekunde']+1
df['Total seconds'] =   (df["Minute"]*60)   +   df["sekunde"]
df['1500 m score']  =   round(0.03768*((480-df["Total seconds"])**1.85))
df["Total Score"]   =   df['100m score']+df["Long jump score"]+df["shot put score"]+df["high jump score"]+df["400m score"]+df['110m hurdles score']+df['Discus throw score']+df['Pole vault score']+df['Javelin throw score']+df['1500 m score']
df["1500 m"]    =   pd.DatetimeIndex(df['1500 m']).time

#clean up
del df['100m score']
del df["Long jump score"] 
del df["shot put score"] 
del df["high jump score"]
del df["400m score"]
del df['110m hurdles score']
del df['Discus throw score']
del df['Pole vault score']
del df['Javelin throw score']
del df['Minute']
del df['sekunde']
del df['milisekunde']
del df["Total seconds"]
del df ["1500 m score"]
df = df.sort_values(['Total Score'], ascending  =   False)
df= df.reset_index(drop =   True)
df.index = np.arange(1, len(df) + 1)
df["Ranking"]   =   df.index
print(df)
df.to_json('Json file')

Answer

Assuming “Decathlon.csv” file looks something like this:

Edan Daniele;12.61;5.00;9.22;1.50;60.39;16.43;21.60;2.6;35.81;00:05:25.720000
Coos Kwesi;13.75;4.84;10.12;1.50;68.44;19.18;30.85;2.8;33.88;00:06:22.750000
Severi Eileifr;13.43;4.35;8.64;1.50;66.06;19.05;24.89;2.2;33.48;00:06:51.010000
Severi Eileifr;13.43;4.35;8.64;1.50;66.06;19.05;24.89;2.2;33.48;00:06:51.010000
Lehi Poghos;13.04;4.53;7.79;1.55;64.72;18.74;24.20;2.4;28.20;00:06:50.760000

Here’s how you can generate the rankings:

df["Ranking"] = df["Total Score"].apply(lambda score: df.index[df["Total Score"] == score].astype(str)).str.join("-")

output:

             Name  100 m  ...  Total Score  Ranking
1    Edan Daniele  12.61  ...       6529.0        1
2      Coos Kwesi  13.75  ...       6088.0        2
3  Severi Eileifr  13.43  ...       5652.0      3-4
4  Severi Eileifr  13.43  ...       5652.0      3-4
5     Lehi Poghos  13.04  ...       5639.0        5

or just use .tolist() to get rankings as a list:

df["Ranking"] = df["Total Score"].apply(lambda score: df.index[df["Total Score"] == score].tolist())
             Name  100 m  ...  Total Score  Ranking
1    Edan Daniele  12.61  ...       6529.0      [1]
2      Coos Kwesi  13.75  ...       6088.0      [2]
3  Severi Eileifr  13.43  ...       5652.0   [3, 4]
4  Severi Eileifr  13.43  ...       5652.0   [3, 4]
5     Lehi Poghos  13.04  ...       5639.0      [5]

Might not be the best approach though

Note: I’ve made rows 3 and 4 identical in the initial csv to match the example you’ve provided