{"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":"<div style=\"text-align:center;padding:5px\">\n<img src=\"https://media.istockphoto.com/id/514459091/vector/gallop-on-horses.jpg?s=612x612&w=0&k=20&c=muRSc8r0mI2HHWsKDxhi7UtlXjobWnZ7QNZASx1PGOk=\" >\n</div>","metadata":{"execution":{"iopub.status.busy":"2022-11-10T21:30:52.660570Z","iopub.execute_input":"2022-11-10T21:30:52.661019Z","iopub.status.idle":"2022-11-10T21:30:53.788512Z","shell.execute_reply.started":"2022-11-10T21:30:52.660982Z","shell.execute_reply":"2022-11-10T21:30:53.787058Z"}}},{"cell_type":"markdown","source":"<h1> Key Inferences </h1>\n\n<ul>\n    <li><b>Weight</b> factor to position at finish is 0.738 (1: quite relate and far from 1: not relate)</li>\n    <li><b>Speed</b> factor to position at finish is 1.579 (1: quite relate and far from 1: not relate)</li>\n</ul>","metadata":{}},{"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nfrom tqdm import tqdm\nfrom math import pi\nimport warnings\n\nimport plotly.graph_objects as go\nfrom plotly.subplots import make_subplots\n\nwarnings.filterwarnings(\"ignore\")\npd.set_option('display.float_format', lambda x: '%.3f' % x)\n\n# Color sections\nv = \"darkviolet\"\np = \"hotpink\"\nr = \"indianred\"\ng = \"lawngreen\"\nb = \"darkturquoise\"","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-10T23:36:33.244805Z","iopub.execute_input":"2022-11-10T23:36:33.245168Z","iopub.status.idle":"2022-11-10T23:36:33.300196Z","shell.execute_reply.started":"2022-11-10T23:36:33.245094Z","shell.execute_reply":"2022-11-10T23:36:33.299290Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Reading the data\nstart_table = pd.read_csv(\n    \"../input/big-data-derby-2022/nyra_start_table.csv\",\n    header=None,\n    names=[\"track_id\", \"race_date\", \"race_number\", \"program_number\", \"weight_carried\", \"jockey\", \"odds\", \"position_at_finish\"],\n    converters={\"track_id\": str, \"race_number\": str, \"program_number\": str}\n)\nrace_table = pd.read_csv(\"../input/big-data-derby-2022/nyra_race_table.csv\", converters={\"track_id\": str, \"race_number\": str, \"post_time\": str})\ntracking_table = pd.read_csv(\"../input/big-data-derby-2022/nyra_tracking_table.csv\", converters={\"track_id\": str, \"race_number\": str, \"program_number\": str})\ncomplete = pd.read_csv(\n    \"../input/big-data-derby-2022/nyra_2019_complete.csv\",\n    header=None,\n    names=[\n        \"track_id\", \"race_date\", \"race_number\", \"program_number\", \"trakus_index\", \"latitude\", \"longitude\", \"distance_id\", \"course_type\",\n        \"track_condition\", \"run_up_distance\", \"race_type\", \"purse\", \"post_time\", \"weight_carried\", \"jockey\", \"odds\", \"position_at_finish\"\n    ],\n    converters={\"track_id\": str, \"race_number\": str, \"program_number\": str, \"post_time\": str},\n    index_col=False,\n    skipinitialspace=True\n)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-10T23:36:33.302195Z","iopub.execute_input":"2022-11-10T23:36:33.302579Z","iopub.status.idle":"2022-11-10T23:36:57.480877Z","shell.execute_reply.started":"2022-11-10T23:36:33.302545Z","shell.execute_reply":"2022-11-10T23:36:57.480176Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<div style=\"color:white;\n           display:fill;\n           border-radius:10px;\n           background-color:#227093;\n           font-size:120%;\n           padding: 3px;\n           font-family:Verdana;\n           letter-spacing:0.5px\">\n\n<h1 style=\"text-align:center;\"> Start Table </h1>\n</div>","metadata":{}},{"cell_type":"markdown","source":"<div style=\"color:white;\n           display:fill;\n           border-radius:10px;\n           background-color:#227093;\n           font-size:120%;\n           padding: 3px;\n           font-family:Verdana;\n           letter-spacing:0.5px\">\n\n<ul>\n    <li>Date range between 2019-01-01 and 2019-12-31</li>\n    <li>Max 13 races in a day</li>\n    <li>Jockey weights range between 110kg and 160kg, average is 120.45kg</li>\n    <li><b>Weight factor to position at finish is 0.738 (1: quite relate and far from 1: not relate)</b></li>\n    <li>SAR (Saratoga) has more races in a day but AQU (Aqueduct) has more races records</li>\n</ul>\n</div>","metadata":{}},{"cell_type":"code","source":"start_table","metadata":{"execution":{"iopub.status.busy":"2022-11-10T23:36:57.481967Z","iopub.execute_input":"2022-11-10T23:36:57.482430Z","iopub.status.idle":"2022-11-10T23:36:57.507293Z","shell.execute_reply.started":"2022-11-10T23:36:57.482403Z","shell.execute_reply":"2022-11-10T23:36:57.506440Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"start_table.info()","metadata":{"execution":{"iopub.status.busy":"2022-11-10T23:36:57.509581Z","iopub.execute_input":"2022-11-10T23:36:57.509889Z","iopub.status.idle":"2022-11-10T23:36:57.535572Z","shell.execute_reply.started":"2022-11-10T23:36:57.509864Z","shell.execute_reply":"2022-11-10T23:36:57.534407Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig = go.Figure(go.Bar(x=start_table.track_id.unique(), y=start_table.track_id.value_counts(), marker_color=p))\nfig.update_layout(\n    title_text=\"Race Counts By Track\",\n    title_x=0.5, title_font_family=\"Raleway\", \n    title_font_size=20, \n    title_font_color=v, \n    width=600, \n    height=400, \n    margin=dict(b=30, l=30, r=30, t=50)\n)\nfig.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-10T23:36:57.536730Z","iopub.execute_input":"2022-11-10T23:36:57.537025Z","iopub.status.idle":"2022-11-10T23:36:57.668852Z","shell.execute_reply.started":"2022-11-10T23:36:57.536994Z","shell.execute_reply":"2022-11-10T23:36:57.667929Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"start_table.program_number = start_table.program_number.str.strip()\nstart_table.race_date = pd.to_datetime(start_table.race_date, format=\"%Y-%m-%d\")\nrace_counts_bydate = start_table.groupby([\"track_id\",\"race_date\"]).count().reset_index()[[\"track_id\",\"race_date\",\"race_number\"]].rename(columns={\"race_number\":\"count\"}).sort_values(by=\"race_date\")\n\nfig = go.Figure()\nfig.add_trace(\n    go.Scatter(\n        x=race_counts_bydate.loc[race_counts_bydate[\"track_id\"] == \"AQU\",\"race_date\"], \n        y=race_counts_bydate.loc[race_counts_bydate[\"track_id\"] == \"AQU\", \"count\"], \n        marker_color=r, \n        marker_size=20, \n        marker_opacity= 0.5, \n        mode='markers', \n        name=\"AQU\")\n)\nfig.add_trace(\n    go.Scatter(\n        x=race_counts_bydate.loc[race_counts_bydate[\"track_id\"] == \"BEL\", \"race_date\"],\n        y=race_counts_bydate.loc[race_counts_bydate[\"track_id\"] == \"BEL\", \"count\"], \n        marker_color=g, \n        marker_size=20,\n        marker_opacity= 0.5,\n        mode='markers', \n        name=\"BEL\")\n)\nfig.add_trace(\n    go.Scatter(\n        x=race_counts_bydate.loc[race_counts_bydate[\"track_id\"] == \"SAR\", \"race_date\"], \n        y=race_counts_bydate.loc[race_counts_bydate[\"track_id\"] == \"SAR\", \"count\"], \n        marker_color=b, \n        marker_size=20,\n        marker_opacity= 0.5,\n        mode='markers',\n        name=\"SAR\")\n)\nfig.update_layout(\n    title_text=\"Race Counts By Date\",\n    title_x=0.5, title_font_family=\"Raleway\", \n    title_font_size=20, \n    title_font_color=v, \n    width=1000, \n    height=400, \n    margin=dict(b=30, l=30, r=30, t=50)\n)\nfig.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-10T23:36:57.670106Z","iopub.execute_input":"2022-11-10T23:36:57.670461Z","iopub.status.idle":"2022-11-10T23:36:57.745990Z","shell.execute_reply.started":"2022-11-10T23:36:57.670430Z","shell.execute_reply":"2022-11-10T23:36:57.745102Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(\"Jockey Weights Statistics\".center(70))\nprint(\"-\"*70)\nstart_table[[\"weight_carried\"]].describe().T","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-10T23:36:57.746999Z","iopub.execute_input":"2022-11-10T23:36:57.747239Z","iopub.status.idle":"2022-11-10T23:36:57.766636Z","shell.execute_reply.started":"2022-11-10T23:36:57.747217Z","shell.execute_reply":"2022-11-10T23:36:57.765730Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"*We can say that, SAR (Saratoga) has more races in a day but AQU (Aqueduct) has more races records.*","metadata":{}},{"cell_type":"code","source":"print(\"Averages of Tracks\".center(38))\nprint(\"-\"*38)\n(\n    start_table.groupby([\"track_id\", \"race_date\"])\n    .nunique()\n    .groupby(\"track_id\")\n    .mean()[[\"race_number\", \"program_number\", \"jockey\"]]\n)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-10T23:36:57.767965Z","iopub.execute_input":"2022-11-10T23:36:57.768475Z","iopub.status.idle":"2022-11-10T23:36:57.799899Z","shell.execute_reply.started":"2022-11-10T23:36:57.768442Z","shell.execute_reply":"2022-11-10T23:36:57.798925Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(\"Jockey statistics to explore their race counts & positions at finish (Top 10)\".center(33))\nprint(\"-\"*33)\n(\n    start_table.groupby([\"race_date\", \"race_number\", \"jockey\"])[\"position_at_finish\"]\n    .max()\n    .groupby(\"jockey\")\n    .agg([\"mean\", \"count\"])\n    .assign(ratio=lambda x: x[\"mean\"] / x[\"count\"])\n    .sort_values(by=\"ratio\")\n    .round(3)\n    .head(10)\n)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-10T23:36:57.801222Z","iopub.execute_input":"2022-11-10T23:36:57.801607Z","iopub.status.idle":"2022-11-10T23:36:57.833716Z","shell.execute_reply.started":"2022-11-10T23:36:57.801575Z","shell.execute_reply":"2022-11-10T23:36:57.832825Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(\"Jockey statistics to explore their race counts & odds (Top 10)\".center(35))\nprint(\"-\"*35)\n(\n    start_table.groupby([\"race_date\", \"race_number\", \"jockey\"])[\"odds\"]\n    .max()\n    .groupby(\"jockey\")\n    .agg([\"mean\", \"count\"])\n    .assign(ratio=lambda x: x[\"mean\"] / x[\"count\"])\n    .sort_values(by=\"ratio\")\n    .round(3)\n    .head(10)\n)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-10T23:36:57.837649Z","iopub.execute_input":"2022-11-10T23:36:57.838389Z","iopub.status.idle":"2022-11-10T23:36:57.861439Z","shell.execute_reply.started":"2022-11-10T23:36:57.838363Z","shell.execute_reply":"2022-11-10T23:36:57.860634Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### **Weight carried factor ratio for position at finish**\n* 1: quite releate\n* far from 1: not releate","metadata":{}},{"cell_type":"code","source":"weight_ratio = (\n    start_table\n    .groupby([\"race_date\", \"race_number\", \"jockey\"])[[\"weight_carried\", \"position_at_finish\"]]\n    .max()\n    .groupby([\"race_date\", \"race_number\"])\n    .rank(method=\"dense\")\n    .assign(weight_factor=lambda x: x[\"weight_carried\"] / x[\"position_at_finish\"])[\"weight_factor\"]\n    .mean()\n    .round(3)\n)\n\nprint(f\"Weight Factor: {weight_ratio}\")","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-10T23:36:57.863624Z","iopub.execute_input":"2022-11-10T23:36:57.863967Z","iopub.status.idle":"2022-11-10T23:36:57.886751Z","shell.execute_reply.started":"2022-11-10T23:36:57.863940Z","shell.execute_reply":"2022-11-10T23:36:57.885524Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"*So we can say that, **weight** is an important factor that could determine the winner*","metadata":{}},{"cell_type":"markdown","source":"<div style=\"color:white;\n           display:fill;\n           border-radius:10px;\n           background-color:#227093;\n           font-size:120%;\n           padding: 3px;\n           font-family:Verdana;\n           letter-spacing:0.5px\">\n\n<h1 style=\"text-align:center;\"> Race Table </h1>\n\n</div>","metadata":{}},{"cell_type":"markdown","source":"<div style=\"color:white;\n           display:fill;\n           border-radius:10px;\n           background-color:#227093;\n           font-size:120%;\n           padding: 3px;\n           font-family:Verdana;\n           letter-spacing:0.5px\">\n\n<ul>\n    <li>Distance range between 450 and 2000, average is 725</li>\n    <li>Most course type is Dirt (1351/2000)</li>\n    <li>Most track condition is Muddy (978/2000)</li>\n    <li>Purse range between 16k and 1.5m, average is 62k</li>\n</ul>\n\n</div>","metadata":{}},{"cell_type":"code","source":"race_table","metadata":{"execution":{"iopub.status.busy":"2022-11-10T23:36:57.887923Z","iopub.execute_input":"2022-11-10T23:36:57.888828Z","iopub.status.idle":"2022-11-10T23:36:57.906889Z","shell.execute_reply.started":"2022-11-10T23:36:57.888787Z","shell.execute_reply":"2022-11-10T23:36:57.905659Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_table.info()","metadata":{"execution":{"iopub.status.busy":"2022-11-10T23:36:57.908150Z","iopub.execute_input":"2022-11-10T23:36:57.908432Z","iopub.status.idle":"2022-11-10T23:36:57.923409Z","shell.execute_reply.started":"2022-11-10T23:36:57.908406Z","shell.execute_reply":"2022-11-10T23:36:57.922317Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_table.race_date = pd.to_datetime(race_table.race_date, format=\"%Y-%m-%d\")","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-10T23:36:57.926267Z","iopub.execute_input":"2022-11-10T23:36:57.926716Z","iopub.status.idle":"2022-11-10T23:36:57.934791Z","shell.execute_reply.started":"2022-11-10T23:36:57.926683Z","shell.execute_reply":"2022-11-10T23:36:57.933842Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(\"Distance Statistics\".center(68))\nprint(\"-\"*68)\nrace_table[[\"distance_id\"]].describe().T","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-10T23:36:57.935886Z","iopub.execute_input":"2022-11-10T23:36:57.936216Z","iopub.status.idle":"2022-11-10T23:36:57.957422Z","shell.execute_reply.started":"2022-11-10T23:36:57.936191Z","shell.execute_reply":"2022-11-10T23:36:57.956426Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig = go.Figure(go.Bar(x=race_table.course_type.unique(), y=race_table.course_type.value_counts(), marker_color=b))\nfig.update_layout(\n    title_text=\"Course Types\",\n    title_x=0.5, title_font_family=\"Raleway\", \n    title_font_size=20, \n    title_font_color=v,\n    width=600, \n    height=400, \n    margin=dict(b=30, l=30, r=30, t=50)\n)\nfig.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-10T23:36:57.958464Z","iopub.execute_input":"2022-11-10T23:36:57.958869Z","iopub.status.idle":"2022-11-10T23:36:57.977976Z","shell.execute_reply.started":"2022-11-10T23:36:57.958844Z","shell.execute_reply":"2022-11-10T23:36:57.977367Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig = go.Figure(go.Bar(x=race_table.track_condition.unique(), y=race_table.track_condition.value_counts(), marker_color=v))\nfig.update_layout(\n    title_text=\"Track Conditions\",\n    title_x=0.5, title_font_family=\"Raleway\", \n    title_font_size=20, \n    title_font_color=v,\n    width=600, \n    height=400, \n    margin=dict(b=30, l=30, r=30, t=50)\n)\nfig.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-10T23:36:57.979066Z","iopub.execute_input":"2022-11-10T23:36:57.979333Z","iopub.status.idle":"2022-11-10T23:36:57.997293Z","shell.execute_reply.started":"2022-11-10T23:36:57.979310Z","shell.execute_reply":"2022-11-10T23:36:57.996418Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(\"RunUp Distance Statistics\".center(64))\nprint(\"-\"*64)\nrace_table[[\"run_up_distance\"]].describe().T","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-10T23:36:57.998284Z","iopub.execute_input":"2022-11-10T23:36:57.998974Z","iopub.status.idle":"2022-11-10T23:36:58.021109Z","shell.execute_reply.started":"2022-11-10T23:36:57.998948Z","shell.execute_reply":"2022-11-10T23:36:58.020490Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig = go.Figure(go.Bar(x=race_table.race_type.unique(), y=race_table.race_type.value_counts(), marker_color=g))\nfig.update_layout(\n    title_text=\"Race Types\",\n    title_x=0.5, title_font_family=\"Raleway\", \n    title_font_size=20, \n    title_font_color=v,\n    width=600, \n    height=400, \n    margin=dict(b=30, l=30, r=30, t=50)\n)\nfig.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-10T23:36:58.021986Z","iopub.execute_input":"2022-11-10T23:36:58.022793Z","iopub.status.idle":"2022-11-10T23:36:58.041562Z","shell.execute_reply.started":"2022-11-10T23:36:58.022765Z","shell.execute_reply":"2022-11-10T23:36:58.040399Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(\"Purse Statistics\".center(80))\nprint(\"-\"*80)\nrace_table[[\"purse\"]].describe().T","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-10T23:36:58.042872Z","iopub.execute_input":"2022-11-10T23:36:58.043363Z","iopub.status.idle":"2022-11-10T23:36:58.059846Z","shell.execute_reply.started":"2022-11-10T23:36:58.043336Z","shell.execute_reply":"2022-11-10T23:36:58.058750Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Purse Distributions","metadata":{}},{"cell_type":"code","source":"def purse_compare_by(col):\n    return (\n        race_table\n        .groupby(col)[[\"purse\"]]\n        .mean()\n        .sort_values(by=\"purse\", ascending=False)\n    ).T","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-10T23:36:58.061083Z","iopub.execute_input":"2022-11-10T23:36:58.061696Z","iopub.status.idle":"2022-11-10T23:36:58.066785Z","shell.execute_reply.started":"2022-11-10T23:36:58.061668Z","shell.execute_reply":"2022-11-10T23:36:58.065447Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(\"Distance & Purse\".center(80))\nprint(\"-\"*80)\npurse_compare_by(\"distance_id\")","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-10T23:36:58.068250Z","iopub.execute_input":"2022-11-10T23:36:58.068585Z","iopub.status.idle":"2022-11-10T23:36:58.090685Z","shell.execute_reply.started":"2022-11-10T23:36:58.068552Z","shell.execute_reply":"2022-11-10T23:36:58.089846Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(\"Course Type & Purse\".center(54))\nprint(\"-\"*54)\npurse_compare_by(\"course_type\")","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-10T23:36:58.091707Z","iopub.execute_input":"2022-11-10T23:36:58.092539Z","iopub.status.idle":"2022-11-10T23:36:58.106147Z","shell.execute_reply.started":"2022-11-10T23:36:58.092506Z","shell.execute_reply":"2022-11-10T23:36:58.105464Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(\"Track Condition & Purse\".center(80))\nprint(\"-\"*80)\npurse_compare_by(\"track_condition\")","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-10T23:36:58.107569Z","iopub.execute_input":"2022-11-10T23:36:58.107924Z","iopub.status.idle":"2022-11-10T23:36:58.123444Z","shell.execute_reply.started":"2022-11-10T23:36:58.107891Z","shell.execute_reply":"2022-11-10T23:36:58.122277Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(\"Race Type & Purse\".center(80))\nprint(\"-\"*80)\npurse_compare_by(\"race_type\")","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-10T23:36:58.125297Z","iopub.execute_input":"2022-11-10T23:36:58.126555Z","iopub.status.idle":"2022-11-10T23:36:58.143171Z","shell.execute_reply.started":"2022-11-10T23:36:58.126523Z","shell.execute_reply":"2022-11-10T23:36:58.141986Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(\"Track Condition - Race Type & Purse\".center(80))\nprint(\"-\"*80)\npurse_compare_by([\"track_condition\", \"race_type\"])","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-10T23:36:58.144637Z","iopub.execute_input":"2022-11-10T23:36:58.144906Z","iopub.status.idle":"2022-11-10T23:36:58.171583Z","shell.execute_reply.started":"2022-11-10T23:36:58.144884Z","shell.execute_reply":"2022-11-10T23:36:58.170428Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"* *Track condition & race type are correlated with purse*\n* *Most correlated column is race type & STK (Stakes) has the most purse*","metadata":{}},{"cell_type":"markdown","source":"<div style=\"color:white;\n           display:fill;\n           border-radius:10px;\n           background-color:#227093;\n           font-size:120%;\n           padding: 3px;\n           font-family:Verdana;\n           letter-spacing:0.5px\">\n\n<h1 style=\"text-align:center;\"> Tracking Table </h1>\n\n</div>","metadata":{}},{"cell_type":"code","source":"tracking_table","metadata":{"execution":{"iopub.status.busy":"2022-11-10T23:36:58.172679Z","iopub.execute_input":"2022-11-10T23:36:58.172929Z","iopub.status.idle":"2022-11-10T23:36:58.188382Z","shell.execute_reply.started":"2022-11-10T23:36:58.172907Z","shell.execute_reply":"2022-11-10T23:36:58.187359Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tracking_table.info()","metadata":{"execution":{"iopub.status.busy":"2022-11-10T23:36:58.194648Z","iopub.execute_input":"2022-11-10T23:36:58.195439Z","iopub.status.idle":"2022-11-10T23:36:58.206109Z","shell.execute_reply.started":"2022-11-10T23:36:58.195399Z","shell.execute_reply":"2022-11-10T23:36:58.204865Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tracking_table.race_date = pd.to_datetime(tracking_table.race_date, format=\"%Y-%m-%d\")\ntracking_table.program_number = tracking_table.program_number.str.strip()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-10T23:36:58.207806Z","iopub.execute_input":"2022-11-10T23:36:58.208218Z","iopub.status.idle":"2022-11-10T23:37:00.545206Z","shell.execute_reply.started":"2022-11-10T23:36:58.208185Z","shell.execute_reply":"2022-11-10T23:37:00.544078Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sample_horse_aqu = tracking_table.query(\"track_id=='AQU'&race_date=='2019-01-01'&race_number=='2'&program_number=='2'\")\nsample_horse_bel = tracking_table.query(\"track_id=='BEL'&race_date=='2019-04-26'&race_number=='2'&program_number=='2'\")\nsample_horse_sar = tracking_table.query(\"track_id=='SAR'&race_date=='2019-07-11'&race_number=='2'&program_number=='2'\")\n\nfig_aqu = make_subplots(rows=1, cols=3, subplot_titles=(\"Sample Horse Tracking at AQU\", \"Sample Horse Tracking at BEL\", \"Sample Horse Tracking at SAR\"))\nfig_aqu.add_trace(go.Scatter(x=sample_horse_aqu[\"latitude\"], y=sample_horse_aqu[\"longitude\"], name=\"AQU\"), row=1, col=1)\nfig_aqu.add_trace(go.Scatter(x=sample_horse_bel[\"latitude\"], y=sample_horse_bel[\"longitude\"], name=\"BEL\"), row=1, col=2)\nfig_aqu.add_trace(go.Scatter(x=sample_horse_sar[\"latitude\"], y=sample_horse_sar[\"longitude\"], name=\"SAR\"), row=1, col=3)\nfig_aqu.show()\n\ndel sample_horse_aqu\ndel sample_horse_bel\ndel sample_horse_sar\n\ndel start_table\ndel race_table\ndel tracking_table","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-10T23:37:00.547195Z","iopub.execute_input":"2022-11-10T23:37:00.547643Z","iopub.status.idle":"2022-11-10T23:37:01.746402Z","shell.execute_reply.started":"2022-11-10T23:37:00.547602Z","shell.execute_reply":"2022-11-10T23:37:01.745435Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<div style=\"color:white;\n           display:fill;\n           border-radius:10px;\n           background-color:#227093;\n           font-size:120%;\n           padding: 3px;\n           font-family:Verdana;\n           letter-spacing:0.5px\">\n\n<h1 style=\"text-align:center;\"> Complete </h1>\n\n</div>","metadata":{}},{"cell_type":"code","source":"complete","metadata":{"execution":{"iopub.status.busy":"2022-11-10T23:37:01.747983Z","iopub.execute_input":"2022-11-10T23:37:01.748327Z","iopub.status.idle":"2022-11-10T23:37:01.774418Z","shell.execute_reply.started":"2022-11-10T23:37:01.748295Z","shell.execute_reply":"2022-11-10T23:37:01.773521Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"complete.info()","metadata":{"execution":{"iopub.status.busy":"2022-11-10T23:37:01.775683Z","iopub.execute_input":"2022-11-10T23:37:01.775984Z","iopub.status.idle":"2022-11-10T23:37:01.788463Z","shell.execute_reply.started":"2022-11-10T23:37:01.775958Z","shell.execute_reply":"2022-11-10T23:37:01.787199Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"complete.program_number = complete.program_number.str.strip()\ncomplete = complete.sort_values(by=[\"track_id\", \"race_date\", \"race_number\", \"program_number\", \"trakus_index\"]).reset_index().drop(\"index\", axis=1)\nshifted = complete.shift(1)[[\"latitude\", \"longitude\"]].rename(columns={\"latitude\":\"latitude_shift\", \"longitude\":\"longitude_shift\"})\nconcated = pd.concat([complete, shifted], axis=1)\n\ndel complete\ndel shifted","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-10T23:37:01.789911Z","iopub.execute_input":"2022-11-10T23:37:01.790735Z","iopub.status.idle":"2022-11-10T23:37:10.125399Z","shell.execute_reply.started":"2022-11-10T23:37:01.790704Z","shell.execute_reply":"2022-11-10T23:37:10.124530Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"concated.loc[0, \"latitude_shift\"] = concated.loc[0, \"latitude\"]\nconcated.loc[0, \"longitude_shift\"] = concated.loc[0, \"longitude\"]\n\nconcated[\"latitude\"] = concated[\"latitude\"].apply(lambda x: (x * pi) / 180)\nconcated[\"longitude\"] = concated[\"longitude\"].apply(lambda x: (x * pi) / 180)\nconcated[\"latitude_shift\"] = concated[\"latitude_shift\"].apply(lambda x: (x * pi) / 180)\nconcated[\"longitude_shift\"] = concated[\"longitude_shift\"].apply(lambda x: (x * pi) / 180)\n\nconcated[\"latitude_diff\"] = concated[\"latitude\"] - concated[\"latitude_shift\"]\nconcated[\"longitude_diff\"] = concated[\"longitude\"] - concated[\"longitude_shift\"]\n\nconcated = concated.assign(a = lambda x: np.sin(x[\"latitude_diff\"]/2)**2 + np.cos(x[\"latitude_shift\"]) * np.cos(x[\"latitude\"]) * np.sin(x[\"longitude_diff\"]/2)**2)\nconcated = concated.assign(distance = lambda x: (2 * np.arctan2(np.sqrt(x[\"a\"]), np.sqrt(1 - x[\"a\"]))))\nconcated[\"distance\"] = concated[\"distance\"] * 6373 * 1000\nconcated.loc[concated[\"trakus_index\"] == 1, \"distance\"] = 0\nconcated[\"speed\"] = concated[\"distance\"] / 0.25  # m/s\n\nconcated.drop([\"latitude\", \"longitude\", \"latitude_shift\", \"longitude_shift\", \"latitude_diff\", \"longitude_diff\", \"a\"], axis=1, inplace=True)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-10T23:37:10.127425Z","iopub.execute_input":"2022-11-10T23:37:10.128047Z","iopub.status.idle":"2022-11-10T23:37:19.889914Z","shell.execute_reply.started":"2022-11-10T23:37:10.128015Z","shell.execute_reply":"2022-11-10T23:37:19.888899Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def get_speed_mean_df(second: int, is_first: bool = True, drop_group_cols: bool = True):\n    df = (\n        concated\n        .groupby([\"track_id\", \"race_date\", \"race_number\", \"program_number\"])[\"speed\"]\n        .apply(lambda x: x.iloc[:second*4].mean())\n        .reset_index()\n        .rename(columns={\"speed\": f\"{('first' if is_first else 'last')}{second}s_speed_mean\"})\n    )\n    if drop_group_cols:\n        df.drop([\"track_id\", \"race_date\", \"race_number\", \"program_number\"], axis=1, inplace=True)\n\n    return df","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-10T23:37:19.891031Z","iopub.execute_input":"2022-11-10T23:37:19.891315Z","iopub.status.idle":"2022-11-10T23:37:19.898298Z","shell.execute_reply.started":"2022-11-10T23:37:19.891281Z","shell.execute_reply":"2022-11-10T23:37:19.896931Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"seconds = [3, 5, 10, 15]\n\nspeed_means = []\nspeed_means.append(get_speed_mean_df(second=1, drop_group_cols=False).drop(\"first1s_speed_mean\", axis=1))  # group cols\n\nfor is_first in [True, False]:\n    for second in seconds:\n        speed_means.append(get_speed_mean_df(second=second, is_first=is_first, drop_group_cols=True))\n\nspeed_df = pd.concat(speed_means, axis=1)\nconcated = concated.merge(speed_df, on=[\"track_id\", \"race_date\", \"race_number\", \"program_number\"], how=\"inner\")\nconcated","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-10T23:37:19.899679Z","iopub.execute_input":"2022-11-10T23:37:19.899980Z","iopub.status.idle":"2022-11-10T23:38:05.252684Z","shell.execute_reply.started":"2022-11-10T23:37:19.899952Z","shell.execute_reply":"2022-11-10T23:38:05.251837Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def get_speed_factor(is_first: bool, second: int):\n    return (\n        concated\n        .groupby([\"track_id\", \"race_date\", \"race_number\", \"program_number\"])[[f\"{('first' if is_first else 'last')}{second}s_speed_mean\", \"position_at_finish\"]]\n        .max()\n        .groupby([\"track_id\", \"race_date\", \"race_number\"])\n        .rank(method=\"dense\", ascending=False)\n        .assign(speed_factor=lambda x: x[f\"{('first' if is_first else 'last')}{second}s_speed_mean\"] / x[\"position_at_finish\"])[\"speed_factor\"]\n        .mean()\n    )","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-10T23:38:05.253940Z","iopub.execute_input":"2022-11-10T23:38:05.254283Z","iopub.status.idle":"2022-11-10T23:38:05.260815Z","shell.execute_reply.started":"2022-11-10T23:38:05.254251Z","shell.execute_reply":"2022-11-10T23:38:05.259743Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Speed means factor ratio for position at finish\n- 1: quite releate\n- far from 1: not releate","metadata":{}},{"cell_type":"code","source":"for is_first in [True, False]:\n    for second in seconds:\n        factor = get_speed_factor(is_first=is_first, second=second)\n        print(f\"{('first' if is_first else 'last')} {second}s speed mean factor: {factor}\")","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-11-10T23:38:05.262560Z","iopub.execute_input":"2022-11-10T23:38:05.262891Z","iopub.status.idle":"2022-11-10T23:38:21.156557Z","shell.execute_reply.started":"2022-11-10T23:38:05.262859Z","shell.execute_reply":"2022-11-10T23:38:21.155491Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"***So we can say that, speed means at first or last seconds are important for position at finish***","metadata":{}},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}