The data.table package data.table added a new function for melt data in several columns at the same time. This is very useful, but I can’t figure out how to preserve the “suffix” of pre-molten variable names. For instance:
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 “form” I want, but the variables a_3 , a_4 , b_3 and b_4 were indexed 1 and 2 . I want the variable column to contain 3,3,3,4,4,4 according to the suffixes of the variable names.
I could do it in the “old fashioned” way with melt , strsplit , dcast , but this is cumbersome. I hope for a one line solution that is still very fast.
source share