Add new rows in R dataframe to fill time gaps

Question

I have a data frame like this:

df <- data.frame(
  id = c(1, 2, 2),
  time_0  = c(2, 3, 6), 
  time_1 = c(4, 5, 8), 
  event = 1, 
  time_end = 10)

df
#>   id time_0 time_1 event time_end
#> 1  1      2      4     1       10
#> 2  2      3      5     1       10
#> 3  2      6      8     1       10

For each id, time_0 is the time event starts, and time_1 is the time event ends. event repeats in id 2. The follow-up time is from 0 to 10. The data frame only includes periods when event occurred. I would like to fill the event free (event = 0) periods during the follow-up time. For example, id 1, event is 0 from 0 (time_0) to 2 (time_1), and again from 4 (time_0) to 10 (`time_1).

The expected data frame like:

df_1 <- data.frame(
  id = c(1, 1, 1, 2, 2, 2, 2, 2),
  time_0  = c(0, 2, 4, 0, 3, 5, 6, 8), 
  time_1 = c(2, 4, 10, 3, 5, 6, 8, 10), 
  event = c(0, 1, 0, 0, 1, 0, 1, 0),  
  time_end = 10)
df_1
#>   id time_0 time_1 event time_end
#> 1  1      0      2     0       10
#> 2  1      2      4     1       10
#> 3  1      4     10     0       10
#> 4  2      0      3     0       10
#> 5  2      3      5     1       10
#> 6  2      5      6     0       10
#> 7  2      6      8     1       10
#> 8  2      8     10     0       10

I have searched SO posts and there are many similar questions but I still have not identified the right one yet. Any suggestions would be appreciated.

user_19198337 · Answer

Try this

m <- data.frame(id = 0 , time_0 = 0, time_1 = 0)
k <- 1
for(i in unique(df$id)){
    x <- sort(unique(c(0 , unlist(subset(df , id == i)[,2:3]) , 10)))
    for(j in 1:(length(x)-1)){
        m[k,] <- c(i , x[j] , x[j+1])
        k <- k + 1
    }
}

ans <- merge(m , df , by = c("id" , "time_0" , "time_1") , all.x = T)

ans$event <- ifelse(is.na(ans$event) , 0 , ans$event)
ans$time_end <- 10
ans

output

  id time_0 time_1 event time_end
1  1      0      2     0       10
2  1      2      4     1       10
3  1      4     10     0       10
4  2      0      3     0       10
5  2      3      5     1       10
6  2      5      6     0       10
7  2      6      8     1       10
8  2      8     10     0       10

Add new rows in R dataframe to fill time gaps

1 Answers