{"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":"# Analysis about the running time of the last 3 furlongs","metadata":{}},{"cell_type":"markdown","source":"## OutLine of this notebook","metadata":{}},{"cell_type":"markdown","source":"- This notebook provides a dataset of the running time of the last 3 furlongs for each horse in each race, calculated from the nyra_tracking_table.\n- check the characteristics of this data\n- I think this data is useful for predicting Darby's ranking because it is positively correlated with position_at_finish","metadata":{"execution":{"iopub.status.busy":"2022-10-16T13:35:47.604735Z","iopub.execute_input":"2022-10-16T13:35:47.605144Z","iopub.status.idle":"2022-10-16T13:35:47.633988Z","shell.execute_reply.started":"2022-10-16T13:35:47.605065Z","shell.execute_reply":"2022-10-16T13:35:47.632572Z"}}},{"cell_type":"markdown","source":"Assumption: the last row of the nyra_tracking_table for each horse in each race represents when the horse finished","metadata":{}},{"cell_type":"markdown","source":"## TOC\n* [Calculate Running time of the last 3 furlongs](#section-one)\n* [Analyze about Running time of the last 3 furlongs](#section-two)\n* [Conclusion](#conclusion)","metadata":{}},{"cell_type":"code","source":"from haversine import haversine\nimport seaborn as sns\nimport matplotlib.pyplot as plt\nimport numpy as np\nimport pandas as pd \nimport seaborn as sns\nfrom tqdm import tqdm\n\n%matplotlib inline","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-11-04T16:19:41.192099Z","iopub.execute_input":"2022-11-04T16:19:41.193258Z","iopub.status.idle":"2022-11-04T16:19:41.201448Z","shell.execute_reply.started":"2022-11-04T16:19:41.193212Z","shell.execute_reply":"2022-11-04T16:19:41.200051Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tracking_df = pd.read_csv(\"/kaggle/input/big-data-derby-2022/nyra_tracking_table.csv\")\nstart_df = pd.read_csv(\"/kaggle/input/big-data-derby-2022/nyra_start_table.csv\", header=None, names=['track_id', 'race_date', 'race_number', 'program_number', 'weight_carried', 'jockey', 'odds', 'position_at_finish'])\nrace_df = pd.read_csv(\"/kaggle/input/big-data-derby-2022/nyra_race_table.csv\")","metadata":{"execution":{"iopub.status.busy":"2022-11-04T16:19:41.450636Z","iopub.execute_input":"2022-11-04T16:19:41.450996Z","iopub.status.idle":"2022-11-04T16:19:45.382843Z","shell.execute_reply.started":"2022-11-04T16:19:41.450965Z","shell.execute_reply":"2022-11-04T16:19:45.381629Z"},"_kg_hide-input":false,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"start_df = pd.merge(\n    start_df, race_df, how='left',\n    on=['track_id', 'race_date', 'race_number']\n)","metadata":{"execution":{"iopub.status.busy":"2022-11-04T16:19:45.388408Z","iopub.execute_input":"2022-11-04T16:19:45.389082Z","iopub.status.idle":"2022-11-04T16:19:45.417018Z","shell.execute_reply.started":"2022-11-04T16:19:45.389043Z","shell.execute_reply":"2022-11-04T16:19:45.416005Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<a id=\"section-one\"></a>\n### Calculate Running time of the last 3 furlongs","metadata":{}},{"cell_type":"markdown","source":"At first, take a look at the trajectory and distance traveled of one horse in one race","metadata":{}},{"cell_type":"code","source":"sample_track_id = 'AQU'\nsample_race_date = '2019-01-01'\nsample_race_number = 1\nsample_program_number = '1  '\none_race_tracking = tracking_df.loc[\n        (tracking_df['track_id'] == sample_track_id) &\n        (tracking_df['race_date'] == sample_race_date) &\n        (tracking_df['race_number'] == sample_race_number)\n    ].sort_values('trakus_index')","metadata":{"execution":{"iopub.status.busy":"2022-11-04T16:21:30.517897Z","iopub.execute_input":"2022-11-04T16:21:30.518274Z","iopub.status.idle":"2022-11-04T16:21:31.330283Z","shell.execute_reply.started":"2022-11-04T16:21:30.518242Z","shell.execute_reply":"2022-11-04T16:21:31.329108Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import matplotlib.ticker as ticker\nsns.set()\nfig, axs = plt.subplots(1,2, figsize=(16, 9))\nsns.scatterplot(\n    data=one_race_tracking, x='longitude', y='latitude', hue='program_number',\n    ax=axs[0]\n               )\nsns.scatterplot(\n    data=one_race_tracking, x='longitude', y='latitude', hue='program_number',\n    ax=axs[1]\n               )\n\naxs[1].set_xlim([-73.8325, -73.832])\naxs[1].set_ylim([40.6706, 40.6712])\naxs[0].set_title(\n    f\"GPS plot, track_id: {sample_track_id}, race_date: {sample_race_date}, race_number: {sample_race_number}\"\n)\naxs[1].set_title(\n    f\"GPS plot at the finish line of race{sample_race_number} for each horse\"\n)\naxs[0].get_xaxis().get_major_formatter().set_useOffset(False)\naxs[0].get_xaxis().set_major_locator(ticker.MaxNLocator(integer=True))\naxs[0].get_yaxis().get_major_formatter().set_useOffset(False)\naxs[0].get_yaxis().set_major_locator(ticker.MaxNLocator(integer=True))\naxs[1].get_xaxis().get_major_formatter().set_useOffset(False)\naxs[1].get_xaxis().set_major_locator(ticker.MaxNLocator(integer=True))\naxs[1].get_yaxis().get_major_formatter().set_useOffset(False)\naxs[1].get_yaxis().set_major_locator(ticker.MaxNLocator(integer=True))\naxs[0].xaxis.set_tick_params(rotation=45)\naxs[1].xaxis.set_tick_params(rotation=45)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-11-04T16:35:18.051360Z","iopub.execute_input":"2022-11-04T16:35:18.051956Z","iopub.status.idle":"2022-11-04T16:35:18.776074Z","shell.execute_reply.started":"2022-11-04T16:35:18.051908Z","shell.execute_reply":"2022-11-04T16:35:18.773001Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The above figure shows that GPS is recorded even after the horse has reached the finish line.\nTherefore, in order to examine the running time of the last 3furlongs, we need to delete this extra post-goal GPS data.\n\nshapital has shared a solution to this complex problem.This code is very helpful!\n\n[shapital - Calculating when horses finish the race\n](https://www.kaggle.com/code/shapital/calculating-when-horses-finish-the-race)","metadata":{}},{"cell_type":"code","source":"!pip install utm\nfrom matplotlib import pyplot as plt\nimport numpy as np\nimport pandas as pd\nfrom tqdm import tqdm\nimport utm\n%matplotlib inline","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:23:10.160749Z","iopub.execute_input":"2022-11-03T17:23:10.161383Z","iopub.status.idle":"2022-11-03T17:23:22.655328Z","shell.execute_reply.started":"2022-11-03T17:23:10.161345Z","shell.execute_reply":"2022-11-03T17:23:22.654181Z"},"_kg_hide-input":true,"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# read the tracking table\ndf = pd.read_csv('../input/big-data-derby-2022/nyra_tracking_table.csv')","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:23:22.657346Z","iopub.execute_input":"2022-11-03T17:23:22.657822Z","iopub.status.idle":"2022-11-03T17:23:26.537228Z","shell.execute_reply.started":"2022-11-03T17:23:22.657778Z","shell.execute_reply":"2022-11-03T17:23:26.535987Z"},"_kg_hide-input":true,"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# read the start table\nstart_table_col_names = ['track_id', 'race_date', 'race_number', 'program_number', 'weight_carried', 'jockey', 'odds', 'position_at_finish']\nstart_table = pd.read_csv('../input/big-data-derby-2022/nyra_start_table.csv', names=start_table_col_names)","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:23:26.541791Z","iopub.execute_input":"2022-11-03T17:23:26.542626Z","iopub.status.idle":"2022-11-03T17:23:26.580548Z","shell.execute_reply.started":"2022-11-03T17:23:26.542582Z","shell.execute_reply":"2022-11-03T17:23:26.578811Z"},"_kg_hide-input":true,"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# read the race table\nrace_table = pd.read_csv('../input/big-data-derby-2022/nyra_race_table.csv')\nrace_table['race_index'] = race_table.index","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:23:26.587993Z","iopub.execute_input":"2022-11-03T17:23:26.588432Z","iopub.status.idle":"2022-11-03T17:23:26.600794Z","shell.execute_reply.started":"2022-11-03T17:23:26.588375Z","shell.execute_reply":"2022-11-03T17:23:26.599623Z"},"_kg_hide-input":true,"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# convert inconsistent program number values to a consistent pg values\npg_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'}\ndf['program_number'] = df['program_number'].astype(str)\nstart_table['program_number'] = start_table['program_number'].astype(str)\ndf['pg'] = df['program_number'].map(pg_dict)\nstart_table['pg'] = start_table['program_number'].map(pg_dict)","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:23:26.602579Z","iopub.execute_input":"2022-11-03T17:23:26.603309Z","iopub.status.idle":"2022-11-03T17:23:27.474437Z","shell.execute_reply.started":"2022-11-03T17:23:26.603274Z","shell.execute_reply":"2022-11-03T17:23:27.473475Z"},"_kg_hide-input":true,"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# merge the position_at_finish value from start table and distance_id + race_index values from race table to the tracking table\ndf = pd.merge(df, start_table[['track_id', 'race_date', 'race_number', 'pg', 'position_at_finish']], how='left', on=['track_id', 'race_date', 'race_number', 'pg'])\ndf = pd.merge(df, race_table[['track_id', 'race_date', 'race_number', 'distance_id', 'race_index']], how='left', on=['track_id', 'race_date', 'race_number'])","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:23:27.475791Z","iopub.execute_input":"2022-11-03T17:23:27.476241Z","iopub.status.idle":"2022-11-03T17:23:31.362528Z","shell.execute_reply.started":"2022-11-03T17:23:27.476203Z","shell.execute_reply":"2022-11-03T17:23:31.361546Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# sort the tracking table\ndf = df.sort_values(['track_id', 'race_date', 'race_number', 'pg', 'trakus_index']).reset_index(drop=True)","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:23:31.363926Z","iopub.execute_input":"2022-11-03T17:23:31.364800Z","iopub.status.idle":"2022-11-03T17:23:36.236018Z","shell.execute_reply.started":"2022-11-03T17:23:31.364747Z","shell.execute_reply":"2022-11-03T17:23:36.235002Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# convert lat and long to x and y\ndf['xy'] = np.array(utm.from_latlon(df['latitude'].values, df['longitude'].values)[:2]).T.tolist()","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:23:36.237381Z","iopub.execute_input":"2022-11-03T17:23:36.237995Z","iopub.status.idle":"2022-11-03T17:23:43.412269Z","shell.execute_reply.started":"2022-11-03T17:23:36.237956Z","shell.execute_reply":"2022-11-03T17:23:43.411287Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# separate the xy column to an x column and a y column\ndf[['x', 'y']] = pd.Series(np.stack(df['xy'].values).T.tolist())\nmean_track_points = df.groupby('track_id')[['x', 'y']].mean()\nmean_track_points['xy'] = mean_track_points.apply(lambda row: np.array([row['x'], row['y']]), axis=1)","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:23:43.414132Z","iopub.execute_input":"2022-11-03T17:23:43.414528Z","iopub.status.idle":"2022-11-03T17:23:54.304963Z","shell.execute_reply.started":"2022-11-03T17:23:43.414488Z","shell.execute_reply":"2022-11-03T17:23:54.304012Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# remove the mean of each track_id from the x and y values\ndf['x'] = df['x'] - df.groupby('track_id')['x'].transform('mean')\ndf['y'] = df['y'] - df.groupby('track_id')['y'].transform('mean')\ndf['xy'] = df[['x', 'y']].apply(lambda row: (row['x'], row['y']), axis=1)","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:23:54.306303Z","iopub.execute_input":"2022-11-03T17:23:54.306764Z","iopub.status.idle":"2022-11-03T17:25:03.206236Z","shell.execute_reply.started":"2022-11-03T17:23:54.306720Z","shell.execute_reply":"2022-11-03T17:25:03.205226Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"finish_lines = {'AQU': np.array([40.67151, -73.83108]),\n                'BEL': np.array([40.713305, -73.722603]),\n                'SAR': np.array([43.071821, -73.77039])}\nxy_finish_lines = {track_id: utm.from_latlon(*finish_lines[track_id])[:2] - mean_track_points.loc[track_id]['xy'] for track_id in ['AQU', 'BEL', 'SAR']}","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:25:03.207536Z","iopub.execute_input":"2022-11-03T17:25:03.208233Z","iopub.status.idle":"2022-11-03T17:25:03.216775Z","shell.execute_reply.started":"2022-11-03T17:25:03.208193Z","shell.execute_reply":"2022-11-03T17:25:03.215854Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# calculate the speed of each horse in every trakus_index\n\ndef get_speed(points):\n    # calculate speed magnitude vector per trakus index\n    speed = [0]\n    points = points.values\n    for i in range(1, len(points)):\n        speed.append(np.linalg.norm(np.array(points[i]) - np.array(points[i-1])))\n    return speed\n\ndf['speed'] = 4 * df.groupby(['race_index', 'pg'])['xy'].transform(get_speed)\n","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:25:03.218491Z","iopub.execute_input":"2022-11-03T17:25:03.218916Z","iopub.status.idle":"2022-11-03T17:25:43.999451Z","shell.execute_reply.started":"2022-11-03T17:25:03.218880Z","shell.execute_reply":"2022-11-03T17:25:43.998255Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# calculate the accumulated distance of each horse in every trakus_index\n\ndef get_distance(speed):\n    # calculate traveled distance vector per trakus index\n    return np.cumsum(speed.values)\n\ndf['distance'] = df.groupby(['race_index', 'pg'])['speed'].transform(get_distance)","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:25:44.000735Z","iopub.execute_input":"2022-11-03T17:25:44.001121Z","iopub.status.idle":"2022-11-03T17:25:45.881977Z","shell.execute_reply.started":"2022-11-03T17:25:44.001079Z","shell.execute_reply":"2022-11-03T17:25:45.880998Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# calculate the relative position of each horse in every trakus_index\n\ndef longitudinal_distance(p1, p2, p3):\n    # calculate distance between point p3 and point p2 along vector from p1 to p2\n    return round(np.dot(p2-p1, p3-p2) / np.linalg.norm(p2-p1), 2)\n\ndef get_longitudinal_distances(points):\n    # calculate longitudinal distance array to every competing horse per trakus index\n    return [[longitudinal_distance(point1[0], point1[1], point2[1]) for point2 in points.values] for point1 in points.values]\n\ndef get_position(longitudinal_distances):\n    # calculate relative position in race vector per trakus index\n    position = (np.array(longitudinal_distances)>=0).sum()\n    return np.nan if position == 0 else position\n\n\ndf_prev = df[['track_id', 'race_date', 'race_number', 'trakus_index', 'pg', 'xy']].copy()\ndf_prev['trakus_index'] = df_prev['trakus_index'] + 1\ndf = pd.merge(df, df_prev, on=['track_id', 'race_date', 'race_number', 'pg', 'trakus_index'], suffixes=['', '_prev'], how='left')\ndf['xy_pair'] = df.apply(lambda row: [np.array(row['xy_prev']), np.array(row['xy'])], axis=1)\ndf['longitudinal_distances'] = df.groupby(['track_id', 'race_date', 'race_number', 'trakus_index'])['xy_pair'].transform(get_longitudinal_distances)\ndf['position'] = df['longitudinal_distances'].apply(get_position)","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:25:45.883455Z","iopub.execute_input":"2022-11-03T17:25:45.884051Z","iopub.status.idle":"2022-11-03T17:39:36.983976Z","shell.execute_reply.started":"2022-11-03T17:25:45.884012Z","shell.execute_reply":"2022-11-03T17:39:36.983008Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# calculate the maximum trakus index of each race and each horse, and mark horses that did not finish the race (dnf)\ndf['race_max_trakus_index'] = df.groupby('race_index')['trakus_index'].transform(max)\ndf['pg_max_trakus_index'] = df.groupby(['race_index', 'pg'])['trakus_index'].transform(max)\ndf['dnf'] = (df['race_max_trakus_index'] > df['pg_max_trakus_index']).astype(int)","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:39:36.985648Z","iopub.execute_input":"2022-11-03T17:39:36.986045Z","iopub.status.idle":"2022-11-03T17:39:37.786609Z","shell.execute_reply.started":"2022-11-03T17:39:36.986007Z","shell.execute_reply":"2022-11-03T17:39:37.785564Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def get_finished(f):\n    # mark finished for all indeces after the first finished mark\n    if 1 in f.values:\n        min_finished_index = f[f == 1].index[0]\n        f[f.index.values >= min_finished_index] = 1\n        return f.values\n    return f.values\n\ndef longitudinal_distance2(p1, p2, p3):\n    # calculate distance between point p3 and point p1 along vector from p1 to p2\n    return round(np.dot(p2-p1, p3-p2) / np.linalg.norm(p2-p1), 2)\n\n# iterate over the races and calculate when each horse finished each race\nfinished = []\nfor race_index in tqdm(sorted(df['race_index'].unique())):\n    dff = df.query(f'race_index == {race_index}').copy()\n    dff_prev = dff[['track_id', 'race_date', 'race_number', 'trakus_index', 'pg', 'x', 'y']].copy()\n    dff_prev['trakus_index'] = dff_prev['trakus_index'] + 1\n    dff = pd.merge(dff, dff_prev, on=['track_id', 'race_date', 'race_number', 'pg', 'trakus_index'], suffixes=['', '_prev'], how='left')\n#     finish_xy = dff.query('trakus_index == race_max_trakus_index').sort_values('position_at_finish', ascending=False)[['x', 'y']].values[0]\n    finish_xy = xy_finish_lines[dff.iloc[0]['track_id']]\n    dff['finished'] = dff[['x', 'y', 'x_prev', 'y_prev', 'distance', 'distance_id']].apply(lambda row: longitudinal_distance2(np.array([row['x_prev'], row['y_prev']]), np.array([row['x'], row['y']]), finish_xy) <= 0 and \\\n                                                                                           row['distance'] > 8 * row['distance_id'], axis=1).astype(int)\n    dff['finished'] = dff.groupby('pg')['finished'].transform(get_finished)\n    finished.append(dff[['race_index', 'pg', 'trakus_index', 'finished']])","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:39:37.788070Z","iopub.execute_input":"2022-11-03T17:39:37.788447Z","iopub.status.idle":"2022-11-03T17:45:42.296516Z","shell.execute_reply.started":"2022-11-03T17:39:37.788396Z","shell.execute_reply":"2022-11-03T17:45:42.295482Z"},"jupyter":{"outputs_hidden":true},"_kg_hide-input":true,"collapsed":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# merge the finished results with the tracking table\nif 'finished' in df.columns:\n    df.drop(columns=['finished'], inplace=True)\ndf = pd.merge(df, pd.concat(finished, ignore_index=True), on=['race_index', 'pg', 'trakus_index'], how='left')\ndf.loc[(df['dnf'] == 0) & (df['distance'] > 8 * df['distance_id']) & (df['speed'] == 0) & (df['finished'] == 0), 'finished'] = 1","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:45:42.298260Z","iopub.execute_input":"2022-11-03T17:45:42.298971Z","iopub.status.idle":"2022-11-03T17:45:53.360243Z","shell.execute_reply.started":"2022-11-03T17:45:42.298930Z","shell.execute_reply":"2022-11-03T17:45:53.359158Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df.pivot_table(index='race_index', columns='position_at_finish', values='finished', aggfunc='sum')","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:45:53.361823Z","iopub.execute_input":"2022-11-03T17:45:53.362181Z","iopub.status.idle":"2022-11-03T17:45:56.111957Z","shell.execute_reply.started":"2022-11-03T17:45:53.362144Z","shell.execute_reply":"2022-11-03T17:45:56.110701Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def get_distance_at_finish(g):\n    if g.iloc[0]['dnf'] == 1:\n        return np.nan\n    if g.iloc[-1]['finished'] == 0:\n        return g.iloc[-1]['distance']\n    return g.query('finished == 1').iloc[0]['distance']\n\ndaf = df.groupby(['race_index', 'pg'], as_index=False).apply(lambda g: pd.Series(index=['distance_at_finish'],\n                                                                                data=get_distance_at_finish(g)))","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:45:56.113811Z","iopub.execute_input":"2022-11-03T17:45:56.114257Z","iopub.status.idle":"2022-11-03T17:46:52.280828Z","shell.execute_reply.started":"2022-11-03T17:45:56.114217Z","shell.execute_reply":"2022-11-03T17:46:52.279873Z"},"_kg_hide-input":true,"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = pd.merge(df, daf, on=['race_index', 'pg'], how='left')","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:46:52.282459Z","iopub.execute_input":"2022-11-03T17:46:52.282811Z","iopub.status.idle":"2022-11-03T17:47:00.397246Z","shell.execute_reply.started":"2022-11-03T17:46:52.282776Z","shell.execute_reply":"2022-11-03T17:47:00.396280Z"},"_kg_hide-input":true,"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df['portion_completed'] = df['distance'] / df['distance_at_finish']","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:47:00.398832Z","iopub.execute_input":"2022-11-03T17:47:00.399231Z","iopub.status.idle":"2022-11-03T17:47:00.439348Z","shell.execute_reply.started":"2022-11-03T17:47:00.399191Z","shell.execute_reply":"2022-11-03T17:47:00.438384Z"},"_kg_hide-input":true,"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df['portion_completed_bin'] = pd.cut(df['portion_completed'], bins=np.arange(0, 1.01, 0.1))","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:47:00.445257Z","iopub.execute_input":"2022-11-03T17:47:00.445573Z","iopub.status.idle":"2022-11-03T17:47:00.560839Z","shell.execute_reply.started":"2022-11-03T17:47:00.445544Z","shell.execute_reply":"2022-11-03T17:47:00.559788Z"},"_kg_hide-input":true,"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"p = df.groupby(['race_index', 'pg', 'portion_completed_bin']).apply(lambda g: g.query('finished == 0').iloc[-1][['position', 'position_at_finish']]).dropna().reset_index()","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:47:00.562401Z","iopub.execute_input":"2022-11-03T17:47:00.562888Z","iopub.status.idle":"2022-11-03T17:56:30.762446Z","shell.execute_reply.started":"2022-11-03T17:47:00.562849Z","shell.execute_reply":"2022-11-03T17:56:30.761468Z"},"_kg_hide-input":true,"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"portion_completed_bins = df['portion_completed_bin'].unique().dropna()\nfig, axs = plt.subplots(2, 2, figsize=(15,10))\nfor i, ax in zip([0, 3, 6, 9], axs.ravel()):\n    px = p[p['portion_completed_bin'] == portion_completed_bins[i]].query('position <= 10 and position_at_finish <= 10').pivot_table(index='position', columns='position_at_finish', values='race_index', aggfunc='count')\n    px.divide(px.sum(axis=0), axis=1).plot(ax=ax, marker='.', grid=True, title=portion_completed_bins[i])","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:56:30.763978Z","iopub.execute_input":"2022-11-03T17:56:30.764570Z","iopub.status.idle":"2022-11-03T17:56:31.987976Z","shell.execute_reply.started":"2022-11-03T17:56:30.764531Z","shell.execute_reply":"2022-11-03T17:56:31.987104Z"},"jupyter":{"source_hidden":true,"outputs_hidden":true},"_kg_hide-input":true,"collapsed":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tracking_df = df.copy()","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:56:31.989150Z","iopub.execute_input":"2022-11-03T17:56:31.990205Z","iopub.status.idle":"2022-11-03T17:56:33.151045Z","shell.execute_reply.started":"2022-11-03T17:56:31.990169Z","shell.execute_reply":"2022-11-03T17:56:33.149457Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tracking_df = tracking_df.loc[tracking_df['finished']==0]","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:56:33.156410Z","iopub.execute_input":"2022-11-03T17:56:33.158855Z","iopub.status.idle":"2022-11-03T17:56:34.716593Z","shell.execute_reply.started":"2022-11-03T17:56:33.158815Z","shell.execute_reply":"2022-11-03T17:56:34.715478Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Extra post-goal GPS information has been removed from the tracking data. Next, I would like to calculate the running time of the last 3furlongs ","metadata":{}},{"cell_type":"code","source":"def haversine_dist(lat1, lon1, lat2, lon2):\n    p1 = (lat1, lon1)\n    p2 = (lat2, lon2)\n    dist = haversine(p1, p2, unit='m')\n    return dist","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:56:34.718249Z","iopub.execute_input":"2022-11-03T17:56:34.718768Z","iopub.status.idle":"2022-11-03T17:56:34.724191Z","shell.execute_reply.started":"2022-11-03T17:56:34.718727Z","shell.execute_reply":"2022-11-03T17:56:34.723200Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def time_of_last_three_furlongs(track_id: str, race_date: str, race_number: int, program_number: int, tracking_df: pd.DataFrame):\n    \"\"\"Calculate the running time of the last 3 halons.\n    Assuming that the last line of the GPS is the moment the horse reaches the finish line.\n    \"\"\"\n    one_tracking_df =  tracking_df.loc[\n        (tracking_df['track_id'] == track_id) &\n        (tracking_df['race_date'] == race_date) &\n        (tracking_df['race_number'] == race_number) &\n        (tracking_df['program_number'] == program_number)\n    ].sort_values('trakus_index')\n    try:\n        one_tracking_df['lat_shift'] = one_tracking_df['latitude'].shift()\n        one_tracking_df['lon_shift'] = one_tracking_df['longitude'].shift()\n        one_tracking_df['race_dist'] = one_tracking_df.apply(lambda x: haversine_dist(x['latitude'], x['longitude'], x['lat_shift'], x['lon_shift']), axis=1)\n        one_tracking_df['race_dist_inv_sum'] = one_tracking_df['race_dist'].values[::-1].cumsum()\n        # trakus_index - The common collection of point of the lat / long of the horse in the race passed as an integer.\n        # From what we can tell, it's collected every 0.25 seconds.\n        time_of_last_three_furlongs = one_tracking_df.loc[one_tracking_df['race_dist_inv_sum'] >= 600]['trakus_index'].values[0] * 0.25\n    except IndexError:\n        return np.nan\n    except Exception as e:\n        return np.nan\n    \n    return time_of_last_three_furlongs","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:56:34.815252Z","iopub.execute_input":"2022-11-03T17:56:34.815718Z","iopub.status.idle":"2022-11-03T17:56:34.826191Z","shell.execute_reply.started":"2022-11-03T17:56:34.815681Z","shell.execute_reply":"2022-11-03T17:56:34.825509Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"This code takes about 4.5h to complete so it is recommended to use the data from the output...","metadata":{}},{"cell_type":"code","source":"%%time\nfor idx, (track_id, race_date, race_number, program_number) in \\\ntqdm(enumerate(zip(start_df['track_id'].values, start_df['race_date'], start_df['race_number'], start_df['program_number'])), total=len(start_df)):\n    start_df.at[\n        (start_df['track_id'] == track_id) &\n        (start_df['race_date'] == race_date) &\n        (start_df['race_number'] == race_number) &\n        (start_df['program_number'] == program_number)\n    , 'time_of_last_three_furlongs'] = time_of_last_three_furlongs(\n        track_id, race_date, race_number, program_number, tracking_df\n    )\nstart_df.to_csv(\"/kaggle/working/start_with_three_furlongs.csv\")\nthree_furlongs_df = start_df.copy()","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:56:34.827492Z","iopub.execute_input":"2022-11-03T17:56:34.828619Z","iopub.status.idle":"2022-11-03T17:59:18.105615Z","shell.execute_reply.started":"2022-11-03T17:56:34.828582Z","shell.execute_reply":"2022-11-03T17:59:18.104626Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<a id=\"section-two\"></a>\n### Analyze about Running time of the last 3 furlongs","metadata":{}},{"cell_type":"code","source":"#three_furlongs_df = pd.read_csv(\"/kaggle/input/three-furlongs/start_with_three_furlongs.csv\")","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:59:18.107026Z","iopub.execute_input":"2022-11-03T17:59:18.107877Z","iopub.status.idle":"2022-11-03T17:59:18.112675Z","shell.execute_reply.started":"2022-11-03T17:59:18.107836Z","shell.execute_reply":"2022-11-03T17:59:18.111651Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"three_furlongs_df.head()","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:59:18.114285Z","iopub.execute_input":"2022-11-03T17:59:18.114651Z","iopub.status.idle":"2022-11-03T17:59:18.134583Z","shell.execute_reply.started":"2022-11-03T17:59:18.114615Z","shell.execute_reply":"2022-11-03T17:59:18.133409Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"three_furlongs_per_position = three_furlongs_df.groupby([\"position_at_finish\", \"distance_id\", \"course_type\"])[\"time_of_last_three_furlongs\"].mean().reset_index().sort_values(by=\"distance_id\")","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:59:18.135769Z","iopub.status.idle":"2022-11-03T17:59:18.136496Z","shell.execute_reply.started":"2022-11-03T17:59:18.136217Z","shell.execute_reply":"2022-11-03T17:59:18.136251Z"},"_kg_hide-input":true,"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"hurdle_df = three_furlongs_per_position.loc[three_furlongs_per_position[\"course_type\"] == \"M\"]\ndart_df = three_furlongs_per_position.loc[three_furlongs_per_position[\"course_type\"] == \"D\"]\nouter_df = three_furlongs_per_position.loc[three_furlongs_per_position[\"course_type\"] == \"O\"]\ninner_df = three_furlongs_per_position.loc[three_furlongs_per_position[\"course_type\"] == \"I\"]\nturf_df = three_furlongs_per_position.loc[three_furlongs_per_position[\"course_type\"] == \"T\"]","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:59:18.137824Z","iopub.status.idle":"2022-11-03T17:59:18.138553Z","shell.execute_reply.started":"2022-11-03T17:59:18.138279Z","shell.execute_reply":"2022-11-03T17:59:18.138303Z"},"_kg_hide-input":true,"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"dart_df.round(2).pivot(\n    index='position_at_finish', columns='distance_id', values='time_of_last_three_furlongs'\n)","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:59:18.139852Z","iopub.status.idle":"2022-11-03T17:59:18.140582Z","shell.execute_reply.started":"2022-11-03T17:59:18.140308Z","shell.execute_reply":"2022-11-03T17:59:18.140332Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"- 1st's time_of_last_three_furlongs by course_type","metadata":{}},{"cell_type":"code","source":"time_of_last_three_furlongs_of_1st = pd.concat([\n    dart_df.loc[dart_df[\"position_at_finish\"] == 1],\n    turf_df.loc[turf_df[\"position_at_finish\"] == 1],\n    outer_df.loc[outer_df[\"position_at_finish\"] == 1],\n    inner_df.loc[inner_df[\"position_at_finish\"] == 1],\n    hurdle_df.loc[hurdle_df[\"position_at_finish\"] == 1]\n])","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:59:18.141874Z","iopub.status.idle":"2022-11-03T17:59:18.142609Z","shell.execute_reply.started":"2022-11-03T17:59:18.142332Z","shell.execute_reply":"2022-11-03T17:59:18.142356Z"},"_kg_hide-input":true,"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots(figsize=(16, 9))\nsns.set()\nsns.lineplot(\n    data=time_of_last_three_furlongs_of_1st,\n    x='distance_id',\n    y='time_of_last_three_furlongs',\n    hue='course_type'\n)\nplt.title(f\"distance_id and Running time of the last 3 furlongs for each course_type\")\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:59:18.143883Z","iopub.status.idle":"2022-11-03T17:59:18.144618Z","shell.execute_reply.started":"2022-11-03T17:59:18.144345Z","shell.execute_reply":"2022-11-03T17:59:18.144370Z"},"_kg_hide-input":true,"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"- The Running time of the last 3 furlongs of turf, inner_turf, and outer_turf are close values\n- Dart has a longer Running time of the last 3 furlongs than turf\n- Hurdle race has a longer distance and shows a completely different trend from other course_types\n- Curiously, in Turf and Dart, the longer the distance, the shorter the time for the last three furlongs.\n    - Is the grade of the race affected?\n    - Turf and Dart races with longer distances need different tactics?","metadata":{}},{"cell_type":"code","source":"fig, ax = plt.subplots(figsize=(16, 9))\nsns.lineplot(\n    data=dart_df.round(2).pivot(\n        index='position_at_finish',\n        columns='distance_id',\n        values='time_of_last_three_furlongs'\n    )\n)\nplt.title(\n    (f\"position_at_finish and Running time of the last 3 furlongs for each distance_id: Dart course\\n\"\n     f\"he position_at_finish and the running time of the last 3 furlongs are positively correlated.\")\n)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-11-04T16:42:57.378408Z","iopub.execute_input":"2022-11-04T16:42:57.378787Z","iopub.status.idle":"2022-11-04T16:42:57.646124Z","shell.execute_reply.started":"2022-11-04T16:42:57.378753Z","shell.execute_reply":"2022-11-04T16:42:57.644865Z"},"_kg_hide-input":true,"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"dart_df[['position_at_finish', 'time_of_last_three_furlongs']].corr()","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:59:18.147908Z","iopub.status.idle":"2022-11-03T17:59:18.148625Z","shell.execute_reply.started":"2022-11-03T17:59:18.148364Z","shell.execute_reply":"2022-11-03T17:59:18.148387Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"- the position_at_finish and the running time of the last 3 furlongs are positively correlated.","metadata":{}},{"cell_type":"code","source":"fig, ax = plt.subplots(figsize=(16, 9))\nsns.lineplot(\n    data=turf_df.round(2).pivot(\n        index='position_at_finish',\n        columns='distance_id',\n        values='time_of_last_three_furlongs'\n    )\n)\nplt.title(\n    f\"position_at_finish and Running time of the last 3 furlongs for each distance_id: Turf course\"\n)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:59:18.149891Z","iopub.status.idle":"2022-11-03T17:59:18.150614Z","shell.execute_reply.started":"2022-11-03T17:59:18.150343Z","shell.execute_reply":"2022-11-03T17:59:18.150367Z"},"_kg_hide-input":true,"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"turf_df[['position_at_finish', 'time_of_last_three_furlongs']].corr()","metadata":{"execution":{"iopub.status.busy":"2022-11-03T17:59:18.151895Z","iopub.status.idle":"2022-11-03T17:59:18.152605Z","shell.execute_reply.started":"2022-11-03T17:59:18.152341Z","shell.execute_reply":"2022-11-03T17:59:18.152364Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"- The correlation is stronger for Turf. Is this because Dart tends to have a leading advantage in general?","metadata":{}},{"cell_type":"markdown","source":"<a id=\"conclusion\"></a>\n### Conclusion\n- This notebook provides a dataset of the running time of the last 3 furlongs for each horse in each race, calculated from the nyra_tracking_table.\n- check the characteristics of this feature.\n- I think this feature is useful for predicting Darby's ranking because it is positively correlated with position_at_finish.","metadata":{}}]}