Generate a graphic enriched tabel summarising rally evolution over multiple stages, rebased to a specific driver.
if __name__=='__main__':
%load_ext autoreload
%autoreload 2
import notebookimport
sr = __import__("Charts - Stage Results")
The autoreload extension is already loaded. To reload it, use: %reload_ext autoreload
# TO DO
# do a step line chart for relative / rebased positions ahead / behind
import pandas as pd
from IPython.display import HTML
import dakar_utils as dakar
from dakar_utils import moveColumn, sparkline2, sparklineStep, moreStyleDriverSplitReportBaseDataframe
dbname2='mexico19.db'
conn2 = sqlite3.connect(dbname2)
c2 = conn2.cursor()
from IPython.display import HTML
def _rebaseTimes(times, bib=None):
if bib is None: return times
return times - times.loc[bib]
REBASER=306
q= 'SELECT entryId, `driver.code` AS Code FROM startlists'
codes = pd.read_sql(q,conn2).set_index('entryId')
codes.head()
Code | |
---|---|
entryId | |
19180 | OGI |
19181 | SUN |
19182 | LAP |
19183 | MEE |
19184 | SOR |
#For WRC
YEAR=2019
rc='RC1'
rally='Mexico'
typ='overall'
wREBASE='OGI'
How do we limit the report to just show the stages on a particular day, or particular loop?
#Based on a function in Itinerary Basics
def dbGetSSitinerary(conn, rally, year=YEAR):
''' Get dataframe containing time control details for a specified rally. '''
q='''
SELECT il.name AS date, itc.*, ce.timeZoneOffset,
isc.itinerarySectionId, isc.name AS section, isc.`order`
FROM itinerary_controls itc
JOIN championship_events ce ON itc.eventId=ce.eventId
JOIN itinerary_sections isc ON itc.`itinerarySections.itinerarySectionId`=isc.itinerarySectionId
JOIN itinerary_legs il ON isc.itineraryLegId=il.itineraryLegId
WHERE ce.`country.name`="{rally}" AND strftime('%Y', startDate)='{year}'
AND firstCarDueDateTimeLocal NOT NULL
AND itc.type='StageStart'
ORDER BY firstCarDueDateTimeLocal
'''.format(rally=rally, year=year)
time_controls = pd.read_sql(q,conn)
time_controls['firstCarDueDateTimeLocal']=pd.to_datetime(time_controls['firstCarDueDateTimeLocal'])
return time_controls
Create a day index so that we can limit reports to show a particular day, set of days, or up to and including a particular day.
We could also support reporting by a section selection.
def listify(items):
''' Turn an argument to a list. '''
return [] if items is None else items if isinstance(items, list) else [items]
def getStagesByDay(daynums=None, sections=None):
''' Return the stages for a given day, days, section or sections. '''
daynums = listify(daynums)
sections = listify(sections)
schedule = dbGetSSitinerary(conn2,rally)
#The grouper will return a group ID, but not in order?
#schedule['daynum'] = schedule.groupby('date').grouper.label_info
#https://stackoverflow.com/a/41638343/454773
schedule['index'] = schedule[['date']].merge( schedule.drop_duplicates( 'date' ).reset_index(), on='date' )['index'].rank(method='dense').astype(int)
tmp = schedule[['date','code','section','order','index']]
if daynums:
tmp = tmp[tmp['index'].isin(daynums)]
if sections:
tmp = tmp[tmp['order'].isin(sections)]
return tmp
date | code | section | order | index | |
---|---|---|---|---|---|
1 | Friday 8th March | SS2 | Section 2 | 2 | 2 |
2 | Friday 8th March | SS3 | Section 2 | 2 | 2 |
3 | Friday 8th March | SS4 | Section 2 | 2 | 2 |
getStagesByDay(sections=2)
date | code | section | order | index | |
---|---|---|---|---|---|
1 | Friday 8th March | SS2 | Section 2 | 2 | 2 |
2 | Friday 8th March | SS3 | Section 2 | 2 | 2 |
3 | Friday 8th March | SS4 | Section 2 | 2 | 2 |
def gapToLeaderBar(conn, rally, rc, typ, stages=None):
Xtmpq = sr.dbGetStageRank(conn, rally, rc, typ, stages)#.head()
Xtmpq = Xtmpq[['entryId','snum', 'diffFirstMs']].pivot(index='entryId',columns='snum',values='diffFirstMs')
Xtmpq = Xtmpq/1000
if typ=='stage':
Xtmpq.columns = ['SS_{}'.format(c) for c in Xtmpq.columns]
else:
Xtmpq.columns = ['SS_{}_{}'.format(c, typ) for c in Xtmpq.columns]
k = '{}GapToLeader'.format(typ)
Xtmpq[k] = Xtmpq[[c for c in Xtmpq.columns ]].values.tolist()
Xtmpq[k] = Xtmpq[k].apply(lambda x: [-y for y in x])
Xtmpq[k] = Xtmpq[k].apply(sparkline2, typ='bar', dot=True)
return Xtmpq
def gapBar(df):
''' Bar chart showing rebased gap at each stage. '''
col='Gap'
df[col] = df[[c for c in df.columns if c.startswith('SS_') and c.endswith('_overall')]].values.tolist()
df[col] = df[col].apply(lambda x: [-y for y in x])
df[col] = df[col].apply(sparkline2, typ='bar', dot=False)
return df
def positionStep(conn, rally, rc, typ, stages=None):
Xtmpq = sr.dbGetStageRank(conn, rally, rc, typ, stages)#.head()
Xtmpq = Xtmpq[['entryId','snum', 'position']].pivot(index='entryId',columns='snum',values='position')
Xtmpq.columns = ['SS_{}_{}_pos'.format(c, typ) for c in Xtmpq.columns]
k = '{}Position'.format(typ)
Xtmpq[k] = Xtmpq[[c for c in Xtmpq.columns ]].values.tolist()
Xtmpq[k] = Xtmpq[k].apply(lambda x: [-y for y in x])
Xtmpq[k] = Xtmpq[k].apply(sparklineStep)
return Xtmpq
# TO DO - this is really clunky; need a better way
def overallAtLastStage(conn, rally, rc, typ, stages=None):
''' Get overall rank associated with last stage in table. '''
Xtmpq = sr.dbGetStageRank(conn, rally, rc, typ, stages)#.head()
Xtmpq = Xtmpq[['entryId','snum', 'position']].pivot(index='entryId',columns='snum',values='position')
last = Xtmpq.columns
return Xtmpq[[last[-1]]]
def generateOverallResultsChartable(conn, rally, rc, rebase=None, stages=None, days=None, sections=None):
''' Generate overall results table for a particular event. '''
if days:
stages = listify(stages) + getStagesByDay(daynums=days)['code'].tolist()
if sections:
stages = listify(stages) + getStagesByDay(sections=sections)['code'].tolist()
wrc = pd.merge(codes, positionStep(conn, rally, rc, 'overall', stages=stages)[['overallPosition']], left_index=True, right_index=True)
wrc = pd.merge(wrc, gapToLeaderBar(conn, rally, rc, 'overall', stages), left_index=True, right_index=True)
moveColumn(wrc, 'overallGapToLeader', right_of='overallPosition')
wrc['Pos'] = overallAtLastStage(conn, rally, rc, typ, stages)
moveColumn(wrc, 'Pos', right_of='overallGapToLeader')
wrc = pd.merge(wrc, positionStep(conn, rally, rc, 'stage', stages)[['stagePosition']], left_index=True, right_index=True)
wrc = pd.merge(wrc, gapToLeaderBar(conn, rally, rc, 'stage', stages), left_index=True, right_index=True)
wrc.rename(columns={'stageGapToLeader':'stageWinnerGap'},inplace=True)
moveColumn(wrc, 'stageWinnerGap', right_of='stagePosition')
wrc = wrc.sort_values('Pos', ascending=True)
wrc=wrc.set_index('Code', drop=True)
#Some tidying up if we have stages in the db but no results...
wrc=wrc.dropna(how='all', axis='columns')
cols = [c for c in wrc.columns if c.startswith('SS')]
if rebase is not None:
wrc[cols] = -wrc[cols].apply(_rebaseTimes, bib=rebase, axis=0)
#This needs to be done after rebasing
wrc = gapBar(wrc)
moveColumn(wrc, 'Gap', left_of='stagePosition')
return wrc
tmp = generateOverallResultsChartable(conn2, rally, rc, rebase=wREBASE, days=1, sections=3)
tmp
overallPosition | overallGapToLeader | Pos | SS_1_overall | Gap | stagePosition | stageWinnerGap | SS_1 | |
---|---|---|---|---|---|---|---|---|
Code | ||||||||
LAP | <img src="data:image/png;base64,iVBORw0KGgoAAA... | <img src="data:image/png;base64,iVBORw0KGgoAAA... | 1 | 0.6 | <img src="data:image/png;base64,iVBORw0KGgoAAA... | <img src="data:image/png;base64,iVBORw0KGgoAAA... | <img src="data:image/png;base64,iVBORw0KGgoAAA... | 0.6 |
MIK | <img src="data:image/png;base64,iVBORw0KGgoAAA... | <img src="data:image/png;base64,iVBORw0KGgoAAA... | 2 | 0.5 | <img src="data:image/png;base64,iVBORw0KGgoAAA... | <img src="data:image/png;base64,iVBORw0KGgoAAA... | <img src="data:image/png;base64,iVBORw0KGgoAAA... | 0.5 |
MEE | <img src="data:image/png;base64,iVBORw0KGgoAAA... | <img src="data:image/png;base64,iVBORw0KGgoAAA... | 3 | -0.0 | <img src="data:image/png;base64,iVBORw0KGgoAAA... | <img src="data:image/png;base64,iVBORw0KGgoAAA... | <img src="data:image/png;base64,iVBORw0KGgoAAA... | -0.0 |
EVA | <img src="data:image/png;base64,iVBORw0KGgoAAA... | <img src="data:image/png;base64,iVBORw0KGgoAAA... | 4 | -0.0 | <img src="data:image/png;base64,iVBORw0KGgoAAA... | <img src="data:image/png;base64,iVBORw0KGgoAAA... | <img src="data:image/png;base64,iVBORw0KGgoAAA... | -0.0 |
NEU | <img src="data:image/png;base64,iVBORw0KGgoAAA... | <img src="data:image/png;base64,iVBORw0KGgoAAA... | 5 | -0.0 | <img src="data:image/png;base64,iVBORw0KGgoAAA... | <img src="data:image/png;base64,iVBORw0KGgoAAA... | <img src="data:image/png;base64,iVBORw0KGgoAAA... | -0.0 |
TÄN | <img src="data:image/png;base64,iVBORw0KGgoAAA... | <img src="data:image/png;base64,iVBORw0KGgoAAA... | 6 | -0.0 | <img src="data:image/png;base64,iVBORw0KGgoAAA... | <img src="data:image/png;base64,iVBORw0KGgoAAA... | <img src="data:image/png;base64,iVBORw0KGgoAAA... | -0.0 |
OGI | <img src="data:image/png;base64,iVBORw0KGgoAAA... | <img src="data:image/png;base64,iVBORw0KGgoAAA... | 7 | -0.0 | <img src="data:image/png;base64,iVBORw0KGgoAAA... | <img src="data:image/png;base64,iVBORw0KGgoAAA... | <img src="data:image/png;base64,iVBORw0KGgoAAA... | -0.0 |
SUN | <img src="data:image/png;base64,iVBORw0KGgoAAA... | <img src="data:image/png;base64,iVBORw0KGgoAAA... | 8 | -0.9 | <img src="data:image/png;base64,iVBORw0KGgoAAA... | <img src="data:image/png;base64,iVBORw0KGgoAAA... | <img src="data:image/png;base64,iVBORw0KGgoAAA... | -0.9 |
SOR | <img src="data:image/png;base64,iVBORw0KGgoAAA... | <img src="data:image/png;base64,iVBORw0KGgoAAA... | 9 | -0.9 | <img src="data:image/png;base64,iVBORw0KGgoAAA... | <img src="data:image/png;base64,iVBORw0KGgoAAA... | <img src="data:image/png;base64,iVBORw0KGgoAAA... | -0.9 |
LAT | <img src="data:image/png;base64,iVBORw0KGgoAAA... | <img src="data:image/png;base64,iVBORw0KGgoAAA... | 10 | -1.1 | <img src="data:image/png;base64,iVBORw0KGgoAAA... | <img src="data:image/png;base64,iVBORw0KGgoAAA... | <img src="data:image/png;base64,iVBORw0KGgoAAA... | -1.1 |
wREBASE='OGI'
tmp = generateOverallResultsChartable(conn2, rally, rc, rebase=wREBASE)
s2 = moreStyleDriverSplitReportBaseDataframe(tmp,'')
#Introduce a dot marker to highlight winner
display(HTML(s2))
dakar.getTablePNG(s2, fnstub='overall_{}_'.format(wREBASE),scale_factor=2)
overallPosition | overallGapToLeader | Pos | SS_1 Overall | SS_2 Overall | SS_3 Overall | SS_4 Overall | Gap | stagePosition | stageWinnerGap | SS_1 | SS_2 | SS_3 | SS_4 | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Code | ||||||||||||||
MIK | 1 | 0.5 | 2.9 | 0.7 | 1.6 | 0.5 | 2.4 | -2.2 | 0.9 | |||||
OGI | 2 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | -0 | |||||
EVA | 3 | -0 | -6 | -7.8 | -8.1 | -0 | -6 | -1.8 | -0.3 | |||||
MEE | 4 | -0 | -5.7 | -9 | -9.6 | -0 | -5.7 | -3.3 | -0.6 | |||||
LAT | 5 | -1.1 | -15.4 | -17.1 | -17.1 | -1.1 | -14.3 | -1.7 | -0 | |||||
LAP | 6 | 0.6 | -16.5 | -19.6 | -20.5 | 0.6 | -17.1 | -3.1 | -0.9 | |||||
TÄN | 7 | -0 | -19 | -26.1 | -26.6 | -0 | -19 | -7.1 | -0.5 | |||||
NEU | 8 | -0 | -41.7 | -48.1 | -48.3 | -0 | -41.7 | -6.4 | -0.2 | |||||
SUN | 0 | -0.9 | 0 | 0 | 0 | -0.9 | 0 | 0 | 0 | |||||
SOR | 0 | -0.9 | -1.1 | -1.6 | 0 | -0.9 | -0.2 | -0.5 | 0 |
'testpng/overall_OGI_.png'
...aka time left on table...
${}_{N}\Delta_i = \sum_{s=1}^N \Delta_{i,s}$ where $\Delta_{i,s}=t_{i,w}-t_{w,s}$ and $t_{i,s}$ is the time on stage $s$ for driver $i$ and $t_{w,s}$ is time on stage $s$ for the stage winner, $w$. We then plot $y={}_{N}\Delta_i$ against $x=s:1..N$ for driver $i$.
We can also look at turning that into a percentage, cf. Formula One 1075 times.
For example:${}_N\nabla_{i} = \frac{\sum_{s=1}^N t_{s,i}}{\sum_{s=1}^N t_{s,w}}$ and again plot $y={}_{N}\nabla_i$ against $x=s:1..N$ for driver $i$.
Note that this gives meaning to "giving 110%" in a roundabout sort of way. A driver might be running ar 105% winner time in early stages, then improve to bring this down to 103%..
We can also look to adding lower margins to table, e.g. searching for max(positive delta)
to find the amount of time lost to the leader on each stage.
Leader rebasing: also consider dynamic / leader rebasing; eg rebaser kernel is {'SS1':'LOE','SS2':'NEU', etc...}
then get times for each of those to rebase against.
#Need a WRC query for this
data
#cols SS, Overall position, Stage position, with a driver index
--------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-14-278f13cb03b6> in <module> 1 #Need a WRC query for this ----> 2 data 3 #cols SS, Overall position, Stage position, with a driver index NameError: name 'data' is not defined
wrc.plot(x='SS_1_overall',drawstyle="steps-mid",linestyle=':')
plt.gca().invert_yaxis()
--------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-29-b335486e5260> in <module> 1 wrc.plot(x='SS_1_overall',drawstyle="steps-mid",linestyle=':') ----> 2 plt.gca().invert_yaxis() NameError: name 'plt' is not defined