{"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":"code","source":"import os\nimport warnings\nfrom pathlib import Path\n\nimport matplotlib.pyplot as plt\nimport numpy as np\nimport pandas as pd\nfrom pandas.api.types import is_string_dtype\nfrom pandas.api.types import is_numeric_dtype\nimport seaborn as sns\nfrom IPython.display import display\nfrom pandas.api.types import CategoricalDtype\n\nfrom sklearn.impute import SimpleImputer\nfrom category_encoders import MEstimateEncoder\nfrom sklearn.cluster import KMeans\nfrom sklearn.ensemble import RandomForestClassifier\nfrom sklearn.decomposition import PCA\nfrom sklearn.feature_selection import mutual_info_regression\nfrom sklearn.model_selection import KFold, cross_val_score\nfrom xgboost import XGBClassifier\nfrom sklearn.model_selection import train_test_split\n\n# Set Matplotlib defaults\nplt.style.use(\"seaborn-whitegrid\")\nplt.rc(\"figure\", autolayout=True)\nplt.rc(\n    \"axes\",\n    labelweight=\"bold\",\n    labelsize=\"large\",\n    titleweight=\"bold\",\n    titlesize=14,\n    titlepad=10,\n)\n\n# Global Variables\nworking_data_dir = Path(\"../input/big-data-derby-2022\")\ninput_data_dir = Path(\"../input/big-data-derby-2022\")\nnum_cat = 7\nnum_val = 10\n\n# Mute warnings\nwarnings.filterwarnings('ignore')","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-08-21T20:03:12.258653Z","iopub.execute_input":"2022-08-21T20:03:12.259169Z","iopub.status.idle":"2022-08-21T20:03:14.576640Z","shell.execute_reply.started":"2022-08-21T20:03:12.259062Z","shell.execute_reply":"2022-08-21T20:03:14.575470Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Introduction #\n\nThis notebook has been created for the Big Data Derby, and is an Exploratory Data Analysis (EDA) of the data contained in the competition.\n\nThe EDA follows a guide provided by Leonie Monigatti, which can be found here: https://towardsdatascience.com/99-lessons-on-data-analysis-from-placing-top-5-in-5-kaggle-analytics-challenges-453d5e7b4581. Some excerpts of code from this guide are used explicitly below.","metadata":{}},{"cell_type":"markdown","source":"## Preparation ##\n\nThere are 4 data files in this compeition, described below:\n\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\nSince the last table is a combination of all three tables, I'll load that one into a Pandas dataframe.","metadata":{}},{"cell_type":"code","source":"def load_data():\n    # Read data\n    df = pd.read_csv(working_data_dir / \"nyra_2019_complete.csv\")\n    return df\n\ndf = load_data()","metadata":{"execution":{"iopub.status.busy":"2022-08-21T20:03:14.579077Z","iopub.execute_input":"2022-08-21T20:03:14.579763Z","iopub.status.idle":"2022-08-21T20:03:29.993778Z","shell.execute_reply.started":"2022-08-21T20:03:14.579715Z","shell.execute_reply":"2022-08-21T20:03:29.992499Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df.info()","metadata":{"execution":{"iopub.status.busy":"2022-08-21T20:03:29.995345Z","iopub.execute_input":"2022-08-21T20:03:29.995702Z","iopub.status.idle":"2022-08-21T20:03:30.021709Z","shell.execute_reply.started":"2022-08-21T20:03:29.995662Z","shell.execute_reply":"2022-08-21T20:03:30.020442Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Program value is a number\ndf.program_number.unique()\ndf.program_number = df.program_number.replace(\"1A \", \"21\")\ndf.program_number = df.program_number.replace(\"3X \", \"33\")\ndf.program_number = df.program_number.replace(\"1X \", \"31\")\ndf.program_number = df.program_number.replace(\"2B \", \"42\")\ndf.program_number.unique()\n\ndf.program_number = np.int64(df.program_number)","metadata":{"execution":{"iopub.status.busy":"2022-08-21T20:03:30.024810Z","iopub.execute_input":"2022-08-21T20:03:30.025199Z","iopub.status.idle":"2022-08-21T20:03:31.779215Z","shell.execute_reply.started":"2022-08-21T20:03:30.025152Z","shell.execute_reply":"2022-08-21T20:03:31.778026Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Dataset Description\n\nThe description of the fields provided by the competition are below:\n\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 \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":"code","source":"# How many unique values are there?\nfor feature in df.columns:\n    if (is_string_dtype(df[feature])):\n        print(feature + \": \" + str(df[feature].nunique()))\n","metadata":{"execution":{"iopub.status.busy":"2022-08-21T20:03:31.780879Z","iopub.execute_input":"2022-08-21T20:03:31.781282Z","iopub.status.idle":"2022-08-21T20:03:34.157496Z","shell.execute_reply.started":"2022-08-21T20:03:31.781236Z","shell.execute_reply":"2022-08-21T20:03:34.156609Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"There are no missing values","metadata":{}},{"cell_type":"code","source":"# Display the percentage of missing values\ndf.isna().sum(axis = 0) / len(df) * 100\n# Visualize the missing values\nsns.heatmap(df.isna(),       \n            cbar = False,    \n            cmap = \"binary\")","metadata":{"execution":{"iopub.status.busy":"2022-08-21T20:03:34.158567Z","iopub.execute_input":"2022-08-21T20:03:34.158931Z","iopub.status.idle":"2022-08-21T20:05:00.850201Z","shell.execute_reply.started":"2022-08-21T20:03:34.158902Z","shell.execute_reply":"2022-08-21T20:05:00.848818Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#num_plots = num_cat+num_val*2\n\nfig, axis = plt.subplots(nrows = 4, ncols = 6, figsize=(15, 12))\n\n#i = 0\n#for feature in df.columns:\n#    if (is_numeric_dtype(df[feature])):\n#        sns.histplot(data = df, x = df[feature], ax = axis[i])\n#        i += 1\n#        sns.boxplot(data = df, y = df[feature], ax = axis[i])\n#        i += 1\n#    if (is_string_dtype(df[feature])):\n#        sns.countplot(data = df, x = df[feature], ax = axis[i])\n#        i += 1\n\nsns.countplot(data = df, x = df[\"track_id\"], ax = axis[0][0])\nsns.countplot(data = df, x = df[\"race_number\"], ax = axis[0][1])\nsns.countplot(data = df, x = df[\"program_number\"], ax = axis[0][2])\nsns.countplot(data = df, x = df[\"course_type\"], ax = axis[0][3])\nsns.countplot(data = df, x = df[\"track_condition\"], ax = axis[0][4])\nsns.countplot(data = df, x = df[\"post_time\"], ax = axis[0][5])\nsns.countplot(data = df, x = df[\"jockey\"], ax = axis[1][0])\n\nsns.histplot(data = df, x = df[\"race_date\"], ax = axis[1][1])\nsns.histplot(data = df, x = df[\"trakus_index\"], ax = axis[1][2])\nsns.boxplot(data = df, x = df[\"trakus_index\"], ax = axis[1][3])\nsns.histplot(data = df, x = df[\"latitude\"], ax = axis[1][4])\nsns.boxplot(data = df, x = df[\"latitude\"], ax = axis[1][5])\nsns.histplot(data = df, x = df[\"longitude\"], ax = axis[2][0])\nsns.boxplot(data = df, x = df[\"longitude\"], ax = axis[2][1])\nsns.histplot(data = df, x = df[\"distance_id\"], ax = axis[2][2])\nsns.boxplot(data = df, x = df[\"distance_id\"], ax = axis[2][3])\nsns.histplot(data = df, x = df[\"run_up_distance\"], ax = axis[2][4])\nsns.boxplot(data = df, x = df[\"run_up_distance\"], ax = axis[2][5])\nsns.histplot(data = df, x = df[\"weight_carried\"], ax = axis[3][0])\nsns.boxplot(data = df, x = df[\"weight_carried\"], ax = axis[3][1])\nsns.histplot(data = df, x = df[\"odds\"], ax = axis[3][2])\nsns.boxplot(data = df, x = df[\"odds\"], ax = axis[3][3])\nsns.histplot(data = df, x = df[\"purse\"], ax = axis[3][4])\nsns.boxplot(data = df, x = df[\"purse\"], ax = axis[3][5])\n \nfig.show()","metadata":{"execution":{"iopub.status.busy":"2022-08-21T20:05:00.852133Z","iopub.execute_input":"2022-08-21T20:05:00.852485Z","iopub.status.idle":"2022-08-21T20:06:27.898143Z","shell.execute_reply.started":"2022-08-21T20:05:00.852455Z","shell.execute_reply":"2022-08-21T20:06:27.897220Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Some initial observations from making these tables:\n* Purse is missing from the competition description of the complete table.\n* Some features are listed as char instead of int64 (e.g., race number)\n* There are three tracks. Some data could be analyzed for differences from each track.\n* Some jockey's are in a lot of races, others in a few races.\n* Odds could be normalized for feature analysis.\n* Positional data should be separated based on track.\n* There are some Purse outliers with very large values.\n* Track Condition looks ordinal.","metadata":{}},{"cell_type":"code","source":"num_list = df.select_dtypes(include=\"int64\").columns\nfig = plt.figure(figsize = (20,25))\n\nfor i in range(len(num_list)):\n    column = num_list[i]\n    sub = fig.add_subplot(10, 5, i + 1)\n    sns.boxplot(x = column, data = df, palette = \"RdYlBu_r\")","metadata":{"execution":{"iopub.status.busy":"2022-08-21T20:06:27.899441Z","iopub.execute_input":"2022-08-21T20:06:27.900142Z","iopub.status.idle":"2022-08-21T20:06:34.836167Z","shell.execute_reply.started":"2022-08-21T20:06:27.900105Z","shell.execute_reply":"2022-08-21T20:06:34.835128Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"I'm creating a new feature by converting the odds field to probability of winning.","metadata":{}},{"cell_type":"code","source":"df[\"odds_probability\"] = df[\"odds\"]/100/(df[\"odds\"]/100+1)\nsns.histplot(data = df, x = df[\"odds_probability\"])","metadata":{"execution":{"iopub.status.busy":"2022-08-21T20:06:34.837517Z","iopub.execute_input":"2022-08-21T20:06:34.837928Z","iopub.status.idle":"2022-08-21T20:06:37.250750Z","shell.execute_reply.started":"2022-08-21T20:06:34.837891Z","shell.execute_reply":"2022-08-21T20:06:37.249712Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's look at the jockeys. ","metadata":{}},{"cell_type":"code","source":"# Load the jockey data\ndf_start = pd.read_csv(working_data_dir / \"nyra_start_table.csv\")","metadata":{"execution":{"iopub.status.busy":"2022-08-21T20:06:37.254500Z","iopub.execute_input":"2022-08-21T20:06:37.254830Z","iopub.status.idle":"2022-08-21T20:06:37.285537Z","shell.execute_reply.started":"2022-08-21T20:06:37.254800Z","shell.execute_reply":"2022-08-21T20:06:37.284541Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(\"Number of jockeys: \" + str(df_start[\"jockey\"].nunique()))\nprint(\"Mean jockey weight: \" + str(df_start[\"weight_carried\"].mean()))\n\nprint(\"The ten most common jockeys are: \")\nprint(df_start[\"jockey\"].value_counts().head(10))\n\nprint(\"The ten least common jockeys are: \")\nprint(df_start[\"jockey\"].value_counts().tail(10))\n","metadata":{"execution":{"iopub.status.busy":"2022-08-21T20:06:37.286975Z","iopub.execute_input":"2022-08-21T20:06:37.287304Z","iopub.status.idle":"2022-08-21T20:06:37.301586Z","shell.execute_reply.started":"2022-08-21T20:06:37.287273Z","shell.execute_reply":"2022-08-21T20:06:37.300672Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's break up the time fields.","metadata":{}},{"cell_type":"code","source":"df.race_date.sample(n=5)","metadata":{"execution":{"iopub.status.busy":"2022-08-21T20:06:37.303064Z","iopub.execute_input":"2022-08-21T20:06:37.303370Z","iopub.status.idle":"2022-08-21T20:06:37.572384Z","shell.execute_reply.started":"2022-08-21T20:06:37.303342Z","shell.execute_reply":"2022-08-21T20:06:37.571169Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Convert to datetime data type\ndf[\"race_date\"] = pd.to_datetime(df[\"race_date\"], format = \"%Y-%m-%d\")","metadata":{"execution":{"iopub.status.busy":"2022-08-21T20:06:37.574943Z","iopub.execute_input":"2022-08-21T20:06:37.575805Z","iopub.status.idle":"2022-08-21T20:06:38.895714Z","shell.execute_reply.started":"2022-08-21T20:06:37.575758Z","shell.execute_reply":"2022-08-21T20:06:38.894394Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Creating datetimeindex features\ndf[\"year\"] = pd.DatetimeIndex(df[\"race_date\"]).year\ndf[\"month\"] = pd.DatetimeIndex(df[\"race_date\"]).month\ndf[\"day\"] = pd.DatetimeIndex(df[\"race_date\"]).dayofyear\ndf[\"weekday\"] = pd.DatetimeIndex(df[\"race_date\"]).weekday","metadata":{"execution":{"iopub.status.busy":"2022-08-21T20:06:38.897462Z","iopub.execute_input":"2022-08-21T20:06:38.897952Z","iopub.status.idle":"2022-08-21T20:06:40.874161Z","shell.execute_reply.started":"2022-08-21T20:06:38.897903Z","shell.execute_reply":"2022-08-21T20:06:40.873027Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The last part of the preparation is to review the evaluation of the competition. \n\nSince the lat/long data is new to the dataset, the goal of the competition is to investigate insights based on this additional data.\n\nThe hosts of the competition are also interested in the health of the horses, although this is not the goal of this specific competition.\n\nAny other insights from the data are also welcome.","metadata":{}},{"cell_type":"markdown","source":"## Exploratory Data Anaysis\n\nWe'll start by plotting some relationships in the data. The goal is to see if there are any interesting relationships.","metadata":{}},{"cell_type":"code","source":"df_sar_Aug2_9_3_JRV = df.loc[df[\"track_id\"] == \"SAR\"].loc[df[\"race_date\"] == \"2019-08-02\"].loc[df[\"jockey\"] == \"John R. Velazquez\"].loc[df[\"race_number\"] == 9].loc[df[\"program_number\"] == 3]\n#df_sar_Aug2_9_3_JRV = df.loc[df[\"track_id\"] == \"SAR\", df[\"race_date\"] == \"2019-08-02\", df[\"race_number\"] == 9, df[\"program_number\"] == 3, df[\"jockey\"] == \"John R. Velazquez\"]\ndf_sar_Aug2_9_3_JRV.sample(n=5)\n","metadata":{"execution":{"iopub.status.busy":"2022-08-21T20:06:40.875881Z","iopub.execute_input":"2022-08-21T20:06:40.876230Z","iopub.status.idle":"2022-08-21T20:06:42.321001Z","shell.execute_reply.started":"2022-08-21T20:06:40.876200Z","shell.execute_reply":"2022-08-21T20:06:42.319925Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Median purse for each jockey.","metadata":{}},{"cell_type":"code","source":"df[\"race_id\"] = str(df.race_date) + str(df.race_number)\ndf_jockey_purse = df.drop_duplicates(subset=[\"race_id\", \"jockey\", \"purse\"])\ndf_jockey_purse.jockey.nunique","metadata":{"execution":{"iopub.status.busy":"2022-08-21T20:06:42.322211Z","iopub.execute_input":"2022-08-21T20:06:42.322522Z","iopub.status.idle":"2022-08-21T20:06:47.184550Z","shell.execute_reply.started":"2022-08-21T20:06:42.322495Z","shell.execute_reply":"2022-08-21T20:06:47.183458Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize=(200,20))\nsns.boxplot(data=df_jockey_purse, x=\"jockey\", y=\"purse\")","metadata":{"execution":{"iopub.status.busy":"2022-08-21T20:06:47.185903Z","iopub.execute_input":"2022-08-21T20:06:47.186591Z","iopub.status.idle":"2022-08-21T20:06:53.511326Z","shell.execute_reply.started":"2022-08-21T20:06:47.186560Z","shell.execute_reply":"2022-08-21T20:06:53.510160Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize=(20,5))\nsns.boxplot(data=df, x=\"track_id\", y=\"purse\")","metadata":{"execution":{"iopub.status.busy":"2022-08-21T20:06:53.512932Z","iopub.execute_input":"2022-08-21T20:06:53.513546Z","iopub.status.idle":"2022-08-21T20:06:56.933680Z","shell.execute_reply.started":"2022-08-21T20:06:53.513511Z","shell.execute_reply":"2022-08-21T20:06:56.932503Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"From these two plots, a couple observations can be made:\n* High purse races are dominated by a small subset of jockeys. \n* Some jockeys don't compete in lower purse races.\n* All tracks support a majority of low purse races, with a few higher purse races. BEL has the highest paid races, followed by SAR and then AQU.","metadata":{}},{"cell_type":"code","source":"sns.scatterplot(data=df, x=df_sar_Aug2_9_3_JRV.longitude, y=df_sar_Aug2_9_3_JRV.latitude)","metadata":{"execution":{"iopub.status.busy":"2022-08-21T20:06:56.935389Z","iopub.execute_input":"2022-08-21T20:06:56.935754Z","iopub.status.idle":"2022-08-21T20:06:57.236556Z","shell.execute_reply.started":"2022-08-21T20:06:56.935721Z","shell.execute_reply":"2022-08-21T20:06:57.235406Z"},"trusted":true},"execution_count":null,"outputs":[]}]}