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

[Paul Miller]

Hello All,

Tried some more Internet searches and came to the conclusion that one probably does need to create a "timevar" before reshaping from long to wide. Below is some code that creates the "timevar" and transposes the data. 

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$Drug <- as.character(TestData$Drug)
TestData$Drug <- with(TestData, ifelse(Drug == "Gemcitabine", "  Gemcitabine", Drug))
TestData$Drug <- with(TestData, ifelse(Drug == "Erlotinib", " Erlotinib", Drug))
TestData <- with(TestData, TestData[order(Subject,Drug), c("Subject", "Drug")])

require(reshape)
Qualifying_Regimen <- TestData
Qualifying_Regimen$Ordvar <- with(TestData, ave(1:nrow(Qualifying_Regimen), Subject, FUN = seq_along)) 
Qualifying_Regimen <- reshape(Qualifying_Regimen, direction="wide", idvar="Subject", timevar="Ordvar", v.names="Drug")

TestData
Qualifying_Regimen
 
The code for creating the timevar came from a response to an earlier post by Joshua Wiley which can be found at: 

http://tolstoy.newcastle.edu.au/R/e15/help/11/07/0387.html

All in all this works pretty well, and making the "timevar" is easy once you know how. If the gods are listening though, it would be nice if reshape could transpose from long to wide based solely on the order in which observations occur in the data. (Assuming of course that it can't do so already.)

Time for a small confession. I've referred in my posts to alphabetical sorting of my Drug column and to wanting the transposed columns to be in alphabetical order. In my actual data, I added two spaces before the drug "Gemcitabine" and one space before the drug "Erlotinib" so that these drugs would always come first and second in the sort order. Unfortunately, I neglected to mention I had done this and as a result must have caused people some confusion. My apologies for this oversight.

Thanks,

Paul