-1

I have some data that has been given to me and looks like this: enter image description here

each line is an experiment conducted, controling 5 variables (the first five columns) and gave results (readings) stored in the following (hundreds of) columns X, X1...etc.

I would like to melt my data.frame so that I just have one result variable storing all the readings of all the experiments, keeping the 5 explanatory variables. The problem is that each experiment yielded a different number of readings. So i don't know how to do this.

I dput an extract of my data.frame below:

structure(list(dose = c(0, 0.65, 1.625, 3.25, 6.1), ponte = structure(c(1L, 
1L, 1L, 1L, 1L), .Label = c("P252224", "P312256"), class = "factor"), 
    jour = structure(c(3L, 3L, 3L, 3L, 3L), .Label = c("J1", 
    "J2", "J3"), class = "factor"), normalise = structure(c(1L, 
    1L, 1L, 1L, 1L), .Label = "non", class = "factor"), box = structure(c(1L, 
    1L, 1L, 1L, 1L), .Label = c("FB1", "FB2"), class = "factor"), 
    X = c(4653.625, 3965.312, 7922.779, 6688.122, 9635.559), 
    X.1 = c(4020.349, 4630.506, 8388.648, 7496.008, NA), X.2 = c(NA, 
    4695.361, 6832.299, 8592.608, NA), X.3 = c(NA, NA, 7504.947, 
    9375.783, NA), X.4 = c(NA, NA, 8391.027, 9080.961, NA), X.5 = c(NA, 
    NA, NA, 10213.631, NA)), .Names = c("dose", "ponte", "jour", 
"normalise", "box", "X", "X.1", "X.2", "X.3", "X.4", "X.5"
), row.names = c(NA, -5L), class = "data.frame")
agenis
  • 8,069
  • 5
  • 53
  • 102
  • 1
    `na.omit(data.frame(df[1:5], stack(df[-(1:5)])[1]))` or something like that or `reshape2::melt(df, 1:5,na.rm=TRUE)` – thelatemail Jun 05 '15 at 05:33
  • thanks @thelatemail that's what i was looking for. I had heard of melt function but didn't know how to deal with multiple values or NAs. – agenis Jun 05 '15 at 07:50

1 Answers1

0

This code removes all columns which names starts with X and adds X.All column containing row averages of all X columns. You didn't mention how you want to aggregate non-NA X.n values so replace mean with your desired function:

df=structure(... your structure from example)
df=cbind(df[,setdiff(colnames(df),grep('^X',colnames(df),value = T))], X.All=apply(df[,grep('^X',colnames(df))], MARGIN=1, FUN=function(x) mean(x,na.rm=T)))
cyberj0g
  • 3,707
  • 1
  • 19
  • 34
  • I don't think that is the intended result - melting data normally implies keeping the raw values rather than aggregating them. – thelatemail Jun 05 '15 at 05:53
  • thanks for the answer, that's right i don't want to agregate just to melt the columns and rows – agenis Jun 05 '15 at 07:52