Recode time variable based on the value of another column-CodePudding

I have a time variable which I want to recode based on the change of event in another column. Specifically, if the value in y changes from 1 to 0, the time will start afresh from 0. I also need this refreshing to be done within the same individual (i.e., sorted by id). The total time length can be different across individuals. Here is the original dataset:

df <- data.frame(id = rep(1:3, c(5,6,7)), time = c(seq(from = 0, to = 2000, by = 500), seq(from = 0, to = 2500, by = 500), seq(from = 0, to = 3000, by = 500)), y = c(0,0,1,0,0,0,0,1,1,0,1,0,1,1,0,1,1,0))
df
   id time y
1   1    0 0
2   1  500 0
3   1 1000 1
4   1 1500 0
5   1 2000 0
6   2    0 0
7   2  500 0
8   2 1000 1
9   2 1500 1
10  2 2000 0
11  2 2500 1
12  3    0 0
13  3  500 1
14  3 1000 1
15  3 1500 0
16  3 2000 1
17  3 2500 1
18  3 3000 0

The end result should look like this (notice the time refreshing in row 4, 10, 15, and 18. The lengths of time are also different across the three individuals):

   id time y
1   1    0 0
2   1  500 0
3   1 1000 1
4   1    0 0
5   1  500 0
6   2    0 0
7   2  500 0
8   2 1000 1
9   2 1500 1
10  2    0 0
11  2  500 1
12  3    0 0
13  3  500 1
14  3 1000 1
15  3    0 0
16  3  500 1
17  3 1000 1
18  3    0 0

How to achieve this? Thank you!

CodePudding user response：

dplyr

library(dplyr)
df %>%
  group_by(id) %>%
  mutate(grp = cumsum(y == 0 & lag(y == 1, default = FALSE))) %>%
  group_by(id, grp) %>%
  mutate(time = time - first(time)) %>%
  ungroup()
# # A tibble: 18 x 4
#       id  time     y   grp
#    <int> <dbl> <dbl> <int>
#  1     1     0     0     0
#  2     1   500     0     0
#  3     1  1000     1     0
#  4     1     0     0     1
#  5     1   500     0     1
#  6     2     0     0     0
#  7     2   500     0     0
#  8     2  1000     1     0
#  9     2  1500     1     0
# 10     2     0     0     1
# 11     2   500     1     1
# 12     3     0     0     0
# 13     3   500     1     0
# 14     3  1000     1     0
# 15     3     0     0     1
# 16     3   500     1     1
# 17     3  1000     1     1
# 18     3     0     0     2

base R

df$grp <- ave(df$y, df$id, FUN = function(z) cumsum(c(FALSE, z[-1] == 0 & z[-length(z)] == 1)))
df$time <- ave(df$time, df[,c("id", "grp")], FUN = function(z) z - z[1])

data.table

library(data.table)
as.data.table(df)[, grp := cumsum(y == 0 & shift(y == 1, type = "lag", fill = FALSE)), by = id
  ][, time := time - first(time), by = .(id, grp)][]

CodePudding user response：

Another solution which does not involve the time variable is:

df %>% group_by(id) %>%
  mutate(count = cumsum(y == 0 & lag(y, default = 0) == 1)) %>% 
  group_by(id, count) %>% 
  mutate(time = 500*(row_number()-1))

      id  time     y count
   <int> <dbl> <dbl> <int>
 1     1     0     0     0
 2     1   500     0     0
 3     1  1000     1     0
 4     1     0     0     1
 5     1   500     0     1
 6     2     0     0     0
 7     2   500     0     0
 8     2  1000     1     0
 9     2  1500     1     0
10     2     0     0     1
11     2   500     1     1
12     3     0     0     0
13     3   500     1     0
14     3  1000     1     0
15     3     0     0     1
16     3   500     1     1
17     3  1000     1     1
18     3     0     0     2