{"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":"This work is a modification of omu_com\nCredit to omu_com(https://www.kaggle.com/hkhiroshima)\n\n# Overview of Notebook\n## Summary of previous study \n[Find a horse with high odds but likely to win!](https://www.kaggle.com/code/hkhiroshima/find-a-horse-with-high-odds-but-likely-to-win)\n* Quantification of a jockey's skill is done in order for a horse with high odds to win in a horse race.\n* Started to examine the horse's perspective, draft strategy, and ticket buyer's perspective to evaluate jockey skill in a 360-degree view.\n* Quantification of skill by obtaining average speed and average acceleration of all data for each jockey.\n\n## Reflections on the previous study\n* Only summary statistics are analyzed, so the jockey's drafting strategy is not analyzed at the time or location level.\n* Only the horse's movement in one race is analyzed, and the direction in which the jockey's drafting strategy is analyzed is unclear.\n \n\n## Assumptions made for additional consideration\n* About Drafting Strategy:The drafting strategy shall be to draft and overtake.\n* About Course\n    * Once the course and distance of the racetrack are determined, the starting position is also determined.\n    * None of the races under analysis has a straight line from start to finish, and at least one curve is present.\n* About Jockey\n    * Because the distance varies from race to race, jockey also considers the pacing of the horses in his drafting strategy.\n    * The pacing of the horses takes into account their position on the course and their relative position to the competition.","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19"}},{"cell_type":"markdown","source":"## Question\n### Jockeys may be checking and changing their strategies at time and course positions?\n\n* To do in this notebook:Analyze the jockey's drafting strategy in terms of time and location.\n    * Time\n        * **Available**: Identify the horse in the race.  \n        * **Under Planning**:Update previously shared notebook with information on horses in the race. \n    * Location\n        * **Available**: Classify straight lines and curves where the horse runs during the race.\n        * **Under Study**: Identify if drafting is taking place during the race. \n* Not to do in this notebook\n    * Location: Consideration of the relative position of the horses to each other\n        \n\n## Reference\nthanks for the nice note book!\n* [updated get finish postion by horse coordinates](https://www.kaggle.com/code/mlconsult/updated-get-finish-postion-by-horse-coordinates)","metadata":{}},{"cell_type":"markdown","source":"# Import Library and Extract Data","metadata":{}},{"cell_type":"code","source":"!pip install utm\nimport utm\n\nimport os\nimport re\nimport warnings\n\n# for analyze data\nimport numpy as np \nimport pandas as pd \nfrom shapely.geometry import Point\nfrom shapely.geometry.polygon import Polygon\nfrom tqdm.auto import tqdm\ntqdm.pandas()\n\n# for visualization\nimport seaborn as sns\nimport matplotlib.pyplot as plt\nfrom matplotlib import mlab\n\nimport plotly.express as px\nfrom plotly.subplots import make_subplots\nimport plotly.graph_objects as go\n\nwarnings.simplefilter('ignore')\n\n#!pip install pymap3d\n#import pymap3d as pm\nsns.set_style(\"darkgrid\")","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:29:59.670270Z","iopub.execute_input":"2022-11-09T04:29:59.670957Z","iopub.status.idle":"2022-11-09T04:30:19.926380Z","shell.execute_reply.started":"2022-11-09T04:29:59.670819Z","shell.execute_reply":"2022-11-09T04:30:19.924693Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Defining the data type when reading data can reduce memory consumption.<br>Thanks @LEONIE[Big Data Derby: Step by Step Analysis 🏇🏇🏇](https://www.kaggle.com/code/iamleonie/big-data-derby-step-by-step-analysis)\n","metadata":{}},{"cell_type":"code","source":"dtype_dict = {\"track_id\": object ,\n              \"race_date\" :object, #will be read with \"parse_dates\" parameter\n              \"race_number\" : np.int8, \n              \"program_number\" : object,\n              \"trakus_index\" : np.int16,  \n              \"latitude\" : np.float64,\n              \"longitude\" : np.float64,\n              \"distance_id\" : np.int16,  \n              \"course_type\" : object, \n              \"track_condition\" : object,\n              \"run_up_distance\" : np.int8,  \n              \"race_type\" : object, \n              \"purse\" : np.int32,  \n              \"post_time\" : np.int16,  \n              \"weight_carried\" : np.int16,  \n              \"jockey\" : object, \n              \"odds\" : np.int16,\n              \"position_at_finish\":np.int8\n             }\nnew_dtype_dict = {0: object ,\n              #1 :object, #will be read with \"parse_dates\" parameter\n              2 : np.int8, \n              3 : object,\n              4 : np.int16,  \n              5 : np.float64,\n              6 : np.float64,\n              7 : np.int16,  \n              8 : object, \n              9 : object,\n              10 : np.int8,  \n              11 : object, \n              12 : np.int32,  \n              13 : np.int16,  \n              14 : np.int16,  \n              15 : object, \n              16 : np.int16,\n              17:np.int8\n             }","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:30:19.929121Z","iopub.execute_input":"2022-11-09T04:30:19.929594Z","iopub.status.idle":"2022-11-09T04:30:19.940913Z","shell.execute_reply.started":"2022-11-09T04:30:19.929550Z","shell.execute_reply":"2022-11-09T04:30:19.938908Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"ALL_PATH = '/kaggle/input/big-data-derby-2022/nyra_2019_complete.csv'\nall_df = pd.read_csv(ALL_PATH, header=None,dtype=dtype_dict,parse_dates=[1])\nall_df.columns= dtype_dict.keys()\nall_df.info()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:30:19.943450Z","iopub.execute_input":"2022-11-09T04:30:19.943934Z","iopub.status.idle":"2022-11-09T04:30:43.343967Z","shell.execute_reply.started":"2022-11-09T04:30:19.943895Z","shell.execute_reply":"2022-11-09T04:30:43.342372Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Memory consumption is 438.8 MB! I better define the data type.","metadata":{}},{"cell_type":"markdown","source":"# Create Feature \nFeatures are added in three categories: time, distance, and analysis aids.\n\n* Time\n    * `time`: Elapsed time since start\n    * `scene_id`: Time binned every 10 seconds (For example, `scene_id` of 0 means 0 to less than 10 seconds)\n* Distance\n    * `run_up_distance`: Distance (feet) from gate to starting point converted to miles\n    * `distance_id`: Distance of the race in furlongs passed as an integer. Example - 600 would be 6 furlongs.\n    * `furlongs_id`: Convert `distance_id` to furlong units\n    * `miles`:Convert the race distance indicated in `distance_id` to miles\n    \n* Jockey\n    * `number_of_rides`:Number of race appearances in the jockey's data\n* Analysis Aids\n    * `program_number`:Unified notation so that sorting is possible\n    * `race_id`:Create unique numbers for all races by concatenating `track_id`,`race_date`,`race_number","metadata":{}},{"cell_type":"code","source":"# convert to integer for sorting\nprogram_num_dict={'1': '01', '1  ': '01', '10': '10', '10 ': '10', '11': '11', '11 ': '11', '12': '12', '12 ': '12',\n           '13': '13', '13 ': '13', '14': '14', '14 ': '14', '15': '15', '15 ': '15', '16': '16', '16 ': '16',\n           '1A ': '01A', '1X ': '01X', '2': '02', '2  ': '02', '2B ': '02B', '3': '03', '3  ': '03', '3X ': '03X',\n           '4': '04', '4  ': '04', '5': '05', '5  ': '05', '6': '06', '6  ': '06', '7': '07', '7  ': '07',\n           '8': '08', '8  ': '08', '9': '09', '9  ': '09', '1A': '01A', '2B': '02B', '3X': '03X', '1X': '01X'}\n\nall_df['program_number'] = all_df['program_number'].apply(lambda x: program_num_dict[x] if type(x)==str else x)\n\n# aggregation key\nkey_columns = [\"track_id\", \"race_date\", \"race_number\"]\nall_df[\"race_id\"] = all_df[\"track_id\"].astype(str) +'_'+ all_df[\"race_date\"].astype(str) +'_'+ all_df[\"race_number\"].astype(str)\n\n# convert distance unit\nall_df['run_up_distance']= all_df['run_up_distance'] *0.3048\nall_df['furlongs_id'] = 8 * all_df['distance_id'] / 600\nall_df['miles'] = all_df['distance_id'] / 600\nall_df['furlongs_id'] = all_df['furlongs_id'].apply(lambda x: round(x,2))\n\n# aggregate categorical row\nall_df['track_course_type'] = all_df['track_id']+ '_' + all_df['course_type'] \nall_df['course_type_miles'] = all_df['track_id']+ '_' + all_df['course_type'] + '_' + all_df['miles'].apply(lambda x: str(round(x,2)))\n\n# get time nand binned time\nall_df['time'] = all_df['trakus_index']*0.25 - 0.25\nall_df['scene_id'] =all_df['time'].apply(lambda x: int(x//10))\n\n# get number of race appearances in the jockey's data\ntemp = all_df[['race_id','jockey']].drop_duplicates().groupby('jockey')['race_id'].count().to_frame().reset_index()\ntemp = temp.rename(columns={'race_id':'number_of_rides'})\nall_df = all_df.merge(temp,on=['jockey'])","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:30:43.359328Z","iopub.execute_input":"2022-11-09T04:30:43.359828Z","iopub.status.idle":"2022-11-09T04:31:53.909921Z","shell.execute_reply.started":"2022-11-09T04:30:43.359789Z","shell.execute_reply":"2022-11-09T04:31:53.908229Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"all_df.race_id.nunique()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T04:31:53.912161Z","iopub.execute_input":"2022-11-09T04:31:53.912742Z","iopub.status.idle":"2022-11-09T04:31:54.401512Z","shell.execute_reply.started":"2022-11-09T04:31:53.912665Z","shell.execute_reply":"2022-11-09T04:31:54.399915Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"There's a total of 2,000 races in the data. Okay, let's analyze.","metadata":{}},{"cell_type":"markdown","source":"---\n# EDA(Exploratory Data Analysis)\n## How many races are held at each racetrack and on what courses?","metadata":{}},{"cell_type":"code","source":"temp = all_df[key_columns+[\"race_id\",\"course_type\"]]\ntemp = temp.groupby([\"track_id\", \"course_type\"]).race_id.nunique().to_frame().reset_index(drop=False)\nplt.figure(figsize=[24,4]);\nsns.heatmap(temp.pivot(index=\"track_id\", columns=[\"course_type\"]).race_id,annot=True);","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:31:54.403338Z","iopub.execute_input":"2022-11-09T04:31:54.403777Z","iopub.status.idle":"2022-11-09T04:32:00.127870Z","shell.execute_reply.started":"2022-11-09T04:31:54.403739Z","shell.execute_reply":"2022-11-09T04:32:00.125936Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Dirt races are held most frequently at Aqueduct racetrack, with approximately 700 races held there. Since the total number of races held is 2000, about 35% of the race data will be for dirt tracks at Aqueduct Racecourse, and we will use this data as the basis for our discussion.<br>\n**Let's take a closer look at the data in terms of time and distance to get a better idea of what goes on during a race**.","metadata":{}},{"cell_type":"markdown","source":"## What is the most frequently held distance at each racetrack?\nLet's use `distance_id` to convert distance units to halons for each race and then create heatmaps for each racetrack!","metadata":{}},{"cell_type":"code","source":"temp = all_df[key_columns+[\"race_id\",\"furlongs_id\"]]\ntemp = temp.groupby([\"track_id\", \"furlongs_id\"]).race_id.nunique().to_frame().reset_index(drop=False)\nplt.figure(figsize=[24,4]);\nsns.heatmap(temp.pivot(index=\"track_id\", columns=[\"furlongs_id\"]).race_id,annot=True);","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:32:00.129852Z","iopub.execute_input":"2022-11-09T04:32:00.130314Z","iopub.status.idle":"2022-11-09T04:32:02.660482Z","shell.execute_reply.started":"2022-11-09T04:32:00.130276Z","shell.execute_reply":"2022-11-09T04:32:02.657445Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The most frequently held distances by racetrack:\n* Aqueduct Racetrack holds 8 furlong races\n* Belmont Racetrack holds 8 furlong races\n* Saratoga Racetrack holds 7.33 furlong races","metadata":{}},{"cell_type":"markdown","source":"## What range of seconds does the race data for each racetrack exist?","metadata":{}},{"cell_type":"code","source":"temp = all_df[key_columns+[\"race_id\",\"scene_id\"]]\ntemp = temp.groupby([\"track_id\", \"scene_id\"]).race_id.nunique().to_frame().reset_index(drop=False)\nplt.figure(figsize=[36,4]);\nsns.heatmap(temp.pivot(index=\"track_id\", columns=[\"scene_id\"]).race_id,annot=True);","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:32:02.662137Z","iopub.execute_input":"2022-11-09T04:32:02.663216Z","iopub.status.idle":"2022-11-09T04:32:05.785281Z","shell.execute_reply.started":"2022-11-09T04:32:02.663169Z","shell.execute_reply":"2022-11-09T04:32:05.783913Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"* The data does not appear to change until after 60 seconds when the racetrack changes (scene_id=6)\n* The above may be rounded numbers, so let's take a closer look.\n\n## Combination of time and distance\nIn terms of racetracks and courses, races are most often held on Aqueduct's dirt course. **How many furlong races are held most often?**","metadata":{}},{"cell_type":"code","source":"track_id='AQU'\ncourse_type='D'\ncondition=\"track_id==@track_id & course_type==@course_type\"\n\ntemp = all_df[key_columns+[\"furlongs_id\",'course_type',\"scene_id\",\"race_id\"]]\ntemp = temp.query(condition)\ntemp = temp.groupby([\"furlongs_id\", \"scene_id\"]).race_id.nunique().to_frame().reset_index(drop=False)\nplt.figure(figsize=[36,8]);\nsns.heatmap(temp.pivot(index=\"furlongs_id\", columns=[\"scene_id\"]).race_id,annot=True);","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:32:05.786815Z","iopub.execute_input":"2022-11-09T04:32:05.787191Z","iopub.status.idle":"2022-11-09T04:32:07.907475Z","shell.execute_reply.started":"2022-11-09T04:32:05.787156Z","shell.execute_reply":"2022-11-09T04:32:07.906180Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"A heat map was drawn with distance on the vertical axis and time on the horizontal axis.\n\n* If you look at the data for each vertical axis (`fulongs_id`), you will find that some of them have extremely small number of data even though they are the same course. : There is one race with `scen_id` of 10 (100<time<=110) for an 8-halon race.\n* **Check if post-race information is not included because the race time is too long.**\n\n**Other notes for consideration**\n* I want to identify data during the race, but it seems that we cannot simply delete information that crossed the finish line.\n* It is possible that the horse has crossed the finish line twice, so if I simply assume that the horse crossed the finish line, I would delete the data during the race.\n* Let's first check the data of crossing the finish line twice from the distance.\n* Since I can get the maximum number of seconds per race, should we just use the last 30 seconds to judge?","metadata":{}},{"cell_type":"markdown","source":"## What is the ranking and winning ratio of the top 3 jockeys in terms of appearances in races?","metadata":{}},{"cell_type":"code","source":"temp=all_df[['race_id','track_id','jockey','position_at_finish']].drop_duplicates()\ntemp_pv=pd.pivot_table(data=temp,index='jockey',columns='position_at_finish',values='race_id',aggfunc='count')\nfor x in temp_pv.columns:\n    temp_pv[x]=temp_pv[x].fillna(0)\n\ntemp_pv['jockey_count']=temp_pv.sum(axis=1)\n\nfor x in temp_pv.columns:\n    if x !='jockey_count':\n        temp_pv[x] = round(100 * temp_pv[x]/temp_pv['jockey_count'],2)\n\ntemp_pv = temp_pv.sort_values(by='jockey_count')\ntemp_pv = temp_pv.reset_index()\n\ncolor_dict = {'Jose Lezcano' : 'yellow'\n              , 'Dylan Davis' : 'blue'\n              , 'Manuel Franco' : 'magenta'}\nfig = plt.figure(figsize=[18,4])\nplt.subplot(1,2,1)\nfor jockey_name in temp_pv.jockey[-3:]:\n    data = temp.query(\"jockey==@jockey_name\")\n    plt.hist(data=data\n             ,x='position_at_finish'\n             ,alpha=0.3)\nplt.legend(temp_pv.jockey[-3:]);\nplt.xlabel('In Order of Arrival');\nplt.ylabel('Frequency');\nplt.title('Ranking of jockey in top 3 runs');\n\nplt.subplot(1,2,2)\ntemp_ratio = temp.groupby(['jockey','position_at_finish'])['race_id'].count().to_frame().reset_index()\nfor jockey_name in temp_pv.jockey[-3:]:\n    data = temp_ratio.query(\"jockey==@jockey_name\")\n    data['ratio'] = round(100*data['race_id']/data['race_id'].sum(),2)\n    plt.bar(data =data\n            ,x='position_at_finish'\n            ,height='ratio'\n            ,alpha=0.3)\nplt.legend(temp_pv.jockey[-3:]);\nplt.xlabel('In Order of Arrival');\nplt.ylabel('Ratio(%)');\nplt.title('Ranking of jockey in top 3 runs');","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:32:07.913183Z","iopub.execute_input":"2022-11-09T04:32:07.913894Z","iopub.status.idle":"2022-11-09T04:32:10.654802Z","shell.execute_reply.started":"2022-11-09T04:32:07.913849Z","shell.execute_reply":"2022-11-09T04:32:10.653251Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"* 'Manuel Franco' has been in first place more often.\n* 'Jose Lezacano' took first place most of the time.","metadata":{}},{"cell_type":"markdown","source":"---\n# Identify the horse in the race\n\nSampling process because it will be very heavy if we plot all the race data in plotly","metadata":{}},{"cell_type":"code","source":"#sample_df = all_df.sample(n = int(all_df.shape[0]*0.1))\nsample_df = all_df.sample(n = int(all_df.shape[0]*0.1))\n\ngoal_line_dict ={'AQU':{\n                    'D':[[40.6719205,-73.8319531],[40.6718795,-73.8316874]]},\n                 'SAR':[[43.071816,-73.770390],[43.072480,-73.770883]]\n                }","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:32:10.656823Z","iopub.execute_input":"2022-11-09T04:32:10.657261Z","iopub.status.idle":"2022-11-09T04:32:12.183760Z","shell.execute_reply.started":"2022-11-09T04:32:10.657221Z","shell.execute_reply":"2022-11-09T04:32:12.181859Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def extract_corse_data(df,track_id_,course_type_,furlongs_id_,\\\n                       available_scene_num_=5,goal_line_dict_=goal_line_dict):\n    condition=\"　track_id==@track_id_ & course_type==@course_type_ &furlongs_id==@furlongs_id_\"\n    #col_lst = ['latitude','longitude','scene_id','track_id','course_type']\n    temp_df = df.query(condition)#[col_lst]\n    \n    if track_id_=='AQU':\n        if course_type_=='D':\n            #outer point\n            goal_df = temp_df.copy()\n            goal_df = goal_df.head(1)\n            goal_df[\"latitude\"] ,goal_df[\"longitude\"] = goal_line_dict[track_id_][course_type_][0]\n            goal_df[\"scene_id\"]  = 20\n\n            #Inner point\n            goal_line_df =temp_df.copy()\n            goal_line_df = goal_line_df.head(1)\n            goal_line_df[\"latitude\"] ,goal_line_df[\"longitude\"] =goal_line_dict[track_id_][course_type_][1]\n            goal_line_df[\"scene_id\"]  = 20\n    # slice by scene\n    available_scene_num = available_scene_num_  \n    drop_scene_num= temp_df['scene_id'].max() - available_scene_num_ # max=all\n    temp_df = temp_df[temp_df['scene_id']> drop_scene_num]\n\n    #merge dataframe\n    goal_line_df = goal_line_df.append(goal_df).drop_duplicates()\n    temp_df = temp_df.append(goal_line_df)\n    \n    return temp_df","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:32:12.185891Z","iopub.execute_input":"2022-11-09T04:32:12.186372Z","iopub.status.idle":"2022-11-09T04:32:12.197674Z","shell.execute_reply.started":"2022-11-09T04:32:12.186333Z","shell.execute_reply":"2022-11-09T04:32:12.196593Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Use plotly to visualize the horse's location on a map!\nThanks for the very nice notebook.\n[Daniel Preciado](https://www.kaggle.com/code/jazivxt/lean-6-secret-royal-biscuit-photo-finish)\n\nThe dirt course at Aqueduct Raceway is nine furlongs, so let's take a look at the 14 furlong race.\n>Reference:[Aqueduct](https://www.gamblingsites.com/horse-racing/tracks-courses/aqueduct-racetrack/)\nI couldn't access the official NYRA information, so I treated it as a reference.","metadata":{}},{"cell_type":"code","source":"track_id ='AQU'\ncourse_type ='D'\nfurlongs_id = 14\navailable_scene_num=14\ntemp_df = extract_corse_data(sample_df,track_id,course_type,furlongs_id,available_scene_num)\n\nfig = px.scatter_mapbox(temp_df, \n                        lat=\"latitude\", lon=\"longitude\",color='scene_id'\n                        ,center={'lat':40.6720012, 'lon':-73.8309422}          \n                        ,hover_name=\"track_id\", zoom=14, height=500)\nfig.update_layout(mapbox_style=\"open-street-map\")\nfig.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:32:12.199279Z","iopub.execute_input":"2022-11-09T04:32:12.199980Z","iopub.status.idle":"2022-11-09T04:32:13.556804Z","shell.execute_reply.started":"2022-11-09T04:32:12.199910Z","shell.execute_reply":"2022-11-09T04:32:13.555078Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"If you think of the yellow dot as the finish line, you can see that it has been crossed twice. There is some data (orange) that crossed the finish line, although only slightly.All races take more than 30 seconds, so let's try to get  last 30 seconds.\n\n---\n\n## Obtaining data during a race","metadata":{}},{"cell_type":"code","source":"# Get max time by using scene_id\ntemp = all_df[['race_id','track_id','scene_id']].groupby(['race_id','track_id']).scene_id.max().to_frame().reset_index()\ntemp = temp.rename(columns={'scene_id':'max_scene_id'})\ntemp = temp[['race_id','max_scene_id']]\n\n# Aqueduct Dirt Course Data Acquisition\ntrack_id ='AQU'\ncourse_type ='D'\ncondition=\"track_id==@track_id & course_type==@course_type\"\nAQU_D_df = all_df.query(condition)\n\n# merge dataframe\nAQU_D_df = AQU_D_df.merge(temp)\n\n# slice data\nAQU_D_df['use_scene_id']=AQU_D_df['max_scene_id'] - 3\nfor idx in AQU_D_df.index:\n    if AQU_D_df.at[idx,'use_scene_id']>AQU_D_df.at[idx,'scene_id']:\n        AQU_D_df.at[idx,'use_scene']=0\n    else:\n        AQU_D_df.at[idx,'use_scene']=1","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:32:13.558922Z","iopub.execute_input":"2022-11-09T04:32:13.560590Z","iopub.status.idle":"2022-11-09T04:33:29.758615Z","shell.execute_reply.started":"2022-11-09T04:32:13.560529Z","shell.execute_reply":"2022-11-09T04:33:29.757007Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def cross(row,track_id_,course_type_,goal_line_dict_=goal_line_dict):\n    # Goal line seems to be different if track_id and course_type changed\n    # below is for dart course \n    if track_id_ =='AQU':\n        if course_type_=='D':\n            x1,y1 = goal_line_dict_[track_id_][course_type_][0]\n            x2,y2 = goal_line_dict_[track_id_][course_type_][1]\n    elif track_id_ =='SAR':\n        x1,y1 = goal_line_dict_[track_id_][0]\n        x2,y2 = goal_line_dict_[track_id_][1]\n    \n    return (row['latitude']-x1)*(y2-y1) - (row['longitude']-y1)*(x2-x1)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:33:29.760622Z","iopub.execute_input":"2022-11-09T04:33:29.761075Z","iopub.status.idle":"2022-11-09T04:33:29.770186Z","shell.execute_reply.started":"2022-11-09T04:33:29.761037Z","shell.execute_reply":"2022-11-09T04:33:29.769101Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"processed_df = AQU_D_df[AQU_D_df['use_scene']==1]\nprocessed_df['crossed'] = processed_df.apply(lambda x:cross(x,'AQU','D'), axis=1)\ndrop_idx = processed_df[processed_df['crossed']<0].index\nAQU_D_df = AQU_D_df.drop(index=drop_idx)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:33:29.771714Z","iopub.execute_input":"2022-11-09T04:33:29.772783Z","iopub.status.idle":"2022-11-09T04:33:47.360546Z","shell.execute_reply.started":"2022-11-09T04:33:29.772712Z","shell.execute_reply":"2022-11-09T04:33:47.358602Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"I've processed Aqueduct's dirt course data together, but let's look at the results I just got for the data during the race\n>Note: I'm checking to see **if the finish line seems to change** for each racetrack and course.\n","metadata":{}},{"cell_type":"code","source":"track_id ='AQU'\ncourse_type ='D'\nfurlongs_id = 14\navailable_scene_num=14\ntemp_df = extract_corse_data(AQU_D_df,track_id,course_type,furlongs_id,available_scene_num)\n\nfig = px.scatter_mapbox(temp_df, \n                        lat=\"latitude\", lon=\"longitude\",color='scene_id'\n                        ,center={'lat':goal_line_dict[track_id][course_type][1][0]\n                                 ,'lon':goal_line_dict[track_id][course_type][1][1]}          \n                        ,hover_name=\"track_id\", zoom=18, height=500)\nfig.update_layout(mapbox_style=\"open-street-map\")\nfig.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:33:47.362494Z","iopub.execute_input":"2022-11-09T04:33:47.362961Z","iopub.status.idle":"2022-11-09T04:33:47.568353Z","shell.execute_reply.started":"2022-11-09T04:33:47.362924Z","shell.execute_reply":"2022-11-09T04:33:47.567083Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Okay, it seems to be working.\n\n","metadata":{}},{"cell_type":"markdown","source":"## Evaluating the finish line by speed\n\nWe expect the horse's speed to decrease once it crosses the finish line; it is difficult to determine this in a single race, but let's try to get the horse's speed to evaluate if the finish line sets the correct coordinates.","metadata":{}},{"cell_type":"code","source":"def tqdm_pandas(t):\n    from pandas.core.frame import DataFrame\n    def inner(df, func, *args, **kwargs):\n        t.total = groups.size // len(groups)\n        def wrapper(*args, **kwargs):\n            t.update(1)\n            return func(*args, **kwargs)\n        result = df.transform(wrapper, *args, **kwargs)\n        t.close()\n        return result\n    DataFrame.progress_transform = inner\n\ndef get_speed(points):\n    speed = [0]\n    points = points.values\n    for i in range(1, len(points)):\n        speed.append(4 * np.linalg.norm(np.array(points[i]) - np.array(points[i-1])))\n    return speed\n\n# caliculate speed\nAQU_D_df = AQU_D_df.sort_values(['track_id', 'race_date', 'race_number', 'program_number', 'trakus_index']).reset_index(drop=True)\nAQU_D_df['xy'] = np.array(utm.from_latlon(AQU_D_df['latitude'].values\n                                         , AQU_D_df['longitude'].values)[:2]).T.tolist()\nAQU_D_df['speed'] =AQU_D_df.groupby(['track_id', 'race_date', 'race_number', 'program_number'])['xy'].progress_transform(get_speed)\n\n# data correction for start\nidx = AQU_D_df[AQU_D_df['trakus_index']==1].index\nAQU_D_df.loc[idx,'speed']=0\n\n# caliculate accelation with start correction\nAQU_D_df['accelation']=AQU_D_df['speed'].diff()/0.25\nAQU_D_df.loc[idx,'accelation']=0","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:33:47.570416Z","iopub.execute_input":"2022-11-09T04:33:47.571169Z","iopub.status.idle":"2022-11-09T04:34:10.594137Z","shell.execute_reply.started":"2022-11-09T04:33:47.571120Z","shell.execute_reply":"2022-11-09T04:34:10.592752Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"","metadata":{}},{"cell_type":"markdown","source":"---\n### Exapmple1: 2019-03-02 Aqueduct Race number 8","metadata":{}},{"cell_type":"code","source":"# Extract Dataframe\ntrack_id ='AQU'\ncourse_type ='D'\nfurlongs_id = 14\navailable_scene_num=14\ncondition=\"　track_id==@track_id & course_type==@course_type &furlongs_id==@furlongs_id\"\ntemp_df = AQU_D_df.query(condition)\n\ntemp_df = temp_df.sort_values(by='trakus_index')\nplt.figure(figsize=[18,6])\nsns.lineplot(data=temp_df\n             ,x='time',y='speed'\n             ,marker='.');","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:34:10.595888Z","iopub.execute_input":"2022-11-09T04:34:10.596353Z","iopub.status.idle":"2022-11-09T04:34:21.631546Z","shell.execute_reply.started":"2022-11-09T04:34:10.596314Z","shell.execute_reply":"2022-11-09T04:34:21.630059Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Looking at the graph of speeds, it appears that the goal line is at a different position, as the speeds start to decrease when the `time` exceeds around 120 seconds. ","metadata":{"_kg_hide-input":false}},{"cell_type":"markdown","source":"### Example2:2019-03-02 Aqueduct Race number 8 after 110 sec","metadata":{}},{"cell_type":"code","source":"plt.figure(figsize=[18,6])\nsns.lineplot(data=temp_df[temp_df['time']>110]\n             ,x='time',y='speed'\n             ,hue='program_number'\n             ,marker='.');\nplt.vlines(x=121,ymin=12,ymax=20,linestyle='--',color='black');","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:34:21.633267Z","iopub.execute_input":"2022-11-09T04:34:21.634664Z","iopub.status.idle":"2022-11-09T04:34:22.187102Z","shell.execute_reply.started":"2022-11-09T04:34:21.634620Z","shell.execute_reply":"2022-11-09T04:34:22.185669Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"From the graph of speed for each horse, the horse with `program_number` of `06` has already reached the goal with a black dotted line. The speed of this horse represents the average of the speeds of the other horses in the lower ranks, so we know that its speed is decreasing.","metadata":{}},{"cell_type":"markdown","source":"## Observation of the progress of a race through animation.\n\nHorse races cannot be understood unless the positional relationship between the horses and the time is set. Let's divide the race into 10-second segments and observe them with animation. Let's consider the following as a reference.\n\n[🐴Race detailed observation with animation🐴](https://www.kaggle.com/code/lichtlab/race-detailed-observation-with-animation)","metadata":{}},{"cell_type":"markdown","source":"### Example 3:Animation - 2019-03-02 Aqueduct Race number 8 ","metadata":{}},{"cell_type":"code","source":"color_dict = {'01' : 'orange',\n             '02' : 'yellow',\n             '03' : 'red',\n             '04' : 'blue',\n             '05' : 'grey',\n             '06' : 'magenta',\n             '07' : 'black'}\n\nsymbol_dict = {\n    \"01\": \"square\",\n    \"02\": \"circle-x\",\n    \"03\": \"circle-x\",\n    \"04\": \"cross\",\n    \"05\": \"diamond\",\n    \"06\": \"star\",\n    \"07\": \"circle-x\"    \n}\n\npx.scatter(temp_df.sort_values(by='scene_id')\n           ,x='longitude',  y='latitude'\n           , animation_frame=\"scene_id\", \n           color='program_number',\n           color_discrete_map=color_dict,\n           symbol='program_number',\n           symbol_map=symbol_dict,\n           range_x = [temp_df['longitude'].min(), temp_df['longitude'].max()],\n           range_y = [temp_df['latitude'].min(), temp_df['latitude'].max()],\n           title='Race simulation lat/long scatter plot', width=700, height=700)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:34:22.188980Z","iopub.execute_input":"2022-11-09T04:34:22.189403Z","iopub.status.idle":"2022-11-09T04:34:22.716756Z","shell.execute_reply.started":"2022-11-09T04:34:22.189367Z","shell.execute_reply":"2022-11-09T04:34:22.715218Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Looking at the 120-130th second (`scene_id`=12), there are fewer horses already in the field, so I suspect that some of them have already reached the finish line.","metadata":{}},{"cell_type":"markdown","source":"\n\n---\n# Classify straight lines and curves where the horse runs during the race.\n\n## Study1 Determine if the horse is on the straight part of the racetrack.\nI got this idea from the following notebook. Thank you for sharing this information.\n[updated get finish postion by horse coordinates](https://www.kaggle.com/code/mlconsult/updated-get-finish-postion-by-horse-coordinates)","metadata":{"execution":{"iopub.status.busy":"2022-08-28T01:30:17.276243Z","iopub.execute_input":"2022-08-28T01:30:17.276689Z","iopub.status.idle":"2022-08-28T01:30:17.284582Z","shell.execute_reply.started":"2022-08-28T01:30:17.276648Z","shell.execute_reply":"2022-08-28T01:30:17.283356Z"}}},{"cell_type":"code","source":"#polygon for aqueduct straight line\npoly=[[40.6739403, -73.8268994],[40.6750219, -73.8306731],[40.6708878, -73.8325583],[40.6697726,-73.8286816]]\n\ndf_poly = pd.DataFrame(poly, columns =['Lat', 'Lon'], dtype = float)\npolygon = Polygon([tuple(x) for x in df_poly[['Lat', 'Lon']].to_numpy()])\n# box in straight \n# print (\"Finsh Line Box\",polygon)\n\n# check and see what horses are in the streight box\nAQU_D_df['Within'] = AQU_D_df.apply(lambda AQU_D_df: polygon.contains(Point(AQU_D_df['latitude'], AQU_D_df['longitude'])), axis=1)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:34:22.718601Z","iopub.execute_input":"2022-11-09T04:34:22.719010Z","iopub.status.idle":"2022-11-09T04:35:41.232903Z","shell.execute_reply.started":"2022-11-09T04:34:22.718975Z","shell.execute_reply":"2022-11-09T04:35:41.231389Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Example 4:Classification of straight lines and curves - 2019-03-02 Aqueduct Race number 8　","metadata":{}},{"cell_type":"code","source":"track_id ='AQU'\ncourse_type ='D'\nfurlongs_id = 14\navailable_scene_num=14\ncondition=\"　track_id==@track_id & course_type==@course_type &furlongs_id==@furlongs_id\"\ntemp_df = AQU_D_df.query(condition)\n\nfig = px.scatter_mapbox(temp_df, \n                        lat=\"latitude\", lon=\"longitude\",color='Within'\n                        ,center={'lat':40.6720012, 'lon':-73.8309422}          \n                        ,hover_name=\"track_id\", zoom=14, height=500)\nfig.update_layout(mapbox_style=\"open-street-map\")\nfig.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:35:41.234646Z","iopub.execute_input":"2022-11-09T04:35:41.235055Z","iopub.status.idle":"2022-11-09T04:35:41.421087Z","shell.execute_reply.started":"2022-11-09T04:35:41.235017Z","shell.execute_reply":"2022-11-09T04:35:41.419532Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The horse's running position could be classified into curves and straight lines.\n\n>Memo: Should the last corner near the finish line be split into the first and second half to determine? We are considering where to implement this in the overall analysis.","metadata":{"execution":{"iopub.status.busy":"2022-08-29T22:58:41.410165Z","iopub.execute_input":"2022-08-29T22:58:41.410828Z","iopub.status.idle":"2022-08-29T22:58:41.419504Z","shell.execute_reply.started":"2022-08-29T22:58:41.410793Z","shell.execute_reply":"2022-08-29T22:58:41.417234Z"}}},{"cell_type":"markdown","source":"### Example 5:Time changes in velocity and acceleration during a race - 2019-03-02 Aqueduct Race number 8 ","metadata":{}},{"cell_type":"code","source":"# Slice data\ntrack_id ='AQU'\ncourse_type ='D'\nfurlongs_id = 14\navailable_scene_num=14\ncondition=\"　track_id==@track_id & course_type==@course_type &furlongs_id==@furlongs_id\"\ntemp_df = AQU_D_df.query(condition)\n\ntemp_df = temp_df.sort_values(by='trakus_index')\n\nplt.figure(figsize=[24,12]);\nplt.subplot(2,2,1);\nsns.lineplot(data=temp_df,x='time',y='speed'\n                ,hue='Within'\n                ,marker='.'\n                );\nplt.title('Speed');\n\nplt.subplot(2,2,2);\nsns.lineplot(data=temp_df,x='time',y='accelation'\n                ,hue='Within'\n                );\nplt.title('Accelation');\n\nplt.subplot(2,2,3);\nsns.lineplot(data=temp_df[temp_df['time']>110],x='time',y='speed'\n                ,hue='program_number'\n                ,marker='.'\n                );\nplt.title('Speed after 110sec.');\n\nplt.subplot(2,2,4);\nsns.lineplot(data=temp_df[temp_df['time']>110],x='time',y='accelation'\n                ,hue='program_number'\n                ,marker='.'\n                );\nplt.title('Accelation  after 110sec.');","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:35:41.422988Z","iopub.execute_input":"2022-11-09T04:35:41.423363Z","iopub.status.idle":"2022-11-09T04:36:04.148434Z","shell.execute_reply.started":"2022-11-09T04:35:41.423333Z","shell.execute_reply":"2022-11-09T04:36:04.147077Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"* Speed - Overview:\n    * The speed reaches its maximum on the first straight section.\n    * The speed tends to decrease at curves.\n    * In the second straight line, there is a tendency for the speed to increase.\n    * In the final straight, the speed appears to have decreased due to the effect of horses that have already reached the finish line being excluded from the tally.\n* Accelation - Overview:\n    * Large changes in acceleration are observed in the straight sections and near the junctions between straight lines and curves.\n    * This change in acceleration seems to reflect the jockey's strategy.\n\n> **Analysis idea**: Since the jockey makes his decision at the transition between the curve and the straight section, let's slice and analyze the data in the 10 seconds before and after the transition between the straight and the curve, or in the last 30 seconds.","metadata":{}},{"cell_type":"markdown","source":"# Work in Progress\n\n* Analyze the transition between the straight and the curve, or in the last 30 seconds.","metadata":{}},{"cell_type":"markdown","source":"---\n# Bonus\n\n## Identify if drafting is taking place during the race. \nLet's proceed with the analysis based on the data of the races I have analyzed so far.To formulate hypotheses, basic analysis of time-series changes, such as autocorrelation.","metadata":{"_kg_hide-output":false,"_kg_hide-input":false}},{"cell_type":"markdown","source":"### Autocorrelation","metadata":{}},{"cell_type":"markdown","source":"#### Speed","metadata":{}},{"cell_type":"code","source":"plt.figure(figsize=[24,18])\nfor val in range(6,7):\n    for idx,p_num in enumerate(temp_df['program_number'].unique()):\n        plt.subplot(4,2,idx+1)\n        plt.acorr(temp_df.query(\"Within ==True & scene_id==@val & program_number==@p_num\")['speed']\n          ,detrend=mlab.detrend_linear\n          ,maxlags=24);\n        plt.title('Speed Autocorrelation '+str(p_num)+'_'+str(val))\n","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:36:04.150397Z","iopub.execute_input":"2022-11-09T04:36:04.151052Z","iopub.status.idle":"2022-11-09T04:36:05.910190Z","shell.execute_reply.started":"2022-11-09T04:36:04.151015Z","shell.execute_reply":"2022-11-09T04:36:05.908698Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"*Observation*\n* Autocorrelations tend to be similar:Group1(01 & 03) Group2(02 & 07) \n* Autocorrelation has a clear periodicity:Group number06\n\n","metadata":{}},{"cell_type":"markdown","source":"#### Accelation","metadata":{}},{"cell_type":"code","source":"plt.figure(figsize=[24,18])\nfor val in range(6,7):\n    for idx,p_num in enumerate(temp_df['program_number'].unique()):\n        plt.subplot(4,2,idx+1)\n        plt.acorr(temp_df.query(\"Within ==True & scene_id==@val & program_number==@p_num\")['accelation']\n          ,detrend=mlab.detrend_linear\n          ,maxlags=6);\n        plt.title('Accelation accelation '+str(p_num)+'_'+str(val))","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-09T04:36:05.912101Z","iopub.execute_input":"2022-11-09T04:36:05.912606Z","iopub.status.idle":"2022-11-09T04:36:07.758577Z","shell.execute_reply.started":"2022-11-09T04:36:05.912558Z","shell.execute_reply":"2022-11-09T04:36:07.756341Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"* Observation\n    * From the autocorrelation of acceleration, it seems that Program_number can be classified into three groups\n        * Group1: 01 & 04 & 07\n        * Group2: 02 & 03 & 05\n        * Group3: 06","metadata":{}},{"cell_type":"markdown","source":"# Reference\n* [Calculating speed, turning angle and position](https://www.kaggle.com/code/shapital/calculating-speed-turning-angle-and-position)\n* [Intro to time series analysis forecasting](https://www.kaggle.com/code/azminetoushikwasi/intro-to-time-series-analysis-forecasting)","metadata":{}},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}