[R] R Data Manipulation - Transposing Data by a Given Column, Like User_ID

[Steve Lianoglou]

Hi,

On Thu, Feb 3, 2011 at 2:41 PM, Mike Schumacher
<mike.schumacher at gmail.com> wrote:
> Hello,
>
> I'd like to transpose data to create an analysis-friendly dataframe.  See
> below for an example, I was unable to use t(x) and I couldn't find a
> function with options like PROC TRANSPOSE in SAS.
>
> The ideal solution handles variable quantities of SITE - but beggars can't
> be choosers.  :-)
>
> Thank you in advance,
>
> Mike
>
> ## INPUT DATA
> USER_ID<-c(1,1,1,2,2,2,3,3,4)
> SITE
> <-c("SITE1","SITE2","SITE3","SITE1","SITE2","SITE3","SITE1","SITE2","SITE3")
> COUNTS <-c(10,13,22,10,12,12,13,44,99)
>
> RAW<-data.frame(USER_ID,SITE,COUNTS)
> RAW
>
> #ANSWER SHOULD LOOK LIKE
> a<-c(1,2,3,4)
> b<-c(10,10,13,0)
> c<-c(13,12,44,0)
> d<-c(22,12,0,99)
>
> RESULT<-data.frame(a,b,c,d)
> names(RESULT)<-c("USER_ID","SITE1","SITE2","SITE3")
> RESULT

Your desired RESULT looks like:

R> RESULT
  USER_ID SITE1 SITE2 SITE3
1       1    10    13    22
2       2    10    12    12
3       3    13    44     0
4       4     0     0    99

You can get this with the reshape (or reshape2) package -- an I'm
guessing xtabs() can do this too, but:

R> library(reshape2)
R> dcast(RAW, USER_ID ~ SITE)
Using COUNTS as value column: use value_var to override.
  USER_ID SITE1 SITE2 SITE3
1       1    10    13    22
2       2    10    12    12
3       3    13    44    NA
4       4    NA    NA    99

Learning how to use formulas (the `X ~ Y` stuff) is your friend. I
have to admit though, I'm not as good at it as I'd like to be, myself.

Hope that helps,

-steve

-- 
Steve Lianoglou
Graduate Student: Computational Systems Biology
 | Memorial Sloan-Kettering Cancer Center
 | Weill Medical College of Cornell University
Contact Info: http://cbio.mskcc.org/~lianos/contact