[R] Reshaping data from long to wide without a "timevar"

[Paul Miller]

Hello All,

I was wondering if it's possible to reshape data from long to wide in R without using a "timevar". I've pasted some sample data below along with some code. The data are sorted by Subject and Drug. I want to transpose the Drug variable into multiple columns in alphabetical order. 

My data have a variable called "RowNo" that functions almost like a "timevar" but not quite. In Subject 6, Erlotinib has a RowNo value of 3 whereas Paclitaxel has a RowNo value of 2. So if I use reshape as in the first bit of code below, the columns for drug don't transpose in alphabetical order. That is, Paclitaxel appears in Drug.2 and Erlotinib appears in Drug.3 when it should be the other way around.

The next two bits of code represent a couple of other things I've tried. The cast function almost works but unfortunately makes a separate column for each drug (at least the way I'm using it). The unstack function works almost perfectly but to my surprise creates a list instead of a dataframe (which I understand is a different kind of list). Thought it might take a single line of code to convert the former structure to the latter but this appears not to be the case.

So can I get what I want without adding a timevar to my data? And if do need a timevar, what's the best way to add it?

Thanks,

Paul
 
connection <- textConnection("
005 1 Gemcitabine
005 2 Erlotinib
006 1 Gemcitabine
006 3 Erlotinib
006 2 Paclitaxel
009 1 Gemcitabine
009 2 Erlotinib
010 1 Gemcitabine
010 2 Erlotinib
010 3 Herceptin
")

TestData <- data.frame(scan(connection, list(Subject = 0, RowNo = 0, Drug = "")))
TestData$Subject <- as.integer(TestData$Subject)
TestData$RowNo <- as.integer(TestData$RowNo)
TestData$Drug <- as.character(TestData$Drug)

require(reshape)

Transpose <- reshape(TestData, direction="wide", idvar="Subject", timevar="RowNo", v.names="Drug")
Transpose

Transpose <- melt(TestData, id.var="Subject", measure.var="Drug")
Transpose <- cast(Transpose, Subject ~ value)
Transpose

Transpose <- unstack(TestData, Drug ~ Subject)
Transpose