---
title: "Big Data Derby 2022"
subtitle: "Data processing"
output:
  html_document:
    toc_depth: 5
    number_sections: true
    df_print: paged
    code_folding: hide
    css: ../input/css-rmd/estilo.css
    code_download: true
---

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE, eval = FALSE, fig.align = "center",
                      fig.width = 5.5, fig.height = 4.5, warning = FALSE,
                      message = FALSE)
```

# Competition

- [Big Data Derby 2022](https://www.kaggle.com/competitions/big-data-derby-2022/overview)

<center>
<img src = "https://github.com/competencias-dataScience/images-derby/blob/main/derby_kaggle.png?raw=true" width = 500/>
</center>

#  Team of collaborators

- [Edimer David Jaramillo](https://www.kaggle.com/edimer)
- [Paola Betancur García](https://www.kaggle.com/pabega)

# Libraries

```{r, eval = TRUE}
library(DT)
library(tidyverse)
library(data.table)
library(fs)
library(lubridate)
library(gganimate)
library(hrbrthemes)
library(geosphere)
library(splines)

# Theme and colors
theme_black <- theme_modern_rc() + theme(
  legend.position = "top",
  strip.text = element_text(colour = 'white', size = 10),
  axis.text.x = element_text(size = 7),
  axis.text.y = element_text(size = 7),
  legend.key.width = unit(0.5, 'cm')
)

colores <-
  c("#00674a",
    "#d8a91d",
    "#ebebeb",
    "#2f8bd2",
    "#c2541d",
    "#ca1b1d")
```

# Functions

```{r, eval = FALSE}
# Function to get the post_time (time of the race)
split_hour <- function(x) {
  len_x = x %>% as.numeric() %>% as.character %>% str_count()
  if (len_x == 3) {
    res1 = strsplit(x, "(?<=.{1})", perl = TRUE)[[1]][[1]]
    res2 = strsplit(x, "(?<=.{1})", perl = TRUE)[[1]][[2]]
    res3 = strsplit(x, "(?<=.{1})", perl = TRUE)[[1]][[3]]
    res_full = str_c(res1, ":", res2, res3)
    
  } else if (len_x == 4) {
    res1 = strsplit(x, "(?<=.{1})", perl = TRUE)[[1]][[1]]
    res2 = strsplit(x, "(?<=.{1})", perl = TRUE)[[1]][[2]]
    res3 = strsplit(x, "(?<=.{1})", perl = TRUE)[[1]][[3]]
    res4 = strsplit(x, "(?<=.{1})", perl = TRUE)[[1]][[4]]
    res_full = str_c(res1, res2, ":", res3, res4)
    
  }
  return(res_full)
}

split_hour <- Vectorize(split_hour)
```

# Import data

```{r}
# ======================================== Data complete

# --- IDs Horse
ids_horse <- read_csv("../input/big-data-derby-2022-global-horse-ids-and-places/horse_ids.csv") %>% 
  select(-c(...1)) %>% 
  rename(race_number = race) %>% 
  mutate(race_number = as.integer(race_number),
         race_date = as.Date(race_date))

# --- Horse name
names_horse <- read_csv("../input/big-data-derby-2022-global-horse-ids-and-places/horse_names.csv") %>% 
  select(-...1)

# --- Data weather
data_weather <-
  read_csv("../input/new-york-city-weather-data-2019/nyc_temperature.csv") %>%
  rename(race_date = date) %>%
  mutate(
    race_date = as.Date(dmy(race_date)),
    precipitation = as.numeric(precipitation),
    new_snow = as.numeric(new_snow),
    snow_depth = as.numeric(snow_depth)
  )

# --- Names of variables
names_complete <-
  c(
    "track_id",
    "race_date",
    "race_number",
    "program_number",
    "trakus_index",
    "latitude",
    "longitude",
    "distance_id",
    "course_type",
    "track_condition",
    "run_up_distance",
    "race_type",
    "purse",
    "post_time",
    "weight_carried",
    "jockey",
    "odds",
    "position_at_finish"
  )

# --- Nyra complete
nyra_2019_complete <-
  read_csv("../input/big-data-derby-2022/nyra_2019_complete.csv") %>%
  set_names(names_complete) %>%
  mutate(across(where(is.character), str_trim)) %>%
  left_join(
    y = ids_horse,
    by = c("track_id", "race_date", "race_number", "program_number")
  ) %>%
  left_join(y = names_horse, by = "horse_id") %>%
  left_join(data_weather, by = "race_date")

gc()

# Export data for easy management: in this case we use the Rds format
write_rds(nyra_2019_complete, file = "./data_full_nyra_idHorse_weather.rds",
          compress = "xz")

gc()

# --- Full data
df_full <- read_rds("./data_full_nyra_idHorse_weather.rds") 
gc()

df_full_final <-
  df_full %>%
  mutate(
    horse_id = as.character(horse_id),
    post_time = split_hour(as.character(post_time)),
    odds = odds / 100,
    distance_id = distance_id / 100,
    new_id = str_c(track_id, "/", race_date, "/", race_number, "/", horse_name)
  ) %>%
  relocate(new_id, everything()) %>%
  arrange(new_id, trakus_index) %>%
  group_by(new_id, horse_id, horse_name) %>%
  mutate(lag_latitude = lag(latitude, n = 1),
         lag_longitude = lag(longitude, n = 1)) %>%
  ungroup()  %>%
  rowwise() %>%
  mutate(
    dist_haversine = distHaversine(
      p1 = c(lag_longitude, lag_latitude),
      p2 = c(longitude, latitude)
    ),
    dist_cosine = distCosine(
      p1 = c(lag_longitude, lag_latitude),
      p2 = c(longitude, latitude)
    ),
    dist_meeus = distMeeus(
      p1 = c(lag_longitude, lag_latitude),
      p2 = c(longitude, latitude)
    ),
    dist_rhumb = distRhumb(
      p1 = c(lag_longitude, lag_latitude),
      p2 = c(longitude, latitude)
    ),
    dist_vincenty_ellips = distVincentyEllipsoid(
      p1 = c(lag_longitude, lag_latitude),
      p2 = c(longitude, latitude)
    ),
    dist_vincenty_sphere = distVincentySphere(
      p1 = c(lag_longitude, lag_latitude),
      p2 = c(longitude, latitude)
    )
  ) %>%
  ungroup() %>%
  mutate(
    speed_haversine = dist_haversine / 0.25,
    speed_cosine = dist_cosine / 0.25,
    speed_meeus = dist_meeus / 0.25,
    speed_rhumb = dist_rhumb / 0.25,
    speed_vincenty_ellips = dist_vincenty_ellips / 0.25,
    speed_vincenty_sphere = dist_vincenty_sphere / 0.25
  ) %>%
  group_by(new_id) %>%
  mutate(
    lag_speed = lag(speed_haversine, n = 1),
    time_relative = trakus_index / max(trakus_index)
  ) %>%
  ungroup() %>%
  mutate(
    acceleration = (speed_haversine - lag_speed) / 0.25,
    type_accel = if_else(
      acceleration > 0,
      true = "+",
      false = if_else(acceleration < 0,
                      true = "-",
                      false = "=")
    ),
    drag_force = 1 / 2 * (1 * 1.29 * 1 * speed_haversine ^ 2),
    power_req = (1 / 2 * (1 * 1.29 * 1 *  speed_haversine ^ 3)) / 1000,
    calories = (power_req * 1000) * 0.239,
    spend_energy = exp(3.02 + (0.0065 * speed_haversine)),
    digestible_energy = ((spend_energy - 13.92) / 0.57) * 0.06
  )

gc()
```

# Test with "Karak"



```{r}
df_full_final %>%
  filter(horse_name == "Karak") %>% 
  group_by(track_id, race_date, race_number, horse_name) %>%
  summarise(
    position = unique(position_at_finish),
    distance = unique(distance_id),
    average_displac = mean(dist_haversine, na.rm = TRUE),
    sd_displac = sd(dist_haversine, na.rm = TRUE),
    median_displac = median(dist_haversine, na.rm = TRUE),
    min_displac = min(dist_haversine, na.rm = TRUE),
    max_displac = max(dist_haversine, na.rm = TRUE),
    average_speed = mean(speed_haversine, na.rm = TRUE),
    median_speed = median(speed_haversine, na.rm = TRUE),
    sd_speed = sd(speed_haversine, na.rm = TRUE),
    min_speed = min(speed_haversine, na.rm = TRUE),
    max_speed = max(speed_haversine, na.rm = TRUE),
    average_acceler = mean(acceleration, na.rm = TRUE),
    median_acceler = median(acceleration, na.rm = TRUE),
    sd_acceler = sd(acceleration, na.rm = TRUE),
    min_acceler = min(acceleration, na.rm = TRUE),
    max_acceler = max(acceleration, na.rm = TRUE),
    rate_minmax_accel = abs(max_acceler) / abs(min_acceler),
    course_type = unique(course_type),
    track_condition = unique(track_condition),
    run_up_distance = unique(run_up_distance),
    race_type = unique(race_type),
    purse = unique(purse),
    post_time = unique(post_time),
    weight_carried = unique(weight_carried),
    n_trakus = n(),
    time_race = n_trakus * 0.25,
    jockey = unique(jockey),
    odds = unique(odds),
    tmax = unique(tmax),
    tmin = unique(tmin),
    tavg = unique(tavg)
  ) %>%
  ungroup() %>%
  arrange(race_date) %>%
  mutate(across(where(is.numeric), round, digits = 2)) %>%
  datatable(
    rownames = FALSE,
    extensions = c('Buttons', 'FixedColumns'),
    options = list(
      dom = 'Bfrtip',
      buttons = c('excel'),
      scrollX = TRUE,
      fixedColumns = list(leftColumns = 3),
      language = list(url = '//cdn.datatables.net/plug-ins/1.10.11/i18n/Spanish.json')
    )
  )
```

- Export data full complete:

```{r}
# Export data for easy management: in this case we use the Rds format
write_rds(df_full_final, file = "./dataFull_speed_forces_energySpend.rds",
          compress = "xz")

gc()
```

# Full data

```{r, eval = TRUE}
data_complete <- read_rds("../input/nyra-full-complete/dataFull_speed_forces_energySpend.rds")
data_complete %>% head()
```

# Test with Karak

```{r, eval = TRUE}
data_complete %>%
  filter(horse_name == "Karak") %>% 
  filter(!is.na(acceleration)) %>% 
  group_by(new_id) %>% 
  mutate(accel_percent = acceleration^2 / sum(acceleration^2),
         accel_acum = cumsum(accel_percent)) %>% 
  ungroup() %>% 
  ggplot(aes(x = trakus_index, y = accel_acum, color = new_id)) +
  geom_line() +
  scale_color_manual(values = colores) +
  labs(x = "Trakucs index", y = "Cumulative acceleration (m/s2)2",
       color = "") +
  theme_black
```