{"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":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-10-01T04:44:59.867263Z","iopub.execute_input":"2022-10-01T04:44:59.867708Z","iopub.status.idle":"2022-10-01T04:44:59.879443Z","shell.execute_reply.started":"2022-10-01T04:44:59.867673Z","shell.execute_reply":"2022-10-01T04:44:59.878258Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Week1 \n\nGoal: The goal of this competition is to analyze horse racing tactics, drafting strategies, and path efficiency. You will develop a model using never-before-released coordinate data along with basic race information.\n\nData: \n- nyra_start_table.csv - horse/jockey race data  >> 1 row per hourse / jockey\n- nyra_race_table.csv - racetrack race data >> 1 row per race \n- nyra_tracking_table.csv - tracking data >> 1 row for every track at .25 seconds (race details) \n\nnyra_2019_complete.csv - combined table of three above files \n\n\n[Data Catalog](https://www.kaggle.com/competitions/big-data-derby-2022/data) \n","metadata":{}},{"cell_type":"markdown","source":"## Week2","metadata":{}},{"cell_type":"markdown","source":"1 - import race table and show first 5 rows\n\n2 - apply EDA to race table with the following questions\n\na) how many races we have in total in 2019? \n\nb) **Numerical Variables:** what is the minimum/maximum/average/median (distance ,run_up_distance, and purse) of a race ? \n\nc) **Categorical Variables:** how many races per (course_type, track_condition, and race_type) \n\nd) **Date Variable** how many races played per week / month in 2019 ? ","metadata":{}},{"cell_type":"markdown","source":"Solution below","metadata":{}},{"cell_type":"markdown","source":"1 - import race table and show first 5 rows","metadata":{}},{"cell_type":"code","source":"race_table = pd.read_csv('/kaggle/input/big-data-derby-2022/nyra_race_table.csv')","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-10-01T04:44:59.881654Z","iopub.execute_input":"2022-10-01T04:44:59.882040Z","iopub.status.idle":"2022-10-01T04:44:59.914885Z","shell.execute_reply.started":"2022-10-01T04:44:59.881989Z","shell.execute_reply":"2022-10-01T04:44:59.913880Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_table.head()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-10-01T04:44:59.916126Z","iopub.execute_input":"2022-10-01T04:44:59.917146Z","iopub.status.idle":"2022-10-01T04:44:59.940977Z","shell.execute_reply.started":"2022-10-01T04:44:59.917104Z","shell.execute_reply":"2022-10-01T04:44:59.940140Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"a) how many races we have in total in 2019?","metadata":{}},{"cell_type":"code","source":"race_table.shape[0]","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-10-01T04:44:59.941984Z","iopub.execute_input":"2022-10-01T04:44:59.942931Z","iopub.status.idle":"2022-10-01T04:44:59.949271Z","shell.execute_reply.started":"2022-10-01T04:44:59.942893Z","shell.execute_reply":"2022-10-01T04:44:59.948091Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"c) **Numerical Variables:** what is the minimum/maximum/average/median (distance ,run_up_distance, and purse) of a race ? \n","metadata":{}},{"cell_type":"code","source":"#Numerical value \nrace_table.describe() ## Show 5 rows of the ","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-10-01T04:44:59.953068Z","iopub.execute_input":"2022-10-01T04:44:59.953733Z","iopub.status.idle":"2022-10-01T04:44:59.994446Z","shell.execute_reply.started":"2022-10-01T04:44:59.953689Z","shell.execute_reply":"2022-10-01T04:44:59.993218Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"c) **Categorical Variables:** how many races per (course_type, track_condition, and race_type) ","metadata":{}},{"cell_type":"code","source":"race_table.course_type.value_counts()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-10-01T04:44:59.995814Z","iopub.execute_input":"2022-10-01T04:44:59.996152Z","iopub.status.idle":"2022-10-01T04:45:00.006076Z","shell.execute_reply.started":"2022-10-01T04:44:59.996115Z","shell.execute_reply":"2022-10-01T04:45:00.004749Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_table.track_condition.value_counts()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-10-01T04:45:00.007230Z","iopub.execute_input":"2022-10-01T04:45:00.007537Z","iopub.status.idle":"2022-10-01T04:45:00.020692Z","shell.execute_reply.started":"2022-10-01T04:45:00.007509Z","shell.execute_reply":"2022-10-01T04:45:00.019542Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_table.race_type.value_counts()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-10-01T04:45:00.022839Z","iopub.execute_input":"2022-10-01T04:45:00.023429Z","iopub.status.idle":"2022-10-01T04:45:00.036306Z","shell.execute_reply.started":"2022-10-01T04:45:00.023376Z","shell.execute_reply":"2022-10-01T04:45:00.034911Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"d) **Date Variable** how many races played per week / month in 2019 ? ","metadata":{}},{"cell_type":"code","source":"race_table.info()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-10-01T04:45:00.037573Z","iopub.execute_input":"2022-10-01T04:45:00.037942Z","iopub.status.idle":"2022-10-01T04:45:00.061221Z","shell.execute_reply.started":"2022-10-01T04:45:00.037911Z","shell.execute_reply":"2022-10-01T04:45:00.060041Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_table.race_date = pd.to_datetime(race_table.race_date)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-10-01T04:45:00.064865Z","iopub.execute_input":"2022-10-01T04:45:00.065214Z","iopub.status.idle":"2022-10-01T04:45:00.075475Z","shell.execute_reply.started":"2022-10-01T04:45:00.065182Z","shell.execute_reply":"2022-10-01T04:45:00.074119Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## Races per Month \nrace_table.race_date.dt.strftime('%Y-%m').value_counts().sort_index()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-10-01T04:45:00.079424Z","iopub.execute_input":"2022-10-01T04:45:00.079794Z","iopub.status.idle":"2022-10-01T04:45:00.104257Z","shell.execute_reply.started":"2022-10-01T04:45:00.079743Z","shell.execute_reply":"2022-10-01T04:45:00.102827Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## Races per Week\nrace_table.race_date.dt.strftime('%Y-%U').value_counts().sort_index()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-10-01T04:45:00.106879Z","iopub.execute_input":"2022-10-01T04:45:00.107712Z","iopub.status.idle":"2022-10-01T04:45:00.128646Z","shell.execute_reply.started":"2022-10-01T04:45:00.107672Z","shell.execute_reply":"2022-10-01T04:45:00.127463Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Week3 \nWhat is an injury? \n(remember the goal is to decrease the injury of horses) \nIn this week we try to define what is an injury. and then see the percentage of injuries in the races based on the breakdowns of week 2\n","metadata":{"execution":{"iopub.status.busy":"2022-09-10T04:41:22.296325Z","iopub.execute_input":"2022-09-10T04:41:22.297286Z","iopub.status.idle":"2022-09-10T04:41:22.308948Z","shell.execute_reply.started":"2022-09-10T04:41:22.297239Z","shell.execute_reply":"2022-09-10T04:41:22.307696Z"}}},{"cell_type":"markdown","source":"Import the complete dataset","metadata":{}},{"cell_type":"code","source":"dataset = pd.read_csv('/kaggle/input/big-data-derby-2022/nyra_2019_complete.csv',header = None)\ndataset.columns = ['track_id', 'race_date', 'race_number', 'program_number',\n       'trakus_index', 'latitude', 'longitude', 'distance_id', 'course_type',\n       'track_condition', 'run_up_distance', 'race_type', 'purse', 'post_time',\n       'weight_carried', 'jockey', 'odds','']","metadata":{"_kg_hide-input":false,"execution":{"iopub.status.busy":"2022-10-01T04:45:00.130281Z","iopub.execute_input":"2022-10-01T04:45:00.130830Z","iopub.status.idle":"2022-10-01T04:45:19.436764Z","shell.execute_reply.started":"2022-10-01T04:45:00.130789Z","shell.execute_reply":"2022-10-01T04:45:19.435625Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"dataset.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-01T04:45:19.438385Z","iopub.execute_input":"2022-10-01T04:45:19.439008Z","iopub.status.idle":"2022-10-01T04:45:19.458588Z","shell.execute_reply.started":"2022-10-01T04:45:19.438971Z","shell.execute_reply":"2022-10-01T04:45:19.457489Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# defining unique race ID\ndataset['race_id'] = dataset.track_id + dataset.race_date + dataset.race_number.astype(str)\n\n# defining unique horse ID\ndataset['horse_id'] = dataset['race_id'] + dataset['program_number'].astype(str)","metadata":{"execution":{"iopub.status.busy":"2022-10-01T04:45:19.460252Z","iopub.execute_input":"2022-10-01T04:45:19.460669Z","iopub.status.idle":"2022-10-01T04:45:24.588033Z","shell.execute_reply.started":"2022-10-01T04:45:19.460634Z","shell.execute_reply":"2022-10-01T04:45:24.586632Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Now let's try to get the number of trackes per horse in each race","metadata":{}},{"cell_type":"code","source":"race_horse = dataset.groupby(['horse_id','race_id']).count()[['track_id']].reset_index()\nrace_horse.columns = ['horse_id','race_id','count_tracks_for_horse']\n","metadata":{"execution":{"iopub.status.busy":"2022-10-01T04:45:24.589679Z","iopub.execute_input":"2022-10-01T04:45:24.590191Z","iopub.status.idle":"2022-10-01T04:45:29.378692Z","shell.execute_reply.started":"2022-10-01T04:45:24.590131Z","shell.execute_reply":"2022-10-01T04:45:29.377566Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"horse id = AQU2019-01-0111 has been tracked 316 times during the race of  AQU2019-01-011 ","metadata":{}},{"cell_type":"code","source":"race_horse.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-01T04:45:29.380273Z","iopub.execute_input":"2022-10-01T04:45:29.380940Z","iopub.status.idle":"2022-10-01T04:45:29.392333Z","shell.execute_reply.started":"2022-10-01T04:45:29.380901Z","shell.execute_reply":"2022-10-01T04:45:29.391119Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race = race_horse.groupby(['race_id']).max()[['count_tracks_for_horse']].reset_index()\nrace.columns = ['race_id','max_tracks_per_race']\n","metadata":{"execution":{"iopub.status.busy":"2022-10-01T04:45:29.393841Z","iopub.execute_input":"2022-10-01T04:45:29.394755Z","iopub.status.idle":"2022-10-01T04:45:29.610985Z","shell.execute_reply.started":"2022-10-01T04:45:29.394718Z","shell.execute_reply":"2022-10-01T04:45:29.609853Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Race AQU2019-01-011 has maximum number of tracks of 316 (i.e. each horse tracked 316 if they completed the race) ","metadata":{}},{"cell_type":"code","source":"race.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-01T04:45:29.612372Z","iopub.execute_input":"2022-10-01T04:45:29.612799Z","iopub.status.idle":"2022-10-01T04:45:29.623314Z","shell.execute_reply.started":"2022-10-01T04:45:29.612750Z","shell.execute_reply":"2022-10-01T04:45:29.621979Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"if the horse was tracked less times than the maximum tracks, this means that the horse didn't complete the race >> the horse got injured examples below: ","metadata":{}},{"cell_type":"code","source":"race_horse = race_horse.merge(race)\nrace_horse['injury'] =  race_horse.count_tracks_for_horse < race_horse.max_tracks_per_race \nrace_horse[race_horse.injury].head()","metadata":{"execution":{"iopub.status.busy":"2022-10-01T04:45:29.624910Z","iopub.execute_input":"2022-10-01T04:45:29.625701Z","iopub.status.idle":"2022-10-01T04:45:29.654196Z","shell.execute_reply.started":"2022-10-01T04:45:29.625656Z","shell.execute_reply":"2022-10-01T04:45:29.653018Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race  = race_horse.groupby('race_id').agg(count_horses = ('horse_id','count') ,has_injury = ('injury','max')) ","metadata":{"execution":{"iopub.status.busy":"2022-10-01T04:45:29.655762Z","iopub.execute_input":"2022-10-01T04:45:29.656139Z","iopub.status.idle":"2022-10-01T04:45:29.682707Z","shell.execute_reply.started":"2022-10-01T04:45:29.656106Z","shell.execute_reply":"2022-10-01T04:45:29.681343Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Now, remember we had 2000 race, now we have also a columns tell us the number of the horses in each race, and if the race has injury or not ? ","metadata":{}},{"cell_type":"code","source":"race","metadata":{"execution":{"iopub.status.busy":"2022-10-01T04:45:29.684431Z","iopub.execute_input":"2022-10-01T04:45:29.685241Z","iopub.status.idle":"2022-10-01T04:45:29.700205Z","shell.execute_reply.started":"2022-10-01T04:45:29.685195Z","shell.execute_reply":"2022-10-01T04:45:29.699373Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_table['race_id'] = race_table.track_id + race_table.race_date.astype(str) + race_table.race_number.astype(str)\nrace_table = race_table.merge(race.reset_index()) ","metadata":{"execution":{"iopub.status.busy":"2022-10-01T04:45:29.701399Z","iopub.execute_input":"2022-10-01T04:45:29.701911Z","iopub.status.idle":"2022-10-01T04:45:29.733977Z","shell.execute_reply.started":"2022-10-01T04:45:29.701879Z","shell.execute_reply":"2022-10-01T04:45:29.732726Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"How many races had injury in 2019 ?","metadata":{}},{"cell_type":"code","source":"race_table.has_injury.sum()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-10-01T04:45:29.735312Z","iopub.execute_input":"2022-10-01T04:45:29.735770Z","iopub.status.idle":"2022-10-01T04:45:29.744569Z","shell.execute_reply.started":"2022-10-01T04:45:29.735726Z","shell.execute_reply":"2022-10-01T04:45:29.743286Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"what is the percentages races had injury in 2019 ?  answer (243 / 2000) ","metadata":{}},{"cell_type":"code","source":"race_table.has_injury.mean()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-10-01T04:45:29.746326Z","iopub.execute_input":"2022-10-01T04:45:29.746837Z","iopub.status.idle":"2022-10-01T04:45:29.761286Z","shell.execute_reply.started":"2022-10-01T04:45:29.746764Z","shell.execute_reply":"2022-10-01T04:45:29.760143Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"What is the track condition that is linked with highest injury rate? ","metadata":{}},{"cell_type":"code","source":"race_table.groupby('track_condition').mean()['has_injury'].sort_values()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-10-01T04:45:29.763761Z","iopub.execute_input":"2022-10-01T04:45:29.764883Z","iopub.status.idle":"2022-10-01T04:45:29.783385Z","shell.execute_reply.started":"2022-10-01T04:45:29.764835Z","shell.execute_reply":"2022-10-01T04:45:29.781948Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_table.columns","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-10-01T04:45:29.790224Z","iopub.execute_input":"2022-10-01T04:45:29.791455Z","iopub.status.idle":"2022-10-01T04:45:29.798665Z","shell.execute_reply.started":"2022-10-01T04:45:29.791414Z","shell.execute_reply":"2022-10-01T04:45:29.797572Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"from the following columns: \n\n'race_date',\n'race_number',\n'distance_id',\n'course_type',\n'track_condition',\n'run_up_distance',\n'race_type',\n'purse',\n'post_time',\n'count_horses'\n\nWhat do u think the variable that is linked with the highest rate of injuries? ","metadata":{}},{"cell_type":"markdown","source":"## Week5 \nSpeed and location: In this week we target to track the horses locations and speed at any point of time of the race. ","metadata":{}},{"cell_type":"code","source":"import seaborn as sns\nfrom math import sin, cos, sqrt, atan2, radians\n","metadata":{"execution":{"iopub.status.busy":"2022-10-01T04:45:29.800207Z","iopub.execute_input":"2022-10-01T04:45:29.800803Z","iopub.status.idle":"2022-10-01T04:45:30.429704Z","shell.execute_reply.started":"2022-10-01T04:45:29.800742Z","shell.execute_reply":"2022-10-01T04:45:30.428622Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Visualising a horse path: \nlets use seaborn library to vizualise one horse movement through a race.","metadata":{}},{"cell_type":"code","source":"horse_example = dataset[dataset.horse_id == 'AQU2019-01-0196  '].sort_values(by = 'trakus_index')\nsns.scatterplot(x = 'latitude', y= 'longitude', data=horse_example,hue = 'trakus_index');","metadata":{"execution":{"iopub.status.busy":"2022-10-01T04:45:30.431550Z","iopub.execute_input":"2022-10-01T04:45:30.432375Z","iopub.status.idle":"2022-10-01T04:45:31.246325Z","shell.execute_reply.started":"2022-10-01T04:45:30.432332Z","shell.execute_reply":"2022-10-01T04:45:31.245034Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Visualising a race: \nlets use seaborn library to vizualise the horses path through the race:","metadata":{}},{"cell_type":"code","source":"race_example = dataset[dataset.race_id == 'AQU2019-01-019'].sort_values(by = 'trakus_index')\nsns.scatterplot(x = 'latitude', y= 'longitude', data=race_example,hue = 'horse_id',legend = False);","metadata":{"execution":{"iopub.status.busy":"2022-10-01T04:45:31.248049Z","iopub.execute_input":"2022-10-01T04:45:31.248414Z","iopub.status.idle":"2022-10-01T04:45:31.946332Z","shell.execute_reply.started":"2022-10-01T04:45:31.248364Z","shell.execute_reply":"2022-10-01T04:45:31.945076Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Tracking horses speed through the race\n\nAll what we need to do is to calculate the distance between two trakus indices, this should represent the speed of the horse in this point of time. ","metadata":{}},{"cell_type":"code","source":"# this function is used to calculate the distance in meters between two lat / long points \n# more to read here https://stackoverflow.com/questions/19412462/getting-distance-between-two-points-based-on-latitude-longitude\ndef distance(lat1,lon1,lat2,lon2):\n    R = 6373000.0\n\n    lat1 = radians(lat1)\n    lon1 = radians(lon1)\n    lat2 = radians(lat2)\n    lon2 = radians(lon2)\n\n    dlon = lon2 - lon1\n    dlat = lat2 - lat1\n\n    a = sin(dlat / 2)**2 + cos(lat1) * cos(lat2) * sin(dlon / 2)**2\n    c = 2 * atan2(sqrt(a), sqrt(1 - a))\n\n    distance = R * c\n    return distance ","metadata":{"execution":{"iopub.status.busy":"2022-10-01T04:45:31.948230Z","iopub.execute_input":"2022-10-01T04:45:31.949108Z","iopub.status.idle":"2022-10-01T04:45:31.958178Z","shell.execute_reply.started":"2022-10-01T04:45:31.949061Z","shell.execute_reply":"2022-10-01T04:45:31.957067Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"locations = dataset[['race_id','horse_id','trakus_index','latitude','longitude']]\nlocations.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-01T04:45:31.959700Z","iopub.execute_input":"2022-10-01T04:45:31.960184Z","iopub.status.idle":"2022-10-01T04:45:32.119575Z","shell.execute_reply.started":"2022-10-01T04:45:31.960138Z","shell.execute_reply":"2022-10-01T04:45:32.118538Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for i in ['latitude','longitude','trakus_index']:\n    locations['next_'+i] = locations.sort_values('trakus_index').groupby('horse_id')[i].shift(-1 )","metadata":{"execution":{"iopub.status.busy":"2022-10-01T04:45:32.121443Z","iopub.execute_input":"2022-10-01T04:45:32.121799Z","iopub.status.idle":"2022-10-01T04:45:45.688033Z","shell.execute_reply.started":"2022-10-01T04:45:32.121744Z","shell.execute_reply":"2022-10-01T04:45:45.686580Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"locations.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-01T04:45:45.689622Z","iopub.execute_input":"2022-10-01T04:45:45.691095Z","iopub.status.idle":"2022-10-01T04:45:45.707818Z","shell.execute_reply.started":"2022-10-01T04:45:45.691041Z","shell.execute_reply":"2022-10-01T04:45:45.706453Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Now we have the location of the current and the next point at the same row, let's calculate the distance between the two points, and this should equal the speed. ","metadata":{}},{"cell_type":"code","source":"    \nlocations['distance'] = locations.apply(lambda row: distance(row['latitude'],row['longitude'],row['next_latitude'],row['next_longitude']), axis=1)\nlocations['distance'] = locations['distance'] / (locations.next_trakus_index - locations.trakus_index)\n","metadata":{"execution":{"iopub.status.busy":"2022-10-01T04:45:45.710099Z","iopub.execute_input":"2022-10-01T04:45:45.710584Z","iopub.status.idle":"2022-10-01T04:48:01.226614Z","shell.execute_reply.started":"2022-10-01T04:45:45.710535Z","shell.execute_reply":"2022-10-01T04:48:01.224141Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"locations.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-01T04:48:01.228965Z","iopub.execute_input":"2022-10-01T04:48:01.230610Z","iopub.status.idle":"2022-10-01T04:48:01.252197Z","shell.execute_reply.started":"2022-10-01T04:48:01.230548Z","shell.execute_reply":"2022-10-01T04:48:01.250702Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Visualize the speed of the horses during the race:\nlet's use our race example to show the speed of the different horses during the race.","metadata":{}},{"cell_type":"code","source":"race_example = locations[locations.race_id == 'AQU2019-01-019'].sort_values(by = 'trakus_index')\n","metadata":{"execution":{"iopub.status.busy":"2022-10-01T04:48:01.254050Z","iopub.execute_input":"2022-10-01T04:48:01.255160Z","iopub.status.idle":"2022-10-01T04:48:01.860247Z","shell.execute_reply.started":"2022-10-01T04:48:01.255118Z","shell.execute_reply":"2022-10-01T04:48:01.859290Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.lineplot(data =race_example , x = 'trakus_index'  , y = 'distance' , hue= 'horse_id', legend= False);","metadata":{"execution":{"iopub.status.busy":"2022-10-01T04:48:01.862004Z","iopub.execute_input":"2022-10-01T04:48:01.863020Z","iopub.status.idle":"2022-10-01T04:48:02.291005Z","shell.execute_reply.started":"2022-10-01T04:48:01.862963Z","shell.execute_reply":"2022-10-01T04:48:02.289896Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Nice..  you see how the speed build up until it reaches maximum and then drop till the end ","metadata":{}},{"cell_type":"markdown","source":"## Week 6\nIn this week we want to visualize and model the rotation of the horse, for that we need to calculate the angle between the trakus index to calculate how sharp is the horse turns, also when the horse turns it either takes the outer side of the fence to turn with high speed, lower angel but longer distance. or take the inner side with lower speed, higher angel and shorter distance. ","metadata":{}},{"cell_type":"markdown","source":"### Calculate the angle between 3 trakus index","metadata":{}},{"cell_type":"code","source":"import math\ndef getAngle(lat1,long1, lat2,long2,lat3,long3):\n    ang = math.degrees(math.atan2(long3-long2, lat3-lat2) - math.atan2(long1-long2, lat1-lat2))\n    return ang + 360 if ang < 0 else ang\n","metadata":{"execution":{"iopub.status.busy":"2022-10-01T04:49:04.949739Z","iopub.execute_input":"2022-10-01T04:49:04.950892Z","iopub.status.idle":"2022-10-01T04:49:04.957524Z","shell.execute_reply.started":"2022-10-01T04:49:04.950833Z","shell.execute_reply":"2022-10-01T04:49:04.956580Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for i in ['latitude','longitude','trakus_index']:\n    locations['previous_'+i] = locations.sort_values('trakus_index').groupby('horse_id')[i].shift( 1 )\nlocations.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-01T04:48:25.435355Z","iopub.execute_input":"2022-10-01T04:48:25.435739Z","iopub.status.idle":"2022-10-01T04:48:40.067296Z","shell.execute_reply.started":"2022-10-01T04:48:25.435709Z","shell.execute_reply":"2022-10-01T04:48:40.066114Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"locations['angle'] = locations.apply(lambda x: getAngle(x['previous_latitude'],x['previous_longitude'],\nx['latitude'],x['longitude'],x['next_latitude'],x['next_longitude']), axis=1)\n","metadata":{"execution":{"iopub.status.busy":"2022-10-01T05:14:12.285356Z","iopub.execute_input":"2022-10-01T05:14:12.285908Z","iopub.status.idle":"2022-10-01T05:17:08.377544Z","shell.execute_reply.started":"2022-10-01T05:14:12.285865Z","shell.execute_reply":"2022-10-01T05:17:08.376107Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_example = locations[locations.race_id == 'AQU2019-01-019'].sort_values(by = 'trakus_index')\n","metadata":{"execution":{"iopub.status.busy":"2022-10-01T05:17:08.379815Z","iopub.execute_input":"2022-10-01T05:17:08.380159Z","iopub.status.idle":"2022-10-01T05:17:08.728015Z","shell.execute_reply.started":"2022-10-01T05:17:08.380127Z","shell.execute_reply":"2022-10-01T05:17:08.726752Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.lineplot(data =race_example[race_example.trakus_index > 10 ]  , x = 'trakus_index'  , y = 'angle' , hue= 'horse_id', legend= False);","metadata":{"execution":{"iopub.status.busy":"2022-10-01T05:17:08.729507Z","iopub.execute_input":"2022-10-01T05:17:08.730083Z","iopub.status.idle":"2022-10-01T05:17:09.136859Z","shell.execute_reply.started":"2022-10-01T05:17:08.730046Z","shell.execute_reply":"2022-10-01T05:17:09.135995Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"See between trakus index # 100 and 200 all the horses are turning around with 1 or 2 degrees. ","metadata":{}},{"cell_type":"code","source":"### Calculate the angle between 3 trakus index","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Calculate the radius of horse rotation","metadata":{}},{"cell_type":"code","source":"race_center = locations.groupby('race_id').mean()[['latitude','longitude']]\nrace_center.columns = [i + '_mean' for i in race_center.columns ]\nlocations = locations.merge(race_center.reset_index())","metadata":{"execution":{"iopub.status.busy":"2022-10-01T05:24:28.604292Z","iopub.execute_input":"2022-10-01T05:24:28.604751Z","iopub.status.idle":"2022-10-01T05:24:30.728994Z","shell.execute_reply.started":"2022-10-01T05:24:28.604712Z","shell.execute_reply":"2022-10-01T05:24:30.727807Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_example = locations[locations.race_id == 'AQU2019-01-019'].sort_values(by = 'trakus_index')\nsns.scatterplot(x = 'latitude', y= 'longitude', data=race_example,hue = 'horse_id',legend = False)\nsns.scatterplot(x = 'latitude_mean', y= 'longitude_mean', data=race_example,hue = 'horse_id',legend = False)","metadata":{"execution":{"iopub.status.busy":"2022-10-01T05:24:37.412385Z","iopub.execute_input":"2022-10-01T05:24:37.413120Z","iopub.status.idle":"2022-10-01T05:24:38.911157Z","shell.execute_reply.started":"2022-10-01T05:24:37.413074Z","shell.execute_reply":"2022-10-01T05:24:38.909815Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"You see this point in the middle? this is the center point of the race, and at each point i want to calculate the distance between the horse location and this point. ","metadata":{}},{"cell_type":"code","source":"locations['distance_from_center'] = locations.apply(lambda row: distance(row['latitude'],row['longitude'],row['latitude_mean'],row['longitude_mean']), axis=1)","metadata":{"execution":{"iopub.status.busy":"2022-10-01T05:25:56.847451Z","iopub.execute_input":"2022-10-01T05:25:56.848114Z","iopub.status.idle":"2022-10-01T05:28:14.707187Z","shell.execute_reply.started":"2022-10-01T05:25:56.848058Z","shell.execute_reply":"2022-10-01T05:28:14.705848Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_example = locations[locations.race_id == 'AQU2019-01-019'].sort_values(by = 'trakus_index')\n","metadata":{"execution":{"iopub.status.busy":"2022-10-01T05:35:41.506863Z","iopub.execute_input":"2022-10-01T05:35:41.507218Z","iopub.status.idle":"2022-10-01T05:35:41.890007Z","shell.execute_reply.started":"2022-10-01T05:35:41.507180Z","shell.execute_reply":"2022-10-01T05:35:41.888947Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_example2 = race_example[race_example.trakus_index / 10 ==  race_example.trakus_index // 10]\nrace_example2 = race_example2[race_example2['horse_id'] == 'AQU2019-01-01910 ']\n","metadata":{"execution":{"iopub.status.busy":"2022-10-01T05:52:20.697592Z","iopub.execute_input":"2022-10-01T05:52:20.698035Z","iopub.status.idle":"2022-10-01T05:52:20.708059Z","shell.execute_reply.started":"2022-10-01T05:52:20.697999Z","shell.execute_reply":"2022-10-01T05:52:20.706698Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.scatterplot(x = 'latitude', y= 'longitude', data=race_example2,hue = 'trakus_index')\nsns.scatterplot(x = 'latitude_mean', y= 'longitude_mean', data=race_example2,legend = False)\nimport matplotlib.pylab as plt\n\n\nx = race_example2.iloc[0][['latitude_mean','latitude']].values\ny = race_example2.iloc[0][['longitude_mean','longitude']].values\nsns.lineplot(x = x, y= y)\nplt.text(x.mean(), y.mean(), 'Distance from center', horizontalalignment='left', size='medium', color='black', weight='semibold');\n","metadata":{"execution":{"iopub.status.busy":"2022-10-01T05:53:12.643319Z","iopub.execute_input":"2022-10-01T05:53:12.643787Z","iopub.status.idle":"2022-10-01T05:53:13.094410Z","shell.execute_reply.started":"2022-10-01T05:53:12.643734Z","shell.execute_reply":"2022-10-01T05:53:13.092984Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.lineplot(x = 'trakus_index', y= 'distance_from_center', data=race_example,hue = 'horse_id');\n","metadata":{"execution":{"iopub.status.busy":"2022-10-01T05:54:48.707343Z","iopub.execute_input":"2022-10-01T05:54:48.707818Z","iopub.status.idle":"2022-10-01T05:54:49.256637Z","shell.execute_reply.started":"2022-10-01T05:54:48.707755Z","shell.execute_reply":"2022-10-01T05:54:49.255440Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"not very representative to compare horses to each others but you can see the distance is changing based on how close and far the horse is from the center of the race, for example, at the beginning of the race between 0 and 100, horses are getting closer to the center point until they move away from it ","metadata":{}},{"cell_type":"markdown","source":"### Calculate the deviation\nNow i want to see which horses prefer to stay closer to the center point and which one prefer to stay on the outer side of the fence ","metadata":{}},{"cell_type":"code","source":"locations['deviation'] = locations['distance_from_center'].sub(locations.groupby(['race_id','trakus_index'])['distance_from_center'].transform('median'))","metadata":{"execution":{"iopub.status.busy":"2022-10-01T05:56:46.957096Z","iopub.execute_input":"2022-10-01T05:56:46.957602Z","iopub.status.idle":"2022-10-01T05:56:47.993340Z","shell.execute_reply.started":"2022-10-01T05:56:46.957561Z","shell.execute_reply":"2022-10-01T05:56:47.992397Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_example = locations[locations.race_id == 'AQU2019-01-019'].sort_values(by = 'trakus_index')\n","metadata":{"execution":{"iopub.status.busy":"2022-10-01T05:57:09.661106Z","iopub.execute_input":"2022-10-01T05:57:09.662471Z","iopub.status.idle":"2022-10-01T05:57:10.898961Z","shell.execute_reply.started":"2022-10-01T05:57:09.662423Z","shell.execute_reply":"2022-10-01T05:57:10.897814Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.lineplot(x = 'trakus_index', y= 'deviation',hue = 'horse_id', data=race_example,);\n","metadata":{"execution":{"iopub.status.busy":"2022-10-01T05:57:16.968968Z","iopub.execute_input":"2022-10-01T05:57:16.969410Z","iopub.status.idle":"2022-10-01T05:57:17.517357Z","shell.execute_reply.started":"2022-10-01T05:57:16.969370Z","shell.execute_reply":"2022-10-01T05:57:17.516169Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"if the deviation is negative, means that jockey stay closer to the center, and if the deviation is positive this means that jockey prefer to stay closer to the outer fence ","metadata":{}},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}