{"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\nimport utm\nimport math\nfrom tqdm.auto import tqdm\ntqdm.pandas()","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-08-25T11:09:06.270369Z","iopub.execute_input":"2022-08-25T11:09:06.271385Z","iopub.status.idle":"2022-08-25T11:09:16.180741Z","shell.execute_reply.started":"2022-08-25T11:09:06.271342Z","shell.execute_reply":"2022-08-25T11:09:16.179517Z"},"trusted":true},"execution_count":null,"outputs":[]},{"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","metadata":{"execution":{"iopub.status.busy":"2022-08-25T11:07:54.090634Z","iopub.execute_input":"2022-08-25T11:07:54.091316Z","iopub.status.idle":"2022-08-25T11:07:54.098884Z","shell.execute_reply.started":"2022-08-25T11:07:54.091276Z","shell.execute_reply":"2022-08-25T11:07:54.097658Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"First load the tracking data table","metadata":{}},{"cell_type":"code","source":"df = pd.read_csv('../input/big-data-derby-2022/nyra_tracking_table.csv')","metadata":{"execution":{"iopub.status.busy":"2022-08-25T11:07:58.510464Z","iopub.execute_input":"2022-08-25T11:07:58.510848Z","iopub.status.idle":"2022-08-25T11:08:06.501647Z","shell.execute_reply.started":"2022-08-25T11:07:58.510815Z","shell.execute_reply":"2022-08-25T11:08:06.500534Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Then convert the program_number to standard values to fix the issue of a horse having seamingly two distinct program_number in a single race","metadata":{}},{"cell_type":"code","source":"df['program_number'] = df['program_number'].astype(str)\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['pg'] = df['program_number'].map(pg_dict)","metadata":{"execution":{"iopub.status.busy":"2022-08-25T11:08:06.503868Z","iopub.execute_input":"2022-08-25T11:08:06.504550Z","iopub.status.idle":"2022-08-25T11:08:07.526404Z","shell.execute_reply.started":"2022-08-25T11:08:06.504511Z","shell.execute_reply":"2022-08-25T11:08:07.525315Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Now sort the DataFrame by (trakus_index, race_date, race_number, pg, trakus_index)","metadata":{"execution":{"iopub.status.busy":"2022-08-25T07:27:29.842186Z","iopub.execute_input":"2022-08-25T07:27:29.842971Z","iopub.status.idle":"2022-08-25T07:27:29.850183Z","shell.execute_reply.started":"2022-08-25T07:27:29.842933Z","shell.execute_reply":"2022-08-25T07:27:29.848776Z"}}},{"cell_type":"code","source":"df = df.sort_values(['track_id', 'race_date', 'race_number', 'pg', 'trakus_index']).reset_index(drop=True)","metadata":{"execution":{"iopub.status.busy":"2022-08-25T11:08:07.531253Z","iopub.execute_input":"2022-08-25T11:08:07.533847Z","iopub.status.idle":"2022-08-25T11:08:13.966393Z","shell.execute_reply.started":"2022-08-25T11:08:07.533808Z","shell.execute_reply":"2022-08-25T11:08:13.965362Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Convert the latitude and longitude values to UTM coordinates (meters)","metadata":{}},{"cell_type":"code","source":"df['xy'] = np.array(utm.from_latlon(df['latitude'].values, df['longitude'].values)[:2]).T.tolist()","metadata":{"execution":{"iopub.status.busy":"2022-08-25T11:15:28.182957Z","iopub.execute_input":"2022-08-25T11:15:28.183335Z","iopub.status.idle":"2022-08-25T11:15:36.170508Z","shell.execute_reply.started":"2022-08-25T11:15:28.183303Z","shell.execute_reply":"2022-08-25T11:15:36.169534Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Calculating horses speeds and turning angles","metadata":{}},{"cell_type":"markdown","source":"Calculate the speed of each horse in every trakus_index using the distances between every two consecutive time stamps","metadata":{}},{"cell_type":"code","source":"def 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\ndf['speed'] = df.groupby(['track_id', 'race_date', 'race_number', 'pg'])['xy'].progress_transform(get_speed)","metadata":{"execution":{"iopub.status.busy":"2022-08-25T11:15:39.999972Z","iopub.execute_input":"2022-08-25T11:15:40.000532Z","iopub.status.idle":"2022-08-25T11:17:26.533577Z","shell.execute_reply.started":"2022-08-25T11:15:40.000497Z","shell.execute_reply":"2022-08-25T11:17:26.532650Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Calculate the turning angle of each horse in every trakus_index using the distances between every 3 consecutive time stamps","metadata":{}},{"cell_type":"code","source":"def getAngle(a, b, c, thresh=5):\n    ang = 180 - math.degrees(math.atan2(c[1]-b[1], c[0]-b[0]) - math.atan2(a[1]-b[1], a[0]-b[0]))\n    if ang < -180:\n        ang += 360\n    if ang > 180:\n        ang -= 360\n    if abs(ang) > thresh:\n        return np.nan\n    return ang\n\ndef get_turning_angle(points):\n    turning_angle = [0]\n    for i in range(1, len(points.values) - 1):\n        turning_angle.append(getAngle(points.values[i-1], points.values[i], points.values[i+1]))\n    turning_angle.append(0)\n    return turning_angle\n\ndf['turning_angle'] = df.groupby(['track_id', 'race_date', 'race_number', 'pg'])['xy'].progress_transform(get_turning_angle)","metadata":{"execution":{"iopub.status.busy":"2022-08-25T11:17:26.535567Z","iopub.execute_input":"2022-08-25T11:17:26.536155Z","iopub.status.idle":"2022-08-25T11:17:58.275822Z","shell.execute_reply.started":"2022-08-25T11:17:26.536115Z","shell.execute_reply":"2022-08-25T11:17:58.274884Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Calculating horses relative positions in the race","metadata":{}},{"cell_type":"markdown","source":"We need the coordinates of the previous time stamp.\n\nFirst make a copy the DataFrame, then add 1 to the trakus index, finally merge the two DataFrames","metadata":{}},{"cell_type":"code","source":"df_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')","metadata":{"execution":{"iopub.status.busy":"2022-08-25T11:17:58.277894Z","iopub.execute_input":"2022-08-25T11:17:58.278273Z","iopub.status.idle":"2022-08-25T11:18:06.406241Z","shell.execute_reply.started":"2022-08-25T11:17:58.278236Z","shell.execute_reply":"2022-08-25T11:18:06.405214Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Make a new column containing a pair of the previous and current coordinates","metadata":{}},{"cell_type":"code","source":"df['xy_pair'] = df.progress_apply(lambda row: [np.array(row['xy_prev']), np.array(row['xy'])], axis=1)","metadata":{"execution":{"iopub.status.busy":"2022-08-25T11:18:51.116976Z","iopub.execute_input":"2022-08-25T11:18:51.117329Z","iopub.status.idle":"2022-08-25T11:20:33.221891Z","shell.execute_reply.started":"2022-08-25T11:18:51.117299Z","shell.execute_reply":"2022-08-25T11:20:33.220698Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Calculate the lateral and longitudinal distances of each horse from all horses in the race in every trakus_index","metadata":{}},{"cell_type":"code","source":"def lateral_distance(p1, p2, p3):\n    return round(np.cross(p2-p1, p1-p3) / np.linalg.norm(p2-p1), 2)\n\ndef longitudinal_distance(p1, p2, p3):\n    return round(np.dot(p2-p1, p3-p2) / np.linalg.norm(p2-p1), 2)\n\ndef get_lateral_distances(points):\n    return [[lateral_distance(point1[0], point1[1], point2[1]) for point2 in points.values] for point1 in points.values]\n\ndef get_longitudinal_distances(points):\n    return [[longitudinal_distance(point1[0], point1[1], point2[1]) for point2 in points.values] for point1 in points.values]\n\ndf['lateral_distances'] = df.groupby(['track_id', 'race_date', 'race_number', 'trakus_index'])['xy_pair'].progress_transform(get_lateral_distances)\ndf['longitudinal_distances'] = df.groupby(['track_id', 'race_date', 'race_number', 'trakus_index'])['xy_pair'].progress_transform(get_longitudinal_distances)","metadata":{"execution":{"iopub.status.busy":"2022-08-25T11:20:33.223825Z","iopub.execute_input":"2022-08-25T11:20:33.224790Z","iopub.status.idle":"2022-08-25T12:29:08.357845Z","shell.execute_reply.started":"2022-08-25T11:20:33.224751Z","shell.execute_reply":"2022-08-25T12:29:08.356782Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Finally, calculate the current position of each horse in the race in every trakus_index","metadata":{}},{"cell_type":"code","source":"def get_position(longitudinal_distances):\n    position = (np.array(longitudinal_distances) >= 0).sum()\n    return np.nan if position == 0 else position\n\ndf['position'] = df['longitudinal_distances'].progress_apply(get_position)","metadata":{"execution":{"iopub.status.busy":"2022-08-25T12:40:36.952192Z","iopub.execute_input":"2022-08-25T12:40:36.953095Z","iopub.status.idle":"2022-08-25T12:41:15.957879Z","shell.execute_reply.started":"2022-08-25T12:40:36.953046Z","shell.execute_reply":"2022-08-25T12:41:15.956955Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df.head()","metadata":{"execution":{"iopub.status.busy":"2022-08-25T12:41:17.947461Z","iopub.execute_input":"2022-08-25T12:41:17.947836Z","iopub.status.idle":"2022-08-25T12:41:17.979955Z","shell.execute_reply.started":"2022-08-25T12:41:17.947804Z","shell.execute_reply":"2022-08-25T12:41:17.978381Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}