{"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\nimport numpy as np\nimport pandas as pd\nfrom tqdm import tqdm\nimport utm","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-10-12T18:03:06.192799Z","iopub.execute_input":"2022-10-12T18:03:06.193441Z","iopub.status.idle":"2022-10-12T18:03:19.908226Z","shell.execute_reply.started":"2022-10-12T18:03:06.193313Z","shell.execute_reply":"2022-10-12T18:03:19.906624Z"},"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-12T18:03:19.910723Z","iopub.execute_input":"2022-10-12T18:03:19.911066Z","iopub.status.idle":"2022-10-12T18:03:28.192874Z","shell.execute_reply.started":"2022-10-12T18:03:19.911034Z","shell.execute_reply":"2022-10-12T18:03:28.191734Z"},"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-12T18:03:28.196371Z","iopub.execute_input":"2022-10-12T18:03:28.197169Z","iopub.status.idle":"2022-10-12T18:03:28.230307Z","shell.execute_reply.started":"2022-10-12T18:03:28.197104Z","shell.execute_reply":"2022-10-12T18:03:28.229300Z"},"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-12T18:03:28.233163Z","iopub.execute_input":"2022-10-12T18:03:28.233889Z","iopub.status.idle":"2022-10-12T18:03:28.254448Z","shell.execute_reply.started":"2022-10-12T18:03:28.233836Z","shell.execute_reply":"2022-10-12T18:03:28.253337Z"},"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-12T18:03:28.255903Z","iopub.execute_input":"2022-10-12T18:03:28.256402Z","iopub.status.idle":"2022-10-12T18:03:29.071295Z","shell.execute_reply.started":"2022-10-12T18:03:28.256345Z","shell.execute_reply":"2022-10-12T18:03:29.070466Z"},"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-12T18:03:29.072831Z","iopub.execute_input":"2022-10-12T18:03:29.074021Z","iopub.status.idle":"2022-10-12T18:03:33.567040Z","shell.execute_reply.started":"2022-10-12T18:03:29.073981Z","shell.execute_reply":"2022-10-12T18:03:33.565864Z"},"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-12T18:03:33.568584Z","iopub.execute_input":"2022-10-12T18:03:33.569159Z","iopub.status.idle":"2022-10-12T18:03:39.162320Z","shell.execute_reply.started":"2022-10-12T18:03:33.569107Z","shell.execute_reply":"2022-10-12T18:03:39.161229Z"},"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-12T18:03:39.163780Z","iopub.execute_input":"2022-10-12T18:03:39.164136Z","iopub.status.idle":"2022-10-12T18:03:46.102945Z","shell.execute_reply.started":"2022-10-12T18:03:39.164083Z","shell.execute_reply":"2022-10-12T18:03:46.102161Z"},"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())","metadata":{"execution":{"iopub.status.busy":"2022-10-12T18:03:46.104133Z","iopub.execute_input":"2022-10-12T18:03:46.104548Z","iopub.status.idle":"2022-10-12T18:03:57.898628Z","shell.execute_reply.started":"2022-10-12T18:03:46.104510Z","shell.execute_reply":"2022-10-12T18:03:57.897448Z"},"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-12T18:03:57.903376Z","iopub.execute_input":"2022-10-12T18:03:57.904251Z","iopub.status.idle":"2022-10-12T18:05:15.168786Z","shell.execute_reply.started":"2022-10-12T18:03:57.904201Z","shell.execute_reply":"2022-10-12T18:05:15.167616Z"},"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-12T18:05:15.170674Z","iopub.execute_input":"2022-10-12T18:05:15.171135Z","iopub.status.idle":"2022-10-12T18:05:59.080000Z","shell.execute_reply.started":"2022-10-12T18:05:15.171074Z","shell.execute_reply":"2022-10-12T18:05:59.079130Z"},"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-12T18:05:59.081589Z","iopub.execute_input":"2022-10-12T18:05:59.082384Z","iopub.status.idle":"2022-10-12T18:06:01.583880Z","shell.execute_reply.started":"2022-10-12T18:05:59.082346Z","shell.execute_reply":"2022-10-12T18:06:01.582668Z"},"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-12T18:06:01.585599Z","iopub.execute_input":"2022-10-12T18:06:01.585916Z","iopub.status.idle":"2022-10-12T18:06:02.313042Z","shell.execute_reply.started":"2022-10-12T18:06:01.585889Z","shell.execute_reply":"2022-10-12T18:06:02.312075Z"},"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_distance(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[['trakus_index', 'pg', 'x', 'y']].copy()\n    dff_prev['trakus_index'] = dff_prev['trakus_index'] + 1\n    dff = pd.merge(dff, dff_prev, on=['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    dff['finished'] = dff.apply(lambda row: longitudinal_distance(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-12T18:06:02.314207Z","iopub.execute_input":"2022-10-12T18:06:02.314522Z","iopub.status.idle":"2022-10-12T18:12:10.144507Z","shell.execute_reply.started":"2022-10-12T18:06:02.314494Z","shell.execute_reply":"2022-10-12T18:12:10.143379Z"},"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-12T18:12:10.145837Z","iopub.execute_input":"2022-10-12T18:12:10.146136Z","iopub.status.idle":"2022-10-12T18:12:17.854483Z","shell.execute_reply.started":"2022-10-12T18:12:10.146095Z","shell.execute_reply":"2022-10-12T18:12:17.853528Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df['finished'].value_counts()","metadata":{"execution":{"iopub.status.busy":"2022-10-12T18:12:17.855623Z","iopub.execute_input":"2022-10-12T18:12:17.855917Z","iopub.status.idle":"2022-10-12T18:12:17.911493Z","shell.execute_reply.started":"2022-10-12T18:12:17.855890Z","shell.execute_reply":"2022-10-12T18:12:17.910429Z"},"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-12T18:19:05.635865Z","iopub.execute_input":"2022-10-12T18:19:05.636794Z","iopub.status.idle":"2022-10-12T18:19:05.994743Z","shell.execute_reply.started":"2022-10-12T18:19:05.636759Z","shell.execute_reply":"2022-10-12T18:19:05.993515Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#import os\n#os.chdir(r'kaggle/working')\n\ndf.pivot_table(index='race_index', columns='position_at_finish', values='finished', aggfunc='sum').to_csv(r'df_name.csv')\n\nfrom IPython.display import FileLink\nFileLink(r'df_name.csv')","metadata":{"execution":{"iopub.status.busy":"2022-10-12T18:25:09.948201Z","iopub.execute_input":"2022-10-12T18:25:09.948615Z","iopub.status.idle":"2022-10-12T18:25:10.289097Z","shell.execute_reply.started":"2022-10-12T18:25:09.948578Z","shell.execute_reply":"2022-10-12T18:25:10.288146Z"},"trusted":true},"execution_count":null,"outputs":[]}]}