R help - Feb 2011 - 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]]

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

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

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
> 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.
>
	[[alternative HTML version deleted]]