{"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":"**Derby data analysis**\n\n**Table of content**\n* Introduction\n* Import of data\n* Data exlporation/preparations\n* Data analysis\n* Conculsion\n\n**Introduction**\nThe big derby data analysis to analyze horse racing data to improve the health of the horse and strategy of competition. To also help racing horse owners, trainers, and veterinarians better understand how equine performance and welfare fit together. The data does not contain the horse's health data, defecting the main purpose of the competition. This notebook centers on winning rate, looking at the odds(which indicates expected win probability),tracks,race type,course type, tracking conditions and jockey ratings per track.\n\nSo let's begin ","metadata":{}},{"cell_type":"code","source":"#import relevant libraries\nimport pandas as pd\nimport numpy as np\nimport matplotlib.pyplot as plt\nimport seaborn as sns","metadata":{"execution":{"iopub.status.busy":"2022-10-20T16:13:14.702421Z","iopub.execute_input":"2022-10-20T16:13:14.703088Z","iopub.status.idle":"2022-10-20T16:13:15.380816Z","shell.execute_reply.started":"2022-10-20T16:13:14.702975Z","shell.execute_reply":"2022-10-20T16:13:15.379448Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# datasets to dataframe\nrace_df = pd.read_csv('../input/big-data-derby-2022/nyra_race_table.csv', low_memory=False)\nstart_df = pd.read_csv('../input/big-data-derby-2022/nyra_start_table.csv', low_memory=False)\ntracking_df = pd.read_csv('../input/big-data-derby-2022/nyra_tracking_table.csv', low_memory=False)\ncomp_df = pd.read_csv('../input/big-data-derby-2022/nyra_2019_complete.csv', low_memory=False, index_col=False, header=None)\n#insert columns names into the start_df\nstart_df.columns =['track_id', 'race_date', 'race_number', 'program_number', 'weight_carried', 'jockey', 'odds', 'position_at_finish']\nstart_df.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-20T16:13:15.382919Z","iopub.execute_input":"2022-10-20T16:13:15.383847Z","iopub.status.idle":"2022-10-20T16:13:42.859649Z","shell.execute_reply.started":"2022-10-20T16:13:15.383798Z","shell.execute_reply":"2022-10-20T16:13:42.858734Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_df.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-20T16:13:42.860884Z","iopub.execute_input":"2022-10-20T16:13:42.861274Z","iopub.status.idle":"2022-10-20T16:13:42.877361Z","shell.execute_reply.started":"2022-10-20T16:13:42.861242Z","shell.execute_reply":"2022-10-20T16:13:42.876126Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":" **Data exploration/preparation**","metadata":{}},{"cell_type":"code","source":"#merging the start_df and race_df\ndf_all = pd.merge(start_df, race_df, on=['track_id', 'race_date', 'race_number'])\ndf_all.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-20T16:13:42.880474Z","iopub.execute_input":"2022-10-20T16:13:42.880852Z","iopub.status.idle":"2022-10-20T16:13:42.927332Z","shell.execute_reply.started":"2022-10-20T16:13:42.880821Z","shell.execute_reply":"2022-10-20T16:13:42.926275Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#checking for null values\npd.isnull(df_all).sum()","metadata":{"execution":{"iopub.status.busy":"2022-10-20T16:13:42.929085Z","iopub.execute_input":"2022-10-20T16:13:42.929502Z","iopub.status.idle":"2022-10-20T16:13:42.946362Z","shell.execute_reply.started":"2022-10-20T16:13:42.929461Z","shell.execute_reply":"2022-10-20T16:13:42.945301Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#now we begin analysis to see how the expected win is affected\n# separate race winners from other dataset rows\ndf_all['win'] = df_all['position_at_finish'].apply(lambda x: 1 if x==1 else 0)","metadata":{"execution":{"iopub.status.busy":"2022-10-20T16:13:42.947685Z","iopub.execute_input":"2022-10-20T16:13:42.948398Z","iopub.status.idle":"2022-10-20T16:13:42.962509Z","shell.execute_reply.started":"2022-10-20T16:13:42.948363Z","shell.execute_reply":"2022-10-20T16:13:42.961289Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Data analysis**\n\nI am going to make a correlation matrix to see how the variables in the relate to each other.","metadata":{}},{"cell_type":"code","source":"df_all['odds_n'] = df_all['odds'] / 100\n#checking tho corr matrix of the odds to the finished position\ndf_all.corr()","metadata":{"execution":{"iopub.status.busy":"2022-10-20T16:13:42.964009Z","iopub.execute_input":"2022-10-20T16:13:42.964341Z","iopub.status.idle":"2022-10-20T16:13:42.993203Z","shell.execute_reply.started":"2022-10-20T16:13:42.964312Z","shell.execute_reply":"2022-10-20T16:13:42.991944Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"as we can see odds and postion at finish has the highest positive correlation\nlets plot a regression plot to visualize their relationship.","metadata":{}},{"cell_type":"code","source":"plt.figure(figsize=(10,6))\nsns.regplot(data = df_all, y = 'position_at_finish', x = 'odds_n', color = 'c').set(title = \"Regression plot: final_place to odds\")","metadata":{"execution":{"iopub.status.busy":"2022-10-20T16:13:42.994634Z","iopub.execute_input":"2022-10-20T16:13:42.995053Z","iopub.status.idle":"2022-10-20T16:13:44.278208Z","shell.execute_reply.started":"2022-10-20T16:13:42.995018Z","shell.execute_reply":"2022-10-20T16:13:44.276982Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The expected win","metadata":{}},{"cell_type":"code","source":"plt.figure(figsize=(10,6))\nsns.histplot(data=df_all, x=\"odds_n\", hue=\"win\", multiple=\"stack\")","metadata":{"execution":{"iopub.status.busy":"2022-10-20T16:13:44.280188Z","iopub.execute_input":"2022-10-20T16:13:44.280659Z","iopub.status.idle":"2022-10-20T16:13:45.337017Z","shell.execute_reply.started":"2022-10-20T16:13:44.280614Z","shell.execute_reply":"2022-10-20T16:13:45.335916Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's see which track had the highest number of wins","metadata":{}},{"cell_type":"code","source":"plt.figure(figsize=(10,6))\nsns.histplot(data=df_all, x=\"track_id\", hue=\"win\", multiple=\"stack\")","metadata":{"execution":{"iopub.status.busy":"2022-10-20T16:13:45.341508Z","iopub.execute_input":"2022-10-20T16:13:45.341856Z","iopub.status.idle":"2022-10-20T16:13:45.602188Z","shell.execute_reply.started":"2022-10-20T16:13:45.341823Z","shell.execute_reply":"2022-10-20T16:13:45.600785Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The Aqueduct track had more races and more wins, followed by Belmont than Saratoga.","metadata":{}},{"cell_type":"code","source":"# seperating the dataset to focus on each track\ndf_bel = df_all[df_all['track_id'] == \"BEL\"]\ndf_sar = df_all[df_all['track_id'] == \"SAR\"]\ndf_aqu = df_all[df_all['track_id'] == \"AQU\"]\n\ndf_aqu.head()\n","metadata":{"execution":{"iopub.status.busy":"2022-10-20T16:13:45.603587Z","iopub.execute_input":"2022-10-20T16:13:45.603976Z","iopub.status.idle":"2022-10-20T16:13:45.634334Z","shell.execute_reply.started":"2022-10-20T16:13:45.603939Z","shell.execute_reply":"2022-10-20T16:13:45.633115Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The next blocks of code will be looking at the winning rate based on track condition,race type and course type\n\n****Race type**** \nSTK - Stakes,WCL - Waiver Claiming, WMC - Waiver Maiden Claiming,SST - Starter Stakes, \nSHP - 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\n**Track condition**\nYL - Yielding, FM - Firm, SY - Sloppy, GD - Good, FT - Fast, MY - Muddy, SF - Soft.\n\n**Couse type**\n M - Hurdle, D - Dirt, O - Outer turf, I - Inner turf, T - turf.","metadata":{}},{"cell_type":"code","source":"df_aqu['track_condition'].value_counts(normalize=True)*100","metadata":{"execution":{"iopub.status.busy":"2022-10-20T16:13:45.638039Z","iopub.execute_input":"2022-10-20T16:13:45.638605Z","iopub.status.idle":"2022-10-20T16:13:45.651074Z","shell.execute_reply.started":"2022-10-20T16:13:45.638563Z","shell.execute_reply":"2022-10-20T16:13:45.649651Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, axes = plt.subplots(1, 3, figsize=(20, 5), sharey=True)\n\nsns.histplot(ax=axes[0],data=df_aqu, x=\"track_condition\", hue=\"win\", multiple=\"stack\")\nsns.histplot(ax=axes[1],data=df_aqu, x=\"race_type\", hue=\"win\", multiple=\"stack\")\nsns.histplot(ax=axes[2],data=df_aqu, x=\"course_type\", hue=\"win\", multiple=\"stack\")","metadata":{"execution":{"iopub.status.busy":"2022-10-20T16:13:45.652412Z","iopub.execute_input":"2022-10-20T16:13:45.652756Z","iopub.status.idle":"2022-10-20T16:13:46.319122Z","shell.execute_reply.started":"2022-10-20T16:13:45.652725Z","shell.execute_reply":"2022-10-20T16:13:46.318118Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"On the Aqueduct track \n* FT-Fast track_condition has the highest number of wins with a high margin(with more race_count)\n* D-Dirt course_type has the highest number of wins\n* CLM-Maiden Claiming race_type has the highest number of wins","metadata":{}},{"cell_type":"code","source":"df_bel['track_condition'].value_counts(normalize=True)*100","metadata":{"execution":{"iopub.status.busy":"2022-10-20T16:13:46.320461Z","iopub.execute_input":"2022-10-20T16:13:46.320791Z","iopub.status.idle":"2022-10-20T16:13:46.330564Z","shell.execute_reply.started":"2022-10-20T16:13:46.320761Z","shell.execute_reply":"2022-10-20T16:13:46.329410Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, axes = plt.subplots(1, 3, figsize=(20, 5), sharey=True)\n\nsns.histplot(ax=axes[0],data=df_bel, x=\"track_condition\", hue=\"win\", multiple=\"stack\")\nsns.histplot(ax=axes[1],data=df_bel, x=\"race_type\", hue=\"win\", multiple=\"stack\")\nsns.histplot(ax=axes[2],data=df_bel, x=\"course_type\", hue=\"win\", multiple=\"stack\")","metadata":{"execution":{"iopub.status.busy":"2022-10-20T16:13:46.332064Z","iopub.execute_input":"2022-10-20T16:13:46.333110Z","iopub.status.idle":"2022-10-20T16:13:46.999597Z","shell.execute_reply.started":"2022-10-20T16:13:46.333067Z","shell.execute_reply":"2022-10-20T16:13:46.998333Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"On the Belmont track \n* FT-Fast track_condition has the highest number of wins with the FM-Firm not far begin, and they both had the most races\n* D-Dirt course_type has the highest number of wins\n* CLM-Maiden Claiming race_type has the highest number of wins with the MSW-Maiden Special Weight right on it's tail.The MSW had the most races.","metadata":{}},{"cell_type":"code","source":"df_sar['track_condition'].value_counts(normalize=True)* 100\n","metadata":{"execution":{"iopub.status.busy":"2022-10-20T16:13:47.000759Z","iopub.execute_input":"2022-10-20T16:13:47.001103Z","iopub.status.idle":"2022-10-20T16:13:47.014429Z","shell.execute_reply.started":"2022-10-20T16:13:47.001070Z","shell.execute_reply":"2022-10-20T16:13:47.013423Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, axes = plt.subplots(1, 3, figsize=(20, 5), sharey=True)\n\nsns.histplot(ax=axes[0],data=df_sar, x=\"track_condition\", hue=\"win\", multiple=\"stack\")\nsns.histplot(ax=axes[1],data=df_sar, x=\"race_type\", hue=\"win\", multiple=\"stack\")\nsns.histplot(ax=axes[2],data=df_sar, x=\"course_type\", hue=\"win\", multiple=\"stack\")","metadata":{"execution":{"iopub.status.busy":"2022-10-20T16:13:47.015470Z","iopub.execute_input":"2022-10-20T16:13:47.015815Z","iopub.status.idle":"2022-10-20T16:13:47.818000Z","shell.execute_reply.started":"2022-10-20T16:13:47.015783Z","shell.execute_reply":"2022-10-20T16:13:47.816708Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"On the Belmont track \n* FT-Fast track_condition has the highest number of wins,with the FM-Firm really close,and they both had the most races\n* D-Dirt course_type has the highest number of wins\n* MSW-Maiden Special Weight race_type has the highest number of wins with the STK-Stakes right behind it.The MSW had the most races.","metadata":{}},{"cell_type":"markdown","source":"Let's take a look at the jockey data","metadata":{}},{"cell_type":"code","source":"df_aqu['jockey'].value_counts(normalize=True) * 100","metadata":{"execution":{"iopub.status.busy":"2022-10-20T16:13:47.819283Z","iopub.execute_input":"2022-10-20T16:13:47.820083Z","iopub.status.idle":"2022-10-20T16:13:47.830145Z","shell.execute_reply.started":"2022-10-20T16:13:47.820050Z","shell.execute_reply":"2022-10-20T16:13:47.828752Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize=(30,30))\nsns.histplot(data=df_aqu, y=\"jockey\", hue=\"win\", multiple=\"stack\")","metadata":{"execution":{"iopub.status.busy":"2022-10-20T16:13:47.831819Z","iopub.execute_input":"2022-10-20T16:13:47.832992Z","iopub.status.idle":"2022-10-20T16:13:50.445871Z","shell.execute_reply.started":"2022-10-20T16:13:47.832946Z","shell.execute_reply":"2022-10-20T16:13:50.444936Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Jockey evalution for Aqueduct track**\n\nTop jockey\n\n   *most number of wins*  \n   \n  Manuel Franco,\n  Jose Lezcano,\n  Junior Alvarado,\n  Dylan Davis,\n  Eric Cancel.\n\n  *most number of races*\n  \n  Manuel Franco,\n  Dylan Davis,\n  Jose Lezcano,\n  Junior Alvarado, \n  Eric Cancel.\n  \n \n \n This makes **Manuel Franco** the champion of Aqueduct track, his has the most number of wins and race_count\n","metadata":{}},{"cell_type":"code","source":"df_bel['jockey'].value_counts(normalize=True) * 100","metadata":{"execution":{"iopub.status.busy":"2022-10-20T16:13:50.447176Z","iopub.execute_input":"2022-10-20T16:13:50.447690Z","iopub.status.idle":"2022-10-20T16:13:50.457061Z","shell.execute_reply.started":"2022-10-20T16:13:50.447658Z","shell.execute_reply":"2022-10-20T16:13:50.456187Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize=(30,30))\nsns.histplot(data=df_bel, y=\"jockey\", hue=\"win\", multiple=\"stack\")","metadata":{"execution":{"iopub.status.busy":"2022-10-20T16:13:50.458369Z","iopub.execute_input":"2022-10-20T16:13:50.458923Z","iopub.status.idle":"2022-10-20T16:13:53.195424Z","shell.execute_reply.started":"2022-10-20T16:13:50.458869Z","shell.execute_reply":"2022-10-20T16:13:53.194153Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Jockey evalution for Belmont track**\n\nTop jockey\n\n   *most number of wins* \n   \n  Jose Lezcano,\n  Manuel Franco,\n  Luis Saez,\n  Joel Rosario,\n  Jose L Ortiz.                           \n  \n\n  *most number of races*\n  \n Manuel Franco,\n Dylan Davis,\n Jose Lezcano,\n Irad Ortiz jr,\n Luis Saez.\n \n \n This makes **Jose Lezcano** the champion of Belmont track, his has the most number of wins.\n","metadata":{}},{"cell_type":"code","source":"df_sar['jockey'].value_counts(normalize=True) * 100","metadata":{"execution":{"iopub.status.busy":"2022-10-20T16:13:53.196842Z","iopub.execute_input":"2022-10-20T16:13:53.197222Z","iopub.status.idle":"2022-10-20T16:13:53.208081Z","shell.execute_reply.started":"2022-10-20T16:13:53.197188Z","shell.execute_reply":"2022-10-20T16:13:53.206558Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize=(30,30))\nsns.histplot(data=df_sar, y=\"jockey\", hue=\"win\", multiple=\"stack\")","metadata":{"execution":{"iopub.status.busy":"2022-10-20T16:13:53.210656Z","iopub.execute_input":"2022-10-20T16:13:53.211475Z","iopub.status.idle":"2022-10-20T16:13:55.617695Z","shell.execute_reply.started":"2022-10-20T16:13:53.211422Z","shell.execute_reply":"2022-10-20T16:13:55.616808Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Jockey evalution for Saratoga track**\n\nTop jockey\n\n   *most number of wins* \n   \n  Jose L Ortiz,\n  Irad Ortiz jr,\n  Javier Castellano,\n  Joel Rosario,\n  Luis Saez.                           \n  \n\n  *most number of races*\n  \nJose L. Ortiz,\nIrad Ortiz Jr,\nLuis Saez,\nManuel Franco,\nJoel Rosario.\n \n \n This makes **Jose L. Ortiz** the champion of Saratoga track, his has the most number of wins.\n","metadata":{}},{"cell_type":"markdown","source":"**Conclusion**\n\nRace done on Fast and Firm track_conditions with a Dirt course_type have higher winning rates.\nManuel Franco seems to be the best jockey in this dataset\n\n\nA dataset with individual horse's health record,Gait patterns in which they where trained with,position of the horse in precentile distance,and where they placed in races with make a really good model for running winning prediction.\n\n*Thank You*\n","metadata":{}}]}