Driver Splits¶

if __name__=='__main__':
    #Let's see what data is available to us in the stagerank_overall table
    stagerank_overall = sr.getEnrichedStageRank(conn, rally, rc=rc, typ='overall')
    print(stagerank_overall.columns)
    display(stagerank_overall.head())

if __name__=='__main__':
    #Get the total stage time for specified driver on each stage
    #We can then subtract this from each driver's time to get their times as rebased delta times
    #  compared to the the specified driver
    rebaser = stagerank_overall[stagerank_overall['drivercode']==drivercode][['code','totalTimeMs']].set_index('code').to_dict(orient='dict')['totalTimeMs']
    display(rebaser)

if __name__=='__main__':
    #Preview the stagerank_overall contents for a particular stage
    display(stagerank_overall[stagerank_overall['code']==ss][['drivercode','position','totalTimeMs','code']])

if __name__=='__main__':
    zz=rebased_stage_stagerank(conn,rally,ss, drivercode, rc)
    display(zz)

if __name__=='__main__':
    display(stagerank_overall.columns)

if __name__=='__main__':
    #If there are no splits, ssd.dbGetSplits should optionally get the overall times from elsewhere as a single split
    splits = ssd.dbGetSplits(conn,rally,ss,rc)#, forcesingle=True)

    elapseddurations=ssd.getElapsedDurations(splits)
    display(elapseddurations.head())

if __name__=='__main__':
    roadPos = getRoadPosition(conn,rally,rc,ss)
    display(roadPos)

if __name__=='__main__':
    rebasedelapseddurations = ssd.rebaseElapsedDurations(elapseddurations, drivercode)
    #This returns columns of the form: drivercode	elapsedDurationS	startDateTime	section	rebased
    #If there are no splits, this is currently an empty dataframe
    rebasedelapseddurations = waypoint_rank(rebasedelapseddurations,by = 'elapsedDurationS')
    display(rebasedelapseddurations.head())

if __name__=='__main__':
    display(rbe)

if __name__=='__main__':
    #test of applying style to pandas dataframe
    #Is this really fown to pandas to fail gracefully if df is empty??
    s = rbe.style.applymap(color_negative)
    display(s)

if __name__=='__main__':
    #splitdurations are the time in each sector (time take to get from one split to the next)
    #But what if there are no splits? We get an empty dataframe...
    splitdurations = ssd.getSplitDurationsFromSplits(conn,rally,ss,rc)
    #splitdurations = waypoint_rank(splitdurations, 'section','stageTimeDurationMs' )
    display(splitdurations)#.head()

if __name__=='__main__':
    #This will be an empty dataframe if there are no splits
    rebasedSplits = ssd.rebaseSplitDurations(splitdurations, drivercode)
    display(rebasedSplits.head())

if __name__=='__main__':
    #preview what's available as a splitduration
    display(splitdurations[splitdurations['drivercode'].isin( ['PAD','NEU'])])

def pivotRebasedSplits(rebasedSplits):
    ''' For each driver row, find the split. '''
    
    #If there are no splits...
    if rebasedSplits.empty:
        return pd.DataFrame(columns=['drivercode']).set_index('drivercode')
    
    rbp=-rebasedSplits.pivot('drivercode','section','rebased')
    rbp.columns=['D{}'.format(c) for c in rbp.columns]
    rbp.sort_values(rbp.columns[-1],ascending =True)
    return rbp

if __name__=='__main__':
    rbp = pivotRebasedSplits(rebasedSplits)
    display(rbp)

if __name__=='__main__':
    #Just remind ourselves of what is available in the road position data
    display(roadPos)

def getDriverSplitReportBaseDataframe(rbe,rbp, zz, roadPos, stageresult, ss):
    #TO DO: return empty w/ proper colnames
    if roadPos.empty: return pd.DataFrame()
    ''' Create a base dataframe for the rebased driver split report. '''
    
    stageresult.columns = ['drivercode','Stage Rank']
    rb2 = pd.merge(rbe,stageresult.set_index('drivercode'),left_index=True, right_index=True)

    rb2=pd.merge(rb2,zz[['position','Overall Time']],left_index=True, right_index=True)
    rb2.rename(columns={'position': 'Overall Position'}, inplace=True)
    
    #The following is calculated rather than being based on the actual timing data / result for the previous stage
    #Would be better to explicitly grab data for previous stage, along with previous ranking
    #display(rb2[['Overall Time','{} Overall'.format(ss)]])
    rb2['Previous'] =  rb2['Overall Time'] - rb2['{} Overall'.format(ss)]
    #Related to this, would be useful to have an overall places gained / lost column
    
    rb2=pd.merge(rb2,rbp,left_index=True, right_index=True)
    rb2=pd.merge(rb2,roadPos[['Road Position']],left_index=True, right_index=True)
    cols=rb2.columns.tolist()
    #Reorder the columns - move Road Position to first column
    rb2=rb2[[cols[-1]]+cols[:-1]]
    
    #reorder cols
    prev = rb2['Previous']
    rb2.drop(labels=['Previous'], axis=1,inplace = True)
    rb2.insert(1, 'Previous', prev)
    
    moveColumn(rb2,'Waypoint Rank',right_of='Previous')
    moveColumn(rb2,'Rebase Gap',right_of='Waypoint Rank')
    #The following line is not correctly locating... it's offsetting by 1 pos to right?
    moveColumn(rb2,'gapToStageLeader',right_of='Overall Position')
    
    
    return rb2

if __name__=='__main__':
    stageresult=sr.getEnrichedStageRank(conn, rally, stages=ss, rc=rc,typ='stage')[['drivercode','position']]
    rb2=getDriverSplitReportBaseDataframe(rbe,rbp, zz, roadPos, stageresult, ss)
    display(rb2)

if __name__=='__main__':
    display(rb2.dtypes)

from IPython.core.display import HTML

if __name__=='__main__':
    html=s.render()
    display(HTML(html))

import seaborn as sns

def moreStyleDriverSplitReportBaseDataframe(rb2,ss, caption=None):
    ''' Style the driver split report dataframe. '''
    
    if rb2.empty: return ''
        
    def _subsetter(cols, items):
        ''' Generate a subset of valid columns from a list. '''
        return [c for c in cols if c in items]
    
    
    #https://community.modeanalytics.com/gallery/python_dataframe_styling/
    # Set CSS properties for th elements in dataframe
    th_props = [
      ('font-size', '11px'),
      ('text-align', 'center'),
      ('font-weight', 'bold'),
      ('color', '#6d6d6d'),
      ('background-color', '#f7f7f9')
      ]

    # Set CSS properties for td elements in dataframe
    td_props = [
      ('font-size', '11px')
      ]

    # Set table styles
    styles = [
      dict(selector="th", props=th_props),
      dict(selector="td", props=td_props)
      ]
    
    #Define colour palettes
    #cmg = sns.light_palette("green", as_cmap=True)
    #The blue palette helps us scale the Road Position column
    # This may help us to help identify any obvious road position effect when sorting stage times by stage rank
    cm=sns.light_palette((210, 90, 60), input="husl",as_cmap=True)

    s2=(rb2.style
        .background_gradient(cmap=cm, subset=_subsetter(rb2.columns, ['Road Position']))
        .applymap(color_negative,
                  subset=[c for c in rb2.columns if isinstance(c, int) and c not in ['Overall Position', 'Road Position']])
        .highlight_min(subset=_subsetter(rb2.columns, ['Overall Position']), color='lightgrey')
        .highlight_max(subset=_subsetter(rb2.columns, ['Overall Time']), color='lightgrey')
        .highlight_max(subset=_subsetter(rb2.columns, ['Previous']), color='lightgrey')
        .apply(bg_color,subset=_subsetter(rb2.columns, ['{} Overall'.format(ss),'{} Overall*'.format(ss), 'Overall Time', 'Previous']))
        .bar(subset=[c for c in rb2.columns if str(c).startswith('D')], align='zero', color=[ '#5fba7d','#d65f5f'])
        .set_table_styles(styles)
        
        #.format({'total_amt_usd_pct_diff': "{:.2%}"})
       )
    
    if caption is not None:
        s2.set_caption(caption)

    #nan issue: https://github.com/pandas-dev/pandas/issues/21527
    return s2.render().replace('nan','')

if __name__=='__main__':
    rb2c = cleanDriverSplitReportBaseDataframe(rb2.copy(), ss)
    s2 = moreStyleDriverSplitReportBaseDataframe(rb2c, ss)
    display(HTML(s2))

def getDriverStageReport(conn, rally, ss, drivercode, rc='RC1', typ='overall', order=None, caption=None):
    ''' Generate a dataframe to report overall stage result. '''
    #'Previous',' SS9 Overall', 'Overall Position'	'Overall Time'; stage position by sort order
    
    if order is None: order='stage'
    #change cols depending on what report / sort order ie. remove redundant col
    
    #Get the overall results, rebased
    zz = rebased_stage_stagerank(conn,rally,ss,drivercode,rc=rc, typ='overall')
    zz.rename(columns={'position':'Overall Position'}, inplace=True)
    
    #Get stage result - does it need to be enriched?
    stageresult=sr.getEnrichedStageRank(conn, rally, stages=ss,rc=rc, typ='stage')

    stagerebaser = stageresult[stageresult['drivercode']==drivercode][['code','elapsedDurationMs']].set_index('code').to_dict(orient='dict')['elapsedDurationMs']
    #The stagerank_overall['code'].map(rebaser) returns the total time for each stage achieved by the rebase driver
    # stagerank_overall['code'] identifies the stage
    #Subtract this rebase time from the overall stage time for each driver by stage

    stcol='{} Time'.format(ss)
    sdeltacol='{} Overall'.format(ss)
    stageresult[sdeltacol] = -(stageresult['elapsedDurationMs'] - stageresult['code'].map(stagerebaser))
    stageresult=stageresult[['drivercode', 'position','elapsedDuration', sdeltacol,'elapsedDurationMs']]
    stageresult.columns=['drivercode', 'Stage Rank',stcol, sdeltacol,'stageDurationMs']
    stageresult[stcol] = stageresult[stcol].str.replace('00000','')
    
    combined = pd.merge(zz,stageresult, on='drivercode' )
    
    combined[sdeltacol] = combined[sdeltacol]/1000
    combined['Previous'] = (combined['Overall Time']-combined[sdeltacol])
    
    _tmp=combined[['drivercode','Previous','Stage Rank',stcol,sdeltacol,'Overall Position','Overall Time']].replace(0,NaN).set_index('drivercode')

    if order=='overall':
        combined=combined.sort_values('Overall Position', ascending=True)
    elif order=='previous':
        combined=combined.fillna(0).sort_values('Previous', ascending=False).replace(0,NaN)
    elif order=='stage':
        combined=combined.sort_values('Stage Rank', ascending=True)
    else:
        #Default is stage order
        combined=combined.sort_values('Stage Rank', ascending=True)
    
    s2 = moreStyleDriverSplitReportBaseDataframe(_tmp, ss, caption)
    return s2

if __name__=='__main__':
    s2=getDriverStageReport(conn, rally, ss, drivercode)
    display(HTML(s2))

def getDriverSplitsReport(conn, rally, ss, drivercode, rc='RC1', typ='overall', 
                          order=None, caption=None, bars=True, dropcols=None):
    ''' Generate dataframe report relative to a given driver on a given stage.
            order: sorts table according to: overall | previous | roadpos
            
        At the moment, the splits reporter doesn't report anything if there are no splits.
        In this case, default to a simple overal stage (without splits) reporter table.
    '''
    
    dropcols = [] if dropcols is None else dropcols
    #TO DO - this needs to fail gracefully if there are no splits
    
    #Allow the drivercode to be relative to a position
    #if drivercode=='firstonroad':
        #allow things like onroad1, onroad2?
    #    drivercode=
    #elif drivercode=='previousfirst':
        #allow things like previous1, previous2?
    #    drivercode = 
    #elif drivercode = 'stagewinner':
        #allowthings like stage1, stage2?
    #    drivercode = 
    
    
    #Get the overall results, rebased
    zz = rebased_stage_stagerank(conn,rally,ss,drivercode,rc=rc, typ=typ)
    
    #Get the road position
    roadPos = getRoadPosition(conn,rally,rc,ss)
    if roadPos.empty:
        #Should we automatically offer the stagetable report as an alternative
        return getDriverStageReport(conn, rally, ss, drivercode, rc=rc, order=order, caption=caption)
    
    #Get the splits
    splits = ssd.dbGetSplits(conn,rally,ss,rc)
    elapseddurations=ssd.getElapsedDurations(splits)
    
    #Rebase the split elapsed durations
    rebasedelapseddurations = ssd.rebaseElapsedDurations(elapseddurations, drivercode)
    rebasedelapseddurations = waypoint_rank(rebasedelapseddurations,by = 'elapsedDurationS')
    rbe = pivotRebasedElapsedDurations(rebasedelapseddurations, ss)
    
    #splitdurations are the time in each sector (time take to get from one split to the next)
    splitdurations = ssd.getSplitDurationsFromSplits(conn,rally,ss,rc)
    rebasedSplits = ssd.rebaseSplitDurations(splitdurations, drivercode)

    rbp = pivotRebasedSplits(rebasedSplits)

    #Get stage result to merge in stage position
    stageresult=sr.getEnrichedStageRank(conn, rally, rc=rc, stages=ss,typ='stage')[['drivercode','position']]

    rb2=getDriverSplitReportBaseDataframe(rbe, rbp, zz, roadPos, stageresult, ss)
    rb2 = cleanDriverSplitReportBaseDataframe(rb2, ss)
    if not bars:
        rb2=rb2.drop([c for c in rb2.columns if str(c).startswith('D')], axis=1)
        
    if ss=='SS1':
        rb2['Previous']=NaN

    if order=='overall':
        rb2=rb2.sort_values('Overall Position', ascending=True)
        #Remove the redundant column
        rb2=rb2.drop(['Overall Position'], axis=1)
        #rb2=rb2.rename(columns={'Overall Position':'{} Overall*'.format(ss)})
    elif order=='previous':
        rb2=rb2.fillna(0).sort_values('Previous', ascending=False).replace(0,NaN)
        #rb2 = rb2.rename(columns={'Previous':'Previous*'})
    elif order=='roadpos':
        rb2=rb2.sort_values('Road Position', ascending=True)
        #rb2 = rb2.rename(columns={'Road Position':'Road Position*'})
    elif order=='stage':
        rb2.sort_values('Stage Rank', ascending=True)
        #Remove the redundant column
        rb2=rb2.drop(['Stage Rank'], axis=1)
    else:
        #Default is stage order
        rb2.sort_values('Stage Rank', ascending=True)
        #Remove the redundant column
        rb2=rb2.drop(['Stage Rank'], axis=1)
        rb2 = rb2.rename(columns={'{} Overall'.format(ss):'{} Overall*'.format(ss)})

    if caption =='auto':
        caption = 'Rebased stage split times for {}{}.'.format('{}, '.format(drivercode), ss)

    dc = [c for c in dropcols if c in rb2.columns]
    rb2 = rb2.drop(columns=dc)
        
    #s = styleDriverSplitReportBaseDataframe(rb2, ss)
    s2 = moreStyleDriverSplitReportBaseDataframe(rb2,ss, caption)
    return s2

if __name__=='__main__':
    s2 = getDriverSplitsReport(conn, rally, ss, drivercode, rc, typ)#, caption='auto')
    display(HTML(s2))

if __name__=='__main__':
    ss='SS19'
    d='TÄN'
    s2 = getDriverSplitsReport(conn, rally, ss, d, rc, typ)
    display(HTML(s2))

if __name__=='__main__':
    s2 = getDriverSplitsReport(conn, rally, 'SS10', 'PAD', rc, typ, 'roadpos')
    display(HTML(s2))

if __name__=='__main__':
    s2 = getDriverSplitsReport(conn, rally, 'SS20', 'TÄN', rc, typ,'stage')
    display(HTML(s2))