{"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":"# DATA DERBY 2022\n\nCredit to : GABRIEL BEGUERIE","metadata":{}},{"cell_type":"markdown","source":"****The objective of this work is the detailed analysis of each race accompanied by graphics and animations for a better understanding of the events, strategies and positions of the horses.****","metadata":{}},{"cell_type":"markdown","source":"# 1) Prepare Data","metadata":{}},{"cell_type":"code","source":"import numpy as np\nimport pandas as pd \nimport os\nimport math\n\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport plotly.express as px\n\n","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:43:20.221930Z","iopub.execute_input":"2022-11-09T04:43:20.222393Z","iopub.status.idle":"2022-11-09T04:43:22.570889Z","shell.execute_reply.started":"2022-11-09T04:43:20.222300Z","shell.execute_reply":"2022-11-09T04:43:22.568821Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"To calculate the distances traveled by each horse I will use Vincenty's formula that I had uploaded in this notebook [https://www.kaggle.com/code/gabrielbeguerie/derby-2022-vincenty-formulae-distance](https://www.kaggle.com/code/gabrielbeguerie/derby-2022-vincenty-formulae-distance)","metadata":{}},{"cell_type":"code","source":"from math import atan\nfrom math import atan2\nfrom math import cos\nfrom math import radians\nfrom math import sin\nfrom math import sqrt\nfrom math import tan\n\n\nclass Vincenty:\n    def __init__(self, c1, c2):        \n        self.meters = self.calc(c1, c2)                   # output distance in meters\n        self.km = self.meters / 1000                      # output distance in kilometers\n        self.mm = self.meters * 1000                      # output distance in millimeters\n        self.miles = self.meters * 0.000621371            # output distance in miles\n        self.n_miles = self.miles * (6080.20 / 5280)      # output distance in nautical miles\n        self.feet = self.miles * 5280                       # output distance in feet\n        self.inches = self.feet * 12                      # output distance in inches\n        self.yards = self.feet / 3                        # output distance in yards\n        \n        \n    def calc(self, c1 ,c2):\n        a = 6378137.0\n        f = 1 / 298.257223563\n        b = (1 - f) * a\n        \n        lat1, lon1, = c1\n        lat2, lon2, = c2\n        \n        U1 = atan((1 - f) * tan(radians(lat1)))\n        U2 = atan((1 - f) * tan(radians(lat2)))\n        \n        L = radians(lon2 - lon1)\n        Lambda = L\n        \n        # COMMON USED VALUES\n        sin_U1 = sin(U1)\n        cos_U1 = cos(U1)\n        sin_U2 = sin(U2)\n        cos_U2 = cos(U2)\n        \n        # VINCENTY CALCULATING DISTANCE IN METERS\n        \n        for iter in range(200):\n            sin_Lambda = sin(Lambda)\n            cos_Lambda = cos(Lambda) \n            sin_Sigma = sqrt((cos_U2 * sin_Lambda) ** 2 + ((cos_U1 * sin_U2) - (sin_U1 * cos_U2 * cos_Lambda)) ** 2 )\n            cos_Sigma = (sin_U1 * sin_U2) + (cos_U1 * cos_U2 * cos_Lambda)\n            Sigma = atan2(sin_Sigma, cos_Sigma)\n            if sin_Sigma != 0:\n                sin_Alpha = (cos_U1 * cos_U2 * sin_Lambda) / sin_Sigma\n            else:\n                sin_Alpha = (cos_U1 * cos_U2 * sin_Lambda) / 0.00001\n            cos_Sq_Alpha = 1 - (sin_Alpha ** 2)\n        \n            try:\n                cos2_Sigma_M = cos_Sigma - ((2 * sin_U1 * sin_U2) / cos_Sq_Alpha)\n            except ZeroDivisionError:\n                cos2_Sigma_M = 0.0\n        \n            C = (f / 16) * cos_Sq_Alpha * (4 + (f * (4 - 3 * cos_Sq_Alpha)))            \n            LP = Lambda\n            Lambda = L + (1 - C) * f * sin_Alpha * (Sigma + C * sin_Sigma * (cos2_Sigma_M + C * cos_Sigma * (-1 + 2 * (cos2_Sigma_M ** 2))))\n        \n            if (abs(Lambda - LP)) < 1e-12:\n                break\n        else:\n            return None\n            \n        u_Sq = cos_Sq_Alpha * ((a ** 2 - b ** 2) / (b ** 2))\n        A = 1 + (u_Sq / 16384) * (4096 + u_Sq * (-768 + u_Sq * (320 - 175 * u_Sq)))\n        B = (u_Sq / 1024) * (256 + u_Sq * (-128 + u_Sq * (74 - 47 * u_Sq)))\n        k1 = (sqrt(1 + u_Sq) - 1) / (sqrt(1 + u_Sq) + 1)\n        #A = (1 + 0.25 * k1 **2) / (1 - k1)\n        #B = k1 * (1 - (3/8) * k1 ** 2)\n        d_Sigma = B * sin_Sigma * (cos2_Sigma_M + (B / 4) * (cos_Sigma * (-1 + 2 * cos2_Sigma_M ** 2) - (B / 6) * cos2_Sigma_M * (-3 + 4 * sin_Sigma ** 2) * (-3 + 4 * cos2_Sigma_M ** 2)))\n        s = b * A * (Sigma - d_Sigma)\n        Alpha_1 = atan2(cos_U2 * sin_Sigma, cos_U1 * sin_U2 - sin_U1 * cos_U2 * cos_Sigma)        \n        Alpha_2 = atan2(cos_U1 * sin_Sigma, - sin_U1 * cos_U2 + cos_U1 * sin_U2 * cos_Sigma)\n        \n        return s\n        ","metadata":{"execution":{"iopub.status.busy":"2022-11-09T04:43:26.018580Z","iopub.execute_input":"2022-11-09T04:43:26.019461Z","iopub.status.idle":"2022-11-09T04:43:26.045264Z","shell.execute_reply.started":"2022-11-09T04:43:26.019411Z","shell.execute_reply":"2022-11-09T04:43:26.044194Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Functions Lambda\n\nreplace = lambda x: x.replace(\" \", '')\ngeo = lambda x, y: Vincenty(x,y).meters","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:43:31.859934Z","iopub.execute_input":"2022-11-09T04:43:31.860398Z","iopub.status.idle":"2022-11-09T04:43:31.867804Z","shell.execute_reply.started":"2022-11-09T04:43:31.860360Z","shell.execute_reply":"2022-11-09T04:43:31.866017Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = pd.read_csv('../input/big-data-derby-2022/nyra_tracking_table.csv')","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:43:34.413685Z","iopub.execute_input":"2022-11-09T04:43:34.414137Z","iopub.status.idle":"2022-11-09T04:43:43.288231Z","shell.execute_reply.started":"2022-11-09T04:43:34.414102Z","shell.execute_reply":"2022-11-09T04:43:43.287198Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"start_table_names = ['track_id', 'race_date', 'race_number', 'program_number', 'weight_carried', 'jockey', 'odds', 'position_at_finish']\nstart_table = pd.read_csv('../input/big-data-derby-2022/nyra_start_table.csv', names=start_table_names)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:43:43.290052Z","iopub.execute_input":"2022-11-09T04:43:43.291383Z","iopub.status.idle":"2022-11-09T04:43:43.327285Z","shell.execute_reply.started":"2022-11-09T04:43:43.291322Z","shell.execute_reply":"2022-11-09T04:43:43.325815Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_table = pd.read_csv('../input/big-data-derby-2022/nyra_race_table.csv')\nrace_table['race_index'] = race_table.index\nrace_table = race_table.drop(columns=['purse', 'post_time'])","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:43:51.481352Z","iopub.execute_input":"2022-11-09T04:43:51.481841Z","iopub.status.idle":"2022-11-09T04:43:51.515804Z","shell.execute_reply.started":"2022-11-09T04:43:51.481807Z","shell.execute_reply":"2022-11-09T04:43:51.514737Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = pd.merge(df, start_table[['track_id', 'race_date', 'race_number', 'program_number', 'jockey', 'position_at_finish']], how='left', on=['track_id', 'race_date', 'race_number', 'program_number'])\ndf = pd.merge(df, race_table[['track_id', 'race_date', 'race_number', 'course_type', 'track_condition', 'race_type', 'distance_id', 'run_up_distance', 'race_index']], how='left', on=['track_id', 'race_date', 'race_number'])","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:43:53.331052Z","iopub.execute_input":"2022-11-09T04:43:53.331757Z","iopub.status.idle":"2022-11-09T04:43:58.379098Z","shell.execute_reply.started":"2022-11-09T04:43:53.331719Z","shell.execute_reply":"2022-11-09T04:43:58.377843Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# For a better understanding of the data, I replace abbreviations with full words.\n\nrace_type_dict =   {  'STK': 'Stakes', \n                      'WCL': 'Waiver Claiming', \n                      'WMC': 'Waiver Maiden Claiming', \n                      'SST': 'Starter Stakes', \n                      'SHP': 'Starter Handicap',\n                      'CLM': 'Claiming', \n                      'STR': 'Starter Allowance', \n                      'AOC': 'Allowance Optionl Claimer', \n                      'SOC': 'Starter Optional Claimer', \n                      'MCL': 'Maiden Claiming', \n                      'ALW': 'Allowance', \n                      'MSW': 'Maiden Special Weight'\n                      }\n\nprogram_number_dict = {'1': '01', '10': '10', '11': '11', '12': '12', '13': '13', '14': '14', '15': '15', '16': '16', '1A': '01A', \n           '1X': '01X', '2': '02', '2B': '02B', '3': '03', '3X': '03X', '4': '04', '5': '05', '6': '06', '7': '07', \n           '8': '08', '9': '09'}\n\ntrack_id_dict = dict(AQU = 'Aqueduct', BEL = 'Belmont', SAR = 'Saratoga')\ncourse_type_dict = dict(M = 'Hurdle', D = 'Dirt', O = 'Outer turf', I = 'Inner turf', T = 'turf')\ntrack_condition_dict =  dict(YL = 'Yielding', FM ='Firm', SY = 'Sloppy', GD = 'Good', FT = 'Fast', MY = 'Muddy', SF = 'Soft')\n\ndf['program_number'] = df['program_number'].apply(replace)\ndf['program_number'] = df['program_number'].map(program_number_dict)\ndf['race_type'] = df['race_type'].map(race_type_dict)\ndf['track_id'] = df['track_id'].map(track_id_dict)\ndf['course_type'] = df['course_type'].map(course_type_dict)\ndf['track_condition'] = df['track_condition'].apply(replace)\ndf['track_condition'] = df['track_condition'].map(track_condition_dict)\ndf = df.sort_values(['race_index', 'program_number', 'trakus_index'])\ndf['run_up_distance'] = df['run_up_distance'] / 3.2808","metadata":{"execution":{"iopub.status.busy":"2022-11-09T04:44:00.468159Z","iopub.execute_input":"2022-11-09T04:44:00.468802Z","iopub.status.idle":"2022-11-09T04:44:11.764114Z","shell.execute_reply.started":"2022-11-09T04:44:00.468749Z","shell.execute_reply":"2022-11-09T04:44:11.762881Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# create column 'latlon' with list ['latitude', 'longitude'] \n\ndf['latlon'] = np.array([df['latitude'].values, df['longitude'].values]).T.tolist()\ndf['latlon_prev'] = df.groupby(['race_index', 'program_number'])['latlon'].transform(lambda x: x.shift(-1))\ndf['latlon_prev'] = df['latlon_prev'].ffill(axis = 'rows')\ndf","metadata":{"execution":{"iopub.status.busy":"2022-11-09T04:44:15.231517Z","iopub.execute_input":"2022-11-09T04:44:15.231974Z","iopub.status.idle":"2022-11-09T04:44:26.731077Z","shell.execute_reply.started":"2022-11-09T04:44:15.231935Z","shell.execute_reply":"2022-11-09T04:44:26.729591Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# In this cell I take the middle position among all the horses in each race.\n# It would be like a \"ghost horse\" to which all the horses in each trakus_index will be attached\n# It is also necessary to know the angle of trajectory of each horse to know its direction.\n\ndef get_angle(coords, coords_prev):\n    coords = np.array(coords.values).tolist()\n    coords_prev = np.array(coords_prev.values).tolist()  \n    ang = []\n    for i in range(len(coords)):\n        if coords_prev[i] != 0: \n            dlat = coords[i][0] - coords_prev[i][0]\n            dlon = coords[i][1] - coords_prev[i][1]\n            if dlon != 0:\n                ang.append(math.atan2(dlat,dlon))\n            else:\n                ang.append(math.atan2(dlat,0.0001))\n        else:\n            ang.append(0)\n   \n    return ang\n\ndef get_angle_mean(coords, coords_prev):\n    coords = np.array(coords.values).tolist()\n    coords_prev = np.array(coords_prev.values).tolist()  \n    ang = []\n    for i in range(len(coords)):\n        dlat = coords[i][0] - coords_prev[i][0]\n        dlon = coords[i][1] - coords_prev[i][1]\n        if dlon != 0:\n            ang.append(math.atan2(dlat,dlon))\n        else:\n            ang.append(math.atan2(dlat,0.0001))\n           \n    return ang\n\ndef get_latlon_mean(coords):\n    coords = np.array(coords).tolist()\n    nn = np.mean(coords, axis=0)\n    coords1 = [nn for i in range(len(coords))]    \n    return coords1\n \n\ndf['finish'] = df['distance_id'] * 2.01168   #line at finish\ndf['angle'] = get_angle(df['latlon'], df['latlon_prev'])\nllm = df.groupby(['race_index', 'trakus_index'])['latlon'].transform(lambda x: get_latlon_mean(x.values))\ndf = df.sort_values(['race_index', 'trakus_index'])\ndf['latlon_mean'] = llm\ndf = df.sort_values(['race_index', 'program_number', 'trakus_index'])\ndf['latlon_mean_prev'] = df.groupby(['race_index', 'program_number'])['latlon_mean'].transform(lambda x: x.shift(-1))\ndf['latlon_mean_prev'] = df['latlon_mean_prev'].ffill(axis = 'rows')\ndf['angle_mean'] = get_angle_mean(df['latlon_mean'], df['latlon_mean_prev'])\ndf","metadata":{"execution":{"iopub.status.busy":"2022-11-09T04:44:29.744431Z","iopub.execute_input":"2022-11-09T04:44:29.744954Z","iopub.status.idle":"2022-11-09T04:47:18.523332Z","shell.execute_reply.started":"2022-11-09T04:44:29.744917Z","shell.execute_reply":"2022-11-09T04:47:18.521911Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# In the following formula, the average position (\"latlon_mean\") is taken as reference \n# and an axes rotation is performed for the position of each horse.\n\n# This is used to obtain a horizontal view of the track without curves and thus be able to see \n#the trajectories of each horse in each trakus_index across the track and also to \n# know the position in the race.\n\ndef get_position_absolute(llh, llm, ang):\n    llh = np.array(llh.values).tolist()\n    llm = np.array(llm.values).tolist()\n    ang = np.array(ang.values).tolist()\n    rol = []     \n    for i in range(len(llh)):\n        x_axis = -((llh[i][1] - llm[i][1]) * math.cos(ang[i]) + (llh[i][0] - llm[i][0]) * math.sin(ang[i]))\n        y_axis = ((llh[i][1] - llm[i][1]) * math.sin(ang[i]) - (llh[i][0] - llm[i][0]) * math.cos(ang[i]))     \n        rol.append([y_axis, x_axis])    \n    return rol\n\ndf['pos_abs'] = get_position_absolute(df['latlon'], df['latlon_mean'], df['angle'])\ndf","metadata":{"execution":{"iopub.status.busy":"2022-11-09T04:47:18.526331Z","iopub.execute_input":"2022-11-09T04:47:18.527849Z","iopub.status.idle":"2022-11-09T04:47:43.905763Z","shell.execute_reply.started":"2022-11-09T04:47:18.527790Z","shell.execute_reply":"2022-11-09T04:47:43.904248Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# We separate the coordinates returned by the axis rotation and obtain:\n# \"px\": exact position of each horse on the x-axis.\n# \"py\": exact position of each horse on the y-axis (width of the track).\n\ndf['px'] = np.stack(df['pos_abs'].values).T.tolist()[1]\ndf['py'] = np.stack(df['pos_abs'].values).T.tolist()[0]\ndf","metadata":{"execution":{"iopub.status.busy":"2022-10-28T18:01:57.312002Z","iopub.execute_input":"2022-10-28T18:01:57.312523Z","iopub.status.idle":"2022-10-28T18:02:26.858659Z","shell.execute_reply.started":"2022-10-28T18:01:57.312485Z","shell.execute_reply":"2022-10-28T18:02:26.857372Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Taking the variable \"px\" now it is very easy to know exactly the position of each horse at \n# each moment of the race without margin of error.\n\ndef get_pos(x):\n    n = [i+1 for i in range(len(x))]    \n    return reversed(n)\n\ndf = df.sort_values(['race_index','trakus_index', 'px'])\ndf['position'] = 0\ndf['position'] = df.groupby(['race_index','trakus_index'])['position'].transform(get_pos)\n","metadata":{"execution":{"iopub.status.busy":"2022-10-28T18:02:26.86041Z","iopub.execute_input":"2022-10-28T18:02:26.861293Z","iopub.status.idle":"2022-10-28T18:04:05.217819Z","shell.execute_reply.started":"2022-10-28T18:02:26.861233Z","shell.execute_reply":"2022-10-28T18:04:05.216106Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = df.sort_values(['race_index', 'program_number', 'trakus_index']).reset_index(drop=True)","metadata":{"execution":{"iopub.status.busy":"2022-10-28T18:04:05.247799Z","iopub.execute_input":"2022-10-28T18:04:05.248377Z","iopub.status.idle":"2022-10-28T18:04:15.053631Z","shell.execute_reply.started":"2022-10-28T18:04:05.24833Z","shell.execute_reply":"2022-10-28T18:04:15.052466Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-28T18:04:15.055119Z","iopub.execute_input":"2022-10-28T18:04:15.05591Z","iopub.status.idle":"2022-10-28T18:04:15.093355Z","shell.execute_reply.started":"2022-10-28T18:04:15.055859Z","shell.execute_reply":"2022-10-28T18:04:15.092502Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 2) Choice of a race","metadata":{}},{"cell_type":"markdown","source":"\nOnce the necessary data is prepared, we can now analyze any race and obtain more specific data.","metadata":{}},{"cell_type":"code","source":"dff = df[(df['race_index'] == 521)].copy(deep=True)\ndff\n","metadata":{"execution":{"iopub.status.busy":"2022-10-28T18:04:15.094458Z","iopub.execute_input":"2022-10-28T18:04:15.095485Z","iopub.status.idle":"2022-10-28T18:04:15.159635Z","shell.execute_reply.started":"2022-10-28T18:04:15.095449Z","shell.execute_reply":"2022-10-28T18:04:15.158275Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 3) Prepare Data Race","metadata":{}},{"cell_type":"code","source":"# Now it's time to get the distances traveled, speeds and acceleration of each horse in the race\n\nsgps = 0.29243 # Aproximatte time signal GPS, not is 0.25 seconds\n\ndef get_distance(coords1, coords2, angh, angm):\n    coords1 = np.array(coords1.values).tolist()\n    coords2 = np.array(coords2.values).tolist()\n    angh = np.array(angh.values).tolist()\n    angm = np.array(angm.values).tolist()\n    dist = []\n    for i in range(len(coords1)):\n        dist.append(geo(coords1[i], coords2[i]) * math.cos(angm[i] - angh[i]))    \n    return dist\n\ndef get_aceleration(speed):\n    speed = np.array(speed.values).tolist()         \n    acel = [0]\n    for i in range(1, len(speed)):\n        acel.append((speed[i] - speed[i-1])/sgps)\n    return acel \n\ndef get_total_distance(tdist):\n    return tdist.values.cumsum()\n        \n\ndff['distance'] = get_distance(dff['latlon'], dff['latlon_prev'], dff['angle'], dff['angle_mean'])\ndff['total_distance'] = dff.groupby(['race_index', 'program_number'])['distance'].transform(get_total_distance)\ndff['total_distance'] = (dff['total_distance'] - dff['run_up_distance']) \ndff['speed'] = dff['distance'] / sgps\ndff['aceleration'] = get_aceleration(dff['distance'])\ndff['at_finish'] = (dff['finish']+dff['run_up_distance']) - dff['total_distance']\ndff\n","metadata":{"execution":{"iopub.status.busy":"2022-10-28T18:04:15.161221Z","iopub.execute_input":"2022-10-28T18:04:15.161734Z","iopub.status.idle":"2022-10-28T18:04:15.275356Z","shell.execute_reply.started":"2022-10-28T18:04:15.161686Z","shell.execute_reply":"2022-10-28T18:04:15.274517Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Get Final Time of the race\n\ndef get_data(data):\n    lista = []\n    for i in range(len(data)):\n        lista.append(np.array(data[i][1].values).tolist()) \n    return lista\n            \ndef get_time(td, pos, af, spd):    \n    tt = 0\n    ftime = [0]\n    for i in range(1, len(td)):\n        for j in range(len(td[i])):\n            if td[i][j] < 0 and td[i-1][j] < 0 and pos[i][j] == 1:               \n                tt += 0\n                ftime.append(tt)\n            elif td[i][j] > 0 and td[i-1][j] < 0 and pos[i][j] == 1:                \n                s = abs(td[i][j] / (spd[i-1][j]))\n                s1 = abs(td[i][j] / (spd[i][j]))\n                diff = ((s + s1) / 2)\n                tt += diff\n                ftime.append(tt)\n            elif td[i][j] > 0 and td[i-1][j] > 0 and af[i][j] > td[i][j] and af[i-1][j] > td[i-1][j] and pos[i][j] == 1:\n                tt += sgps\n                ftime.append(tt)\n            elif td[i][j] > 0 and td[i-1][j] > 0 and af[i][j] - td[i-1][j] > 0 and af[i][j] - td[i][j] < 0 and pos[i][j] == 1:\n                s = (abs(af[i][j] - td[i-1][j]) / (spd[i-1][j])) \n                s1 = (abs(af[i][j] - td[i-1][j]) / (spd[i][j])) \n                diff = ((s + s1) / 2)\n                tt += diff\n                ftime.append(tt)\n            else:\n                pass\n       \n    return ftime\n                \n\ntotal_d = get_data(np.array(dff.groupby(['trakus_index'])['total_distance'], dtype=object))\npos = get_data(np.array(dff.groupby(['trakus_index'])['position'], dtype=object))\nat_fnsh = get_data(np.array(dff.groupby(['trakus_index'])['finish'], dtype=object))\nspd = get_data(np.array(dff.groupby(['trakus_index'])['speed'], dtype=object))\ntime = get_time(total_d, pos, at_fnsh, spd)\ntime_race = pd.DataFrame(time)\nprint(f'Approximate Final Time: {round(time[-1],2)} seconds')\ntime_race","metadata":{"execution":{"iopub.status.busy":"2022-10-28T18:04:15.276897Z","iopub.execute_input":"2022-10-28T18:04:15.277566Z","iopub.status.idle":"2022-10-28T18:04:15.377925Z","shell.execute_reply.started":"2022-10-28T18:04:15.277523Z","shell.execute_reply":"2022-10-28T18:04:15.376982Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Race Finish: calculate position perfect with position_at_finish\n\ndff[(dff['at_finish'].shift(1)>0) & (dff['at_finish']<0)]","metadata":{"execution":{"iopub.status.busy":"2022-10-28T18:04:15.379457Z","iopub.execute_input":"2022-10-28T18:04:15.380111Z","iopub.status.idle":"2022-10-28T18:04:15.416486Z","shell.execute_reply.started":"2022-10-28T18:04:15.380072Z","shell.execute_reply":"2022-10-28T18:04:15.415108Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 4) Analysis Race","metadata":{}},{"cell_type":"code","source":"sns.set(rc = {'figure.figsize':(15,8)})\nrc = sns.scatterplot(data=dff, x=\"longitude\", y=\"latitude\", hue=\"program_number\")","metadata":{"execution":{"iopub.status.busy":"2022-10-28T18:04:15.418125Z","iopub.execute_input":"2022-10-28T18:04:15.418671Z","iopub.status.idle":"2022-10-28T18:04:16.240198Z","shell.execute_reply.started":"2022-10-28T18:04:15.418615Z","shell.execute_reply":"2022-10-28T18:04:16.238732Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"This work is focused on the use of the width of the track and the decisions of each rider on how to use it for the benefit of obtaining a better position.\n\nThe graph shows this use of the track width.\n\nThe positive number of \"py\" is the inside of the track with respect to the curve and the negative number is the outside of the track.\n\nThe width of the track is unknown, \"py\" at zero is the average position between all horses at each trakus_index.\n\nThe dotted vertical line is the finish line.","metadata":{}},{"cell_type":"code","source":"line_finish = list(dff['finish'].values)[0]\nprint(line_finish)\ng = sns.FacetGrid(dff, col=\"program_number\", hue=\"program_number\", col_wrap=3, height=2, aspect=2, )\ng.map(sns.lineplot, \"total_distance\", \"py\", alpha=.9)\ng.add_legend()\ng.refline(x=line_finish)\n","metadata":{"_kg_hide-output":false,"execution":{"iopub.status.busy":"2022-10-28T18:04:16.241905Z","iopub.execute_input":"2022-10-28T18:04:16.242443Z","iopub.status.idle":"2022-10-28T18:04:19.676398Z","shell.execute_reply.started":"2022-10-28T18:04:16.242397Z","shell.execute_reply":"2022-10-28T18:04:19.674928Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"In summary, this graph shows the track width utilization of each horse throughout the race. The highest density of points indicates the position where it has remained the longest.","metadata":{}},{"cell_type":"code","source":"sns.set(rc = {'figure.figsize':(15,8)})\nrc = sns.stripplot(data=dff, x=\"program_number\", y=\"py\", hue=\"program_number\")","metadata":{"execution":{"iopub.status.busy":"2022-10-28T18:04:19.678245Z","iopub.execute_input":"2022-10-28T18:04:19.678792Z","iopub.status.idle":"2022-10-28T18:04:21.981399Z","shell.execute_reply.started":"2022-10-28T18:04:19.678743Z","shell.execute_reply":"2022-10-28T18:04:21.980146Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Animation race**\n\nWith all the data collected, an animation can be made of the race and the positions of the horses across the track and also their hierarchical position in the race at all times.\n\nRemember that positive \"py\" indicates the inner part of the track and negative \"py\" the outer part with respect to the curve.\n\nIn the animation, the size of the circles indicates the speed and the text indicates \"position in the race - program number\".","metadata":{}},{"cell_type":"markdown","source":"Compare the animation with the [Race in Youtube](https://youtu.be/GGexEKtN0FQ)","metadata":{}},{"cell_type":"code","source":"%%HTML\n\n<video src=\"https://storage.googleapis.com/kagglesdsdata/datasets/2586498/4414669/HorseRaceAqueduct.mp4?X-Goog-Algorithm=GOOG4-RSA-SHA256&X-Goog-Credential=databundle-worker-v2%40kaggle-161607.iam.gserviceaccount.com%2F20221028%2Fauto%2Fstorage%2Fgoog4_request&X-Goog-Date=20221028T175617Z&X-Goog-Expires=345600&X-Goog-SignedHeaders=host&X-Goog-Signature=132caa3afcbcfa2abcbe818d4fd706a123ed009f7584ddc13bb16f872294f19adb3cba7be248b86a31605f487e2b747c1f9105b279a8084b111de2d75d7e9b9f48a08773e3a42ab7efc5b49ae4f18ba3372a139c84cbd6bb0f3237cb786c7d0430e08f2172aa564b19fb57ab9436f5b3f2ff6b5e33f8fdcda42af7b81e2f3c6d2311cd52a1de52d66be7b4d56843bd1caf3ed20467d62e5e1ca9e5eb3415c1cc4535b80b14a3e13126dd0457127feadcfacfaff7160135f6562a181bad8a8a7abd49a32d2a546e2121376ed60cb19d237419174c5c97878b6d7b40682bc43ed7ce224af0a0f5df72fdb5df53d914c9085f1fdc0c7191fa205bd2d5cd12f7bdc2\" width=\"640\" height=\"480\" controls />\n","metadata":{"execution":{"iopub.status.busy":"2022-10-28T18:04:21.982795Z","iopub.execute_input":"2022-10-28T18:04:21.983168Z","iopub.status.idle":"2022-10-28T18:04:21.991506Z","shell.execute_reply.started":"2022-10-28T18:04:21.983132Z","shell.execute_reply":"2022-10-28T18:04:21.990434Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import plotly.express as px\n\ndef get_id(pos, pn):\n    pos = np.array(pos.values).tolist()\n    pn = np.array(pn.values).tolist()\n    ident = []\n    for i in range(len(pos)):\n        ident.append(f'{pos[i]}-{pn[i]}')\n    return ident\n    \n\nanimation_race = dff.copy(deep=True)\nanimation_race['speed1'] = animation_race['speed'] + 0.1\nanimation_race['id'] = get_id(dff[\"position\"], dff[\"program_number\"])\nanimation_race1 = animation_race[(animation_race['trakus_index'] % 1 == 0)]\nanimation_race1 = animation_race[(animation_race['latlon'] != animation_race['latlon_prev'])]\n\npx.scatter(animation_race1, x='px', y='py', animation_frame='trakus_index',\n           color='program_number',\n           text= 'id',\n           size = 'speed1',  \n           range_x = [animation_race1['px'].min()*1.1, animation_race1['px'].max()*1.1],\n           range_y = [animation_race1['py'].min()*1.1, animation_race1['py'].max()*1.1],\n           title='Race simulation scatter plot', width=640, height=480)\n","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-10-28T18:07:27.376179Z","iopub.execute_input":"2022-10-28T18:07:27.376951Z","iopub.status.idle":"2022-10-28T18:07:44.862512Z","shell.execute_reply.started":"2022-10-28T18:07:27.37689Z","shell.execute_reply":"2022-10-28T18:07:44.861275Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 5) Analysis Imaginary Race\n\nTo conclude: we will take several horses from different races with similar situations that have finished in first place and analyze the use of the width of the track to corroborate if there is a relationship between winning or losing.","metadata":{}},{"cell_type":"code","source":"# Identifying horses by race index and program number\n\ndef get_ident(race, pn):\n    race = np.array(race.values).tolist()\n    pn = np.array(pn.values).tolist()\n    ident = []\n    for i in range(len(race)):\n        ident.append(f'{race[i]}-{pn[i]}')\n    return ident\n\nimaginary_race = df[(df['track_id'] == 'Aqueduct') & (df['position_at_finish'] == 1) & \n                   (df['course_type'] == 'Dirt') & (df['track_condition'] == 'Fast') &\n                   (df['distance_id'] == 600) & (df['race_type'] == 'Maiden Claiming') &\n                   (df['race_index'] <= 320)].copy(deep=True)\nimaginary_race['ident'] = get_ident(imaginary_race['race_index'], imaginary_race['program_number'])\nimaginary_race","metadata":{"execution":{"iopub.status.busy":"2022-10-28T18:04:42.327451Z","iopub.execute_input":"2022-10-28T18:04:42.32791Z","iopub.status.idle":"2022-10-28T18:04:43.732731Z","shell.execute_reply.started":"2022-10-28T18:04:42.327868Z","shell.execute_reply":"2022-10-28T18:04:43.73143Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"imaginary_race['ident'].value_counts()","metadata":{"execution":{"iopub.status.busy":"2022-10-28T18:04:43.734115Z","iopub.execute_input":"2022-10-28T18:04:43.734513Z","iopub.status.idle":"2022-10-28T18:04:43.745428Z","shell.execute_reply.started":"2022-10-28T18:04:43.734474Z","shell.execute_reply":"2022-10-28T18:04:43.744053Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def get_distance_ir(coords1, coords2):\n    coords1 = np.array(coords1.values).tolist()\n    coords2 = np.array(coords2.values).tolist()    \n    dist = []\n    for i in range(len(coords1)):\n        dist.append(geo(coords1[i], coords2[i]))    \n    return dist\n\nimaginary_race['distance'] = get_distance_ir(imaginary_race['latlon'], imaginary_race['latlon_prev'])\nimaginary_race['total_distance'] = imaginary_race.groupby(['ident'])['distance'].transform(get_total_distance)\nimaginary_race['total_distance'] = imaginary_race['total_distance'] - imaginary_race['run_up_distance']\nimaginary_race['speed'] = imaginary_race['distance'] / 0.25\nimaginary_race['aceleration'] = get_aceleration(imaginary_race['distance'])\nimaginary_race['at_finish'] = imaginary_race['finish'] - imaginary_race['total_distance']\nimaginary_race","metadata":{"execution":{"iopub.status.busy":"2022-10-28T18:04:43.747125Z","iopub.execute_input":"2022-10-28T18:04:43.748425Z","iopub.status.idle":"2022-10-28T18:04:43.85744Z","shell.execute_reply.started":"2022-10-28T18:04:43.748372Z","shell.execute_reply":"2022-10-28T18:04:43.856225Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**The Imaginary Race**","metadata":{}},{"cell_type":"code","source":"sns.set(rc = {'figure.figsize':(15,8)})\nrc = sns.scatterplot(data=imaginary_race, x=\"longitude\", y=\"latitude\", hue=\"ident\")","metadata":{"execution":{"iopub.status.busy":"2022-10-28T18:04:43.859641Z","iopub.execute_input":"2022-10-28T18:04:43.860465Z","iopub.status.idle":"2022-10-28T18:04:44.801016Z","shell.execute_reply.started":"2022-10-28T18:04:43.860413Z","shell.execute_reply":"2022-10-28T18:04:44.799737Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"line_finish = list(imaginary_race['finish'].values)[0]\nprint(line_finish)\ng2 = sns.FacetGrid(imaginary_race, col=\"ident\", hue=\"ident\", col_wrap=3, height=2, aspect=2, )\ng2.map(sns.lineplot, \"total_distance\", \"py\", alpha=.9)\ng2.add_legend()\ng2.refline(x=line_finish)\n","metadata":{"_kg_hide-output":true,"execution":{"iopub.status.busy":"2022-10-28T18:04:44.802713Z","iopub.execute_input":"2022-10-28T18:04:44.8032Z","iopub.status.idle":"2022-10-28T18:04:49.426526Z","shell.execute_reply.started":"2022-10-28T18:04:44.80315Z","shell.execute_reply":"2022-10-28T18:04:49.425105Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"From this graph it can be seen that the horses in this imaginary race use a small space of the width of the track during the entire race, except for a few exceptions.","metadata":{}},{"cell_type":"code","source":"sns.set(rc = {'figure.figsize':(15,8)})\nrc = sns.stripplot(data=imaginary_race, x=\"ident\", y=\"py\", hue=\"ident\")","metadata":{"execution":{"iopub.status.busy":"2022-10-28T18:04:49.428127Z","iopub.execute_input":"2022-10-28T18:04:49.428632Z","iopub.status.idle":"2022-10-28T18:04:53.698097Z","shell.execute_reply.started":"2022-10-28T18:04:49.428585Z","shell.execute_reply":"2022-10-28T18:04:53.697103Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Animation Imaginary Race**\n\nTo make the animation of this imaginary race, it is necessary to calculate again the average position between all the horses to obtain the relation between all of them and to obtain the real \"px\" and \"py\" of this race in particular and not those that they bring from their respective races related to the horses of those other races.\n\n\nAgain in the animation of this imaginary race will appear on each horse: position in this race - identification of the horse (race index - program number)","metadata":{}},{"cell_type":"code","source":"animation_imaginary_race = imaginary_race.copy(deep=True)\nanimation_imaginary_race['speed1'] = imaginary_race['speed'] + 0.1\nanimation_imaginary_race['latlon_mean'] = animation_imaginary_race.groupby(['trakus_index'])['latlon'].transform(lambda x: get_latlon_mean(x.values))\nanimation_imaginary_race['pos_abs'] = get_position_absolute(animation_imaginary_race['latlon'], animation_imaginary_race['latlon_mean'], animation_imaginary_race['angle'])\nanimation_imaginary_race['px'] = np.stack(animation_imaginary_race['pos_abs'].values).T.tolist()[1]\nanimation_imaginary_race['py'] = np.stack(animation_imaginary_race['pos_abs'].values).T.tolist()[0]\nanimation_imaginary_race = animation_imaginary_race.sort_values(['trakus_index', 'px'])\nanimation_imaginary_race['position'] = 0\nanimation_imaginary_race['position'] = animation_imaginary_race.groupby(['trakus_index'])['position'].transform(get_pos)\nanimation_imaginary_race['id'] = get_id(animation_imaginary_race[\"position\"], animation_imaginary_race[\"ident\"])\npx.scatter(animation_imaginary_race, x='px', y='py', animation_frame='trakus_index',\n           color='ident',\n           text= 'id',\n           size = 'speed1',  \n           range_x = [animation_imaginary_race['px'].min()*1.1, animation_imaginary_race['px'].max()*1.1],\n           range_y = [animation_imaginary_race['py'].min()*1.1, animation_imaginary_race['py'].max()*1.1],\n           title='Race simulation scatter plot', width=800, height=600)","metadata":{"execution":{"iopub.status.busy":"2022-10-28T18:04:53.699543Z","iopub.execute_input":"2022-10-28T18:04:53.700428Z","iopub.status.idle":"2022-10-28T18:05:11.135593Z","shell.execute_reply.started":"2022-10-28T18:04:53.700391Z","shell.execute_reply":"2022-10-28T18:05:11.134298Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Conclusions","metadata":{}},{"cell_type":"markdown","source":"According to the data collected and the graphs, it is very likely that keeping the horse in a narrow corridor for the entire race or a large part of it will result in a good result.\nOn the other hand, moving widely across the width of the track is a reason for poor results.","metadata":{}}]}