Dear R Community,
I am attempting to transpose a dataset from rows to columns but am stuck. I
have tried using reshape() with little luck, possibly due to the categorical
nature of the data. For example:
id<-c(1,2,2,3,3,3)
author<-c("j","k","k","l","l","l")
tmt<-c("cbt","act","dbt","act","act","cbt")
alliance<-c("wai","other","calpas","wai","itas","other")
data<-as.data.frame(cbind(id,author,tmt,alliance))
> data
id author tmt alliance
1 1 j cbt wai
2 2 k act other
3 2 k dbt calpas
4 3 l act wai
5 3 l act itas
6 3 l cbt other
I would like the data to be in the following format:
id author tmt.1 tmt.2 alliance.1 alliance.2 alliance.3
1 j cbt NA wai NA NA
2 k act dbt other calpas NA
3 l act act wai itas other
I tried:
> wide <- reshape(data, idvar=c("id","author"),
direction="wide")
Error in `[.data.frame`(data, , timevar) : undefined columns selected
If I specify all columns, it gives the following format (which is not
desired):
wide <- reshape(data, idvar=c("id","author"),
+ direction="wide", *v.names="tmt",
timevar="alliance"*)
> wide
id author tmt.wai tmt.other tmt.calpas tmt.itas
1 1 j cbt <NA> <NA> <NA>
2 2 k <NA> act dbt <NA>
4 3 l act cbt <NA> act
Any help is much appreciated!
AC
[[alternative HTML version deleted]]
AC -
The easiest way I can think of is to create a time variable,
so reshape will know which observation to call 1, 2, or 3.
data$time = unlist(sapply(split(data,data$id),function(x)1:nrow(x)))
or
data$time = unlist(tapply(data$id,data$id,function(x)1:length(x))
Then
reshape(data,idvar=c('id','author'),timevar='time',direction='wide')
should give you what you want.
- Phil Spector
Statistical Computing Facility
Department of Statistics
UC Berkeley
spector at stat.berkeley.edu
On Sun, 4 Oct 2009, AC Del Re wrote:
> Dear R Community,
> I am attempting to transpose a dataset from rows to columns but am stuck. I
> have tried using reshape() with little luck, possibly due to the
categorical
> nature of the data. For example:
>
> id<-c(1,2,2,3,3,3)
>
author<-c("j","k","k","l","l","l")
>
tmt<-c("cbt","act","dbt","act","act","cbt")
>
alliance<-c("wai","other","calpas","wai","itas","other")
> data<-as.data.frame(cbind(id,author,tmt,alliance))
>
>> data
> id author tmt alliance
> 1 1 j cbt wai
> 2 2 k act other
> 3 2 k dbt calpas
> 4 3 l act wai
> 5 3 l act itas
> 6 3 l cbt other
>
> I would like the data to be in the following format:
>
> id author tmt.1 tmt.2 alliance.1 alliance.2 alliance.3
> 1 j cbt NA wai NA NA
> 2 k act dbt other calpas NA
> 3 l act act wai itas other
>
>
> I tried:
>
>> wide <- reshape(data, idvar=c("id","author"),
direction="wide")
>
> Error in `[.data.frame`(data, , timevar) : undefined columns selected
>
> If I specify all columns, it gives the following format (which is not
> desired):
>
> wide <- reshape(data, idvar=c("id","author"),
> + direction="wide", *v.names="tmt",
timevar="alliance"*)
>
>> wide
> id author tmt.wai tmt.other tmt.calpas tmt.itas
> 1 1 j cbt <NA> <NA> <NA>
> 2 2 k <NA> act dbt <NA>
> 4 3 l act cbt <NA> act
>
> Any help is much appreciated!
>
> AC
>
> [[alternative HTML version deleted]]
>
> ______________________________________________
> 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.
>
> > Wonderful, Phil! Your suggestion produced the exact format I desired. > Thank you kindly, >AC Del Re> > > On Sun, Oct 4, 2009 at 2:03 PM, Phil Spector <spector@stat.berkeley.edu>wrote: > >> AC - >> The easiest way I can think of is to create a time variable, >> so reshape will know which observation to call 1, 2, or 3. >> >> data$time = unlist(sapply(split(data,data$id),function(x)1:nrow(x))) >> or >> data$time = unlist(tapply(data$id,data$id,function(x)1:length(x)) >> >> Then >> >> reshape(data,idvar=c('id','author'),timevar='time',direction='wide') >> >> should give you what you want. >> - Phil Spector >> Statistical Computing Facility >> Department of Statistics >> UC Berkeley >> spector@stat.berkeley.edu >> >> >> >> On Sun, 4 Oct 2009, AC Del Re wrote: >> >> Dear R Community, >>> I am attempting to transpose a dataset from rows to columns but am stuck. >>> I >>> have tried using reshape() with little luck, possibly due to the >>> categorical >>> nature of the data. For example: >>> >>> id<-c(1,2,2,3,3,3) >>> author<-c("j","k","k","l","l","l") >>> tmt<-c("cbt","act","dbt","act","act","cbt") >>> alliance<-c("wai","other","calpas","wai","itas","other") >>> data<-as.data.frame(cbind(id,author,tmt,alliance)) >>> >>> data >>>> >>> id author tmt alliance >>> 1 1 j cbt wai >>> 2 2 k act other >>> 3 2 k dbt calpas >>> 4 3 l act wai >>> 5 3 l act itas >>> 6 3 l cbt other >>> >>> I would like the data to be in the following format: >>> >>> id author tmt.1 tmt.2 alliance.1 alliance.2 alliance.3 >>> 1 j cbt NA wai NA NA >>> 2 k act dbt other calpas NA >>> 3 l act act wai itas other >>> >>> >>> I tried: >>> >>> wide <- reshape(data, idvar=c("id","author"), direction="wide") >>>> >>> >>> Error in `[.data.frame`(data, , timevar) : undefined columns selected >>> >>> If I specify all columns, it gives the following format (which is not >>> desired): >>> >>> wide <- reshape(data, idvar=c("id","author"), >>> + direction="wide", *v.names="tmt", timevar="alliance"*) >>> >>> wide >>>> >>> id author tmt.wai tmt.other tmt.calpas tmt.itas >>> 1 1 j cbt <NA> <NA> <NA> >>> 2 2 k <NA> act dbt <NA> >>> 4 3 l act cbt <NA> act >>> >>> Any help is much appreciated! >>> >>> AC >>> >>> [[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]]