R help - May 2008 - reshape a wide data frame from wide to a long format with metadata columns

Hello R users and developers,
I have a general question about reshaping a wide data frame using the
"reshape" command. I have a data frame consisting of 108 columns that
I would like to convert to a long table and remove the metadata
(embedded in the column names of the wide table) to new metadata
columns in the long format.

#example data frame. NOTE column names contain metadata::
mediacont.21.MC1A contains  treatment=mediacont, time=21, rep=MC1A

data1<-data.frame(cbind(name=0:10,
mediacont.21.MC1A=10:20,mediatreat.20.MD1A=30:40,
treat.20.T2C=50:60,treat.20.T2A=70:80,condtp.20.C2A=90:100,condtp.20.C2B=100:110,
mediacont.21.MC1B=120:130, mediacont.21.MC1B=130:140))

#example of reshape code that works, this creates a data frame with
single metadata column and unique ID column. I could use this and
create a loop to write metadata columns based on the metadata in the
time column.  However if I understand from reading the reshape command
it can create new metadata columns based on the names of the columns
in wide format.  I  used the "." as a separator between metadata.

data2<- reshape(data1, idvar="name", direction="long",
varying=c(names(data1[-1])), v.names="intensity",
times=names(data1[-1]),sep=".")


## this code does not work, but I think I need something like this.
data3<- reshape(data1, idvar="name", direction="long",
varying=c(names(data1[-1])),sep=".",
v.names=list(c("treat","time","rep",
"intensity")))


Could someone please offer me some scripting advise on how to reshape
the dataframe from wide
into long format and split the metadata out of the column names.
sep="."

I am trying to create a table with a header like this
"name", "treat", "time", "rep",
"original column name", "intensity"

Thank you,
Alan

Hi Alan,
> I have a general question about reshaping a wide data frame using the
> "reshape" command. I have a data frame consisting of 108 columns
that
> I would like to convert to a long table and remove the metadata
> (embedded in the column names of the wide table) to new metadata
> columns in the long format.
You could try using the reshape _package_ (http://had.co.nz/reshape) to do this:

data1 <- data.frame(
  name = 0:10,
  mediacont.21.MC1A = 10:20,
  mediatreat.20.MD1A = 30:40,
  treat.20.T2C = 50:60,
  treat.20.T2A = 70:80,
  condtp.20.C2A = 90:100,
  condtp.20.C2B = 100:110,
  mediacont.21.MC1B = 120:130
)

dm <- melt(data1, id = "name")
head(dm)
ids <- colsplit(dm$variable, "\\.",
c("treatment","time","rep"))
dm <- cbind(dm, ids)
dm$variable <- NULL
dm$time <- as.numeric(dm$time)

It's then easy to reshape and aggregate as required:

cast(dm, treatment ~ time, mean)
cast(dm, ... ~ treatment)

Hadley

-- 
http://had.co.nz/