This is a bit verbose, but it works. (I'd probably have formatted the data differently in the beginning, a column for Home/Away and a match id, would simplify the tidying)
library(tidyverse)
game_date <- as.Date(
c(
'2014-10-01',
'2014-10-02',
'2014-10-02',
'2014-10-03',
'2014-10-03',
'2014-10-03',
'2014-10-04',
'2014-10-04',
'2014-10-04'
)
)
t1 <- c('LDN', 'PBO', 'BAR',
'BAR', 'BEL', 'LDN',
'BAR', 'PBO', 'GUE')
t2 <- c('GUE', 'BEL', 'SUD',
'GUE', 'PBO', 'KIT',
'LDN', 'NB', 'BEL')
df <- data.frame(game_date, t1, t2)
library(sjmisc)
df <- df %>%
pivot_longer(cols = c(t1, t2),
names_to = "t1_t2",
values_to = "teams") %>%
group_by(teams) %>%
arrange(game_date) %>%
mutate(
status = case_when (
lag(game_date, 1) == game_date - 1 &
lag(game_date, 2) == game_date - 2 ~ "tired",
lag(game_date, 1) == game_date - 1 ~ "fatigue",
TRUE ~ "rested"
)
) %>%
ungroup() %>%
mutate(
t1_status = case_when(t1_t2 == "t1" ~ status,
TRUE ~ NA_character_),
t2_status = case_when(t1_t2 == "t2" ~ status,
TRUE ~ NA_character_),
t1 = case_when(t1_t2 == "t1" ~ teams,
TRUE ~ NA_character_),
t2 = case_when(t1_t2 == "t2" ~ teams,
TRUE ~ NA_character_)
) %>%
mutate(
t1 = case_when (is_even(row_number()) ~ lag(t1, 1),
is_odd(row_number()) ~ t1),
t2 = case_when (is_odd(row_number()) ~ lead(t2, 1),
is_even(row_number()) ~ t2),
t1_status = case_when (
is_even(row_number()) ~ lag(t1_status, 1),
is_odd(row_number()) ~ t1_status
),
t2_status = case_when (
is_odd(row_number()) ~ lead(t2_status, 1),
is_even(row_number()) ~ t2_status
)
) %>%
select(game_date, t1, t2, t1_status, t2_status) %>%
distinct()