{"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\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-15T13:42:46.429264Z","iopub.execute_input":"2022-10-15T13:42:46.429672Z","iopub.status.idle":"2022-10-15T13:42:46.442280Z","shell.execute_reply.started":"2022-10-15T13:42:46.429638Z","shell.execute_reply":"2022-10-15T13:42:46.441004Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import pandas as pd\npd.set_option('display.max_columns', 50)","metadata":{"execution":{"iopub.status.busy":"2022-10-15T13:42:52.558574Z","iopub.execute_input":"2022-10-15T13:42:52.559009Z","iopub.status.idle":"2022-10-15T13:42:52.564088Z","shell.execute_reply.started":"2022-10-15T13:42:52.558971Z","shell.execute_reply":"2022-10-15T13:42:52.562864Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Reading the data","metadata":{}},{"cell_type":"code","source":"rdf = pd.read_csv('/kaggle/input/big-data-derby-2022/nyra_race_table.csv')\nsdf = pd.read_csv('/kaggle/input/big-data-derby-2022/nyra_start_table.csv', header=None)\n\n# reading the horse id dataset provided by\nhdf = pd.read_csv('/kaggle/input/big-data-derby-2022-global-horse-ids-and-places/horse_ids.csv')","metadata":{"execution":{"iopub.status.busy":"2022-10-15T13:47:06.047685Z","iopub.execute_input":"2022-10-15T13:47:06.048112Z","iopub.status.idle":"2022-10-15T13:47:06.096312Z","shell.execute_reply.started":"2022-10-15T13:47:06.048075Z","shell.execute_reply":"2022-10-15T13:47:06.095144Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Cleaning the data","metadata":{"_kg_hide-input":false}},{"cell_type":"code","source":"start_cols = ['track_id','race_date','race_number','program_number','weight_carried','jockey','odds','final_place']","metadata":{"execution":{"iopub.status.busy":"2022-10-15T13:48:35.791157Z","iopub.execute_input":"2022-10-15T13:48:35.791549Z","iopub.status.idle":"2022-10-15T13:48:35.797277Z","shell.execute_reply.started":"2022-10-15T13:48:35.791518Z","shell.execute_reply":"2022-10-15T13:48:35.795862Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sdf.rename({i: col for i, col in enumerate(start_cols)}, axis=1, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-10-15T13:48:38.588126Z","iopub.execute_input":"2022-10-15T13:48:38.588514Z","iopub.status.idle":"2022-10-15T13:48:38.596365Z","shell.execute_reply.started":"2022-10-15T13:48:38.588483Z","shell.execute_reply":"2022-10-15T13:48:38.595445Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sdf['program_number'] = sdf['program_number'].apply(lambda x: x.strip())","metadata":{"execution":{"iopub.status.busy":"2022-10-15T13:50:33.334835Z","iopub.execute_input":"2022-10-15T13:50:33.335245Z","iopub.status.idle":"2022-10-15T13:50:33.345927Z","shell.execute_reply.started":"2022-10-15T13:50:33.335210Z","shell.execute_reply":"2022-10-15T13:50:33.344566Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"hdf['program_number'] = hdf['program_number'].apply(lambda x: x.strip())","metadata":{"execution":{"iopub.status.busy":"2022-10-15T13:50:56.545190Z","iopub.execute_input":"2022-10-15T13:50:56.545975Z","iopub.status.idle":"2022-10-15T13:50:56.556947Z","shell.execute_reply.started":"2022-10-15T13:50:56.545930Z","shell.execute_reply":"2022-10-15T13:50:56.555747Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"hdf.rename(columns = {'race':'race_number','finishing_place':'final_place'}, inplace = True)","metadata":{"execution":{"iopub.status.busy":"2022-10-15T13:52:13.723830Z","iopub.execute_input":"2022-10-15T13:52:13.724226Z","iopub.status.idle":"2022-10-15T13:52:13.730928Z","shell.execute_reply.started":"2022-10-15T13:52:13.724193Z","shell.execute_reply":"2022-10-15T13:52:13.729547Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"hdf['final_place'].unique()","metadata":{"execution":{"iopub.status.busy":"2022-10-15T13:55:42.556211Z","iopub.execute_input":"2022-10-15T13:55:42.556630Z","iopub.status.idle":"2022-10-15T13:55:42.564155Z","shell.execute_reply.started":"2022-10-15T13:55:42.556590Z","shell.execute_reply":"2022-10-15T13:55:42.562991Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sdf['race_date'] = pd.to_datetime(sdf[\"race_date\"], format='%Y-%m-%d')\nrdf['race_date'] = pd.to_datetime(rdf[\"race_date\"], format='%Y-%m-%d')\nhdf['race_date'] = pd.to_datetime(hdf[\"race_date\"], format='%Y-%m-%d')","metadata":{"execution":{"iopub.status.busy":"2022-10-15T14:00:14.255080Z","iopub.execute_input":"2022-10-15T14:00:14.255507Z","iopub.status.idle":"2022-10-15T14:00:14.277037Z","shell.execute_reply.started":"2022-10-15T14:00:14.255468Z","shell.execute_reply":"2022-10-15T14:00:14.275834Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Queries","metadata":{}},{"cell_type":"code","source":"print(\"Shape of nyra_start_table:\",sdf.shape)\nprint(\"Shape of nyra_race_table:\",rdf.shape)\nprint(\"Shape of horse_table:\",hdf.shape)","metadata":{"execution":{"iopub.status.busy":"2022-10-15T13:58:48.677546Z","iopub.execute_input":"2022-10-15T13:58:48.677977Z","iopub.status.idle":"2022-10-15T13:58:48.684093Z","shell.execute_reply.started":"2022-10-15T13:58:48.677942Z","shell.execute_reply":"2022-10-15T13:58:48.683204Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### We can see that there is 1 row more compared to start table","metadata":{}},{"cell_type":"code","source":"rdf.info()","metadata":{"execution":{"iopub.status.busy":"2022-10-15T14:02:47.572170Z","iopub.execute_input":"2022-10-15T14:02:47.572952Z","iopub.status.idle":"2022-10-15T14:02:47.588714Z","shell.execute_reply.started":"2022-10-15T14:02:47.572911Z","shell.execute_reply":"2022-10-15T14:02:47.586855Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sdf.info()","metadata":{"execution":{"iopub.status.busy":"2022-10-15T14:03:09.504264Z","iopub.execute_input":"2022-10-15T14:03:09.504649Z","iopub.status.idle":"2022-10-15T14:03:09.526729Z","shell.execute_reply.started":"2022-10-15T14:03:09.504616Z","shell.execute_reply":"2022-10-15T14:03:09.525367Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# merge start and race table\ndf = pd.merge(sdf,rdf,on=['track_id','race_date','race_number'], how='left')\ndf.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-15T14:05:48.184703Z","iopub.execute_input":"2022-10-15T14:05:48.185148Z","iopub.status.idle":"2022-10-15T14:05:48.227739Z","shell.execute_reply.started":"2022-10-15T14:05:48.185110Z","shell.execute_reply":"2022-10-15T14:05:48.226606Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df.shape","metadata":{"execution":{"iopub.status.busy":"2022-10-15T14:06:05.080051Z","iopub.execute_input":"2022-10-15T14:06:05.080472Z","iopub.status.idle":"2022-10-15T14:06:05.089173Z","shell.execute_reply.started":"2022-10-15T14:06:05.080437Z","shell.execute_reply":"2022-10-15T14:06:05.087837Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df.columns","metadata":{"execution":{"iopub.status.busy":"2022-10-15T14:06:33.541921Z","iopub.execute_input":"2022-10-15T14:06:33.542346Z","iopub.status.idle":"2022-10-15T14:06:33.550297Z","shell.execute_reply.started":"2022-10-15T14:06:33.542310Z","shell.execute_reply":"2022-10-15T14:06:33.549112Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = df[[\n    'track_id', 'race_date', 'race_number', 'program_number',\n#     'weight_carried', 'jockey', 'odds',\n    'final_place',\n#     'distance_id',\n#     'course_type', 'track_condition', 'run_up_distance', 'race_type',\n#     'purse', 'post_time'\n]]","metadata":{"execution":{"iopub.status.busy":"2022-10-15T14:08:07.970879Z","iopub.execute_input":"2022-10-15T14:08:07.971300Z","iopub.status.idle":"2022-10-15T14:08:07.978791Z","shell.execute_reply.started":"2022-10-15T14:08:07.971263Z","shell.execute_reply":"2022-10-15T14:08:07.977669Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df.head()","metadata":{"execution":{"iopub.status.busy":"2022-10-15T14:08:09.975446Z","iopub.execute_input":"2022-10-15T14:08:09.976485Z","iopub.status.idle":"2022-10-15T14:08:09.991143Z","shell.execute_reply.started":"2022-10-15T14:08:09.976441Z","shell.execute_reply":"2022-10-15T14:08:09.989645Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Exploring data","metadata":{}},{"cell_type":"code","source":"table = pd.merge(df,hdf,on=['track_id','race_date','race_number','program_number','final_place'], how='left')","metadata":{"execution":{"iopub.status.busy":"2022-10-15T14:12:27.549724Z","iopub.execute_input":"2022-10-15T14:12:27.550144Z","iopub.status.idle":"2022-10-15T14:12:27.568399Z","shell.execute_reply.started":"2022-10-15T14:12:27.550109Z","shell.execute_reply":"2022-10-15T14:12:27.567454Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"table.info()","metadata":{"execution":{"iopub.status.busy":"2022-10-15T14:12:29.252919Z","iopub.execute_input":"2022-10-15T14:12:29.253993Z","iopub.status.idle":"2022-10-15T14:12:29.271419Z","shell.execute_reply.started":"2022-10-15T14:12:29.253951Z","shell.execute_reply":"2022-10-15T14:12:29.270491Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"table.isnull().sum()","metadata":{"execution":{"iopub.status.busy":"2022-10-15T14:12:58.064148Z","iopub.execute_input":"2022-10-15T14:12:58.064566Z","iopub.status.idle":"2022-10-15T14:12:58.076968Z","shell.execute_reply.started":"2022-10-15T14:12:58.064529Z","shell.execute_reply":"2022-10-15T14:12:58.075890Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## We can see that 103 rows does not match with each other and there is 1 row more in horse_id dataset\n### Please let me know if I'm doing something wrong","metadata":{}},{"cell_type":"code","source":"table2 = pd.merge(sdf,hdf,on=['track_id','race_date','race_number','program_number','final_place'], how='left')\ntable2.isnull().sum()","metadata":{"execution":{"iopub.status.busy":"2022-10-15T14:21:46.780093Z","iopub.execute_input":"2022-10-15T14:21:46.780523Z","iopub.status.idle":"2022-10-15T14:21:46.813976Z","shell.execute_reply.started":"2022-10-15T14:21:46.780487Z","shell.execute_reply":"2022-10-15T14:21:46.812852Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}