Data Import/Clean

library(readxl)
library(dplyr)
library(tidyr)

Faceoff = read_excel("Faceoff Wins and Losses (since 1997-98).xlsx")
Faceoff = Faceoff %>%
  select(c("Team","Season","FOW%"))

Goal_against_strength = read_excel("Goals Against by Strength (since 2009-10).xlsx")
Goal_against_strength = Goal_against_strength %>%
   select(-c("GP","W","L","T","OT","P%","GA/GP")) 

Goal_for_strength = read_excel("Goals For by Strength (since 2009-10).xlsx")
Goal_for_strength = Goal_for_strength %>%
   select(-c("GP","P","GA","GF","W","L","T","OT","P%","GF/GP")) 

Outshoot = read_excel("OutshootOutshot By.xlsx")
Outshoot = Outshoot %>%
  select(c("Team","Season","Shots/GP","SA/GP"))

Penalties = read_excel("Penalties.xlsx")
Penalties = Penalties %>%
  select(c("Team","Season",`Net Pen/60`,`Pen Taken/60`))

Penalty_kill = read_excel("Penalty Kill Time (since 2009-10).xlsx")
Penalty_kill = Penalty_kill %>%
  separate(`TOI 4v5`, c("Min", "Sec"),sep=":") %>%
  mutate(
    TOI_4v5 = as.numeric(Min)*60+as.numeric(Sec)
  ) %>%
  separate(`TOI 3v5`, c("Min", "Sec"),sep=":") %>%
  mutate(
    TOI_3v5 = as.numeric(Min)*60+as.numeric(Sec)
  ) %>%
    select(c("Team","Season","TOI_4v5","TOI_3v5"))


Power_play = read_excel("Power Play Time (since 2009-10).xlsx")
Power_play = Power_play %>%
  separate(`5v4 TOI`, c("Min", "Sec"),sep=":") %>%
  mutate(
    TOI_5v4 = as.numeric(Min)*60+as.numeric(Sec)
  ) %>%
  separate(`5v3 TOI`, c("Min", "Sec"),sep=":") %>%
  mutate(
    TOI_5v3 = as.numeric(Min)*60+as.numeric(Sec)
  ) %>%
    select(c("Team","Season","TOI_5v4","TOI_5v3"))
  
Shot_attempt = read_excel("SAT Counts (5v5, since 2009-10).xlsx")
Shot_attempt = Shot_attempt %>%
  separate(Shots, c("t", "h"),sep=",") %>%
  mutate(
    Shots = as.numeric(t)*1000+as.numeric(h)
  ) %>%
  separate(`SAT For`, c("t", "h"),sep=",") %>%
  mutate(
    SAT_For = as.numeric(t)*1000+as.numeric(h)
  ) %>%
  separate(`SAT Agst`, c("t", "h"),sep=",") %>%
  mutate(
    SAT_Agst = as.numeric(t)*1000+as.numeric(h)
  ) %>%
  separate(`USAT For`, c("t", "h"),sep=",") %>%
  mutate(
    USAT_For = as.numeric(t)*1000+as.numeric(h)
  ) %>%
  separate(`USAT Agst`, c("t", "h"),sep=",") %>%
  mutate(
    USAT_Agst = as.numeric(t)*1000+as.numeric(h)
  ) %>%
  select(-c("GP","t","h"))


Total = left_join(Faceoff, Goal_against_strength, c("Team","Season")) %>%
        left_join(., Goal_for_strength, c("Team","Season")) %>%
        left_join(., Outshoot, c("Team","Season")) %>%
        left_join(., Penalties, c("Team","Season")) %>%
        left_join(., Penalty_kill, c("Team","Season")) %>%
        left_join(., Power_play, c("Team","Season")) %>%
        left_join(., Shot_attempt, c("Team","Season"))

write.csv(Total, "~/Desktop/Git/NHL/nhl_data.csv")