0

I am attempting to use Data.table melt to melt 2 groups of measure columns into two separate value columns. I am able to perform the melt operation, but the problem is that I need to preserve the names of the columns in the newly created variable column.

Reproducible example:

library(data.table)
ids = c(1,2,3,4,5)
a_1 = c(0,1,0,0,1)
a_2 = c(1,1,0,0,1)
b_1 = c(0.5, 0.34, 0.23, 0.10, 0.4)
b_2 = c(0.2, 0.00, 0.32, 0.04, 0.47)
d = data.table(ids, a_1,a_2,b_1,b_2)

m = melt(d, id = "ids", measure.vars = patterns("a_", "b_") )

> m
    ids variable value1 value2
 1:   1        1      0   0.50
 2:   2        1      1   0.34
 3:   3        1      0   0.23
 4:   4        1      0   0.10
 5:   5        1      1   0.40
 6:   1        2      1   0.20
 7:   2        2      1   0.00
 8:   3        2      0   0.32
 9:   4        2      0   0.04
10:   5        2      1   0.47

As you can see, the variable column now has 2 labels: 1,2. What I would like, is for this column to contain the original column names a_1,a_2,b_1,b_2.

Is this possible to accomplish in a single melt operation?

I know it is possible to subset the original table into 2 separate data.tables and then melt each individually before merging, but I perform this type of operation very often, so if I can do it in less keystrokes that would be preferable.

Frank
  • 66,179
  • 8
  • 96
  • 180
Reilstein
  • 1,193
  • 2
  • 11
  • 25
  • Which column name do you want to retain? Variable `1` contains values from both columns `a_1` and `b_1` and similarly variable `2` contains values from both columns `a_2` and `b_2`. Maybe you're looking to modify the variable column after performing the `melt`. – manotheshark Mar 09 '17 at 19:42
  • @manotheshark I was hoping to retain the original column names within the variable column. Instead of `1` it would have the original `a_1` and `a_2` column names so I could tell which column the entry came from. Apparently it is not currently supported by data.table as shown in the link that Frank provided. – Reilstein Mar 09 '17 at 20:52

0 Answers0