extend data with data.table

Question

I have this data.table

library(data.table)

data.table(
  id = c(rep(1, 3), rep(2, 2)),
  begin = c(1, 4, 8, 1, 11),
  end = c(3, 7, 12, 10, 12),
  state = c("A", "B", "A", "B", "A")
)

I would like to have this output :

data.table(
  id = c(1, 2),
  m1 = c("A", "B"),
  m2 = c("A", "B"),
  m3 = c("A", "B"),
  m4 = c("B", "B"),
  m5 = c("B", "B"),
  m6 = c("B", "B"),
  m7 = c("B", "B"),
  m8 = c("A", "B"),
  m9 = c("A", "B"),
  m10 = c("A", "B"),
  m11 = c("A", "A"),
  m12 = c("A", "A")
)

Those who used to do sequence analysis may have recognized that I'm trying to do what seqformat do in the TRaMiNeR package would do, but with higher performance due to use of data.table

Answer 1

One option with data.table would be to melt the dataset after creating a sequence column, then grouped by 'i1', 'id', 'state', get the sequence of first and last 'value', dcast it from 'long' to 'wide'

dt1 <- melt(dt[, i1 := seq_len(.N)], id.vars = c("i1", "id", "state"))[,
      paste0("m", seq(first(value), last(value))), .(i1, id, state)]
dcast(dt1, id ~ V1, value.var = "state")[]
#    id m1 m10 m11 m12 m2 m3 m4 m5 m6 m7 m8 m9
#1:  1  A   A   A   A  A  A  B  B  B  B  A  A
#2:  2  B   B   A   A  B  B  B  B  B  B  B  B

Answer 2

A solution using the tidyverse.

library(tidyverse)
library(data.table)

dat <- data.table(
  id = c(rep(1, 3), rep(2, 2)),
  begin = c(1, 4, 8, 1, 11),
  end = c(3, 7, 12, 10, 12),
  state = c("A", "B", "A", "B", "A")
)

dat2 <- dat %>%
  mutate(Index = map2(begin, end, `:`)) %>%
  unnest() %>%
  mutate(Index = str_c("m", Index)) %>%
  select(id, state, Index) %>%
  spread(Index, state) %>%
  select(id, str_c("m", 1:(ncol(.) - 1)))
dat2
#   id m1 m2 m3 m4 m5 m6 m7 m8 m9 m10 m11 m12
# 1  1  A  A  A  B  B  B  B  A  A   A   A   A
# 2  2  B  B  B  B  B  B  B  B  B   B   A   A