{"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":"code","source":"import numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nfrom tqdm import tqdm # for progress bar","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-11-05T12:41:50.662848Z","iopub.execute_input":"2022-11-05T12:41:50.663317Z","iopub.status.idle":"2022-11-05T12:41:50.685573Z","shell.execute_reply.started":"2022-11-05T12:41:50.663242Z","shell.execute_reply":"2022-11-05T12:41:50.684417Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Incomplete Race Tagging\n\nThe Injury tagging is based on hypothesis that, if jockey did not covered the race distance, it means that jockey haven't completed the race which indicates that there is some problem. Therefore instead of finding Injury data, I have tagged jokey race with only 25-30% completion of race.\n\n\nWe have given Total Race Distances. From Latitude-Longitude we can find out approximate distace cover by Jockey.\n\nHere I am saying approximate distance since, the staring point of a race is missing. We directly have our first lat-long point after 0.25 second. Also with the help of 2 lat-long  points, we can calculate straight distance between them, but the race track is curve. So Total  calculated covered distance of Jockey will be slightly less than Total race distance.","metadata":{}},{"cell_type":"code","source":"# Lets read all the tables, and combine the same.\n\njockey_df = pd.read_csv('/kaggle/input/big-data-derby-2022/nyra_start_table.csv',\n                        names=['track_id', 'race_date','race_number','program_number',\n                               'weight_carried','jockey','odds','position_at_finish'])\n\nrace_df = pd.read_csv('/kaggle/input/big-data-derby-2022/nyra_race_table.csv')\ntrack_df = pd.read_csv('/kaggle/input/big-data-derby-2022/nyra_tracking_table.csv')\n\ncombine_df = track_df.merge(race_df, on = ['track_id','race_date','race_number']) \\\n.merge(jockey_df, on = ['track_id', 'race_date', 'race_number', 'program_number'])\n\n","metadata":{"execution":{"iopub.status.busy":"2022-11-05T12:41:50.692023Z","iopub.execute_input":"2022-11-05T12:41:50.692397Z","iopub.status.idle":"2022-11-05T12:42:02.410503Z","shell.execute_reply.started":"2022-11-05T12:41:50.692348Z","shell.execute_reply":"2022-11-05T12:42:02.409587Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# saving index in a new column. It will help us to merge multiple dataframes later.\ncombine_df['index'] = combine_df.index \n\n\n# lets create one variable RaceGrpID which will be combination of Track+Date+RaceNo+ProgramNo.\n# so instead of grouping by these 4 column, we will group by single column.\ncombine_df['RaceGrpID']=combine_df.track_id+'_'+combine_df.race_date+'_RN'+combine_df.race_number.astype(str)\\\n+'_PN'+combine_df.program_number.astype(str)\n\n# removing empty space from RaceGrpID column.\ncombine_df['RaceGrpID'] = combine_df['RaceGrpID'].str.strip()","metadata":{"execution":{"iopub.status.busy":"2022-11-05T12:42:02.412125Z","iopub.execute_input":"2022-11-05T12:42:02.412411Z","iopub.status.idle":"2022-11-05T12:42:08.773787Z","shell.execute_reply.started":"2022-11-05T12:42:02.412364Z","shell.execute_reply":"2022-11-05T12:42:08.772748Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"combine_df.head()","metadata":{"execution":{"iopub.status.busy":"2022-11-05T12:42:08.774866Z","iopub.execute_input":"2022-11-05T12:42:08.775121Z","iopub.status.idle":"2022-11-05T12:42:08.801150Z","shell.execute_reply.started":"2022-11-05T12:42:08.775098Z","shell.execute_reply":"2022-11-05T12:42:08.799544Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Lets study one race taken at the place 'AQU', on 1st jan 2019, Race Number 9 and Pragram (jockey) No 6. \n\nTrakus index is the 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. So lets sort data by trakus index and analyse latitude and longitude\n","metadata":{}},{"cell_type":"code","source":"combine_df[combine_df.RaceGrpID=='AQU_2019-01-01_RN9_PN6'].sort_values('trakus_index')","metadata":{"execution":{"iopub.status.busy":"2022-11-05T12:42:08.803058Z","iopub.execute_input":"2022-11-05T12:42:08.803357Z","iopub.status.idle":"2022-11-05T12:42:09.937519Z","shell.execute_reply.started":"2022-11-05T12:42:08.803331Z","shell.execute_reply":"2022-11-05T12:42:09.936367Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Using Latitue and Longitute information, Calculate the total distance covered by Jockey","metadata":{}},{"cell_type":"code","source":"# Python 3 utility function to calculate Distance Between Two Points on Earth\nfrom math import radians, cos, sin, asin, sqrt\ndef distance(lat1, lat2, lon1, lon2):\n     \n    # The math module contains a function named\n    # radians which converts from degrees to radians.\n    lon1 = radians(lon1)\n    lon2 = radians(lon2)\n    lat1 = radians(lat1)\n    lat2 = radians(lat2)\n      \n    # Haversine formula\n    dlon = lon2 - lon1\n    dlat = lat2 - lat1\n    a = sin(dlat / 2)**2 + cos(lat1) * cos(lat2) * sin(dlon / 2)**2\n \n    c = 2 * asin(sqrt(a))\n    \n    # Radius of earth in kilometers. Use 3956 for miles\n    r = 6371\n      \n    # calculate the result\n    return(c * r)\n\n# distance between Mumbai to Delhi is 1,419.8 km. Lets check if this function is calculating correct answer or not?\nlat1 = 19.0760 # mumbai latitude\nlat2 = 28.7041 # delhi latitude\n\nlon1 = 72.8777 # mumbai longitude\nlon2 =  77.1025 # delhi longitude\n\nprint(distance(lat1, lat2, lon1, lon2), \"K.M\")\n\n#Yes. It is calculating approximatly correct distance. Therefore we can use this method.\n#For each group we will calculate the distance.","metadata":{"execution":{"iopub.status.busy":"2022-11-05T12:42:09.938660Z","iopub.execute_input":"2022-11-05T12:42:09.938962Z","iopub.status.idle":"2022-11-05T12:42:09.949547Z","shell.execute_reply.started":"2022-11-05T12:42:09.938931Z","shell.execute_reply":"2022-11-05T12:42:09.948243Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"hop_dist_dict = {} # distance between 2 trakus\ncumulative_dist_dict={} # cumulative distance from start to current point\nfor grp, rows in tqdm(combine_df.groupby(['RaceGrpID'])):\n    #print(grp)\n    rows = rows.sort_values(['trakus_index']).reset_index(drop= False)\n    prev=cur=0\n    cum_dist = 0\n    for cur in range(rows.shape[0]):\n        if cur == 0:\n            hop_dist_dict[rows.loc[cur,'index']]=0\n            cumulative_dist_dict[rows.loc[cur,'index']]=cum_dist\n            continue\n        prev = cur-1\n        \n        dist = distance(rows.loc[prev,'latitude'], \n                        rows.loc[cur,'latitude'], \n                        rows.loc[prev,'longitude'], \n                        rows.loc[cur,'longitude']) * 100 #(converting KM to meter)\n        cum_dist = cum_dist+dist\n        hop_dist_dict[rows.loc[cur,'index']]=dist\n        cumulative_dist_dict[rows.loc[cur,'index']]=cum_dist\n\ncombine_df['HopDist']=combine_df['index'].apply(lambda x: hop_dist_dict.get(x))\ncombine_df['CumDist']=combine_df['index'].apply(lambda x: cumulative_dist_dict.get(x))","metadata":{"execution":{"iopub.status.busy":"2022-11-05T12:49:04.894572Z","iopub.execute_input":"2022-11-05T12:49:04.894933Z","iopub.status.idle":"2022-11-05T12:54:44.049353Z","shell.execute_reply.started":"2022-11-05T12:49:04.894904Z","shell.execute_reply":"2022-11-05T12:54:44.047764Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# the race distance is in feet. Therefore we will convert the distance into the unit - meter\nfeet_to_meter_factor = 1/3.281\ncombine_df['DistanceMtr']=combine_df.distance_id* feet_to_meter_factor","metadata":{"execution":{"iopub.status.busy":"2022-11-05T12:54:44.051307Z","iopub.execute_input":"2022-11-05T12:54:44.051682Z","iopub.status.idle":"2022-11-05T12:54:44.073662Z","shell.execute_reply.started":"2022-11-05T12:54:44.051652Z","shell.execute_reply":"2022-11-05T12:54:44.072707Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"combine_df.head()","metadata":{"execution":{"iopub.status.busy":"2022-11-05T12:54:44.074718Z","iopub.execute_input":"2022-11-05T12:54:44.075014Z","iopub.status.idle":"2022-11-05T12:54:44.101144Z","shell.execute_reply.started":"2022-11-05T12:54:44.074985Z","shell.execute_reply":"2022-11-05T12:54:44.099953Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"combine_df[combine_df.RaceGrpID=='AQU_2019-01-01_RN9_PN6'].sort_values('trakus_index')\n# we get aproximate distance, since we do not know the start point of race. we have 1st record after 0.25 sec\n\n# we do not have lat,lon of 0th second.\n# also track is circular, but we are calculating straight distance between 2 points. hence we loose there some distance.\n# so cumulative total distance is aproximate distance","metadata":{"execution":{"iopub.status.busy":"2022-11-05T12:54:44.103192Z","iopub.execute_input":"2022-11-05T12:54:44.103533Z","iopub.status.idle":"2022-11-05T12:54:44.494471Z","shell.execute_reply.started":"2022-11-05T12:54:44.103502Z","shell.execute_reply":"2022-11-05T12:54:44.493098Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# lets calculate speed \nconversion_factor = (0.6/0.25)\ncombine_df['SpeedMPH'] = combine_df['HopDist']*conversion_factor","metadata":{"execution":{"iopub.status.busy":"2022-11-05T12:55:28.631216Z","iopub.execute_input":"2022-11-05T12:55:28.631595Z","iopub.status.idle":"2022-11-05T12:55:28.656014Z","shell.execute_reply.started":"2022-11-05T12:55:28.631562Z","shell.execute_reply":"2022-11-05T12:55:28.654953Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"combine_df.head()","metadata":{"execution":{"iopub.status.busy":"2022-11-05T12:55:39.021166Z","iopub.execute_input":"2022-11-05T12:55:39.022072Z","iopub.status.idle":"2022-11-05T12:55:39.054601Z","shell.execute_reply.started":"2022-11-05T12:55:39.022031Z","shell.execute_reply":"2022-11-05T12:55:39.052987Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"combine_df[combine_df.RaceGrpID=='AQU_2019-01-01_RN9_PN6'].sort_values('trakus_index').\\\n    reset_index(drop = True).SpeedMPH.plot()","metadata":{"execution":{"iopub.status.busy":"2022-11-05T12:56:06.269045Z","iopub.execute_input":"2022-11-05T12:56:06.269440Z","iopub.status.idle":"2022-11-05T12:56:06.853180Z","shell.execute_reply.started":"2022-11-05T12:56:06.269403Z","shell.execute_reply":"2022-11-05T12:56:06.852247Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"In above plot horse's speed is incresing ","metadata":{"execution":{"iopub.status.busy":"2022-11-03T16:13:48.894654Z","iopub.execute_input":"2022-11-03T16:13:48.895077Z","iopub.status.idle":"2022-11-03T16:13:48.902093Z","shell.execute_reply.started":"2022-11-03T16:13:48.895042Z","shell.execute_reply":"2022-11-03T16:13:48.900559Z"}}},{"cell_type":"code","source":"# We have total distance coverd by jockey and total race track distance.\n# lets calculate remaining distance in percentage\ncombine_df.loc[:,'RemainingDist'] = ((combine_df.DistanceMtr - combine_df.CumDist)*100)/ combine_df.DistanceMtr","metadata":{"execution":{"iopub.status.busy":"2022-11-05T12:56:10.679311Z","iopub.execute_input":"2022-11-05T12:56:10.679779Z","iopub.status.idle":"2022-11-05T12:56:10.714625Z","shell.execute_reply.started":"2022-11-05T12:56:10.679727Z","shell.execute_reply":"2022-11-05T12:56:10.713817Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# since there are total 297 trakus, we will print the last point of the race.\ncombine_df[(combine_df.RaceGrpID=='AQU_2019-01-01_RN9_PN6') & (combine_df.trakus_index ==297)].sort_values('trakus_index')","metadata":{"execution":{"iopub.status.busy":"2022-11-05T12:56:14.876269Z","iopub.execute_input":"2022-11-05T12:56:14.876668Z","iopub.status.idle":"2022-11-05T12:56:15.290655Z","shell.execute_reply.started":"2022-11-05T12:56:14.876640Z","shell.execute_reply":"2022-11-05T12:56:15.289402Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# remaining dist is 27%\n# lets check the other's\n\ngroupwise_remaining_dist = combine_df.groupby('RaceGrpID').min()['RemainingDist']","metadata":{"execution":{"iopub.status.busy":"2022-11-05T12:56:18.076193Z","iopub.execute_input":"2022-11-05T12:56:18.076586Z","iopub.status.idle":"2022-11-05T12:56:31.397046Z","shell.execute_reply.started":"2022-11-05T12:56:18.076555Z","shell.execute_reply":"2022-11-05T12:56:31.396039Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# it seems majority has 28-29% race incompletion rate.\ndisplay(groupwise_remaining_dist)\ndisplay(groupwise_remaining_dist.describe())\n# 75 % data lies inside 28.05","metadata":{"execution":{"iopub.status.busy":"2022-11-05T12:57:00.379098Z","iopub.execute_input":"2022-11-05T12:57:00.379452Z","iopub.status.idle":"2022-11-05T12:57:00.402150Z","shell.execute_reply.started":"2022-11-05T12:57:00.379421Z","shell.execute_reply":"2022-11-05T12:57:00.401132Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"groupwise_remaining_dist.sort_values().reset_index().plot()","metadata":{"execution":{"iopub.status.busy":"2022-11-05T12:57:06.253059Z","iopub.execute_input":"2022-11-05T12:57:06.253423Z","iopub.status.idle":"2022-11-05T12:57:06.442219Z","shell.execute_reply.started":"2022-11-05T12:57:06.253374Z","shell.execute_reply":"2022-11-05T12:57:06.440974Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"jockey_incomeplete_race = groupwise_remaining_dist[groupwise_remaining_dist>=31].sort_values()\njockey_incomeplete_race = jockey_incomeplete_race.reset_index()\njockey_incomeplete_race['IncompleteRace']=True\ndisplay(jockey_incomeplete_race)\njockey_incomeplete_race.RemainingDist = None\njockey_incomeplete_race.drop('RemainingDist', axis=1, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-11-05T12:57:06.585405Z","iopub.execute_input":"2022-11-05T12:57:06.585758Z","iopub.status.idle":"2022-11-05T12:57:06.604885Z","shell.execute_reply.started":"2022-11-05T12:57:06.585730Z","shell.execute_reply":"2022-11-05T12:57:06.603426Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"jockey_incomeplete_race","metadata":{"execution":{"iopub.status.busy":"2022-11-05T12:57:07.232136Z","iopub.execute_input":"2022-11-05T12:57:07.233052Z","iopub.status.idle":"2022-11-05T12:57:07.245765Z","shell.execute_reply.started":"2022-11-05T12:57:07.232991Z","shell.execute_reply":"2022-11-05T12:57:07.244751Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"combine_df = combine_df.merge(jockey_incomeplete_race, on = 'RaceGrpID', how = 'left' )","metadata":{"execution":{"iopub.status.busy":"2022-11-05T12:57:08.079478Z","iopub.execute_input":"2022-11-05T12:57:08.080565Z","iopub.status.idle":"2022-11-05T12:57:15.015688Z","shell.execute_reply.started":"2022-11-05T12:57:08.080529Z","shell.execute_reply":"2022-11-05T12:57:15.014694Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"combine_df.IncompleteRace.fillna(False, inplace = True)","metadata":{"execution":{"iopub.status.busy":"2022-11-05T12:57:15.017691Z","iopub.execute_input":"2022-11-05T12:57:15.018870Z","iopub.status.idle":"2022-11-05T12:57:15.491858Z","shell.execute_reply.started":"2022-11-05T12:57:15.018820Z","shell.execute_reply":"2022-11-05T12:57:15.490648Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"jockey_race_df = combine_df[['RaceGrpID','track_id','race_date','race_number','program_number',\n                             'IncompleteRace','course_type','track_condition','run_up_distance',\n                            'race_type','purse','post_time','weight_carried','DistanceMtr']].\\\n                            drop_duplicates().reset_index(drop = True)","metadata":{"execution":{"iopub.status.busy":"2022-11-05T12:57:15.500335Z","iopub.execute_input":"2022-11-05T12:57:15.500833Z","iopub.status.idle":"2022-11-05T12:57:20.132073Z","shell.execute_reply.started":"2022-11-05T12:57:15.500798Z","shell.execute_reply":"2022-11-05T12:57:20.130788Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"display(jockey_race_df)\njockey_race_df.IncompleteRace.value_counts()","metadata":{"execution":{"iopub.status.busy":"2022-11-05T12:59:52.963342Z","iopub.execute_input":"2022-11-05T12:59:52.964475Z","iopub.status.idle":"2022-11-05T12:59:52.991705Z","shell.execute_reply.started":"2022-11-05T12:59:52.964434Z","shell.execute_reply":"2022-11-05T12:59:52.990793Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Conclusion\nOut of 14915 jockey race, it has found that approximately 152 race are incomplete due to some reason. ","metadata":{}}]}