{"cells": [{"cell_type": "markdown", "id": "1cc2b638-9c29-4d58-82c2-ba418f11def6", "metadata": {}, "source": ["# Analyzing horse racing data"]}, {"cell_type": "markdown", "id": "63e7dc5b-2c75-416f-ab55-fdaeb0ee9de0", "metadata": {}, "source": ["## First"]}, {"cell_type": "markdown", "id": "56e3729f-3e75-49db-b249-9034e62f5f93", "metadata": {}, "source": ["The notebook analyses the effect of path and distance travelled by horses in the race. It is still in progress, but I hope that you will find something useful. The main point of my analysis is that the final place of horse depends on the distance traveled."]}, {"cell_type": "markdown", "id": "6a73e67e-4681-4815-b1c0-7e1baabbdae5", "metadata": {}, "source": ["## Imports"]}, {"cell_type": "code", "execution_count": 30, "id": "b00ee787-0896-4f11-95b2-ffe5f45bb1eb", "metadata": {}, "outputs": [], "source": ["# install fastkaggle if not available\n", "try: import fastkaggle\n", "except ModuleNotFoundError:\n", "    !pip install -Uq fastkaggle\n", "from fastkaggle import *\n", "\n", "comp = \"big-data-derby-2022\"\n", "path = setup_comp(comp, install='fastai \"timm>=0.6.2.dev0\"')\n", "from fastai.tabular.all import *  # include handy tools such as numpy and pandas\n", "# import pandas as pd\n", "# import numpy as np"]}, {"cell_type": "markdown", "id": "339cdb8b-46cf-4a1e-8110-cc44340f11e4", "metadata": {}, "source": ["## Data preprocessing"]}, {"cell_type": "markdown", "id": "57b12de0-3038-4f64-9581-5927d13792ed", "metadata": {}, "source": ["In this section, I read the data and modify it. \n", "Changes I make are:\n", "- Divide date into year, month and day column\n", "- Add new column race_id to identify a certain race\n", "- Add new column id to identify a certain horse at a certain race\n", "- Calculate the distance traveled by the horse and create columns distance and speed\n", "- Create new dataframes for the races in dirt and in turf(inner turf, outer turf, turf and hurdle) since the course types affects differently in dirt and turf"]}, {"cell_type": "code", "execution_count": 31, "id": "1926d9e3-45e9-47df-a1a0-98636dfeef0d", "metadata": {}, "outputs": [], "source": ["Path.BASE_PATH = path"]}, {"cell_type": "code", "execution_count": 32, "id": "0dc32282-a68b-4f44-a935-7650541ab01a", "metadata": {}, "outputs": [], "source": ["path.ls()"]}, {"cell_type": "code", "execution_count": 33, "id": "3371101f-1f85-4c8c-bd86-17ef71bfb473", "metadata": {}, "outputs": [], "source": ["df = pd.read_csv(path/'nyra_2019_complete.csv', low_memory=False, index_col=False, header=None)"]}, {"cell_type": "code", "execution_count": 34, "id": "e318be88-5459-452b-a3ed-8e369ea57785", "metadata": {}, "outputs": [], "source": ["df.head()"]}, {"cell_type": "code", "execution_count": 35, "id": "752698a6-2761-4b55-9fd8-f21eb18f81cb", "metadata": {}, "outputs": [], "source": ["col_names = ['track_id','race_date','race_number','program_number','trakus_index','latitude',\n", "             'longitude','distance_id','course_type','track_condition','run_up_distance',\n", "             'race_type','purse','post_time','weight_carried','jockey','odds','final_place']\n", "df.set_axis(col_names, axis='columns', inplace=True)\n", "df.head()"]}, {"cell_type": "code", "execution_count": 36, "id": "c8dc5075-65ed-4c27-8295-24b82a17f41f", "metadata": {}, "outputs": [], "source": ["# add useful features 'month', 'day', 'race_id' and 'id'\n", "df[['year','month','day']] = df['race_date'].str.split(\"-\", expand=True).astype(int)\n", "df = df.drop(columns=['year','race_date'])\n", "df['race_id'] = (df['month'].astype(str)+'-'+df['day'].astype(str)+'-'+df['track_id']\n", "                +'-'+df['race_number'].astype(str))\n", "df['id'] = df['race_id']+'-'+df['program_number']"]}, {"cell_type": "code", "execution_count": 37, "id": "62e479fe-1898-448f-9364-7601f9305b6d", "metadata": {}, "outputs": [], "source": ["# add a new column 'distance' that states the distance traveled from the last point\n", "# add a new column 'speed'\n", "def calc_distance(df, r=6371):\n", "    coord = df.latitude, df.longitude, df.latitude2, df.longitude2\n", "    lat1, long1, lat2, long2 = [np.radians(c.to_numpy()) for c in coord]  \n", "    a = (np.square(np.sin((lat2-lat1)/2)) + np.cos(lat1)*np.cos(lat2)* \n", "         np.square(np.sin((long2-long1)/2)))\n", "    distance = 2*1000*r*np.arcsin(np.sqrt(a))\n", "    return distance\n", "\n", "df = df.sort_values(by=['track_id','month','day','race_number','program_number','trakus_index'])\n", "df = df.reset_index(drop=True)\n", "df1 = df[['id','latitude','longitude']].groupby('id').shift(-1)\n", "df[['latitude2', 'longitude2']] = df1[['latitude', 'longitude']]\n", "df['distance'] = calc_distance(df)\n", "df['speed'] = df['distance']/0.25\n", "df.head()"]}, {"cell_type": "code", "execution_count": 38, "id": "d33c25e7-346b-46b6-907b-5c58289b84f8", "metadata": {}, "outputs": [], "source": ["# divide the data into dirt and turf\n", "df_dirt = df[df['course_type']=='D'].drop(columns='course_type').copy()\n", "df_turfs = df[df['course_type']!='D'].copy()"]}, {"cell_type": "code", "execution_count": 39, "id": "8e0a8a24-2cca-4864-9557-19b947e6a4ec", "metadata": {}, "outputs": [], "source": ["# set categories and add order to them\n", "#dirt_cond = 'MY ', 'SY ', 'GD ', 'FT '\n", "#turf_cond = 'FM ', 'GD ', 'YL ', 'SF '\n", "\n", "#df_dirt['track_condition'] = df_dirt['track_condition'].astype('category')\n", "#df_dirt['track_condition'].cat.set_categories(dirt_cond, ordered=True, inplace=True)\n", "\n", "#df_turfs['track_condition'] = df_turfs['track_condition'].astype('category')\n", "#df_turfs['track_condition'].cat.set_categories(turf_cond, ordered=True, inplace=True)"]}, {"cell_type": "code", "execution_count": 40, "id": "51410107-5e0c-450a-893a-afebe32826d3", "metadata": {}, "outputs": [], "source": ["df_dirt.shape[0], df_turfs.shape[0]"]}, {"cell_type": "markdown", "id": "eb3af8a3-fca9-4544-908e-cb2462547553", "metadata": {}, "source": ["## Analyzing"]}, {"cell_type": "code", "execution_count": 41, "id": "102d989b-618b-4342-87fb-aad3b1270595", "metadata": {}, "outputs": [], "source": ["np.sort(df_dirt['distance_id'].unique())"]}, {"cell_type": "code", "execution_count": 42, "id": "2172e473-aa5b-462a-a683-e3dcd5a9252d", "metadata": {}, "outputs": [], "source": ["c1 = df_dirt['race_number']==9\n", "c2 = df_dirt['track_id']=='AQU'\n", "c3 = df_dirt['month']==1\n", "c4 = df_dirt['day']==1\n", "c5 = df_dirt['program_number']=='6  '\n", "\n", "temp = df_dirt[c1&c2&c3&c4]\n", "l = []\n", "\n", "for pn in temp['program_number'].unique():\n", "    l.append(len(temp[temp['program_number']==pn]))\n", "    \n", "l, sum(l)"]}, {"cell_type": "code", "execution_count": 43, "id": "d650177c-7f03-4c26-bc7f-3a2b7e4af716", "metadata": {}, "outputs": [], "source": ["df_dirt.head()"]}, {"cell_type": "code", "execution_count": 44, "id": "cfb4b21f-a7ef-45e5-a8b0-dd89aba4120e", "metadata": {}, "outputs": [], "source": ["plt.scatter(df_dirt[df_dirt.id==df_dirt.id.unique()[5]].latitude, \n", "            df_dirt[df_dirt.id==df_dirt.id.unique()[5]].longitude, s=0.1)"]}, {"cell_type": "code", "execution_count": 45, "id": "51ebe8ca-96bd-49c7-8e5d-6119a2d88c60", "metadata": {}, "outputs": [], "source": ["temp = df_dirt[df_dirt['race_id']==df_dirt['race_id'].unique()[5]]\n", "temp.set_index('trakus_index').groupby(by='id')['speed'].plot()\n", "plt.title('speed')"]}, {"cell_type": "markdown", "id": "f4d28e56-d31b-47c0-8a16-8d35104a138f", "metadata": {}, "source": ["### The effect of the distance traveled on final place"]}, {"cell_type": "markdown", "id": "236cc9fc-ba3d-403e-895d-1ebcbae9104a", "metadata": {}, "source": ["In this section, I will explore how path affect final place. The effects are investigated for different tracks and distances with a sample count of more than 10. Since there are a different number of horses in races, I use horses with a final place of 5 or better than 5, and races with 5 or more than 5 horses."]}, {"cell_type": "markdown", "id": "9f7187a9-a661-4b90-a88f-4fcaaa72cae9", "metadata": {}, "source": ["### Dirt"]}, {"cell_type": "code", "execution_count": 46, "id": "b54f87e4-30ec-45a1-89d6-bcdd3c805a96", "metadata": {}, "outputs": [], "source": ["horse_count = df_dirt.groupby(by=['race_id'])['final_place'].max().to_frame()\n", "horse_count.hist()\n", "plt.title('number of horses')\n", "plt.xlabel('number of horses')\n", "plt.ylabel('frequency')\n", "\n", "print([len(horse_count[horse_count['final_place']<=x]) for x in [3,4,5,14]])"]}, {"cell_type": "code", "execution_count": 47, "id": "1d02952c-db20-46ca-af57-3588fb854382", "metadata": {}, "outputs": [], "source": ["race_drop = list(horse_count[horse_count['final_place']<=4].index)\n", "df_dirt_drop = df_dirt[df_dirt['race_id'].isin(race_drop)==False]"]}, {"cell_type": "code", "execution_count": 48, "id": "d0b6d942-9241-450d-a9d3-2058d4c5af69", "metadata": {}, "outputs": [], "source": ["len(df_dirt_drop), len(df_dirt)"]}, {"cell_type": "code", "execution_count": 49, "id": "eced83ac-154e-4056-957f-81cec8bf351a", "metadata": {}, "outputs": [], "source": ["c1 = df_dirt_drop['distance_id']==600\n", "c2 = df_dirt_drop['track_id']=='AQU'\n", "c3 = df_dirt_drop['final_place']<=5\n", "temp = df_dirt_drop[c1&c2&c3]\n", "temp_group = temp.groupby(by='final_place')['distance'].sum()\n", "temp_group.plot(kind='bar', ylim=(290000,300000))"]}, {"cell_type": "code", "execution_count": 50, "id": "7a5fe4a1-66de-43be-8ffc-aa520cefc416", "metadata": {}, "outputs": [], "source": ["def distance_sum(df, distance, track):\n", "    c1 = df['distance_id']==distance\n", "    c2 = df['track_id']==track\n", "    c3 = df['final_place']<=5\n", "    df = df[c1&c2&c3]\n", "    df_gr = df.groupby(by='final_place')['distance'].sum()\n", "    return df_gr/df_gr.max()"]}, {"cell_type": "code", "execution_count": 51, "id": "86f173bf-bcfe-44ad-b206-8e0ec6ea2d40", "metadata": {}, "outputs": [], "source": ["distance_sum(df_dirt_drop, 600, 'AQU').plot(kind='bar', ylim=(0.9,1.))"]}, {"cell_type": "code", "execution_count": 52, "id": "0812eb9e-21ee-43f9-b169-071fbdacd1ae", "metadata": {}, "outputs": [], "source": ["df_dirt_drop[df_dirt_drop['final_place']==1].odds.hist\n", "df_dirt_drop[df_dirt_drop['final_place']==1].shape[0], df_dirt_drop[(df_dirt_drop['final_place']==1)&(df_dirt_drop['odds']>=200)].shape[0]"]}, {"cell_type": "code", "execution_count": 53, "id": "549aa7a9-d80b-4224-bad9-654967a8edb6", "metadata": {}, "outputs": [], "source": ["for dist in np.sort(df_dirt_drop['distance_id'].unique()):\n", "    print(dist,\":\", len(df_dirt_drop[df_dirt_drop['distance_id']==dist]['race_id'].unique()), 'races')"]}, {"cell_type": "code", "execution_count": 54, "id": "e17bf1ae-f934-44ac-b9b9-21f7090f8746", "metadata": {}, "outputs": [], "source": ["def distance_dataframes(df, sample=10):\n", "    dfs = []\n", "\n", "    for track in ['AQU','BEL','SAR']:\n", "        df_dist = pd.DataFrame()\n", "        cond = df['track_id']==track\n", "        distance = []\n", "\n", "        for dist in np.sort(df[cond]['distance_id'].unique()):\n", "            if len(df[cond&(df['distance_id']==dist)]['race_id'].unique())>=sample:\n", "                distance.append(dist)\n", "\n", "        for dist in distance:\n", "            df_dist = pd.concat([df_dist, distance_sum(df, dist, track)], axis=1)\n", "\n", "        df_dist.columns = [str(col) for col in distance]\n", "        dfs.append(df_dist)\n", "        \n", "    return dfs\n", "\n", "def plot_dataframes(dfs, ylim=(0.95,1.005)):\n", "    if not dfs[0].empty:\n", "        dfs[0].plot(ylim=ylim)\n", "        plt.title('AQU')\n", "        plt.xlabel('final place')\n", "        plt.ylabel('distance traveled')\n", "    \n", "    if not dfs[1].empty:\n", "        dfs[1].plot(ylim=ylim)\n", "        plt.title('BEL')\n", "        plt.xlabel('final place')\n", "        plt.ylabel('distance traveled')\n", "    \n", "    if not dfs[2].empty:\n", "        dfs[2].plot(ylim=ylim)\n", "        plt.title('SAR')\n", "        plt.xlabel('final place')\n", "        plt.ylabel('distance traveled')"]}, {"cell_type": "code", "execution_count": 55, "id": "7c419782-b4c4-44c8-9c49-47b3ba793ec1", "metadata": {}, "outputs": [], "source": ["dfs_dirt = distance_dataframes(df_dirt_drop)\n", "plot_dataframes(dfs_dirt)"]}, {"cell_type": "code", "execution_count": 56, "id": "eb7e24b3-8e89-44bc-8a50-e5d2dbd450ca", "metadata": {}, "outputs": [], "source": ["print('BEL',600,\":\", len(df_dirt_drop[(df_dirt_drop['track_id']=='BEL')&(df_dirt_drop['distance_id']==600)]['race_id'].unique()))\n", "for dist in [600,700]:\n", "    print('SAR',dist,\":\", len(df_dirt_drop[(df_dirt_drop['track_id']=='SAR')&(df_dirt_drop['distance_id']==dist)]['race_id'].unique()))"]}, {"cell_type": "markdown", "id": "d9a4a90d-1d17-4b4d-a61a-ba0d28b552c0", "metadata": {}, "source": ["In dirt, it shows that the horses have ranked when they have run longer distances than the others. Possibly, this is because the winning horses are usually the fastest horses and thus run on a safer path. Further research could study the path traveled by horses with different odds."]}, {"cell_type": "markdown", "id": "52de465f-d4e0-4049-8e3f-06094b02fb6a", "metadata": {}, "source": ["### Turfs"]}, {"cell_type": "code", "execution_count": 57, "id": "b6b11f2a-6c71-49d2-8cdd-73e57aa877b5", "metadata": {}, "outputs": [], "source": ["horse_count = df_turfs.groupby(by=['race_id'])['final_place'].max().to_frame()\n", "horse_count.hist()\n", "plt.title('number of horses')\n", "plt.xlabel('number of horses')\n", "plt.ylabel('frequency')\n", "\n", "print([len(horse_count[horse_count['final_place']<=x]) for x in [3,4,5,14]])"]}, {"cell_type": "code", "execution_count": 58, "id": "8f9080c0-7049-40f9-a76f-45c7021f2ac7", "metadata": {}, "outputs": [], "source": ["for c in df_turfs['course_type'].unique():\n", "    print(c,':',len(df_turfs[df_turfs['course_type']==c]['race_id'].unique()))"]}, {"cell_type": "code", "execution_count": 59, "id": "e54016ff-6a75-4efa-a355-fea35a98042c", "metadata": {}, "outputs": [], "source": ["race_drop = list(horse_count[horse_count['final_place']<=4].index)\n", "df_turfs_drop = df_turfs[df_turfs['race_id'].isin(race_drop)==False]"]}, {"cell_type": "code", "execution_count": 60, "id": "7084feef-ae35-4259-8c07-814a3a06242e", "metadata": {}, "outputs": [], "source": ["df_outer = df_turfs_drop[df_turfs_drop['course_type']=='O']\n", "df_inner = df_turfs_drop[df_turfs_drop['course_type']=='I']\n", "df_turf = df_turfs_drop[df_turfs_drop['course_type']=='T']"]}, {"cell_type": "code", "execution_count": 61, "id": "90e8355d-8b22-4571-8b17-74f46fbf595a", "metadata": {}, "outputs": [], "source": ["for dist in np.sort(df_inner['distance_id'].unique()):\n", "    print(dist,\":\", len(df_inner[df_inner['distance_id']==dist]['race_id'].unique()), 'races')"]}, {"cell_type": "code", "execution_count": null, "id": "ebab79bc-9ea7-4d1c-bb2c-0dd51133378c", "metadata": {}, "outputs": [], "source": ["dfs_outer = distance_dataframes(df_outer)\n", "print('Outer Turf')\n", "plot_dataframes(dfs_outer, ylim=(0.8,1.005))"]}, {"cell_type": "code", "execution_count": null, "id": "997edd46-bc68-4a38-9d3e-1dce95afc96c", "metadata": {}, "outputs": [], "source": ["dfs_inner = distance_dataframes(df_inner)\n", "print('Inner Turf')\n", "plot_dataframes(dfs_inner, ylim=(0.92,1.005))"]}, {"cell_type": "code", "execution_count": null, "id": "e9cf624c-87ef-4f12-a8b4-d286d6d10be5", "metadata": {}, "outputs": [], "source": ["dfs_turf = distance_dataframes(df_turf)\n", "print('Turf')\n", "plot_dataframes(dfs_turf, ylim=(0.88,1.005))"]}, {"cell_type": "markdown", "id": "a0ff52e1-2f8e-4d94-9bb5-c26b48ad7a86", "metadata": {}, "source": ["In turfs, different course types shows different results. The next step could be to investigate the reason behind these results."]}, {"cell_type": "markdown", "id": "f9e89754-ed26-4a8f-87f3-c12786f8f6b8", "metadata": {}, "source": ["## References\n", "https://edge.twinspires.com/racing/an-introduction-to-track-conditions-and-all-the-related-lingo/\n", "https://www.kaggle.com/code/xxxxyyyy80008/derby-2022-eda-speed-analysis#Speed-analysis\n", "https://towardsdatascience.com/finding-distance-between-two-latitudes-and-longitudes-in-python-43e92d6829ff\n", "https://paulickreport.com/horseplayers-category/thoroughbred-idea-foundation-run-up-made-final-time-misleading-in-jessamine/#:~:text=%E2%80%9CRun-up%E2%80%9D%20is%20the%20distance%20from%20where%20the%20gate,distance%20listed%20in%20the%20program%20or%20past%20performances."]}, {"cell_type": "code", "execution_count": null, "id": "5e1057fb-6ec1-474f-9d34-0098dd51e9bb", "metadata": {}, "outputs": [], "source": ["if not iskaggle:\n", "    push_notebook('hd', 'The effect of the distance traveled',\n", "                  title='The effect of the distance traveled',\n", "                  file='horse_race.ipynb',\n", "                  competition=comp, private=False, gpu=True)"]}], "metadata": {"kernelspec": {"display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3"}, "language_info": {"codemirror_mode": {"name": "ipython", "version": 3}, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.9.13"}}, "nbformat": 4, "nbformat_minor": 5}