{"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":"# Description \nExtra information from this data set.\n\n## Race Table\n\n`race_id`: The unique id for race `race_date` + `_` + `race_number`.\n\n`run_up_distance`:  Distance in feet of the gate to the start of the race passed as an integer.\n\n`track_distance`: Distance in feet of the track.\n\n\n## Start Table\n`race_id`: The unique id for race `race_date` + `_` + `race_number`.\n\n## Tracking Table\n`race_id`: The unique id for race `race_date` + `_` + `race_number`.\n\n`trakus_sec`: The time in unit second.\n\n`is_finished`: The `bool` indicates if the race has finished, `True` is finished, `False` otherwise.\n\n`ref_latitude`: The average latitude of horses in the race at the moment.\n\n`ref_longitude`: The average longitude of horses in the race at the moment.\n\n`vel_lat`: The latitude component of velocity of a group of horses in the race at the moment.\n\n`vel_long`: The longitude component of velocity of a group of horses in the race at the moment.\n\n`horse_relative_position_lat`: The differnt between latitude of the horse and the ref_latitude at the moment.\n\n`horse_relative_position_long`: The differnt between longitude of the horse and the ref_longitude at the moment.\n\n`angle`: The angle between the position of the horse and the direction of group's velocity at the moment.\n\n`zone`: The relative zone of a horse in a race at the moment at the moment.\n\n`rank`: The relative rank of a horse in a race at the moment at the moment.\n","metadata":{}},{"cell_type":"code","source":"import numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-11-10T18:50:51.178048Z","iopub.execute_input":"2022-11-10T18:50:51.178691Z","iopub.status.idle":"2022-11-10T18:50:51.204220Z","shell.execute_reply.started":"2022-11-10T18:50:51.178601Z","shell.execute_reply":"2022-11-10T18:50:51.203373Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Functions","metadata":{}},{"cell_type":"code","source":"def longitudinal_distance(p1, p2, p3):\n    # calculate distance between point p3 and point p1 along vector from p1 to p2\n    dot_product = np.sum((p2-p1) * (p3-p2), axis=1)\n    return dot_product / np.linalg.norm(p2-p1, axis=1)","metadata":{"execution":{"iopub.status.busy":"2022-11-10T18:50:52.754678Z","iopub.execute_input":"2022-11-10T18:50:52.755227Z","iopub.status.idle":"2022-11-10T18:50:52.762733Z","shell.execute_reply.started":"2022-11-10T18:50:52.755178Z","shell.execute_reply":"2022-11-10T18:50:52.761554Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def convert_lat_lon_to_x_y_meters(track_df: pd.DataFrame):\n    track_df = track_df.sort_values([\"race_id\", \"program_number\", \"trakus_index\"]).copy()\n    \n    track_df[\"latitude_initial\"] = track_df.groupby([\"race_id\", \"program_number\"])[\"latitude\"].transform(lambda x: x.iloc[0])\n    track_df[\"longitude_initial\"] = track_df.groupby([\"race_id\", \"program_number\"])[\"longitude\"].transform(lambda x: x.iloc[0])\n    \n    diff_lon = track_df.longitude_initial - track_df.longitude \n    diff_lat =  track_df.latitude_initial  - track_df.latitude\n    sum_lat = track_df.latitude_initial + track_df.latitude\n    \n    track_df[\"x_m\"] = KILOMETERS_TO_METERS * diff_lon * EARTH_CIRCUMFERENCE * np.cos((sum_lat) * np.pi / 360) / 360\n    track_df[\"y_m\"] = KILOMETERS_TO_METERS * diff_lat * EARTH_CIRCUMFERENCE / 360\n    \n    return track_df.drop(columns=[\"latitude_initial\", \"longitude_initial\"])","metadata":{"execution":{"iopub.status.busy":"2022-11-10T18:50:53.726041Z","iopub.execute_input":"2022-11-10T18:50:53.726556Z","iopub.status.idle":"2022-11-10T18:50:53.744045Z","shell.execute_reply.started":"2022-11-10T18:50:53.726499Z","shell.execute_reply":"2022-11-10T18:50:53.742685Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def get_cumulative_distance(track_df: pd.DataFrame):\n    track_df = track_df.copy()\n    track_df[\"x_m_shift_1\"] = track_df.groupby([\"race_id\", \"program_number\"])[\"x_m\"].shift(1)\n    track_df[\"y_m_shift_1\"] = track_df.groupby([\"race_id\", \"program_number\"])[\"y_m\"].shift(1)\n\n    track_df[\"sum_squared\"] = (track_df[\"x_m\"] - track_df[\"x_m_shift_1\"])**2 + (track_df[\"y_m\"] - track_df[\"y_m_shift_1\"]) ** 2\n    track_df[\"distance\"] = np.sqrt(track_df[\"sum_squared\"])\n    \n    track_df[\"cumulative_distance\"] = track_df.groupby([\"race_id\", \"program_number\"])[\"distance\"].transform(np.cumsum)\n    track_df.cumulative_distance.fillna(0, inplace=True)\n    \n    return track_df.drop(columns=[\"x_m_shift_1\", \"y_m_shift_1\", \"sum_squared\", \"distance\"])","metadata":{"execution":{"iopub.status.busy":"2022-11-10T18:50:55.126907Z","iopub.execute_input":"2022-11-10T18:50:55.127287Z","iopub.status.idle":"2022-11-10T18:50:55.136066Z","shell.execute_reply.started":"2022-11-10T18:50:55.127256Z","shell.execute_reply":"2022-11-10T18:50:55.134823Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def get_dnf(track_df: pd.DataFrame):\n    track_df = track_df.copy()\n    \n    track_df[\"max_trakus_index\"] = track_df.groupby(\"race_id\")[\"trakus_index\"].transform(\"max\")\n    track_df[\"max_program_trakus_index\"] = track_df.groupby(RACE_PROGRAM_ID)[\"trakus_index\"].transform(\"max\")\n    track_df[\"dnf\"] = np.where(track_df.max_program_trakus_index.lt(track_df.max_trakus_index), True, False)\n    \n    return track_df.drop(columns=[\"max_trakus_index\", \"max_program_trakus_index\"])","metadata":{"execution":{"iopub.status.busy":"2022-11-10T18:50:55.634257Z","iopub.execute_input":"2022-11-10T18:50:55.634682Z","iopub.status.idle":"2022-11-10T18:50:55.643551Z","shell.execute_reply.started":"2022-11-10T18:50:55.634645Z","shell.execute_reply":"2022-11-10T18:50:55.641660Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def get_finish_indicator(track_df: pd.DataFrame, start_df: pd.DataFrame, race_df: pd.DataFrame):\n    df = track_df.copy()\n    start_cols = [*RACE_PROGRAM_ID, \"relative_place\"]\n    race_cols = [\"race_id\", \"track_distance\"]\n    \n    df = df.merge(start_df[start_cols], on=RACE_PROGRAM_ID, how=\"left\").merge(race_df[race_cols], on=[\"race_id\"], how=\"left\")\n    df[\"max_trakus_index\"] = df.groupby(\"race_id\")[\"trakus_index\"].transform(\"max\")\n    \n    finish_mask = df.trakus_index.eq(df.max_trakus_index) & df.relative_place.eq(0) & ~df.dnf\n    \n    finish_x = df.loc[finish_mask].groupby(\"race_id\")[\"x_m\"].max()\n    finish_y = df.loc[finish_mask].groupby(\"race_id\")[\"y_m\"].max()\n    df[\"finish_x\"] = df.race_id.map(finish_x)\n    df[\"finish_y\"] = df.race_id.map(finish_y)\n    \n    df = df.sort_values([*RACE_PROGRAM_ID, \"trakus_index\"])\n    \n    df[\"x_m_shift_1\"] = df.groupby(RACE_PROGRAM_ID)[\"x_m\"].shift(1).fillna(0)\n    df[\"y_m_shift_1\"] = df.groupby(RACE_PROGRAM_ID)[\"y_m\"].shift(1).fillna(0)\n    \n    df[\"longitudinal_distance\"] = longitudinal_distance(\n        df[[\"x_m_shift_1\", \"y_m_shift_1\"]].to_numpy(), \n        df[[\"x_m\", \"y_m\"]].to_numpy(),\n        df[[\"finish_x\", \"finish_y\"]].to_numpy()\n    )\n    \n    finished_mask = df.cumulative_distance.gt(df.track_distance) & df.longitudinal_distance.le(0)\n    df[\"is_finished\"] = np.where(finished_mask, True, False)\n    \n    df = df.sort_values([*RACE_PROGRAM_ID, \"trakus_index\"])\n    edge_case_finished_mask = ~df.groupby(RACE_PROGRAM_ID)[\"is_finished\"].tail(1) & ~df.dnf # programs with no is_finished\n    df.loc[edge_case_finished_mask, \"is_finished\"] = True\n\n    just_finished_mask = df.groupby(RACE_PROGRAM_ID)[\"is_finished\"].transform(np.cumsum).eq(1)\n    \n    finish_trakus = df.loc[just_finished_mask, [*RACE_PROGRAM_ID, \"trakus_index\"]].copy()\n    finish_trakus.rename(columns={\"trakus_index\": \"finish_trakus_index\"}, inplace=True)\n    \n    df = df.merge(finish_trakus, on=RACE_PROGRAM_ID)\n    \n    drop_cols = [\"x_m_shift_1\", \"y_m_shift_1\", \"finish_x\", \"finish_y\", \"max_trakus_index\", \"relative_place\", \"track_distance\"]\n    return df.drop(columns=drop_cols)","metadata":{"execution":{"iopub.status.busy":"2022-11-10T18:50:56.172570Z","iopub.execute_input":"2022-11-10T18:50:56.172984Z","iopub.status.idle":"2022-11-10T18:50:56.191746Z","shell.execute_reply.started":"2022-11-10T18:50:56.172950Z","shell.execute_reply":"2022-11-10T18:50:56.190645Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def angle_between(vel_x: np.ndarray, vel_y: np.ndarray, horse_x: np.ndarray, horse_y: np.ndarray):\n    dot = vel_x * horse_x + vel_y * horse_y      # dot product between [x1, y1] and [x2, y2]\n    det = vel_x * horse_y - vel_y * horse_x    # determinant\n    angle = np.arctan2(det, dot)  # atan2(y, x) or atan2(sin, cos)\n    \n    angle = np.where(angle < 0, (angle * 180 / np.pi) + 360, angle * 180 / np.pi)\n\n    return angle\n\ndef get_relative_position_table(tracking_df: pd.DataFrame):\n    # get relative position\n    df = tracking_df.copy()\n    df[[\"ref_latitude\", \"ref_longitude\"]] = df.groupby([\"race_id\", \"trakus_index\"])[[\"latitude\", \"longitude\"]].transform(\"mean\")\n    df = df.sort_values(RACE_PROGRAM_ID)\n    df[[\"vel_lat\", \"vel_long\"]] = df.groupby(RACE_PROGRAM_ID)[[\"ref_latitude\", \"ref_longitude\"]].diff(RELATIVE_POSITION_GRANULARITY).shift(-RELATIVE_POSITION_GRANULARITY).ffill()\n\n    df[\"horse_relative_position_lat\"] = df[\"latitude\"] - df[\"ref_latitude\"]\n    df[\"horse_relative_position_long\"] = df[\"longitude\"] - df[\"ref_longitude\"]\n\n    \n    # get angle\n    df[\"angle\"] = angle_between(\n        df.vel_long.values,\n        df.vel_lat.values, \n        df.horse_relative_position_long.values, \n        df.horse_relative_position_lat.values\n    )\n    # get relative distance\n    df[\"horse_relative_distance\"] = np.hypot(df.horse_relative_position_long.values, df.horse_relative_position_lat.values)\n    \n    return df\n\ndef get_zones(tracking_df: pd.DataFrame):\n    df = tracking_df.copy()\n    zones = [*ZONE_MAP.keys()]\n    zones_angles = [*ZONE_MAP.values()]\n    df[\"zone\"] = np.select([df[\"angle\"].round().astype(int).isin(angles) for angles in zones_angles], zones)\n    \n    return df\n\ndef get_ranks(tracking_df: pd.DataFrame):\n    df = tracking_df.copy()\n    df[\"cos_val\"] = df.horse_relative_distance.values * np.cos(df.angle.values * np.pi / 180)\n    df[\"rank\"] = df.groupby([\"race_id\", \"trakus_index\"])[[\"cos_val\"]].rank(ascending=False, method=\"dense\")\n    \n    return df.drop(columns=[\"cos_val\"])","metadata":{"execution":{"iopub.status.busy":"2022-11-10T18:50:57.063407Z","iopub.execute_input":"2022-11-10T18:50:57.063798Z","iopub.status.idle":"2022-11-10T18:50:57.080587Z","shell.execute_reply.started":"2022-11-10T18:50:57.063768Z","shell.execute_reply":"2022-11-10T18:50:57.079233Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Constants ","metadata":{}},{"cell_type":"code","source":"ROOT = \"/kaggle/input/big-data-derby-2022\"\nOUTPUT_DIR = \"/kaggle/working\"","metadata":{"execution":{"iopub.status.busy":"2022-11-10T18:51:03.146833Z","iopub.execute_input":"2022-11-10T18:51:03.147225Z","iopub.status.idle":"2022-11-10T18:51:03.152746Z","shell.execute_reply.started":"2022-11-10T18:51:03.147192Z","shell.execute_reply":"2022-11-10T18:51:03.151583Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"FURLONGS_TO_METERS = 201.168 \nFEET_TO_METERS = 0.3048\nKILOMETERS_TO_METERS = 1000\nEARTH_CIRCUMFERENCE = 40000\nDISTANCE_ID_TO_FURLONGS = 100\nRELATIVE_POSITION_GRANULARITY = 4 # Measure every relative position every 4 trakus","metadata":{"execution":{"iopub.status.busy":"2022-11-10T18:51:03.511605Z","iopub.execute_input":"2022-11-10T18:51:03.511994Z","iopub.status.idle":"2022-11-10T18:51:03.517830Z","shell.execute_reply.started":"2022-11-10T18:51:03.511964Z","shell.execute_reply":"2022-11-10T18:51:03.516555Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"RACE_PROGRAM_ID = [\"race_id\", \"program_number\"]\nZONE_MAP = {\n    \"front-left\": [*range(0, 45 + 1)],\n    \"front-right\": [*range(315, 360 + 1)],\n    \"right\": [*range(225, 315 + 1)],\n    \"left\": [*range(45, 135 + 1)], \n    \"back-left\": [*range(135,180 + 1)],\n    \"back-right\": [*range(180, 225 + 1)],    \n}","metadata":{"execution":{"iopub.status.busy":"2022-11-10T18:51:03.924598Z","iopub.execute_input":"2022-11-10T18:51:03.925015Z","iopub.status.idle":"2022-11-10T18:51:03.932441Z","shell.execute_reply.started":"2022-11-10T18:51:03.924984Z","shell.execute_reply":"2022-11-10T18:51:03.931008Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Race Table\n**Contains information regarding the race.**\n\n`track_id` - 3 character id for the track the race took place at. AQU -Aqueduct, BEL - Belmont, SAR - Saratoga.\n\n`race_date` - date the race took place. YYYY-MM-DD.\n\n`race_number` - Number of the race. Passed as 3 characters but can be cast or converted to int for this data set.\n\n`distance_id` - Distance of the race in furlongs passed as an integer. Example - 600 would be 6 furlongs.\n\n`course_type` - The course the race was run over passed as one character. M - Hurdle, D - Dirt, O - Outer turf, I - Inner turf, T - turf.\n\n`track_condition` - The condition of the course the race was run on passed as three characters. YL - Yielding, FM - Firm, SY - Sloppy, GD - Good, FT - Fast, MY - Muddy, SF - Soft.\n\n`run_up_distance` - Distance in feet of the gate to the start of the race passed as an integer.\n\n`race_type` - The classification of the race passed as as five characters. STK - Stakes, WCL - Waiver Claiming, WMC - Waiver Maiden Claiming, SST - Starter Stakes, SHP - Starter Handicap, CLM - Claiming, STR - Starter Allowance, AOC - Allowance Optionl Claimer, SOC - Starter Optional Claimer, MCL - Maiden Claiming, ALW - Allowance, MSW - Maiden Special Weight.\n\n`purse` - Purse in US dollars of the race passed as an money with two decimal places.\n\n`post_time` - Time of day the race began passed as 5 character. Example - 01220 would be 12:20.","metadata":{}},{"cell_type":"code","source":"race = pd.read_csv(f\"{ROOT}/nyra_race_table.csv\", parse_dates=[\"race_date\"])\nrace[\"race_id\"] = race[\"race_date\"].astype(str) + \"_\" + race[\"race_number\"].astype(str)\nrace[\"track_distance\"] = race[\"distance_id\"].div(DISTANCE_ID_TO_FURLONGS).mul(FURLONGS_TO_METERS)","metadata":{"execution":{"iopub.status.busy":"2022-11-10T18:51:04.707751Z","iopub.execute_input":"2022-11-10T18:51:04.708468Z","iopub.status.idle":"2022-11-10T18:51:04.755491Z","shell.execute_reply.started":"2022-11-10T18:51:04.708433Z","shell.execute_reply":"2022-11-10T18:51:04.754376Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Start Table\n\n**Contains information about each program in the race.**\n\n`track_id` - 3 character id for the track the race took place at. AQU -Aqueduct, BEL - Belmont, SAR - Saratoga.\n\n`race_date` - date the race took place. YYYY-MM-DD.\n\n`race_number` - Number of the race. Passed as 3 characters but can be cast or converted to int for this data set.\n\n`program_number` - Program number of the horse in the race passed as 3 characters. Should remain 3 characters as it isn't limited to just numbers. Is essentially the unique identifier of the horse in the race.\n\n`weight_carried` - An integer of the weight carried by the horse in the race.\n\n`jockey` - Name of the jockey on the horse in the race. 50 character max.\n\n`odds` - Odds to win the race passed as an integer. Divide by 100 to derive the odds to 1. Example - 1280 would be 12.8-1.\n\n`position_at_finish` - An integer of the horse's finishing position. (added to the dataset 9/8/22)","metadata":{}},{"cell_type":"code","source":"start = pd.read_csv(f\"{ROOT}/nyra_start_table.csv\", \n                    names=[\"track_id\", \"race_date\", \"race_number\", \"program_number\", \"weight_carried\",\n                           \"jockey\", \"odds\", \"position_at_finish\"],\n                    parse_dates=[\"race_date\"])\nstart[\"race_id\"] = start[\"race_date\"].astype(str) + \"_\" + start[\"race_number\"].astype(str)\nstart[\"program_number\"] = start.program_number.str.lstrip(\" \").str.rstrip(\" \")\nstart[\"no_of_positions\"] = start.groupby(\"race_id\")[\"position_at_finish\"].transform(\"max\")\nstart[\"relative_place\"] = start[\"position_at_finish\"] - start[\"no_of_positions\"]\nstart[\"normalized_place\"] = start[\"position_at_finish\"] / start[\"no_of_positions\"]\nstart[\"is_last_place\"] = (start[\"position_at_finish\"] == start[\"no_of_positions\"])","metadata":{"execution":{"iopub.status.busy":"2022-11-10T18:51:05.605425Z","iopub.execute_input":"2022-11-10T18:51:05.605808Z","iopub.status.idle":"2022-11-10T18:51:05.755446Z","shell.execute_reply.started":"2022-11-10T18:51:05.605777Z","shell.execute_reply":"2022-11-10T18:51:05.754459Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Tracking table\n\n**Contains information about the position of each horse throughout the race.**\n\n`track_id` - 3 character id for the track the race took place at. AQU -Aqueduct, BEL - Belmont, SAR - Saratoga.\n\n`race_date` - date the race took place. YYYY-MM-DD.\n\n`race_number` - Number of the race. Passed as 3 characters but can be cast or converted to int for this data set.\n\n`program_number` - Program number of the horse in the race passed as 3 characters. Should remain 3 characters as it isn't limited to just numbers. Is essentially the unique identifier of the horse in the race.\n\n`trakus_index` - The common collection of point of the lat / long of the horse in the race passed as an integer. From what we can tell, it's collected every 0.25 seconds.\n\n`latitude` - The latitude of the horse in the race passed as a float.\n\n`longitude` - The longitude of the horse in the race passed as a float.","metadata":{}},{"cell_type":"code","source":"tracking = pd.read_csv(f\"{ROOT}/nyra_tracking_table.csv\", parse_dates=[\"race_date\"])\n\n\ntracking[\"race_id\"] = tracking[\"race_date\"].astype(str) + \"_\" +tracking[\"race_number\"].astype(str)\ntracking[\"program_number\"] = tracking[\"program_number\"].str.lstrip(\" \").str.rstrip(\" \")\ntracking[\"trakus_sec\"] = (tracking[\"trakus_index\"] /  4)\n\ntracking = tracking.sort_values(RACE_PROGRAM_ID)\n","metadata":{"execution":{"iopub.status.busy":"2022-11-10T18:51:06.417923Z","iopub.execute_input":"2022-11-10T18:51:06.419102Z","iopub.status.idle":"2022-11-10T18:51:51.349046Z","shell.execute_reply.started":"2022-11-10T18:51:06.419063Z","shell.execute_reply":"2022-11-10T18:51:51.347726Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time \ntracking = (\n    tracking\n    .pipe(convert_lat_lon_to_x_y_meters)\n    .pipe(get_cumulative_distance)\n    .pipe(get_dnf)\n    .pipe(get_finish_indicator, start, race)\n    .pipe(get_relative_position_table)\n    .pipe(get_zones)\n    .pipe(get_ranks)\n)","metadata":{"execution":{"iopub.status.busy":"2022-11-10T18:51:51.351185Z","iopub.execute_input":"2022-11-10T18:51:51.351585Z","iopub.status.idle":"2022-11-10T18:53:07.023062Z","shell.execute_reply.started":"2022-11-10T18:51:51.351552Z","shell.execute_reply":"2022-11-10T18:53:07.021932Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tracking = tracking.sort_values(RACE_PROGRAM_ID)","metadata":{"execution":{"iopub.status.busy":"2022-11-10T18:53:07.024665Z","iopub.execute_input":"2022-11-10T18:53:07.025849Z","iopub.status.idle":"2022-11-10T18:53:09.452455Z","shell.execute_reply.started":"2022-11-10T18:53:07.025797Z","shell.execute_reply":"2022-11-10T18:53:09.451510Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tracking.columns","metadata":{"execution":{"iopub.status.busy":"2022-11-10T18:53:24.728010Z","iopub.execute_input":"2022-11-10T18:53:24.728441Z","iopub.status.idle":"2022-11-10T18:53:24.741928Z","shell.execute_reply.started":"2022-11-10T18:53:24.728404Z","shell.execute_reply":"2022-11-10T18:53:24.740420Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tracking.zone.unique()","metadata":{"execution":{"iopub.status.busy":"2022-11-10T18:55:23.744300Z","iopub.execute_input":"2022-11-10T18:55:23.744728Z","iopub.status.idle":"2022-11-10T18:55:24.155828Z","shell.execute_reply.started":"2022-11-10T18:55:23.744697Z","shell.execute_reply":"2022-11-10T18:55:24.154733Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import numpy as np\nimport matplotlib.pyplot as plt\n\ndef horse_relative_position_plot_zone(df):\n    fig, ax = plt.subplots(figsize=(12,8))\n    idxs = list(df.index)\n    # Vector origin location\n    X = [0]\n    Y = [0]\n\n    # Directional vectors\n    U = df[\"vel_long\"][idxs[0]]\n    V = df[\"vel_lat\"][idxs[0]]\n\n\n    # Creating plot\n    plt.quiver(X, Y, U, V, color='r', units='xy', scale=3)\n    c = {\"back-left\":\"bo\", \"back-right\":\"go\", \"right\":\"yo\", \"left\":\"co\", \"front-left\":\"c\", \"front-right\":\"m\"}\n    j = 0 \n    for i in idxs:\n        plt.plot((0, df[\"horse_relative_position_long\"][i]), (0, df[\"horse_relative_position_lat\"][i]), c[df[\"zone\"][i]], linestyle=\"--\")\n        ax.text(df[\"horse_relative_position_long\"][i], df[\"horse_relative_position_lat\"][i], df[\"program_number\"][i], size=15)\n        j += 1\n\n    # x-lim and y-lim\n    plt.xlim(-0.0002, 0.0002)\n    plt.ylim(-0.0002, 0.0002)\n\n    # Show plot with grid\n    plt.grid()\n    plt.show()","metadata":{"execution":{"iopub.status.busy":"2022-11-10T18:58:17.307128Z","iopub.execute_input":"2022-11-10T18:58:17.307588Z","iopub.status.idle":"2022-11-10T18:58:17.318243Z","shell.execute_reply.started":"2022-11-10T18:58:17.307549Z","shell.execute_reply":"2022-11-10T18:58:17.317021Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from IPython.display import display\n\ndef plot_by_trakus_index(data, steps):\n    _df = data[(data[\"trakus_index\"] == (4 * steps))]\n    _df = _df.reset_index(drop=True)\n    print(f'Race: {_df[\"race_id\"][0]}, time {steps} s.')\n    horse_relative_position_plot_zone(_df)\n    display(_df[['program_number', 'zone', 'rank']])","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:00:50.224050Z","iopub.execute_input":"2022-11-10T19:00:50.224438Z","iopub.status.idle":"2022-11-10T19:00:50.231675Z","shell.execute_reply.started":"2022-11-10T19:00:50.224409Z","shell.execute_reply":"2022-11-10T19:00:50.230274Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Zones and Rank\n\nThe zone indicates the relative position of each horse in a race. This helps us analyse the strategic position of a horse. The rank indecates the relative rank of each horse in a race. The zone and rank can be assigned using this methodology. \n1. Find the referent point of the race at the moment, which is`ref_latitude` & `ref_longtitude`. \n2. Find the going direction of the race, this helps us identify where are the front, back, left, and right of the race at the moment, which is the `vel_lat` and `vel_log`\n3. Find the horse's relative position, which is the angle between the horse's position and the direction of velocity.\n4. Assign the zone to the horse's relative position to see which range of angle should be front, back, left, and right.\n5. Assign the rank to the horse. This is measured by the cosine value between horse's position vector and the group's velocity vector.","metadata":{}},{"cell_type":"code","source":"sample_df = tracking[tracking[\"race_id\"] == '2019-01-01_9']","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:02:49.482238Z","iopub.execute_input":"2022-11-10T19:02:49.482671Z","iopub.status.idle":"2022-11-10T19:02:49.879585Z","shell.execute_reply.started":"2022-11-10T19:02:49.482639Z","shell.execute_reply":"2022-11-10T19:02:49.878219Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_by_trakus_index(sample_df, 1) # the begining of the race","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:03:04.923203Z","iopub.execute_input":"2022-11-10T19:03:04.923610Z","iopub.status.idle":"2022-11-10T19:03:05.280236Z","shell.execute_reply.started":"2022-11-10T19:03:04.923579Z","shell.execute_reply":"2022-11-10T19:03:05.279095Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_by_trakus_index(sample_df, 10) # 10 sec. letter","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:03:42.425426Z","iopub.execute_input":"2022-11-10T19:03:42.425956Z","iopub.status.idle":"2022-11-10T19:03:42.789545Z","shell.execute_reply.started":"2022-11-10T19:03:42.425910Z","shell.execute_reply":"2022-11-10T19:03:42.788536Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_by_trakus_index(sample_df, 25) # as you know the race is anticlockwise","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:04:25.180501Z","iopub.execute_input":"2022-11-10T19:04:25.180900Z","iopub.status.idle":"2022-11-10T19:04:25.544772Z","shell.execute_reply.started":"2022-11-10T19:04:25.180868Z","shell.execute_reply":"2022-11-10T19:04:25.543476Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_by_trakus_index(sample_df, 27) # They are turning","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:05:42.068671Z","iopub.execute_input":"2022-11-10T19:05:42.069121Z","iopub.status.idle":"2022-11-10T19:05:42.432954Z","shell.execute_reply.started":"2022-11-10T19:05:42.069085Z","shell.execute_reply":"2022-11-10T19:05:42.431767Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_by_trakus_index(sample_df, 30) # Turning","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:06:07.566963Z","iopub.execute_input":"2022-11-10T19:06:07.567363Z","iopub.status.idle":"2022-11-10T19:06:07.932660Z","shell.execute_reply.started":"2022-11-10T19:06:07.567302Z","shell.execute_reply":"2022-11-10T19:06:07.931371Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_by_trakus_index(sample_df, 35) # Turning... and ... The nuumber 10 took the oppotunity!","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:06:25.403042Z","iopub.execute_input":"2022-11-10T19:06:25.403482Z","iopub.status.idle":"2022-11-10T19:06:25.680118Z","shell.execute_reply.started":"2022-11-10T19:06:25.403445Z","shell.execute_reply":"2022-11-10T19:06:25.678721Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_by_trakus_index(sample_df, 40) # The nuumber 10 !! ","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:07:20.051855Z","iopub.execute_input":"2022-11-10T19:07:20.052774Z","iopub.status.idle":"2022-11-10T19:07:20.374155Z","shell.execute_reply.started":"2022-11-10T19:07:20.052735Z","shell.execute_reply":"2022-11-10T19:07:20.373093Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_by_trakus_index(sample_df, 50) # the number 10 !","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:08:06.178354Z","iopub.execute_input":"2022-11-10T19:08:06.178987Z","iopub.status.idle":"2022-11-10T19:08:06.395613Z","shell.execute_reply.started":"2022-11-10T19:08:06.178946Z","shell.execute_reply":"2022-11-10T19:08:06.394371Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_by_trakus_index(sample_df, 60) # Still the number 10 but the others are so close !! ","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:10:31.197633Z","iopub.execute_input":"2022-11-10T19:10:31.198058Z","iopub.status.idle":"2022-11-10T19:10:31.474087Z","shell.execute_reply.started":"2022-11-10T19:10:31.198022Z","shell.execute_reply":"2022-11-10T19:10:31.472721Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_by_trakus_index(sample_df, 62) # ok now it's number 3","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:13:23.988722Z","iopub.execute_input":"2022-11-10T19:13:23.989815Z","iopub.status.idle":"2022-11-10T19:13:24.207439Z","shell.execute_reply.started":"2022-11-10T19:13:23.989769Z","shell.execute_reply":"2022-11-10T19:13:24.205885Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_by_trakus_index(sample_df, 65) # Still number 3","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:14:48.759834Z","iopub.execute_input":"2022-11-10T19:14:48.760382Z","iopub.status.idle":"2022-11-10T19:14:49.121514Z","shell.execute_reply.started":"2022-11-10T19:14:48.760347Z","shell.execute_reply":"2022-11-10T19:14:49.120222Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_by_trakus_index(sample_df, 67) # Yes I thought the number 3 is the 1st place","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:15:28.383503Z","iopub.execute_input":"2022-11-10T19:15:28.384008Z","iopub.status.idle":"2022-11-10T19:15:28.736887Z","shell.execute_reply.started":"2022-11-10T19:15:28.383970Z","shell.execute_reply":"2022-11-10T19:15:28.735800Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# And yes number 3 is the first place\nstart[start[\"race_id\"] == \"2019-01-01_9\"]","metadata":{"execution":{"iopub.status.busy":"2022-11-10T19:09:55.701538Z","iopub.execute_input":"2022-11-10T19:09:55.701980Z","iopub.status.idle":"2022-11-10T19:09:55.725068Z","shell.execute_reply.started":"2022-11-10T19:09:55.701944Z","shell.execute_reply":"2022-11-10T19:09:55.723903Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"As the plots show, the rank and zone can describe the event happening along the race. Who's the lead? Who's the last? What's the position in the field they select? This helps us analyse the strategic position of the race further. ","metadata":{}},{"cell_type":"markdown","source":"# Validation Checking","metadata":{}},{"cell_type":"code","source":"finished_trakus = tracking.loc[tracking.is_finished].groupby(RACE_PROGRAM_ID)[\"trakus_index\"].min().reset_index().rename(columns={0: \"trakus_index\"})\nfinished_trakus = finished_trakus.sort_values([\"race_id\", \"trakus_index\"])\nfinished_trakus[\"rank\"] = finished_trakus.groupby(\"race_id\").trakus_index.cumcount() + 1\n\nfinished_trakus = finished_trakus.merge(start[[*RACE_PROGRAM_ID, \"position_at_finish\"]], on=RACE_PROGRAM_ID, how=\"left\")\nfinished_trakus[\"error\"] = finished_trakus[\"rank\"] - finished_trakus[\"position_at_finish\"]","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:27:53.913543Z","iopub.execute_input":"2022-11-09T09:27:53.914606Z","iopub.status.idle":"2022-11-09T09:27:54.074907Z","shell.execute_reply.started":"2022-11-09T09:27:53.914553Z","shell.execute_reply":"2022-11-09T09:27:54.073529Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"finished_trakus.error.describe()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:27:57.526274Z","iopub.execute_input":"2022-11-09T09:27:57.526708Z","iopub.status.idle":"2022-11-09T09:27:57.540090Z","shell.execute_reply.started":"2022-11-09T09:27:57.526670Z","shell.execute_reply":"2022-11-09T09:27:57.538821Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Write output","metadata":{}},{"cell_type":"code","source":"drop_cols = [\n    \"x_m\",\n    \"y_m\",\n    \"angle\",\n    \"horse_relative_distance\", \n    \"longitudinal_distance\", \n    \"race_date\", \n    \"race_number\",\n    \"ref_latitude\",\n    \"ref_longitude\",\n#     \"velocity\",\n#     \"horse_position\",\n]","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:28:09.004795Z","iopub.execute_input":"2022-11-09T09:28:09.005239Z","iopub.status.idle":"2022-11-09T09:28:09.012099Z","shell.execute_reply.started":"2022-11-09T09:28:09.005204Z","shell.execute_reply":"2022-11-09T09:28:09.010429Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race.to_csv(f\"{OUTPUT_DIR}/race-updated.csv\", index=False)\nstart.to_csv(f\"{OUTPUT_DIR}/start-updated.csv\", index=False)\ntracking.drop(columns=drop_cols).to_csv(f\"{OUTPUT_DIR}/tracking-updated.csv\", index=False)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:28:18.387426Z","iopub.execute_input":"2022-11-09T09:28:18.387904Z","iopub.status.idle":"2022-11-09T09:29:33.443590Z","shell.execute_reply.started":"2022-11-09T09:28:18.387867Z","shell.execute_reply":"2022-11-09T09:29:33.442302Z"},"trusted":true},"execution_count":null,"outputs":[]}]}