Unable to Accurately Calculate Finishing Position by Cumulative Lap Time

[andrewestes41]

I am trying to calculate the position of each driver after each lap in the race (ideally we would know the position at each point in the race but I don't think that data is accessible yet). I am using the 2021 Saudi Arabia race as my test race for now. The issue is the final sorted value is not in the same order of the finishers.

The ending order should result with the point structure: 26, 18, 15, 12, 10, 8, 6, 4, 2, 1, 0, 0
However it is currently showing a final 12, 26, 15, 18, 10, 2, 1, 4, 0, 6, 8, 0
If we take away the pitstop on lap 50, the order is 18, 26, 12, 15, 10, 8, 4, 2, 6, 1, 0, 0.
If we remove all pit stops, there are still incorrect results: 18, 26, 12, 15, 10, 8, 6, 4, 1, 2, 0, 0

Below is the code I'm using. I'd appreciate any assistance on troubleshooting this!

#%% loading data
saudi_arabia_race_21 = ff1.get_session(2021, 21, 'R')
saudi_arabia_race_21.load()
saudi_arabia_race_21_laps = saudi_arabia_race_21.laps
saudi_arabia_race_21_messages= saudi_arabia_race_21.race_control_messages
saudi_arabia_race_21_weather= saudi_arabia_race_21.weather_data
saudi_arabia_race_21_results= saudi_arabia_race_21.results

#%% making data into date-time
saudi_arabia_race_21_results.rename(columns = {'Abbreviation':'Driver'}, inplace = True)
testMerge = pd.merge(saudi_arabia_race_21_laps, saudi_arabia_race_21_results[['Driver', 'GridPosition', 'Position', 'Points']],on='Driver', how='left')
testMerge.rename(columns = {'GridPosition':'StartPosition', 'Position':'EndPosition'}, inplace = True)
testMerge['TimeSeconds'] = testMerge['Time'].dt.total_seconds()
testMerge['LapTimeSeconds'] = testMerge['LapTime'].dt.total_seconds()
testMerge['PitInTimeSeconds'] = testMerge['PitInTime'].dt.total_seconds()
testMerge['PitOutTimeSeconds'] = testMerge['PitOutTime'].dt.total_seconds()
testMerge['Sector1TimeSeconds'] = testMerge['Sector1Time'].dt.total_seconds()
testMerge['Sector2TimeSeconds'] = testMerge['Sector2Time'].dt.total_seconds()
testMerge['Sector3TimeSeconds'] = testMerge['Sector3Time'].dt.total_seconds()
testMerge['LapStartTimeSeconds'] = testMerge['LapStartTime'].dt.total_seconds()
#testMerge.fillna(0,inplace=True)
testMerge['TotalTime'] = testMerge['Sector1TimeSeconds'] + testMerge['Sector2TimeSeconds'] + testMerge['Sector3TimeSeconds']

#%% making data with pitstop time
testMerge2 = testMerge
testMerge2['PitTime'] = testMerge2.PitOutTimeSeconds.shift(-1, fill_value=0) - testMerge2.PitInTimeSeconds
testMerge2['TotalTimeWithPit'] = testMerge2.TotalTime + testMerge2.PitTime

#%% function to create cumulative time
def cumulativeSum(s):
   sm = 0
   cumulative_list = []
   for i in s:
      sm = sm + i
      cumulative_list.append(sm)
   return cumulative_list

#%% looking and sorting cumulative time INCLUDING pitstops
cumulative = pd.DataFrame(cumulativeSum(testMerge2['TotalTimeWithPit']))
testMerge2.join(cumulative)

newCumulative = pd.DataFrame(testMerge2.groupby("Driver")["TotalTimeWithPit"].cumsum())
newdf = testMerge2.join(newCumulative, rsuffix='Woop')
testMergeSorted = newdf.sort_values(['LapNumber', 'TotalTimeWithPitWoop'])

testMergeFinal = testMergeSorted[[
'DriverNumber', 'LapNumber', 'Compound', 'TyreLife', 'FreshTyre', 'Stint', 
'Team', 'Driver', 'TrackStatus', 'StartPosition', 'EndPosition', 'Points', 
'PitInTimeSeconds', 'PitOutTimeSeconds', 'Sector1TimeSeconds', 
'Sector2TimeSeconds', 'Sector3TimeSeconds', 'TotalTime', 'PitTime',
'TotalTimeWithPit', 'TotalTimeWithPitWoop']]

testMergeFinal
#%% looking and sorting cumulative time EXCLUDING pitstops
cumulative2 = pd.DataFrame(cumulativeSum(testMerge2['TotalTime']))
tm2 = testMerge2.join(cumulative2)

newCumulative2 = pd.DataFrame(tm2.groupby("Driver")["TotalTime"].cumsum())
newdf2 = tm2.join(newCumulative2, rsuffix='WoopWoop')
tm2sorted = newdf2.sort_values(['LapNumber', 'TotalTimeWoopWoop'])
tm2sorted.head() 

tm2Final = tm2sorted[[
'DriverNumber', 'LapNumber', 'Compound', 'TyreLife', 'FreshTyre', 'Stint', 
'Team', 'Driver', 'TrackStatus', 'StartPosition', 'EndPosition', 'Points', 
'PitInTimeSeconds', 'PitOutTimeSeconds', 'Sector1TimeSeconds', 
'Sector2TimeSeconds', 'Sector3TimeSeconds', 'TotalTime', 'PitTime',
'TotalTimeWithPit', 'TotalTimeWoopWoop']]

tm2Final

[theOehrly]

Hey, I'm sorry, but I can't really help you at the moment. The code that you posted doesn't run. Please break it down to a minimal working example. That means, something that I can simply copy and paste to reproduce the problem.

[andrewestes41]

Appreciate you taking a look at this!

#%% loading libraries
import numpy as np
import pandas as pd

import fastf1 as ff1
from fastf1 import plotting
plotting.setup_mpl()

import matplotlib.pyplot as plt
from matplotlib.collections import LineCollection
from matplotlib import cm
import plotly.express as px
import plotly.graph_objs as go

from sklearn import linear_model
from patsy import dmatrices
import statsmodels.api as sm
import statsmodels.formula.api as smf

#%% loading data
saudi_arabia_race_21 = ff1.get_session(2021, 21, 'R')
saudi_arabia_race_21.load()
saudi_arabia_race_21_laps = saudi_arabia_race_21.laps
saudi_arabia_race_21_results= saudi_arabia_race_21.results

#%% making data into date-time
saudi_arabia_race_21_results.rename(columns = {'Abbreviation':'Driver'}, inplace = True)
testMerge = pd.merge(saudi_arabia_race_21_laps, saudi_arabia_race_21_results[['Driver', 'GridPosition', 'Position', 'Points']],on='Driver', how='left')
testMerge.rename(columns = {'GridPosition':'StartPosition', 'Position':'EndPosition'}, inplace = True)
testMerge['TimeSeconds'] = testMerge['Time'].dt.total_seconds()
testMerge['LapTimeSeconds'] = testMerge['LapTime'].dt.total_seconds()
testMerge['PitInTimeSeconds'] = testMerge['PitInTime'].dt.total_seconds()
testMerge['PitOutTimeSeconds'] = testMerge['PitOutTime'].dt.total_seconds()
testMerge['Sector1TimeSeconds'] = testMerge['Sector1Time'].dt.total_seconds()
testMerge['Sector2TimeSeconds'] = testMerge['Sector2Time'].dt.total_seconds()
testMerge['Sector3TimeSeconds'] = testMerge['Sector3Time'].dt.total_seconds()
testMerge['LapStartTimeSeconds'] = testMerge['LapStartTime'].dt.total_seconds()
#testMerge.fillna(0,inplace=True)

#calculating time
testMerge['TotalTime'] = testMerge['Sector1TimeSeconds'] + testMerge['Sector2TimeSeconds'] + testMerge['Sector3TimeSeconds']
testMerge['PitTime'] = (testMerge.PitOutTimeSeconds.shift(-1, fill_value=0) - testMerge.PitInTimeSeconds).clip(lower=0)
testMerge['TotalTimeWithPit'] = testMerge.TotalTime + testMerge.PitTime
testMerge['Cumulative'] = testMerge.groupby("Driver")["TotalTimeWithPit"].cumsum()
testMerge['CumulativePit'] = testMerge.groupby("Driver")["PitTime"].cumsum()
testMerge['CumulativeLap'] = testMerge.groupby("Driver")["TotalTime"].cumsum()

#sorting by lap and time
testMerge_sorted = testMerge
testMerge_sorted = testMerge.sort_values(['LapNumber', 'CumulativeLap'])
testMerge_sorted['Gap'] = (testMerge_sorted['Cumulative'] - testMerge_sorted['Cumulative'].shift()).clip(lower=0)
testMerge_sorted[['Driver', 'Points', 'Cumulative', 'CumulativePit', 'CumulativeLap']].tail(15)

testMerge_sorted = testMerge_sorted[[
'DriverNumber', 'LapNumber', 'Compound', 'TyreLife', 'FreshTyre', 'Stint', 'Gap',
'Team', 'Driver', 'TrackStatus', 'StartPosition', 'EndPosition', 'Points', 
'PitTime', 'PitInTimeSeconds', 'PitOutTimeSeconds', 'Sector1TimeSeconds', 
'Sector2TimeSeconds', 'Sector3TimeSeconds', 'Cumulative', 'CumulativeLap', 'CumulativePit']]

testMerge_sorted[['Driver', 'Points', 'Cumulative', 'CumulativePit', 'CumulativeLap']].tail(15)

[theOehrly]

I always get an error on the last statement, this part here

testMerge_sorted = testMerge_sorted[[
    'DriverNumber', 'LapNumber', 'Compound', 'TyreLife', 'FreshTyre', 'Stint', 'Gap',
    'Team', 'Driver', 'TrackStatus', 'StartPosition', 'EndPosition', 'Points',
    'PitTime', 'PitInTimeSeconds', 'PitOutTimeSeconds', 'Sector1TimeSeconds',
    'Sector2TimeSeconds', 'Sector3TimeSeconds', 'Cumulative', 'CumulativeLap', 'CumulativePit']]

Traceback (most recent call last):
  File "C:\Dateien\Code\Formula1\Fast-F1\exp_analysis.py", line 46, in <module>
    testMerge_sorted = testMerge_sorted[[
  File "C:\Dateien\Code\Formula1\Fast-F1\venv\lib\site-packages\pandas\core\frame.py", line 3767, in __getitem__
    indexer = self.columns._get_indexer_strict(key, "columns")[1]
  File "C:\Dateien\Code\Formula1\Fast-F1\venv\lib\site-packages\pandas\core\indexes\base.py", line 5876, in _get_indexer_strict
    self._raise_if_missing(keyarr, indexer, axis_name)
  File "C:\Dateien\Code\Formula1\Fast-F1\venv\lib\site-packages\pandas\core\indexes\base.py", line 5938, in _raise_if_missing
    raise KeyError(f"{not_found} not in index")
KeyError: "['EndPosition'] not in index"

[andrewestes41]

I'm not sure why 'EndPosition' is not in the index. It is created in this section:

#%% making data into date-time saudi_arabia_race_21_results.rename(columns = {'Abbreviation':'Driver'}, inplace = True) testMerge = pd.merge(saudi_arabia_race_21_laps, saudi_arabia_race_21_results[['Driver', 'GridPosition', 'Position', 'Points']],on='Driver', how='left') testMerge.rename(columns = {'GridPosition':'StartPosition', 'Position':'EndPosition'}, inplace = True)

I copy+pasted my code (made a slight change to how to handle NA values) and it went through as expected. Here is a snippet of the output from the final line:

The change for the NA values was
#testMerge.fillna(0, inplace=True) testMerge = testMerge.replace(np.nan, 0)

Regardless, you can probably get around the error by removing 'EndPosition' from the final statement that is throwing the error. We are already viewing the points so we know the final positioning data and know that it is out of order for some reason.

[theOehrly]

import pandas as pd

import fastf1 as ff1
from fastf1 import plotting

plotting.setup_mpl()



saudi_arabia_race_21 = ff1.get_session(2021, 21, 'R')
saudi_arabia_race_21.load()
saudi_arabia_race_21_laps = saudi_arabia_race_21.laps
saudi_arabia_race_21_results = saudi_arabia_race_21.results

# %% making data into date-time
saudi_arabia_race_21_results.rename(columns={'Abbreviation': 'Driver'}, inplace=True)
testMerge = pd.merge(saudi_arabia_race_21_laps,
                     saudi_arabia_race_21_results[['Driver', 'GridPosition', 'Position', 'Points']], on='Driver',
                     how='left')


### my code ###
testMerge['TotalTime'] = testMerge['Time'] - saudi_arabia_race_21.session_start_time
result = testMerge.groupby('Driver').tail(1).sort_values(by='TotalTime')

TOTAL_LAPS = 50  # lazy, you could also get it from the data

# exclude results of lapped cars that didn't complete the full distance
# they are mixed in between the other cars
result = result[~(result['LapNumber'] < TOTAL_LAPS)]


print(result[['Driver', 'DriverNumber', 'Points', 'TotalTime']])

results in

    Driver DriverNumber  Points              TotalTime
49     HAM           44    26.0 0 days 02:06:15.291000
99     VER           33    18.0 0 days 02:06:22.130000
149    BOT           77    15.0 0 days 02:06:42.814000
199    OCO           31    12.0 0 days 02:06:42.959000
249    RIC            3    10.0 0 days 02:06:55.431000
299    GAS           10     8.0 0 days 02:06:56.903000
349    LEC           16     6.0 0 days 02:06:59.789000
399    SAI           55     4.0 0 days 02:07:01.906000
449    GIO           99     2.0 0 days 02:07:13.801000
499    NOR            4     1.0 0 days 02:07:16.660000
549    STR           18     0.0 0 days 02:07:32.521000
599    LAT            6     0.0 0 days 02:07:38.549000

So everything works as expected.

You should not include the pit stops separately. They are already included in the lap and sector times. Something in your code is not working as intended. I don't know what and I don't feel like debugging it. I just wanted to validate that the data itself is correct.

[andrewestes41]

Appreciate you taking the time. I'll take your more elegant code and manipulate it for what I'm really looking for. Thanks again for your quick responses here.