79099968

Date: 2024-10-17 22:08:46
Score: 1
Natty:
Report link

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
Reasons:
  • RegEx Blacklisted phrase (2): urgent
  • Long answer (-1):
  • Has code block (-0.5):
  • Low reputation (0.5):
Posted by: jsumner