I like to re-arrange a table (sTable) based on the value of one the rows
(Analyte) as shown below. Blocks of data with different values for Analyte
need to be stacked below each other. Any easy way to do this or any advice
where to look?
Since it may be possible to get this in an earlier stage of the script, I
have added the R script that produces the original table below.
Thanks for any help.
Rene
Original table (sTable):
1 2 3 4 5
6
Analyte "RBV" "RBV" "RBV"
"TBV" "TBV"
"TBV"
Dose "200" "400" "600"
"200" "400"
"600"
AUC.n "4" "4" "4"
"4" "4"
"4"
AUC.mean " 44.023714" " 77.853594"
"113.326952" " 4.657904" "
8.140416" " 12.034377"
...
Re-arranged table:
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 R script to produce the original table:
# 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))
# The real dataset may have NAs in it
df <- data.frame(Subj, Analyte, Dose, AUC)
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(doBy)
sData <- summaryBy(AUC ~ Analyte + Dose, data=df, FUN=myStats)
sTable <- t(sData)
> I like to re-arrange a table (sTable) based on the value of one the rows > (Analyte) as shown below. Blocks of data with different values for Analyte > need to be stacked below each other. Any easy way to do this or any advice > where to look?How about: library(reshape) dfm <- melt(df, m="AUC") cast(dfm, Analyte + result_variable ~ Dose, myStats) # A few other variations cast(dfm, Analyte ~ Dose ~ result_variable, myStats) cast(dfm, Analyte + Dose ~ result_variable, myStats) # See http://had.co.nz/reshape for more documentation Hadley
Thanks for the reply. I applied your suggestions on my example and maybe I am missing something but these are alternate ways to create the original table but not the re-arranged table. The data are not stacked by Analyte. I will take a look at the documentation for this library to check whether other code can do it. Rene -----Original Message----- From: hadley wickham [mailto:h.wickham at gmail.com] Sent: Thursday, February 15, 2007 11:56 AM To: Rene Braeckman Cc: r-help at stat.math.ethz.ch Subject: Re: [R] How to re-arrange data in table?> I like to re-arrange a table (sTable) based on the value of one the > rows > (Analyte) as shown below. Blocks of data with different values for > Analyte need to be stacked below each other. Any easy way to do this > or any advice where to look?How about: library(reshape) dfm <- melt(df, m="AUC") cast(dfm, Analyte + result_variable ~ Dose, myStats) # A few other variations cast(dfm, Analyte ~ Dose ~ result_variable, myStats) cast(dfm, Analyte + Dose ~ result_variable, myStats) # See http://had.co.nz/reshape for more documentation Hadley
On 2/16/07, Rene Braeckman <RMan54 at cox.net> wrote:> Thanks for the reply. I applied your suggestions on my example and maybe I > am missing something but these are alternate ways to create the original > table but not the re-arranged table. The data are not stacked by Analyte. I > will take a look at the documentation for this library to check whether > other code can do it. > ReneThe first example produces:> cast(dfm, Analyte + result_variable ~ Dose, myStats)Analyte result_variable X200 X400 X600 1 RBV n 4.0000000 4.0000000 4.0000000 2 RBV mean 36.3162818 75.9106859 103.3703973 3 RBV SD 6.2482597 21.9670933 16.6163538 4 RBV CV 17.2051197 28.9380777 16.0745767 5 RBV median 34.9785799 70.2915655 96.9303908 6 RBV min 30.5608993 56.5248140 92.2041812 7 RBV max 44.7470678 106.5347986 127.4166264 8 TBV n 4.0000000 4.0000000 4.0000000 9 TBV mean 3.4647015 8.0472022 11.9026746 10 TBV SD 0.8035588 0.1214145 0.1639920 11 TBV CV 23.1927273 1.5087796 1.3777746 12 TBV median 3.3883839 8.0290574 11.9685917 13 TBV min 2.5689998 7.9216424 11.6625081 14 TBV max 4.5130385 8.2090518 12.0110069 Which looks stacked by analyte to me (although not in exactly the same format as your table). Perhaps I misunderstand what you want. Maybe you want:> cast(dfm, result_variable ~ Dose | Analyte, myStats)$RBV result_variable X200 X400 X600 1 n 4.00000 4.00000 4.00000 2 mean 36.31628 75.91069 103.37040 3 SD 6.24826 21.96709 16.61635 4 CV 17.20512 28.93808 16.07458 5 median 34.97858 70.29157 96.93039 6 min 30.56090 56.52481 92.20418 7 max 44.74707 106.53480 127.41663 $TBV result_variable X200 X400 X600 1 n 4.0000000 4.0000000 4.0000000 2 mean 3.4647015 8.0472022 11.9026746 3 SD 0.8035588 0.1214145 0.1639920 4 CV 23.1927273 1.5087796 1.3777746 5 median 3.3883839 8.0290574 11.9685917 6 min 2.5689998 7.9216424 11.6625081 7 max 4.5130385 8.2090518 12.0110069 Hadley