{"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":"# Exploration of Big Data Derby 2022 data\n\n## Table of contents\n* [Start with race data](#1)\n* [Start data](#2)\n* [Full data](#3)\n* [Drill down to specific races](#4)\n* [Analyze horse dynamics in a race](#5)","metadata":{}},{"cell_type":"code","source":"# packages\n\n# standard\nimport pandas as pd\nimport numpy as np\nimport time as time\n\n# plots\nimport matplotlib.pyplot as plt\nimport seaborn as sns\n\n# calc distance between geo coordinates\nfrom geopy.distance import great_circle as GRC","metadata":{"_uuid":"051d70d956493feee0c6d64651c6a088724dca2a","_execution_state":"idle","execution":{"iopub.status.busy":"2022-09-06T12:09:11.656313Z","iopub.execute_input":"2022-09-06T12:09:11.656703Z","iopub.status.idle":"2022-09-06T12:09:12.369926Z","shell.execute_reply.started":"2022-09-06T12:09:11.656670Z","shell.execute_reply":"2022-09-06T12:09:12.368729Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# show all columns in notebook\npd.set_option('display.max_columns', None)","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:09:14.108934Z","iopub.execute_input":"2022-09-06T12:09:14.109545Z","iopub.status.idle":"2022-09-06T12:09:14.115280Z","shell.execute_reply.started":"2022-09-06T12:09:14.109502Z","shell.execute_reply":"2022-09-06T12:09:14.114045Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# show files\n!ls -l '../input/big-data-derby-2022'","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:09:15.581368Z","iopub.execute_input":"2022-09-06T12:09:15.582211Z","iopub.status.idle":"2022-09-06T12:09:16.635954Z","shell.execute_reply.started":"2022-09-06T12:09:15.582160Z","shell.execute_reply":"2022-09-06T12:09:16.634461Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<a id='1'></a>\n# Start with race data","metadata":{}},{"cell_type":"code","source":"# import and first glance\ndf_race = pd.read_csv('../input/big-data-derby-2022/nyra_race_table.csv')\ndf_race.head(10)","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:09:18.437839Z","iopub.execute_input":"2022-09-06T12:09:18.439336Z","iopub.status.idle":"2022-09-06T12:09:18.492436Z","shell.execute_reply.started":"2022-09-06T12:09:18.439282Z","shell.execute_reply":"2022-09-06T12:09:18.491575Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Abbreviations\n\n#### Tracks: \n* AQU - Aqueduct\n* BEL - Belmont\n* SAR - Saratoga\n\n#### Course Type:\n* M - Hurdle\n* D - Dirt\n* O - Outer turf \n* I - Inner turf\n* T - Turf\n\n#### Track Condition:\n* YL - Yielding\n* FM - Firm\n* SY - Sloppy\n* GD - Good\n* FT - Fast\n* MY - Muddy\n* SF - Soft\n\n#### Race Type: \n* 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","metadata":{}},{"cell_type":"code","source":"# structure of data\ndf_race.info(verbose=True, show_counts=True)","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:09:22.389483Z","iopub.execute_input":"2022-09-06T12:09:22.390224Z","iopub.status.idle":"2022-09-06T12:09:22.419279Z","shell.execute_reply.started":"2022-09-06T12:09:22.390184Z","shell.execute_reply":"2022-09-06T12:09:22.417901Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# convert distance in furlongs (x100) to meters\ndf_race['distance_m'] = (pd.to_numeric(df_race.distance_id)/100) *  201.168","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:09:24.589305Z","iopub.execute_input":"2022-09-06T12:09:24.589688Z","iopub.status.idle":"2022-09-06T12:09:24.597240Z","shell.execute_reply.started":"2022-09-06T12:09:24.589658Z","shell.execute_reply":"2022-09-06T12:09:24.595999Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# basic stats\ndf_race.describe(include='all')","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:09:29.763834Z","iopub.execute_input":"2022-09-06T12:09:29.764210Z","iopub.status.idle":"2022-09-06T12:09:29.818125Z","shell.execute_reply.started":"2022-09-06T12:09:29.764179Z","shell.execute_reply":"2022-09-06T12:09:29.817089Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# add unique id\ndf_race['unique_id'] = df_race.track_id.astype(str) + '_' + df_race.race_date.astype(str) + '_' + df_race.race_number.astype(str)\ndf_race.unique_id.value_counts()","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:09:32.490534Z","iopub.execute_input":"2022-09-06T12:09:32.490925Z","iopub.status.idle":"2022-09-06T12:09:32.507675Z","shell.execute_reply.started":"2022-09-06T12:09:32.490893Z","shell.execute_reply":"2022-09-06T12:09:32.506455Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Using track, race date and race number we get a unique key for the individual races!","metadata":{}},{"cell_type":"markdown","source":"### Categorical Features","metadata":{}},{"cell_type":"code","source":"# define categorical features\nfeatures_race_cat = ['track_id','distance_id','course_type','track_condition','race_type']","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:09:34.810261Z","iopub.execute_input":"2022-09-06T12:09:34.810704Z","iopub.status.idle":"2022-09-06T12:09:34.815981Z","shell.execute_reply.started":"2022-09-06T12:09:34.810664Z","shell.execute_reply":"2022-09-06T12:09:34.814892Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# plot frequencies\nfor f in features_race_cat:\n    plt.figure(figsize=(8,4))\n    if (f=='distance_id'):\n        df_race[f].value_counts().sort_index().plot(kind='bar')\n    else:\n        df_race[f].value_counts().plot(kind='bar')\n    plt.title(f)\n    plt.grid()\n    plt.show()","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:09:36.744746Z","iopub.execute_input":"2022-09-06T12:09:36.745152Z","iopub.status.idle":"2022-09-06T12:09:37.784390Z","shell.execute_reply.started":"2022-09-06T12:09:36.745119Z","shell.execute_reply":"2022-09-06T12:09:37.783451Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Numerical Features","metadata":{}},{"cell_type":"code","source":"# define numerical features\nfeatures_race_num = ['run_up_distance','purse','post_time','distance_m']","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:09:41.128241Z","iopub.execute_input":"2022-09-06T12:09:41.128652Z","iopub.status.idle":"2022-09-06T12:09:41.133577Z","shell.execute_reply.started":"2022-09-06T12:09:41.128616Z","shell.execute_reply":"2022-09-06T12:09:41.132287Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# plot histogram and boxplot\nfor f in features_race_num:\n    fig, (ax1, ax2) = plt.subplots(2, 1, figsize=(12,6))\n    \n    ax1.hist(df_race[f], bins=20)\n    ax1.grid()\n    ax1.set_title(f)\n    \n    ax2.boxplot(df_race[f], vert=False)\n    ax2.grid()   \n    ax2.set_title(f)\n    \n    plt.show()","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:09:43.019669Z","iopub.execute_input":"2022-09-06T12:09:43.020066Z","iopub.status.idle":"2022-09-06T12:09:44.197639Z","shell.execute_reply.started":"2022-09-06T12:09:43.020033Z","shell.execute_reply":"2022-09-06T12:09:44.196686Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Correlation","metadata":{}},{"cell_type":"code","source":"# calc correlation matrices\ncorr_pearson = df_race[features_race_num].corr(method='pearson')\ncorr_spearman = df_race[features_race_num].corr(method='spearman') # rank correlation\n\n# plot side by side\nplt.figure(figsize=(12,4))\nax1 = plt.subplot(1,2,1)\nsns.heatmap(corr_pearson, annot=True, cmap='RdYlGn', vmin=-1, vmax=+1)\nplt.title('Pearson Correlation')\n\nax2 = plt.subplot(1,2,2, sharex=ax1)\nsns.heatmap(corr_spearman, annot=True, cmap='RdYlGn', vmin=-1, vmax=+1)\nplt.title('Spearman Correlation')\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:09:46.862438Z","iopub.execute_input":"2022-09-06T12:09:46.862835Z","iopub.status.idle":"2022-09-06T12:09:47.599438Z","shell.execute_reply.started":"2022-09-06T12:09:46.862801Z","shell.execute_reply":"2022-09-06T12:09:47.596406Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# pairwise scatter plot - grouped by track\nsns.pairplot(data=df_race, vars=features_race_num,\n             hue='track_id',\n             plot_kws= {'s' : 10, 'alpha' : 0.5})\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:14:36.540956Z","iopub.execute_input":"2022-09-06T12:14:36.541348Z","iopub.status.idle":"2022-09-06T12:14:41.590481Z","shell.execute_reply.started":"2022-09-06T12:14:36.541318Z","shell.execute_reply":"2022-09-06T12:14:41.589241Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Cross Tables","metadata":{}},{"cell_type":"code","source":"# course types by track\npd.crosstab(df_race.track_id, df_race.course_type)","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:14:54.008625Z","iopub.execute_input":"2022-09-06T12:14:54.009147Z","iopub.status.idle":"2022-09-06T12:14:54.039272Z","shell.execute_reply.started":"2022-09-06T12:14:54.009097Z","shell.execute_reply":"2022-09-06T12:14:54.038154Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# track conditions by track\npd.crosstab(df_race.track_id, df_race.track_condition)","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:14:56.402577Z","iopub.execute_input":"2022-09-06T12:14:56.403347Z","iopub.status.idle":"2022-09-06T12:14:56.425410Z","shell.execute_reply.started":"2022-09-06T12:14:56.403293Z","shell.execute_reply":"2022-09-06T12:14:56.424424Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<a id='2'></a>\n# Start Data","metadata":{}},{"cell_type":"code","source":"# import and first glance\ndf_start = pd.read_csv('../input/big-data-derby-2022/nyra_start_table.csv')\ndf_start.head(10)","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:14:59.163195Z","iopub.execute_input":"2022-09-06T12:14:59.164012Z","iopub.status.idle":"2022-09-06T12:14:59.217922Z","shell.execute_reply.started":"2022-09-06T12:14:59.163969Z","shell.execute_reply":"2022-09-06T12:14:59.216987Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# structure of start data\ndf_start.info()","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:15:00.778938Z","iopub.execute_input":"2022-09-06T12:15:00.779578Z","iopub.status.idle":"2022-09-06T12:15:00.796149Z","shell.execute_reply.started":"2022-09-06T12:15:00.779542Z","shell.execute_reply":"2022-09-06T12:15:00.795113Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# basic stats\ndf_start.describe(include='all')","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:15:01.768459Z","iopub.execute_input":"2022-09-06T12:15:01.769270Z","iopub.status.idle":"2022-09-06T12:15:01.984884Z","shell.execute_reply.started":"2022-09-06T12:15:01.769227Z","shell.execute_reply":"2022-09-06T12:15:01.984097Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# horses and jockeys\nctab_horse_jockey = pd.crosstab(df_start.program_number, df_start.jockey)\nctab_horse_jockey","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:15:04.961215Z","iopub.execute_input":"2022-09-06T12:15:04.961608Z","iopub.status.idle":"2022-09-06T12:15:05.128284Z","shell.execute_reply.started":"2022-09-06T12:15:04.961576Z","shell.execute_reply":"2022-09-06T12:15:05.127355Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# jockey count\ndf_start.jockey.value_counts()","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:15:13.386343Z","iopub.execute_input":"2022-09-06T12:15:13.386997Z","iopub.status.idle":"2022-09-06T12:15:13.396787Z","shell.execute_reply.started":"2022-09-06T12:15:13.386958Z","shell.execute_reply":"2022-09-06T12:15:13.395595Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# jockey count - top 20\nplt.figure(figsize=(10,5))\ndf_start.jockey.value_counts()[0:20].plot(kind='bar')\nplt.title('Jockey - Count (Top 20 only)')\nplt.grid()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:15:15.356418Z","iopub.execute_input":"2022-09-06T12:15:15.356849Z","iopub.status.idle":"2022-09-06T12:15:15.623455Z","shell.execute_reply.started":"2022-09-06T12:15:15.356811Z","shell.execute_reply":"2022-09-06T12:15:15.622211Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# program number (horses) count\nplt.figure(figsize=(10,5))\ndf_start.program_number.value_counts().plot(kind='bar')\nplt.title('Program Number - Count')\nplt.grid()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:15:17.788916Z","iopub.execute_input":"2022-09-06T12:15:17.790210Z","iopub.status.idle":"2022-09-06T12:15:18.027534Z","shell.execute_reply.started":"2022-09-06T12:15:17.790153Z","shell.execute_reply":"2022-09-06T12:15:18.026406Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# distribution of odds\nplt.figure(figsize=(10,5))\ndf_start.odds.plot(kind='hist', bins=50)\nplt.title('Odds')\nplt.grid()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:15:20.395858Z","iopub.execute_input":"2022-09-06T12:15:20.397106Z","iopub.status.idle":"2022-09-06T12:15:20.636195Z","shell.execute_reply.started":"2022-09-06T12:15:20.397061Z","shell.execute_reply":"2022-09-06T12:15:20.635083Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# plot odds vs weight carried\nplt.figure(figsize=(8,8))\nplt.scatter(df_start.weight_carried, df_start.odds, alpha=0.25)\nplt.title('Odds vs. Weight Carried')\nplt.xlabel('Weight')\nplt.ylabel('Odds')\nplt.grid()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:15:22.880436Z","iopub.execute_input":"2022-09-06T12:15:22.881350Z","iopub.status.idle":"2022-09-06T12:15:23.092993Z","shell.execute_reply.started":"2022-09-06T12:15:22.881311Z","shell.execute_reply":"2022-09-06T12:15:23.091832Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<a id='3'></a>\n# Full Data","metadata":{}},{"cell_type":"code","source":"# import data\nt1 = time.time()\ndf = pd.read_csv('../input/big-data-derby-2022/nyra_2019_complete.csv',\n                 low_memory=False)\nt2 = time.time()\nprint('Elapsed time [s]: ', np.round(t2-t1,4))","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:15:26.020313Z","iopub.execute_input":"2022-09-06T12:15:26.020730Z","iopub.status.idle":"2022-09-06T12:15:41.786151Z","shell.execute_reply.started":"2022-09-06T12:15:26.020695Z","shell.execute_reply":"2022-09-06T12:15:41.784824Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# show first few rows\ndf.head(5)","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:15:41.787860Z","iopub.execute_input":"2022-09-06T12:15:41.788180Z","iopub.status.idle":"2022-09-06T12:15:41.808486Z","shell.execute_reply.started":"2022-09-06T12:15:41.788151Z","shell.execute_reply":"2022-09-06T12:15:41.807276Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# structure of data\ndf.info(verbose=True, show_counts=True)","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:15:41.810521Z","iopub.execute_input":"2022-09-06T12:15:41.811399Z","iopub.status.idle":"2022-09-06T12:15:43.633817Z","shell.execute_reply.started":"2022-09-06T12:15:41.811337Z","shell.execute_reply":"2022-09-06T12:15:43.632547Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### The program numbers need some cleaning:","metadata":{}},{"cell_type":"code","source":"# show categories\ndf.program_number.value_counts().index","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:15:43.636699Z","iopub.execute_input":"2022-09-06T12:15:43.637803Z","iopub.status.idle":"2022-09-06T12:15:43.916433Z","shell.execute_reply.started":"2022-09-06T12:15:43.637767Z","shell.execute_reply":"2022-09-06T12:15:43.915131Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# remove redundant spaces\ndf.program_number = df.program_number.str.strip()","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:15:43.917905Z","iopub.execute_input":"2022-09-06T12:15:43.918301Z","iopub.status.idle":"2022-09-06T12:15:46.156640Z","shell.execute_reply.started":"2022-09-06T12:15:43.918259Z","shell.execute_reply":"2022-09-06T12:15:46.153553Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# show categories again\ndf.program_number.value_counts().index","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:15:46.162945Z","iopub.execute_input":"2022-09-06T12:15:46.165542Z","iopub.status.idle":"2022-09-06T12:15:46.523957Z","shell.execute_reply.started":"2022-09-06T12:15:46.165495Z","shell.execute_reply":"2022-09-06T12:15:46.522550Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# convert distance in furlongs (x100) to meters\ndf['distance_m'] = (pd.to_numeric(df.distance_id)/100) *  201.168","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:15:46.525776Z","iopub.execute_input":"2022-09-06T12:15:46.526168Z","iopub.status.idle":"2022-09-06T12:15:46.602166Z","shell.execute_reply.started":"2022-09-06T12:15:46.526136Z","shell.execute_reply":"2022-09-06T12:15:46.601268Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<a id='4'></a>\n# Drill down to specific races","metadata":{}},{"cell_type":"markdown","source":"## A race in Aqueduct","metadata":{}},{"cell_type":"code","source":"# filter by track, date and race number\ndf_select = df[(df.track_id=='AQU') &\n               (df.race_date=='2019-01-01') &\n               (df.race_number==1)]\n# preview\ndf_select.head()","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:15:53.465472Z","iopub.execute_input":"2022-09-06T12:15:53.467119Z","iopub.status.idle":"2022-09-06T12:15:54.462116Z","shell.execute_reply.started":"2022-09-06T12:15:53.467061Z","shell.execute_reply":"2022-09-06T12:15:54.460357Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# jockey and horses\nctab = pd.crosstab(df_select.jockey, df_select.program_number) / df_select.trakus_index.nunique()\nctab.astype(int)","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:15:56.624240Z","iopub.execute_input":"2022-09-06T12:15:56.625304Z","iopub.status.idle":"2022-09-06T12:15:56.654170Z","shell.execute_reply.started":"2022-09-06T12:15:56.625263Z","shell.execute_reply":"2022-09-06T12:15:56.653322Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# features for each jockey\ndf_jock = df_select.groupby('jockey')[['weight_carried','odds']].mean()\ndf_jock['inv_odds'] = 1/df_jock.odds\n# we can derive probability of winning from odds \n# by normalizing inverse values:\nnn = df_jock.inv_odds.sum()\ndf_jock['win_prob_perc'] = np.round(100*df_jock.inv_odds / nn,2)\ndf_jock","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:15:59.285690Z","iopub.execute_input":"2022-09-06T12:15:59.286757Z","iopub.status.idle":"2022-09-06T12:15:59.310769Z","shell.execute_reply.started":"2022-09-06T12:15:59.286714Z","shell.execute_reply":"2022-09-06T12:15:59.309828Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# plot race\nplt.figure(figsize=(10,7))\nsns.scatterplot(data=df_select, x='longitude', y='latitude',\n                hue='jockey',\n                size='trakus_index')\nplt.grid()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:16:04.063971Z","iopub.execute_input":"2022-09-06T12:16:04.064429Z","iopub.status.idle":"2022-09-06T12:16:04.588055Z","shell.execute_reply.started":"2022-09-06T12:16:04.064391Z","shell.execute_reply":"2022-09-06T12:16:04.586884Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# zoom into finish\nplt.figure(figsize=(10,7))\nsns.scatterplot(data=df_select[df_select.trakus_index>300], x='longitude', y='latitude',\n                hue='jockey',\n                size='trakus_index')\nplt.grid()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:16:15.879280Z","iopub.execute_input":"2022-09-06T12:16:15.879713Z","iopub.status.idle":"2022-09-06T12:16:16.338507Z","shell.execute_reply.started":"2022-09-06T12:16:15.879680Z","shell.execute_reply":"2022-09-06T12:16:16.337419Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Select a race on another track (Belmont)","metadata":{}},{"cell_type":"code","source":"df_select = df[(df.track_id=='BEL') &\n               (df.race_date=='2019-09-14') &\n               (df.race_number==1)]\n# preview\ndf_select.head()","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:16:27.369925Z","iopub.execute_input":"2022-09-06T12:16:27.371020Z","iopub.status.idle":"2022-09-06T12:16:28.144403Z","shell.execute_reply.started":"2022-09-06T12:16:27.370978Z","shell.execute_reply":"2022-09-06T12:16:28.143254Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# jockey and horses\nctab = pd.crosstab(df_select.jockey, df_select.program_number) / df_select.trakus_index.nunique()\nctab.astype(int)","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:16:30.678470Z","iopub.execute_input":"2022-09-06T12:16:30.679448Z","iopub.status.idle":"2022-09-06T12:16:30.718226Z","shell.execute_reply.started":"2022-09-06T12:16:30.679373Z","shell.execute_reply":"2022-09-06T12:16:30.717102Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# features for each jockey\ndf_jock = df_select.groupby('jockey')[['weight_carried','odds']].mean()\ndf_jock['inv_odds'] = 1/df_jock.odds\n# we can derive probability of winning from odds \n# by normalizing inverse values:\nnn = df_jock.inv_odds.sum()\ndf_jock['win_prob_perc'] = np.round(100*df_jock.inv_odds / nn,2)\ndf_jock","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:16:31.843220Z","iopub.execute_input":"2022-09-06T12:16:31.844356Z","iopub.status.idle":"2022-09-06T12:16:31.869763Z","shell.execute_reply.started":"2022-09-06T12:16:31.844305Z","shell.execute_reply":"2022-09-06T12:16:31.868293Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# plot race\nplt.figure(figsize=(10,7))\nsns.scatterplot(data=df_select, x='longitude', y='latitude',\n                hue='jockey',\n                size='trakus_index')\nplt.grid()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:16:34.732927Z","iopub.execute_input":"2022-09-06T12:16:34.733329Z","iopub.status.idle":"2022-09-06T12:16:35.248958Z","shell.execute_reply.started":"2022-09-06T12:16:34.733293Z","shell.execute_reply":"2022-09-06T12:16:35.247478Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# zoom into finish\nplt.figure(figsize=(10,7))\nsns.scatterplot(data=df_select[df_select.trakus_index>200], x='longitude', y='latitude',\n                hue='jockey',\n                size='trakus_index')\nplt.grid()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:16:37.142689Z","iopub.execute_input":"2022-09-06T12:16:37.143105Z","iopub.status.idle":"2022-09-06T12:16:37.596489Z","shell.execute_reply.started":"2022-09-06T12:16:37.143068Z","shell.execute_reply":"2022-09-06T12:16:37.595277Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## And now a race on the third track (Saratoga)","metadata":{}},{"cell_type":"code","source":"df_select = df[(df.track_id=='SAR') &\n               (df.race_date=='2019-09-02') &\n               (df.race_number==1)]\n# preview\ndf_select.head()","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:16:39.837995Z","iopub.execute_input":"2022-09-06T12:16:39.838698Z","iopub.status.idle":"2022-09-06T12:16:40.572061Z","shell.execute_reply.started":"2022-09-06T12:16:39.838651Z","shell.execute_reply":"2022-09-06T12:16:40.570966Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# jockey and horses\nctab = pd.crosstab(df_select.jockey, df_select.program_number) / df_select.trakus_index.nunique()\nctab.astype(int)","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:16:42.269216Z","iopub.execute_input":"2022-09-06T12:16:42.269650Z","iopub.status.idle":"2022-09-06T12:16:42.298761Z","shell.execute_reply.started":"2022-09-06T12:16:42.269612Z","shell.execute_reply":"2022-09-06T12:16:42.297688Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# features for each jockey\ndf_jock = df_select.groupby('jockey')[['weight_carried','odds']].mean()\ndf_jock['inv_odds'] = 1/df_jock.odds\n# we can derive probability of winning from odds \n# by normalizing inverse values:\nnn = df_jock.inv_odds.sum()\ndf_jock['win_prob_perc'] = np.round(100*df_jock.inv_odds / nn,2)\ndf_jock","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:16:43.946900Z","iopub.execute_input":"2022-09-06T12:16:43.947334Z","iopub.status.idle":"2022-09-06T12:16:43.967451Z","shell.execute_reply.started":"2022-09-06T12:16:43.947297Z","shell.execute_reply":"2022-09-06T12:16:43.966590Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# plot race\nplt.figure(figsize=(10,7))\nsns.scatterplot(data=df_select, x='longitude', y='latitude',\n                hue='jockey',\n                size='trakus_index')\nplt.grid()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:16:49.859478Z","iopub.execute_input":"2022-09-06T12:16:49.859874Z","iopub.status.idle":"2022-09-06T12:16:50.431744Z","shell.execute_reply.started":"2022-09-06T12:16:49.859832Z","shell.execute_reply":"2022-09-06T12:16:50.430434Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# zoom into finish\nplt.figure(figsize=(10,7))\nsns.scatterplot(data=df_select[df_select.trakus_index>400], x='longitude', y='latitude',\n                hue='jockey',\n                size='trakus_index')\nplt.grid()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:16:52.952990Z","iopub.execute_input":"2022-09-06T12:16:52.953705Z","iopub.status.idle":"2022-09-06T12:16:53.389711Z","shell.execute_reply.started":"2022-09-06T12:16:52.953667Z","shell.execute_reply":"2022-09-06T12:16:53.388471Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<a id='5'></a>\n# Analyze horse dynamics in a race","metadata":{}},{"cell_type":"code","source":"# select race\ndf_select = df[(df.track_id=='SAR') &\n               (df.race_date=='2019-09-02') &\n               (df.race_number==1)]","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:16:58.018307Z","iopub.execute_input":"2022-09-06T12:16:58.018720Z","iopub.status.idle":"2022-09-06T12:16:58.736556Z","shell.execute_reply.started":"2022-09-06T12:16:58.018685Z","shell.execute_reply":"2022-09-06T12:16:58.735187Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# extract and prepare horse data\ndf_horse = df_select[df_select.program_number=='4']\n# sort by index\ndf_horse = df_horse.sort_values(by='trakus_index')\n# reset index\ndf_horse.reset_index(drop=True, inplace=True)\ndf_horse.head()","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:17:01.370699Z","iopub.execute_input":"2022-09-06T12:17:01.371100Z","iopub.status.idle":"2022-09-06T12:17:01.395145Z","shell.execute_reply.started":"2022-09-06T12:17:01.371067Z","shell.execute_reply":"2022-09-06T12:17:01.393923Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Evaluate movement","metadata":{}},{"cell_type":"code","source":"# calc distances between two time steps\nn_horse = df_horse.shape[0]\ndf_horse['dist_inc'] = 0\n\nfor i in range(0,n_horse-1):\n    x1 = df_horse.longitude.iloc[i]\n    y1 = df_horse.latitude.iloc[i]\n    x2 = df_horse.longitude.iloc[i+1]\n    y2 = df_horse.latitude.iloc[i+1]\n    d = GRC((x1,y1),(x2,y2)) # use great circle distance\n    df_horse.loc[i+1,'dist_inc'] = d.m # result in meters\n    \n# aggregate incremental distances\ndf_horse['dist'] = df_horse.dist_inc.cumsum()","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:17:07.178054Z","iopub.execute_input":"2022-09-06T12:17:07.178480Z","iopub.status.idle":"2022-09-06T12:17:07.391722Z","shell.execute_reply.started":"2022-09-06T12:17:07.178439Z","shell.execute_reply":"2022-09-06T12:17:07.390626Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# plot \"speed\"\nplt.figure(figsize=(12,4))\nplt.plot(df_horse.dist_inc)\nplt.grid()\nplt.title('Incremental Distance (meters) vs Index ~ Speed')\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:17:09.166170Z","iopub.execute_input":"2022-09-06T12:17:09.166607Z","iopub.status.idle":"2022-09-06T12:17:09.361981Z","shell.execute_reply.started":"2022-09-06T12:17:09.166570Z","shell.execute_reply":"2022-09-06T12:17:09.360579Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# proxy of acceleration as increment of \"speed\"\nplt.figure(figsize=(12,4))\nplt.plot(df_horse.dist_inc.diff())\nplt.grid()\nplt.title('Acceleration Proxy')\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:28:45.973983Z","iopub.execute_input":"2022-09-06T12:28:45.974409Z","iopub.status.idle":"2022-09-06T12:28:46.178194Z","shell.execute_reply.started":"2022-09-06T12:28:45.974361Z","shell.execute_reply":"2022-09-06T12:28:46.176971Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# plot aggregated distance\nplt.figure(figsize=(12,4))\nplt.plot(df_horse.dist)\nplt.grid()\nplt.title('Aggregated Distance (meters) vs Index')\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:29:16.328719Z","iopub.execute_input":"2022-09-06T12:29:16.329110Z","iopub.status.idle":"2022-09-06T12:29:16.511684Z","shell.execute_reply.started":"2022-09-06T12:29:16.329078Z","shell.execute_reply":"2022-09-06T12:29:16.510407Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# visualize position and speed using bubble plot\nplt.figure(figsize=(12,8))\nplt.scatter(df_horse.longitude, df_horse.latitude,\n            s = 100*df_horse.dist_inc, # visualize speed by bubble size\n            alpha=0.15, edgecolors='black')\nplt.grid()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:17:13.760397Z","iopub.execute_input":"2022-09-06T12:17:13.760815Z","iopub.status.idle":"2022-09-06T12:17:13.991519Z","shell.execute_reply.started":"2022-09-06T12:17:13.760778Z","shell.execute_reply":"2022-09-06T12:17:13.990331Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# ... same restricted to subset\nplt.figure(figsize=(12,8))\nplt.scatter(df_horse.longitude[0:100], df_horse.latitude[0:100],\n            s = 100*df_horse.dist_inc[0:100], # visualize speed by bubble size\n            alpha=0.25, edgecolors='black')\nplt.grid()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-09-06T12:30:15.134402Z","iopub.execute_input":"2022-09-06T12:30:15.135608Z","iopub.status.idle":"2022-09-06T12:30:15.404846Z","shell.execute_reply.started":"2022-09-06T12:30:15.135554Z","shell.execute_reply":"2022-09-06T12:30:15.403449Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# export for external analyses\ndf_horse.to_csv('horse_track_example.csv')","metadata":{},"execution_count":null,"outputs":[]}]}