{"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 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":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-10-21T12:49:49.498682Z","iopub.execute_input":"2022-10-21T12:49:49.499109Z","iopub.status.idle":"2022-10-21T12:50:02.049516Z","shell.execute_reply.started":"2022-10-21T12:49:49.499077Z","shell.execute_reply":"2022-10-21T12:50:02.048106Z"},"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-10-21T12:01:11.917079Z","iopub.execute_input":"2022-10-21T12:01:11.917689Z","iopub.status.idle":"2022-10-21T12:01:20.435754Z","shell.execute_reply.started":"2022-10-21T12:01:11.917613Z","shell.execute_reply":"2022-10-21T12:01:20.434493Z"},"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-10-21T12:01:20.437648Z","iopub.execute_input":"2022-10-21T12:01:20.438077Z","iopub.status.idle":"2022-10-21T12:01:20.476876Z","shell.execute_reply.started":"2022-10-21T12:01:20.438041Z","shell.execute_reply":"2022-10-21T12:01:20.475610Z"},"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-10-21T12:01:20.478349Z","iopub.execute_input":"2022-10-21T12:01:20.478748Z","iopub.status.idle":"2022-10-21T12:01:20.502254Z","shell.execute_reply.started":"2022-10-21T12:01:20.478710Z","shell.execute_reply":"2022-10-21T12:01:20.500943Z"},"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-10-21T12:01:20.505889Z","iopub.execute_input":"2022-10-21T12:01:20.506265Z","iopub.status.idle":"2022-10-21T12:01:21.353868Z","shell.execute_reply.started":"2022-10-21T12:01:20.506232Z","shell.execute_reply":"2022-10-21T12:01:21.352717Z"},"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-10-21T12:01:21.355241Z","iopub.execute_input":"2022-10-21T12:01:21.355573Z","iopub.status.idle":"2022-10-21T12:01:26.087335Z","shell.execute_reply.started":"2022-10-21T12:01:21.355544Z","shell.execute_reply":"2022-10-21T12:01:26.085898Z"},"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-10-21T12:01:26.088917Z","iopub.execute_input":"2022-10-21T12:01:26.090027Z","iopub.status.idle":"2022-10-21T12:01:32.051040Z","shell.execute_reply.started":"2022-10-21T12:01:26.089965Z","shell.execute_reply":"2022-10-21T12:01:32.049753Z"},"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-10-21T12:01:32.052850Z","iopub.execute_input":"2022-10-21T12:01:32.053333Z","iopub.status.idle":"2022-10-21T12:01:39.394877Z","shell.execute_reply.started":"2022-10-21T12:01:32.053300Z","shell.execute_reply":"2022-10-21T12:01:39.393598Z"},"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-10-21T12:01:39.396356Z","iopub.execute_input":"2022-10-21T12:01:39.396824Z","iopub.status.idle":"2022-10-21T12:01:52.221242Z","shell.execute_reply.started":"2022-10-21T12:01:39.396791Z","shell.execute_reply":"2022-10-21T12:01:52.219949Z"},"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-10-21T12:01:52.222789Z","iopub.execute_input":"2022-10-21T12:01:52.223264Z","iopub.status.idle":"2022-10-21T12:03:11.760383Z","shell.execute_reply.started":"2022-10-21T12:01:52.223230Z","shell.execute_reply":"2022-10-21T12:03:11.759152Z"},"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-10-21T12:03:11.761795Z","iopub.execute_input":"2022-10-21T12:03:11.762145Z","iopub.status.idle":"2022-10-21T12:03:11.771772Z","shell.execute_reply.started":"2022-10-21T12:03:11.762116Z","shell.execute_reply":"2022-10-21T12:03:11.770392Z"},"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-10-21T12:03:11.773311Z","iopub.execute_input":"2022-10-21T12:03:11.774130Z","iopub.status.idle":"2022-10-21T12:03:57.492820Z","shell.execute_reply.started":"2022-10-21T12:03:11.774095Z","shell.execute_reply":"2022-10-21T12:03:57.491045Z"},"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-10-21T12:03:57.494420Z","iopub.execute_input":"2022-10-21T12:03:57.494839Z","iopub.status.idle":"2022-10-21T12:04:00.109256Z","shell.execute_reply.started":"2022-10-21T12:03:57.494803Z","shell.execute_reply":"2022-10-21T12:04:00.108182Z"},"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-10-21T12:04:00.113222Z","iopub.execute_input":"2022-10-21T12:04:00.113621Z","iopub.status.idle":"2022-10-21T12:19:09.586419Z","shell.execute_reply.started":"2022-10-21T12:04:00.113585Z","shell.execute_reply":"2022-10-21T12:19:09.585210Z"},"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-10-21T12:19:09.587996Z","iopub.execute_input":"2022-10-21T12:19:09.588347Z","iopub.status.idle":"2022-10-21T12:19:10.560328Z","shell.execute_reply.started":"2022-10-21T12:19:09.588317Z","shell.execute_reply":"2022-10-21T12:19:10.559269Z"},"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-10-21T12:19:10.561649Z","iopub.execute_input":"2022-10-21T12:19:10.562023Z","iopub.status.idle":"2022-10-21T12:25:44.371013Z","shell.execute_reply.started":"2022-10-21T12:19:10.561992Z","shell.execute_reply":"2022-10-21T12:25:44.369782Z"},"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-10-21T12:25:44.372399Z","iopub.execute_input":"2022-10-21T12:25:44.372861Z","iopub.status.idle":"2022-10-21T12:25:56.619270Z","shell.execute_reply.started":"2022-10-21T12:25:44.372830Z","shell.execute_reply":"2022-10-21T12:25:56.618078Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df['finished'].value_counts()","metadata":{"execution":{"iopub.status.busy":"2022-10-21T12:25:56.620800Z","iopub.execute_input":"2022-10-21T12:25:56.621252Z","iopub.status.idle":"2022-10-21T12:25:56.678040Z","shell.execute_reply.started":"2022-10-21T12:25:56.621207Z","shell.execute_reply":"2022-10-21T12:25:56.676774Z"},"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-10-21T12:25:56.679510Z","iopub.execute_input":"2022-10-21T12:25:56.681055Z","iopub.status.idle":"2022-10-21T12:25:59.724436Z","shell.execute_reply.started":"2022-10-21T12:25:56.681005Z","shell.execute_reply":"2022-10-21T12:25:59.723066Z"},"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-10-21T12:25:59.726398Z","iopub.execute_input":"2022-10-21T12:25:59.726887Z","iopub.status.idle":"2022-10-21T12:27:05.308280Z","shell.execute_reply.started":"2022-10-21T12:25:59.726842Z","shell.execute_reply":"2022-10-21T12:27:05.307072Z"},"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-10-21T12:27:05.310109Z","iopub.execute_input":"2022-10-21T12:27:05.311100Z","iopub.status.idle":"2022-10-21T12:27:14.155652Z","shell.execute_reply.started":"2022-10-21T12:27:05.311062Z","shell.execute_reply":"2022-10-21T12:27:14.154401Z"},"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-10-21T12:27:14.157335Z","iopub.execute_input":"2022-10-21T12:27:14.158511Z","iopub.status.idle":"2022-10-21T12:27:14.195267Z","shell.execute_reply.started":"2022-10-21T12:27:14.158462Z","shell.execute_reply":"2022-10-21T12:27:14.194090Z"},"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-10-21T12:27:14.196763Z","iopub.execute_input":"2022-10-21T12:27:14.197184Z","iopub.status.idle":"2022-10-21T12:27:14.333418Z","shell.execute_reply.started":"2022-10-21T12:27:14.197149Z","shell.execute_reply":"2022-10-21T12:27:14.332078Z"},"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-10-21T12:27:14.335434Z","iopub.execute_input":"2022-10-21T12:27:14.336346Z","iopub.status.idle":"2022-10-21T12:39:12.590241Z","shell.execute_reply.started":"2022-10-21T12:27:14.336283Z","shell.execute_reply":"2022-10-21T12:39:12.588820Z"},"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-10-21T12:50:02.052329Z","iopub.execute_input":"2022-10-21T12:50:02.053393Z","iopub.status.idle":"2022-10-21T12:50:03.502465Z","shell.execute_reply.started":"2022-10-21T12:50:02.053334Z","shell.execute_reply":"2022-10-21T12:50:03.501215Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df.columns","metadata":{"execution":{"iopub.status.busy":"2022-10-21T12:39:12.653259Z","iopub.status.idle":"2022-10-21T12:39:12.654411Z","shell.execute_reply.started":"2022-10-21T12:39:12.654178Z","shell.execute_reply":"2022-10-21T12:39:12.654203Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}