{"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 Horse Racing Analysis","metadata":{}},{"cell_type":"markdown","source":" ### Goal of the competition\n The goal of this competition is to analyze horse racing tactics, drafting strategies, and path efficiency","metadata":{}},{"cell_type":"markdown","source":"### Dataset\nThe dataset's provided by NYTHA/NYRA which inludes, but not limited to: tracks races took place, race date, weight carried, jockey, odds, course type, course condition, etc.","metadata":{}},{"cell_type":"markdown","source":"### Column ID's\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\n","metadata":{}},{"cell_type":"markdown","source":"### Importing Libraries","metadata":{}},{"cell_type":"code","source":"##importing libraries\nimport numpy as np\nimport pandas as pd\nfrom matplotlib import pyplot as plt\nimport seaborn as sns","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:46:43.860206Z","iopub.execute_input":"2022-11-10T19:46:43.860564Z","iopub.status.idle":"2022-11-10T19:46:43.865177Z","shell.execute_reply.started":"2022-11-10T19:46:43.860535Z","shell.execute_reply":"2022-11-10T19:46:43.864147Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Data Preparation","metadata":{}},{"cell_type":"code","source":"race_table = pd.read_csv(\"../input/big-data-derby-2022/nyra_race_table.csv\")\nstart_table = pd.read_csv(\"../input/big-data-derby-2022/nyra_start_table.csv\")\ntracking_table = pd.read_csv(\"../input/big-data-derby-2022/nyra_tracking_table.csv\")\ncomplete_table = pd.read_csv(\"../input/big-data-derby-2022/nyra_2019_complete.csv\")","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:46:43.870880Z","iopub.execute_input":"2022-11-10T19:46:43.871815Z","iopub.status.idle":"2022-11-10T19:47:05.231964Z","shell.execute_reply.started":"2022-11-10T19:46:43.871772Z","shell.execute_reply":"2022-11-10T19:47:05.231141Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(race_table.info())\nprint(start_table.info())\nprint(tracking_table.info())\nprint(complete_table.info())","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:47:05.234291Z","iopub.execute_input":"2022-11-10T19:47:05.234634Z","iopub.status.idle":"2022-11-10T19:47:05.278625Z","shell.execute_reply.started":"2022-11-10T19:47:05.234602Z","shell.execute_reply":"2022-11-10T19:47:05.277393Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Data Processing","metadata":{}},{"cell_type":"code","source":"print(race_table.head())\n","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:47:05.280699Z","iopub.execute_input":"2022-11-10T19:47:05.281225Z","iopub.status.idle":"2022-11-10T19:47:05.292884Z","shell.execute_reply.started":"2022-11-10T19:47:05.281075Z","shell.execute_reply":"2022-11-10T19:47:05.291826Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(start_table.head())\n","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:47:05.295499Z","iopub.execute_input":"2022-11-10T19:47:05.295781Z","iopub.status.idle":"2022-11-10T19:47:05.303873Z","shell.execute_reply.started":"2022-11-10T19:47:05.295756Z","shell.execute_reply":"2022-11-10T19:47:05.303215Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(tracking_table.head())\n","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:47:05.304944Z","iopub.execute_input":"2022-11-10T19:47:05.305263Z","iopub.status.idle":"2022-11-10T19:47:05.321535Z","shell.execute_reply.started":"2022-11-10T19:47:05.305237Z","shell.execute_reply":"2022-11-10T19:47:05.320560Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(complete_table.head())","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:47:05.322698Z","iopub.execute_input":"2022-11-10T19:47:05.323007Z","iopub.status.idle":"2022-11-10T19:47:05.340854Z","shell.execute_reply.started":"2022-11-10T19:47:05.322980Z","shell.execute_reply":"2022-11-10T19:47:05.339640Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"###assigning columns names to the columns in the complete table \ncomplete_table.columns = [\"track_id\",\"race_date\",\"race_number\",\"program_number\",\"trakus_index\",\n                \"latitude\",\"longitude\",\"distance_id\",\"course_type\",\"track_condition\", \n                \"run_up_distance\",\"race_type\",\"purse\",\"post_time\",\"weight_carried\",\"jockey\",\n                \"odds\", \"position_at_finish\"]\n","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:47:05.341880Z","iopub.execute_input":"2022-11-10T19:47:05.342248Z","iopub.status.idle":"2022-11-10T19:47:05.354393Z","shell.execute_reply.started":"2022-11-10T19:47:05.342213Z","shell.execute_reply":"2022-11-10T19:47:05.352869Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"complete_table.head()","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:47:05.355820Z","iopub.execute_input":"2022-11-10T19:47:05.356172Z","iopub.status.idle":"2022-11-10T19:47:05.385836Z","shell.execute_reply.started":"2022-11-10T19:47:05.356132Z","shell.execute_reply":"2022-11-10T19:47:05.384517Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"###checking for null values\ncomplete_table.isna().any()","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:47:05.387146Z","iopub.execute_input":"2022-11-10T19:47:05.387492Z","iopub.status.idle":"2022-11-10T19:47:06.623393Z","shell.execute_reply.started":"2022-11-10T19:47:05.387465Z","shell.execute_reply":"2022-11-10T19:47:06.622246Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"###race duration\nprint(complete_table[\"race_date\"].max())\nprint(complete_table[\"race_date\"].min())\n","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:47:06.628071Z","iopub.execute_input":"2022-11-10T19:47:06.628763Z","iopub.status.idle":"2022-11-10T19:47:07.177973Z","shell.execute_reply.started":"2022-11-10T19:47:06.628732Z","shell.execute_reply":"2022-11-10T19:47:07.176582Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"###checking for unique track id\nprint( complete_table[\"track_id\"].unique())\n\n###checking for unique race type\nprint(complete_table[\"race_type\"].unique())\n\n###unique track condition\nprint(complete_table[\"track_condition\"].unique())\n\n###unique course type\nprint(complete_table[\"course_type\"].unique())\n","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:47:07.180183Z","iopub.execute_input":"2022-11-10T19:47:07.181186Z","iopub.status.idle":"2022-11-10T19:47:08.351779Z","shell.execute_reply.started":"2022-11-10T19:47:07.181145Z","shell.execute_reply":"2022-11-10T19:47:08.350249Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"##extracting month from the race date column\ncomplete_table[\"race_month\"] = pd.DatetimeIndex(complete_table['race_date']).month\ncomplete_table[\"race_month\"]","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:47:08.354728Z","iopub.execute_input":"2022-11-10T19:47:08.355044Z","iopub.status.idle":"2022-11-10T19:47:09.092991Z","shell.execute_reply.started":"2022-11-10T19:47:08.355016Z","shell.execute_reply":"2022-11-10T19:47:09.091599Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"##replacing the values in the track id column\ncomplete_table['track_id'] = complete_table['track_id'].replace('AQU','Aqueduct')","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:47:09.094302Z","iopub.execute_input":"2022-11-10T19:47:09.094680Z","iopub.status.idle":"2022-11-10T19:47:09.291203Z","shell.execute_reply.started":"2022-11-10T19:47:09.094644Z","shell.execute_reply":"2022-11-10T19:47:09.290023Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"complete_table['track_id'] = complete_table['track_id'].replace({'BEL':'Belmont', 'SAR':'Saratoga'})\ncomplete_table['track_id'].unique()","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:47:09.292335Z","iopub.execute_input":"2022-11-10T19:47:09.292626Z","iopub.status.idle":"2022-11-10T19:47:10.134376Z","shell.execute_reply.started":"2022-11-10T19:47:09.292599Z","shell.execute_reply":"2022-11-10T19:47:10.133389Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"##replacing the values in the track condition column\ncomplete_table[\"track_condition\"] = complete_table[\"track_condition\"].replace({\"YL \": \"Yielding\",\n                                                                               \"FM \" :\"Firm\", \n                                                                               \"SY \" :\" Sloppy\", \n                                                                               \"GD \" : \"Good\", \n                                                                               \"FT \": \"Fast\", \n                                                                               \"MY \": \"Muddy\",\n                                                                               \"SF \": \"Soft\"})\ncomplete_table[\"track_condition\"].unique()","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:47:10.135569Z","iopub.execute_input":"2022-11-10T19:47:10.136449Z","iopub.status.idle":"2022-11-10T19:47:11.569289Z","shell.execute_reply.started":"2022-11-10T19:47:10.136414Z","shell.execute_reply":"2022-11-10T19:47:11.567766Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"##replacing the values in the race type column\ncomplete_table[\"race_type\"] = complete_table[\"race_type\"].replace({\"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\"})\ncomplete_table[\"race_type\"].unique()","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:47:11.570840Z","iopub.execute_input":"2022-11-10T19:47:11.571226Z","iopub.status.idle":"2022-11-10T19:47:13.624084Z","shell.execute_reply.started":"2022-11-10T19:47:11.571190Z","shell.execute_reply":"2022-11-10T19:47:13.622850Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"###replacing the values in the course type column\ncomplete_table[\"course_type\"]=complete_table[\"course_type\"].replace({\"M\" : \"Hurdle\", \n                                                                 \"D\" : \"Dirt\",\n                                                                 \"O\" : \"Outer turf\", \n                                                                 \"I\" : \"Inner turf\", \n                                                                 \"T\" : \"Turf\"})\ncomplete_table[\"course_type\"].unique()","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:47:13.625226Z","iopub.execute_input":"2022-11-10T19:47:13.625508Z","iopub.status.idle":"2022-11-10T19:47:14.819686Z","shell.execute_reply.started":"2022-11-10T19:47:13.625482Z","shell.execute_reply":"2022-11-10T19:47:14.818473Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Exploratory Data Analysis","metadata":{}},{"cell_type":"code","source":"##trackid distribution\ncomplete_table.groupby(\"track_id\")[\"track_id\"].count().plot(kind = \"bar\", title = \"Track Location\")\nplt.xlabel(\"Track location\");","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:47:14.821145Z","iopub.execute_input":"2022-11-10T19:47:14.821530Z","iopub.status.idle":"2022-11-10T19:47:15.522476Z","shell.execute_reply.started":"2022-11-10T19:47:14.821497Z","shell.execute_reply":"2022-11-10T19:47:15.521461Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"###count of race by month\ncomplete_table.groupby(\"race_month\")[\"race_month\"].count().sort_values(ascending = False).plot(kind = \"bar\", title = \"Number of Races by Month\")\nplt.xlabel(\"Month\");","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:47:15.523742Z","iopub.execute_input":"2022-11-10T19:47:15.524676Z","iopub.status.idle":"2022-11-10T19:47:15.769613Z","shell.execute_reply.started":"2022-11-10T19:47:15.524617Z","shell.execute_reply":"2022-11-10T19:47:15.768694Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"###distribution of track condition\ncomplete_table.groupby(\"track_condition\")[\"track_condition\"].count().sort_values( ascending = False).plot(kind = \"bar\", title = \"Race Course Condition\")\nplt.xlabel(\"Track condition\");","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:47:15.770782Z","iopub.execute_input":"2022-11-10T19:47:15.773388Z","iopub.status.idle":"2022-11-10T19:47:16.432427Z","shell.execute_reply.started":"2022-11-10T19:47:15.773354Z","shell.execute_reply":"2022-11-10T19:47:16.431386Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"###race type distribution\ncomplete_table.groupby(\"race_type\")[\"race_type\"].count().sort_values().plot(kind = \"barh\", title = \"Race Type\")\nplt.ylabel(\"Race type\");","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:47:16.433749Z","iopub.execute_input":"2022-11-10T19:47:16.434070Z","iopub.status.idle":"2022-11-10T19:47:17.166414Z","shell.execute_reply.started":"2022-11-10T19:47:16.434041Z","shell.execute_reply":"2022-11-10T19:47:17.164959Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"###summary statistics of the purse distribution for race passed\ncomplete_table.pivot_table(values = \"purse\", index = \"race_type\", aggfunc = [np.mean, max, min, sum])","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:47:17.168063Z","iopub.execute_input":"2022-11-10T19:47:17.168760Z","iopub.status.idle":"2022-11-10T19:47:19.162451Z","shell.execute_reply.started":"2022-11-10T19:47:17.168725Z","shell.execute_reply":"2022-11-10T19:47:19.161264Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"###distribution of course type\ncomplete_table.groupby(\"course_type\")[\"course_type\"].count().sort_values(ascending = False).plot(kind = \"bar\", title = \"Race Course Type\");","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:47:19.164435Z","iopub.execute_input":"2022-11-10T19:47:19.164847Z","iopub.status.idle":"2022-11-10T19:47:19.823334Z","shell.execute_reply.started":"2022-11-10T19:47:19.164813Z","shell.execute_reply":"2022-11-10T19:47:19.822164Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"###top 10 jockeys\ntop_jockey = complete_table.groupby(\"jockey\")[\"track_id\"].count().sort_values(ascending = False)\ntop_jockey.head(10).plot(kind = \"bar\", title = \"Top 10 Jockeys by Number of Races\");\n","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:47:19.825132Z","iopub.execute_input":"2022-11-10T19:47:19.825566Z","iopub.status.idle":"2022-11-10T19:47:20.548735Z","shell.execute_reply.started":"2022-11-10T19:47:19.825530Z","shell.execute_reply":"2022-11-10T19:47:20.547648Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"color_dict = {'Dirt' : 'blue',\n             'Inner turf' : 'yellow',\n             'Outer turf' : 'red',\n             'Turf' : 'orange',\n             'Hurdle' : 'grey'}\n\n\n\nfor track in ['Aqueduct', 'Belmont', 'Saratoga']:\n    plt.figure(figsize = (16, 8))\n\n    sns.scatterplot(data = complete_table[(complete_table.track_id == track)], x = \"longitude\", y = \"latitude\", \n                    hue = \"course_type\", \n                    marker='.',\n                   palette=color_dict, )\n\n    plt.title(track)\n    plt.legend()\n    plt.show()","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:47:20.549820Z","iopub.execute_input":"2022-11-10T19:47:20.550120Z","iopub.status.idle":"2022-11-10T19:48:49.095081Z","shell.execute_reply.started":"2022-11-10T19:47:20.550077Z","shell.execute_reply":"2022-11-10T19:48:49.093880Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"###plot of the weight carried by the horse in the race\nsns.histplot(complete_table[\"weight_carried\"], bins = 20)\nplt.xlabel(\"Weight\")\nplt.title(\"Distribution of Jockey Weight\");\n","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:48:49.096324Z","iopub.execute_input":"2022-11-10T19:48:49.096622Z","iopub.status.idle":"2022-11-10T19:48:52.156019Z","shell.execute_reply.started":"2022-11-10T19:48:49.096594Z","shell.execute_reply":"2022-11-10T19:48:52.154860Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"###race number distribution\n\ncomplete_table.hist(column = \"race_number\", alpha = .5 ,rwidth = 1)\nplt.xlabel(\"Race number\")\nplt.style.use('bmh')\nplt.title(\"Race Number Distribution\");","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:48:52.157199Z","iopub.execute_input":"2022-11-10T19:48:52.157454Z","iopub.status.idle":"2022-11-10T19:48:52.425409Z","shell.execute_reply.started":"2022-11-10T19:48:52.157431Z","shell.execute_reply":"2022-11-10T19:48:52.424040Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.style.available","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:48:52.429188Z","iopub.execute_input":"2022-11-10T19:48:52.429457Z","iopub.status.idle":"2022-11-10T19:48:52.436566Z","shell.execute_reply.started":"2022-11-10T19:48:52.429434Z","shell.execute_reply":"2022-11-10T19:48:52.435472Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"###program number distribution\ncomplete_table.hist(column = \"trakus_index\", alpha = .5 ,rwidth = 1)\nplt.style.use('fast')\nplt.title(\"Trakus Index Distribution\");","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:48:52.438289Z","iopub.execute_input":"2022-11-10T19:48:52.438735Z","iopub.status.idle":"2022-11-10T19:48:52.723974Z","shell.execute_reply.started":"2022-11-10T19:48:52.438702Z","shell.execute_reply":"2022-11-10T19:48:52.722866Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}