I am working in R, and I would like to create a relative time index for the case when a treatment can turn on and off for an individual. Thus, the index would count upwards from some negative number until 0, when treatment becomes a 1 and will count up as long as treatment remains at 1 before resetting. It simplest to explain what I need using an example.
Suppose I have data that looks like this:
df <- data.frame(id = c(1,1,1,1,1,1,1,1,1,1,1,2,2,2,2), treatment = c(0,0,1,1,0,0,0,1,1,1,0,0,1,1,1))
I would like to get to the following result:
id treatment relative_time
1 1 0 -2
2 1 0 -1
3 1 1 0
4 1 1 1
5 1 0 -3
6 1 0 -2
7 1 0 -1
8 1 1 0
9 1 1 1
10 1 1 2
11 1 0 .
12 2 0 -1
13 2 1 0
14 2 1 1
15 2 1 2
I have seen that the following code works for when you have treatment occurring once per individuals, but sadly I have not been able to find a way to adapt it to my case:
df %>%
group_by(id) %>%
mutate(relative_time = seq_along(treatment) - which(treatment %in% 1))
Any help would be appreciated!
You could use dplyr library to first create a new grouping variable using cumsum() to detect value changes, then sequence each group 0,1,2...(n-1) when treatment is 1, and -n+0, -n+1, ... -1 when treatment is 0,
library(dplyr)
df %>%
mutate(
g = cumsum(treatment != lag(treatment, default = TRUE)),
.by = id
) |>
mutate(
relative_time = seq_along(g) - 1 + ifelse(treatment, 0, -length(g)),
.by = c(id, g)
) |>
select(-g)
output
id treatment relative_time
1 1 0 -2
2 1 0 -1
3 1 1 0
4 1 1 1
5 1 0 -3
6 1 0 -2
7 1 0 -1
8 1 1 0
9 1 1 1
10 1 1 2
11 1 0 -1
12 2 0 -1
13 2 1 0
14 2 1 1
15 2 1 2