{"metadata":{"kernelspec":{"name":"ir","display_name":"R","language":"R"},"language_info":{"name":"R","codemirror_mode":"r","pygments_lexer":"r","mimetype":"text/x-r-source","file_extension":".r","version":"4.0.5"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# **Aquaduct Finish Line Notebook**","metadata":{}},{"cell_type":"markdown","source":"# Load libraries","metadata":{}},{"cell_type":"code","source":"library(readr)\nlibrary(ggplot2)\nlibrary(dplyr, warn.conflicts = FALSE)\nlibrary(gridExtra)\nlibrary(SOAR)\nlibrary(viridis)\noptions(dplyr.summarise.inform = FALSE)","metadata":{"execution":{"iopub.status.busy":"2022-10-13T01:49:00.781083Z","iopub.execute_input":"2022-10-13T01:49:00.782931Z","iopub.status.idle":"2022-10-13T01:49:01.141516Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig <- function(width, heigth){\n     options(repr.plot.width = width, repr.plot.height = heigth)\n}","metadata":{"execution":{"iopub.status.busy":"2022-10-13T03:19:58.250213Z","iopub.execute_input":"2022-10-13T03:19:58.252232Z","iopub.status.idle":"2022-10-13T03:19:58.270600Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Read in the data","metadata":{}},{"cell_type":"code","source":"race<-read.csv('/kaggle/input/big-data-derby-2022/nyra_race_table.csv')\ndim(race);names(race)\n\nstart<-read.csv('/kaggle/input/big-data-derby-2022/nyra_start_table.csv',header=FALSE)\nnames(start) <- c(\"track_id\",\"race_date\",\"race_number\",\"program_number\",\"weight_carried\", \"jockey\",\"odds\",\"position_at_finish\")\ndim(start);names(start)\n\ntracking<-read.csv('/kaggle/input/big-data-derby-2022/nyra_tracking_table.csv')\ndim(tracking);names(tracking)\n\ncomp <- read.csv(\"/kaggle/input/big-data-derby-2022/nyra_2019_complete.csv\",header=FALSE)\nnames(comp) <- union(union(names(tracking),names(race)),names(start))\nglimpse(comp)\n","metadata":{"execution":{"iopub.status.busy":"2022-10-13T02:15:12.649846Z","iopub.execute_input":"2022-10-13T02:15:12.652235Z","iopub.status.idle":"2022-10-13T02:16:58.115508Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Where is the finish line?\n\n# Look at the outer track at the Aquaduct Race Course\n\n* Create a working data set of the dirt races (i.e. the outer track) at the Aquaduct.\n* Attach a unique number to each race in the working dataset","metadata":{}},{"cell_type":"code","source":"work <- comp %>%\n        filter(track_id == \"AQU\" & course_type==\"D\") %>%\n        select(track_id,course_type,race_date,race_number,distance_id,program_number,\n               trakus_index,position_at_finish,latitude,longitude)\ndim(work)# 1749718       10\nStore(comp)\n\n#attach a unique number to each race in the work dataset using the race_table data\nrace$race_date <- as.character(race$race_date)\nrace_sub <- race %>% \n            filter(track_id == \"AQU\" & course_type==\"D\") %>%\n            select(track_id,race_date,race_number)\ndim(race_sub) #[1] 700   3\nrace_sub$rownum <- 1:700 #700 total races, this will be used to identify the races\nwork <- merge(work,race_sub)\ndim(work) #1749718      11","metadata":{"execution":{"iopub.status.busy":"2022-10-13T02:44:18.132036Z","iopub.execute_input":"2022-10-13T02:44:18.134484Z","iopub.status.idle":"2022-10-13T02:45:14.888121Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Question: Is the finish line the final location of the last place horse?\n## Steps\n* Identify the last place horse for each race.\n* Pull the latitude and longitude for the last place horse from the working dataset.\n  + In 34 races there was no location data for the last place horse at the final trakus_index. The first example of this is the 6th race on 2019-01-04 where the program_number 1 horse did not finish the race. The races where the last place horse did not finish the race were excluded.\n","metadata":{}},{"cell_type":"code","source":"#Identify the last place horse for each race\nlast_finish <- work %>% \n  group_by(track_id,race_date,race_number,rownum) %>%\n  summarize(\n    last=max(position_at_finish),\n    max_index = max(trakus_index))%>%\n  rename(position_at_finish=last,trakus_index=max_index)\n# dim(last_finish) #[1] 700   6\n\n# Pull the latitude and longitude for the last place horse from the working dataset\nlast_finish <- merge(last_finish,work,all.x=TRUE, all.y=FALSE)\n#dim(last_finish);names(last_finish)\n      #why 701? where does extra 1 come from? \n      #filter(last_finish,duplicated(rownum))\n      #filter(last_finish,rownum==432) #one tie for 5th place\n\n#34 rows did not find a match\n#one example is the 6th race on 2019-01-04\n#the program_number=1 horse did not finish race\n  filter(last_finish,is.na(latitude))[1,]\n  r15 <- filter(work,rownum==15)\n  table(r15$program_number)\n#For current purposes, the non-finishers can be excluded\n  last_finish <- filter(last_finish, !is.na(latitude))","metadata":{"execution":{"iopub.status.busy":"2022-10-13T02:46:35.304565Z","iopub.execute_input":"2022-10-13T02:46:35.306108Z","iopub.status.idle":"2022-10-13T02:46:38.073649Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"* Graph the locations of the last place horse at the final trakus_index","metadata":{}},{"cell_type":"code","source":"#Graph the final locations for the last place finisher \noptions(repr.plot.width = 12, repr.plot.height = 12)\n  p1 <- ggplot(last_finish,aes(x=latitude,y=longitude),fig(10,4))+\n    # The next line is show the entire track for reference\n    geom_point(data=filter(work,distance_id==1100),color=\"lightgrey\",size=.5)+\n    geom_point(size=1)+\n    # Add centerpoint of track for reference\n    geom_point(aes(x=40.672542,y=-73.8296269),size=2,color=\"red\")+\n    labs(title=\"Final location of the horse that finished last\",\n         subtitle = \"2019 Aquaduct Dirt Races\")\n  \n  p2 <- ggplot(last_finish,aes(x=latitude,y=longitude))+\n    geom_point(size=.5)\n  grid.arrange(p1,p2,nrow=2)\n    # The last place finisher is not always at precisely the same location\n    # on the track","metadata":{"execution":{"iopub.status.busy":"2022-10-13T03:26:23.708936Z","iopub.execute_input":"2022-10-13T03:26:23.710611Z","iopub.status.idle":"2022-10-13T03:26:24.504331Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"There is more variation in location than would be expected if the final trackus_index occured when the last place horse crossed the finish line.\n\n# Where are the final locations of the other horses?\n\n* Identify the final recorded trakus_index for each race.\n* Pull the latitude and longitude for all of the horses at the final recorded trakus_index from the working dataset.\n* Look at a table of the positions_at_finish.","metadata":{}},{"cell_type":"code","source":"##############\n# Where are the final locations of the other horses?\n#  \n#Identify the final recorded trakus_index for each race\n  temp <- work %>% group_by(track_id,race_date,race_number) %>%\n               summarize(max_index = max(trakus_index)) %>%\n               rename(trakus_index=max_index)\n  #dim(temp)#[1] 700   4\n  \n#Pull all horses at the final trakus_index for each race \n  max_race_trakus <- merge(temp,work,all.x=TRUE,all.y=FALSE) \n  #dim(max_race_trakus)#[1] 5015   15\n  table(max_race_trakus$position_at_finish)","metadata":{"execution":{"iopub.status.busy":"2022-10-13T02:50:42.927418Z","iopub.execute_input":"2022-10-13T02:50:42.929881Z","iopub.status.idle":"2022-10-13T02:50:44.470894Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n+ Note: There can be ties which is why there are 701 horses in first place. An example is the 9th race on 2019-02-09. When there are two 1st place finishers, there is no 2nd place finisher\n","metadata":{}},{"cell_type":"code","source":"## Which race has more than one first place finisher?\n  first <- filter(max_race_trakus,position_at_finish == 1)\n  filter(first,duplicated(rownum)) #169\n  filter(max_race_trakus,rownum==169)\n  ","metadata":{"execution":{"iopub.status.busy":"2022-10-13T02:53:05.362956Z","iopub.execute_input":"2022-10-13T02:53:05.366781Z","iopub.status.idle":"2022-10-13T02:53:05.466021Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Look at the final locations for all finish positions\n  options(repr.plot.width = 12, repr.plot.height = 12)\n  ggplot(max_race_trakus,\n         aes(x=latitude,y=longitude,color=position_at_finish))+\n    geom_point(size=.5)+\n    facet_wrap(vars(position_at_finish))+\n    scale_color_viridis(option=\"C\")+\n    theme(axis.text.x = element_text(angle = 90))+\n    labs(color=\"Finish\",title=\"Final Location by Finish Position\",\n         subtitle = \"2019 Aquaduct Dirt Races\",\n         caption = \"SWH 2022-10-10\")","metadata":{"execution":{"iopub.status.busy":"2022-10-13T03:27:10.769814Z","iopub.execute_input":"2022-10-13T03:27:10.771623Z","iopub.status.idle":"2022-10-13T03:27:12.380672Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Find the races with the fewest horses to identify finish line most precisely\n\n* First, find the number of horses in each race.","metadata":{}},{"cell_type":"code","source":"#Find the race with the fewest horses to identify finish line most precisely\n  temp <- max_race_trakus %>% \n    group_by(track_id,race_date,race_number,rownum) %>%\n    summarize(num_horses = length(unique(program_number))) %>%\n    arrange(num_horses)\n  table(temp$num_horses)","metadata":{"execution":{"iopub.status.busy":"2022-10-13T02:54:38.130675Z","iopub.execute_input":"2022-10-13T02:54:38.132691Z","iopub.status.idle":"2022-10-13T02:54:38.195976Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n* Focus on races with only 4 horses because finishing in the top four generally entitles the owner to a share of the purse, thus it seems that the earliest the tracking would be stopped is when the fourth horse crosses the finish line. It should be stopped nearly immediately after the last horse has finished the finish line.\n","metadata":{}},{"cell_type":"code","source":"#Look at 4th place because finishing in the top four generally entitles\n  # the owner to a share of the purse.\n  #Identify races with only 4 horses\n  races_4horses <- (filter(temp,num_horses==4))$rownum\n  p1 <- ggplot(filter(max_race_trakus,rownum %in% races_4horses),\n         aes(x=latitude,y=longitude,\n             color=as.character(position_at_finish)))+\n    #The next line is show the entire track for reference\n    geom_point(data=filter(work,distance_id==1100),color=\"lightgrey\",size=.5)+\n    geom_point(size=1.5)+\n    theme(axis.text.x = element_text(angle = 90))+\n    labs(color=\"Finish\",title=\"Final Location by Finish Position\",\n         subtitle = \"for 4-horse races\",\n         #caption = \"SWH 2022-10-12\"\n         )\n\n  p2 <- ggplot(filter(max_race_trakus,rownum %in% races_4horses),\n         aes(x=latitude,y=longitude,\n             color=as.character(position_at_finish)))+\n    facet_wrap(vars(position_at_finish))+\n    geom_point(size=1.5)+\n    theme(axis.text.x = element_text(angle = 90))+\n    labs(color=\"Finish\",\n         #title=\"Final Location by Finish Position for 4-horse races\",\n         subtitle = \"2019 Aquaduct Dirt Races\",\n         caption = \"SWH 2022-10-11\"\n         )\n  options(repr.plot.width = 12, repr.plot.height = 12)\n  grid.arrange(p1,p2,nrow=2)","metadata":{"execution":{"iopub.status.busy":"2022-10-13T03:28:11.385805Z","iopub.execute_input":"2022-10-13T03:28:11.387946Z","iopub.status.idle":"2022-10-13T03:28:12.854308Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Find the max latitude of 4th place finishers in 4-horse races\n  \n  max_lat <- filter(max_race_trakus,rownum %in% races_4horses) %>%\n             filter(latitude==max(latitude))\n  max_lat","metadata":{"execution":{"iopub.status.busy":"2022-10-13T02:58:09.724752Z","iopub.execute_input":"2022-10-13T02:58:09.726406Z","iopub.status.idle":"2022-10-13T02:58:09.768550Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n* The location with the maximum latitude for the 4th place finishers in 4-horse races is latitude=40.67125 and longitude = -73.83206. The finish line must be close to this point.\n","metadata":{}}]}