{"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":"Below, I wrote a code which calculates the distances taken by each horse between two consecutive trakus logs. If you need an EDA on this competition's data, you may take a look at [my previously published notebook](https://www.kaggle.com/code/ozoozo/big-data-derby-2022-starter-eda).\n\nTo calculate the distances between two coordinate points, we will use [mpu](https://github.com/MartinThoma/mpu) utility functions.","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":{"execution":{"iopub.status.busy":"2022-09-20T09:51:11.78103Z","iopub.execute_input":"2022-09-20T09:51:11.781963Z","iopub.status.idle":"2022-09-20T09:51:11.792492Z","shell.execute_reply.started":"2022-09-20T09:51:11.781878Z","shell.execute_reply":"2022-09-20T09:51:11.791476Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"!pip install mpu\nimport mpu","metadata":{"execution":{"iopub.status.busy":"2022-09-20T09:51:11.79399Z","iopub.execute_input":"2022-09-20T09:51:11.794944Z","iopub.status.idle":"2022-09-20T09:51:24.122803Z","shell.execute_reply.started":"2022-09-20T09:51:11.794907Z","shell.execute_reply":"2022-09-20T09:51:24.121686Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"
nyra_2019_complete.csv file is the file that contains the whole data. Let's read it and make some arrangements in order to decrease memory usage and make some data replacements to remove some sources for future errors:
","metadata":{}},{"cell_type":"code","source":"df_complete = pd.read_csv(\"/kaggle/input/big-data-derby-2022/nyra_2019_complete.csv\")\ndf_complete.columns = [\"track_id\",\"race_date\",\"race_number\",\"program_number\",\"trakus_index\",\"latitude\",\"longitude\",\"distance_id\",\"course_type\",\"track_condition\",\"run_up_distance\",\"race_type\",\"purse\",\"post_time\",\"weight_carried\",\"jockey\",\"odds\",\"position\"]\ndf_complete = df_complete.sort_values(by=[\"track_id\",\"race_date\",\"race_number\",\"program_number\",\"trakus_index\"]).reset_index(drop=True)\n\n\n\ndf_complete[\"race_number\"] = df_complete[\"race_number\"].astype(\"int8\")\ndf_complete[\"trakus_index\"] = df_complete[\"trakus_index\"].astype(\"int16\")\ndf_complete[\"distance_id\"] = df_complete[\"distance_id\"].astype(\"int16\")\ndf_complete[\"run_up_distance\"] = df_complete[\"run_up_distance\"].astype(\"int16\")\ndf_complete[\"weight_carried\"] = df_complete[\"weight_carried\"].astype(\"int16\")\ndf_complete[\"position\"] = df_complete[\"position\"].astype(\"int8\")\ndf_complete[\"odds\"] = df_complete[\"odds\"].astype(\"int16\")\ndf_complete[\"post_time\"] = df_complete[\"post_time\"].astype(\"int16\")\n\n\ndf_complete[\"program_number\"] = df_complete[\"program_number\"].astype(\"str\").str.replace(\"1A\",\"11\")\ndf_complete[\"program_number\"] = df_complete[\"program_number\"].astype(\"str\").str.replace(\"3X\",\"30\")\ndf_complete[\"program_number\"] = df_complete[\"program_number\"].astype(\"str\").str.replace(\"1X\",\"10\")\ndf_complete[\"program_number\"] = df_complete[\"program_number\"].astype(\"str\").str.replace(\"2B\",\"22\")\n\n\ndf_complete[\"program_number\"] = df_complete[\"program_number\"].astype(\"int8\")\n\n\n\ndf_complete = df_complete.set_index([\"track_id\",\"race_date\",\"race_number\",\"program_number\"])\n\ndf_complete","metadata":{"execution":{"iopub.status.busy":"2022-09-20T09:51:24.124472Z","iopub.execute_input":"2022-09-20T09:51:24.125364Z","iopub.status.idle":"2022-09-20T09:52:07.710044Z","shell.execute_reply.started":"2022-09-20T09:51:24.125326Z","shell.execute_reply":"2022-09-20T09:52:07.708868Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"
Let's calculate the distances:
","metadata":{}},{"cell_type":"code","source":"df = pd.DataFrame(data=df_complete[[\"trakus_index\",\"latitude\",\"longitude\"]].reset_index(drop=True))\ndf[\"prev_trakus_index\"] = 0\ndf[\"prev_latitude\"] = 0\ndf[\"prev_longitude\"] = 0\ndf.loc[1:len(df)-1,\"prev_trakus_index\"] = df.loc[0:len(df)-2,\"trakus_index\"].values\ndf.loc[1:len(df)-1,\"prev_latitude\"] = df.loc[0:len(df)-2,\"latitude\"].values\ndf.loc[1:len(df)-1,\"prev_longitude\"] = df.loc[0:len(df)-2,\"longitude\"].values\n\ndf[\"is_intial_point\"] = df[\"trakus_index\"] - df[\"prev_trakus_index\"]\ndf.loc[df[\"is_intial_point\"]>0,\"is_intial_point\"] = 0\ndf.loc[df[\"is_intial_point\"]<0,\"is_intial_point\"] = 1\n\n\ndef calc_distance(lat1, lon1, lat2, lon2):\n return mpu.haversine_distance((lat1,lon1),(lat2,lon2))\n\ndef map_distance(df):\n return pd.Series(\n map(calc_distance,df['latitude'],df['longitude'],df['prev_latitude'],df['prev_longitude'])\n )\n\ndf['trakus_distance'] = map_distance(df).values\n\ndf.loc[df[\"is_intial_point\"]==1,'trakus_distance'] = 0\ndf.loc[df[\"prev_latitude\"]==0,'trakus_distance'] = 0\n\n\n\n\ndf_complete[\"trakus_distance\"] = df[\"trakus_distance\"].values\n\n\ndf_complete","metadata":{"execution":{"iopub.status.busy":"2022-09-20T09:52:07.712535Z","iopub.execute_input":"2022-09-20T09:52:07.712906Z","iopub.status.idle":"2022-09-20T09:52:26.450051Z","shell.execute_reply.started":"2022-09-20T09:52:07.712871Z","shell.execute_reply":"2022-09-20T09:52:26.448854Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"
Please don't forget that we changed the datatype of program_number and changed the following program_numbers: \n
df_complete[\"program_number\"] = df_complete[\"program_number\"].astype(\"str\").str.replace(\"1A\",\"11\")
\n
df_complete[\"program_number\"] = df_complete[\"program_number\"].astype(\"str\").str.replace(\"3X\",\"30\")
\n
df_complete[\"program_number\"] = df_complete[\"program_number\"].astype(\"str\").str.replace(\"1X\",\"10\")
\n
df_complete[\"program_number\"] = df_complete[\"program_number\"].astype(\"str\").str.replace(\"2B\",\"22\")
\n \n \n
If you want to use the original program_numbers, then please put back the original names after you run this code.\n","metadata":{}}]}