CODEWITHSUNDEEP
rtidyverse
gav
gav

Reputation: 87

Tidyverse: group_by, arrange, and lag across columns

I am working on a projection model for sports where I need to understand in a certain team's most recent game:

  1. Who is their next opponent? (solved)
  2. When is the last time their next opponent played?

reprex that can be used below. Using row 1 as an example, I would need to understand that "a"'s next opponent "e"'s most recent game was game_id_ 3.

game_id_ <- c(1, 1, 2, 2, 3, 3, 4, 4, 5, 5, 6, 6)

game_date_ <- c(rep("2021-01-29", 6), rep("2021-01-30", 6))

team_ <- c("a", "b", "c", "d", "e", "f", "b", "c", "d", "f", "e", "a")

opp_ <- c("b", "a", "d", "c", "f", "e", "c", "b", "f", "d", "a", "e")

df <- data.frame(game_id_, game_date_, team_, opp_)

#Next opponent
df <- df %>% 
  arrange(game_date_, game_id_, team_) %>% 
  group_by(team_) %>% 
  mutate(next_opp = lead(opp_, n = 1L))

If I can provide more details, please let me know.

Upvotes: 2

Views: 204

Answers (1)

akrun
akrun

Reputation: 887028

We can use match to return the corresponding game_id_

library(dplyr)
df %>%
   arrange(game_date_, game_id_, team_) %>%
   group_by(team_) %>%
   mutate(next_opp = lead(opp_, n = 1L)) %>%
   ungroup %>%
   mutate(last_time = game_id_[match(next_opp, opp_)])

Upvotes: 1

Related Questions