Relative minimum values in pandas

Viewed 45

i have the following dataframe in pandas:

    Race_ID Athlete_ID  Finish_time
0   1.0     1.0         56.1
1   1.0     3.0         60.2
2   1.0     2.0         57.1
3   1.0     4.0         57.2
4   2.0     2.0         56.2
5   2.0     1.0         56.3
6   2.0     3.0         56.4
7   2.0     4.0         56.5
8   3.0     1.0         61.2
9   3.0     2.0         62.1
10  3.0     3.0         60.4
11  3.0     4.0         60.0
12  4.0     2.0         55.0
13  4.0     1.0         54.0
14  4.0     3.0         53.0
15  4.0     4.0         52.0

where Race_ID is in descending order of time. (i.e. 1 is the most current race nad 4 is the oldest race)

And I want to add a new column Relative_time@t-1 which is the Athlete's Finish_time in the last race relative to the fastest time in the last race. Hence the output would look something like

    Race_ID Athlete_ID  Finish_time Relative_time@t-1
0   1.0     1.0         56.1        56.3/56.2
1   1.0     3.0         60.2        56.4/56.2
2   1.0     2.0         57.1        56.2/56.2
3   1.0     4.0         57.2        56.5/56.2
4   2.0     2.0         56.2        62.1/60
5   2.0     1.0         56.3        61.2/60
6   2.0     3.0         56.4        60.4/60
7   2.0     4.0         56.5        60/60
8   3.0     1.0         61.2        54/52
9   3.0     2.0         62.1        55/52
10  3.0     3.0         60.4        53/52
11  3.0     4.0         60.0        52/52
12  4.0     2.0         55.0        0
13  4.0     1.0         54.0        0
14  4.0     3.0         53.0        0
15  4.0     4.0         52.0        0

Here's the code:

data = [[1,1,56.1,'56.3/56.2'],
        [1,3,60.2,'56.4/56.2'],
        [1,2,57.1,'56.2/56.2'],
        [1,4,57.2,'56.5/56.2'],
        [2,2,56.2,'62.1/60'],
        [2,1,56.3,'61.2/60'],
        [2,3,56.4,'60.4/60'],
        [2,4,56.5,'60/60'],
        [3,1,61.2,'54/52'],
        [3,2,62.1,'55/52'],
        [3,3,60.4,'53/52'],
        [3,4,60,'52/52'],
        [4,2,55,'0'],
        [4,1,54,'0'],
        [4,3,53,'0'],
        [4,4,52,'0']]

df = pd.DataFrame(data,columns=['Race_ID','Athlete_ID','Finish_time','Relative_time@t-1'],dtype=float)

I intentionally made the Relative_time@t-1 as str instead of int to show the formula.

Here is what I have tried:

df.sort_values(by = ['Race_ID', 'Athlete_ID'], ascending=[True, True], inplace=True)
df['Finish_time@t-1'] = df.groupby('Athlete_ID')['Finish_time'].shift(-1)
df['Finish_time@t-1'] = df['Finish_time@t-1'].replace(np.nan, 0, regex = True)

So I get the numerator for the new column but I don't know how to get the minimum time for each Race_ID (i.e. the value in the denominator)

Thank you in advance.

1 Answers

Try this:

(df.groupby('Athlete_ID')['Finish_time']
.shift(-1)
.div(df['Race_ID'].map(
    df.groupby('Race_ID')['Finish_time']
    .min()
    .shift(-1)))
.fillna(0))

Output:

0     1.001779
1     1.003559
2     1.000000
3     1.005338
4     1.035000
5     1.020000
6     1.006667
7     1.000000
8     1.038462
9     1.057692
10    1.019231
11    1.000000
12    0.000000
13    0.000000
14    0.000000
15    0.000000
Related