Melt and cast data table using pattern

Question

The data.table package added a new feature to melt data into multiple columns simultaneously. This is very useful, but I can't figure out how to preserve the "suffix" of the pre-melted variable names. For example:

library(data.table)

# create data table
dt <- data.table(id = seq(3), a_3 = seq(3), a_4 = seq(4, 6), b_3 = seq(7, 9), b_4 = seq(10, 12))

# melt and cast in one step using new feature
m1 <- melt(dt, id.vars='id', measure=patterns("a_", "b_"), value.name=c("a_", "b_"))

Results in the data table:

   id variable a_ b_
1:  1        1  1  7
2:  2        1  2  8
3:  3        1  3  9
4:  1        2  4 10
5:  2        2  5 11
6:  3        2  6 12

This is the "shape" I want, but the variables a_3, a_4, b_3 and b_4 have been indexed 1 and 2. What I want is the variable column to contain 3,3,3,4,4,4, according to the suffixes of the variable names.

I could obviously do this the "old-fashioned" way with melt, strsplit, dcast, but that's kind of cumbersome. I'm hoping for a one-line solution that's still very fast.

Answer 1

We can do this with splitstackshape. It gives the '.time_1' column automatically

library(splitstackshape)
merged.stack(dt, var.stubs=c("a", "b"), sep="_")
#   id .time_1 a  b
#1:  1       3 1  7
#2:  1       4 4 10
#3:  2       3 2  8
#4:  2       4 5 11
#5:  3       3 3  9
#6:  3       4 6 12