Q: R - Create a column with consecutive numbers and reset based on other column using mutate I am trying to use mutate to create a new variable game_plus in my df, which counts the number of days since an event occurred in another column game and resets every time the event occurs. For instance, the variable game in my df is binary and can take on either "Game" or "Training". I was thinking to use a nested ifelse statement to generate the following output:
game         game_plus
Game             0
Training         1
Training         2
Training         3
Game             0
Training         1
Training         2
Game             0
Training         1
Training         2
Training         3
Training         4

I would also like to the opposite for a column game_minus, which essentially counts the number of days before an event, like the following.
game         game_plus      game_minus
Game             0              0
Training         1              3
Training         2              2
Training         3              1
Game             0              0
Training         1              2
Training         2              1 
Game             0              0
Training         1              4
Training         2              3
Training         3              2
Training         4              1

Can anybody assist with this? I know I can use ifelse(game == "Game", 0, but I am struggling to figure out how to incorporate the - prior to or since the event occurred- element into this ifelse statement. Any help would be appreciated!

A: library(tibble)
library(dplyr)

game_tbl <- 
tibble(game = c("Game",rep("Training", 3), "Game",rep("Training", 2),"Game",rep("Training", 4)))


game_tbl  %>% 
  mutate(period = cumsum(game == "Game")) %>% ## which rows belong to one game period 
  group_by(period, game) %>% 
  mutate(game_plus = case_when(game == "Game" ~ 0L , TRUE ~ row_number())) %>%
  group_by(period) %>%  
  mutate(units = n() ) %>% ## how many rows per  game period
  mutate(game_minus =  case_when(game == "Game" ~ 0L, TRUE ~  units - row_number() + 1L )) %>%
  ungroup() %>%
  select(game, game_plus, game_minus)

Basically you need to work with group_by and row_number.
row_number adds the number of each row. In combination with group_by it coutns through the rows for each group. I added the helper variable period to identify all rows that belong to one game-period (from game to last training). Thus, row_number will count rows through each game-period for game == "Game" and game == "Training". I also added the helper varible units to count the rows for each game-period.

A: Here's a solution using data.table the gist here is breaking up the data into sections using n_games. Then data.table has a built in way to essentially get row numbers using .I and so if we break up the data into each section we can take the row numbers in reverse to get the count down. The last thing left is to assign each game day a value of 0 instead of the number of days until the next game.
library(data.table)

dt = data.table(game = c("Game",rep("Training", 3), "Game",rep("Training", 2),"Game",rep("Training", 4)))
## Create an indicator for if a day has a game
dt[,game_ind := ifelse(game == 'Game', 1, 0)]
## Use the indicators to break up the data into groups by taking the cumulative sum of games
dt[,n_games := cumsum(game_ind)]
## .SD[,.I] gets the row number for each group of n_games, rev makes it so that it's 
## counting down instead of up
dt[,game_minus := rev(unlist(.SD[,.I])), by = n_games]
## Set game days to 0
dt[game == 'Game', game_minus := 0]

dt
#>         game game_ind n_games game_minus
#>  1:     Game        1       1          0
#>  2: Training        0       1          3
#>  3: Training        0       1          2
#>  4: Training        0       1          1
#>  5:     Game        1       2          0
#>  6: Training        0       2          2
#>  7: Training        0       2          1
#>  8:     Game        1       3          0
#>  9: Training        0       3          4
#> 10: Training        0       3          3
#> 11: Training        0       3          2
#> 12: Training        0       3          1

## If you want to clean up
dt[,c('game_ind', 'n_games') := NULL]

head(dt)
#>        game game_minus
#> 1:     Game          0
#> 2: Training          3
#> 3: Training          2
#> 4: Training          1
#> 5:     Game          0
#> 6: Training          2

Created on 2020-11-25 by the reprex package (v0.3.0)
