This is not particularly elegant but here is one way using reshape2::dcast and plyr::rbind.fill.matrix. I expect there is a cleaner way.
This uses each unique value of df$bench and filters for df2$start for that value, makes a table of the filtered data's end value, transforms the table to a matrix and binds those together for each value of bench in the original data.
df <- data.frame(
ids = c(123, 456, 456, 789, 789),
time = c("start", "start", "end", "start", "end"),
bench = c("urgent", "intervene", "benchmark", "watch", "above")
)
df2 <- reshape2::dcast(df, ids ~ time, value.var = "bench")
tab <- do.call(plyr::rbind.fill.matrix, lapply(unique(df$bench), function(i) {
sub <- df2[df2$start == i, ]
if (nrow(sub) > 0 & any(!is.na(sub$end))) {
tab <- table(sub$start, sub$end)
matrix(tab, ncol = ncol(tab), dimnames = dimnames(tab))
} else {
out <- matrix(0)
colnames(out) <- i
out
}
}))
rownames(tab) <- unique(df$bench)
tab
urgent benchmark above
urgent 0 NA NA
intervene NA 1 NA
benchmark NA 0 NA
watch NA NA 1
above NA NA 0
# using larger example data
set.seed(123)
df <- do.call(rbind, lapply(1:100, function(i) {
tms <- times[seq_len(round(runif(1, 1.3, 2)))]
bnch <- sample(benchmarks, length(tms), replace = TRUE)
data.frame(ids = i, time = tms, bench = bnch)
}))
# ... same steps as above
# this time no 0s because the data has more options present for start/end
above benchmark intervene urgent watch
benchmark 3 1 2 1 4
intervene 7 NA 3 4 3
above 3 5 NA 2 2
urgent 5 5 7 3 3
watch 3 2 NA 2 2