{"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":"!pip install haversine","metadata":{"_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","papermill":{"duration":0.025743,"end_time":"2022-11-05T13:05:07.262023","exception":false,"start_time":"2022-11-05T13:05:07.236280","status":"completed"},"tags":[],"execution":{"iopub.status.busy":"2022-11-09T01:13:54.941666Z","iopub.execute_input":"2022-11-09T01:13:54.942183Z","iopub.status.idle":"2022-11-09T01:14:08.099875Z","shell.execute_reply.started":"2022-11-09T01:13:54.942139Z","shell.execute_reply":"2022-11-09T01:14:08.098616Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import pandas as pd\nimport numpy as np\nimport matplotlib.pyplot as plt\nimport seaborn as sb\nimport matplotlib.pyplot as plt\nfrom tqdm import tqdm\nfrom math import radians, cos, sin, asin, sqrt\nimport haversine as hs\nfrom haversine import Unit","metadata":{"execution":{"iopub.status.busy":"2022-11-09T01:14:08.101638Z","iopub.execute_input":"2022-11-09T01:14:08.102025Z","iopub.status.idle":"2022-11-09T01:14:08.763271Z","shell.execute_reply.started":"2022-11-09T01:14:08.101991Z","shell.execute_reply":"2022-11-09T01:14:08.762115Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_df = pd.read_csv('../input/big-data-derby-2022/nyra_race_table.csv')\ntrack_df = pd.read_csv('../input/big-data-derby-2022/nyra_tracking_table.csv')\njockey_df = pd.read_csv('../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\ncombine_df = track_df.merge(race_df, on = ['track_id','race_date','race_number']).merge(jockey_df, on = ['track_id', 'race_date', 'race_number', 'program_number'])","metadata":{"papermill":{"duration":14.675609,"end_time":"2022-11-05T13:05:21.960563","exception":false,"start_time":"2022-11-05T13:05:07.284954","status":"completed"},"tags":[],"execution":{"iopub.status.busy":"2022-11-09T01:17:19.225095Z","iopub.execute_input":"2022-11-09T01:17:19.225581Z","iopub.status.idle":"2022-11-09T01:17:29.700059Z","shell.execute_reply.started":"2022-11-09T01:17:19.225545Z","shell.execute_reply":"2022-11-09T01:17:29.698553Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"combine_df['index'] = combine_df.index\ncombine_df['RaceGrpID']=combine_df.track_id+'_'+combine_df.race_date+'_RN'+combine_df.race_number.astype(str)+'_PN'+combine_df.program_number.astype(str)\ncombine_df['RaceGrpID'] = combine_df['RaceGrpID'].str.strip()","metadata":{"papermill":{"duration":9.651134,"end_time":"2022-11-05T13:05:31.618958","exception":false,"start_time":"2022-11-05T13:05:21.967824","status":"completed"},"tags":[],"execution":{"iopub.status.busy":"2022-11-09T01:17:42.651424Z","iopub.execute_input":"2022-11-09T01:17:42.652021Z","iopub.status.idle":"2022-11-09T01:17:52.509949Z","shell.execute_reply.started":"2022-11-09T01:17:42.651952Z","shell.execute_reply":"2022-11-09T01:17:52.508948Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pn1_df = combine_df[combine_df.RaceGrpID=='AQU_2019-01-01_RN9_PN1'].sort_values('trakus_index')\npn2_df = combine_df[combine_df.RaceGrpID=='AQU_2019-01-01_RN9_PN2'].sort_values('trakus_index')\npn3_df = combine_df[combine_df.RaceGrpID=='AQU_2019-01-01_RN9_PN3'].sort_values('trakus_index')\npn4_df = combine_df[combine_df.RaceGrpID=='AQU_2019-01-01_RN9_PN4'].sort_values('trakus_index')\npn5_df = combine_df[combine_df.RaceGrpID=='AQU_2019-01-01_RN9_PN5'].sort_values('trakus_index')\npn6_df = combine_df[combine_df.RaceGrpID=='AQU_2019-01-01_RN9_PN6'].sort_values('trakus_index')\npn7_df = combine_df[combine_df.RaceGrpID=='AQU_2019-01-01_RN9_PN7'].sort_values('trakus_index')\npn8_df = combine_df[combine_df.RaceGrpID=='AQU_2019-01-01_RN9_PN8'].sort_values('trakus_index')\npn9_df = combine_df[combine_df.RaceGrpID=='AQU_2019-01-01_RN9_PN9'].sort_values('trakus_index')\npn10_df = combine_df[combine_df.RaceGrpID=='AQU_2019-01-01_RN9_PN10'].sort_values('trakus_index')\npn11_df = combine_df[combine_df.RaceGrpID=='AQU_2019-01-01_RN9_PN11'].sort_values('trakus_index')","metadata":{"execution":{"iopub.status.busy":"2022-11-09T01:18:14.948760Z","iopub.execute_input":"2022-11-09T01:18:14.949290Z","iopub.status.idle":"2022-11-09T01:18:20.979952Z","shell.execute_reply.started":"2022-11-09T01:18:14.949247Z","shell.execute_reply":"2022-11-09T01:18:20.978746Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pn_dict = [pn1_df, pn2_df, pn3_df, pn4_df, pn5_df, pn6_df, pn7_df, pn8_df, pn9_df, pn10_df, pn11_df]","metadata":{"papermill":{"duration":2.145745,"end_time":"2022-11-05T13:05:33.836734","exception":false,"start_time":"2022-11-05T13:05:31.690989","status":"completed"},"tags":[],"execution":{"iopub.status.busy":"2022-11-09T01:18:27.561755Z","iopub.execute_input":"2022-11-09T01:18:27.562427Z","iopub.status.idle":"2022-11-09T01:18:27.571432Z","shell.execute_reply.started":"2022-11-09T01:18:27.562370Z","shell.execute_reply":"2022-11-09T01:18:27.569687Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"distances = {}\ncum_distances = {}\nfor pn in pn_dict:\n    for grp, rows in tqdm(pn.groupby(['RaceGrpID'])):\n        rows = rows.sort_values(['trakus_index']).reset_index(drop= False)\n        prev = 0\n        cur = 0\n        cum_dist = 0\n        for cur in range(rows.shape[0]):\n            if cur == 0:\n                distances[rows.loc[cur,'index']]=0\n                cum_distances[rows.loc[cur,'index']]=cum_dist\n                continue\n            prev = cur-1\n            \n            dist = hs.haversine((rows.loc[prev,'latitude'], rows.loc[prev,'longitude']), \n                            (rows.loc[cur,'latitude'], rows.loc[cur,'longitude']),unit=Unit.MILES)\n            cum_dist = cum_dist + dist\n            distances[rows.loc[cur,'index']] = dist\n            cum_distances[rows.loc[cur,'index']] = cum_dist\n\n    pn['Dist']=pn['index'].apply(lambda x: distances.get(x))\n    pn['CumDist']=pn['index'].apply(lambda x: cum_distances.get(x))","metadata":{"papermill":{"duration":0.023507,"end_time":"2022-11-05T13:05:33.883400","exception":false,"start_time":"2022-11-05T13:05:33.859893","status":"completed"},"tags":[],"execution":{"iopub.status.busy":"2022-11-09T01:18:56.012786Z","iopub.execute_input":"2022-11-09T01:18:56.013325Z","iopub.status.idle":"2022-11-09T01:18:56.357698Z","shell.execute_reply.started":"2022-11-09T01:18:56.013284Z","shell.execute_reply":"2022-11-09T01:18:56.356256Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"hour_conversion = 60*60/0.25\nfor df in pn_dict:\n    df.sort_values('trakus_index')\n    df['Speed'] = df['Dist']*hour_conversion","metadata":{"execution":{"iopub.status.busy":"2022-11-09T01:21:25.104606Z","iopub.execute_input":"2022-11-09T01:21:25.105061Z","iopub.status.idle":"2022-11-09T01:21:25.127769Z","shell.execute_reply.started":"2022-11-09T01:21:25.105024Z","shell.execute_reply":"2022-11-09T01:21:25.126359Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"ax, fig = plt.subplots(figsize=(15,15))\nfor df in pn_dict:\n    plt.plot(df['trakus_index'], df['Speed'])\nplt.show()","metadata":{"papermill":{"duration":0.734462,"end_time":"2022-11-05T13:11:57.509792","exception":false,"start_time":"2022-11-05T13:11:56.775330","status":"completed"},"tags":[],"execution":{"iopub.status.busy":"2022-11-09T01:22:32.085008Z","iopub.execute_input":"2022-11-09T01:22:32.085485Z","iopub.status.idle":"2022-11-09T01:22:32.431287Z","shell.execute_reply.started":"2022-11-09T01:22:32.085450Z","shell.execute_reply":"2022-11-09T01:22:32.429427Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_len = max(pn1_df['trakus_index'])\nqrt_race = int(race_len/4)\n\nax, fig = plt.subplots(figsize=(15,5))\ndef get_qrt_speed(pn):\n    pn_q1_df = pn[0:qrt_race]\n    pn_q2_df = pn[qrt_race+1:qrt_race*2]\n    pn_q3_df = pn[qrt_race*2+1:qrt_race*3]\n    pn_q4_df = pn[qrt_race*3+1:qrt_race*4]\n    \n    avg_q1_speed=0\n    avg_q2_speed=0\n    avg_q3_speed=0\n    avg_q4_speed=0\n    for row in pn_q1_df:\n        avg_q1_speed = pn_q1_df['Speed'].sum()\n        avg_q1_speed = avg_q1_speed/qrt_race\n    for row in pn_q2_df:\n        avg_q2_speed = pn_q2_df['Speed'].sum()\n        avg_q2_speed = avg_q2_speed/qrt_race\n    for row in pn_q3_df:\n        avg_q3_speed = pn_q3_df['Speed'].sum()\n        avg_q3_speed = avg_q3_speed/qrt_race\n    for row in pn_q4_df:\n        avg_q4_speed = pn_q4_df['Speed'].sum()\n        avg_q4_speed = avg_q4_speed/qrt_race\n\n    return avg_q1_speed, avg_q2_speed, avg_q3_speed, avg_q4_speed\n\nfor df in pn_dict:\n    print(get_qrt_speed(df), df['Speed'].mean(), df['position_at_finish'].max())\n    plt.plot(get_qrt_speed(df))\nplt.show()","metadata":{"papermill":{"duration":0.293056,"end_time":"2022-11-05T13:11:58.001158","exception":false,"start_time":"2022-11-05T13:11:57.708102","status":"completed"},"tags":[],"execution":{"iopub.status.busy":"2022-11-09T01:23:18.110242Z","iopub.execute_input":"2022-11-09T01:23:18.110790Z","iopub.status.idle":"2022-11-09T01:23:18.514742Z","shell.execute_reply.started":"2022-11-09T01:23:18.110748Z","shell.execute_reply":"2022-11-09T01:23:18.513286Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### We can see that the first place had the highest speed ONLY in the 3rd quarter of the race. This may indicate that drafting the first half of the race is a good strategy to conserve energy. Overtaking happens during the 3rd quarter and maintaining position throughout the 4th quarter is also important since the winner still had a higher than average 4th quarter speed.","metadata":{}}]}