{"cells":[{"metadata":{"_uuid":"051d70d956493feee0c6d64651c6a088724dca2a","_execution_state":"idle","trusted":true,"_kg_hide-output":true},"cell_type":"code","source":"library(tidyverse)\nlibrary(lubridate)\n\ndf_game <- read_csv(\"../input/nfl-playing-surface-analytics/PlayList.csv\")\ndf_injury <- read_csv(\"../input/nfl-playing-surface-analytics/InjuryRecord.csv\")","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Generate Date"},{"metadata":{},"cell_type":"markdown","source":"Inspired by this [kernel](https://www.kaggle.com/jpmiller/how-to-adjust-orientation), we can generate the date from `PlayerDay` variable"},{"metadata":{"trusted":true},"cell_type":"code","source":"summary_day <- df_game %>% \n  select(PlayerKey, GameID, PlayerDay:Weather) %>% \n  distinct() %>% \n  group_by(PlayerDay) %>% \n  summarise(game_cnt = n(),\n            player_cnt = n_distinct(PlayerKey)) %>% \n  ungroup() %>% \n  mutate(day_diff = PlayerDay - lag(PlayerDay),\n         player_diff = player_cnt - lag(player_cnt))\nhead(summary_day)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"summary_day %>% \n  ggplot(aes(PlayerDay, game_cnt)) +\n  geom_point() +\n  theme_minimal()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"By taking a look at the data, we can classify season and pre-season from the day like this. I assume `PlayDay` with minus value is pre-season for 2017 and `PlayDay` before day-365 is pre-season for 2018."},{"metadata":{"trusted":true},"cell_type":"code","source":"summary_day <- summary_day %>% \n  mutate(day_category = case_when(PlayerDay < 270 ~ \"S1\",\n                                  PlayerDay >= 270 ~ \"S2\"),\n         is_preseason = case_when(PlayerDay < 0 ~ TRUE,\n                                  day_category == \"S2\" & PlayerDay < 365 ~ TRUE,\n                                  TRUE ~ FALSE),\n         day_category_final = paste0(day_category, \"_\",\n                                     ifelse(is_preseason, \"0\", \"1\"))) \n# check the summary\nsummary_day %>% \n  group_by(day_category, is_preseason) %>% \n  summarise(day_cnt = n(),\n            day_min = min(PlayerDay),\n            day_max = max(PlayerDay),\n            length = day_max - day_min + 1,\n            player_cnt_max = max(player_cnt),\n            player_cnt_avg = mean(player_cnt),\n            player_cnt_median = median(player_cnt)) %>% \n  ungroup() %>% \n  arrange(day_category, desc(is_preseason))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"It is confirmed that there are 116 days in a season. It could be season 2017 and 2018.\n\n- Season 2017: 7 Sept 2017 - 31 Dec 2017\n- Season 2018: 6 Sept 2018 - 30 Dec 2018"},{"metadata":{"trusted":true},"cell_type":"code","source":"# get start_date\nstart_date <- ymd(20170907) + min(summary_day$PlayerDay) - 1\nsummary_day <- summary_day %>% \n  mutate(date_origin = start_date,\n         date_est = date_origin + cumsum(replace_na(day_diff, 0))) %>% \n  select(-date_origin)\n\n# check\nsummary_day %>% \n  filter(PlayerDay %in% c(1,365)) %>% \n  select(PlayerDay, day_category_final, date_est)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Well, the date is matched smoothly"},{"metadata":{"trusted":true},"cell_type":"code","source":"head(summary_day)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# join back\ndf_game <- df_game %>% \n  left_join(summary_day %>% \n               select(PlayerDay, date_est, day_category, is_preseason, day_category_final))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# join with injury data\ndf_game_unique <- df_game %>% \n    count(GameID, date_est, day_category, day_category_final, is_preseason) %>% \n    select(-n) %>% \n    left_join(df_injury %>% \n                  mutate(level = DM_M1 + DM_M7 + DM_M28 + DM_M42) %>% \n                  group_by(GameID) %>% \n                  filter(row_number() == 1) %>% \n                  ungroup() %>% \n                  select(GameID, level, BodyPart)) %>% \n    mutate(level = replace_na(level, 0),\n           is_injury = ifelse(level > 0, TRUE, FALSE))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Data Exploration"},{"metadata":{},"cell_type":"markdown","source":"### Injury Rate by Day"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_game_unique %>% \n    mutate(day = wday(date_est, label = TRUE, week_start = 1)) %>% \n    group_by(day) %>% \n    summarise(game_cnt = n(),\n              game_injury_cnt = sum(is_injury, na.rm = TRUE)) %>% \n    ungroup() %>% \n    mutate(game_injury_pct = game_injury_cnt / game_cnt) %>% \n    arrange(desc(game_injury_pct))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Injury Rate by Season"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_game_unique %>% \n    mutate(day = wday(date_est, label = TRUE, week_start = 1)) %>% \n    group_by(day_category_final) %>% \n    summarise(game_cnt = n(),\n              game_injury_cnt = sum(is_injury, na.rm = TRUE)) %>% \n    ungroup() %>% \n    mutate(game_injury_pct = game_injury_cnt / game_cnt) %>% \n    arrange(desc(game_injury_pct))","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"R","language":"R","name":"ir"},"language_info":{"mimetype":"text/x-r-source","name":"R","pygments_lexer":"r","version":"3.4.2","file_extension":".r","codemirror_mode":"r"}},"nbformat":4,"nbformat_minor":1}