Reshaping wide dataset in interval format

Question

I am working on a "wide" dataset, and now I would like to use a specific package (-msSurv-, for non-parametric multistate models) which requires data in interval form.

My current dataset is characterized by one row for each individual:

dat <- read.table(text = "

   id    cohort   t0    s1     t1     s2      t2     s3    t3
    1      2      0      1     50      2      70     4     100
    2      1      0      2     15      3      100    0     0   

", header=TRUE)

where cohort is a time-fixed covariate, and s1-s3 correspond to the values that a time-varying covariate s = 1,2,3,4 takes over time (they are the distinct states visited by the individual over time). Calendar time is defined by t1-t3, and ranges from 0 to 100 for each individual.

So, for instance, individual 1 stays in state = 1 up to calendar time = 50, then he stays in state = 2 up to time = 70, and finally he stays in state = 4 up to time 100.

What I would like to obtain is a dataset in "interval" form, that is:

id   cohort  t.start    t.stop   start.s   end.s          
1      2        0         50        1        2
1      2       50         70        2        4
1      2       70        100        4        4
2      1        0         15        2        3
2      1       15        100        3        3

I hope the example is sufficiently clear, otherwise please let me know and I will try to further clarify.

How would you automatize this reshaping? Consider that I have a relatively large number of (simulated) individuals, around 1 million.

Thank you very much for any help.

Answer 1

I think I understand. Does this work?

require(data.table)
dt <- data.table(dat, key=c("id", "cohort"))
dt.out <- dt[,  list(t.start=c(t0,t1,t2), t.stop=c(t1,t2,t3), 
                     start.s=c(s1,s2,s3), end.s=c(s2,s3,s3)), 
                     by = c("id", "cohort")]

#    id cohort t.start t.stop start.s end.s
# 1:  1      2       0     50       1     2
# 2:  1      2      50     70       2     4
# 3:  1      2      70    100       4     4
# 4:  2      1       0     15       2     3
# 5:  2      1      15    100       3     0
# 6:  2      1     100      0       0     0

If the output you show is indeed right and is what you require, then you can obtain with two more lines (not the best way probably, but it should nevertheless be fast)

# remove rows where start.s and end.s are both 0
dt.out <- dt.out[, .SD[start.s > 0 | end.s > 0], by=1:nrow(dt.out)]
# replace end.s values with corresponding start.s values where end.s == 0
# it can be easily done with max(start.s, end.s) because end.s >= start.s ALWAYS
dt.out <- dt.out[, end.s := max(start.s, end.s), by=1:nrow(dt.out)]
dt.out[, nrow:=NULL]

> dt.out
#    id cohort t.start t.stop start.s end.s
# 1:  1      2       0     50       1     2
# 2:  1      2      50     70       2     4
# 3:  1      2      70    100       4     4
# 4:  2      1       0     15       2     3
# 5:  2      1      15    100       3     3