{"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":"import numpy as np\nimport matplotlib.pyplot as plt\nimport seaborn as sns","metadata":{"execution":{"iopub.status.busy":"2022-09-19T16:15:10.346622Z","iopub.execute_input":"2022-09-19T16:15:10.347643Z","iopub.status.idle":"2022-09-19T16:15:10.353045Z","shell.execute_reply.started":"2022-09-19T16:15:10.347589Z","shell.execute_reply":"2022-09-19T16:15:10.352094Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"!pip install pyspark","metadata":{"execution":{"iopub.status.busy":"2022-09-19T16:15:10.354938Z","iopub.execute_input":"2022-09-19T16:15:10.355281Z","iopub.status.idle":"2022-09-19T16:15:21.232056Z","shell.execute_reply.started":"2022-09-19T16:15:10.355251Z","shell.execute_reply":"2022-09-19T16:15:21.230469Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from pyspark.sql import SparkSession","metadata":{"execution":{"iopub.status.busy":"2022-09-19T16:15:21.234668Z","iopub.execute_input":"2022-09-19T16:15:21.235095Z","iopub.status.idle":"2022-09-19T16:15:21.241500Z","shell.execute_reply.started":"2022-09-19T16:15:21.235054Z","shell.execute_reply":"2022-09-19T16:15:21.240160Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"racePath=r'../input/big-data-derby-2022/nyra_race_table.csv'\nstartPath=r'../input/nyra-start-table/nyra_start_table.csv'\ntrackPath=r'../input/big-data-derby-2022/nyra_tracking_table.csv'","metadata":{"execution":{"iopub.status.busy":"2022-09-19T16:15:21.243225Z","iopub.execute_input":"2022-09-19T16:15:21.244277Z","iopub.status.idle":"2022-09-19T16:15:21.253572Z","shell.execute_reply.started":"2022-09-19T16:15:21.244241Z","shell.execute_reply":"2022-09-19T16:15:21.252790Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"spark=SparkSession.builder.appName('research').getOrCreate()","metadata":{"execution":{"iopub.status.busy":"2022-09-19T16:15:21.256683Z","iopub.execute_input":"2022-09-19T16:15:21.257143Z","iopub.status.idle":"2022-09-19T16:15:21.269522Z","shell.execute_reply.started":"2022-09-19T16:15:21.257111Z","shell.execute_reply":"2022-09-19T16:15:21.268258Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"raceData=spark.read.csv(racePath,inferSchema=True,header=True)\nstartData=spark.read.csv(startPath,inferSchema=True,header=True)\ntrackData=spark.read.csv(trackPath,inferSchema=True,header=True)","metadata":{"execution":{"iopub.status.busy":"2022-09-19T16:15:21.271009Z","iopub.execute_input":"2022-09-19T16:15:21.271373Z","iopub.status.idle":"2022-09-19T16:15:29.680911Z","shell.execute_reply.started":"2022-09-19T16:15:21.271324Z","shell.execute_reply":"2022-09-19T16:15:29.679926Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"startData.createOrReplaceTempView(\"start_data\")\nraceData.createOrReplaceTempView(\"race_data\")\ntrackData.createOrReplaceTempView(\"track_data\")\ndims=(15,9)\nsns.set_style('whitegrid')","metadata":{"execution":{"iopub.status.busy":"2022-09-19T16:15:29.681887Z","iopub.execute_input":"2022-09-19T16:15:29.682210Z","iopub.status.idle":"2022-09-19T16:15:29.707079Z","shell.execute_reply.started":"2022-09-19T16:15:29.682181Z","shell.execute_reply":"2022-09-19T16:15:29.705886Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Track Location legend (for all the cells below):\n**AQU** - Aqueduct, **BEL** - Belmont, **SAR** - Saratoga","metadata":{}},{"cell_type":"code","source":"fig,ax=plt.subplots(figsize=(9,9))\nsns.countplot(ax=ax,x=spark.sql(\"select track_id from start_data where position_at_finish=1\").toPandas()[\"track_id\"])\nplt.title(\"Frequency of Track Location for winning horses\")","metadata":{"execution":{"iopub.status.busy":"2022-09-19T16:16:27.923599Z","iopub.execute_input":"2022-09-19T16:16:27.924150Z","iopub.status.idle":"2022-09-19T16:16:28.261782Z","shell.execute_reply.started":"2022-09-19T16:16:27.924107Z","shell.execute_reply":"2022-09-19T16:16:28.260523Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig,ax=plt.subplots(figsize=dims)\nsns.boxplot(data=spark.sql(\"select track_id,weight_carried from start_data where position_at_finish=1\").toPandas(),x=\"weight_carried\",y=\"track_id\")\nplt.title(\"Boxplots of Carry Weight for winning horses\")","metadata":{"execution":{"iopub.status.busy":"2022-09-19T16:15:30.064484Z","iopub.execute_input":"2022-09-19T16:15:30.066056Z","iopub.status.idle":"2022-09-19T16:15:30.415412Z","shell.execute_reply.started":"2022-09-19T16:15:30.066007Z","shell.execute_reply":"2022-09-19T16:15:30.414581Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig,ax=plt.subplots(figsize=dims)\nsns.boxplot(data=spark.sql(\"select track_id,odds from start_data where position_at_finish=1\").toPandas(),x=\"odds\",y=\"track_id\")\nplt.title(\"Boxplots of Odds for winning horses\")","metadata":{"execution":{"iopub.status.busy":"2022-09-19T16:15:30.416885Z","iopub.execute_input":"2022-09-19T16:15:30.417469Z","iopub.status.idle":"2022-09-19T16:15:30.797637Z","shell.execute_reply.started":"2022-09-19T16:15:30.417436Z","shell.execute_reply":"2022-09-19T16:15:30.796472Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Course Type legend:\n**M** - Hurdle, **D** - Dirt, **O** - Outer turf, **I** - Inner turf, **T** - turf","metadata":{}},{"cell_type":"code","source":"fig,ax=plt.subplots(figsize=dims)\nsns.countplot(data=spark.sql(\"select race_data.track_id,course_type from race_data inner join (select track_id, race_date, race_number from start_data where position_at_finish=1) tmp on race_data.track_id=tmp.track_id and race_data.race_date=tmp.race_date and race_data.race_number=tmp.race_number\").toPandas(),x=\"course_type\",hue=\"track_id\")\nplt.title(\"Frequency of Course Types for winning horses\")","metadata":{"execution":{"iopub.status.busy":"2022-09-19T16:15:30.800667Z","iopub.execute_input":"2022-09-19T16:15:30.801213Z","iopub.status.idle":"2022-09-19T16:15:31.331045Z","shell.execute_reply.started":"2022-09-19T16:15:30.801180Z","shell.execute_reply":"2022-09-19T16:15:31.329818Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig,ax=plt.subplots(figsize=dims)\nsns.boxplot(data=spark.sql(\"select race_data.track_id,distance_id from race_data inner join (select track_id, race_date, race_number from start_data where position_at_finish=1) tmp on race_data.track_id=tmp.track_id and race_data.race_date=tmp.race_date and race_data.race_number=tmp.race_number\").toPandas(),x=\"distance_id\",y=\"track_id\")\nplt.title(\"Boxplots of Distance (multiples of 100 furlongs) for winning horses\")","metadata":{"execution":{"iopub.status.busy":"2022-09-19T16:15:31.332928Z","iopub.execute_input":"2022-09-19T16:15:31.333679Z","iopub.status.idle":"2022-09-19T16:15:31.905585Z","shell.execute_reply.started":"2022-09-19T16:15:31.333632Z","shell.execute_reply":"2022-09-19T16:15:31.904434Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Track Condition legend:\n**YL** - Yielding, **FM** - Firm, **SY** - Sloppy, **GD** - Good, **FT** - Fast, **MY** - Muddy, **SF** - Soft.","metadata":{}},{"cell_type":"code","source":"fig,ax=plt.subplots(figsize=dims)\nsns.countplot(data=spark.sql(\"select race_data.track_id,track_condition from race_data inner join (select track_id, race_date, race_number from start_data where position_at_finish=1) tmp on race_data.track_id=tmp.track_id and race_data.race_date=tmp.race_date and race_data.race_number=tmp.race_number\").toPandas(),x=\"track_condition\",hue=\"track_id\")\nplt.title(\"Frequency of Track Condition for winning horses\")","metadata":{"execution":{"iopub.status.busy":"2022-09-19T16:15:31.907168Z","iopub.execute_input":"2022-09-19T16:15:31.907502Z","iopub.status.idle":"2022-09-19T16:15:32.473798Z","shell.execute_reply.started":"2022-09-19T16:15:31.907472Z","shell.execute_reply":"2022-09-19T16:15:32.472851Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig,ax=plt.subplots(figsize=dims)\nsns.boxplot(data=spark.sql(\"select race_data.track_id,run_up_distance from race_data inner join (select track_id, race_date, race_number from start_data where position_at_finish=1) tmp on race_data.track_id=tmp.track_id and race_data.race_date=tmp.race_date and race_data.race_number=tmp.race_number\").toPandas(),x=\"run_up_distance\",y=\"track_id\")\nplt.title(\"Boxplots of Run-up Distance for winning horses\")","metadata":{"execution":{"iopub.status.busy":"2022-09-19T16:15:32.475409Z","iopub.execute_input":"2022-09-19T16:15:32.475759Z","iopub.status.idle":"2022-09-19T16:15:33.216623Z","shell.execute_reply.started":"2022-09-19T16:15:32.475715Z","shell.execute_reply":"2022-09-19T16:15:33.215510Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Race Type legend:\n**STK** - Stakes, **WCL** - Waiver Claiming, **WMC** - Waiver Maiden Claiming, **SST** - Starter Stakes, **SHP** - Starter Handicap, **CLM** - Claiming, **STR** - Starter Allowance, **AOC** - Allowance Optionl Claimer, **SOC** - Starter Optional Claimer, **MCL** - Maiden Claiming, **ALW** - Allowance, **MSW** - Maiden Special Weight.","metadata":{}},{"cell_type":"code","source":"fig,ax=plt.subplots(figsize=dims)\nsns.countplot(data=spark.sql(\"select race_data.track_id,race_type from race_data inner join (select track_id, race_date, race_number from start_data where position_at_finish=1) tmp on race_data.track_id=tmp.track_id and race_data.race_date=tmp.race_date and race_data.race_number=tmp.race_number\").toPandas(),x=\"race_type\",hue=\"track_id\")\nplt.title(\"Frequency of Race Types for winning horses\")","metadata":{"execution":{"iopub.status.busy":"2022-09-19T16:15:33.217894Z","iopub.execute_input":"2022-09-19T16:15:33.218319Z","iopub.status.idle":"2022-09-19T16:15:33.935876Z","shell.execute_reply.started":"2022-09-19T16:15:33.218285Z","shell.execute_reply":"2022-09-19T16:15:33.934936Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## For Purse histogram below:\n#### **1e6** - 1000000\n#### The markings of X-axis are to be understood as: 0, 200000, 400000, 60000, etc. That is, actual marking = (given marking) * 1000000","metadata":{}},{"cell_type":"code","source":"fig,ax=plt.subplots(figsize=dims)\nsns.boxplot(data=spark.sql(\"select race_data.track_id,purse from race_data inner join (select track_id, race_date, race_number from start_data where position_at_finish=1) tmp on race_data.track_id=tmp.track_id and race_data.race_date=tmp.race_date and race_data.race_number=tmp.race_number\").toPandas(),x=\"purse\",y=\"track_id\")\nplt.title(\"Boxplots of Purse for winning horses\")","metadata":{"execution":{"iopub.status.busy":"2022-09-19T16:15:33.937056Z","iopub.execute_input":"2022-09-19T16:15:33.937378Z","iopub.status.idle":"2022-09-19T16:15:34.442490Z","shell.execute_reply.started":"2022-09-19T16:15:33.937349Z","shell.execute_reply":"2022-09-19T16:15:34.441412Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig,ax=plt.subplots(figsize=(18,15))\nsns.scatterplot(data=spark.sql(\"select latitude,longitude from track_data inner join (select track_id, race_date, race_number from start_data where position_at_finish=1) tmp on track_data.track_id=tmp.track_id and track_data.race_date=tmp.race_date and track_data.race_number=tmp.race_number where track_data.track_id='AQU'\").toPandas(),x=\"latitude\",y=\"longitude\")\nplt.title(\"Course path took by winning horses in Aqueduct\")","metadata":{"execution":{"iopub.status.busy":"2022-09-19T17:04:57.968711Z","iopub.execute_input":"2022-09-19T17:04:57.969223Z","iopub.status.idle":"2022-09-19T17:05:23.230057Z","shell.execute_reply.started":"2022-09-19T17:04:57.969187Z","shell.execute_reply":"2022-09-19T17:05:23.228898Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig,ax=plt.subplots(figsize=(18,15))\nsns.scatterplot(data=spark.sql(\"select latitude,longitude from track_data inner join (select track_id, race_date, race_number from start_data where position_at_finish=1) tmp on track_data.track_id=tmp.track_id and track_data.race_date=tmp.race_date and track_data.race_number=tmp.race_number where track_data.track_id='BEL'\").toPandas(),x=\"latitude\",y=\"longitude\")\nplt.title(\"Course path took by winning horses in Belmont\")","metadata":{"execution":{"iopub.status.busy":"2022-09-19T17:06:56.955466Z","iopub.execute_input":"2022-09-19T17:06:56.956034Z","iopub.status.idle":"2022-09-19T17:07:19.741923Z","shell.execute_reply.started":"2022-09-19T17:06:56.955991Z","shell.execute_reply":"2022-09-19T17:07:19.740628Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig,ax=plt.subplots(figsize=(18,15))\nsns.scatterplot(data=spark.sql(\"select latitude,longitude from track_data inner join (select track_id, race_date, race_number from start_data where position_at_finish=1) tmp on track_data.track_id=tmp.track_id and track_data.race_date=tmp.race_date and track_data.race_number=tmp.race_number where track_data.track_id='SAR'\").toPandas(),x=\"latitude\",y=\"longitude\")\nplt.title(\"Course path took by winning horses in Saratoga\")","metadata":{"execution":{"iopub.status.busy":"2022-09-19T17:07:53.231311Z","iopub.execute_input":"2022-09-19T17:07:53.231747Z","iopub.status.idle":"2022-09-19T17:08:09.203104Z","shell.execute_reply.started":"2022-09-19T17:07:53.231700Z","shell.execute_reply":"2022-09-19T17:08:09.201926Z"},"trusted":true},"execution_count":null,"outputs":[]}]}