melt a data.table with a column pattern

Question

I have a data.table that looks like this:

id A1g_hi A2g_hi A3g_hi A4g_hi
1  2      3      4      5
...

I would like to melt this table so that it looks like this:

id time hi
1  1    2
1  2    3
1  3    4
1  4    5
...

I attempted something like this:

melt(dtb, measure.vars = patterns("^A"), value.name = "hi", variable.name="time")

which does not give me what I would like. Do I need to resort to string splitting here or are there native data.table functions that do this?

Answer 1

I can suggest an easy dplyr+tidyr solution.

library(data.table)
library(dplyr)
library(tidyr)

dt <- as.data.table(read.table(text = "id A1g_hi A2g_hi A3g_hi A4g_hi
1  2      3      4      5", header = T))

dt %>% gather(time, hi, -id) %>% mutate(time = extract_numeric(time))

  id time hi
1  1    1  2
2  1    2  3
3  1    3  4
4  1    4  5

Answer 2

I raise my glass to @rawr who apparently understands the base-R reshape-function. For me it is an eternal mystery, despite many efforts at understanding its documentation and many efforts at solving problems with it. Despite my general disdain for the hadleyverse efforts at "simplifying" (but for me obfuscating) R by universal "nonstandardization", I find his invention of the reshape2::melt-function to be a great aid in efficient manipulation.

require(reshape2)
> melt(dat, id.var="id")
  id variable value
1  1   A1g_hi     2
2  1   A2g_hi     3
3  1   A3g_hi     4
4  1   A4g_hi     5
> str(melt(dat, id.var="id"))
'data.frame':   4 obs. of  3 variables:
 $ id      : int  1 1 1 1
 $ variable: Factor w/ 4 levels "A1g_hi","A2g_hi",..: 1 2 3 4
 $ value   : int  2 3 4 5

So:

> dat2[[2]] <- as.numeric(dat2[[2]])
> dat2
  id variable value
1  1        1     2
2  1        2     3
3  1        3     4
4  1        4     5