converting activity start- and end-time into binned data for multiple groups in R dplyr/tidyr

Question

I have data that looks something like this:

foo <- data.frame(userid = c("a","a","b","b","b"),
                  activity = factor(c("x","y","z","z","x")),
                  st=c(0, 20,   0, 10, 25), # start time
                  et=c(20, 30, 10, 25, 30)) # end time

and I want, for each userid, to convert the activity data into five minute time bins. The result would look something like this:

result <- data.frame(userid = c("a", "b"),
                         x1 = c("x", "z"),
                         x2 = c("x", "z"),
                         x3 = c("x", "z"),
                         x4 = c("x", "z"),
                         x5 = c("y", "z"),
                         x6 = c("y", "x"))

The following approach works, but it is quite cumbersome and very slow. This takes about 15 minutes on my modest-sized dataset.

library(dplyr)
library(tidyr)

lvls <- levels(foo$activity)

time_bin <- function(st, et, act) {
  bins <- seq(0, 30, by=5)
  tb <- as.integer(bins>=st & bins<et)*as.integer(act)
  tb[tb>0] <- lvls[tb]
  data.frame(tb=tb, bins=bins)
}

new_foo <- 
  foo %>% 
  rowwise() %>%
  do(data.frame(., time_bin(.$st, .$et, .$activity))) %>%
  select(-(activity:et)) %>%
  group_by(userid) %>%
  subset(tb>0) %>%
  spread(bins, tb)

Is there a faster or more convenient way of going about this?

Answer 1

You can try:

library(data.table)
library(reshape2)

dt = setDT(foo)[,seq(min(st)+5,max(et),5),.(userid,activity)]
dcast(dt, userid~V1, value.var='activity')
#  userid 5 10 15 20 25 30
#1      a x  x  x  x  y  y
#2      b z  z  z  z  z  x