# Summarize 3 variables into one and calculate a „skill“value + ranking

I have a data set that looks like this:

Name Top Speed Number Sprints Cumulative Sprint distance
Xyz 55 300 33.3
Xyz123 45 350 32.0

Top Speed is in km/h. Cumulated Sprint distance is in km. Number of sprints is a counted number.

I need a way to rank the data in a smart way by rating. each entry should have a rating, which is calculated from the 3 columns. Based on this rating I would like to rank the data. So someone with a high maximum speed, most sprints run and the total distance sprinted should have a better rating than someone who has a worse value overall. I don't care about the range. I just want it to be "logical" and smart. Overall, it's about giving each entry a value as a "skill" based on the 3 variables. Overall, I am thinking of a formula that I can apply to new data. It would also be useful to be able to weight variables here in the formula. So the influence in the target variable. Or do you see a ML model hier which I fit with the 3 columns and let him rate it? Which model would work here?

• Have you tried simply ranking each person on each of the different variables and then simply take the mean/median of those ranks to get an overall rank for each person? Oct 12, 2021 at 7:11
• Is this a common approach? My idea was to have a value that includes at least two variables, for example maximum speed + counted sprints. How about using both as ranking and then calculating median/mean over both? What if I want to weight here? Oct 12, 2021 at 7:54
• That would also be a possibility, but then you have to careful since you are combining variables with different ranges. This means that you are implicitly weighing the variable with a larger range more heavily, which in your example would be the number of sprints. Oct 12, 2021 at 8:00
• Can you describe your first proporsal with some python/pandas example? Oct 12, 2021 at 13:27

## 1 Answer

You can try using a simple ranking method that ranks each person on each of the column and then uses the mean or median of those ranks to get an overall of the person. This would look something like this when using the mean rank:

import pandas as pd

df = pd.DataFrame({
"Name": ["Xyz", "Xyz123"],
"Top Speed": [55, 45],
"Number Sprints": [300, 350],
"Cumulative Sprint Distance": [33.3, 32]
})

(
df
.set_index("Name")
.rank(numeric_only=True, ascending=False)
.assign(overall_rank = lambda x: x.mean(axis=1).rank())
)

Name Top Speed Number Sprints Cumulative Sprint Distance overall_rank
Xyz 1 2 1 1
Xyz123 2 1 2 2
• Thanks! But how would you now calculate a skill value out of the 3 variables? Like a new column which has a value which is influenced by the 3 others. Just sum them up is not that nice as we have 3 different kind of data types (speed, count and distance) . Something between a range of 1-10 for instance?or 0-1? what do you think? Oct 12, 2021 at 13:54
• You could once again use rank for this, but instead of the numerical rank use the percentile (using pct=True) and average those to get the average percentile ranking for each person. Oct 12, 2021 at 14:12