{"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 Analysis - Horse with High Odds\n\n## Overview of Analysis\n\n**Purpose of this Competion**\nWe hope this competition allows us to interact with data scientists to help find solutions to equine safety issues as well as develop a roster of academics and motivated hobbyists who lead us in analyzing the coming generations of data.\n\n**Awareness of the Issues**\nHow do you pick a horse that has high odds but is likely to win? I thought that the jockey's skill and strategy would be very important for a horse with high odds to win. Therefore, I am examining ways to quantify the jockey's skill.\n\n**Q: Can we evaluate a jockey's skills in 360 degrees?**\nTo Answer this question write down viewpoint for 360-degree evaluation.\n\n* From viewpoint of Horse\n  * Does jockey bring out the horse's ability for the course?\n  * Is the jockey putting too much pressure on the horse during the race?\n* From viewpoint of Drafting Strategy\n  * Is the jockey able to execute a drafting strategy for each course?\n  * Will the fastest horse win? If it wins, what course does it take?\n* From viewpoint of Ticket Buyer\n  * How well is the jockey supported by the ticket buyers?\n","metadata":{}},{"cell_type":"code","source":"import os\nimport pandas as pd\nimport numpy as np\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport warnings\nwarnings.simplefilter(\"ignore\")\n\n\n!pip install pymap3d\nimport pymap3d as pm","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:36:22.978973Z","iopub.execute_input":"2022-09-01T06:36:22.979713Z","iopub.status.idle":"2022-09-01T06:36:40.326772Z","shell.execute_reply.started":"2022-09-01T06:36:22.979593Z","shell.execute_reply":"2022-09-01T06:36:40.325774Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Load the Data","metadata":{}},{"cell_type":"code","source":"All_Path = \"../input/big-data-derby-2022/nyra_2019_complete.csv\"\nStart_Path = \"../input/big-data-derby-2022/nyra_start_table.csv\"\nRun_Path = \"../input/big-data-derby-2022/nyra_race_table.csv\"\nTrack_Path = \"../input/big-data-derby-2022/nyra_tracking_table.csv\"\n\nall_df = pd.read_csv(All_Path)\nstart_df = pd.read_csv(Start_Path)\nrace_df = pd.read_csv(Run_Path)\ntrack_df = pd.read_csv(Track_Path)\n\n","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:36:40.328996Z","iopub.execute_input":"2022-09-01T06:36:40.329385Z","iopub.status.idle":"2022-09-01T06:36:58.726760Z","shell.execute_reply.started":"2022-09-01T06:36:40.329348Z","shell.execute_reply":"2022-09-01T06:36:58.725725Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Check the shape of each dataframe\nprint(all_df.shape) # (row, column)\nprint(start_df.shape)\nprint(race_df.shape)\nprint(track_df.shape)","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:36:58.728193Z","iopub.execute_input":"2022-09-01T06:36:58.728674Z","iopub.status.idle":"2022-09-01T06:36:58.735084Z","shell.execute_reply.started":"2022-09-01T06:36:58.728635Z","shell.execute_reply":"2022-09-01T06:36:58.733785Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Basic EDA\n\nnyra race table","metadata":{}},{"cell_type":"code","source":"race_df.head()","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:36:58.738220Z","iopub.execute_input":"2022-09-01T06:36:58.739375Z","iopub.status.idle":"2022-09-01T06:36:58.762615Z","shell.execute_reply.started":"2022-09-01T06:36:58.739340Z","shell.execute_reply":"2022-09-01T06:36:58.761757Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_df.describe()","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:36:58.764183Z","iopub.execute_input":"2022-09-01T06:36:58.765164Z","iopub.status.idle":"2022-09-01T06:36:58.800228Z","shell.execute_reply.started":"2022-09-01T06:36:58.765128Z","shell.execute_reply":"2022-09-01T06:36:58.799185Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_df.info()","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:36:58.801794Z","iopub.execute_input":"2022-09-01T06:36:58.802206Z","iopub.status.idle":"2022-09-01T06:36:58.820062Z","shell.execute_reply.started":"2022-09-01T06:36:58.802171Z","shell.execute_reply":"2022-09-01T06:36:58.818940Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_df.isnull().sum()","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:36:58.821923Z","iopub.execute_input":"2022-09-01T06:36:58.822653Z","iopub.status.idle":"2022-09-01T06:36:58.833703Z","shell.execute_reply.started":"2022-09-01T06:36:58.822618Z","shell.execute_reply":"2022-09-01T06:36:58.832302Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"There is no Null Values in race data","metadata":{}},{"cell_type":"markdown","source":"## Cardinality","metadata":{}},{"cell_type":"code","source":"race_df.nunique().plot.bar()","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:36:58.835703Z","iopub.execute_input":"2022-09-01T06:36:58.836183Z","iopub.status.idle":"2022-09-01T06:36:59.105897Z","shell.execute_reply.started":"2022-09-01T06:36:58.836149Z","shell.execute_reply":"2022-09-01T06:36:59.105002Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's look at the columns with high cardinarity.\nHigh Cardinarity Column\n\nName: **race_date,race_date,run_up_distance,purse**","metadata":{}},{"cell_type":"code","source":"def get_column(df):\n    cat = [x for x in df.columns if df[x].dtype == \"object\"]\n    num = [x for x in df.columns if df[x].dtype != \"object\"]\n    return cat, num\n\ncat, num = get_column(race_df)","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:36:59.107352Z","iopub.execute_input":"2022-09-01T06:36:59.107932Z","iopub.status.idle":"2022-09-01T06:36:59.114051Z","shell.execute_reply.started":"2022-09-01T06:36:59.107894Z","shell.execute_reply":"2022-09-01T06:36:59.112934Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cat","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:36:59.118779Z","iopub.execute_input":"2022-09-01T06:36:59.119086Z","iopub.status.idle":"2022-09-01T06:36:59.126610Z","shell.execute_reply.started":"2022-09-01T06:36:59.119039Z","shell.execute_reply":"2022-09-01T06:36:59.125534Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"num","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:36:59.128023Z","iopub.execute_input":"2022-09-01T06:36:59.128383Z","iopub.status.idle":"2022-09-01T06:36:59.137844Z","shell.execute_reply.started":"2022-09-01T06:36:59.128349Z","shell.execute_reply":"2022-09-01T06:36:59.136613Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def plot_bar(df,col_name_,agg_col,color_):\n    std_df = pd.DataFrame(df.groupby(col_name_)[agg_col].count()).reset_index();\n    std_df[str(agg_col)+'_ratio'] = 100*(std_df[agg_col]/df.shape[0])\n    std_df = std_df.sort_values(by=agg_col,ascending=False)\n    order = std_df[col_name_]\n    if col_name_ in ['purse','post_time']:\n        plt.figure(figsize=[48,4]);\n    else:\n        plt.figure(figsize=[12,4]);\n    plt.subplot(1,2,1);\n    g = sns.countplot(data=df,x=col_name_,color=color_,order=order);\n    plt.ylabel('Frequency');\n    plt.title('Distribution of '+str(col_name_));\n\n    plt.subplot(1,2,2);\n    sns.barplot(data=std_df,x=col_name_,y=str(agg_col)+'_ratio',color=color_,order=order);\n    plt.ylabel('Ratio(%)');\n    plt.title('Ratio(%) of '+str(col_name_));\n\n    \n    temp_df = std_df.T\n    temp_df.columns = temp_df.loc[col_name_,:].values\n    temp_df = temp_df.drop(index=col_name_)\n    temp_df.index = ['Count','Ratio(%)']\n\n    display(temp_df)\n","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:36:59.139904Z","iopub.execute_input":"2022-09-01T06:36:59.140314Z","iopub.status.idle":"2022-09-01T06:36:59.152175Z","shell.execute_reply.started":"2022-09-01T06:36:59.140279Z","shell.execute_reply":"2022-09-01T06:36:59.151312Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_df[\"track_id\"].value_counts()","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:36:59.153813Z","iopub.execute_input":"2022-09-01T06:36:59.154329Z","iopub.status.idle":"2022-09-01T06:36:59.167824Z","shell.execute_reply.started":"2022-09-01T06:36:59.154295Z","shell.execute_reply":"2022-09-01T06:36:59.166775Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"track_id","metadata":{}},{"cell_type":"code","source":"plot_bar(race_df, \"track_id\", \"course_type\", \"lightblue\")","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:36:59.169466Z","iopub.execute_input":"2022-09-01T06:36:59.169812Z","iopub.status.idle":"2022-09-01T06:36:59.479229Z","shell.execute_reply.started":"2022-09-01T06:36:59.169778Z","shell.execute_reply":"2022-09-01T06:36:59.478356Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The most raced location was AQU - Aqueduct, with 825 races, or about 41% of the total, followed by BEL - Belmont, with 772 races, or about 38% of the total. The second most raced location is BEL - Belmont, with 772 races, or 38% of the total, and the third most raced location is SAR - Saratoga, with 403 races, or 20% of the total..","metadata":{}},{"cell_type":"markdown","source":"race_date","metadata":{}},{"cell_type":"code","source":"# Copy the race dataframe\nunique_race_df = race_df.copy()\n\n# convert the race_date column into datetime dtype\nunique_race_df[\"race_date\"] = pd.to_datetime(unique_race_df[\"race_date\"])\n\n# create new column race_month from race_date column\nunique_race_df[\"race_month\"] = unique_race_df[\"race_date\"].dt.month\n\n# Count race number according to race month and track id\n# unique_race_df.groupby([\"track_id\", \"race_month\"])[\"race_number\"].count()\n\n# Create the stucture to show race number in a table\npd.crosstab(unique_race_df[\"track_id\"], unique_race_df[\"race_month\"]).style.background_gradient(cmap=\"Blues\")\n\n","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:36:59.480627Z","iopub.execute_input":"2022-09-01T06:36:59.480933Z","iopub.status.idle":"2022-09-01T06:36:59.576393Z","shell.execute_reply.started":"2022-09-01T06:36:59.480906Z","shell.execute_reply":"2022-09-01T06:36:59.575311Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's look at when races are held at each racetrack. It seems to me that each racetrack has a specific time of year when they hold their races. In AQU, horse races are held from November to April. BEL holds horse races from April to July and September and October In SAR, they are held in July and August.","metadata":{}},{"cell_type":"markdown","source":"course_type","metadata":{}},{"cell_type":"code","source":"plot_bar(race_df, \"course_type\", \"track_id\", \"lightblue\")","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:36:59.577884Z","iopub.execute_input":"2022-09-01T06:36:59.579517Z","iopub.status.idle":"2022-09-01T06:36:59.902947Z","shell.execute_reply.started":"2022-09-01T06:36:59.579480Z","shell.execute_reply":"2022-09-01T06:36:59.902053Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_df[\"course_type\"].value_counts()","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:36:59.904922Z","iopub.execute_input":"2022-09-01T06:36:59.905187Z","iopub.status.idle":"2022-09-01T06:36:59.914530Z","shell.execute_reply.started":"2022-09-01T06:36:59.905161Z","shell.execute_reply":"2022-09-01T06:36:59.913518Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"track_condition\n\nLet's check the condition of the ground when the race was held.\n\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\nFor dirt tracks the track conditions are:\n\n- FT -fast: dry, even, resilient surface\n- GD -good: a track that is almost fast\n- MY -muddy: a track that is wet but has no standing water\n- SY -sloppy: a track saturated with water; with standing water visible\n","metadata":{}},{"cell_type":"code","source":"plot_bar(race_df[race_df[\"course_type\"] == \"D\"], \"track_condition\", \"track_id\", \"lightblue\")","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:36:59.915844Z","iopub.execute_input":"2022-09-01T06:36:59.916896Z","iopub.status.idle":"2022-09-01T06:37:00.223159Z","shell.execute_reply.started":"2022-09-01T06:36:59.916852Z","shell.execute_reply":"2022-09-01T06:37:00.222177Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's look at the case of a dirt ground. This ground seems to be more susceptible to rain. When a horse race is being held, the ground condition is fast, held 978 times, accounting for about 72% of the total. the second most common ground condition is sloppy, held 207 times, accounting for about 15% of the total. the third most common ground condition is good, held 92 times, accounting for about 4% of the total. The fourth most common ground condition is muddy, held 74 times, accounting for about 5% of the total.\n\nFor turf tracks, the track conditions are:\n\n- FM -firm: a firm, resilient surface\n- GD -good: a turf course slightly softer than firm\n- YL -yielding: a turf course with a significant amount of \"give\" to the ground due to recent rain\n- SF- soft: a turf course with a large amount of moisture. Horses sink very deeply into it","metadata":{}},{"cell_type":"code","source":"plot_bar(race_df[race_df[\"course_type\"] != \"D\"], \"track_condition\", \"track_id\", \"lightblue\")","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:37:00.224840Z","iopub.execute_input":"2022-09-01T06:37:00.225176Z","iopub.status.idle":"2022-09-01T06:37:00.628219Z","shell.execute_reply.started":"2022-09-01T06:37:00.225142Z","shell.execute_reply":"2022-09-01T06:37:00.627083Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's look at the case of non-dirt ground. This ground seems to be less affected by rain. When a horse race is being held, the ground condition is firm 455 times, accounting for about 70% of the total. The fourth most common ground condition is soft, which was held 3 times, accounting for about 0.4% of the total.","metadata":{}},{"cell_type":"markdown","source":"race_type","metadata":{}},{"cell_type":"code","source":"plot_bar(race_df, \"race_type\", \"track_id\", \"lightblue\")","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:37:00.634991Z","iopub.execute_input":"2022-09-01T06:37:00.635435Z","iopub.status.idle":"2022-09-01T06:37:01.419749Z","shell.execute_reply.started":"2022-09-01T06:37:00.635400Z","shell.execute_reply":"2022-09-01T06:37:01.418864Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The most popular type of race was CLM - Claiming, with 471 events, or about 23% of the total. The third most popular race type is MCL - Maiden Claiming, which has been held 325 times and accounts for about 16% of the total. The top three, but not the top three, accounting for more than 10% of the total are, in descending order, STK, AOC, and ALW; those with less than 5% of the total are, in descending order, STR, SOC, SST, WCL, SHP, and WMC.","metadata":{}},{"cell_type":"markdown","source":"## Numeric\n\nrace_number","metadata":{}},{"cell_type":"code","source":"def plot_hist(df, col_name_, color_):\n    plt.figure(figsize = [12, 4])\n    sns.histplot(data = df, x = col_name_, color = color_)\n    plt.ylabel(\"Frequency\")\n    plt.title(\"Distribution of \" + str(col_name_));\n    agg_df = pd.DataFrame(df[col_name_].value_counts())\n    display(agg_df.T)","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:37:01.424009Z","iopub.execute_input":"2022-09-01T06:37:01.426281Z","iopub.status.idle":"2022-09-01T06:37:01.434470Z","shell.execute_reply.started":"2022-09-01T06:37:01.426229Z","shell.execute_reply":"2022-09-01T06:37:01.433194Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_hist(race_df, \"race_number\", \"lightblue\")","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:37:01.438976Z","iopub.execute_input":"2022-09-01T06:37:01.442853Z","iopub.status.idle":"2022-09-01T06:37:01.781481Z","shell.execute_reply.started":"2022-09-01T06:37:01.442810Z","shell.execute_reply":"2022-09-01T06:37:01.780580Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_df[\"race_number\"].value_counts()","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:37:01.785969Z","iopub.execute_input":"2022-09-01T06:37:01.788138Z","iopub.status.idle":"2022-09-01T06:37:01.800633Z","shell.execute_reply.started":"2022-09-01T06:37:01.788090Z","shell.execute_reply":"2022-09-01T06:37:01.799544Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's look at the number of races held by race number. Races numbered 1 through 8 have been held 210 times, the ninth race has been held 163 times, the tenth race 89 The 10th race was held 89 times, the 11th race 29 times, the 12th race 9 times, and the 13th race 3 times.\n\n","metadata":{}},{"cell_type":"markdown","source":"distance_id","metadata":{}},{"cell_type":"code","source":"plot_hist(race_df,'distance_id','lightblue')","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:37:01.805414Z","iopub.execute_input":"2022-09-01T06:37:01.807519Z","iopub.status.idle":"2022-09-01T06:37:02.154749Z","shell.execute_reply.started":"2022-09-01T06:37:01.807484Z","shell.execute_reply":"2022-09-01T06:37:02.152820Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Check distance_id note.\n\n> Distance of the race in furlongs passed as an integer. Example - 600 would be 6 furlongs.","metadata":{}},{"cell_type":"code","source":"race_df[\"less_than_2mile\"] = race_df[\"distance_id\"].apply(lambda x : 1 if x >= 1200 else 0)\nplot_bar(race_df, \"less_than_2mile\", \"track_id\", \"lightblue\")","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:37:02.155991Z","iopub.execute_input":"2022-09-01T06:37:02.156357Z","iopub.status.idle":"2022-09-01T06:37:02.440224Z","shell.execute_reply.started":"2022-09-01T06:37:02.156329Z","shell.execute_reply":"2022-09-01T06:37:02.439182Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Races under 2 miles have been held 1979 times, accounting for about 99% of the total; races over 2 miles have been held 21 times, accounting for about 1% of the total","metadata":{}},{"cell_type":"markdown","source":"post_time","metadata":{}},{"cell_type":"code","source":"plot_hist(race_df,'post_time','lightblue')","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:37:02.441746Z","iopub.execute_input":"2022-09-01T06:37:02.442090Z","iopub.status.idle":"2022-09-01T06:37:02.711650Z","shell.execute_reply.started":"2022-09-01T06:37:02.442056Z","shell.execute_reply":"2022-09-01T06:37:02.710767Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The race seems to be held at a fixed time.\n\n","metadata":{}},{"cell_type":"markdown","source":"purse","metadata":{}},{"cell_type":"code","source":"plt.figure(figsize=[12,4]);\nsns.histplot(data=race_df,x='purse',color='lightblue');\nplt.ylabel('Frequency');\nplt.title('Distribution of purse');\nplt.vlines(x=race_df['purse'].quantile(0.9),ymin=0,ymax=250,color='red');","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:37:02.714127Z","iopub.execute_input":"2022-09-01T06:37:02.715192Z","iopub.status.idle":"2022-09-01T06:37:03.330150Z","shell.execute_reply.started":"2022-09-01T06:37:02.715150Z","shell.execute_reply":"2022-09-01T06:37:03.329168Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"90% of the race purse (in US dollars) is less than $100,000\n\n","metadata":{}},{"cell_type":"code","source":"race_df['ishigh_purse']= race_df['purse'].apply(lambda x: 1 if x >race_df['purse'].quantile(0.9) else 0)","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:37:03.334943Z","iopub.execute_input":"2022-09-01T06:37:03.335227Z","iopub.status.idle":"2022-09-01T06:37:04.691234Z","shell.execute_reply.started":"2022-09-01T06:37:03.335201Z","shell.execute_reply":"2022-09-01T06:37:04.690296Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"nyra_start_table","metadata":{}},{"cell_type":"code","source":"start_df.info()","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:37:24.888723Z","iopub.execute_input":"2022-09-01T06:37:24.889151Z","iopub.status.idle":"2022-09-01T06:37:24.908879Z","shell.execute_reply.started":"2022-09-01T06:37:24.889100Z","shell.execute_reply":"2022-09-01T06:37:24.907933Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"start_df.head()","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:37:53.379466Z","iopub.execute_input":"2022-09-01T06:37:53.379977Z","iopub.status.idle":"2022-09-01T06:37:53.397213Z","shell.execute_reply.started":"2022-09-01T06:37:53.379944Z","shell.execute_reply":"2022-09-01T06:37:53.396276Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"start_df.nunique()","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:38:13.396936Z","iopub.execute_input":"2022-09-01T06:38:13.397636Z","iopub.status.idle":"2022-09-01T06:38:13.416171Z","shell.execute_reply.started":"2022-09-01T06:38:13.397589Z","shell.execute_reply":"2022-09-01T06:38:13.415151Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cat, num = get_column(start_df)","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:40:13.365230Z","iopub.execute_input":"2022-09-01T06:40:13.366153Z","iopub.status.idle":"2022-09-01T06:40:13.371569Z","shell.execute_reply.started":"2022-09-01T06:40:13.366104Z","shell.execute_reply":"2022-09-01T06:40:13.370202Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cat, num","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:40:22.775123Z","iopub.execute_input":"2022-09-01T06:40:22.775826Z","iopub.status.idle":"2022-09-01T06:40:22.783692Z","shell.execute_reply.started":"2022-09-01T06:40:22.775788Z","shell.execute_reply":"2022-09-01T06:40:22.782317Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Categorical\nprogram_number","metadata":{}},{"cell_type":"code","source":"plot_bar(start_df, \"program_number\", \"track_id\", \"lightblue\")","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:42:06.890677Z","iopub.execute_input":"2022-09-01T06:42:06.891048Z","iopub.status.idle":"2022-09-01T06:42:07.411232Z","shell.execute_reply.started":"2022-09-01T06:42:06.891011Z","shell.execute_reply":"2022-09-01T06:42:07.410296Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"- The number of events seems to decrease as the race number increases.\n- Race number remains the same up to 6 and about 11% of the total.\n","metadata":{}},{"cell_type":"markdown","source":"jockey","metadata":{}},{"cell_type":"code","source":"start_df.shape[0], start_df.shape[0]/len(start_df[\"jockey\"].unique())","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:44:23.363004Z","iopub.execute_input":"2022-09-01T06:44:23.363545Z","iopub.status.idle":"2022-09-01T06:44:23.374400Z","shell.execute_reply.started":"2022-09-01T06:44:23.363501Z","shell.execute_reply":"2022-09-01T06:44:23.373225Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"In all, 14315 horses are entered in all races with duplicates, but there are only 178 jockeys in all. Considering a simple average, each jockey has appeared approximately 83 times. Is this true?","metadata":{}},{"cell_type":"code","source":"# Count the track_id for particular jockey\njockey_df = pd.DataFrame(start_df.groupby(\"jockey\")[\"track_id\"].count())\njockey_df = jockey_df.reset_index()\njockey_df.columns = [\"jockey\", \"Count\"]\n\n# Get the Ratio of Count\njockey_df[\"Ratio(%)\"] = 100 * jockey_df[\"Count\"] / jockey_df[\"Count\"].sum()\njockey_df = jockey_df.sort_values(by = \"Count\", ascending = False)\njockey_df","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:50:59.604676Z","iopub.execute_input":"2022-09-01T06:50:59.605059Z","iopub.status.idle":"2022-09-01T06:50:59.627258Z","shell.execute_reply.started":"2022-09-01T06:50:59.605026Z","shell.execute_reply":"2022-09-01T06:50:59.626298Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize = [16,6]);\nplt.subplot(1,2,1)\nsns.barplot(data = jockey_df.head(30), x = \"jockey\", y = \"Count\", color = \"lightgreen\");\nplt.tight_layout\nplt.xticks(rotation = 90);\n\nplt.subplot(1,2,2)\nsns.barplot(data = jockey_df.head(30), x = \"jockey\", y = \"Ratio(%)\", color = \"lightgreen\");\nplt.xticks(rotation = 90);\nplt.tight_layout;\n\ntemp_df = jockey_df.head(30).T\ntemp_df.columns = temp_df.loc[\"jockey\",:]\ntemp_df = temp_df.drop(index = \"jockey\")\ntemp_df","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:56:12.156227Z","iopub.execute_input":"2022-09-01T06:56:12.156688Z","iopub.status.idle":"2022-09-01T06:56:13.383119Z","shell.execute_reply.started":"2022-09-01T06:56:12.156649Z","shell.execute_reply":"2022-09-01T06:56:13.382156Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"A look at the top jockeys in terms of appearances shows that certain jockeys appear more often than others: Manuel Franco has appeared in 1274 races, about 8% of the total. The jockey with the 30th most appearances is Carlos J. Hernandez, who has appeared in 80 races.","metadata":{}},{"cell_type":"markdown","source":"### Numerical\nweight_carried","metadata":{}},{"cell_type":"code","source":"plot_hist(start_df, \"weight_carried\", \"lightgreen\")","metadata":{"execution":{"iopub.status.busy":"2022-09-01T06:57:29.683350Z","iopub.execute_input":"2022-09-01T06:57:29.684301Z","iopub.status.idle":"2022-09-01T06:57:30.161421Z","shell.execute_reply.started":"2022-09-01T06:57:29.684240Z","shell.execute_reply":"2022-09-01T06:57:30.160279Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The weight carried by horses is mostly less than 130 and rarely more than 140.\n\n","metadata":{}},{"cell_type":"markdown","source":"odds\n> Notice: 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.","metadata":{}},{"cell_type":"code","source":"start_df[\"odds_float\"] = start_df[\"odds\"]/100\nstart_df[\"expectation\"] = 1/start_df[\"odds_float\"]\n","metadata":{"execution":{"iopub.status.busy":"2022-09-01T07:00:08.008117Z","iopub.execute_input":"2022-09-01T07:00:08.009099Z","iopub.status.idle":"2022-09-01T07:00:08.018018Z","shell.execute_reply.started":"2022-09-01T07:00:08.009061Z","shell.execute_reply":"2022-09-01T07:00:08.016973Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_hist(start_df, \"odds_float\", \"lightgreen\")","metadata":{"execution":{"iopub.status.busy":"2022-09-01T07:00:26.741130Z","iopub.execute_input":"2022-09-01T07:00:26.742130Z","iopub.status.idle":"2022-09-01T07:00:27.261132Z","shell.execute_reply.started":"2022-09-01T07:00:26.742093Z","shell.execute_reply":"2022-09-01T07:00:27.260194Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The odds show that they are concentrated around 4x to 5x.\n\n> Note:Odds can be less than 100, and some seem to be 0 in rare cases.","metadata":{}},{"cell_type":"code","source":"start_df[start_df[\"odds\"] < 100].sort_values(\"odds\")","metadata":{"execution":{"iopub.status.busy":"2022-09-01T07:02:27.670281Z","iopub.execute_input":"2022-09-01T07:02:27.671281Z","iopub.status.idle":"2022-09-01T07:02:27.694184Z","shell.execute_reply.started":"2022-09-01T07:02:27.671223Z","shell.execute_reply":"2022-09-01T07:02:27.693224Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"nyra_tracking_table","metadata":{}},{"cell_type":"markdown","source":"- nyra_tracking_table.csv\n  - track_id\n    - 3 character id for the track the race took place at. AQU -Aqueduct, BEL - Belmont, SAR - Saratoga.\n  - race_date\n    - date the race took place. YYYY-MM-DD.\n    - There are 217 dates on which the race took place.\n  - race_number\n    - Number of the race. Passed as 3 characters but can be cast or converted to int for this data set.\n    - Up to 13 races are registered.\n  - program_number\n    - 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\n    - 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    - Information at 264 measurements up to 75%.\n  - latitude\n    - The latitude of the horse in the race passed as a float.\n  - longitude\n    - The longitude of the horse in the race passed as a float.\n- Data analysis results\n  - There appears to be no missing values.","metadata":{}},{"cell_type":"markdown","source":"### Categorcal\nNote:I omitted analisys for track_id, race_date, program_number","metadata":{}},{"cell_type":"code","source":"get_column(track_df)","metadata":{"execution":{"iopub.status.busy":"2022-09-01T07:07:38.002393Z","iopub.execute_input":"2022-09-01T07:07:38.002967Z","iopub.status.idle":"2022-09-01T07:07:38.011349Z","shell.execute_reply.started":"2022-09-01T07:07:38.002930Z","shell.execute_reply":"2022-09-01T07:07:38.010401Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"track_df.dtypes","metadata":{"execution":{"iopub.status.busy":"2022-09-01T07:08:02.173108Z","iopub.execute_input":"2022-09-01T07:08:02.173835Z","iopub.status.idle":"2022-09-01T07:08:02.183465Z","shell.execute_reply.started":"2022-09-01T07:08:02.173798Z","shell.execute_reply":"2022-09-01T07:08:02.182195Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Numerical\ntrakus_index","metadata":{}},{"cell_type":"code","source":"track_df.head()","metadata":{"execution":{"iopub.status.busy":"2022-09-01T07:11:13.674938Z","iopub.execute_input":"2022-09-01T07:11:13.675328Z","iopub.status.idle":"2022-09-01T07:11:13.691150Z","shell.execute_reply.started":"2022-09-01T07:11:13.675294Z","shell.execute_reply":"2022-09-01T07:11:13.690106Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.histplot(data = track_df, x = \"trakus_index\", color = \"tab:red\", bins = 50);\nplt.title(\"Distribution of time step\")\nplt.vlines(x = 250, ymin = 0, ymax = 0.35*1000**2)\nplt.vlines(x = 350, ymin = 0, ymax = 0.35*1000**2);\n","metadata":{"execution":{"iopub.status.busy":"2022-09-01T07:11:21.046353Z","iopub.execute_input":"2022-09-01T07:11:21.046895Z","iopub.status.idle":"2022-09-01T07:11:21.957096Z","shell.execute_reply.started":"2022-09-01T07:11:21.046858Z","shell.execute_reply":"2022-09-01T07:11:21.956165Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"trankus_index is number of mesurements. The number of measurements remained almost the same until the 250th measurement. The 350th measurement is about 1/3 of the 250th measurement.","metadata":{}},{"cell_type":"markdown","source":"latitude' and 'longitude'","metadata":{}},{"cell_type":"code","source":"def get_track_id(df,track_id_): \n    temp_df = df[df.track_id==track_id_]\n    temp_df = temp_df.sample(n=int(temp_df.shape[0]/10),random_state=42)\n    return temp_df\n","metadata":{"execution":{"iopub.status.busy":"2022-09-01T07:17:36.904218Z","iopub.execute_input":"2022-09-01T07:17:36.904897Z","iopub.status.idle":"2022-09-01T07:17:36.910827Z","shell.execute_reply.started":"2022-09-01T07:17:36.904859Z","shell.execute_reply":"2022-09-01T07:17:36.909806Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize=[24,6])\n\nplt.subplot(1,3,1)\ntrack_id = 'AQU'\ntemp_df=get_track_id(track_df,track_id)\nsns.scatterplot(data=temp_df,x='latitude',y='longitude');\nplt.title('Course Type of '+str(track_id));\n\nplt.subplot(1,3,2)\ntrack_id = 'BEL'\ntemp_df=get_track_id(track_df,track_id)\nsns.scatterplot(data=temp_df,x='latitude',y='longitude');\nplt.title('Course Type of '+str(track_id));\n\nplt.subplot(1,3,3)\ntrack_id = 'SAR'\ntemp_df=get_track_id(track_df,track_id)\nsns.scatterplot(data=temp_df,x='latitude',y='longitude');\nplt.title('Course Type of '+str(track_id));\n","metadata":{"execution":{"iopub.status.busy":"2022-09-01T07:17:37.873432Z","iopub.execute_input":"2022-09-01T07:17:37.874390Z","iopub.status.idle":"2022-09-01T07:17:41.816728Z","shell.execute_reply.started":"2022-09-01T07:17:37.874342Z","shell.execute_reply":"2022-09-01T07:17:41.815823Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The track of the racetrack is shaped like an oval track with an additional straightaway. Note: It is possible to distinguish whether each 'course_type' has or does not have a non-oval course.","metadata":{}},{"cell_type":"markdown","source":"## More EDA\n#### nyra_2019_complete\nLet's look next at the course types for each racetrack.","metadata":{}},{"cell_type":"code","source":"plt.figure(figsize=[24,6])\n\nplt.subplot(1,3,1)\ntrack_id = 'AQU'\ntemp_df=get_track_id(all_df,track_id)\nsns.scatterplot(data=temp_df,x='latitude',y='longitude',hue='course_type');\nplt.title('Course Type of '+str(track_id));\n\nplt.subplot(1,3,2)\ntrack_id = 'BEL'\ntemp_df=get_track_id(all_df,track_id)\nsns.scatterplot(data=temp_df,x='latitude',y='longitude',hue='course_type');\nplt.title('Course Type of '+str(track_id));\n\nplt.subplot(1,3,3)\ntrack_id = 'SAR'\ntemp_df=get_track_id(all_df,track_id)\nsns.scatterplot(data=temp_df,x='latitude',y='longitude',hue='course_type');\nplt.title('Course Type of '+str(track_id));","metadata":{"execution":{"iopub.status.busy":"2022-09-01T07:18:49.764205Z","iopub.execute_input":"2022-09-01T07:18:49.764581Z","iopub.status.idle":"2022-09-01T07:19:14.632372Z","shell.execute_reply.started":"2022-09-01T07:18:49.764550Z","shell.execute_reply":"2022-09-01T07:19:14.631545Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Discussion Topic:Can we classify the inner terf at Aqueduct racetrack as a T? Outer terf at Aqueduct is a dirt course and should be treated separately. However, isn't the turf course about the size of the inner terf at Saratoga?Is there a reason you didn't treat it as inner turf?\n\nAqueduct_Racetrack:Wikipedia\n\n> The track itself has three courses. The main track (dirt) has a circumference of 1+1⁄8 miles (1.8 km). Inside of the main track are two courses: the 1 mile (1.6 km) Main Turf Course, and the Inner Turf Course measuring 7.065 furlongs (1.421 km). The track has seating capacity of 17,000 and total capacity of 40,000.[3] The facility houses the New York Racing Association's headquarters.\n\nAqueduct Racetrack:GAMBLING SITES.COM\n\n> The main course is a flat dirt track that measures 1 1/8-miles. Instead of just having one inner course, there are actually two tracks that are featured inside the outside oval at Aqueduct. There is a 1-mile inner dirt course that encloses a smaller, 7/8-mile-long turf course.\n\nSaratoga Race Course\n\n> As is the case with the other two tracks operated by the New York Racing Association – Aqueduct and Belmont Park – there are three separate tracks in the main course at Saratoga Race Course:\n - a main (dirt) track, which, like that at Aqueduct, has a 1+1⁄8-mile (9-furlong or 1,811 m) circumference;\n    - chute for 7-furlong events\n    - chute for 1 mile events known as the Wilson Chute. Originally was in use until 1972 when an additional parking lot was created. * The chute was used in 1992 for one season. The Wilson Chute was resurrected in 2022.[20]\n - a 1-mile (8-furlong) turf track, known officially as the Mellon Turf Course in honor of the Mellon family, whose members include prominent thoroughbred owner/breeder Paul Mellon and his father Andrew Mellon, a former United States Treasury Secretary; and\n - an inner turf track, the circumference of which is 7 furlongs (1,408 m).[21]","metadata":{}},{"cell_type":"markdown","source":"## Location information is not reliable.\nThe latitude and longitude information used to view the course was sampled. The actual data contains errors, so let's take a look at what's inside.","metadata":{}},{"cell_type":"code","source":"def getrace(df,track_id_,race_date_,race_number_,program_num_):\n    horse_df = df[(df['track_id']==track_id_)\n         &(df['race_date']==race_date_)\n         &(df['race_number']==race_number_)\n         &(df['program_number']==program_num_)]\n    return horse_df\n\nplt.figure(figsize=[16,4])\nplt.subplot(1,2,1)\nsns.scatterplot(data=getrace(track_df,'BEL','2019-10-02',5,'2  '),x='latitude',y='longitude');\nplt.title('Missing Position');\n\nplt.subplot(1,2,2)\nsns.scatterplot(data=getrace(track_df,'AQU','2019-12-07',3,'5  '),x='latitude',y='longitude');\nplt.title('Abnomal Position');","metadata":{"execution":{"iopub.status.busy":"2022-09-01T07:28:40.943140Z","iopub.execute_input":"2022-09-01T07:28:40.944199Z","iopub.status.idle":"2022-09-01T07:28:43.151131Z","shell.execute_reply.started":"2022-09-01T07:28:40.944160Z","shell.execute_reply":"2022-09-01T07:28:43.150207Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}