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

[Paul Miller]

Hi Michael,

Sorry, my description seems to have been less than adequate. I want my transposed data to look something like:

  Subject      Drug.1     Drug.2    Drug.3
1       5 Gemcitabine  Erlotinib      <NA>
3       6 Gemcitabine  Erlotinib Paclitaxel
6       9 Gemcitabine  Erlotinib      <NA>
8      10 Gemcitabine  Erlotinib Herceptin

This is almost the same as what one gets with:

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

The difference is that Subject 6 has "Gemcitabine, Erlotinib, Paclitaxel" instead of "Gemcitabine, Paclitaxel, Erlotinib". That's what I mean when I say I want the columns in alphabetical order.

Thanks,

Paul


--- On Tue, 3/20/12, R. Michael Weylandt <michael.weylandt at gmail.com> wrote:

> From: R. Michael Weylandt <michael.weylandt at gmail.com>
> Subject: Re: [R] Reshaping data from long to wide without a "timevar"
> To: "Paul Miller" <pjmiller_57 at yahoo.com>
> Cc: r-help at r-project.org
> Received: Tuesday, March 20, 2012, 9:01 AM
> If I understand you right,
> 
> library(reshape2)
> dcast(melt(TestData, id.var = "Subject", measure.var =
> "Drug"), Subject ~ value)
> 
> Michael
> 
> On Tue, Mar 20, 2012 at 9:50 AM, Paul Miller <pjmiller_57 at yahoo.com>
> wrote:
> > 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
> >
> > ______________________________________________
> > R-help at r-project.org
> mailing list
> > https://stat.ethz.ch/mailman/listinfo/r-help
> > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> > and provide commented, minimal, self-contained,
> reproducible code.
>