{"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":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport statistics\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-10-09T14:28:00.103094Z","iopub.execute_input":"2022-10-09T14:28:00.103579Z","iopub.status.idle":"2022-10-09T14:28:00.692089Z","shell.execute_reply.started":"2022-10-09T14:28:00.103483Z","shell.execute_reply":"2022-10-09T14:28:00.690860Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"the goal of this stage is to\napply EDA to race table with the following questions\n\n\n[a) how many races we have in total in 2019?](#a)\n\n[b) Numerical Variables: what is the minimum/maximum/average/median (distance ,run_up_distance, and purse) of a race ?](#b)\n\n[c) Categorical Variables: how many races per (course_type, track_condition, and race_type)](#c)\n\n[d) Date Variable how many races played per week / month in 2019 ?](#d)","metadata":{}},{"cell_type":"code","source":"race=pd.read_csv(\"/kaggle/input/big-data-derby-2022/nyra_race_table.csv\")\nrace.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:00.694937Z","iopub.execute_input":"2022-10-09T14:28:00.695437Z","iopub.status.idle":"2022-10-09T14:28:00.722424Z","shell.execute_reply.started":"2022-10-09T14:28:00.695387Z","shell.execute_reply":"2022-10-09T14:28:00.721233Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"start=pd.read_csv(\"/kaggle/input/big-data-derby-2022/nyra_start_table.csv\",header=None)\nstart.columns=['track_id','race_date','race_number','program_number','weight_carried','jockey','odds','position_at_finish']\nstart.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:00.724322Z","iopub.execute_input":"2022-10-09T14:28:00.725000Z","iopub.status.idle":"2022-10-09T14:28:00.760050Z","shell.execute_reply.started":"2022-10-09T14:28:00.724964Z","shell.execute_reply":"2022-10-09T14:28:00.758748Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tracking=pd.read_csv(\"/kaggle/input/big-data-derby-2022/nyra_tracking_table.csv\")\ntracking.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:00.854490Z","iopub.execute_input":"2022-10-09T14:28:00.854944Z","iopub.status.idle":"2022-10-09T14:28:05.407439Z","shell.execute_reply.started":"2022-10-09T14:28:00.854906Z","shell.execute_reply":"2022-10-09T14:28:05.406092Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"complete=tracking.merge(start,on=['track_id','race_date','race_number','program_number'])\ncomplete.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:05.409488Z","iopub.execute_input":"2022-10-09T14:28:05.409877Z","iopub.status.idle":"2022-10-09T14:28:08.196811Z","shell.execute_reply.started":"2022-10-09T14:28:05.409840Z","shell.execute_reply":"2022-10-09T14:28:08.195692Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"complete=complete.merge(race,on=['track_id','race_date','race_number'])\ncomplete.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:08.198005Z","iopub.execute_input":"2022-10-09T14:28:08.198326Z","iopub.status.idle":"2022-10-09T14:28:11.373419Z","shell.execute_reply.started":"2022-10-09T14:28:08.198299Z","shell.execute_reply":"2022-10-09T14:28:11.372156Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"lets have a look on the race table","metadata":{}},{"cell_type":"code","source":"race.shape","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:11.375878Z","iopub.execute_input":"2022-10-09T14:28:11.376253Z","iopub.status.idle":"2022-10-09T14:28:11.385193Z","shell.execute_reply.started":"2022-10-09T14:28:11.376222Z","shell.execute_reply":"2022-10-09T14:28:11.383825Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race.info()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:11.386686Z","iopub.execute_input":"2022-10-09T14:28:11.387034Z","iopub.status.idle":"2022-10-09T14:28:11.405429Z","shell.execute_reply.started":"2022-10-09T14:28:11.387002Z","shell.execute_reply":"2022-10-09T14:28:11.403820Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<a id='a'></a>\na) how many races we have in total in 2019?\nwe need to fix the data type in the race_date column, then make sure that all the races occurred in 2019. ","metadata":{}},{"cell_type":"code","source":"race.race_date = pd.to_datetime(race.race_date) ","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:11.408591Z","iopub.execute_input":"2022-10-09T14:28:11.409084Z","iopub.status.idle":"2022-10-09T14:28:11.418955Z","shell.execute_reply.started":"2022-10-09T14:28:11.409030Z","shell.execute_reply":"2022-10-09T14:28:11.417961Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race.info()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:11.420610Z","iopub.execute_input":"2022-10-09T14:28:11.421102Z","iopub.status.idle":"2022-10-09T14:28:11.440139Z","shell.execute_reply.started":"2022-10-09T14:28:11.421056Z","shell.execute_reply":"2022-10-09T14:28:11.438951Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race.race_date.min(),race.race_date.max()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:11.441213Z","iopub.execute_input":"2022-10-09T14:28:11.441587Z","iopub.status.idle":"2022-10-09T14:28:11.455163Z","shell.execute_reply.started":"2022-10-09T14:28:11.441543Z","shell.execute_reply":"2022-10-09T14:28:11.454046Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race.shape[0]","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:11.456776Z","iopub.execute_input":"2022-10-09T14:28:11.457745Z","iopub.status.idle":"2022-10-09T14:28:11.467790Z","shell.execute_reply.started":"2022-10-09T14:28:11.457710Z","shell.execute_reply":"2022-10-09T14:28:11.466668Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<a id='b'></a>\nwhat is the minimum/maximum/average/median (distance ,run_up_distance, and purse) of a race ?","metadata":{}},{"cell_type":"code","source":"race.describe()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:11.472341Z","iopub.execute_input":"2022-10-09T14:28:11.473175Z","iopub.status.idle":"2022-10-09T14:28:11.508079Z","shell.execute_reply.started":"2022-10-09T14:28:11.473133Z","shell.execute_reply":"2022-10-09T14:28:11.506608Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<a id='c'></a>\nhow many races per (track_id, course_type, track_condition, and race_type)","metadata":{}},{"cell_type":"code","source":"race.track_id.value_counts()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:11.509837Z","iopub.execute_input":"2022-10-09T14:28:11.510920Z","iopub.status.idle":"2022-10-09T14:28:11.521131Z","shell.execute_reply.started":"2022-10-09T14:28:11.510877Z","shell.execute_reply":"2022-10-09T14:28:11.520032Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig,ax=plt.subplots()\nrace.track_id.value_counts().plot(kind='bar');\nax.set_xticklabels(['AQU -Aqueduct', 'BEL - Belmont', 'SAR - Saratoga'])\nax.set_xlabel(\"Track Id\")\nax.set_title(\"How Many Races Per Track Id\")\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:11.522856Z","iopub.execute_input":"2022-10-09T14:28:11.523901Z","iopub.status.idle":"2022-10-09T14:28:11.752887Z","shell.execute_reply.started":"2022-10-09T14:28:11.523860Z","shell.execute_reply":"2022-10-09T14:28:11.751696Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race.course_type.value_counts()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:11.754347Z","iopub.execute_input":"2022-10-09T14:28:11.754732Z","iopub.status.idle":"2022-10-09T14:28:11.765060Z","shell.execute_reply.started":"2022-10-09T14:28:11.754697Z","shell.execute_reply":"2022-10-09T14:28:11.763809Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig,ax=plt.subplots()\nrace.course_type.value_counts().plot(kind='bar');\nax.set_xticklabels(['D - Dirt','T - turf','I - Inner turf','O - Outer turf','M - Hurdle'])\nax.set_xlabel(\"Course Type\")\nax.set_title(\"How Many Races Per Course Type\")\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:11.766377Z","iopub.execute_input":"2022-10-09T14:28:11.767241Z","iopub.status.idle":"2022-10-09T14:28:11.980917Z","shell.execute_reply.started":"2022-10-09T14:28:11.767204Z","shell.execute_reply":"2022-10-09T14:28:11.979699Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race.track_condition.value_counts()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:11.982689Z","iopub.execute_input":"2022-10-09T14:28:11.983172Z","iopub.status.idle":"2022-10-09T14:28:11.993557Z","shell.execute_reply.started":"2022-10-09T14:28:11.983119Z","shell.execute_reply":"2022-10-09T14:28:11.992218Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots()\nrace.track_condition.value_counts().plot(kind='bar')\nTrackCondition=['FT - Fast','FM - Firm','GD - Good','SY - Sloppy','MY - Muddy','YL - Yielding','SF - Soft']\nax.set_xticklabels(TrackCondition,rotation=90)\nax.set_xlabel(\"Track Condition\")\nax.set_title(\"How Many Races Per Track Condition\")\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:11.994981Z","iopub.execute_input":"2022-10-09T14:28:11.995320Z","iopub.status.idle":"2022-10-09T14:28:12.149491Z","shell.execute_reply.started":"2022-10-09T14:28:11.995290Z","shell.execute_reply":"2022-10-09T14:28:12.148636Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race.race_type.value_counts()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:12.151612Z","iopub.execute_input":"2022-10-09T14:28:12.152796Z","iopub.status.idle":"2022-10-09T14:28:12.161569Z","shell.execute_reply.started":"2022-10-09T14:28:12.152727Z","shell.execute_reply":"2022-10-09T14:28:12.160695Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"TrackCondition=['CLM - Claiming','MSW - Maiden Special Weight','MCL - Maiden Claiming','STK - Stakes',\n                'AOC - Allowance Optional Claimer','ALW - Allowance','STR - Starter Allowance','SOC - Starter Optional Claimer',\n               'SST - Starter Stakes','WCL - Waiver Claiming','SHP - Starter Handicap','WMC - Waiver Maiden Claiming']\nfig, ax = plt.subplots()\nrace.race_type.value_counts().plot(kind='bar')\nax.set_xticklabels(TrackCondition,rotation=90)\nax.set_xlabel(\"Race Type\")\nax.set_title(\"How Many Races Per Race Type\")\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:12.163132Z","iopub.execute_input":"2022-10-09T14:28:12.163753Z","iopub.status.idle":"2022-10-09T14:28:12.427224Z","shell.execute_reply.started":"2022-10-09T14:28:12.163717Z","shell.execute_reply":"2022-10-09T14:28:12.426248Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<a id='d'></a>\nhow many races played per week / month in 2019 ?","metadata":{}},{"cell_type":"code","source":"#races per month\nrace.race_date.dt.strftime(\"%m\").value_counts().sort_index()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:12.428561Z","iopub.execute_input":"2022-10-09T14:28:12.429129Z","iopub.status.idle":"2022-10-09T14:28:12.450871Z","shell.execute_reply.started":"2022-10-09T14:28:12.429095Z","shell.execute_reply":"2022-10-09T14:28:12.449586Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots()\nMnth=['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec']\nrace.race_date.dt.strftime(\"%m\").value_counts().sort_index().plot(kind='bar')\nax.set_xlabel(\"Month\")\nax.set_xticklabels(Mnth,rotation=90)\nax.set_title(\"How Many Races Per Month\")\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:12.452368Z","iopub.execute_input":"2022-10-09T14:28:12.452775Z","iopub.status.idle":"2022-10-09T14:28:12.707725Z","shell.execute_reply.started":"2022-10-09T14:28:12.452741Z","shell.execute_reply":"2022-10-09T14:28:12.706413Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#races per week\nrace.race_date.dt.strftime(\"%U\").value_counts().sort_index()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:12.709177Z","iopub.execute_input":"2022-10-09T14:28:12.709563Z","iopub.status.idle":"2022-10-09T14:28:12.734483Z","shell.execute_reply.started":"2022-10-09T14:28:12.709527Z","shell.execute_reply":"2022-10-09T14:28:12.733213Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots()\nrace.race_date.dt.strftime(\"%U\").value_counts().sort_index().plot(kind='bar',figsize=(15,5))\nax.set_xlabel(\"Week No.\")\nax.set_title(\"How Many Races Per Week\")\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:12.736046Z","iopub.execute_input":"2022-10-09T14:28:12.736464Z","iopub.status.idle":"2022-10-09T14:28:13.426460Z","shell.execute_reply.started":"2022-10-09T14:28:12.736417Z","shell.execute_reply":"2022-10-09T14:28:13.425066Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.catplot(x='distance_id',data=race,kind='count',col='track_id');","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:13.427963Z","iopub.execute_input":"2022-10-09T14:28:13.428350Z","iopub.status.idle":"2022-10-09T14:28:14.391422Z","shell.execute_reply.started":"2022-10-09T14:28:13.428313Z","shell.execute_reply":"2022-10-09T14:28:14.389893Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.catplot(x='course_type',data=race,kind='count',col='track_id');","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:14.393005Z","iopub.execute_input":"2022-10-09T14:28:14.393487Z","iopub.status.idle":"2022-10-09T14:28:15.013933Z","shell.execute_reply.started":"2022-10-09T14:28:14.393450Z","shell.execute_reply":"2022-10-09T14:28:15.012681Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.catplot(x='race_type',data=race,kind='count',col='track_id');","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:15.015565Z","iopub.execute_input":"2022-10-09T14:28:15.016058Z","iopub.status.idle":"2022-10-09T14:28:15.947015Z","shell.execute_reply.started":"2022-10-09T14:28:15.016022Z","shell.execute_reply":"2022-10-09T14:28:15.945712Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"lets have a look on the start race","metadata":{}},{"cell_type":"code","source":"start.shape","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:15.948828Z","iopub.execute_input":"2022-10-09T14:28:15.949205Z","iopub.status.idle":"2022-10-09T14:28:15.955294Z","shell.execute_reply.started":"2022-10-09T14:28:15.949172Z","shell.execute_reply":"2022-10-09T14:28:15.954318Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"start.info()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:15.956472Z","iopub.execute_input":"2022-10-09T14:28:15.956870Z","iopub.status.idle":"2022-10-09T14:28:15.979814Z","shell.execute_reply.started":"2022-10-09T14:28:15.956833Z","shell.execute_reply":"2022-10-09T14:28:15.978114Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"start.race_date=pd.to_datetime(start.race_date)","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:15.987154Z","iopub.execute_input":"2022-10-09T14:28:15.987551Z","iopub.status.idle":"2022-10-09T14:28:15.999577Z","shell.execute_reply.started":"2022-10-09T14:28:15.987518Z","shell.execute_reply":"2022-10-09T14:28:15.998146Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"start.shape","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:16.001354Z","iopub.execute_input":"2022-10-09T14:28:16.002111Z","iopub.status.idle":"2022-10-09T14:28:16.012318Z","shell.execute_reply.started":"2022-10-09T14:28:16.002060Z","shell.execute_reply":"2022-10-09T14:28:16.010904Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"start.info()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:16.013729Z","iopub.execute_input":"2022-10-09T14:28:16.014108Z","iopub.status.idle":"2022-10-09T14:28:16.034860Z","shell.execute_reply.started":"2022-10-09T14:28:16.014077Z","shell.execute_reply":"2022-10-09T14:28:16.033392Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#No of horses\nlen(start.program_number.unique())","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:16.036263Z","iopub.execute_input":"2022-10-09T14:28:16.036626Z","iopub.status.idle":"2022-10-09T14:28:16.045594Z","shell.execute_reply.started":"2022-10-09T14:28:16.036595Z","shell.execute_reply":"2022-10-09T14:28:16.044163Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#No of races each horse enters\nstart.program_number.value_counts()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:16.047571Z","iopub.execute_input":"2022-10-09T14:28:16.048448Z","iopub.status.idle":"2022-10-09T14:28:16.061907Z","shell.execute_reply.started":"2022-10-09T14:28:16.048400Z","shell.execute_reply":"2022-10-09T14:28:16.060491Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots()\nstart.program_number.value_counts().plot(kind='bar',figsize=(15,5))\nax.set_title(\"number of races each horse played\");","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:16.063747Z","iopub.execute_input":"2022-10-09T14:28:16.064216Z","iopub.status.idle":"2022-10-09T14:28:16.449106Z","shell.execute_reply.started":"2022-10-09T14:28:16.064105Z","shell.execute_reply":"2022-10-09T14:28:16.447906Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"start.groupby('program_number')[['weight_carried']].describe()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:16.454305Z","iopub.execute_input":"2022-10-09T14:28:16.454827Z","iopub.status.idle":"2022-10-09T14:28:16.582515Z","shell.execute_reply.started":"2022-10-09T14:28:16.454778Z","shell.execute_reply":"2022-10-09T14:28:16.581237Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"start.groupby('program_number')[['odds']].describe()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:16.584247Z","iopub.execute_input":"2022-10-09T14:28:16.585538Z","iopub.status.idle":"2022-10-09T14:28:16.712775Z","shell.execute_reply.started":"2022-10-09T14:28:16.585484Z","shell.execute_reply":"2022-10-09T14:28:16.711420Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"start.groupby('program_number')[['position_at_finish']].describe()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:16.714769Z","iopub.execute_input":"2022-10-09T14:28:16.715586Z","iopub.status.idle":"2022-10-09T14:28:16.840163Z","shell.execute_reply.started":"2022-10-09T14:28:16.715522Z","shell.execute_reply":"2022-10-09T14:28:16.838881Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.catplot(x='position_at_finish',data=start,kind='count',row='program_number');","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:16.841562Z","iopub.execute_input":"2022-10-09T14:28:16.841980Z","iopub.status.idle":"2022-10-09T14:28:21.641128Z","shell.execute_reply.started":"2022-10-09T14:28:16.841945Z","shell.execute_reply":"2022-10-09T14:28:21.640163Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"start['program_number'].unique()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:21.642515Z","iopub.execute_input":"2022-10-09T14:28:21.643714Z","iopub.status.idle":"2022-10-09T14:28:21.653194Z","shell.execute_reply.started":"2022-10-09T14:28:21.643637Z","shell.execute_reply":"2022-10-09T14:28:21.651851Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#No of jockeys\nlen(start.jockey.unique())","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:21.654792Z","iopub.execute_input":"2022-10-09T14:28:21.656088Z","iopub.status.idle":"2022-10-09T14:28:21.665820Z","shell.execute_reply.started":"2022-10-09T14:28:21.656037Z","shell.execute_reply":"2022-10-09T14:28:21.664574Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#most 10 jockeys\nstart.jockey.value_counts()[0:10]","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:21.667416Z","iopub.execute_input":"2022-10-09T14:28:21.667866Z","iopub.status.idle":"2022-10-09T14:28:21.681207Z","shell.execute_reply.started":"2022-10-09T14:28:21.667830Z","shell.execute_reply":"2022-10-09T14:28:21.679801Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"complete.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:21.682839Z","iopub.execute_input":"2022-10-09T14:28:21.683185Z","iopub.status.idle":"2022-10-09T14:28:21.707920Z","shell.execute_reply.started":"2022-10-09T14:28:21.683156Z","shell.execute_reply":"2022-10-09T14:28:21.706701Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"complete['program_number'].unique()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:21.709326Z","iopub.execute_input":"2022-10-09T14:28:21.709689Z","iopub.status.idle":"2022-10-09T14:28:22.073923Z","shell.execute_reply.started":"2022-10-09T14:28:21.709632Z","shell.execute_reply":"2022-10-09T14:28:22.072522Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"complete.shape","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:22.075297Z","iopub.execute_input":"2022-10-09T14:28:22.075635Z","iopub.status.idle":"2022-10-09T14:28:22.082617Z","shell.execute_reply.started":"2022-10-09T14:28:22.075605Z","shell.execute_reply":"2022-10-09T14:28:22.081323Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"complete.info()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:22.084595Z","iopub.execute_input":"2022-10-09T14:28:22.085053Z","iopub.status.idle":"2022-10-09T14:28:22.102612Z","shell.execute_reply.started":"2022-10-09T14:28:22.085020Z","shell.execute_reply":"2022-10-09T14:28:22.100814Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#creating a race id\ncomplete['race_id']=complete.track_id+complete.race_date+complete.race_number.astype(str)\ncomplete.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:22.104259Z","iopub.execute_input":"2022-10-09T14:28:22.104798Z","iopub.status.idle":"2022-10-09T14:28:26.622945Z","shell.execute_reply.started":"2022-10-09T14:28:22.104739Z","shell.execute_reply":"2022-10-09T14:28:26.621612Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"complete.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:26.628358Z","iopub.execute_input":"2022-10-09T14:28:26.628824Z","iopub.status.idle":"2022-10-09T14:28:26.653342Z","shell.execute_reply.started":"2022-10-09T14:28:26.628785Z","shell.execute_reply":"2022-10-09T14:28:26.652003Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#creating a horse id in every race\ncomplete['horse_id']=complete.race_id+complete.program_number.astype(str)\ncomplete.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:26.655065Z","iopub.execute_input":"2022-10-09T14:28:26.655567Z","iopub.status.idle":"2022-10-09T14:28:27.565484Z","shell.execute_reply.started":"2022-10-09T14:28:26.655515Z","shell.execute_reply":"2022-10-09T14:28:27.564115Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_horse = complete.groupby(['horse_id','race_id']).count()[['track_id']].reset_index()\nrace_horse.columns = ['horse_id','race_id','count_tracks_for_horse']\nrace_horse.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:27.566893Z","iopub.execute_input":"2022-10-09T14:28:27.567266Z","iopub.status.idle":"2022-10-09T14:28:33.733132Z","shell.execute_reply.started":"2022-10-09T14:28:27.567234Z","shell.execute_reply":"2022-10-09T14:28:33.731750Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_horse1 = complete.groupby(['horse_id','race_id']).max()[['trakus_index']].reset_index()\nrace_horse1.columns = ['horse_id','race_id','max_trakus_index']\nrace_horse1.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:33.735067Z","iopub.execute_input":"2022-10-09T14:28:33.737191Z","iopub.status.idle":"2022-10-09T14:28:49.916822Z","shell.execute_reply.started":"2022-10-09T14:28:33.737147Z","shell.execute_reply":"2022-10-09T14:28:49.915513Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race1 = race_horse.groupby(['race_id']).max()[['count_tracks_for_horse']].reset_index()\nrace1.columns = ['race_id','max_tracks_per_race']\nrace1.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:49.918603Z","iopub.execute_input":"2022-10-09T14:28:49.919019Z","iopub.status.idle":"2022-10-09T14:28:50.141825Z","shell.execute_reply.started":"2022-10-09T14:28:49.918985Z","shell.execute_reply":"2022-10-09T14:28:50.140493Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_horse = race_horse.merge(race1,on='race_id')\nrace_horse=race_horse.merge(race_horse1,on=['horse_id','race_id'])","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:50.143408Z","iopub.execute_input":"2022-10-09T14:28:50.143854Z","iopub.status.idle":"2022-10-09T14:28:50.191469Z","shell.execute_reply.started":"2022-10-09T14:28:50.143801Z","shell.execute_reply":"2022-10-09T14:28:50.189862Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_horse['injury1'] =  race_horse.count_tracks_for_horse < race_horse.max_tracks_per_race \nrace_horse['injury2'] =  race_horse.max_tracks_per_race  > race_horse.max_trakus_index\nrace_horse.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:50.193120Z","iopub.execute_input":"2022-10-09T14:28:50.193481Z","iopub.status.idle":"2022-10-09T14:28:50.213551Z","shell.execute_reply.started":"2022-10-09T14:28:50.193448Z","shell.execute_reply":"2022-10-09T14:28:50.212077Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_horse.injury1.sum(),race_horse.injury2.sum()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:50.215205Z","iopub.execute_input":"2022-10-09T14:28:50.215804Z","iopub.status.idle":"2022-10-09T14:28:50.226397Z","shell.execute_reply.started":"2022-10-09T14:28:50.215753Z","shell.execute_reply":"2022-10-09T14:28:50.225108Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"How many races had injury in 2019 ?","metadata":{"execution":{"iopub.status.busy":"2022-10-07T01:27:01.904873Z","iopub.execute_input":"2022-10-07T01:27:01.905207Z","iopub.status.idle":"2022-10-07T01:27:01.912532Z","shell.execute_reply.started":"2022-10-07T01:27:01.905183Z","shell.execute_reply":"2022-10-07T01:27:01.911310Z"}}},{"cell_type":"code","source":"race_horse.groupby('race_id')['injury1'].sum().sum()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:50.227756Z","iopub.execute_input":"2022-10-09T14:28:50.228287Z","iopub.status.idle":"2022-10-09T14:28:50.246751Z","shell.execute_reply.started":"2022-10-09T14:28:50.228247Z","shell.execute_reply":"2022-10-09T14:28:50.245210Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race2  = race_horse.groupby('race_id').agg(count_horses = ('horse_id','count') ,has_injury = ('injury1','max')) \nrace2.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:50.248524Z","iopub.execute_input":"2022-10-09T14:28:50.249180Z","iopub.status.idle":"2022-10-09T14:28:50.285188Z","shell.execute_reply.started":"2022-10-09T14:28:50.249142Z","shell.execute_reply":"2022-10-09T14:28:50.283874Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"what is the percentages races had injury in 2019 ? answer (243 / 2000)","metadata":{}},{"cell_type":"code","source":"race_horse.groupby('race_id')['injury1'].sum().sum()/race.shape[0]","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:50.286865Z","iopub.execute_input":"2022-10-09T14:28:50.287256Z","iopub.status.idle":"2022-10-09T14:28:50.304297Z","shell.execute_reply.started":"2022-10-09T14:28:50.287221Z","shell.execute_reply":"2022-10-09T14:28:50.302968Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"What is the track condition that is linked with highest injury rate?","metadata":{}},{"cell_type":"code","source":"complete=complete.merge(race_horse,on=['race_id','horse_id'])\ncomplete.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:28:50.307510Z","iopub.execute_input":"2022-10-09T14:28:50.308022Z","iopub.status.idle":"2022-10-09T14:29:00.441378Z","shell.execute_reply.started":"2022-10-09T14:28:50.307974Z","shell.execute_reply":"2022-10-09T14:29:00.440096Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"complete.groupby('track_condition').mean()['injury1'].sort_values()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:29:00.446476Z","iopub.execute_input":"2022-10-09T14:29:00.446923Z","iopub.status.idle":"2022-10-09T14:29:04.623888Z","shell.execute_reply.started":"2022-10-09T14:29:00.446882Z","shell.execute_reply":"2022-10-09T14:29:04.622710Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"complete.groupby('distance_id').mean()['injury1'].sort_values()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:29:04.625521Z","iopub.execute_input":"2022-10-09T14:29:04.625894Z","iopub.status.idle":"2022-10-09T14:29:06.276720Z","shell.execute_reply.started":"2022-10-09T14:29:04.625862Z","shell.execute_reply":"2022-10-09T14:29:06.275186Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"complete.groupby('course_type').mean()['injury1'].sort_values()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:29:06.278626Z","iopub.execute_input":"2022-10-09T14:29:06.279014Z","iopub.status.idle":"2022-10-09T14:29:08.269085Z","shell.execute_reply.started":"2022-10-09T14:29:06.278982Z","shell.execute_reply":"2022-10-09T14:29:08.267873Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"complete.groupby('race_type').mean()['injury1'].sort_values()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:29:08.271079Z","iopub.execute_input":"2022-10-09T14:29:08.271767Z","iopub.status.idle":"2022-10-09T14:29:10.263317Z","shell.execute_reply.started":"2022-10-09T14:29:08.271727Z","shell.execute_reply":"2022-10-09T14:29:10.261968Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"complete.groupby('run_up_distance').mean()['injury1'].sort_values()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:29:10.265264Z","iopub.execute_input":"2022-10-09T14:29:10.265771Z","iopub.status.idle":"2022-10-09T14:29:11.910727Z","shell.execute_reply.started":"2022-10-09T14:29:10.265731Z","shell.execute_reply":"2022-10-09T14:29:11.909217Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"columns=['trakus_index','latitude','longitude']\nfor i in columns:\n    complete['next_'+i]=complete.sort_values('trakus_index').groupby('horse_id')[i].shift(-1)\ncomplete.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:29:11.912312Z","iopub.execute_input":"2022-10-09T14:29:11.912734Z","iopub.status.idle":"2022-10-09T14:29:43.226731Z","shell.execute_reply.started":"2022-10-09T14:29:11.912696Z","shell.execute_reply":"2022-10-09T14:29:43.225588Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"complete=complete.dropna(axis=0)\ncomplete.isna().sum().sum()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:30:34.902832Z","iopub.execute_input":"2022-10-09T14:30:34.903295Z","iopub.status.idle":"2022-10-09T14:30:40.983030Z","shell.execute_reply.started":"2022-10-09T14:30:34.903261Z","shell.execute_reply":"2022-10-09T14:30:40.981690Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"complete['not_sequence']=(complete['next_trakus_index']-complete['trakus_index'])==1\ncomplete['not_sequence'].sum().sum()==complete.shape[0]","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:30:49.084483Z","iopub.execute_input":"2022-10-09T14:30:49.085352Z","iopub.status.idle":"2022-10-09T14:30:49.124882Z","shell.execute_reply.started":"2022-10-09T14:30:49.085302Z","shell.execute_reply":"2022-10-09T14:30:49.123688Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Distance\n\nThis uses the ‘haversine’ formula to calculate the great-circle distance between two points – that is, the shortest distance over the earth’s surface – giving an ‘as-the-crow-flies’ distance between the points (ignoring any hills they fly over, of course!).\n\nHaversine formula\n\na = sin²(Δφ/2) + cos φ1 ⋅ cos φ2 ⋅ sin²(Δλ/2)\n\nc = 2 ⋅ atan2( √a, √(1−a) )\n\nd = R ⋅ c\n\nwhere\tφ is latitude, λ is longitude, R is earth’s radius (mean radius = 6,371km);\n\nnote that angles need to be in radians to pass to trig functions!","metadata":{}},{"cell_type":"code","source":"from math import sin,cos,atan2,radians,sqrt\ndef distance(lat1,lon1,lat2,lon2):\n    R = 6373000\n\n    lat1 = radians(lat1)\n    lon1 = radians(lon1)\n    lat2 = radians(lat2)\n    lon2 = radians(lon2)\n\n    dlon = lon2 - lon1\n    dlat = lat2 - lat1\n\n    a = sin(dlat / 2)**2 + cos(lat1) * cos(lat2) * sin(dlon / 2)**2\n    c = 2 * atan2(sqrt(a), sqrt(1 - a))\n\n    distance = R * c\n    return distance ","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:31:23.403559Z","iopub.execute_input":"2022-10-09T14:31:23.404089Z","iopub.status.idle":"2022-10-09T14:31:23.413481Z","shell.execute_reply.started":"2022-10-09T14:31:23.404046Z","shell.execute_reply":"2022-10-09T14:31:23.412064Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"complete['distance'] = complete.apply(lambda row: distance(row['latitude'],row['longitude'],row['next_latitude'],row['next_longitude']), axis=1)\ncomplete['distance'] = complete['distance'] / (complete.next_trakus_index - complete.trakus_index)\ncomplete.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:32:02.218047Z","iopub.execute_input":"2022-10-09T14:32:02.218503Z","iopub.status.idle":"2022-10-09T14:34:22.945202Z","shell.execute_reply.started":"2022-10-09T14:32:02.218467Z","shell.execute_reply":"2022-10-09T14:34:22.944260Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"complete['distance'].describe()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:34:22.947047Z","iopub.execute_input":"2022-10-09T14:34:22.947631Z","iopub.status.idle":"2022-10-09T14:34:23.204555Z","shell.execute_reply.started":"2022-10-09T14:34:22.947594Z","shell.execute_reply":"2022-10-09T14:34:23.203278Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"complete=complete.dropna(axis=0)\ncomplete.isna().sum().sum()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:34:23.206214Z","iopub.execute_input":"2022-10-09T14:34:23.206898Z","iopub.status.idle":"2022-10-09T14:34:29.267768Z","shell.execute_reply.started":"2022-10-09T14:34:23.206861Z","shell.execute_reply":"2022-10-09T14:34:29.266460Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"lets remove outliers","metadata":{}},{"cell_type":"code","source":"from scipy.stats import iqr\niqr=iqr(complete['distance'])\nprint('IQR=',iqr)\nupper_threshold=np.quantile(complete['distance'],0.75)+1.5*iqr\nprint('upper_threshold',upper_threshold)\ncomplete_normal=complete[complete['distance']<upper_threshold]\ncomplete_normal.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:34:29.270917Z","iopub.execute_input":"2022-10-09T14:34:29.271312Z","iopub.status.idle":"2022-10-09T14:34:30.757970Z","shell.execute_reply.started":"2022-10-09T14:34:29.271277Z","shell.execute_reply":"2022-10-09T14:34:30.756731Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"complete_normal.shape,complete.shape","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:34:30.760016Z","iopub.execute_input":"2022-10-09T14:34:30.760429Z","iopub.status.idle":"2022-10-09T14:34:30.769147Z","shell.execute_reply.started":"2022-10-09T14:34:30.760393Z","shell.execute_reply":"2022-10-09T14:34:30.767857Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"complete_outliers=complete[complete['distance']>upper_threshold]\ncomplete_outliers['trakus_index'].head()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:36:53.697384Z","iopub.execute_input":"2022-10-09T14:36:53.697885Z","iopub.status.idle":"2022-10-09T14:36:53.719234Z","shell.execute_reply.started":"2022-10-09T14:36:53.697843Z","shell.execute_reply":"2022-10-09T14:36:53.717520Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"complete_outliers.shape","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:34:30.817756Z","iopub.execute_input":"2022-10-09T14:34:30.818579Z","iopub.status.idle":"2022-10-09T14:34:30.827191Z","shell.execute_reply.started":"2022-10-09T14:34:30.818536Z","shell.execute_reply":"2022-10-09T14:34:30.825740Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"complete_outliers['not_sequence']=(complete_outliers['next_trakus_index']-complete_outliers['trakus_index'])==1\ncomplete_outliers['not_sequence'].sum().sum()==complete_outliers.shape[0]","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:34:30.829140Z","iopub.execute_input":"2022-10-09T14:34:30.829604Z","iopub.status.idle":"2022-10-09T14:34:30.842337Z","shell.execute_reply.started":"2022-10-09T14:34:30.829553Z","shell.execute_reply":"2022-10-09T14:34:30.841050Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"(complete['distance']<0).sum().sum()","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:34:30.844291Z","iopub.execute_input":"2022-10-09T14:34:30.845046Z","iopub.status.idle":"2022-10-09T14:34:30.869050Z","shell.execute_reply.started":"2022-10-09T14:34:30.845008Z","shell.execute_reply":"2022-10-09T14:34:30.867994Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"complete_outliers.index","metadata":{"execution":{"iopub.status.busy":"2022-10-09T14:34:30.871745Z","iopub.execute_input":"2022-10-09T14:34:30.872301Z","iopub.status.idle":"2022-10-09T14:34:30.879556Z","shell.execute_reply.started":"2022-10-09T14:34:30.872265Z","shell.execute_reply":"2022-10-09T14:34:30.878585Z"},"trusted":true},"execution_count":null,"outputs":[]}]}