[R] How to re-arrange data in table?

[Rene Braeckman]

I found the answer myself. Since the casted object is an array of
data.frames, I am looping though the array to write/append the data.frames
to disk. Matters got a bit more complicated in the melting and casting since
I have more than just AUC as measured values. Got that handled as well. A
full example with two measured variables (AUC and Cmax) is listed below in
case other users like to see.

The only remaining thing is that the headers for each data.frame column are
a combination, formed as "Analyte.Variable_Dose" while I need all 3 elements
(Analyte, Variable and Dose) on different lines. If there is a quick fix,
please let me know. I noticed, for example, that "prettyprint(res$RBV)" adds
separate lines for Variable and Dose.
 
Thanks again for pointing me to your Reshape library. Very powerful.
Rene

# Simulated simplified data
Subj  <- rep(1:4, each=6)
Analyte <- rep(c(rep("RBV",3),rep("TBV",3)),4)
Dose <- rep(c(200,400,600),8)
AUC <- rnorm(24, c(40,80,120,4,8,12), c(8,16,24,0.8,0.16,0.24))
Cmax <- rnorm(24, c(4,8,12,0.4,0.8,1.2), c(0.8,1.6,2.4,0.08,0.016,0.024))

# The real dataset may have NAs in it
df <- data.frame(Subj, Analyte, Dose, AUC, Cmax)

myStats <- function(x) {
    count <- function(x) length(na.omit(x))
    pCV <- function(x) sd(x,na.rm=TRUE) / mean(x,na.rm=TRUE) * 100
    c(
      n = count(x),
      mean = mean(x,na.rm=TRUE),
      SD = sd(x,na.rm=TRUE),
      CV = pCV(x),
      median = median(x,na.rm=TRUE),
      min = min(x,na.rm=TRUE),
      max = max(x,na.rm=TRUE)
      )
}

library(reshape)
# Melting: one measured value per record + several identifiers
dfm <- melt.data.frame(df, measure.var=c("AUC","Cmax"),
                            id.var=c("Analyte","Subj","Dose"),
                            variable_name="Variable")
# Casting: Array of data.frames with statistics
res <- cast(dfm, result_variable ~ Variable + Dose | Analyte, myStats)
# Write to disk
for (i in seq(1,dim(res))) {
    if (i == 1)
        write.table(res[i], file="stest.xls", sep="\t", row.names=FALSE,
col.names=TRUE, qmethod="double")
    else
        write.table(res[i], file="stest.xls", sep="\t", row.names=FALSE,
col.names=TRUE, qmethod="double", append=TRUE)
}

 

-----Original Message-----
From: r-help-bounces at stat.math.ethz.ch
[mailto:r-help-bounces at stat.math.ethz.ch] On Behalf Of Rene Braeckman
Sent: Thursday, February 15, 2007 1:59 PM
To: 'hadley wickham'
Cc: r-help at stat.math.ethz.ch
Subject: Re: [R] How to re-arrange data in table?

The last example in your last message comes really close to the re-arranged
table listed in my original message.

Re-arranged table listed in my original message:

           1             2             3          
Analyte    "RBV"         "RBV"         "RBV"        
Dose       "200"         "400"         "600"        
AUC.n      "4"           "4"           "4"          
AUC.mean   " 44.023714"  " 77.853594"  "113.326952"  
...
Analyte    "TBV"         "TBV"         "TBV"        
Dose       "200"         "400"         "600"        
AUC.n      "4"           "4"           "4"          
AUC.mean   "  4.657904"  "  8.140416"  " 12.034377" 
...

The final step was to write this table to disk as follows:

write.table(myTable, file="stest.xls", sep="\t", row.names=T, col.names=F,
qmethod="double")

Since

cast(dfm, result_variable ~ Dose | Analyte, myStats)

it not a table, I can't use this. Is there another way to write the data to
disk after the cast and get the same results in the file? Or convert the
array into a table?

Thanks again for all your help,
Rene
Irvine, California, USA