Mike Schumacher
2011-Feb-03 19:41 UTC
[R] R Data Manipulation - Transposing Data by a Given Column, Like User_ID
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 -- Michael Schumacher mike.schumacher@gmail.com [[alternative HTML version deleted]]
Steve Lianoglou
2011-Feb-03 20:52 UTC
[R] R Data Manipulation - Transposing Data by a Given Column, Like User_ID
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") > RESULTYour 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: cbio.mskcc.org/~lianos/contact
Eik Vettorazzi
2011-Feb-03 20:52 UTC
[R] R Data Manipulation - Transposing Data by a Given Column, Like User_ID
Hi Mike reshape will be your friend. reshape(RAW,direction="wide",timevar="SITE",idvar="USER_ID") there is also the 'reshape'-package, which can do some more sophisticated transformations. hth. Am 03.02.2011 20:41, schrieb Mike Schumacher:> 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 > > > >-- Eik Vettorazzi Institut f?r Medizinische Biometrie und Epidemiologie Universit?tsklinikum Hamburg-Eppendorf Martinistr. 52 20246 Hamburg T ++49/40/7410-58243 F ++49/40/7410-57790
Dennis Murphy
2011-Feb-03 22:09 UTC
[R] R Data Manipulation - Transposing Data by a Given Column, Like User_ID
Hi: This also works, except that the result is of class 'table': xtabs(COUNTS ~ USER_ID + SITE, data = RAW) SITE USER_ID SITE1 SITE2 SITE3 1 10 13 22 2 10 12 12 3 13 44 0 4 0 0 99 If you need a data frame, then the earlier responses apply. HTH, Dennis On Thu, Feb 3, 2011 at 11:41 AM, Mike Schumacher <mike.schumacher@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 > > > > > -- > Michael Schumacher > mike.schumacher@gmail.com > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help@r-project.org mailing list > stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide > R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. >[[alternative HTML version deleted]]