proctor proctor - 1 year ago 63
R Question

Split then stack large dt with numerous columns

PD0_Code PD0_Flock PD0_Tag PD0_Value PD1_Code PD1_Flock PD1_Tag PD1_Value PD2_Code PD2_Flock PD2_Tag PD2_Value

I have a data table that contains 384 columns. I need it transformed from wide into long format. I cannot use melt or split/stack as the column headers are different. Above is the format of column headers I am dealing with. What I need is the large dt split into 4 columns (Code, Flock, Tag, Value) and then stacked on top of each other. I have tried the following and it splits it correctly - now I just need to be able to stack it all together

split(dt2, (seq_along(4)))
x <- cbind(obj.list)

I would really appreciate some help with this. Thanks

Answer Source

We can use melt from data.table which can take multiple patterns in the measure.

melt(setDT(df1), measure = patterns("Code$", "Flock$", "Tag$", "Value$"),
   = c("Code", "Flock", "Tag", "Value"))


df1 <- structure(list(PD0_Code = c(1L, 3L, 5L), PD0_Flock = c(4L, 9L, 
7L), PD0_Tag = c(3L, 4L, 3L), PD0_Value = c(4L, 6L, 4L), PD1_Code = c(2L, 
4L, 6L), PD1_Flock = c(1L, 8L, 9L), PD1_Tag = c(4L, 4L, 3L), 
PD1_Value = c(1L, 5L, 4L), PD2_Code = c(1L, 6L, 5L), PD2_Flock = c(3L, 
9L, 4L), PD2_Tag = c(3L, 4L, 2L), PD2_Value = c(1L, 3L, 7L
)), .Names = c("PD0_Code", "PD0_Flock", "PD0_Tag", "PD0_Value", 
"PD1_Code", "PD1_Flock", "PD1_Tag", "PD1_Value", "PD2_Code", 
"PD2_Flock", "PD2_Tag", "PD2_Value"), class = "data.frame", 
 row.names = c(NA, -3L))