{"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)\n\nfrom matplotlib import pyplot as plt\nimport seaborn as sns\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-09-20T17:52:37.897199Z","iopub.execute_input":"2022-09-20T17:52:37.897756Z","iopub.status.idle":"2022-09-20T17:52:37.906827Z","shell.execute_reply.started":"2022-09-20T17:52:37.897709Z","shell.execute_reply":"2022-09-20T17:52:37.905570Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"horse_data = pd.read_csv('/kaggle/input/big-data-derby-2022/nyra_start_table.csv',names = ['track_id', 'race_date', 'race_number', 'program_number', 'weight_carried', 'jockey', 'odds', 'position_at_finish'])\ntrack_data = pd.read_csv('/kaggle/input/big-data-derby-2022/nyra_race_table.csv')\ntracking_data = pd.read_csv('/kaggle/input/big-data-derby-2022/nyra_tracking_table.csv', names = ['track_id', 'race_date', 'race_number', 'program_number', 'trakus_index', 'latitude', 'longitude'])","metadata":{"execution":{"iopub.status.busy":"2022-09-20T17:52:37.909240Z","iopub.execute_input":"2022-09-20T17:52:37.909800Z","iopub.status.idle":"2022-09-20T17:52:43.365120Z","shell.execute_reply.started":"2022-09-20T17:52:37.909750Z","shell.execute_reply":"2022-09-20T17:52:43.363831Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **START TABLE**","metadata":{}},{"cell_type":"code","source":"horse_data.head()","metadata":{"execution":{"iopub.status.busy":"2022-09-20T17:52:43.366724Z","iopub.execute_input":"2022-09-20T17:52:43.367727Z","iopub.status.idle":"2022-09-20T17:52:43.382132Z","shell.execute_reply.started":"2022-09-20T17:52:43.367687Z","shell.execute_reply":"2022-09-20T17:52:43.380960Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"horse_data['odds'] = horse_data['odds']/100\nhorse_data","metadata":{"execution":{"iopub.status.busy":"2022-09-20T17:52:43.385194Z","iopub.execute_input":"2022-09-20T17:52:43.385768Z","iopub.status.idle":"2022-09-20T17:52:43.410981Z","shell.execute_reply.started":"2022-09-20T17:52:43.385724Z","shell.execute_reply":"2022-09-20T17:52:43.409846Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"horse_data.groupby(['race_date'])['race_date'].count()","metadata":{"execution":{"iopub.status.busy":"2022-09-20T17:52:43.412449Z","iopub.execute_input":"2022-09-20T17:52:43.412911Z","iopub.status.idle":"2022-09-20T17:52:43.428643Z","shell.execute_reply.started":"2022-09-20T17:52:43.412867Z","shell.execute_reply":"2022-09-20T17:52:43.427557Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"best_horses = horse_data.groupby(['jockey'])['position_at_finish'].mean().sort_values(ascending=True).to_frame(name = 'average performance')\nbest_horses.head()","metadata":{"execution":{"iopub.status.busy":"2022-09-20T17:52:43.429931Z","iopub.execute_input":"2022-09-20T17:52:43.430253Z","iopub.status.idle":"2022-09-20T17:52:43.446752Z","shell.execute_reply.started":"2022-09-20T17:52:43.430223Z","shell.execute_reply":"2022-09-20T17:52:43.445644Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Average performance of each horse (178 horses)","metadata":{}},{"cell_type":"code","source":"horse_data.loc[horse_data.jockey == 'Cory Orm']","metadata":{"execution":{"iopub.status.busy":"2022-09-20T17:52:43.448155Z","iopub.execute_input":"2022-09-20T17:52:43.448533Z","iopub.status.idle":"2022-09-20T17:52:43.465422Z","shell.execute_reply.started":"2022-09-20T17:52:43.448483Z","shell.execute_reply":"2022-09-20T17:52:43.464161Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"This horse only had one race! We need a better way to rank the horses in terms of performance\nThe best ranking should have a low position for as many races as possible. This is hard to model so my plan is to encode the position so that a higher number signifies better position. Then we can do position * number of races","metadata":{}},{"cell_type":"code","source":"horse_data.position_at_finish.dtype","metadata":{"execution":{"iopub.status.busy":"2022-09-20T17:52:43.467060Z","iopub.execute_input":"2022-09-20T17:52:43.467428Z","iopub.status.idle":"2022-09-20T17:52:43.477353Z","shell.execute_reply.started":"2022-09-20T17:52:43.467371Z","shell.execute_reply":"2022-09-20T17:52:43.476223Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"new_feat_df = horse_data.copy()\nencoding = {'position_at_finish':  {1: 14, 2: 13, 3: 12, 4: 11, 5:10, 6: 9, 7:8, 8:7, 9:6, 10:5, 11:4, 12:3, 13:2, 14:1}}\nnew_feat_df = new_feat_df.replace(encoding)\nnew_feat_df.head()","metadata":{"execution":{"iopub.status.busy":"2022-09-20T17:52:43.483043Z","iopub.execute_input":"2022-09-20T17:52:43.483466Z","iopub.status.idle":"2022-09-20T17:52:43.506284Z","shell.execute_reply.started":"2022-09-20T17:52:43.483432Z","shell.execute_reply":"2022-09-20T17:52:43.505298Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"horse_data.head()","metadata":{"execution":{"iopub.status.busy":"2022-09-20T17:52:43.507569Z","iopub.execute_input":"2022-09-20T17:52:43.508181Z","iopub.status.idle":"2022-09-20T17:52:43.522788Z","shell.execute_reply.started":"2022-09-20T17:52:43.508144Z","shell.execute_reply":"2022-09-20T17:52:43.521580Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def performance(grp):\n    return grp.position_at_finish.mode() * grp.jockey.count()\n    \n    \n\nbest_horses = new_feat_df.groupby('jockey').apply( lambda grp: grp['position_at_finish'].median()*grp['jockey'].count()).sort_values(ascending = False)\nbest_horses","metadata":{"execution":{"iopub.status.busy":"2022-09-20T18:04:46.222046Z","iopub.execute_input":"2022-09-20T18:04:46.222948Z","iopub.status.idle":"2022-09-20T18:04:46.304574Z","shell.execute_reply.started":"2022-09-20T18:04:46.222885Z","shell.execute_reply":"2022-09-20T18:04:46.303755Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Seems like on average the best jockeys were Manuel Franco, Jose Lezcano, Dylan Davis, Irad Ortiz Jr. and Junior Alvarado, with Suguru Hamanaka being the worst. ","metadata":{}},{"cell_type":"code","source":"fig, ax = plt.subplots()\nsns.countplot(data = horse_data, ax=ax, x='track_id').set(title = 'Tracks played')","metadata":{"execution":{"iopub.status.busy":"2022-09-20T18:08:07.849769Z","iopub.execute_input":"2022-09-20T18:08:07.850237Z","iopub.status.idle":"2022-09-20T18:08:08.070048Z","shell.execute_reply.started":"2022-09-20T18:08:07.850198Z","shell.execute_reply":"2022-09-20T18:08:08.069075Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"AQU was the track used the most","metadata":{}},{"cell_type":"code","source":"horse_data['race_date'] = pd.to_datetime(horse_data['race_date'])\nhorse_data.head()","metadata":{"execution":{"iopub.status.busy":"2022-09-20T17:52:44.426027Z","iopub.execute_input":"2022-09-20T17:52:44.426359Z","iopub.status.idle":"2022-09-20T17:52:44.449576Z","shell.execute_reply.started":"2022-09-20T17:52:44.426326Z","shell.execute_reply":"2022-09-20T17:52:44.448463Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots(figsize=(25,5))\n\nsns.histplot(data=horse_data,hue=\"track_id\", ax=ax,x=\"race_date\")\nax.set_title(\"Tracks throughout year\", fontsize=20)","metadata":{"execution":{"iopub.status.busy":"2022-09-20T18:09:14.711919Z","iopub.execute_input":"2022-09-20T18:09:14.712401Z","iopub.status.idle":"2022-09-20T18:09:15.332990Z","shell.execute_reply.started":"2022-09-20T18:09:14.712347Z","shell.execute_reply":"2022-09-20T18:09:15.332159Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"AQU was used from November to April (Winter), BEL was from May to July and September to November(Summer/Autumn) and SAR was used from July to September (Summer). ","metadata":{}},{"cell_type":"code","source":"fig, ax = plt.subplots(figsize=(25,5))\nsns.countplot(data = horse_data, ax=ax, x = 'race_number', hue = 'track_id')","metadata":{"execution":{"iopub.status.busy":"2022-09-20T17:52:46.108753Z","iopub.execute_input":"2022-09-20T17:52:46.109400Z","iopub.status.idle":"2022-09-20T17:52:46.628907Z","shell.execute_reply.started":"2022-09-20T17:52:46.109343Z","shell.execute_reply":"2022-09-20T17:52:46.627460Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We had up to 13 races per day, but usually it was 1-9","metadata":{}},{"cell_type":"code","source":"horse_data['race_number'] = horse_data['race_number'].astype('int8')","metadata":{"execution":{"iopub.status.busy":"2022-09-20T17:52:46.630599Z","iopub.execute_input":"2022-09-20T17:52:46.631400Z","iopub.status.idle":"2022-09-20T17:52:46.639186Z","shell.execute_reply.started":"2022-09-20T17:52:46.631334Z","shell.execute_reply":"2022-09-20T17:52:46.637585Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"horse_data.program_number.value_counts()","metadata":{"execution":{"iopub.status.busy":"2022-09-20T17:52:46.640869Z","iopub.execute_input":"2022-09-20T17:52:46.641359Z","iopub.status.idle":"2022-09-20T17:52:46.658189Z","shell.execute_reply.started":"2022-09-20T17:52:46.641316Z","shell.execute_reply":"2022-09-20T17:52:46.656435Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"date_num_df = horse_data.groupby(['race_date'])['race_number'].max().to_frame(name = 'number of races').reset_index()\ndate_num_df","metadata":{"execution":{"iopub.status.busy":"2022-09-20T17:52:46.659498Z","iopub.execute_input":"2022-09-20T17:52:46.659884Z","iopub.status.idle":"2022-09-20T17:52:46.684812Z","shell.execute_reply.started":"2022-09-20T17:52:46.659847Z","shell.execute_reply":"2022-09-20T17:52:46.683702Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots(figsize=(20,5))\nsns.histplot(data = date_num_df, x = 'number of races')","metadata":{"execution":{"iopub.status.busy":"2022-09-20T17:52:46.686649Z","iopub.execute_input":"2022-09-20T17:52:46.687867Z","iopub.status.idle":"2022-09-20T17:52:47.042130Z","shell.execute_reply.started":"2022-09-20T17:52:46.687821Z","shell.execute_reply":"2022-09-20T17:52:47.040881Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Around 7-10 races each day. ","metadata":{}},{"cell_type":"code","source":"fig, ax = plt.subplots(figsize=(20,5))\nsns.kdeplot(data = horse_data, x = 'weight_carried')","metadata":{"execution":{"iopub.status.busy":"2022-09-20T17:52:47.044083Z","iopub.execute_input":"2022-09-20T17:52:47.045589Z","iopub.status.idle":"2022-09-20T17:52:47.470061Z","shell.execute_reply.started":"2022-09-20T17:52:47.045536Z","shell.execute_reply":"2022-09-20T17:52:47.468874Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Average weight carried by horse is around 120","metadata":{}},{"cell_type":"code","source":"fig, ax = plt.subplots(figsize=(20,5))\nsns.kdeplot(data = horse_data, x = 'odds')","metadata":{"execution":{"iopub.status.busy":"2022-09-20T17:52:47.471686Z","iopub.execute_input":"2022-09-20T17:52:47.472866Z","iopub.status.idle":"2022-09-20T17:52:47.865390Z","shell.execute_reply.started":"2022-09-20T17:52:47.472820Z","shell.execute_reply":"2022-09-20T17:52:47.863973Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **RACE TABLE**","metadata":{}},{"cell_type":"code","source":"track_data.head()","metadata":{"execution":{"iopub.status.busy":"2022-09-20T17:52:47.872112Z","iopub.execute_input":"2022-09-20T17:52:47.873111Z","iopub.status.idle":"2022-09-20T17:52:47.891130Z","shell.execute_reply.started":"2022-09-20T17:52:47.873060Z","shell.execute_reply":"2022-09-20T17:52:47.889752Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"track_data['race_date'] = pd.to_datetime(track_data['race_date'])\ntrack_data.info()","metadata":{"execution":{"iopub.status.busy":"2022-09-20T17:52:47.893073Z","iopub.execute_input":"2022-09-20T17:52:47.893547Z","iopub.status.idle":"2022-09-20T17:52:47.921390Z","shell.execute_reply.started":"2022-09-20T17:52:47.893498Z","shell.execute_reply":"2022-09-20T17:52:47.920398Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.countplot(data = track_data, x = 'course_type')","metadata":{"execution":{"iopub.status.busy":"2022-09-20T17:52:47.922738Z","iopub.execute_input":"2022-09-20T17:52:47.924167Z","iopub.status.idle":"2022-09-20T17:52:48.153875Z","shell.execute_reply.started":"2022-09-20T17:52:47.924105Z","shell.execute_reply":"2022-09-20T17:52:48.153103Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"track_data.distance_id.value_counts()","metadata":{"execution":{"iopub.status.busy":"2022-09-20T17:52:48.155228Z","iopub.execute_input":"2022-09-20T17:52:48.156293Z","iopub.status.idle":"2022-09-20T17:52:48.164341Z","shell.execute_reply.started":"2022-09-20T17:52:48.156259Z","shell.execute_reply":"2022-09-20T17:52:48.163248Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"grp = track_data.groupby(['distance_id'])['distance_id'].count().to_frame(name = 'number of races').reset_index()\ndisplay(grp.head())\nfig, ax = plt.subplots(figsize=(20,5))\nax = sns.barplot(data = grp, x = 'distance_id', y = 'number of races')\nfor i in ax.containers:\n    ax.bar_label(i,)","metadata":{"execution":{"iopub.status.busy":"2022-09-20T17:52:48.166007Z","iopub.execute_input":"2022-09-20T17:52:48.166465Z","iopub.status.idle":"2022-09-20T17:52:48.620539Z","shell.execute_reply.started":"2022-09-20T17:52:48.166433Z","shell.execute_reply":"2022-09-20T17:52:48.619263Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Most were 6-8 furlongs","metadata":{}},{"cell_type":"code","source":"ax = sns.countplot(data = track_data, x = 'track_condition')\nfor i in ax.containers:\n    ax.bar_label(i,)","metadata":{"execution":{"iopub.status.busy":"2022-09-20T17:52:48.622031Z","iopub.execute_input":"2022-09-20T17:52:48.622405Z","iopub.status.idle":"2022-09-20T17:52:48.877499Z","shell.execute_reply.started":"2022-09-20T17:52:48.622349Z","shell.execute_reply":"2022-09-20T17:52:48.876525Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Most tracks were fast","metadata":{}},{"cell_type":"code","source":"ax = sns.countplot(data = track_data, x = 'race_type')\nfor i in ax.containers:\n    ax.bar_label(i,)","metadata":{"execution":{"iopub.status.busy":"2022-09-20T17:52:48.879128Z","iopub.execute_input":"2022-09-20T17:52:48.879505Z","iopub.status.idle":"2022-09-20T17:52:49.185446Z","shell.execute_reply.started":"2022-09-20T17:52:48.879472Z","shell.execute_reply":"2022-09-20T17:52:49.184257Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Most types were Claiming, Maiden Special Weight, Maiden Claiming","metadata":{}},{"cell_type":"code","source":"track_data.run_up_distance.describe()","metadata":{"execution":{"iopub.status.busy":"2022-09-20T17:52:49.187202Z","iopub.execute_input":"2022-09-20T17:52:49.187654Z","iopub.status.idle":"2022-09-20T17:52:49.200589Z","shell.execute_reply.started":"2022-09-20T17:52:49.187601Z","shell.execute_reply":"2022-09-20T17:52:49.199428Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}