{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"\n#  <center> Big Data Derby 2022 - EDA - Jockeys' Journeys in a year</center>\n\n\n## Outline of this analysis\n\n\n<a id=\"toc\"></a>\n\n- [1. Win win win!](#1)\n    - 1.1 Who won most races in 2019? Who finish in top 3 position in most races?\n    - 1.2 Who has the highest win rate? Who has the place rate (finished in 1st, 2nd, and 3rd palces)?\n\n    \n    \n- [2. How did jockeys' performance progress over time? ](#2)\n    - 2.1 The journey of Manuel Franco, Jose Lezcano, Javier Castellano, Irad Ortiz Jr.\n\n    \n\n\n- [3. Zero-sum game](#3)\n    - 3.1 Who frequently competed in the same races?\n    - 3.2 Can one jockey maintain his advantage over another jockey throughout all races?\n\n\n- [4. Take aways](#4)\n    \n","metadata":{}},{"cell_type":"code","source":"import numpy as np \nimport pandas as pd \nimport matplotlib.pyplot as plt\nimport matplotlib.lines as mlines\nimport pandas as pd\nimport statsmodels.api as sm\nimport seaborn as sns\n\nimport pyarrow.parquet as pq\nimport pyarrow as pa","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-09-25T03:51:48.614114Z","iopub.execute_input":"2022-09-25T03:51:48.614994Z","iopub.status.idle":"2022-09-25T03:51:50.302521Z","shell.execute_reply.started":"2022-09-25T03:51:48.614865Z","shell.execute_reply":"2022-09-25T03:51:50.298996Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#/kaggle/input/derby-2022-merge-and-convert-data-to-parquet-file/nyra_2019_complete.parquet\n\nrace_table = pd.read_csv('/kaggle/input/big-data-derby-2022/nyra_race_table.csv')\nstart_table = pd.read_csv('/kaggle/input/big-data-derby-2022/nyra_start_table.csv', header=None)\n# tracking_table = pd.read_csv('/kaggle/input/big-data-derby-2022/nyra_tracking_table.csv')\nstart_table.columns=['track_id', 'race_date', 'race_number', 'program_number', 'weight_carried', 'jockey', 'odds', 'position_at_finish']","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-09-25T03:51:50.304252Z","iopub.execute_input":"2022-09-25T03:51:50.305358Z","iopub.status.idle":"2022-09-25T03:51:50.371468Z","shell.execute_reply.started":"2022-09-25T03:51:50.305300Z","shell.execute_reply":"2022-09-25T03:51:50.369843Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_type_dict =   {  'STK': 'Stakes', \n                      'WCL': 'Waiver Claiming', \n                      'WMC': 'Waiver Maiden Claiming', \n                      'SST': 'Starter Stakes', \n                      'SHP': 'Starter Handicap',\n                      'CLM': 'Claiming', \n                      'STR': 'Starter Allowance', \n                      'AOC': 'Allowance Optionl Claimer', \n                      'SOC': 'Starter Optional Claimer', \n                      'MCL': 'Maiden Claiming', \n                      'ALW': 'Allowance', \n                      'MSW': 'Maiden Special Weight'\n                      }\n\ntrack_id_dict = dict(AQU = 'Aqueduct', BEL = 'Belmont', SAR = 'Saratoga')\ncourse_type_dict = dict(M = 'Hurdle', D = 'Dirt', O = 'Outer turf', I = 'Inner turf', T = 'turf')\ntrack_condition_dict =  dict(YL = 'Yielding', FM ='Firm', SY = 'Sloppy', GD = 'Good', FT = 'Fast', MY = 'Muddy', SF = 'Soft')\n\nrace_table['race_type'] = race_table['race_type'].map(race_type_dict)\nrace_table['track_id'] = race_table['track_id'].map(track_id_dict)\nrace_table['course_type'] = race_table['course_type'].map(course_type_dict)\n\nrace_table['track_condition']=race_table['track_condition'].apply(lambda x:x.replace(\" \", ''))\nrace_table['track_condition'] = race_table['track_condition'].map(track_condition_dict)\n\n\nstart_table['track_id'] = start_table['track_id'].map(track_id_dict)\n\nprint(start_table.shape, race_table.shape)\nstart_table = start_table.merge(race_table, on=['track_id','race_date', 'race_number'], how='inner')\nprint(start_table.shape, race_table.shape)","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2022-09-25T03:51:50.373164Z","iopub.execute_input":"2022-09-25T03:51:50.374488Z","iopub.status.idle":"2022-09-25T03:51:50.438125Z","shell.execute_reply.started":"2022-09-25T03:51:50.374438Z","shell.execute_reply":"2022-09-25T03:51:50.436275Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#['PiYG', 'PRGn', 'BrBG', 'PuOr', 'RdGy', 'RdBu', 'RdYlBu','RdYlGn', 'Spectral', 'coolwarm', 'bwr', 'seismic']\n#['Pastel1', 'Pastel2', 'Paired', 'Accent', 'Dark2','Set1', 'Set2', 'Set3', 'tab10', 'tab20', 'tab20b', 'tab20c']\n# ['Greys', 'Purples', 'Blues', 'Greens', 'Oranges', 'Reds','YlOrBr', 'YlOrRd', 'OrRd', 'PuRd', 'RdPu', 'BuPu','GnBu', 'PuBu', 'YlGnBu', 'PuBuGn', 'BuGn', 'YlGn']\ncaption = ''\ndef highlight_max(styler):\n    #styler.set_caption(\"Number of races per course type and distance\")\n    styler.format(precision=0, na_rep='', thousands=\", \",)\n    #styler.background_gradient(axis=None, vmin=1, vmax=5, cmap=\"Blues\")#.background_gradient(cmap='Blues')  YlGnBu\n    styler.highlight_max(props='color:white;background-color:dodgerblue', axis=0)\n    styler.highlight_max(props='color:white;background-color:tomato;', axis=1)\n    styler.highlight_max(props='color:white; font-weight:bold; background-color:purple;', axis=None)\n    styler.set_caption(caption)\n    return styler\n\n\ndef highlight_min(styler):\n    #styler.set_caption(\"Number of races per course type and distance\")\n    styler.format(precision=0, na_rep='', thousands=\", \",)\n    #styler.background_gradient(axis=None, vmin=1, vmax=5, cmap=\"Blues\")#.background_gradient(cmap='Blues')  YlGnBu\n    styler.highlight_min(props='color:white;background-color:dodgerblue', axis=0)\n    styler.highlight_min(props='color:white;background-color:tomato;', axis=1)\n    styler.highlight_min(props='color:white; font-weight:bold; background-color:purple;', axis=None)\n    styler.set_caption(caption)\n    return styler\n","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-09-25T03:51:50.442655Z","iopub.execute_input":"2022-09-25T03:51:50.444070Z","iopub.status.idle":"2022-09-25T03:51:50.455253Z","shell.execute_reply.started":"2022-09-25T03:51:50.444022Z","shell.execute_reply":"2022-09-25T03:51:50.453824Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<a id=\"1\"></a>\n\n# <center>Win win win!</center>\n\n[back to top](#toc)\n\n## Who won most races in 2019? Who finish in top 3 position  in most races?\n\n\n*Note: In the following analysis, we define*\n\n> - `win rate` as percentage of races a jockey finished in 1st position out of all the races he participated in. \n> - `place rate` as percentage of races a jockey finished in 1st, 2nd, or 3rd position out of all the races he perticipated in.\n\n\n\nWhile there were 178 jockeys participated in the 2000 races in 2019, only 43 of them raced in 30 or more races. And the number goes down to 21 (the number of jockeys) who raced in 200 or more races.\n\nAmong the 2000 races in 2019, **Manuel Franco** participated in 1274 (63.7%) races, and won 214 of them. This means among all the races took place in 2019, more than one in ten races had the 1st place taken by him! He had a win rate of 17% and place rate around 49%. \n\nBut was he the one who finished in 1st, 2nd, or 3rd position in most races? The answer is YES!\n\nManuel Franco finished in 2nd position in 214 races and in 3rd position in 192 races. In total, he finished in top 3 positions in 620 races, which is 31% of all races!\n\nThe stunning numbers undoubtly proves Manuel Franco was the best jockey in 2019. Who is the 2nd best? \n\nThe answer is Jose Lezcano. he raced in 990 races, won 206 races and had win rate of 20.8%. He finished in top 3 positions in 491 races, which is 49.6% out of all races he participated. ","metadata":{}},{"cell_type":"code","source":"#https://github.com/arseniyturin/Capstone-Project/blob/master/plotting.py\n\n#plt.rcParams['figure.dpi'] = 227 #The default dpi in matplotlib is 100.\n\ndef hist(data, name, bins=50):\n    #plt.rcParams['figure.dpi'] = 227\n    plt.figure(figsize=(6,4))\n    plt.style.use('seaborn-whitegrid')\n    plt.hist(data, bins=bins, facecolor='#4ac2fb')\n    plt.title(name, fontsize=9)\n    plt.axvline(np.array(data.mean()), color='tomato', ls='--', lw=2)\n    plt.grid(visible=True, which='major', axis='both', color='lightgray', linestyle='--', linewidth=0.5)\n    plt.xlabel(data.name, fontsize=10)\n    plt.ylabel('count', fontsize=10)\n    plt.show()   \ndef qqplot(data):\n    #plt.rcParams['figure.dpi'] = 227\n    plt.figure(figsize=(16,6))\n    plt.style.use('seaborn-whitegrid')\n    sm.qqplot(data.dropna(), line='s', scale=1)\n    plt.title('Check for Normality', fontsize=16)\n    plt.show()\n    \ndef bar(data, caption, figsize=(12, 6)):\n    if data.index.dtype in ['int64', 'int32', 'float64', 'float32']:\n        data.index = data.index.astype(str)\n\n    plt.figure(figsize=figsize)\n    plt.style.use('seaborn-whitegrid')\n    plt.barh(data.index.values, data.values, color=\"lightseagreen\")\n    for i, v in enumerate(data.values):\n        plt.text(v + 3.9, i + .025, str(v), fontsize=12, color=\"teal\", fontweight='bold')#deepskyblue#dodgerblue\n    plt.title(caption, fontsize=15)\n    #plt.xlabel(data.name, fontsize=13)\n    plt.xticks([])\n    plt.grid(visible=True, which='major', axis='both', color='lightgray', linestyle='-', linewidth=0.2)\n    plt.gca().spines[['top', 'right']].set_visible(False)\n    plt.gca().spines[['bottom', 'left']].set_edgecolor('whitesmoke')\n    plt.rc('ytick', labelsize=13, ) \n    \n    plt.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-09-25T03:51:50.458110Z","iopub.execute_input":"2022-09-25T03:51:50.459243Z","iopub.status.idle":"2022-09-25T03:51:50.480140Z","shell.execute_reply.started":"2022-09-25T03:51:50.459159Z","shell.execute_reply":"2022-09-25T03:51:50.478216Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def stacked_barh(data, figsize=(15, 14), color_set='b', \n                 sum_col='sum', caption='', sub_title='',\n                 labels=[]):\n    fig, ax = plt.subplots(figsize = figsize)\n    plt.style.use('seaborn-whitegrid')\n    \n    if color_set=='b': #blue\n        colors = ['dodgerblue', 'deepskyblue', 'lightskyblue', 'lightcyan',  'aliceblue','royalblue']\n    elif color_set=='g': #green\n        colors = ['teal', 'lightseagreen', 'powderblue', 'lightcyan',  'azure','darkslategray']\n    \n    \n    for i in range(data.shape[1]-1): \n        if i>1:\n            fc = 'dimgray'\n        else:\n            fc = 'white'\n        if i<0:\n            ax.barh(y = data.index.values, width = data.iloc[:, i].values, height=0.8, color=colors[i], label=labels[i])\n        else:\n            ax.barh(y = data.index.values, width = data.iloc[:, i].values, height=0.8, left=data.iloc[:, :(i)].sum(axis=1).values, color=colors[i], label=labels[i])\n        ax.bar_label(ax.containers[i], label_type='center', color=fc, fontsize=10, fontweight=\"bold\")\n        \n        \n    ax.bar_label(ax.containers[i], labels=data[sum_col].map('{:.0f}'.format), label_type='edge', color=colors[-1], fontsize=10, fontweight=\"bold\")\n\n\n    ax.set_ylabel('')\n    ax.set_title(caption, fontsize=10, loc='left')\n    ax.legend()\n\n\n    plt.xlabel('', fontsize=13)\n    plt.xticks([])\n    plt.grid(visible=True, which='major', axis='both', color='lightgray', linestyle='-', linewidth=0.2)\n    plt.gca().spines[['top', 'right']].set_visible(False)\n    plt.gca().spines[['bottom', 'left']].set_edgecolor('whitesmoke')\n    #plt.rc('ytick', labelsize=13, ) \n    plt.suptitle(caption,fontsize=16, y=0.89, x=0.3)\n    plt.title(sub_title,fontsize=12, y=0.96, loc='left')\n    plt.show()    ","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-09-25T03:51:50.481978Z","iopub.execute_input":"2022-09-25T03:51:50.482482Z","iopub.status.idle":"2022-09-25T03:51:50.499565Z","shell.execute_reply.started":"2022-09-25T03:51:50.482438Z","shell.execute_reply":"2022-09-25T03:51:50.498296Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def _2lines(data, figsize=(15, 8), caption='', labels=[]):\n    #['teal', 'lightseagreen', 'powderblue', 'lightcyan',  'azure','darkslategray']\n    fig, ax = plt.subplots(figsize = figsize)\n    ax2 = ax.twinx()\n    ax.set_title(caption, fontsize=15, loc='left')\n    #ax.set_xlabel('time')\n\n    ax.plot(data.index, data.iloc[:, 0], marker='', markerfacecolor='teal', markersize=2, color='lightseagreen', linewidth=2)\n    ax2.plot(data.index, data.iloc[:, 1], marker='', markerfacecolor='tomato', color='salmon', linewidth=2, linestyle='--')\n\n    ax.set_ylabel(labels[0])\n    ax2.set_ylabel(labels[1])\n    ax.legend([labels[0]], loc='upper left')\n    ax2.legend([labels[1]], loc='upper center')\n    ax.yaxis.grid(color='lightgray', linestyle='dashed')\n\n    plt.tight_layout()\n\n    plt.show()\n    \ndef lines(data, figsize=(15, 8), caption='', labels=[]):\n    plt.figure(figsize=figsize)#dpi=100\n    colors = ['lightseagreen', 'salmon', 'lightskyblue', 'mediumseagreen', 'sandybrown', 'lightslategray', 'wheat', 'crimson']\n    mcolors = ['teal', 'tomato', 'skyblue', 'mediumseagreen', 'peru', 'slategray', 'tan',  'firebrick']\n    \n    for i in range(data.shape[1]):\n        plt.plot(data.index, data.iloc[:, i], marker='', markerfacecolor=mcolors[i], markersize=2, color=colors[i], linewidth=2, label=labels[i])\n        plt.text(data.shape[0] + 1, data.values[-1, i ], labels[i], fontsize=11, color=mcolors[i])\n\n    #plt.legend()\n    plt.title(caption,  fontsize=14, loc='left')\n\n    plt.grid(visible=True, which='major', axis='both', color='lightgray', linestyle='-', linewidth=0.2)\n    plt.gca().spines[['top', 'right']].set_visible(False)\n    plt.gca().spines[['bottom', 'left']].set_edgecolor('whitesmoke')\n    \n    plt.yticks(fontsize=9, )#rotation=90\n    plt.xticks(fontsize=9, )#rotation=90\n    plt.tight_layout()\n    plt.show()\n    ","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-09-25T03:51:50.501281Z","iopub.execute_input":"2022-09-25T03:51:50.502481Z","iopub.status.idle":"2022-09-25T03:51:50.521593Z","shell.execute_reply.started":"2022-09-25T03:51:50.502436Z","shell.execute_reply":"2022-09-25T03:51:50.519335Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#['PiYG', 'PRGn', 'BrBG', 'PuOr', 'RdGy', 'RdBu', 'RdYlBu','RdYlGn', 'Spectral', 'coolwarm', 'bwr', 'seismic']\n#['Pastel1', 'Pastel2', 'Paired', 'Accent', 'Dark2','Set1', 'Set2', 'Set3', 'tab10', 'tab20', 'tab20b', 'tab20c']\n# ['Greys', 'Purples', 'Blues', 'Greens', 'Oranges', 'Reds','YlOrBr', 'YlOrRd', 'OrRd', 'PuRd', 'RdPu', 'BuPu','GnBu', 'PuBu', 'YlGnBu', 'PuBuGn', 'BuGn', 'YlGn']\ncaption = ''\ndef highlight_max(styler):\n    #styler.set_caption(\"Number of races per course type and distance\")\n    styler.format(precision=0, na_rep='', thousands=\", \",)\n    #styler.background_gradient(axis=None, vmin=1, vmax=5, cmap=\"Blues\")#.background_gradient(cmap='Blues')  YlGnBu\n    styler.highlight_max(props='color:white;background-color:dodgerblue', axis=0)\n    styler.highlight_max(props='color:white;background-color:tomato;', axis=1)\n    styler.highlight_max(props='color:white; font-weight:bold; background-color:purple;', axis=None)\n    styler.set_caption(caption)\n    return styler\n\n\ndef highlight_min(styler):\n    #styler.set_caption(\"Number of races per course type and distance\")\n    styler.format(precision=0, na_rep='', thousands=\", \",)\n    #styler.background_gradient(axis=None, vmin=1, vmax=5, cmap=\"Blues\")#.background_gradient(cmap='Blues')  YlGnBu\n    styler.highlight_min(props='color:white;background-color:dodgerblue', axis=0)\n    styler.highlight_min(props='color:white;background-color:tomato;', axis=1)\n    styler.highlight_min(props='color:white; font-weight:bold; background-color:purple;', axis=None)\n    styler.set_caption(caption)\n    return styler\n","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-09-25T03:51:50.525024Z","iopub.execute_input":"2022-09-25T03:51:50.525833Z","iopub.status.idle":"2022-09-25T03:51:50.545957Z","shell.execute_reply.started":"2022-09-25T03:51:50.525766Z","shell.execute_reply":"2022-09-25T03:51:50.544679Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cs=pd.pivot_table(start_table[start_table['position_at_finish']<=3], values='race_date', \n               index=['jockey'], columns=['position_at_finish'], aggfunc=np.size)\ncs['sum'] = cs.sum(axis=1)\ncs.sort_values(by='sum', ascending=False, inplace=True)\ncs = cs.head(30)\ncs.sort_values(by='sum', ascending=True, inplace=True)\n\nstacked_barh(cs, figsize=(16, 15), color_set='g', sum_col='sum', \n             caption='Num of races jockey finished at 1st, 2nd, 3rd place', \n             sub_title = 'Manuel Franco won 214 races and finished in 2nd and 3rd place in 214 and 192 races respectively (214 + 214 + 192 = 620)',\n             labels=['1st place', '2nd place', '3rd place'])","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-09-25T03:51:50.547979Z","iopub.execute_input":"2022-09-25T03:51:50.549080Z","iopub.status.idle":"2022-09-25T03:51:51.963628Z","shell.execute_reply.started":"2022-09-25T03:51:50.549020Z","shell.execute_reply":"2022-09-25T03:51:51.962165Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cs0=pd.pivot_table(start_table, values='race_date', \n               index=['jockey'],aggfunc=np.size)\ncs0.columns=['total']\ncs0 = cs.merge(cs0, left_index=True, right_index=True, how='inner')\nfor i in [1, 2, 3, 'sum']:\n    cs0[i] = (100*cs0[i]/cs0['total']).round(0)\n\n\nstacked_barh(cs0[[1, 2, 3, 'sum']], figsize=(16, 15), color_set='g', sum_col='sum', \n             caption='% of races finished at top 3 places out of races ran by each jockey', \n             sub_title = 'Manuel Franco won 17% of races he ran and had 49% place rate (17 + 17 + 15 = 49)', \n             labels=['1st place', '2nd place', '3rd place'])","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-09-25T03:51:51.965445Z","iopub.execute_input":"2022-09-25T03:51:51.967095Z","iopub.status.idle":"2022-09-25T03:51:53.118497Z","shell.execute_reply.started":"2022-09-25T03:51:51.967013Z","shell.execute_reply":"2022-09-25T03:51:53.116150Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Who has the highest win rate? Who has the place rate (finished in 1st, 2nd, and 3rd palces)?\n\n> Note: this part of analysis excludes jockeys who ran less than 30 races to make the results statistical meaningful.\n\n\nThere were two jockeys had win rate more than 20% and they are Javier Castellano and Jose Lezcano. Both had win rate around 21%. Jose Lezcano raced in 990 races which is nearly half of the 2000 races in 2019. He was also in 2nd place in terms of number of races won by jockeys and number of races finished in 1st, 2nd, and 3rd places.\n\nFour jockeys had place rate more than 50%, and they are Javier Castellano, Irad Ortiz Jr. Joel Rosario, and ose L. Ortiz. Despite the fact that Manuel Franco won most number of races and had most number races in top 3 positions, his place rate was slightly below 50%.\n\nJavier Castellano raced in 541 races (i.e. 27% out of 2000 races) and had 55% place rate. Irad Ortiz Jr. raced in over 800 races (i.e. over 40% races) and had a place rate of 54%. \n","metadata":{}},{"cell_type":"code","source":"cs=pd.pivot_table(start_table[start_table['position_at_finish']<=3], values='race_date', \n               index=['jockey'], columns=['position_at_finish'], aggfunc=np.size, fill_value=0)\ncs['sum'] = cs.sum(axis=1)\n\ncs0=pd.pivot_table(start_table, values='race_number', \n               index=['jockey'],aggfunc=np.size)\ncs0.columns=['total']\ncs0 = cs.merge(cs0, left_index=True, right_index=True, how='inner')\nfor i in [1, 2, 3, 'sum']:\n    cs0[i] = (100*cs0[i]/cs0['total']).round(0)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-09-25T03:51:53.120566Z","iopub.execute_input":"2022-09-25T03:51:53.121964Z","iopub.status.idle":"2022-09-25T03:51:53.170047Z","shell.execute_reply.started":"2022-09-25T03:51:53.121916Z","shell.execute_reply":"2022-09-25T03:51:53.168752Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"stacked_barh(cs0[cs0['total']>=30].sort_values(by='sum').tail(30)[[1, 2, 3, 'sum']], \n             figsize=(16, 15), color_set='g', sum_col='sum', \n             caption='% of races finished at top 3 positions out of races ran by each jockey', \n             sub_title = 'Javier Castellano had a win rate of 21% and place rate of 55%, both were the highest among the jockeys.',\n             labels=['1st place', '2nd place', '3rd place'])","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-09-25T03:51:53.172392Z","iopub.execute_input":"2022-09-25T03:51:53.172939Z","iopub.status.idle":"2022-09-25T03:51:54.415992Z","shell.execute_reply.started":"2022-09-25T03:51:53.172887Z","shell.execute_reply":"2022-09-25T03:51:54.414656Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<a id=\"2\"></a>\n\n# <center>How did jockeys' performance progress over time?</center>\n\n[back to top](#toc)\n\n## The journey of Manuel Franco, Jose Lezcano, Javier Castellano, Irad Ortiz Jr.\n\n\n\n> Note: `win` means finish in the 1st position. `place` means finish in 1st, 2nd, or 3rd position.\n\n\n Manuel was unstoppable in the first 500 races. But once Javier and Irad joined the game, Manuel's performance hit a plateau. \n \n Irad and Javier joined the game around the 500th races in the year, and their winnings kept climbing linearly until around the 1500th race.\n \n It seemed that before the 500th and after the 1500th races, Manuel and Jose had their advantages, and between the 500th and 1250th race, Irad and Javier had their adavantages. ","metadata":{}},{"cell_type":"code","source":"jockeys = ['Manuel Franco', 'Jose Lezcano', 'Javier Castellano', 'Irad Ortiz Jr.']\n\ncs_ = start_table[['race_date', 'race_number']].drop_duplicates()\n\n\nfor jk in jockeys:\n    cs = start_table[start_table['jockey']==jk].sort_values(by=['race_date', 'race_number'])\n    cs.reset_index(drop=True, inplace=True)\n    cs['win'] = (cs['position_at_finish']<2).astype(int)\n    cs['place'] = (cs['position_at_finish']<4).astype(int)\n    cs['win_cumsum'] = cs['win'].cumsum()\n    cs['place_cumsum'] = cs['place'].cumsum()\n    \n    cs_ = cs_.merge(cs[['race_date', 'race_number', 'win']], on=['race_date', 'race_number'], how='left')\n    cs_.sort_values(by=['race_date', 'race_number'], inplace=True)\n    cs_.rename(columns={'win':jk}, inplace=True)\n    cs_[jk].fillna(0, inplace=True)\n    cs_[jk] = cs_[jk].cumsum()\n    \n    \n    _2lines(cs[['win_cumsum', 'place_cumsum']], figsize=(10, 5),  \n            caption=f\"{jk}'s journey: Num of wins and places over time\", \n            labels = ['cumulative wins', 'cumulative places'])\n    \ncs_.sort_values(by=['race_date', 'race_number'], inplace=True)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-09-25T03:51:54.422420Z","iopub.execute_input":"2022-09-25T03:51:54.422996Z","iopub.status.idle":"2022-09-25T03:51:55.934316Z","shell.execute_reply.started":"2022-09-25T03:51:54.422939Z","shell.execute_reply":"2022-09-25T03:51:55.933043Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"lines(cs_.iloc[:, 2:], figsize=(13, 6), caption='Cumulative num. of wins over time for each jockey',  labels = cs_.columns[2:])","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-09-25T03:51:55.935930Z","iopub.execute_input":"2022-09-25T03:51:55.936902Z","iopub.status.idle":"2022-09-25T03:51:56.271342Z","shell.execute_reply.started":"2022-09-25T03:51:55.936862Z","shell.execute_reply":"2022-09-25T03:51:56.269467Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"jockeys = ['Manuel Franco', 'Jose Lezcano', 'Javier Castellano', 'Irad Ortiz Jr.']\n\ncs_ = start_table[['race_date', 'race_number']].drop_duplicates()\n\n\nfor jk in jockeys:\n    cs = start_table[start_table['jockey']==jk].sort_values(by=['race_date', 'race_number'])\n    cs.reset_index(drop=True, inplace=True)\n    cs['win'] = (cs['position_at_finish']<2).astype(int)\n    cs['place'] = (cs['position_at_finish']<4).astype(int)\n    cs['run']=1\n    cs['win_cumsum'] = (cs['win'].cumsum())/(cs['run'].cumsum())\n    cs['place_cumsum'] = (cs['place'].cumsum())/(cs['run'].cumsum())\n    \n    \n    cs_ = cs_.merge(cs[['race_date', 'race_number', 'win', 'run']], on=['race_date', 'race_number'], how='left')\n    cs_.sort_values(by=['race_date', 'race_number'], inplace=True)\n    cs_.rename(columns={'win':jk, 'run':f'{jk}run'}, inplace=True)\n    cs_.fillna(0, inplace=True)\n    cs_[jk] = (cs_[jk].cumsum())/(cs_[f'{jk}run'].cumsum())\n    cs_.drop(columns=[f'{jk}run'], inplace=True)\n    \n    \n    lines(cs[['win_cumsum', 'place_cumsum']], figsize=(12, 6),  \n            caption=f\"{jk}'s journey: cumulative win rate and place rate over time\", \n            labels = ['cumulative win rate', 'cumulative place rate'])\n    \ncs_.sort_values(by=['race_date', 'race_number'], inplace=True)\ncs_ = cs_.replace([-np.inf, np.inf], np.nan).fillna(0)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-09-25T03:51:56.273108Z","iopub.execute_input":"2022-09-25T03:51:56.273545Z","iopub.status.idle":"2022-09-25T03:51:57.606843Z","shell.execute_reply.started":"2022-09-25T03:51:56.273507Z","shell.execute_reply":"2022-09-25T03:51:57.605171Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"lines(cs_.iloc[:, 2:], figsize=(15, 8), caption='Cumulative win rate over time for each jockey',  labels = cs_.columns[2:])","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-09-25T03:51:57.608511Z","iopub.execute_input":"2022-09-25T03:51:57.609689Z","iopub.status.idle":"2022-09-25T03:51:57.961843Z","shell.execute_reply.started":"2022-09-25T03:51:57.609580Z","shell.execute_reply":"2022-09-25T03:51:57.960259Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<a id=\"3\"></a>\n\n# <center>Zero-sum game</center>\n\n[back to top](#toc)\n\n\n## Who frequently competed in the same races?\n\n\n\n**Manuel Franco** and **Dylan Davis** competed against each in most number of races. They ran together in 726 races which accounted for more than 36% of all races in 2019.\n\n\nJose Lezcano and Manuel Franco competed in 659 (33% out of 2000) races; Jose Lezcano and Dylan Davis competed in 575 (28.8%) races.\n\n\n\n","metadata":{}},{"cell_type":"code","source":"cs = start_table[['track_id', 'race_date', 'race_number', 'jockey', 'program_number']]\n#cs.set_index(keys=['track_id', 'race_date', 'jockey'], inplace=True)\npcs = cs.pivot(index=['track_id', 'race_date',  'race_number',], columns='jockey', values='program_number')\n\npcs[pcs>'0']=1\npcs.fillna(0, inplace=True)\ncoocc = pcs.T.dot(pcs)\nnp.fill_diagonal(coocc.values, 0)\n\ncoocc_s = coocc.unstack().sort_values()\ncoocc_s.index.names = ['jockey1', 'jockey2']\ncoocc_s = coocc_s.reset_index()\ncoocc_s.columns = ['jockey1', 'jockey2', 'race_cnt']","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-09-25T03:51:57.963440Z","iopub.execute_input":"2022-09-25T03:51:57.963833Z","iopub.status.idle":"2022-09-25T03:51:58.229025Z","shell.execute_reply.started":"2022-09-25T03:51:57.963800Z","shell.execute_reply":"2022-09-25T03:51:58.227453Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"jockeys = ['Manuel Franco', 'Jose Lezcano', 'Javier Castellano', 'Irad Ortiz Jr.']\nfor jk in jockeys:\n    cs = coocc_s.loc[(coocc_s['jockey1']==jk) & (coocc_s['race_cnt']>0), ['jockey2', 'race_cnt']]\n    cs.set_index(keys=['jockey2'], inplace=True)\n    bar(cs['race_cnt'].tail(20), f'Who competed against {jk} ? The top 20 jockeys who competed most races against {jk}', figsize=(15, 9.5))  ","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-09-25T03:51:58.231001Z","iopub.execute_input":"2022-09-25T03:51:58.231549Z","iopub.status.idle":"2022-09-25T03:51:59.654861Z","shell.execute_reply.started":"2022-09-25T03:51:58.231496Z","shell.execute_reply":"2022-09-25T03:51:59.653751Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Can one jockey maintain his advantage over another jockey throughout all races?\n\n\nWe look at 3 pairs of jockeys (Manuel Franco and Dylan Davis, Jose Lezcano and Manuel Franco, Jose Lezcano and Dylan Davis) and see how they outperformed each other over time. \n\n\nFirst, we only look at the races either one in a pair won, and examine how the the win rates change over time.","metadata":{}},{"cell_type":"code","source":"for pair in [['Manuel Franco', 'Dylan Davis'], ['Jose Lezcano', 'Manuel Franco'], ['Jose Lezcano', 'Dylan Davis']]:\n    cs = pcs[pcs[pair].sum(axis=1)==2][pair].reset_index()[['track_id', 'race_date', 'race_number']]\n    cs.columns.name=''\n    # print(cs.shape)\n    cs = cs.merge(start_table, on = ['track_id', 'race_date', 'race_number'], how='inner')\n    cs = cs[cs['jockey'].isin(pair)]\n    # print(cs.shape)\n    cs.sort_values(by=['race_date', 'race_number'], ascending=[True, True], inplace=True)\n    cs_ = pd.pivot_table(cs[cs['position_at_finish']<=1], values='position_at_finish', \n                   index=['race_date', 'race_number'], columns=['jockey'], aggfunc=np.size, fill_value=0)\n    cs_.reset_index(drop=True, inplace=True)\n    for jk in pair:\n        cs_[jk] = (cs_[jk].cumsum())/(cs_.index+1)\n        \n    lines(cs_, figsize=(12, 5), caption=f'Races {pair[0]} and {pair[1]} ran together: win rate over time for each jockey',  labels = cs_.columns)\n    ","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-09-25T03:51:59.656363Z","iopub.execute_input":"2022-09-25T03:51:59.656937Z","iopub.status.idle":"2022-09-25T03:52:00.776368Z","shell.execute_reply.started":"2022-09-25T03:51:59.656902Z","shell.execute_reply":"2022-09-25T03:52:00.774949Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Then, we look at all the races each pair ran together and see how each one outperformed each other over time. For example, in a race, if one finished in 3rd position and the other finished in 5th position, then the one at 3rd position gets a point. \n\nManuel Franco and Dylan Davis: after the initial fluctuations in the first 100ish races, these two gained certain equilibrium.\n\nJose Lezcano and Manuel Franco: Jose gradually lost more and more to Manuel even though Manuel did not cath up with Jose - in terms of points got - in the end.\n\nJose Lezcano and Dylan Davis: Dylan quickly outperformed Jose in the first 50 races they two ran together. The pace slowed down after 250 races together, but Dylan maintained outperforming Jose to the end.","metadata":{}},{"cell_type":"code","source":"for pair in [['Manuel Franco', 'Dylan Davis'], ['Jose Lezcano', 'Manuel Franco'], ['Jose Lezcano', 'Dylan Davis']]:\n    cs = pcs[pcs[pair].sum(axis=1)==2][pair].reset_index()[['track_id', 'race_date', 'race_number']]\n    cs.columns.name=''\n    # print(cs.shape)\n    cs = cs.merge(start_table, on = ['track_id', 'race_date', 'race_number'], how='inner')\n    cs = cs[cs['jockey'].isin(pair)]\n    # print(cs.shape)\n    cs.sort_values(by=['race_date', 'race_number'], ascending=[True, True], inplace=True)\n\n    cs0 = cs.pivot(index=['race_date', 'race_number'], columns=['jockey'], values=['position_at_finish'])\n    cs0.columns = [b for _, b in cs0.columns]\n    cs_ = cs0.copy()\n    cs_[pair[0]]=(cs0[pair[0]]>cs0[pair[1]]).astype(int)\n    cs_[pair[1]]=(cs0[pair[0]]<cs0[pair[1]]).astype(int)\n    cs_.reset_index(drop=True, inplace=True)\n    \n    for jk in pair:\n        cs_[jk] = (cs_[jk].cumsum())/(cs_.index+1)\n        \n    lines(cs_, figsize=(12, 5), caption=f'Races {pair[0]} and {pair[1]} ran together: scores over time time for each jockey',  labels = cs_.columns)\n        ","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-09-25T03:52:00.777943Z","iopub.execute_input":"2022-09-25T03:52:00.778425Z","iopub.status.idle":"2022-09-25T03:52:01.683021Z","shell.execute_reply.started":"2022-09-25T03:52:00.778377Z","shell.execute_reply":"2022-09-25T03:52:01.681495Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<a id=\"4\"></a>\n\n# <center>Take aways</center>\n\n[back to top](#toc)\n\n\n## The competitions were among a small group of jockeys\n\n- 178 jockeys participated in the 2000 races in 2019 \n- 43 of them raced in 30+ races \n- 21 jockeys raced in 200+ races\n\n## Manuel Franco was the KING on tracks\n\n**Of all 2000 races in 2019:**\n\n- he raced in ~ 64% races\n- he won 10%+ races\n- he had a win rate of 17%  \n- he place rate around 49%\n\n## A sure thing for Javier Castellano?\n\nJavier Castellano had a win rate over 20% and 55% place rate, both numbers were the highest among all jockeys.\n\n\n## Some wins, some losses\n\n - Manuel was unstoppable in the first 500 races. But once Javier and Irad joined the game, Manuel's performance hit a plateau. \n - Irad and Javier joined the game around the 500th races in the year, and their winnings kept climbing linearly until around the 1500th race.\n - It seemed that before the 500th and after the 1500th races, Manuel and Jose had their advantages, and between the 500th and 1250th race, Irad and Javier had their adavantages. ","metadata":{}}]}