{"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-08-18T14:19:35.430387Z","iopub.execute_input":"2022-08-18T14:19:35.430888Z","iopub.status.idle":"2022-08-18T14:19:35.438724Z","shell.execute_reply.started":"2022-08-18T14:19:35.430849Z","shell.execute_reply":"2022-08-18T14:19:35.437889Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_start_table = pd.read_csv(\"../input/big-data-derby-2022/nyra_start_table.csv\", low_memory = False)\n\nnyra_start_table.head()","metadata":{"execution":{"iopub.status.busy":"2022-08-18T14:19:38.833201Z","iopub.execute_input":"2022-08-18T14:19:38.834380Z","iopub.status.idle":"2022-08-18T14:19:38.870125Z","shell.execute_reply.started":"2022-08-18T14:19:38.834320Z","shell.execute_reply":"2022-08-18T14:19:38.869059Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# racetrack race data \nnyra_race_table = pd.read_csv(\"../input/big-data-derby-2022/nyra_race_table.csv\", low_memory = False)\n\nnyra_race_table.head()","metadata":{"execution":{"iopub.status.busy":"2022-08-18T14:19:40.881210Z","iopub.execute_input":"2022-08-18T14:19:40.881710Z","iopub.status.idle":"2022-08-18T14:19:40.904147Z","shell.execute_reply.started":"2022-08-18T14:19:40.881642Z","shell.execute_reply":"2022-08-18T14:19:40.902936Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# tracking data \nnyra_tracking_table = pd.read_csv(\"../input/big-data-derby-2022/nyra_tracking_table.csv\", low_memory = False)\n\nnyra_tracking_table.head()","metadata":{"execution":{"iopub.status.busy":"2022-08-18T14:19:42.818282Z","iopub.execute_input":"2022-08-18T14:19:42.819114Z","iopub.status.idle":"2022-08-18T14:19:48.842987Z","shell.execute_reply.started":"2022-08-18T14:19:42.819064Z","shell.execute_reply":"2022-08-18T14:19:48.841723Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_2019_complete = pd.read_csv(\"../input/big-data-derby-2022/nyra_2019_complete.csv\")\n\nnyra_2019_complete.head()","metadata":{"execution":{"iopub.status.busy":"2022-08-18T14:19:50.801508Z","iopub.execute_input":"2022-08-18T14:19:50.802456Z","iopub.status.idle":"2022-08-18T14:20:00.909508Z","shell.execute_reply.started":"2022-08-18T14:19:50.802408Z","shell.execute_reply":"2022-08-18T14:20:00.908379Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_tracking_table.info()","metadata":{"execution":{"iopub.status.busy":"2022-08-18T14:20:02.754841Z","iopub.execute_input":"2022-08-18T14:20:02.755288Z","iopub.status.idle":"2022-08-18T14:20:02.768369Z","shell.execute_reply.started":"2022-08-18T14:20:02.755247Z","shell.execute_reply":"2022-08-18T14:20:02.766920Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_start_table.info()","metadata":{"execution":{"iopub.status.busy":"2022-08-18T14:20:04.369748Z","iopub.execute_input":"2022-08-18T14:20:04.370221Z","iopub.status.idle":"2022-08-18T14:20:04.386925Z","shell.execute_reply.started":"2022-08-18T14:20:04.370181Z","shell.execute_reply":"2022-08-18T14:20:04.385808Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_race_table.info()","metadata":{"execution":{"iopub.status.busy":"2022-08-18T14:20:14.152982Z","iopub.execute_input":"2022-08-18T14:20:14.153473Z","iopub.status.idle":"2022-08-18T14:20:14.171427Z","shell.execute_reply.started":"2022-08-18T14:20:14.153437Z","shell.execute_reply":"2022-08-18T14:20:14.169903Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_2019_complete.info()","metadata":{"execution":{"iopub.status.busy":"2022-08-18T14:20:15.970302Z","iopub.execute_input":"2022-08-18T14:20:15.971209Z","iopub.status.idle":"2022-08-18T14:20:15.985450Z","shell.execute_reply.started":"2022-08-18T14:20:15.971167Z","shell.execute_reply":"2022-08-18T14:20:15.984424Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### merge key column in Race ID","metadata":{}},{"cell_type":"code","source":"key_col = [\"track_id\", \"race_date\", \"race_number\"]\n\nfor i in [nyra_tracking_table,nyra_start_table,nyra_race_table]:\n    i[\"race_id\"] = i[key_col].astype(str).agg('/'.join,axis=1)\n    \nprint(nyra_start_table[key_col].astype(str).agg('/'.join,axis=1))  ","metadata":{"execution":{"iopub.status.busy":"2022-08-18T14:20:19.026293Z","iopub.execute_input":"2022-08-18T14:20:19.027226Z","iopub.status.idle":"2022-08-18T14:23:29.621359Z","shell.execute_reply.started":"2022-08-18T14:20:19.027171Z","shell.execute_reply":"2022-08-18T14:23:29.619910Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_2019_complete.nunique()","metadata":{"execution":{"iopub.status.busy":"2022-08-18T14:24:22.870959Z","iopub.execute_input":"2022-08-18T14:24:22.871414Z","iopub.status.idle":"2022-08-18T14:24:27.058324Z","shell.execute_reply.started":"2022-08-18T14:24:22.871379Z","shell.execute_reply":"2022-08-18T14:24:27.057109Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_2019_complete.isnull().sum()","metadata":{"execution":{"iopub.status.busy":"2022-08-18T14:24:28.852110Z","iopub.execute_input":"2022-08-18T14:24:28.852534Z","iopub.status.idle":"2022-08-18T14:24:30.482422Z","shell.execute_reply.started":"2022-08-18T14:24:28.852499Z","shell.execute_reply":"2022-08-18T14:24:30.480892Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_2019_complete.describe()","metadata":{"execution":{"iopub.status.busy":"2022-08-18T14:24:32.196675Z","iopub.execute_input":"2022-08-18T14:24:32.197469Z","iopub.status.idle":"2022-08-18T14:24:33.497876Z","shell.execute_reply.started":"2022-08-18T14:24:32.197429Z","shell.execute_reply":"2022-08-18T14:24:33.496717Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_2019_complete.track_id.value_counts()","metadata":{"execution":{"iopub.status.busy":"2022-08-18T14:24:35.045723Z","iopub.execute_input":"2022-08-18T14:24:35.046231Z","iopub.status.idle":"2022-08-18T14:24:35.370572Z","shell.execute_reply.started":"2022-08-18T14:24:35.046185Z","shell.execute_reply":"2022-08-18T14:24:35.369447Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_2019_complete.race_number.value_counts()","metadata":{"execution":{"iopub.status.busy":"2022-08-18T14:24:37.524223Z","iopub.execute_input":"2022-08-18T14:24:37.525450Z","iopub.status.idle":"2022-08-18T14:24:37.579211Z","shell.execute_reply.started":"2022-08-18T14:24:37.525390Z","shell.execute_reply":"2022-08-18T14:24:37.577913Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_2019_complete.program_number.value_counts()","metadata":{"execution":{"iopub.status.busy":"2022-08-18T14:24:39.268801Z","iopub.execute_input":"2022-08-18T14:24:39.269886Z","iopub.status.idle":"2022-08-18T14:24:39.553316Z","shell.execute_reply.started":"2022-08-18T14:24:39.269835Z","shell.execute_reply":"2022-08-18T14:24:39.552134Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_2019_complete['race_date'] = pd.to_datetime(nyra_2019_complete['race_date'])\nnyra_2019_complete['day'], nyra_2019_complete['month'] = nyra_2019_complete['race_date'].dt.day, nyra_2019_complete['race_date'].dt.month","metadata":{"execution":{"iopub.status.busy":"2022-08-18T14:24:41.572552Z","iopub.execute_input":"2022-08-18T14:24:41.573671Z","iopub.status.idle":"2022-08-18T14:24:43.396629Z","shell.execute_reply.started":"2022-08-18T14:24:41.573603Z","shell.execute_reply":"2022-08-18T14:24:43.394996Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_2019_complete.info()","metadata":{"execution":{"iopub.status.busy":"2022-08-18T14:24:45.604901Z","iopub.execute_input":"2022-08-18T14:24:45.605362Z","iopub.status.idle":"2022-08-18T14:24:45.621503Z","shell.execute_reply.started":"2022-08-18T14:24:45.605328Z","shell.execute_reply":"2022-08-18T14:24:45.620327Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import matplotlib.pyplot as plt\nimport seaborn as sns\nimport plotly.express as px","metadata":{"execution":{"iopub.status.busy":"2022-08-18T14:24:47.909185Z","iopub.execute_input":"2022-08-18T14:24:47.910009Z","iopub.status.idle":"2022-08-18T14:24:47.914836Z","shell.execute_reply.started":"2022-08-18T14:24:47.909963Z","shell.execute_reply":"2022-08-18T14:24:47.913733Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#sns.pairplot(nyra_2019_complete, hue = \"track_id\")\n#plt.show()","metadata":{"execution":{"iopub.status.busy":"2022-08-18T14:24:49.796791Z","iopub.execute_input":"2022-08-18T14:24:49.797206Z","iopub.status.idle":"2022-08-18T14:24:49.802427Z","shell.execute_reply.started":"2022-08-18T14:24:49.797172Z","shell.execute_reply":"2022-08-18T14:24:49.800842Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.pairplot(nyra_start_table, hue = \"track_id\")\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-08-18T14:26:33.100636Z","iopub.execute_input":"2022-08-18T14:26:33.101168Z","iopub.status.idle":"2022-08-18T14:26:41.184198Z","shell.execute_reply.started":"2022-08-18T14:26:33.101128Z","shell.execute_reply":"2022-08-18T14:26:41.182745Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.pairplot(nyra_race_table, hue = \"track_id\")\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-08-18T14:26:49.355075Z","iopub.execute_input":"2022-08-18T14:26:49.355514Z","iopub.status.idle":"2022-08-18T14:26:58.684542Z","shell.execute_reply.started":"2022-08-18T14:26:49.355477Z","shell.execute_reply":"2022-08-18T14:26:58.683297Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots(1,1, figsize=(12,6))\n(nyra_start_table.groupby(\"track_id\").race_date.nunique()).plot(kind=\"bar\")\nax.set_ylabel(\"Unique Race Dates\")","metadata":{"execution":{"iopub.status.busy":"2022-08-18T14:27:30.205422Z","iopub.execute_input":"2022-08-18T14:27:30.205900Z","iopub.status.idle":"2022-08-18T14:27:30.410923Z","shell.execute_reply.started":"2022-08-18T14:27:30.205862Z","shell.execute_reply":"2022-08-18T14:27:30.409593Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots(1,1, figsize=(12,6))\n(nyra_start_table.groupby(\"track_id\")[\"race_id\"].nunique()).plot(kind=\"bar\")\nax.set_ylabel(\"Unique Races\")","metadata":{"execution":{"iopub.status.busy":"2022-08-18T14:27:32.458413Z","iopub.execute_input":"2022-08-18T14:27:32.459462Z","iopub.status.idle":"2022-08-18T14:27:32.632240Z","shell.execute_reply.started":"2022-08-18T14:27:32.459409Z","shell.execute_reply":"2022-08-18T14:27:32.630847Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyra_2019_complete.race_number.hist()","metadata":{"execution":{"iopub.status.busy":"2022-08-18T14:27:34.700560Z","iopub.execute_input":"2022-08-18T14:27:34.701034Z","iopub.status.idle":"2022-08-18T14:27:35.048193Z","shell.execute_reply.started":"2022-08-18T14:27:34.700995Z","shell.execute_reply":"2022-08-18T14:27:35.047312Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"color_dict = {'D' : 'blue',\n             'I' : 'yellow',\n             'O' : 'red',\n             'T' : 'orange',\n             'M' : 'grey'}\nplt.figure(figsize = (16, 8))\nplt.style.use(\"dark_background\")\nfor track in ['AQU', 'BEL', 'SAR']:\n    sns.scatterplot(data = nyra_2019_complete[(nyra_2019_complete.track_id == track)], x = \"longitude\", y = \"latitude\", \n                    hue = \"course_type\", \n                    marker='.',\n                   palette=color_dict, )\n\n    plt.title(track)\n    plt.show()","metadata":{"execution":{"iopub.status.busy":"2022-08-18T14:27:49.851036Z","iopub.execute_input":"2022-08-18T14:27:49.851495Z","iopub.status.idle":"2022-08-18T14:30:46.952376Z","shell.execute_reply.started":"2022-08-18T14:27:49.851447Z","shell.execute_reply":"2022-08-18T14:30:46.950994Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}