{"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":"# Big Data Derby 2022 ","metadata":{}},{"cell_type":"markdown","source":"## Competition Overview","metadata":{}},{"cell_type":"markdown","source":"\"The goal of this competition is to analyze horse racing tactics, drafting strategies, and path efficiency. You will develop a model using never-before-released coordinate data along with basic race information.\n\nYour work will help racing horse owners, trainers, and veterinarians better understand how equine performance and welfare fit together. With better data analysis, equine welfare could significantly improve.\"","metadata":{}},{"cell_type":"markdown","source":"**We chose to**:","metadata":{}},{"cell_type":"markdown","source":"- Create a horse rating measuring expected finish position versus actual finish position. How does a horse’s expected finish position change through the running of a race? Does this metric rely solely on a horse’s own position or is it influenced by the position of competitors?","metadata":{}},{"cell_type":"markdown","source":"### Dataset Description\n\n#### File descriptions\n- **nyra_start_table.csv**- horse/jockey race data\n- **nyra_race_table.csv**- racetrack race data\n- **nyra_tracking_table.csv**- tracking data\n- **nyra_2019_complete.csv**- combined table of three above files\n\n#### Columns\n**nyra_start_table.csv**\n\n- `track_id` - 3 character id for the track the race took place at. AQU -Aqueduct, BEL - Belmont, SAR - Saratoga.\n- `race_date` - date the race took place. YYYY-MM-DD.\n- `race_number` - Number of the race. Passed as 3 characters but can be cast or converted to int for this data set.\n- `program_number` - Program number of the horse in the race passed as 3 characters. Should remain 3 characters as it isn't limited to just numbers. Is essentially the unique identifier of the horse in the race.\n- `weight_carried` - An integer of the weight carried by the horse in the race.\n- `jockey` - Name of the jockey on the horse in the race. 50 character max.\n- `odds` - Odds to win the race passed as an integer. Divide by 100 to derive the odds to 1. Example - 1280 would be 12.8-1.\n    Formula for profit = (our bet) * (first number)\n\n- `position_at_finish` - An integer of the horse's finishing position. (added to the dataset 9/8/22)\n\n**nyra_race_table.csv**\n\n- `track_id` - 3 character id for the track the race took place at. AQU -Aqueduct, BEL - Belmont, SAR - Saratoga.\n- `race_date` - date the race took place. YYYY-MM-DD.\n- `race_number` - Number of the race. Passed as 3 characters but can be cast or converted to int for this data set.\n- `distance_id` - Distance of the race in furlongs passed as an integer. Example - 600 would be 6 furlongs.\n- `course_type` - The course the race was run over passed as one character. M - Hurdle, D - Dirt, O - Outer turf, I - Inner turf, T - turf.\n- `track_condition` - The condition of the course the race was run on passed as three characters. YL - Yielding, FM - Firm, SY - Sloppy, GD - Good, FT - Fast, MY - Muddy, SF - Soft.\n- `run_up_distance` - Distance in feet of the gate to the start of the race passed as an integer.\n- `race_type` - The classification of the race passed as as five characters. STK - Stakes, WCL - Waiver Claiming, WMC - Waiver Maiden Claiming, SST - Starter Stakes, SHP - Starter Handicap, CLM - Claiming, STR - Starter Allowance, AOC - Allowance Optionl Claimer, SOC - Starter Optional Claimer, MCL - Maiden Claiming, ALW - Allowance, MSW - Maiden Special Weight.\n- `purse` - Purse in US dollars of the race passed as an money with two decimal places.\n- `post_time` - Time of day the race began passed as 5 character. Example - 01220 would be 12:20.\n\n**nyra_tracking_table.csv**\n\n- `track_id` - 3 character id for the track the race took place at. AQU -Aqueduct, BEL - Belmont, SAR - Saratoga.\n- `race_date` - date the race took place. YYYY-MM-DD.\n- `race_number` - Number of the race. Passed as 3 characters but can be cast or converted to int for this data set.\n- `program_number` - Program number of the horse in the race passed as 3 characters. Should remain 3 characters as it isn't limited to just numbers. Is essentially the unique identifier of the horse in the race.\n- `trakus_index` - The common collection of point of the lat / long of the horse in the race passed as an integer. From what we can tell, it's collected every 0.25 seconds.\n- `latitude` - The latitude of the horse in the race passed as a float.\n- `longitude` - The longitude of the horse in the race passed as a float.","metadata":{}},{"cell_type":"markdown","source":"# Exploratory Data Analysis","metadata":{}},{"cell_type":"code","source":"# # requirements\n# ! pip install haversine\n# ! pip install tqdm>=4.9.0","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:01:50.397081Z","iopub.execute_input":"2022-11-09T21:01:50.398022Z","iopub.status.idle":"2022-11-09T21:01:50.421399Z","shell.execute_reply.started":"2022-11-09T21:01:50.397912Z","shell.execute_reply":"2022-11-09T21:01:50.420268Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import pandas as pd\nimport numpy as np\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport datetime as dt\n\nfrom haversine import haversine_vector, Unit\n\narchive_path = \"../input/big-data-derby-2022/\"","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:02:20.313233Z","iopub.execute_input":"2022-11-09T21:02:20.313703Z","iopub.status.idle":"2022-11-09T21:02:20.320100Z","shell.execute_reply.started":"2022-11-09T21:02:20.313665Z","shell.execute_reply":"2022-11-09T21:02:20.318672Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Short overview of nyra_start_table.csv","metadata":{}},{"cell_type":"code","source":"nyra_start_table = pd.read_csv(archive_path + \"nyra_start_table.csv\", header=None)\n\nnyra_start_table.columns = [\"track_id\", \"race_date\", \"race_number\", \"program_number\",\n                            \"weight_carried\", \"jockey\", \"odds\", \"position_at_finish\"]\n\nnyra_start_table.index = pd.DatetimeIndex(nyra_start_table.race_date)\nnyra_start_table.index.name = \"date\"\nnyra_start_table = nyra_start_table.drop([\"race_date\"], axis = 1)\nnyra_start_table = nyra_start_table.sort_index()\nnyra_start_table.index = pd.DatetimeIndex(nyra_start_table.index)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:02:21.291037Z","iopub.execute_input":"2022-11-09T21:02:21.291464Z","iopub.status.idle":"2022-11-09T21:02:21.359647Z","shell.execute_reply.started":"2022-11-09T21:02:21.291421Z","shell.execute_reply":"2022-11-09T21:02:21.358569Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"participants_counter = nyra_start_table.groupby([\"track_id\"]).resample('D').count().cumsum()\nparticipants_counter = participants_counter.drop(\"track_id\", axis = 1)\nparticipants_counter = participants_counter.reset_index(level = 0)\nparticipants_counter = participants_counter[[\"track_id\",\"program_number\"]]\nparticipants_counter.columns = [\"track_id\", \"participants_count\"]\nparticipants_counter.index = pd.DatetimeIndex(participants_counter.index)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:02:22.882930Z","iopub.execute_input":"2022-11-09T21:02:22.883320Z","iopub.status.idle":"2022-11-09T21:02:22.921643Z","shell.execute_reply.started":"2022-11-09T21:02:22.883290Z","shell.execute_reply":"2022-11-09T21:02:22.920611Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sar_add = pd.DataFrame({\"date\": [dt.date(2019,1,1), dt.date(2019,7,10), dt.date(2019,12,31)],\n                        \"track_id\": [\"SAR\", \"SAR\", \"SAR\"],\n                        \"participants_count\": [0, 0, 14915]})\n\nsar_add.index = sar_add.date\nsar_add = sar_add.drop(\"date\", axis = 1)\n\nbel_add = pd.DataFrame({\"date\": [dt.date(2019,1,1),dt.date(2019,4,25), dt.date(2019,12,31)],\n                        \"track_id\": [\"BEL\", \"BEL\", \"BEL\"], \n                        \"participants_count\": [0, 0, 11730]})\n\nbel_add.index = bel_add.date\nbel_add = bel_add.drop(\"date\", axis = 1)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:03:17.319605Z","iopub.execute_input":"2022-11-09T21:03:17.320061Z","iopub.status.idle":"2022-11-09T21:03:17.333853Z","shell.execute_reply.started":"2022-11-09T21:03:17.320024Z","shell.execute_reply":"2022-11-09T21:03:17.332315Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"participants_counter = pd.concat([participants_counter, sar_add, bel_add])","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:03:19.061228Z","iopub.execute_input":"2022-11-09T21:03:19.061807Z","iopub.status.idle":"2022-11-09T21:03:19.076876Z","shell.execute_reply.started":"2022-11-09T21:03:19.061756Z","shell.execute_reply":"2022-11-09T21:03:19.074484Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize=(16,8))\nsns.set_theme()\nplot = sns.lineplot(data = participants_counter.reset_index(), x=\"date\", y=\"participants_count\", hue= \"track_id\" )\nplot.set_title(\"Cumsum of participants in races\", fontsize = 25)\nplot.set_xlabel(\"Date\", fontsize = 17)\nplot.set_ylabel(\"Cumsum of participants in races\", fontsize = 17)\nplot.legend(title = \"Track\");","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:03:20.475414Z","iopub.execute_input":"2022-11-09T21:03:20.475856Z","iopub.status.idle":"2022-11-09T21:03:21.143291Z","shell.execute_reply.started":"2022-11-09T21:03:20.475821Z","shell.execute_reply":"2022-11-09T21:03:21.141143Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_start_table.groupby(\"track_id\").resample('D')[[\"race_number\"]].max().groupby(\"track_id\").sum()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:04:17.770104Z","iopub.execute_input":"2022-11-09T21:04:17.770704Z","iopub.status.idle":"2022-11-09T21:04:17.818791Z","shell.execute_reply.started":"2022-11-09T21:04:17.770654Z","shell.execute_reply":"2022-11-09T21:04:17.815135Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"mean_number_participants = nyra_start_table.groupby([\"track_id\", \"race_number\"]).resample('D')[[\"program_number\"]].nunique()\nmean_number_participants = mean_number_participants.loc[mean_number_participants.program_number != 0]\nmean_number_participants.groupby(\"track_id\").max()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:04:18.159660Z","iopub.execute_input":"2022-11-09T21:04:18.160107Z","iopub.status.idle":"2022-11-09T21:04:18.279286Z","shell.execute_reply.started":"2022-11-09T21:04:18.160069Z","shell.execute_reply":"2022-11-09T21:04:18.278110Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"How we can see the track the race took places depends on season and there were the most participants during summer competitions at Aqueduct track. The AQU and BEL tracks are bigger than SAR one as the max number of jockey at track is more by two. ","metadata":{}},{"cell_type":"code","source":"plt.figure(figsize=(16,8))\nplot = sns.boxplot(data = nyra_start_table, x = \"track_id\", y = \"weight_carried\")\nplot.set_title(\"Boxplot of carried weight by tack_id\", fontsize=14)\nplot.set_xlabel(\"Date\", fontsize = 17)\nplot.set_ylabel(\"Carried weight by horse\");","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:04:18.896799Z","iopub.execute_input":"2022-11-09T21:04:18.897738Z","iopub.status.idle":"2022-11-09T21:04:19.225387Z","shell.execute_reply.started":"2022-11-09T21:04:18.897679Z","shell.execute_reply":"2022-11-09T21:04:19.224126Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_start_table.groupby(\"track_id\").weight_carried.describe()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:04:19.227459Z","iopub.execute_input":"2022-11-09T21:04:19.227888Z","iopub.status.idle":"2022-11-09T21:04:19.261640Z","shell.execute_reply.started":"2022-11-09T21:04:19.227851Z","shell.execute_reply":"2022-11-09T21:04:19.259899Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_start_table.groupby([nyra_start_table.track_id, pd.cut(nyra_start_table.weight_carried, [100, 119, 120, 123, 150, 160])]).weight_carried.agg([\"count\", \"mean\"])","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:04:19.263642Z","iopub.execute_input":"2022-11-09T21:04:19.264050Z","iopub.status.idle":"2022-11-09T21:04:19.308313Z","shell.execute_reply.started":"2022-11-09T21:04:19.264004Z","shell.execute_reply":"2022-11-09T21:04:19.307038Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"How we can see 25 times horses at SAR carried weight over 150","metadata":{}},{"cell_type":"code","source":"nyra_start_table.loc[(nyra_start_table.weight_carried > 150)].groupby([ \"track_id\", \"jockey\"]).track_id.agg([\"count\"])","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:04:19.548220Z","iopub.execute_input":"2022-11-09T21:04:19.548682Z","iopub.status.idle":"2022-11-09T21:04:19.566021Z","shell.execute_reply.started":"2022-11-09T21:04:19.548641Z","shell.execute_reply":"2022-11-09T21:04:19.564427Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Carried jackeys for these times repeats.","metadata":{}},{"cell_type":"code","source":"weight_carred_describe = nyra_start_table.groupby([\"date\", \"race_number\"]).weight_carried.describe()\nweight_carred_describe.nlargest(20, columns = \"mean\")","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:04:20.348159Z","iopub.execute_input":"2022-11-09T21:04:20.348765Z","iopub.status.idle":"2022-11-09T21:04:26.828105Z","shell.execute_reply.started":"2022-11-09T21:04:20.348720Z","shell.execute_reply":"2022-11-09T21:04:26.826745Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"How we could expect there were several races that minimum carried weight was over 142. We will take that into consideration during building models. What is more we see that these races were always first in particular day. ","metadata":{}},{"cell_type":"code","source":"nyra_start_table.corr(\"pearson\")[\"position_at_finish\"].sort_values()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:04:26.830355Z","iopub.execute_input":"2022-11-09T21:04:26.830760Z","iopub.status.idle":"2022-11-09T21:04:26.844549Z","shell.execute_reply.started":"2022-11-09T21:04:26.830724Z","shell.execute_reply":"2022-11-09T21:04:26.842960Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_start_table.corr(\"spearman\")[\"position_at_finish\"].sort_values()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:04:26.846230Z","iopub.execute_input":"2022-11-09T21:04:26.846778Z","iopub.status.idle":"2022-11-09T21:04:26.869217Z","shell.execute_reply.started":"2022-11-09T21:04:26.846707Z","shell.execute_reply":"2022-11-09T21:04:26.867910Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"There is a small negative correlation between carried weight by horse and `its position_at_finish`.","metadata":{}},{"cell_type":"code","source":"nyra_start_table.loc[nyra_start_table.weight_carried < 132].corr()[\"position_at_finish\"]","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:04:26.873356Z","iopub.execute_input":"2022-11-09T21:04:26.873913Z","iopub.status.idle":"2022-11-09T21:04:26.891987Z","shell.execute_reply.started":"2022-11-09T21:04:26.873859Z","shell.execute_reply":"2022-11-09T21:04:26.890584Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_start_table.loc[nyra_start_table.weight_carried > 132].corr()[\"position_at_finish\"]","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:04:26.895781Z","iopub.execute_input":"2022-11-09T21:04:26.896452Z","iopub.status.idle":"2022-11-09T21:04:26.911564Z","shell.execute_reply.started":"2022-11-09T21:04:26.896401Z","shell.execute_reply":"2022-11-09T21:04:26.909091Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"What we can see `odds` are not so correlated with `position_at_finish` for hourses that carried weight over 132, but the reason maybe just small number of that records.  ","metadata":{}},{"cell_type":"code","source":"nyra_start_table.groupby(\"jockey\").race_number.count().describe()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:04:26.913590Z","iopub.execute_input":"2022-11-09T21:04:26.914139Z","iopub.status.idle":"2022-11-09T21:04:26.934644Z","shell.execute_reply.started":"2022-11-09T21:04:26.914089Z","shell.execute_reply":"2022-11-09T21:04:26.932815Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"There are 178 jockeys and most of them raced less than 17 times (If we don't consider coincidence of surnames)","metadata":{}},{"cell_type":"code","source":"nyra_start_table[\"odds\"] = nyra_start_table[\"odds\"] / 100","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:04:26.936846Z","iopub.execute_input":"2022-11-09T21:04:26.937516Z","iopub.status.idle":"2022-11-09T21:04:26.949402Z","shell.execute_reply.started":"2022-11-09T21:04:26.937447Z","shell.execute_reply":"2022-11-09T21:04:26.946909Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_start_table.groupby(pd.qcut(nyra_start_table.odds, 10)).position_at_finish.agg([\"sum\", \"mean\"])","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:04:26.953667Z","iopub.execute_input":"2022-11-09T21:04:26.954316Z","iopub.status.idle":"2022-11-09T21:04:26.992816Z","shell.execute_reply.started":"2022-11-09T21:04:26.954267Z","shell.execute_reply":"2022-11-09T21:04:26.991431Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"So people betting racing have some clue about it as horses with the smalles `odds` win most often.","metadata":{}},{"cell_type":"code","source":"plt.figure(figsize = (16,8))\nplot = sns.heatmap(nyra_start_table.corr(), vmin=-1, vmax=1, annot=True, cmap='seismic')\nplot.set_title(\"Pearson correaltion\", fontsize = 25)\nplot.set_xlabel(\"Date\", fontsize = 17)\nplot.set_ylabel(\"Carried weight by horse\", fontsize = 17);\nplt.xticks(fontsize = 14);\nplt.yticks(fontsize = 14);","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:04:26.995304Z","iopub.execute_input":"2022-11-09T21:04:26.995745Z","iopub.status.idle":"2022-11-09T21:04:27.464272Z","shell.execute_reply.started":"2022-11-09T21:04:26.995707Z","shell.execute_reply":"2022-11-09T21:04:27.462949Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_start_table","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:04:27.471006Z","iopub.execute_input":"2022-11-09T21:04:27.472060Z","iopub.status.idle":"2022-11-09T21:04:27.503455Z","shell.execute_reply.started":"2022-11-09T21:04:27.471991Z","shell.execute_reply":"2022-11-09T21:04:27.501093Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_start_table.groupby(\"race_number\").resample('D').count()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:04:27.506366Z","iopub.execute_input":"2022-11-09T21:04:27.506952Z","iopub.status.idle":"2022-11-09T21:04:27.576340Z","shell.execute_reply.started":"2022-11-09T21:04:27.506898Z","shell.execute_reply":"2022-11-09T21:04:27.574222Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_start_table_counter = nyra_start_table.groupby(\"race_number\").track_id.resample('D').agg([\"count\"])\nnyra_start_table_counter = nyra_start_table_counter.loc[nyra_start_table_counter[\"count\"] != 0]\nnyra_start_table_counter.groupby(\"race_number\").describe()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:04:27.578755Z","iopub.execute_input":"2022-11-09T21:04:27.579647Z","iopub.status.idle":"2022-11-09T21:04:27.855875Z","shell.execute_reply.started":"2022-11-09T21:04:27.579587Z","shell.execute_reply":"2022-11-09T21:04:27.854702Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The minimum number of participants in race was 3 and maximum 14.","metadata":{}},{"cell_type":"markdown","source":"## Short overview of nyra_race_table.csv","metadata":{}},{"cell_type":"code","source":"nyra_race_table = pd.read_csv(archive_path + \"nyra_race_table.csv\")\nnyra_race_table.index = nyra_race_table.race_date\nnyra_race_table = nyra_race_table.drop(\"race_date\", axis = 1)\nnyra_race_table[\"start_hour\"] = np.where(nyra_race_table[\"post_time\"].astype(str).str.len() == 4, nyra_race_table[\"post_time\"].astype(str).str[0:2], \"0\" + nyra_race_table[\"post_time\"].astype(str).str[0]) + \":\" + nyra_race_table[\"post_time\"].astype(str).str[-2:]\nnyra_race_table.index = nyra_race_table.index + \" \" + nyra_race_table.start_hour\nnyra_race_table.index = pd.DatetimeIndex(nyra_race_table.index)\n\nnyra_race_table[\"distance_meters\"] = 201.168 * nyra_race_table[\"distance_id\"]  # furlong to meters] ","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:04:27.857510Z","iopub.execute_input":"2022-11-09T21:04:27.857959Z","iopub.status.idle":"2022-11-09T21:04:27.899396Z","shell.execute_reply.started":"2022-11-09T21:04:27.857909Z","shell.execute_reply":"2022-11-09T21:04:27.898164Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"hour_races_count = nyra_race_table.groupby([\"track_id\",nyra_race_table.index.hour])[\"race_number\"].count().reset_index()\nhour_races_count.columns = [\"track_id\", \"hour\", \"race_count\"]\n\nplt.figure(figsize=(16,8))\nsns.barplot(data = hour_races_count, x=\"hour\", y=\"race_count\", hue=\"track_id\")\nplt.xlabel(\"Hour\")\nplt.ylabel(\"Race count\")\nplt.title(\"The number of races versus the time of day\", fontsize=14)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:04:47.044143Z","iopub.execute_input":"2022-11-09T21:04:47.044722Z","iopub.status.idle":"2022-11-09T21:04:47.508641Z","shell.execute_reply.started":"2022-11-09T21:04:47.044582Z","shell.execute_reply":"2022-11-09T21:04:47.507311Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"How we can see races took places between 1 AM and 12 AM and there is almost now races between 7 AM and 11 AM.\nRaces at Aqueduct(winter) usually takes places between 1AM and 3AM and at 12AM.\nRaces at Saratoga(summer) usually takes places between 4AM and 5AM.","metadata":{}},{"cell_type":"markdown","source":"# Nyra_2019_complete.csv","metadata":{}},{"cell_type":"markdown","source":"**nyra_2019_complete.csv**\n- `track_id` - 3 character id for the track the race took place at. AQU -Aqueduct, BEL - Belmont, SAR - Saratoga.\n- `race_date` - date the race took place. YYYY-MM-DD.\n- `race_number` - Number of the race. Passed as 3 characters but can be cast or converted to int for this data set.\n- `program_number` - Program number of the horse in the race passed as 3 characters. Should remain 3 characters as it isn't limited to just numbers. Is essentially the unique identifier of the horse in the race.\n- `trakus_index` - The common collection of point of the lat / long of the horse in the race passed as an integer. From what we can tell, it's collected every 0.25 seconds.\n- `latitude` - The latitude of the horse in the race passed as a float.\n- `longitude` - The longitude of the horse in the race passed as a float.\n- `distance_id` - Distance of the race in furlongs passed as an integer. Example - 600 would be 6 furlongs.\n- `course_type` - The course the race was run over passed as one character. M - Hurdle, D - Dirt, O - Outer turf, I - Inner turf, T - turf.\n- `track_condition` - The condition of the course the race was run on passed as three characters. YL - Yielding, FM - Firm, SY - Sloppy, GD - Good, FT - Fast, MY - Muddy, SF - Soft.\n- `run_up_distance` - Distance in feet of the gate to the start of the race passed as an integer.\n- `race_type` - The classification of the race passed as as five characters. STK - Stakes, WCL - Waiver Claiming, WMC - Waiver Maiden Claiming, SST - Starter Stakes, SHP - Starter Handicap, CLM - Claiming, STR - Starter Allowance, AOC - Allowance Optionl Claimer, SOC - Starter Optional Claimer, MCL - Maiden Claiming, ALW - Allowance, MSW - Maiden Special Weight.\n- `purse` - Purse in US dollars of the race passed as an money with two decimal places.\n- `post_time` - Time of day the race began passed as 5 character. Example - 01220 would be 12:20.\n- `weight_carried` - An integer of the weight carried by the horse in the race.\n- `jockey` - Name of the jockey on the horse in the race. 50 character max.\n- `odds` - Odds to win the race passed as an integer. Divide by 100 to derive the odds to 1. Example - 1280 would be 12.8-1.\n- `position_at_finish` - An integer of the horse's finishing position. (added to the dataset 9/8/22)\n\n**Important!**\n- `track_id` + `race_date` + `race_number` = unique race\n- `track_id` + `race_date` + `race_number` + `program_number` = unique horse\n- `track_id` + `race_date` + `race_number` + `program_number` + `trakus_index` = unique horse position \n\nIn order to get long/lat in correct order, you have to sort (ascending) by `trakus_index`. ","metadata":{}},{"cell_type":"code","source":"dtype = {\n        \"track_id\": str,\n        \"race_date\": str,\n        \"race_number\": int,\n        \"program_number\": str,\n        \"trakus_index\": int,\n        \"latitude\": float,\n        \"longitude\": float,\n        \"distance_id\": int,\n        \"course_type\": str,\n        \"track_condition\": str,\n        \"run_up_distance\": int,\n        \"race_type\": str,\n        \"purse\": int,\n        \"post_time\": str,\n        \"weight_carried\": int,\n        \"jockey\": str,\n        \"odds\": int,\n        \"position_at_finish\": int\n    }\n\nnyra_complete = pd.read_csv(\n    archive_path + \"nyra_2019_complete.csv\", \n    header=None, \n    names=dtype.keys(),\n    dtype=dtype\n)\n\nnyra_complete = nyra_complete.sort_values(by=[\"track_id\", \"race_date\", \"race_number\", \"program_number\", \"trakus_index\"])\n\nnyra_complete[\"distance_meters\"] = nyra_complete[\"distance_id\"] * 201.168 / 100 # furlong to meters\nnyra_complete.head(5)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:04:48.474614Z","iopub.execute_input":"2022-11-09T21:04:48.475489Z","iopub.status.idle":"2022-11-09T21:05:15.049759Z","shell.execute_reply.started":"2022-11-09T21:04:48.475437Z","shell.execute_reply":"2022-11-09T21:05:15.047970Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Race route example\n(without longitude/latitude aspect)","metadata":{}},{"cell_type":"code","source":"def plot_race(race_data, title=\"\", ax=None):\n    ax = plt.gca() if ax is None else ax\n    ax.plot(race_data[\"longitude\"], race_data[\"latitude\"])\n    ax.set_title(title, fontsize=14)\n    ax.ticklabel_format(useOffset=False)\n\nAQU_race_example = nyra_complete.loc[\n    nyra_complete[\"track_id\"].eq(\"AQU\") & \n    nyra_complete[\"race_date\"].eq(\"2019-01-01\") &\n    nyra_complete[\"race_number\"].eq(1) & \n    nyra_complete[\"program_number\"].eq(\"1  \")\n    , :].sort_values(by=\"trakus_index\")\n\nBEL_race_example = nyra_complete.loc[\n    nyra_complete[\"track_id\"].eq(\"BEL\") & \n    nyra_complete[\"race_date\"].eq(\"2019-04-26\") &\n    nyra_complete[\"race_number\"].eq(1) & \n    nyra_complete[\"program_number\"].eq(\"1  \")\n    , :].sort_values(by=\"trakus_index\")\n\nSAR_race_example = nyra_complete.loc[\n    nyra_complete[\"track_id\"].eq(\"SAR\") & \n    nyra_complete[\"race_date\"].eq(\"2019-07-11\") &\n    nyra_complete[\"race_number\"].eq(1) & \n    nyra_complete[\"program_number\"].eq(\"1  \")\n    , :].sort_values(by=\"trakus_index\")\n\nfig, axs = plt.subplots(1, 3, figsize=(15, 4))\nplot_race(AQU_race_example, \"AQU race example\", axs[0])\nplot_race(BEL_race_example, \"BEL race example\", axs[1])\nplot_race(SAR_race_example, \"SAR race example\", axs[2])\nplt.subplots_adjust(wspace=0.3)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:05:15.053466Z","iopub.execute_input":"2022-11-09T21:05:15.054419Z","iopub.status.idle":"2022-11-09T21:05:19.045277Z","shell.execute_reply.started":"2022-11-09T21:05:15.054362Z","shell.execute_reply":"2022-11-09T21:05:19.043709Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"AQU_race_example = nyra_complete.loc[\n    nyra_complete[\"track_id\"].eq(\"AQU\") & \n    nyra_complete[\"race_date\"].eq(\"2019-01-01\") &\n    nyra_complete[\"race_number\"].eq(1), :].sort_values(by=[\"program_number\", \"trakus_index\"])\n\nplt.figure(figsize=(10, 10))\nsns.lineplot(data=AQU_race_example, x=\"longitude\", y=\"latitude\", hue=\"program_number\", sort=False, lw=1)\nplt.ticklabel_format(useOffset=False)\nplt.title(\"AQU race example - all participants\", fontsize=14)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:05:19.047391Z","iopub.execute_input":"2022-11-09T21:05:19.048342Z","iopub.status.idle":"2022-11-09T21:05:20.251048Z","shell.execute_reply.started":"2022-11-09T21:05:19.048285Z","shell.execute_reply":"2022-11-09T21:05:20.249560Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Computing every-0.25-second (trakus) distance\n* `trakus_distance` - haversine distance between actual and preceding position\n* `trakus_distance_cumsum` -  `trakus_distance` cumulative sum","metadata":{}},{"cell_type":"code","source":"from tqdm.auto import tqdm\ntqdm.pandas()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:05:20.253991Z","iopub.execute_input":"2022-11-09T21:05:20.254382Z","iopub.status.idle":"2022-11-09T21:05:20.270082Z","shell.execute_reply.started":"2022-11-09T21:05:20.254347Z","shell.execute_reply":"2022-11-09T21:05:20.267771Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# may take a while\n\ndef compute_distance_between_records(x: pd.DataFrame):\n    a = x[[\"latitude\", \"longitude\"]]\n    b = x[[\"latitude\", \"longitude\"]].shift(1).bfill()\n    x[\"trakus_distance\"] = haversine_vector(a, b, Unit.METERS)\n    x[\"trakus_distance_cumsum\"] = x[\"trakus_distance\"].cumsum()\n    # x[\"distance_perct\"] = x[\"trakus_distance_cumsum\"] / x[\"distance_meters\"]\n    return x\n\nnyra_complete = nyra_complete.groupby([\"track_id\", \"race_date\", \"race_number\", \"program_number\"]).progress_apply(compute_distance_between_records)\nnyra_complete[\"trakus_distance_perct\"] = nyra_complete[\"trakus_distance_cumsum\"] / nyra_complete[\"distance_meters\"]","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:05:20.271460Z","iopub.execute_input":"2022-11-09T21:05:20.272048Z","iopub.status.idle":"2022-11-09T21:06:56.686064Z","shell.execute_reply.started":"2022-11-09T21:05:20.272004Z","shell.execute_reply":"2022-11-09T21:06:56.684984Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_complete[[\"trakus_index\", \"trakus_distance\", \"trakus_distance_cumsum\", \"trakus_distance_perct\"]].head(5)","metadata":{"scrolled":true,"execution":{"iopub.status.busy":"2022-11-09T21:06:56.687488Z","iopub.execute_input":"2022-11-09T21:06:56.687854Z","iopub.status.idle":"2022-11-09T21:06:56.929556Z","shell.execute_reply.started":"2022-11-09T21:06:56.687822Z","shell.execute_reply":"2022-11-09T21:06:56.928183Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_complete[[\"distance_meters\", \"trakus_index\", \"trakus_distance\", \"trakus_distance_cumsum\", \"trakus_distance_perct\"]].tail(5)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:06:56.931138Z","iopub.execute_input":"2022-11-09T21:06:56.931653Z","iopub.status.idle":"2022-11-09T21:06:57.014433Z","shell.execute_reply.started":"2022-11-09T21:06:56.931609Z","shell.execute_reply":"2022-11-09T21:06:57.012952Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"It seems that computed cumulative distance is usually grater than `distance_meters` - probably because horse's position is captured for some time after the end of the race.","metadata":{}},{"cell_type":"markdown","source":"### Speed during race - example","metadata":{}},{"cell_type":"code","source":"AQU_race_example = nyra_complete.loc[\n    nyra_complete[\"track_id\"].eq(\"AQU\") & \n    nyra_complete[\"race_date\"].eq(\"2019-01-01\") &\n    nyra_complete[\"race_number\"].eq(1), :].sort_values(by=[\"program_number\", \"trakus_index\"])\n\nplt.figure(figsize=(16, 5))\nsns.lineplot(data=AQU_race_example, x=\"trakus_distance_perct\", y=\"trakus_distance\", hue=\"program_number\", sort=False, lw=1)\nplt.title(\"AQU example - speed during race - all participants\", fontsize=14)\nplt.xlabel(\"% of total distance\")\nplt.ylabel(\"meters per 0.25 second\")\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:06:57.016437Z","iopub.execute_input":"2022-11-09T21:06:57.016852Z","iopub.status.idle":"2022-11-09T21:06:58.183331Z","shell.execute_reply.started":"2022-11-09T21:06:57.016816Z","shell.execute_reply":"2022-11-09T21:06:58.182329Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Every-0.25-second-position, based on cumulative distance","metadata":{}},{"cell_type":"code","source":"def compute_distance_position(x: pd.DataFrame):\n    x[\"distance_position\"] = x[\"trakus_distance_cumsum\"].rank(method=\"first\", ascending=False)\n    x.loc[x[\"trakus_distance_cumsum\"] > x[\"distance_meters\"] + 100, \"distance_position\"] = np.NAN \n    return x[\"distance_position\"] \n \ndistance_position = nyra_complete.groupby([\"track_id\", \"race_date\", \"race_number\", \"trakus_index\"]).progress_apply(compute_distance_position)\nnyra_complete = nyra_complete.join(distance_position.droplevel([0, 1, 2, 3]))\n\nnyra_complete[\"distance_position\"] = nyra_complete[\"distance_position\"].ffill()\nnyra_complete[\"distance_position\"] = nyra_complete[\"distance_position\"].astype(int)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:06:58.185237Z","iopub.execute_input":"2022-11-09T21:06:58.185673Z","iopub.status.idle":"2022-11-09T21:28:22.133559Z","shell.execute_reply.started":"2022-11-09T21:06:58.185635Z","shell.execute_reply":"2022-11-09T21:28:22.131765Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_complete[[\"trakus_distance\", \"trakus_distance_cumsum\", \"trakus_distance_perct\", \"distance_position\", \"position_at_finish\"]].tail(5)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:28:22.138945Z","iopub.execute_input":"2022-11-09T21:28:22.140135Z","iopub.status.idle":"2022-11-09T21:28:24.325684Z","shell.execute_reply.started":"2022-11-09T21:28:22.140084Z","shell.execute_reply":"2022-11-09T21:28:24.323710Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Is last computed position correct? (equal to `position_at_finish` feature?)","metadata":{}},{"cell_type":"code","source":"def check_position_at_finish(x: pd.DataFrame):\n    last_position = x[\"distance_position\"].iloc[-1]\n    position_at_finish = x[\"position_at_finish\"].iloc[-1]\n    return pd.Series({\n        \"last_position\": last_position,\n        \"position_at_finish\": position_at_finish,\n        \"postion_difference\": last_position - position_at_finish,\n        \"is_distance_position_correct\": last_position == position_at_finish\n    })\n \nposition_check = nyra_complete.groupby([\"track_id\", \"race_date\", \"race_number\", \"program_number\"]).progress_apply(check_position_at_finish)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:28:24.327316Z","iopub.execute_input":"2022-11-09T21:28:24.327826Z","iopub.status.idle":"2022-11-09T21:28:35.454684Z","shell.execute_reply.started":"2022-11-09T21:28:24.327785Z","shell.execute_reply":"2022-11-09T21:28:35.453321Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# how often computed position equals to `position_at_finish`\nposition_check[\"is_distance_position_correct\"].value_counts()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:28:35.456170Z","iopub.execute_input":"2022-11-09T21:28:35.456648Z","iopub.status.idle":"2022-11-09T21:28:35.468165Z","shell.execute_reply.started":"2022-11-09T21:28:35.456584Z","shell.execute_reply":"2022-11-09T21:28:35.466541Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# how much computed position differs from position_at_finish\nposition_check[\"postion_difference\"].value_counts().sort_index()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:28:35.471079Z","iopub.execute_input":"2022-11-09T21:28:35.471661Z","iopub.status.idle":"2022-11-09T21:28:35.484769Z","shell.execute_reply.started":"2022-11-09T21:28:35.471611Z","shell.execute_reply":"2022-11-09T21:28:35.483138Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### How the position changes during the race?","metadata":{}},{"cell_type":"markdown","source":"#### AQU example","metadata":{}},{"cell_type":"code","source":"from matplotlib.ticker import MaxNLocator\n\nAQU_race_example = nyra_complete.loc[\n    nyra_complete[\"track_id\"].eq(\"AQU\") & \n    nyra_complete[\"race_date\"].eq(\"2019-01-01\") &\n    nyra_complete[\"race_number\"].eq(1), :].sort_values(by=[\"program_number\", \"trakus_index\"])\n\nfig = plt.figure(figsize=(16, 5))\nsns.lineplot(data=AQU_race_example, x=\"trakus_distance_perct\", y=\"distance_position\", hue=\"program_number\")\nplt.title(\"AQU example - position during race - all participants\", fontsize=14)\nplt.xlabel(\"% of total distance\")\nplt.ylabel(\"Position based on distance\")\nfig.gca().yaxis.set_major_locator(MaxNLocator(integer=True))\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:28:35.487202Z","iopub.execute_input":"2022-11-09T21:28:35.487847Z","iopub.status.idle":"2022-11-09T21:28:36.692222Z","shell.execute_reply.started":"2022-11-09T21:28:35.487771Z","shell.execute_reply":"2022-11-09T21:28:36.690547Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Number of unique positions - statistics","metadata":{}},{"cell_type":"code","source":"# number of horses\ndef get_number_of_horses_in_race(x: pd.DataFrame):\n    x[\"number_of_horses\"] = x[\"program_number\"].nunique()\n    return x\n\nnyra_complete = nyra_complete.groupby([\"track_id\", \"race_date\", \"race_number\"]).progress_apply(get_number_of_horses_in_race)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:28:36.694016Z","iopub.execute_input":"2022-11-09T21:28:36.694394Z","iopub.status.idle":"2022-11-09T21:28:55.374023Z","shell.execute_reply.started":"2022-11-09T21:28:36.694360Z","shell.execute_reply":"2022-11-09T21:28:55.372855Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def count_unique_position(x: pd.DataFrame):\n    upc = x[\"distance_position\"].nunique()\n    number_of_horses = x[\"number_of_horses\"].iloc[0]\n    return pd.Series({\n        \"unique_position_count\": upc,\n        \"unique_position_count_ratio\": upc / number_of_horses,\n        \"number_of_position_changes\": (x[\"distance_position\"] != x[\"distance_position\"].shift(1)).sum()\n    })\n \nunique_position_count = nyra_complete.groupby([\"track_id\", \"race_date\", \"race_number\", \"program_number\"]).progress_apply(count_unique_position)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:28:55.375785Z","iopub.execute_input":"2022-11-09T21:28:55.376243Z","iopub.status.idle":"2022-11-09T21:29:13.404932Z","shell.execute_reply.started":"2022-11-09T21:28:55.376195Z","shell.execute_reply":"2022-11-09T21:29:13.403489Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"unique_position_count[\"unique_position_count\"].describe().round(2)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:29:13.406564Z","iopub.execute_input":"2022-11-09T21:29:13.406962Z","iopub.status.idle":"2022-11-09T21:29:13.423279Z","shell.execute_reply.started":"2022-11-09T21:29:13.406927Z","shell.execute_reply":"2022-11-09T21:29:13.421311Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# unique_position_count_ratio - unique_position_count divided by number of race participants \nunique_position_count[\"unique_position_count_ratio\"].describe().round(2)","metadata":{"scrolled":true,"execution":{"iopub.status.busy":"2022-11-09T21:29:13.424945Z","iopub.execute_input":"2022-11-09T21:29:13.425377Z","iopub.status.idle":"2022-11-09T21:29:13.439759Z","shell.execute_reply.started":"2022-11-09T21:29:13.425340Z","shell.execute_reply":"2022-11-09T21:29:13.438496Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"unique_position_count[\"number_of_position_changes\"].describe().round(2)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:29:13.441089Z","iopub.execute_input":"2022-11-09T21:29:13.441453Z","iopub.status.idle":"2022-11-09T21:29:13.454291Z","shell.execute_reply.started":"2022-11-09T21:29:13.441421Z","shell.execute_reply":"2022-11-09T21:29:13.453401Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Referring to these statistics, every horse changes his position approximately 16 times, takes 6 unique positions, what covers 75% of total number of unique positions. These numbers may decrease, if we exclude first seconds of race.","metadata":{}},{"cell_type":"markdown","source":"#### Number of unique positions - statistics exlcluding first part (10%) of race distance","metadata":{}},{"cell_type":"code","source":"def count_unique_position(x: pd.DataFrame):\n    upc = x[\"distance_position\"].nunique()\n    number_of_horses = x[\"number_of_horses\"].iloc[0]\n    return pd.Series({\n        \"unique_position_count\": upc,\n        \"unique_position_count_ratio\": upc / number_of_horses,\n        \"number_of_position_changes\": (x[\"distance_position\"] != x[\"distance_position\"].shift(1)).sum()\n    })\n \nunique_position_count_cutted = nyra_complete.loc[nyra_complete[\"trakus_distance_perct\"] > 0.1].groupby([\"track_id\", \"race_date\", \"race_number\", \"program_number\"]).progress_apply(count_unique_position)","metadata":{"scrolled":true,"execution":{"iopub.status.busy":"2022-11-09T21:29:13.456293Z","iopub.execute_input":"2022-11-09T21:29:13.456756Z","iopub.status.idle":"2022-11-09T21:29:32.962957Z","shell.execute_reply.started":"2022-11-09T21:29:13.456718Z","shell.execute_reply":"2022-11-09T21:29:32.961738Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"unique_position_count_cutted[\"unique_position_count_ratio\"].describe().round(2)","metadata":{"scrolled":true,"execution":{"iopub.status.busy":"2022-11-09T21:29:32.964741Z","iopub.execute_input":"2022-11-09T21:29:32.965106Z","iopub.status.idle":"2022-11-09T21:29:32.977468Z","shell.execute_reply.started":"2022-11-09T21:29:32.965073Z","shell.execute_reply":"2022-11-09T21:29:32.976293Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"unique_position_count_cutted[\"number_of_position_changes\"].describe().round(2)","metadata":{"scrolled":true,"execution":{"iopub.status.busy":"2022-11-09T21:29:32.979000Z","iopub.execute_input":"2022-11-09T21:29:32.979339Z","iopub.status.idle":"2022-11-09T21:29:32.997107Z","shell.execute_reply.started":"2022-11-09T21:29:32.979308Z","shell.execute_reply":"2022-11-09T21:29:32.994679Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Conclusion** - position changes, but mostly during first phase of the race.","metadata":{}},{"cell_type":"markdown","source":"## Did every horse in race has the same number of position mesurements","metadata":{}},{"cell_type":"code","source":"distance = nyra_complete.groupby(['track_id', 'race_date', 'race_number', 'program_number']).agg({\n    'trakus_distance_cumsum':'max', 'distance_meters':'max'})","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:29:32.998856Z","iopub.execute_input":"2022-11-09T21:29:33.000084Z","iopub.status.idle":"2022-11-09T21:29:34.590866Z","shell.execute_reply.started":"2022-11-09T21:29:33.000043Z","shell.execute_reply":"2022-11-09T21:29:34.589665Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tracking = pd.read_csv(archive_path + 'nyra_tracking_table.csv')\n\nmax_index = tracking.groupby(['track_id','race_date','race_number','program_number']).agg({\n    'trakus_index':'max'\n}).reset_index()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:29:34.592525Z","iopub.execute_input":"2022-11-09T21:29:34.592965Z","iopub.status.idle":"2022-11-09T21:29:44.778500Z","shell.execute_reply.started":"2022-11-09T21:29:34.592931Z","shell.execute_reply":"2022-11-09T21:29:44.777140Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tmp = max_index.groupby(['track_id','race_date','race_number']).agg({\n    'trakus_index':['max','min']\n})\ntmp.columns = ['max','min']\nprint(tmp.shape)\nprint(tmp.loc[tmp['max']!=tmp['min']].shape)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:29:44.780036Z","iopub.execute_input":"2022-11-09T21:29:44.780397Z","iopub.status.idle":"2022-11-09T21:29:44.801435Z","shell.execute_reply.started":"2022-11-09T21:29:44.780364Z","shell.execute_reply":"2022-11-09T21:29:44.799670Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"96/2000*100","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:29:44.805523Z","iopub.execute_input":"2022-11-09T21:29:44.805945Z","iopub.status.idle":"2022-11-09T21:29:44.814456Z","shell.execute_reply.started":"2022-11-09T21:29:44.805903Z","shell.execute_reply":"2022-11-09T21:29:44.813116Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"In 4.8% races there are differences in numbers of mesurement for horses.","metadata":{}},{"cell_type":"markdown","source":"## Differences between given distance and calculated distance","metadata":{}},{"cell_type":"code","source":"fig = plt.figure(figsize=(16, 12))\nsns.boxplot(x=distance['distance_meters'].round(0).astype(int), y=distance['trakus_distance_cumsum']);\nplt.xlabel(\"Actual distance\", fontsize=14)\nplt.ylabel(\"Calculated distance\", fontsize=14)\nplt.title(\"Actual distance vs. calculated distance\", fontsize=14)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:29:44.816098Z","iopub.execute_input":"2022-11-09T21:29:44.816473Z","iopub.status.idle":"2022-11-09T21:29:45.406091Z","shell.execute_reply.started":"2022-11-09T21:29:44.816424Z","shell.execute_reply":"2022-11-09T21:29:45.404935Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"distance = distance.reset_index(drop=True)\nstats = distance.groupby(['distance_meters']).describe()\nstats.round(2)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:29:45.411719Z","iopub.execute_input":"2022-11-09T21:29:45.412105Z","iopub.status.idle":"2022-11-09T21:29:45.529622Z","shell.execute_reply.started":"2022-11-09T21:29:45.412071Z","shell.execute_reply":"2022-11-09T21:29:45.528382Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"* There are few distances with small amount of records, some are probably from one or two races. \n* Sometimes (in aproximately 5% of races) few horses position was counted for smaller amount of time than the rest.","metadata":{}},{"cell_type":"markdown","source":"### Statistics for the most popular races","metadata":{}},{"cell_type":"code","source":"tmp  = stats.reset_index().loc[:,[['distance_meters',''],['trakus_distance_cumsum', 'count']]]\ntmp.columns = ['distance_meters','count']\ntmp = tmp.loc[tmp['count']>=500]\ndistance_cut = pd.merge(distance, tmp, on='distance_meters', how='inner').drop('count', axis=1)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:29:45.531087Z","iopub.execute_input":"2022-11-09T21:29:45.531985Z","iopub.status.idle":"2022-11-09T21:29:45.555163Z","shell.execute_reply.started":"2022-11-09T21:29:45.531949Z","shell.execute_reply":"2022-11-09T21:29:45.553979Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig = plt.figure(figsize=(16, 12))\nsns.boxplot(x=distance_cut['distance_meters'].round(0).astype(int), y=distance_cut['trakus_distance_cumsum'])\nplt.xlabel(\"Actual distance\", fontsize=14)\nplt.ylabel(\"Calculated distance\", fontsize=14)\nplt.title(\"Actual distance vs. calculated distance\", fontsize=14)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:29:45.556962Z","iopub.execute_input":"2022-11-09T21:29:45.557448Z","iopub.status.idle":"2022-11-09T21:29:45.919796Z","shell.execute_reply.started":"2022-11-09T21:29:45.557401Z","shell.execute_reply":"2022-11-09T21:29:45.918474Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"distance_cut.groupby('distance_meters').describe().round(2)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:29:45.921337Z","iopub.execute_input":"2022-11-09T21:29:45.921694Z","iopub.status.idle":"2022-11-09T21:29:45.983318Z","shell.execute_reply.started":"2022-11-09T21:29:45.921662Z","shell.execute_reply":"2022-11-09T21:29:45.982201Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Statistics without horses with incomplete positions","metadata":{}},{"cell_type":"code","source":"trakus_count = tracking.groupby([\n        'track_id',\n        'race_date',\n        'race_number',\n        'program_number'\n    ]).agg({'trakus_index':'max'}).reset_index()\nmax_trakus = trakus_count.reset_index().groupby([\n        'track_id',\n        'race_date',\n        'race_number'\n    ]).agg({'trakus_index':'max'}).reset_index()\ntmp = pd.merge(trakus_count, max_trakus, how='inner', on=['track_id', 'race_date', 'race_number', 'trakus_index'])","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:29:45.984845Z","iopub.execute_input":"2022-11-09T21:29:45.985196Z","iopub.status.idle":"2022-11-09T21:29:47.626169Z","shell.execute_reply.started":"2022-11-09T21:29:45.985164Z","shell.execute_reply":"2022-11-09T21:29:47.624915Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"distance2 = pd.merge(nyra_complete, tmp, how='inner', on=['track_id', \n                                                          'race_date',\n                                                          'race_number',\n                                                          'trakus_index', \n                                                          'program_number']).loc[:,['distance_meters', \n                                                                                  'trakus_distance_cumsum']]\ndistance2.head(5)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:29:47.627494Z","iopub.execute_input":"2022-11-09T21:29:47.627908Z","iopub.status.idle":"2022-11-09T21:29:50.379168Z","shell.execute_reply.started":"2022-11-09T21:29:47.627873Z","shell.execute_reply":"2022-11-09T21:29:50.377964Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig = plt.figure(figsize=(16, 12))\nsns.boxplot(x=distance2['distance_meters'].round(0).astype(int), y=distance2['trakus_distance_cumsum'])\nplt.xlabel(\"Actual distance\", fontsize=14)\nplt.ylabel(\"Calculated distance\", fontsize=14)\nplt.title(\"Actual distance vs. calculated distance\", fontsize=14)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:29:50.380570Z","iopub.execute_input":"2022-11-09T21:29:50.380961Z","iopub.status.idle":"2022-11-09T21:29:50.958868Z","shell.execute_reply.started":"2022-11-09T21:29:50.380927Z","shell.execute_reply":"2022-11-09T21:29:50.957774Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"distance.groupby(['distance_meters']).describe().round(2)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:29:50.960526Z","iopub.execute_input":"2022-11-09T21:29:50.961320Z","iopub.status.idle":"2022-11-09T21:29:51.075362Z","shell.execute_reply.started":"2022-11-09T21:29:50.961273Z","shell.execute_reply":"2022-11-09T21:29:51.074103Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Statistics for the most popular races","metadata":{}},{"cell_type":"code","source":"tmp  = stats.reset_index().loc[:,[['distance_meters',''],['trakus_distance_cumsum', 'count']]]\ntmp.columns = ['distance_meters','count']\ntmp = tmp.loc[tmp['count']>=500]\ndistance2_cut = pd.merge(distance2, tmp, on='distance_meters', how='inner').drop('count', axis=1)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:29:51.077062Z","iopub.execute_input":"2022-11-09T21:29:51.078022Z","iopub.status.idle":"2022-11-09T21:29:51.102646Z","shell.execute_reply.started":"2022-11-09T21:29:51.077969Z","shell.execute_reply":"2022-11-09T21:29:51.101446Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig = plt.figure(figsize=(16, 12))\nsns.boxplot(x=distance2_cut['distance_meters'].round(0).astype(int), y=distance2_cut['trakus_distance_cumsum'])\nplt.xlabel(\"Actual distance\", fontsize=14)\nplt.ylabel(\"Calculated distance\", fontsize=14)\nplt.title(\"Actual distance vs. calculated distance\", fontsize=14)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:29:51.104349Z","iopub.execute_input":"2022-11-09T21:29:51.104733Z","iopub.status.idle":"2022-11-09T21:29:51.465570Z","shell.execute_reply.started":"2022-11-09T21:29:51.104700Z","shell.execute_reply":"2022-11-09T21:29:51.464697Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Feature engineering\n\nNew features:\n* % of distance - `trakus_distance_perct`\n* number of horses in race - `number_of_horses`\n* distance to the nearest horse (previous and next) - `distance_to_previous_horse`, `distance_to_next_horse`\n* difference between horse distance and mean distance of all horses - `difference_from_mean_distance`\n\nFeatures to remove:\n- `track_id`\n- `race_date`\n- `race_number`\n- `program_number`\n- `trakus_index` ???\n- `latitude`\n- `longitude`\n- `distance_id`\n- `purse` \n- `post_time`\n- `odds`\n- `jockey` \n- `race_type`\n- `trakus_distance_cumsum`\n\nFeatures to encode:\n- `course_type` \n- `track_condition`","metadata":{}},{"cell_type":"code","source":"# distance to the nearest horse\ndef get_distance_to_the_nearest_horse(x: pd.DataFrame):\n    tmp = x['trakus_distance_cumsum'].sort_values()\n    distance_to_previous_horse = tmp.diff(1).fillna(0).rename(\"distance_to_previous_horse\")\n    distance_to_next_horse = distance_to_previous_horse.shift(-1).fillna(0).rename(\"distance_to_next_horse\")\n    return pd.concat([distance_to_previous_horse, distance_to_next_horse], axis=1)\n\ndistance_to_other = nyra_complete.groupby([\"track_id\", \"race_date\", \"race_number\", \"trakus_index\"]).progress_apply(get_distance_to_the_nearest_horse)\nnyra_complete = nyra_complete.join(distance_to_other.droplevel([0, 1, 2, 3]))","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:29:51.466991Z","iopub.execute_input":"2022-11-09T21:29:51.467500Z","iopub.status.idle":"2022-11-09T21:52:26.920038Z","shell.execute_reply.started":"2022-11-09T21:29:51.467467Z","shell.execute_reply":"2022-11-09T21:52:26.918631Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# difference from mean distance\ndef difference_from_mean_distance(x: pd.DataFrame):\n    tmp = (x[\"trakus_distance_cumsum\"] - x[\"trakus_distance_cumsum\"].mean()).rename(\"difference_from_mean_distance\")\n    return tmp\n\ndifference_from_mean_distance = nyra_complete.groupby([\"track_id\", \"race_date\", \"race_number\", \"trakus_index\"]).progress_apply(difference_from_mean_distance)\n\nnyra_complete = nyra_complete.join(difference_from_mean_distance.droplevel([0, 1, 2, 3]))","metadata":{"execution":{"iopub.status.busy":"2022-11-09T21:52:26.921476Z","iopub.execute_input":"2022-11-09T21:52:26.921831Z","iopub.status.idle":"2022-11-09T22:02:14.365959Z","shell.execute_reply.started":"2022-11-09T21:52:26.921798Z","shell.execute_reply":"2022-11-09T22:02:14.364654Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_complete[\"course_type\"] = nyra_complete[\"course_type\"].map({\n    \"M\": \"Hurdle\",\n    \"D\": \"Dirt\",\n    \"O\": \"Outer turf\",\n    \"I\": \"Inner turf\",\n    \"T\": \"Turf\",    \n})","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:02:14.367534Z","iopub.execute_input":"2022-11-09T22:02:14.368665Z","iopub.status.idle":"2022-11-09T22:02:14.808327Z","shell.execute_reply.started":"2022-11-09T22:02:14.368626Z","shell.execute_reply":"2022-11-09T22:02:14.806904Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_complete[\"track_condition\"] = nyra_complete[\"track_condition\"].map({\n    \"YL \": \"Yielding\",\n    \"MY \": \"Muddy\",\n    \"SY \": \"Sloppy\",\n    \"SF \": \"Soft\",\n    \"FM \": \"Firm\",    \n    \"GD \": \"Good\",    \n    \"FT \": \"Fast\",  \n})","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:02:14.809805Z","iopub.execute_input":"2022-11-09T22:02:14.810213Z","iopub.status.idle":"2022-11-09T22:02:15.341521Z","shell.execute_reply.started":"2022-11-09T22:02:14.810179Z","shell.execute_reply":"2022-11-09T22:02:15.340435Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_complete = nyra_complete.join(pd.get_dummies(nyra_complete[\"course_type\"]))","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:02:15.342888Z","iopub.execute_input":"2022-11-09T22:02:15.343850Z","iopub.status.idle":"2022-11-09T22:02:17.146903Z","shell.execute_reply.started":"2022-11-09T22:02:15.343805Z","shell.execute_reply":"2022-11-09T22:02:17.145832Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_complete = nyra_complete.join(pd.get_dummies(nyra_complete[\"track_condition\"]))","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:02:17.148460Z","iopub.execute_input":"2022-11-09T22:02:17.149009Z","iopub.status.idle":"2022-11-09T22:02:19.056129Z","shell.execute_reply.started":"2022-11-09T22:02:17.148967Z","shell.execute_reply":"2022-11-09T22:02:19.054222Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Model","metadata":{}},{"cell_type":"code","source":"import statsmodels.api as sm\nfrom sklearn.model_selection import train_test_split","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:02:19.057915Z","iopub.execute_input":"2022-11-09T22:02:19.059337Z","iopub.status.idle":"2022-11-09T22:02:20.202579Z","shell.execute_reply.started":"2022-11-09T22:02:19.059286Z","shell.execute_reply":"2022-11-09T22:02:20.201036Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_complete = nyra_complete.loc[nyra_complete.weight_carried <= 132]\nnyra_complete.index = pd.DatetimeIndex(nyra_complete[\"race_date\"])\nnyra_complete = nyra_complete.drop(\"race_date\", axis = 1)\nnyra_complete.index = pd.DatetimeIndex(nyra_complete.index)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:02:20.204537Z","iopub.execute_input":"2022-11-09T22:02:20.205041Z","iopub.status.idle":"2022-11-09T22:02:26.085979Z","shell.execute_reply.started":"2022-11-09T22:02:20.204983Z","shell.execute_reply":"2022-11-09T22:02:26.084701Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_complete.columns","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:02:26.087744Z","iopub.execute_input":"2022-11-09T22:02:26.088303Z","iopub.status.idle":"2022-11-09T22:02:26.095981Z","shell.execute_reply.started":"2022-11-09T22:02:26.088268Z","shell.execute_reply":"2022-11-09T22:02:26.094690Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"columns_X = [\n    'run_up_distance', 'weight_carried',\n     'distance_meters', 'trakus_distance',\n    'trakus_distance_perct', 'distance_position', 'number_of_horses',\n    'distance_to_previous_horse', 'distance_to_next_horse',\n    'difference_from_mean_distance',\n    'Hurdle',\n    \"Dirt\",\n    \"Outer turf\",\n    \"Inner turf\",\n    \"Turf\",\n    \"Yielding\",\n    \"Muddy\",\n    \"Sloppy\",\n    \"Soft\",\n    \"Firm\",\n    \"Good\",\n    \"Fast\",\n    \"odds\",\n]","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:02:54.070742Z","iopub.execute_input":"2022-11-09T22:02:54.071218Z","iopub.status.idle":"2022-11-09T22:02:54.078226Z","shell.execute_reply.started":"2022-11-09T22:02:54.071185Z","shell.execute_reply":"2022-11-09T22:02:54.077009Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"dates = nyra_complete.resample('D').track_id.max().dropna().to_frame()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:02:54.422754Z","iopub.execute_input":"2022-11-09T22:02:54.423649Z","iopub.status.idle":"2022-11-09T22:02:58.908437Z","shell.execute_reply.started":"2022-11-09T22:02:54.423610Z","shell.execute_reply":"2022-11-09T22:02:58.906743Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_dates, test_dates = train_test_split(dates, test_size=0.2, random_state=42, stratify = dates[\"track_id\"])\ntrain_dates, test_dates = train_dates.index, test_dates.index ","metadata":{"scrolled":true,"execution":{"iopub.status.busy":"2022-11-09T22:02:58.911187Z","iopub.execute_input":"2022-11-09T22:02:58.911609Z","iopub.status.idle":"2022-11-09T22:02:58.923835Z","shell.execute_reply.started":"2022-11-09T22:02:58.911558Z","shell.execute_reply":"2022-11-09T22:02:58.922003Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_train = nyra_complete.loc[train_dates][columns_X]\nX_test = nyra_complete.loc[test_dates][columns_X]\n\ny_train = nyra_complete.loc[train_dates][[\"position_at_finish\"]]\ny_test = nyra_complete.loc[test_dates][[\"position_at_finish\"]]","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:03:02.951887Z","iopub.execute_input":"2022-11-09T22:03:02.952643Z","iopub.status.idle":"2022-11-09T22:03:21.807585Z","shell.execute_reply.started":"2022-11-09T22:03:02.952605Z","shell.execute_reply":"2022-11-09T22:03:21.806071Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_train = sm.add_constant(X_train)\nX_test = sm.add_constant(X_test)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:03:21.809962Z","iopub.execute_input":"2022-11-09T22:03:21.810785Z","iopub.status.idle":"2022-11-09T22:03:22.528617Z","shell.execute_reply.started":"2022-11-09T22:03:21.810732Z","shell.execute_reply":"2022-11-09T22:03:22.527258Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"model = sm.OLS(y_train, X_train).fit()","metadata":{"scrolled":true,"execution":{"iopub.status.busy":"2022-11-09T22:03:22.530112Z","iopub.execute_input":"2022-11-09T22:03:22.530569Z","iopub.status.idle":"2022-11-09T22:03:31.042852Z","shell.execute_reply.started":"2022-11-09T22:03:22.530515Z","shell.execute_reply":"2022-11-09T22:03:31.040866Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"model.summary()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:03:31.047859Z","iopub.execute_input":"2022-11-09T22:03:31.048516Z","iopub.status.idle":"2022-11-09T22:03:31.591885Z","shell.execute_reply.started":"2022-11-09T22:03:31.048457Z","shell.execute_reply":"2022-11-09T22:03:31.589309Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"prediction_y = model.predict(X_test)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:03:31.594781Z","iopub.execute_input":"2022-11-09T22:03:31.595461Z","iopub.status.idle":"2022-11-09T22:03:31.750071Z","shell.execute_reply.started":"2022-11-09T22:03:31.595403Z","shell.execute_reply":"2022-11-09T22:03:31.748176Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_complete_test = nyra_complete.loc[test_dates]\nnyra_complete_test[\"predicted_PaF\"] = prediction_y","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:03:31.752825Z","iopub.execute_input":"2022-11-09T22:03:31.753518Z","iopub.status.idle":"2022-11-09T22:03:33.004357Z","shell.execute_reply.started":"2022-11-09T22:03:31.753456Z","shell.execute_reply":"2022-11-09T22:03:33.003384Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_complete_test[\"predicted_PaF\"] = np.where(nyra_complete_test[\"predicted_PaF\"] < 1, 1, nyra_complete_test[\"predicted_PaF\"])","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:03:33.005940Z","iopub.execute_input":"2022-11-09T22:03:33.006841Z","iopub.status.idle":"2022-11-09T22:03:33.019638Z","shell.execute_reply.started":"2022-11-09T22:03:33.006804Z","shell.execute_reply":"2022-11-09T22:03:33.018342Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# track_id + race_date + race_number + program_number = unique horse","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:03:33.021383Z","iopub.execute_input":"2022-11-09T22:03:33.021754Z","iopub.status.idle":"2022-11-09T22:03:33.028985Z","shell.execute_reply.started":"2022-11-09T22:03:33.021719Z","shell.execute_reply":"2022-11-09T22:03:33.027861Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Results Analysys","metadata":{}},{"cell_type":"code","source":"indexing = nyra_complete_test.groupby([\"track_id\",\"race_date\",\"race_number\",\"program_number\"]).count()\nindexing[\"hourse_index\"] =  [i for i in range(0,2916)]\nindexing = indexing[[\"hourse_index\"]].reset_index()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:03:33.030358Z","iopub.execute_input":"2022-11-09T22:03:33.030776Z","iopub.status.idle":"2022-11-09T22:03:33.827366Z","shell.execute_reply.started":"2022-11-09T22:03:33.030735Z","shell.execute_reply":"2022-11-09T22:03:33.826260Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_complete_test = nyra_complete_test.merge(indexing, on = [\"track_id\", \"race_date\", \"race_number\", \"program_number\"])","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:03:33.831282Z","iopub.execute_input":"2022-11-09T22:03:33.831690Z","iopub.status.idle":"2022-11-09T22:03:35.638124Z","shell.execute_reply.started":"2022-11-09T22:03:33.831658Z","shell.execute_reply":"2022-11-09T22:03:35.636914Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_1_place = nyra_complete_test.loc[nyra_complete_test.position_at_finish == 1]","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:03:35.639656Z","iopub.execute_input":"2022-11-09T22:03:35.640039Z","iopub.status.idle":"2022-11-09T22:03:35.991489Z","shell.execute_reply.started":"2022-11-09T22:03:35.640006Z","shell.execute_reply":"2022-11-09T22:03:35.990218Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(test_1_place.hourse_index.unique()[0])\nprint(test_1_place.hourse_index.unique()[75])\nprint(test_1_place.hourse_index.unique()[150])\nprint(test_1_place.hourse_index.unique()[225])\nprint(test_1_place.hourse_index.unique()[300])\nprint(test_1_place.hourse_index.unique()[350])\nprint(test_1_place.hourse_index.unique()[400])","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:03:35.993799Z","iopub.execute_input":"2022-11-09T22:03:35.994262Z","iopub.status.idle":"2022-11-09T22:03:36.011279Z","shell.execute_reply.started":"2022-11-09T22:03:35.994219Z","shell.execute_reply":"2022-11-09T22:03:36.009770Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data_for_plot1 = test_1_place.loc[(test_1_place.hourse_index == 270) | (test_1_place.hourse_index == 2154) |\n                 (test_1_place.hourse_index == 1230) | (test_1_place.hourse_index == 465) |\n                 (test_1_place.hourse_index == 262)|\n                 (test_1_place.hourse_index == 2088)]","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:03:36.012812Z","iopub.execute_input":"2022-11-09T22:03:36.013924Z","iopub.status.idle":"2022-11-09T22:03:36.025480Z","shell.execute_reply.started":"2022-11-09T22:03:36.013879Z","shell.execute_reply":"2022-11-09T22:03:36.024015Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize = (16,8))\nsns.set_theme()\nsns.lineplot(data = data_for_plot1, x=\"trakus_index\", y=\"predicted_PaF\", hue = \"hourse_index\");\nplt.xlabel(\"Trakus_index\", fontsize=14)\nplt.ylabel(\"Predicted place at finish\", fontsize=14)\nplt.title(\"Finish position change through the running of a race\", fontsize=14)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:03:36.027683Z","iopub.execute_input":"2022-11-09T22:03:36.028760Z","iopub.status.idle":"2022-11-09T22:03:36.512841Z","shell.execute_reply.started":"2022-11-09T22:03:36.028720Z","shell.execute_reply":"2022-11-09T22:03:36.511480Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"In every case predicting 1 position at finish was easier in second part of race. ","metadata":{}},{"cell_type":"code","source":"test_12_place = nyra_complete_test.loc[nyra_complete_test.position_at_finish == 12]","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:03:36.514729Z","iopub.execute_input":"2022-11-09T22:03:36.515149Z","iopub.status.idle":"2022-11-09T22:03:36.526191Z","shell.execute_reply.started":"2022-11-09T22:03:36.515110Z","shell.execute_reply":"2022-11-09T22:03:36.524912Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(test_12_place.hourse_index.unique()[0])\nprint(test_12_place.hourse_index.unique()[2])\nprint(test_12_place.hourse_index.unique()[4])\nprint(test_12_place.hourse_index.unique()[8])\nprint(test_12_place.hourse_index.unique()[10])","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:03:36.527403Z","iopub.execute_input":"2022-11-09T22:03:36.527768Z","iopub.status.idle":"2022-11-09T22:03:36.538124Z","shell.execute_reply.started":"2022-11-09T22:03:36.527735Z","shell.execute_reply":"2022-11-09T22:03:36.536725Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data_for_plot2 = test_12_place.loc[(test_12_place.hourse_index == 2665) | (test_12_place.hourse_index == 2740) |\n                 (test_12_place.hourse_index == 2175) | (test_12_place.hourse_index == 773) |\n                 (test_12_place.hourse_index == 262)|\n                 (test_12_place.hourse_index == 609)]","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:04:01.911687Z","iopub.execute_input":"2022-11-09T22:04:01.915168Z","iopub.status.idle":"2022-11-09T22:04:01.931272Z","shell.execute_reply.started":"2022-11-09T22:04:01.915101Z","shell.execute_reply":"2022-11-09T22:04:01.929731Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize = (16,8))\nsns.set_theme()\nsns.lineplot(data = data_for_plot2, x=\"trakus_index\", y=\"predicted_PaF\", hue = \"hourse_index\");\nplt.xlabel(\"Trakus_index\", fontsize=14)\nplt.ylabel(\"Predicted place at finish\", fontsize=14)\nplt.title(\"Finish position change through the running of a race\", fontsize=14)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:04:03.422735Z","iopub.execute_input":"2022-11-09T22:04:03.423450Z","iopub.status.idle":"2022-11-09T22:04:04.003911Z","shell.execute_reply.started":"2022-11-09T22:04:03.423411Z","shell.execute_reply":"2022-11-09T22:04:04.002292Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Predicting the last place is almost impossible in first phase of race. We can assume that performing of hourse in second half is the most important element in winning.","metadata":{}},{"cell_type":"code","source":"test_5_place = nyra_complete_test.loc[nyra_complete_test.position_at_finish == 5]","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:04:06.459719Z","iopub.execute_input":"2022-11-09T22:04:06.460153Z","iopub.status.idle":"2022-11-09T22:04:06.556829Z","shell.execute_reply.started":"2022-11-09T22:04:06.460118Z","shell.execute_reply":"2022-11-09T22:04:06.555654Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(test_5_place.hourse_index.unique()[0])\nprint(test_5_place.hourse_index.unique()[2])\nprint(test_5_place.hourse_index.unique()[4])\nprint(test_5_place.hourse_index.unique()[8])\nprint(test_5_place.hourse_index.unique()[10])","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:04:06.658551Z","iopub.execute_input":"2022-11-09T22:04:06.658987Z","iopub.status.idle":"2022-11-09T22:04:06.672814Z","shell.execute_reply.started":"2022-11-09T22:04:06.658950Z","shell.execute_reply":"2022-11-09T22:04:06.671366Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data_for_plot3 = test_5_place.loc[(test_5_place.hourse_index == 266) | (test_5_place.hourse_index == 280) |\n                 (test_5_place.hourse_index == 295) | (test_5_place.hourse_index == 2649) |\n                 (test_5_place.hourse_index == 262)|\n                 (test_5_place.hourse_index == 2668)]","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:04:07.242480Z","iopub.execute_input":"2022-11-09T22:04:07.243146Z","iopub.status.idle":"2022-11-09T22:04:07.254307Z","shell.execute_reply.started":"2022-11-09T22:04:07.243112Z","shell.execute_reply":"2022-11-09T22:04:07.252947Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize = (16,8))\nsns.set_theme()\nsns.lineplot(data = data_for_plot3, x=\"trakus_index\", y=\"predicted_PaF\", hue = \"hourse_index\");\nplt.xlabel(\"Trakus_index\", fontsize=14)\nplt.ylabel(\"Predicted place at finish\", fontsize=14)\nplt.title(\"Finish position change through the running of a race\", fontsize=14)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:04:07.639996Z","iopub.execute_input":"2022-11-09T22:04:07.640432Z","iopub.status.idle":"2022-11-09T22:04:08.115236Z","shell.execute_reply.started":"2022-11-09T22:04:07.640398Z","shell.execute_reply":"2022-11-09T22:04:08.113932Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"On the one hand predicting palces in middle is easy (has good metrics results), because how the linear regression works. On the other the resoning from regression resutls for these places is slighty more difficult. ","metadata":{}},{"cell_type":"code","source":"columns_X = [\n    'run_up_distance', 'weight_carried',\n     'distance_meters', 'trakus_distance',\n    'trakus_distance_perct', 'distance_position', 'number_of_horses',\n    'distance_to_previous_horse', 'distance_to_next_horse',\n    'difference_from_mean_distance',\n    'Hurdle',\n    \"Dirt\",\n    \"Outer turf\",\n    \"Inner turf\",\n    \"Turf\",\n    \"Yielding\",\n    \"Muddy\",\n    \"Sloppy\",\n    \"Soft\",\n    \"Firm\",\n    \"Good\",\n    \"Fast\",\n    \"odds\",\n]\n\nX_train = nyra_complete.loc[nyra_complete.trakus_distance_perct < 0.25].loc[train_dates][columns_X]\ny_train = nyra_complete.loc[nyra_complete.trakus_distance_perct < 0.25].loc[train_dates][[\"position_at_finish\"]]\n\nX_train = sm.add_constant(X_train)\nmodel = sm.OLS(y_train, X_train).fit()\n\nprint(\"in 1/4 part of race\")\nprint(\"coef of distance_to_previous_horse in 1/4 race\", model.params.distance_to_previous_horse)\nprint(\"coef of distance_to_next_horse in 1/4 race\", model.params.distance_to_next_horse)\n\n\nX_train = nyra_complete.loc[nyra_complete.trakus_distance_perct.between(0.25,0.5)].loc[train_dates][columns_X]\ny_train = nyra_complete.loc[nyra_complete.trakus_distance_perct.between(0.25,0.5)].loc[train_dates][[\"position_at_finish\"]]\n\nX_train = sm.add_constant(X_train)\nmodel = sm.OLS(y_train, X_train).fit()\n\nprint(\"in 2/4 part of race\")\nprint(\"coef of distance_to_previous_horse\", model.params.distance_to_previous_horse)\nprint(\"coef of distance_to_next_horse\", model.params.distance_to_next_horse)\n\n\nX_train = nyra_complete.loc[nyra_complete.trakus_distance_perct.between(0.5,0.75)].loc[train_dates][columns_X]\ny_train = nyra_complete.loc[nyra_complete.trakus_distance_perct.between(0.5,0.75)].loc[train_dates][[\"position_at_finish\"]]\n\nX_train = sm.add_constant(X_train)\nmodel = sm.OLS(y_train, X_train).fit()\n\nprint(\"in 3/4 part of race\")\nprint(\"coef of distance_to_previous_horse\", model.params.distance_to_previous_horse)\nprint(\"coef of distance_to_next_horse\", model.params.distance_to_next_horse)\n\nX_train = nyra_complete.loc[nyra_complete.trakus_distance_perct.between(0.75, 1)].loc[train_dates][columns_X]\ny_train = nyra_complete.loc[nyra_complete.trakus_distance_perct.between(0.75, 1)].loc[train_dates][[\"position_at_finish\"]]\n\nX_train = sm.add_constant(X_train)\nmodel = sm.OLS(y_train, X_train).fit()\n\nprint(\"in 4/4 part of race\")\nprint(\"coef of distance_to_previous_horse\", model.params.distance_to_previous_horse)\nprint(\"coef of distance_to_next_horse\", model.params.distance_to_next_horse)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T22:04:08.308079Z","iopub.execute_input":"2022-11-09T22:04:08.308803Z","iopub.status.idle":"2022-11-09T22:04:26.638541Z","shell.execute_reply.started":"2022-11-09T22:04:08.308765Z","shell.execute_reply":"2022-11-09T22:04:26.633084Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"How we can see the the distance_to_next_hourse is the most (linear) significant in last quarter of race","metadata":{}}]}