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>)
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