library(plyr)
# Sample data
set.seed(1)
dat = data.frame(
id = rep(1:3, each = 10),
time = rep(1:10, times = 3),
value = sample(0:3, size = 30, replace = TRUE)
)
dat
## id time value
## 1 1 1 0
## 2 1 2 3
## 3 1 3 2
## 4 1 4 0
## 5 1 5 1
## 6 1 6 0
## 7 1 7 2
## 8 1 8 2
## 9 1 9 1
## 10 1 10 1
## 11 2 1 2
## 12 2 2 2
## 13 2 3 0
## 14 2 4 0
## 15 2 5 0
## 16 2 6 1
## 17 2 7 1
## 18 2 8 1
## 19 2 9 1
## 20 2 10 2
## 21 3 1 0
## 22 3 2 2
## 23 3 3 0
## 24 3 4 0
## 25 3 5 0
## 26 3 6 0
## 27 3 7 1
## 28 3 8 0
## 29 3 9 0
## 30 3 10 1
# Calculate "transitions"
dat = dat[order(dat$id, dat$time), ]
dat = split(dat, dat$id)
dat = lapply(dat, function(x) {x$value_t_minus_1 = c(NA, x$value[-length(x$value)]); x})
dat = do.call(rbind, dat)
dat = dat[complete.cases(dat), ]
dat$transition = paste0(dat$value_t_minus_1, "->", dat$value)
dat$value_t_minus_1 = NULL
dat
## id time value transition
## 1.2 1 2 3 0->3
## 1.3 1 3 2 3->2
## 1.4 1 4 0 2->0
## 1.5 1 5 1 0->1
## 1.6 1 6 0 1->0
## 1.7 1 7 2 0->2
## 1.8 1 8 2 2->2
## 1.9 1 9 1 2->1
## 1.10 1 10 1 1->1
## 2.12 2 2 2 2->2
## 2.13 2 3 0 2->0
## 2.14 2 4 0 0->0
## 2.15 2 5 0 0->0
## 2.16 2 6 1 0->1
## 2.17 2 7 1 1->1
## 2.18 2 8 1 1->1
## 2.19 2 9 1 1->1
## 2.20 2 10 2 1->2
## 3.22 3 2 2 0->2
## 3.23 3 3 0 2->0
## 3.24 3 4 0 0->0
## 3.25 3 5 0 0->0
## 3.26 3 6 0 0->0
## 3.27 3 7 1 0->1
## 3.28 3 8 0 1->0
## 3.29 3 9 0 0->0
## 3.30 3 10 1 0->1
# Aggregate
dat = count(dat[c("id", "transition")])
dat
## id transition freq
## 1 1 0->1 1
## 2 1 0->2 1
## 3 1 0->3 1
## 4 1 1->0 1
## 5 1 1->1 1
## 6 1 2->0 1
## 7 1 2->1 1
## 8 1 2->2 1
## 9 1 3->2 1
## 10 2 0->0 2
## 11 2 0->1 1
## 12 2 1->1 3
## 13 2 1->2 1
## 14 2 2->0 1
## 15 2 2->2 1
## 16 3 0->0 4
## 17 3 0->1 2
## 18 3 0->2 1
## 19 3 1->0 1
## 20 3 2->0 1