R data.table melt for multiple measure variables

Question

i want to add an extra column samples in the melted data.table which will be keys for columns D-0-1, D-0-2, D-A-1, D-A-2, D-A-3 in original data.table. So the final melted data will have the following columns chrom pos strand samples measure value. could you pls help?

here is code i'm using

# Create a melted version for AVG, MED and samples
melted_AVG_MED <- melt(consensus_hiQ_CpGs, id.vars = c("chrom", "pos", "strand"), 
                       measure.vars = c("AVG", "MED"), 
                       variable.name = "measure", value.name = "value")

pls see sample data

structure(list(chrom = c("chr16", "chr9", "chr2", "chr2", "chr2", 
"chr5", "chr4", "chr8", "chr7", "chr19"), pos = c(45610432L, 
114912823L, 120609324L, 180012622L, 76370138L, 18336978L, 23044573L, 
88382377L, 89749892L, 55697045L), strand = c("+", "-", "-", "-", 
"-", "-", "-", "-", "+", "-"), `D-0-1` = c(17L, 27L, 20L, 15L, 
23L, 12L, 23L, 17L, 23L, 26L), `D-0-2` = c(8L, 9L, 7L, 11L, 9L, 
9L, 10L, 5L, 6L, 11L), `D-A-1` = c(16L, 23L, 21L, 12L, 17L, 19L, 
18L, 17L, 17L, 16L), `D-A-2` = c(14L, 23L, 23L, 9L, 21L, 4L, 
14L, 20L, 22L, 14L), `D-A-3` = c(11L, 17L, 20L, 17L, 10L, 14L, 
13L, 16L, 10L, 17L), MIN = c(8L, 9L, 7L, 9L, 9L, 4L, 10L, 5L, 
6L, 11L), SUM = c(66, 99, 91, 64, 80, 58, 78, 75, 78, 84), AVG = c(13.2, 
19.8, 18.2, 12.8, 16, 11.6, 15.6, 15, 15.6, 16.8), MED = c(14, 
23, 20, 12, 17, 12, 14, 17, 17, 16)), row.names = c(NA, -10L), class = c("data.table", 
"data.frame"), .internal.selfref = <pointer: 0x1def820>)

Answer 1

Maybe this could help:

library(data.table)

dt_measures <- melt(dt, id.vars = c("chrom", "pos", "strand"), 
                    measure.vars = c("AVG", "MED"), 
                    variable.name = "measure", value.name = "value_measure")

dt_samples <- melt(dt, id.vars = c("chrom", "pos", "strand"), 
                   measure.vars = c("D-0-1", "D-0-2", "D-A-1", "D-A-2", "D-A-3"), 
                   variable.name = "samples", value.name = "value_samples")

dt_samples[dt_measures, on = .(chrom, pos, strand), allow.cartesian = TRUE]

     chrom       pos strand samples value_samples measure value_measure
  1: chr16  45610432      +   D-0-1            17     AVG          13.2
  2: chr16  45610432      +   D-0-2             8     AVG          13.2
  3: chr16  45610432      +   D-A-1            16     AVG          13.2
  4: chr16  45610432      +   D-A-2            14     AVG          13.2
  5: chr16  45610432      +   D-A-3            11     AVG          13.2
  6:  chr9 114912823      -   D-0-1            27     AVG          19.8
  7:  chr9 114912823      -   D-0-2             9     AVG          19.8
  8:  chr9 114912823      -   D-A-1            23     AVG          19.8
  9:  chr9 114912823      -   D-A-2            23     AVG          19.8
 10:  chr9 114912823      -   D-A-3            17     AVG          19.8
 11:  chr2 120609324      -   D-0-1            20     AVG          18.2
 12:  chr2 120609324      -   D-0-2             7     AVG          18.2
 13:  chr2 120609324      -   D-A-1            21     AVG          18.2
 14:  chr2 120609324      -   D-A-2            23     AVG          18.2
 15:  chr2 120609324      -   D-A-3            20     AVG          18.2
 16:  chr2 180012622      -   D-0-1            15     AVG          12.8
 17:  chr2 180012622      -   D-0-2            11     AVG          12.8
 18:  chr2 180012622      -   D-A-1            12     AVG          12.8
 19:  chr2 180012622      -   D-A-2             9     AVG          12.8
 20:  chr2 180012622      -   D-A-3            17     AVG          12.8
 21:  chr2  76370138      -   D-0-1            23     AVG          16.0
 22:  chr2  76370138      -   D-0-2             9     AVG          16.0
 23:  chr2  76370138      -   D-A-1            17     AVG          16.0
 24:  chr2  76370138      -   D-A-2            21     AVG          16.0
 25:  chr2  76370138      -   D-A-3            10     AVG          16.0
 26:  chr5  18336978      -   D-0-1            12     AVG          11.6
 27:  chr5  18336978      -   D-0-2             9     AVG          11.6
 28:  chr5  18336978      -   D-A-1            19     AVG          11.6
 29:  chr5  18336978      -   D-A-2             4     AVG          11.6
 30:  chr5  18336978      -   D-A-3            14     AVG          11.6
 31:  chr4  23044573      -   D-0-1            23     AVG          15.6
 32:  chr4  23044573      -   D-0-2            10     AVG          15.6
 33:  chr4  23044573      -   D-A-1            18     AVG          15.6
 34:  chr4  23044573      -   D-A-2            14     AVG          15.6
 35:  chr4  23044573      -   D-A-3            13     AVG          15.6
 36:  chr8  88382377      -   D-0-1            17     AVG          15.0
 37:  chr8  88382377      -   D-0-2             5     AVG          15.0
 38:  chr8  88382377      -   D-A-1            17     AVG          15.0
 39:  chr8  88382377      -   D-A-2            20     AVG          15.0
 40:  chr8  88382377      -   D-A-3            16     AVG          15.0
 41:  chr7  89749892      +   D-0-1            23     AVG          15.6
 42:  chr7  89749892      +   D-0-2             6     AVG          15.6
 43:  chr7  89749892      +   D-A-1            17     AVG          15.6
 44:  chr7  89749892      +   D-A-2            22     AVG          15.6
 45:  chr7  89749892      +   D-A-3            10     AVG          15.6
 46: chr19  55697045      -   D-0-1            26     AVG          16.8
 47: chr19  55697045      -   D-0-2            11     AVG          16.8
 48: chr19  55697045      -   D-A-1            16     AVG          16.8
 49: chr19  55697045      -   D-A-2            14     AVG          16.8
 50: chr19  55697045      -   D-A-3            17     AVG          16.8
 51: chr16  45610432      +   D-0-1            17     MED          14.0
 52: chr16  45610432      +   D-0-2             8     MED          14.0
 53: chr16  45610432      +   D-A-1            16     MED          14.0
 54: chr16  45610432      +   D-A-2            14     MED          14.0
 55: chr16  45610432      +   D-A-3            11     MED          14.0
 56:  chr9 114912823      -   D-0-1            27     MED          23.0
 57:  chr9 114912823      -   D-0-2             9     MED          23.0
 58:  chr9 114912823      -   D-A-1            23     MED          23.0
 59:  chr9 114912823      -   D-A-2            23     MED          23.0
 60:  chr9 114912823      -   D-A-3            17     MED          23.0
 61:  chr2 120609324      -   D-0-1            20     MED          20.0
 62:  chr2 120609324      -   D-0-2             7     MED          20.0
 63:  chr2 120609324      -   D-A-1            21     MED          20.0
 64:  chr2 120609324      -   D-A-2            23     MED          20.0
 65:  chr2 120609324      -   D-A-3            20     MED          20.0
 66:  chr2 180012622      -   D-0-1            15     MED          12.0
 67:  chr2 180012622      -   D-0-2            11     MED          12.0
 68:  chr2 180012622      -   D-A-1            12     MED          12.0
 69:  chr2 180012622      -   D-A-2             9     MED          12.0
 70:  chr2 180012622      -   D-A-3            17     MED          12.0
 71:  chr2  76370138      -   D-0-1            23     MED          17.0
 72:  chr2  76370138      -   D-0-2             9     MED          17.0
 73:  chr2  76370138      -   D-A-1            17     MED          17.0
 74:  chr2  76370138      -   D-A-2            21     MED          17.0
 75:  chr2  76370138      -   D-A-3            10     MED          17.0
 76:  chr5  18336978      -   D-0-1            12     MED          12.0
 77:  chr5  18336978      -   D-0-2             9     MED          12.0
 78:  chr5  18336978      -   D-A-1            19     MED          12.0
 79:  chr5  18336978      -   D-A-2             4     MED          12.0
 80:  chr5  18336978      -   D-A-3            14     MED          12.0
 81:  chr4  23044573      -   D-0-1            23     MED          14.0
 82:  chr4  23044573      -   D-0-2            10     MED          14.0
 83:  chr4  23044573      -   D-A-1            18     MED          14.0
 84:  chr4  23044573      -   D-A-2            14     MED          14.0
 85:  chr4  23044573      -   D-A-3            13     MED          14.0
 86:  chr8  88382377      -   D-0-1            17     MED          17.0
 87:  chr8  88382377      -   D-0-2             5     MED          17.0
 88:  chr8  88382377      -   D-A-1            17     MED          17.0
 89:  chr8  88382377      -   D-A-2            20     MED          17.0
 90:  chr8  88382377      -   D-A-3            16     MED          17.0
 91:  chr7  89749892      +   D-0-1            23     MED          17.0
 92:  chr7  89749892      +   D-0-2             6     MED          17.0
 93:  chr7  89749892      +   D-A-1            17     MED          17.0
 94:  chr7  89749892      +   D-A-2            22     MED          17.0
 95:  chr7  89749892      +   D-A-3            10     MED          17.0
 96: chr19  55697045      -   D-0-1            26     MED          16.0
 97: chr19  55697045      -   D-0-2            11     MED          16.0
 98: chr19  55697045      -   D-A-1            16     MED          16.0
 99: chr19  55697045      -   D-A-2            14     MED          16.0
100: chr19  55697045      -   D-A-3            17     MED          16.0
     chrom       pos strand samples value_samples measure value_measure