{"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":"---\n# Overview of Analysis","metadata":{}},{"cell_type":"markdown","source":"## Background\nOur sport is currently investing significant money in collecting far more precise tracking data in the hopes of improving equine welfare. Along with stride data, we can now collect measures for heart rate, EKG, longitudinal movement, dorsal/ventral movement, medial/lateral deviation, total power and total landing vibration. However, we do not have analysts with the appropriate expertise to help decipher these data sets.\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?<br>\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 re\nimport warnings\n\n# for analyze data\nimport numpy as np \nimport pandas as pd \nimport seaborn as sns\nimport matplotlib.pyplot as plt\n\nwarnings.simplefilter('ignore')\n\n!pip install pymap3d\nimport pymap3d as pm","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:18.323686Z","iopub.execute_input":"2022-08-28T02:09:18.324155Z","iopub.status.idle":"2022-08-28T02:09:32.873957Z","shell.execute_reply.started":"2022-08-28T02:09:18.324065Z","shell.execute_reply":"2022-08-28T02:09:32.872770Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"---\n# Extract ","metadata":{}},{"cell_type":"code","source":"ALL_PATH = '/kaggle/input/big-data-derby-2022/nyra_2019_complete.csv'\nSTART_PATH = '/kaggle/input/big-data-derby-2022/nyra_start_table.csv'\nRACE_PATH = '/kaggle/input/big-data-derby-2022/nyra_race_table.csv'\nTRACK_PATH = '/kaggle/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(RACE_PATH)\ntrack_df = pd.read_csv(TRACK_PATH)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:32.875885Z","iopub.execute_input":"2022-08-28T02:09:32.876680Z","iopub.status.idle":"2022-08-28T02:09:50.841854Z","shell.execute_reply.started":"2022-08-28T02:09:32.876637Z","shell.execute_reply":"2022-08-28T02:09:50.840347Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(f\"{'-'*30}Show Row and Column Number of Each Matrix{'-'*30})\")\nprint(f\"nyra_race_table Row:{race_df.shape[0]} Column:{race_df.shape[1]}\")\nprint(f\"nyra_start_table Row:{start_df.shape[0]} Column:{start_df.shape[1]}\")\nprint(f\"nyra_tracking_table Row:{track_df.shape[0]} Column:{track_df.shape[1]}\")","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:50.843829Z","iopub.execute_input":"2022-08-28T02:09:50.844151Z","iopub.status.idle":"2022-08-28T02:09:50.851047Z","shell.execute_reply.started":"2022-08-28T02:09:50.844122Z","shell.execute_reply":"2022-08-28T02:09:50.849779Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's briefly analyze the matrix in order of decreasing size.","metadata":{}},{"cell_type":"markdown","source":"# Basic EDA","metadata":{}},{"cell_type":"markdown","source":"## nyra_race_table","metadata":{}},{"cell_type":"markdown","source":"* 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* race_number \n    - Number of the race. Passed as 3 characters but can be cast or converted to int for this data set.\n* distance_id \n    - Distance of the race in furlongs passed as an integer. Example - 600 would be 6 furlongs.\n* course_type \n    - 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 \n    - 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 \n    - Distance in feet of the gate to the start of the race passed as an integer.\n* race_type \n    - 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 \n    - Purse in US dollars of the race passed as an money with two decimal places.\n* post_time \n    - Time of day the race began passed as 5 character. Example - 01220 would be 12:20.","metadata":{"execution":{"iopub.status.busy":"2022-08-20T23:05:06.734311Z","iopub.execute_input":"2022-08-20T23:05:06.734779Z","iopub.status.idle":"2022-08-20T23:05:06.746861Z","shell.execute_reply.started":"2022-08-20T23:05:06.734743Z","shell.execute_reply":"2022-08-20T23:05:06.745521Z"}}},{"cell_type":"code","source":"race_df.info()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:50.853611Z","iopub.execute_input":"2022-08-28T02:09:50.854518Z","iopub.status.idle":"2022-08-28T02:09:50.885947Z","shell.execute_reply.started":"2022-08-28T02:09:50.854464Z","shell.execute_reply":"2022-08-28T02:09:50.884954Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"There appears to be no missing values.","metadata":{}},{"cell_type":"markdown","source":"### Cardinality","metadata":{}},{"cell_type":"code","source":"race_df.nunique().plot.bar();","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:50.887307Z","iopub.execute_input":"2022-08-28T02:09:50.887677Z","iopub.status.idle":"2022-08-28T02:09:51.105219Z","shell.execute_reply.started":"2022-08-28T02:09:50.887642Z","shell.execute_reply":"2022-08-28T02:09:51.103618Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's look at the columns with high cardinarity.<br>\nHigh Cardinarity Column Name:`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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:51.106729Z","iopub.execute_input":"2022-08-28T02:09:51.107026Z","iopub.status.idle":"2022-08-28T02:09:51.112689Z","shell.execute_reply.started":"2022-08-28T02:09:51.107001Z","shell.execute_reply":"2022-08-28T02:09:51.111958Z"},"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)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:51.113865Z","iopub.execute_input":"2022-08-28T02:09:51.114389Z","iopub.status.idle":"2022-08-28T02:09:51.128543Z","shell.execute_reply.started":"2022-08-28T02:09:51.114358Z","shell.execute_reply":"2022-08-28T02:09:51.127229Z"},"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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:51.129898Z","iopub.execute_input":"2022-08-28T02:09:51.130245Z","iopub.status.idle":"2022-08-28T02:09:51.393903Z","shell.execute_reply.started":"2022-08-28T02:09:51.130214Z","shell.execute_reply":"2022-08-28T02:09:51.392753Z"},"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":"unique_race_df=race_df.copy()\nunique_race_df['race_date']=pd.to_datetime(unique_race_df['race_date'])\nunique_race_df['race_month']=unique_race_df['race_date'].dt.month\nunique_race_df.groupby(['track_id','race_month'])['race_number'].count()\npd.crosstab(unique_race_df['track_id'],unique_race_df['race_month']).style.background_gradient(cmap='Blues')","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:51.395260Z","iopub.execute_input":"2022-08-28T02:09:51.395593Z","iopub.status.idle":"2022-08-28T02:09:51.637501Z","shell.execute_reply.started":"2022-08-28T02:09:51.395563Z","shell.execute_reply":"2022-08-28T02:09:51.636508Z"},"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.\nIn AQU, horse races are held from November to April.\nBEL holds horse races from April to July and September and October\nIn 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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:51.640623Z","iopub.execute_input":"2022-08-28T02:09:51.640924Z","iopub.status.idle":"2022-08-28T02:09:51.896677Z","shell.execute_reply.started":"2022-08-28T02:09:51.640898Z","shell.execute_reply":"2022-08-28T02:09:51.895565Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Dirt races have been held 1,351 times, accounting for more than 67% of the total. \nTurf and inner turf races have been held more than 200 times, accounting for more than 10% of the total, while outer turf races have been held 67 times, accounting for less than 4% of the total.\nHadle has been held 9 times, accounting for less than 0.5% of the total.\n","metadata":{"execution":{"iopub.status.busy":"2022-08-21T00:25:35.358412Z","iopub.execute_input":"2022-08-21T00:25:35.359995Z","iopub.status.idle":"2022-08-21T00:25:35.368610Z","shell.execute_reply.started":"2022-08-21T00:25:35.359922Z","shell.execute_reply":"2022-08-21T00:25:35.366438Z"}}},{"cell_type":"markdown","source":"### track_condition","metadata":{}},{"cell_type":"markdown","source":"Let'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\nReference[Wikipedia](https://en.wikipedia.org/wiki/Going_(horse_racing))<br>","metadata":{}},{"cell_type":"markdown","source":"For dirt tracks the track conditions are:<br>\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","metadata":{}},{"cell_type":"code","source":"plot_bar(race_df[race_df['course_type']=='D'],'track_condition','track_id','lightblue')","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:51.898004Z","iopub.execute_input":"2022-08-28T02:09:51.898392Z","iopub.status.idle":"2022-08-28T02:09:52.132132Z","shell.execute_reply.started":"2022-08-28T02:09:51.898358Z","shell.execute_reply":"2022-08-28T02:09:52.130762Z"},"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.","metadata":{}},{"cell_type":"markdown","source":"For turf tracks, the track conditions are:<br>\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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:52.133628Z","iopub.execute_input":"2022-08-28T02:09:52.134049Z","iopub.status.idle":"2022-08-28T02:09:52.369332Z","shell.execute_reply.started":"2022-08-28T02:09:52.134008Z","shell.execute_reply":"2022-08-28T02:09:52.367983Z"},"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.\n","metadata":{}},{"cell_type":"markdown","source":"### race_type","metadata":{}},{"cell_type":"code","source":"plot_bar(race_df,'race_type','track_id','lightblue')","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:52.370750Z","iopub.execute_input":"2022-08-28T02:09:52.371274Z","iopub.status.idle":"2022-08-28T02:09:52.695755Z","shell.execute_reply.started":"2022-08-28T02:09:52.371241Z","shell.execute_reply":"2022-08-28T02:09:52.693832Z"},"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":"---\n### Numeric","metadata":{}},{"cell_type":"markdown","source":"### race_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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:52.697477Z","iopub.execute_input":"2022-08-28T02:09:52.697839Z","iopub.status.idle":"2022-08-28T02:09:52.704412Z","shell.execute_reply.started":"2022-08-28T02:09:52.697805Z","shell.execute_reply":"2022-08-28T02:09:52.703370Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_hist(race_df,'race_number','lightblue')","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:52.705489Z","iopub.execute_input":"2022-08-28T02:09:52.706072Z","iopub.status.idle":"2022-08-28T02:09:52.903170Z","shell.execute_reply.started":"2022-08-28T02:09:52.706042Z","shell.execute_reply":"2022-08-28T02:09:52.902169Z"},"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.","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-08-28T02:09:52.904515Z","iopub.execute_input":"2022-08-28T02:09:52.905181Z","iopub.status.idle":"2022-08-28T02:09:53.148083Z","shell.execute_reply.started":"2022-08-28T02:09:52.905151Z","shell.execute_reply":"2022-08-28T02:09:53.147081Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Check `distance_id` note.\n> Distance of the race in furlongs passed as an integer. Example - 600 would be 6 furlongs.\n\n","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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:53.149340Z","iopub.execute_input":"2022-08-28T02:09:53.149646Z","iopub.status.idle":"2022-08-28T02:09:53.366753Z","shell.execute_reply.started":"2022-08-28T02:09:53.149618Z","shell.execute_reply":"2022-08-28T02:09:53.365664Z"},"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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:53.369222Z","iopub.execute_input":"2022-08-28T02:09:53.370184Z","iopub.status.idle":"2022-08-28T02:09:53.575017Z","shell.execute_reply.started":"2022-08-28T02:09:53.370156Z","shell.execute_reply":"2022-08-28T02:09:53.574061Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The race seems to be held at a fixed time.","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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:53.578059Z","iopub.execute_input":"2022-08-28T02:09:53.578379Z","iopub.status.idle":"2022-08-28T02:09:54.074542Z","shell.execute_reply.started":"2022-08-28T02:09:53.578351Z","shell.execute_reply":"2022-08-28T02:09:54.073089Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"90% of the race purse (in US dollars) is less than $100,000","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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:54.076125Z","iopub.execute_input":"2022-08-28T02:09:54.076486Z","iopub.status.idle":"2022-08-28T02:09:55.201571Z","shell.execute_reply.started":"2022-08-28T02:09:54.076455Z","shell.execute_reply":"2022-08-28T02:09:55.199770Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n\n---\n\n## nyra_start_table","metadata":{}},{"cell_type":"code","source":"start_df.info()","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:55.203244Z","iopub.execute_input":"2022-08-28T02:09:55.203624Z","iopub.status.idle":"2022-08-28T02:09:55.217769Z","shell.execute_reply.started":"2022-08-28T02:09:55.203594Z","shell.execute_reply":"2022-08-28T02:09:55.216821Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"start_df.nunique()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:55.219746Z","iopub.execute_input":"2022-08-28T02:09:55.220840Z","iopub.status.idle":"2022-08-28T02:09:55.239542Z","shell.execute_reply.started":"2022-08-28T02:09:55.220794Z","shell.execute_reply":"2022-08-28T02:09:55.238194Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n\n>Notice:\n>* `jockey` columns is high cardinarity\n>* I omitted the analisys for key column to aggregate`track_id`,`race_date`","metadata":{}},{"cell_type":"markdown","source":"### Categorical ","metadata":{}},{"cell_type":"code","source":"cat,num=get_column(start_df)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:55.241215Z","iopub.execute_input":"2022-08-28T02:09:55.241666Z","iopub.status.idle":"2022-08-28T02:09:55.246454Z","shell.execute_reply.started":"2022-08-28T02:09:55.241626Z","shell.execute_reply":"2022-08-28T02:09:55.245452Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### program_number","metadata":{}},{"cell_type":"code","source":"plot_bar(start_df,'program_number','track_id','lightgreen')","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:55.247595Z","iopub.execute_input":"2022-08-28T02:09:55.247891Z","iopub.status.idle":"2022-08-28T02:09:55.699200Z","shell.execute_reply.started":"2022-08-28T02:09:55.247865Z","shell.execute_reply":"2022-08-28T02:09:55.697951Z"},"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":{"execution":{"iopub.status.busy":"2022-08-21T03:36:05.971761Z","iopub.execute_input":"2022-08-21T03:36:05.972565Z","iopub.status.idle":"2022-08-21T03:36:05.980589Z","shell.execute_reply.started":"2022-08-21T03:36:05.972525Z","shell.execute_reply":"2022-08-21T03:36:05.978447Z"}}},{"cell_type":"markdown","source":"### jockey","metadata":{}},{"cell_type":"code","source":"start_df.shape[0],start_df.shape[0]/len(start_df['jockey'].unique())","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:55.701041Z","iopub.execute_input":"2022-08-28T02:09:55.701510Z","iopub.status.idle":"2022-08-28T02:09:55.710143Z","shell.execute_reply.started":"2022-08-28T02:09:55.701470Z","shell.execute_reply":"2022-08-28T02:09:55.709264Z"},"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":"jockey_df = pd.DataFrame(start_df.groupby('jockey')['track_id'].count())\njockey_df = jockey_df.reset_index()\njockey_df.columns =['jockey','Count']\njockey_df['Ratio(%)'] =100 * jockey_df['Count']/jockey_df['Count'].sum()\njockey_df = jockey_df.sort_values(by='Count',ascending=False)\n\nplt.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);\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')","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:55.711426Z","iopub.execute_input":"2022-08-28T02:09:55.711751Z","iopub.status.idle":"2022-08-28T02:09:56.525686Z","shell.execute_reply.started":"2022-08-28T02:09:55.711720Z","shell.execute_reply":"2022-08-28T02:09:56.524738Z"},"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","metadata":{}},{"cell_type":"markdown","source":"### weight_carried","metadata":{}},{"cell_type":"code","source":"plot_hist(start_df,'weight_carried','lightgreen')","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:56.527072Z","iopub.execute_input":"2022-08-28T02:09:56.527459Z","iopub.status.idle":"2022-08-28T02:09:56.898217Z","shell.execute_reply.started":"2022-08-28T02:09:56.527424Z","shell.execute_reply":"2022-08-28T02:09:56.897342Z"},"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.","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']","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:56.904275Z","iopub.execute_input":"2022-08-28T02:09:56.904828Z","iopub.status.idle":"2022-08-28T02:09:56.912194Z","shell.execute_reply.started":"2022-08-28T02:09:56.904799Z","shell.execute_reply":"2022-08-28T02:09:56.911175Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_hist(start_df,'odds_float','lightgreen')","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:56.913347Z","iopub.execute_input":"2022-08-28T02:09:56.913632Z","iopub.status.idle":"2022-08-28T02:09:57.334833Z","shell.execute_reply.started":"2022-08-28T02:09:56.913608Z","shell.execute_reply":"2022-08-28T02:09:57.333678Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The odds show that they are concentrated around 4x to 5x.\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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:57.336317Z","iopub.execute_input":"2022-08-28T02:09:57.336654Z","iopub.status.idle":"2022-08-28T02:09:57.357898Z","shell.execute_reply.started":"2022-08-28T02:09:57.336625Z","shell.execute_reply":"2022-08-28T02:09:57.357170Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n## nyra_tracking_table","metadata":{}},{"cell_type":"code","source":"#track_df.info()\n#track_df.describe(include='all').style.background_gradient(cmap='Blues')","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:57.359102Z","iopub.execute_input":"2022-08-28T02:09:57.359406Z","iopub.status.idle":"2022-08-28T02:09:57.363985Z","shell.execute_reply.started":"2022-08-28T02:09:57.359380Z","shell.execute_reply":"2022-08-28T02:09:57.363235Z"},"trusted":true},"execution_count":null,"outputs":[]},{"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<br>\n* Data analysis results\n    * There appears to be no missing values.","metadata":{"execution":{"iopub.status.busy":"2022-08-20T08:29:45.082415Z","iopub.execute_input":"2022-08-20T08:29:45.082954Z","iopub.status.idle":"2022-08-20T08:29:45.095437Z","shell.execute_reply.started":"2022-08-20T08:29:45.082906Z","shell.execute_reply":"2022-08-20T08:29:45.093332Z"}}},{"cell_type":"markdown","source":"---","metadata":{}},{"cell_type":"markdown","source":"## Categorcal\n> Note:I omitted analisys for `track_id`, `race_date`, `program_number`","metadata":{}},{"cell_type":"code","source":"get_column(track_df)","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:57.365686Z","iopub.execute_input":"2022-08-28T02:09:57.366151Z","iopub.status.idle":"2022-08-28T02:09:57.378894Z","shell.execute_reply.started":"2022-08-28T02:09:57.366109Z","shell.execute_reply":"2022-08-28T02:09:57.378181Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Numerical","metadata":{}},{"cell_type":"markdown","source":"### trakus_index","metadata":{}},{"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":{"_kg_hide-output":false,"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:57.379974Z","iopub.execute_input":"2022-08-28T02:09:57.380257Z","iopub.status.idle":"2022-08-28T02:09:57.962190Z","shell.execute_reply.started":"2022-08-28T02:09:57.380231Z","shell.execute_reply":"2022-08-28T02:09:57.961232Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"`trankus_index` is number of mesurements.\nThe number of measurements remained almost the same until the 250th measurement.\nThe 350th measurement is about 1/3 of the 250th measurement.\n","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","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:09:57.964251Z","iopub.execute_input":"2022-08-28T02:09:57.964689Z","iopub.status.idle":"2022-08-28T02:09:57.971128Z","shell.execute_reply.started":"2022-08-28T02:09:57.964655Z","shell.execute_reply":"2022-08-28T02:09:57.969673Z"},"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));","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:14:52.733087Z","iopub.execute_input":"2022-08-28T02:14:52.733879Z","iopub.status.idle":"2022-08-28T02:14:55.793056Z","shell.execute_reply.started":"2022-08-28T02:14:52.733843Z","shell.execute_reply":"2022-08-28T02:14:55.792153Z"},"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.\nNote: It is possible to distinguish whether each 'course_type' has or does not have a non-oval course.\n\n# More EDA\n---\n## nyra_2019_complete\n\nLet's look next at the course types for each racetrack.\n","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-08-28T02:52:28.648200Z","iopub.execute_input":"2022-08-28T02:52:28.648719Z","iopub.status.idle":"2022-08-28T02:52:42.093858Z","shell.execute_reply.started":"2022-08-28T02:52:28.648683Z","shell.execute_reply":"2022-08-28T02:52:42.092615Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Discussion Topic**:Can we classify the inner terf at Aqueduct racetrack as a `T`?\nOuter 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\n[Aqueduct_Racetrack:Wikipedia](https://en.wikipedia.org/wiki/Aqueduct_Racetrack)\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.<br>\n\n[Aqueduct Racetrack:GAMBLING SITES.COM](https://www.gamblingsites.com/horse-racing/tracks-courses/aqueduct-racetrack/)\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**.<br>\n\n[Saratoga Race Course](https://en.wikipedia.org/wiki/Saratoga_Race_Course)\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]\n","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":{"execution":{"iopub.status.busy":"2022-08-22T09:44:43.902978Z","iopub.execute_input":"2022-08-22T09:44:43.903356Z","iopub.status.idle":"2022-08-22T09:44:43.938253Z","shell.execute_reply.started":"2022-08-22T09:44:43.903322Z","shell.execute_reply":"2022-08-22T09:44:43.936014Z"}}},{"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');\n","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:22:43.853938Z","iopub.execute_input":"2022-08-28T02:22:43.854400Z","iopub.status.idle":"2022-08-28T02:22:45.929046Z","shell.execute_reply.started":"2022-08-28T02:22:43.854362Z","shell.execute_reply":"2022-08-28T02:22:45.927970Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"---\n# Q: Can we evaluate a jockey's skills in 360 degrees?<br>\n\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    * Will the fastest horse win? If it wins, what course does it take?\n    * Is the jockey able to execute a drafting strategy for each course?\n* From viewpoint of **Ticket Buyer**\n    * How well is the jockey supported by the ticket buyers? \n---","metadata":{}},{"cell_type":"markdown","source":"","metadata":{}},{"cell_type":"markdown","source":"# Does jockey bring out the horse's ability for the course? \n","metadata":{}},{"cell_type":"markdown","source":"## Getting Velocity and Accelation\n\nFirst, let's get the velocity and acceleration from the change in position information. Then, obtain one-second moving averages for the velocity and acceleration. Finally, obtain a summary statistic of the moving average of the horse's velocity and acceleration for each race. The summary statistics are mean, minimum, maximum, standard deviation, and skewness.\n\nReference: [obtain-time-course-of-speed-during-a-race](https://www.kaggle.com/code/roniheka/obtain-time-course-of-speed-during-a-race)\nThank you for shareing!","metadata":{}},{"cell_type":"code","source":"program_num_dict={'5  ':5, '1  ':1, '2  ':2, '3  ':3, '4  ':4, '6  ':6, '7  ':7, '8  ':8, '1A ':17,\n       '10 ':10, '9  ':9, '11 ':11, '12 ':12, '13 ':13, '14 ':14, '2B ':20, '16 ':16, '15 ':15,'3X ':18, '1X ':19}\ntrack_df.program_number = track_df.program_number.apply(lambda x: program_num_dict[x])","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:10:10.079386Z","iopub.status.idle":"2022-08-28T02:10:10.079859Z","shell.execute_reply.started":"2022-08-28T02:10:10.079703Z","shell.execute_reply":"2022-08-28T02:10:10.079720Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n","metadata":{}},{"cell_type":"code","source":"def get_speed(df,track_id_,debug=False):\n    track_id = track_id_\n    target_tracking = df.query(\"track_id == @track_id\").sort_values(\"trakus_index\")[['race_date','race_number']].drop_duplicates()\n    process_lst = [[target_tracking.at[idx,'race_date'],target_tracking.at[idx,'race_number']] \n                   for idx in target_tracking.index.values]\n    if debug==True:\n        process_lst = process_lst[:2]\n        print(len(process_lst))\n    \n    temp_df= pd.DataFrame()\n    for idx ,process in enumerate(process_lst): \n        if debug==True:\n            print(process)\n        \n        race_date = process[0]\n        race_number = process[1]\n        target_tracking = df.query(\"track_id == @track_id & race_date == @race_date & race_number == @race_number\").sort_values(\"trakus_index\")\n\n        dtime = 0.25 #0.25 second per trakus_index according to the data description.\n        #Elevations obtained from google earth\n        if track_id == \"AQU\":\n            elevation = 3\n        elif track_id == \"BEL\":\n            elevation = 20\n        elif track_id == \"SAR\":\n            elevation = 93\n\n        target_trackings = []\n        for number, group in target_tracking.groupby(\"program_number\"):\n            ecef = np.array(pm.geodetic2ecef(group[\"latitude\"].values, group[\"longitude\"].values, np.array([elevation]*len(group)))).T\n            #Calculate speed [km/h]\n            v_ecef = np.sqrt(np.sum(np.diff(ecef,axis=0) ** 2, axis = 1)) * 3.6 / dtime\n            \n            # Condition:If more than 100 km, replace with the average speed during the race\n            condition = v_ecef > 100 \n            v_ecef[condition] = np.mean(v_ecef)\n            \n            group[\"time\"] = group[\"trakus_index\"] * dtime -  dtime\n            group[\"speed\"] = np.insert(v_ecef, 0, 0)\n            target_trackings.append(group)\n            #print(\"No.\", number,\" Mean speed : {:.2f} km/h\".format(np.mean(v_ecef)))\n        target_tracking = pd.concat(target_trackings)\n        \n        if idx ==0:\n            temp_df=target_tracking\n        else:\n            temp_df=temp_df.append(target_tracking)\n            \n    return temp_df\n\n\ntrack_id = \"AQU\"\nAQU_v_df = get_speed(track_df,track_id)\n\ntrack_id = \"BEL\"\nBEL_v_df = get_speed(track_df,track_id)\n\ntrack_id = \"SAR\"\nSAR_v_df = get_speed(track_df,track_id)","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2022-08-28T02:10:10.080684Z","iopub.status.idle":"2022-08-28T02:10:10.081455Z","shell.execute_reply.started":"2022-08-28T02:10:10.081253Z","shell.execute_reply":"2022-08-28T02:10:10.081270Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"track_v_df=SAR_v_df\ntrack_v_df=track_v_df.append(AQU_v_df)\ntrack_v_df=track_v_df.append(BEL_v_df)\ntrack_v_df=track_v_df.sort_values(by=['track_id','race_date','race_number','program_number','trakus_index'])\ntrack_v_df['speed_prev']=track_v_df['speed'].diff().fillna(0)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:10:10.082532Z","iopub.status.idle":"2022-08-28T02:10:10.082827Z","shell.execute_reply.started":"2022-08-28T02:10:10.082683Z","shell.execute_reply":"2022-08-28T02:10:10.082698Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"track_v_df['acccelation']= track_v_df['speed_prev']/0.25\n\ntrack_df_movement = track_v_df.groupby(['track_id','race_date','race_number','program_number']).rolling(window=4).agg(\n    {'speed':['mean']\n    ,'acccelation':['mean']})\n\ntrack_df_movement = track_df_movement.reset_index()\ncol_name=[x[0]+x[1] for x in track_df_movement.columns]\ntrack_df_movement.columns = track_df_movement.columns.droplevel(0)\ntrack_df_movement.columns = col_name\ntrack_df_movement=track_df_movement[track_df_movement['speedmean'].isna()==False]\n\ntrack_df_movement = track_df_movement.groupby(['track_id','race_date','race_number','program_number']).agg(\n    {'speedmean':['mean','min','max','std','skew']\n    ,'acccelationmean':['mean','min','max','std','skew']})\n\ntrack_df_movement=track_df_movement.reset_index()\ncol_name=[x[0]+x[1] for x in track_df_movement.columns]\ntrack_df_movement.columns = track_df_movement.columns.droplevel(0)\ntrack_df_movement.columns = col_name\n\nstart_df['program_number'] = start_df['program_number'].apply(lambda x: program_num_dict[str(x)])\ntrack_df_movement = track_df_movement.merge(start_df)\ntrack_df_movement = track_df_movement.merge(race_df)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:10:10.083934Z","iopub.status.idle":"2022-08-28T02:10:10.084229Z","shell.execute_reply.started":"2022-08-28T02:10:10.084084Z","shell.execute_reply":"2022-08-28T02:10:10.084099Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize=[24,18])\nplt.subplot(3,2,1)\nsns.histplot(data=track_df_movement,x='speedmeanmean',hue='track_id');\nplt.subplot(3,2,2)\nsns.histplot(data=track_df_movement,x='speedmeanmin',hue='track_id');\nplt.subplot(3,2,3)\nsns.histplot(data=track_df_movement,x='speedmeanmax',hue='track_id');\nplt.subplot(3,2,4)\nsns.histplot(data=track_df_movement,x='speedmeanstd',hue='track_id');\nplt.subplot(3,2,5)\nsns.histplot(data=track_df_movement,x='speedmeanskew',hue='track_id');","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:10:10.085116Z","iopub.status.idle":"2022-08-28T02:10:10.085431Z","shell.execute_reply.started":"2022-08-28T02:10:10.085253Z","shell.execute_reply":"2022-08-28T02:10:10.085267Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"It was found that different `track_id`s had different average speeds, but other characteristics were not clearly visible.","metadata":{}},{"cell_type":"markdown","source":"## Would it be possible to detect statistically anomalous velocity?","metadata":{}},{"cell_type":"markdown","source":"In these situations, let's use IQR first.\n\n>In descriptive statistics, the interquartile range (IQR) is a measure of statistical dispersion, which is the spread of the data.[1] The IQR may also be called the midspread, middle 50%, fourth spread, or H‑spread. It is defined as the difference between the 75th and 25th percentiles of the data.[2][3][4] To calculate the IQR, the data set is divided into quartiles, or four rank-ordered even parts via linear interpolation.[1] These quartiles are denoted by Q1 (also called the lower quartile), Q2 (the median), and Q3 (also called the upper quartile). The lower quartile corresponds with the 25th percentile and the upper quartile corresponds with the 75th percentile, so IQR = Q3 −  Q1[1]. https://en.wikipedia.org/wiki/Interquartile_range","metadata":{}},{"cell_type":"code","source":"AQU_df=track_df_movement[track_df_movement.track_id==\"AQU\"]\nAQU_df=AQU_df.sample(n=int(AQU_df.shape[0]/10))\n#sns.scatterplot(data=AQU_df,x='latitude',y='longitude',hue='course_type');","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:10:10.087515Z","iopub.status.idle":"2022-08-28T02:10:10.088018Z","shell.execute_reply.started":"2022-08-28T02:10:10.087792Z","shell.execute_reply":"2022-08-28T02:10:10.087815Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"track_df_movement.speedmeanskew.plot.box();\nplt.ylabel('Skew');","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:10:10.089618Z","iopub.status.idle":"2022-08-28T02:10:10.089994Z","shell.execute_reply.started":"2022-08-28T02:10:10.089829Z","shell.execute_reply":"2022-08-28T02:10:10.089845Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"IQR=track_df_movement.speedmeanskew.quantile(0.75)-track_df_movement.speedmeanskew.quantile(0.25)\nup_wisker=1.5 * IQR + track_df_movement.speedmeanskew.quantile(0.75)\nlower_wisker=-1.5 * IQR + track_df_movement.speedmeanskew.quantile(0.25)\ntrack_df_movement_IQR = track_df_movement[(track_df_movement.speedmeanskew>lower_wisker)|(track_df_movement.speedmeanskew<up_wisker)]","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:10:10.091548Z","iopub.status.idle":"2022-08-28T02:10:10.091894Z","shell.execute_reply.started":"2022-08-28T02:10:10.091736Z","shell.execute_reply":"2022-08-28T02:10:10.091751Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's select the horses that are present in the whiskers above and below the boxplot.\n\n**Note:** This analysis is temporary and subject to change.","metadata":{}},{"cell_type":"markdown","source":"## Relationship between average horse speed and odds\n\nThe best part of horse racing would be to guess the winning horse. On the other hand, if you want to get rich, you might want to put your money on a horse with high odds. Horses with low odds are supposed to be easier to win, but do they have a higher average speed than the horses in the race? We have just the data to find out.","metadata":{}},{"cell_type":"code","source":"plt.figure(figsize=[24,18])\nplt.subplot(3,2,1)\nsns.scatterplot(data=track_df_movement_IQR,x='speedmeanmean',y='odds',hue='track_id');\nplt.subplot(3,2,2)\nsns.scatterplot(data=track_df_movement_IQR,x='speedmeanmin',y='odds',hue='track_id');\nplt.subplot(3,2,3)\nsns.scatterplot(data=track_df_movement_IQR,x='speedmeanmax',y='odds',hue='track_id');\nplt.subplot(3,2,4)\nsns.scatterplot(data=track_df_movement_IQR,x='speedmeanstd',y='odds',hue='track_id');\nplt.subplot(3,2,5)\nsns.scatterplot(data=track_df_movement_IQR,x='speedmeanskew',y='odds',hue='track_id');","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:10:10.093042Z","iopub.status.idle":"2022-08-28T02:10:10.093394Z","shell.execute_reply.started":"2022-08-28T02:10:10.093208Z","shell.execute_reply":"2022-08-28T02:10:10.093224Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"---\n# Is the jockey putting too much pressure on the horse during the race?\nThe jockey's strategy and pressure on the horse were reflected in the horse's average speed and average acceleration.\n","metadata":{}},{"cell_type":"code","source":"start_df[(start_df['track_id']=='SAR')\n         &(start_df['race_date']=='2019-08-17')\n         &(start_df['race_number']==6)].sort_values(by='odds').style.background_gradient('Blues')\n\njokey_merge_df = race_df.merge(start_df)\njokey_general_rating_df = jokey_merge_df.groupby(['track_id','course_type','jockey']).agg({'odds':['count','min','mean','max','std','skew']\n                                                                       ,'purse':['count','min','mean','max','std','skew']})\njokey_general_rating_df = jokey_general_rating_df.reset_index()\n\njokey_merge_df = jokey_merge_df.merge(track_v_df)\njokey_move_rating_df = jokey_merge_df.groupby(['track_id','course_type','jockey']).agg({'speed':['count','min','mean','max','std','skew']\n                                                                       ,'acccelation':['count','min','mean','max','std','skew']})\njokey_move_rating_df = jokey_move_rating_df.reset_index()\n\njokey_ttl_rating_df = jokey_general_rating_df.merge(jokey_move_rating_df)\n\njokey_ttl_rating_df['track_course'] =jokey_ttl_rating_df['track_id'] +'_' + jokey_ttl_rating_df['course_type']\n","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:10:10.094734Z","iopub.status.idle":"2022-08-28T02:10:10.095050Z","shell.execute_reply.started":"2022-08-28T02:10:10.094900Z","shell.execute_reply":"2022-08-28T02:10:10.094915Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Jockey's average speed","metadata":{}},{"cell_type":"code","source":"plt.figure(figsize=[16,6])\nsns.histplot(data=jokey_ttl_rating_df.sort_values(by=( 'speed','count')),\n    x=('speed','mean'),hue='track_id');\nplt.title('Effect of Track on Average Speed per Jockey');","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:10:10.096128Z","iopub.status.idle":"2022-08-28T02:10:10.096464Z","shell.execute_reply.started":"2022-08-28T02:10:10.096284Z","shell.execute_reply":"2022-08-28T02:10:10.096314Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The average speed of each jockey is frequently around 67.5 km/h for SAR and BEL, and around 65 km/h for AQU.","metadata":{}},{"cell_type":"markdown","source":"## Jockey's average speed","metadata":{}},{"cell_type":"code","source":"plt.figure(figsize=[16,6])\nsns.histplot(data=jokey_ttl_rating_df.sort_values(by=( 'speed','count')),\n    x=('acccelation','mean'),hue='track_id');\nplt.title('Effect of Track on Average Acccelation per Jockey');","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:10:10.097771Z","iopub.status.idle":"2022-08-28T02:10:10.098100Z","shell.execute_reply.started":"2022-08-28T02:10:10.097932Z","shell.execute_reply":"2022-08-28T02:10:10.097965Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The average acceleration of each jockey does not seem to vary in frequency from racecourse to racecourse, but outliers do exist.","metadata":{}},{"cell_type":"markdown","source":"# Will the fastest horse win? If it wins, what course does it take?\nAnalyzing the course of the horse with the fastest average speed will provide material for making new hypotheses.","metadata":{}},{"cell_type":"code","source":"temp_df = pd.concat([getrace(track_df,'SAR','2019-08-17',6,5),getrace(track_df,'SAR','2019-08-17',6,10)])\n\nstart_df[(start_df['track_id']=='SAR')\n         &(start_df['race_date']=='2019-08-17')\n         &(start_df['race_number']==6)].sort_values(by='odds').style.background_gradient('Blues')","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:10:10.099422Z","iopub.status.idle":"2022-08-28T02:10:10.099731Z","shell.execute_reply.started":"2022-08-28T02:10:10.099586Z","shell.execute_reply":"2022-08-28T02:10:10.099600Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Indicate the race entered by the horse with the highest average speed that appears to be error free.The horse selected is the horse with 'program_number' of 10. The `program_number` of the winning horse is 10 and the `program_number` of the second-placed horse is 5.The odds do not seem very high.","metadata":{}},{"cell_type":"code","source":"plt.figure(figsize=[12,4]);\nsns.scatterplot(data=temp_df,x='latitude',y='longitude',hue='program_number');\nplt.legend(bbox_to_anchor=(1.15, 1));\nplt.title('The trajectories of the first and second horses');\nplt.vlines(x=43.0715,ymin=-73.772,ymax=-73.771,color='red');","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:10:10.100913Z","iopub.status.idle":"2022-08-28T02:10:10.101319Z","shell.execute_reply.started":"2022-08-28T02:10:10.101110Z","shell.execute_reply":"2022-08-28T02:10:10.101152Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's see how the first horse (black dot) won. The red line shows where he finished. Since we don't know where the first-placed horse overtook the second-placed horse, we will try to narrow the scope a little more.","metadata":{}},{"cell_type":"code","source":"plt.figure(figsize=[12,4]);\nsns.scatterplot(data=temp_df[(temp_df.trakus_index>100)&(temp_df.trakus_index<150)],x='latitude',y='longitude',hue='program_number');\nplt.legend(bbox_to_anchor=(1.15, 1));\n#plt.vlines(x=43.0715,ymin=-73.772,ymax=-73.771,color='red');\nplt.title('The trajectories of the first and second horses:Passing');","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:10:10.102445Z","iopub.status.idle":"2022-08-28T02:10:10.102743Z","shell.execute_reply.started":"2022-08-28T02:10:10.102601Z","shell.execute_reply":"2022-08-28T02:10:10.102616Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"At the moment of overtaking, the horse in first place has moved slightly to the outside and is overtaking the horse in second place from the outside. What is this?\nIs this a strategy to use the second-placed horse as a wind shelter during the curve to conserve his strength and then overtake him all at once?","metadata":{}},{"cell_type":"code","source":"plt.figure(figsize=[12,4]);\nsns.scatterplot(data=temp_df[temp_df.trakus_index>230],x='latitude',y='longitude',hue='program_number');\nplt.legend(bbox_to_anchor=(1.15, 1));\nplt.vlines(x=43.0715,ymin=-73.772,ymax=-73.771,color='red');\nplt.title('The trajectories of the first and second horses:Goal');","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-28T02:10:10.103681Z","iopub.status.idle":"2022-08-28T02:10:10.103974Z","shell.execute_reply.started":"2022-08-28T02:10:10.103827Z","shell.execute_reply":"2022-08-28T02:10:10.103840Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Think of the red straight line as the goal position.The horse I had just selected was the wining horse.","metadata":{}},{"cell_type":"markdown","source":"## Ideas for Drafting Hypotheses\nTo evaluate the Drafting strategy, we can analyze the relationship between `program_number` and scenes such as straight, before curve during curve, and after curve. n","metadata":{}},{"cell_type":"markdown","source":"# Under Construction","metadata":{}},{"cell_type":"code","source":"#Let's take a look at the average speed per odds and jockey on the Aqueduct dirt track. There seems to be a slight downward trend. It may be a new way to enjoy horse racing to calculate the average speed of jockeys and horses for each race and each course.","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2022-08-28T02:10:10.105269Z","iopub.status.idle":"2022-08-28T02:10:10.105589Z","shell.execute_reply.started":"2022-08-28T02:10:10.105446Z","shell.execute_reply":"2022-08-28T02:10:10.105461Z"},"trusted":true},"execution_count":null,"outputs":[]}]}