2

I have this data below. I want to melt all No_of.reads cloumns in one column and all _contamination_ columns in another column. So the final dataframe would have diluted_sample, No_of_reads and _contamination_ columns. I tried to do this in two steps, but this would give me repeated observations. What is the right way to do it?

code:

test.dput.melted <- melt(test.dput, id = 1:3, measure = 4:7)
test.dput.melted <- melt(test.dput.melted, id = c(1,4,5), measure = 2:3)

Data:

 test.dput<- structure(list(diluted_sample = c("100%", "95%", "90%", "85%", 
"80%", "75%"), No_of_reads_from_NA12878 = c("15,000,000", "14,250,000", 
"13,500,000", "12,750,000", "12,000,000", "11,250,000"), No_of_reads_from_NA12877 = c("0", 
"750,000", "1,500,000", "2,250,000", "3,000,000", "3,750,000"
), tEst_contamination_of_NA12878 = c("99.60%", "99.10%", "96.80%", 
"92.60%", "88%", "82.60%"), pair_contamination_of_NA12878 = c("100.00%", 
"94.15%", "88.72%", "83.36%", "78.20%", "73.08%"), tEst_contamination_of_NA12877 = c("0.10%", 
"7%", "13.60%", "20.10%", "26.20%", "32.10%"), pair_contamination_of_NA12877 = c("0.10%", 
"5.21%", "10.50%", "15.85%", "20.92%", "26.04%")), .Names = c("diluted_sample", 
"No_of_reads_from_NA12878", "No_of_reads_from_NA12877", "tEst_contamination_of_NA12878", 
"pair_contamination_of_NA12878", "tEst_contamination_of_NA12877", 
"pair_contamination_of_NA12877"), row.names = c(NA, 6L), class = "data.frame")
MAPK
  • 5,635
  • 4
  • 37
  • 88
  • Do you want all possible combinations of reads and contamination? – iod Oct 20 '18 at 18:05
  • @iod I don't want combination, just want the data in long format with reads and contamination columns. – MAPK Oct 20 '18 at 18:06
  • I'm not at my computer right now, but my solution would be to create one df with a melted reads column, and one with a melted polution column, and then do a left_join. – iod Oct 20 '18 at 18:45

2 Answers2

2

Since you tagged data.table and melt

library(magrittr)
library(data.table)
setDT(test.dput)


n.reads <- 
  test.dput[, grep('diluted|reads', names(test.dput)), with = F] %>% 
    melt(1, variable.name = 'Which_No_of_reads',
            value.name    = 'No_of_reads') %>% 
    .[, Which_No_of_reads := gsub('No_of_reads_from_', '', Which_No_of_reads)]

contam <- 
  test.dput[, grep('diluted|contamination', names(test.dput)), with = F] %>% 
    melt(1, variable.name = 'Which_contamination',
            value.name    = '_contamination_') %>% 
    .[, Which_contamination := gsub('contamination_of_', '', Which_contamination)]

cbind(n.reads, contam) %>% 
  .[, unique(names(.)), with = F]

#    diluted_sample Which_No_of_reads No_of_reads Which_contamination _contamination_
#  1:           100%           NA12878  15,000,000        tEst_NA12878          99.60%
#  2:            95%           NA12878  14,250,000        tEst_NA12878          99.10%
#  3:            90%           NA12878  13,500,000        tEst_NA12878          96.80%
#  4:            85%           NA12878  12,750,000        tEst_NA12878          92.60%
#  5:            80%           NA12878  12,000,000        tEst_NA12878             88%
#  6:            75%           NA12878  11,250,000        tEst_NA12878          82.60%
#  7:           100%           NA12877           0        pair_NA12878         100.00%
#  8:            95%           NA12877     750,000        pair_NA12878          94.15%
#  9:            90%           NA12877   1,500,000        pair_NA12878          88.72%
# 10:            85%           NA12877   2,250,000        pair_NA12878          83.36%
# 11:            80%           NA12877   3,000,000        pair_NA12878          78.20%
# 12:            75%           NA12877   3,750,000        pair_NA12878          73.08%
# 13:           100%           NA12878  15,000,000        tEst_NA12877           0.10%
# 14:            95%           NA12878  14,250,000        tEst_NA12877              7%
# 15:            90%           NA12878  13,500,000        tEst_NA12877          13.60%
# 16:            85%           NA12878  12,750,000        tEst_NA12877          20.10%
# 17:            80%           NA12878  12,000,000        tEst_NA12877          26.20%
# 18:            75%           NA12878  11,250,000        tEst_NA12877          32.10%
# 19:           100%           NA12877           0        pair_NA12877           0.10%
# 20:            95%           NA12877     750,000        pair_NA12877           5.21%
# 21:            90%           NA12877   1,500,000        pair_NA12877          10.50%
# 22:            85%           NA12877   2,250,000        pair_NA12877          15.85%
# 23:            80%           NA12877   3,000,000        pair_NA12877          20.92%
# 24:            75%           NA12877   3,750,000        pair_NA12877          26.04%
IceCreamToucan
  • 28,083
  • 2
  • 22
  • 38
  • Not sure why your solution gives me similar result to that from my code above and it did not work. iod's solution however works for me. Thanks anyway! – MAPK Oct 20 '18 at 20:14
1

Using tidyr::gather and dplyr:

test.melted<-gather(test.dput,key="reads_source",value="reads",starts_with("No_of_reads"))
test.melted.NA12878<-test.melted[test.melted$reads_source=="No_of_reads_from_NA12878",] %>% 
  gather(key="contamination_type",value="contamination",
         contains("contamination_of_NA12878"))
test.melted.NA12877<-test.melted[test.melted$reads_source=="No_of_reads_from_NA12877",] %>% 
  gather(key="contamination_type",value="contamination",
         contains("contamination_of_NA12877"))
test.melted.full<-rbind(test.melted.NA12877[,c(-2:-3)],test.melted.NA12878[,c(-2:-3)])

This solution is obviously only good for this specific dataset. If you have more read sites, that could also be managed, using greping and possibly a for-loop.

iod
  • 7,412
  • 2
  • 17
  • 36