Hi group,
I am trying to convert the organization of a data frame so I can do some
correlations between stocks,
I have something like this:
stock.returns <-
data.frame(rbind(c("MSFT","20110301",0.05),c("MSFT","20110302",0.01),c("GOOG","20110301",-0.01),c("GOOG","20110302",0.04)))
colnames(stock.returns) <-
c("Ticker","Date","Return")
stock.returns
Ticker Date Return
1 MSFT 20110301 0.05
2 MSFT 20110302 0.01
3 GOOG 20110301 -0.01
4 GOOG 20110302 0.04
And want to convert it to this:
stock.returns <-
data.frame(rbind(c("20110301",0.05,-0.01),c("20110302",0.01,0.04)))
colnames(stock.returns) <-
c("Date","MSFT","GOOG")
stock.returns
Date MSFT GOOG
1 20110301 0.05 -0.01
2 20110302 0.01 0.04
Can anyone offer any suggestions?
Thanks,
Chris
--
View this message in context:
http://r.789695.n4.nabble.com/Reorganize-data-frame-tp3381929p3381929.html
Sent from the R help mailing list archive at Nabble.com.
require(reshape2) dcast(stock.returns, Date ~ Ticker) The numbers were changed from their original values, but if you originally created the values Return as.numeric they should stay the same On Wednesday, March 16, 2011 at 9:37 AM, chris99 wrote: Hi group,> > I am trying to convert the organization of a data frame so I can do some > correlations between stocks, > > I have something like this: > > stock.returns <- > data.frame(rbind(c("MSFT","20110301",0.05),c("MSFT","20110302",0.01),c("GOOG","20110301",-0.01),c("GOOG","20110302",0.04))) > colnames(stock.returns) <- c("Ticker","Date","Return") > stock.returns > Ticker Date Return > 1 MSFT 20110301 0.05 > 2 MSFT 20110302 0.01 > 3 GOOG 20110301 -0.01 > 4 GOOG 20110302 0.04 > > > And want to convert it to this: > > stock.returns <- > data.frame(rbind(c("20110301",0.05,-0.01),c("20110302",0.01,0.04))) > colnames(stock.returns) <- c("Date","MSFT","GOOG") > stock.returns > Date MSFT GOOG > 1 20110301 0.05 -0.01 > 2 20110302 0.01 0.04 > > > Can anyone offer any suggestions? > > Thanks, > Chris > > -- > View this message in context: http://r.789695.n4.nabble.com/Reorganize-data-frame-tp3381929p3381929.html > Sent from the R help mailing list archive at Nabble.com. > > ______________________________________________ > 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]]
Here's one way:> ans = reshape(stock.returns,idvar='Date',+ varying=list(names(stock.returns)[-1]), + direction='long', + times=names(stock.returns)[-1], + v.names='Return',timevar='Ticker')> rownames(ans) = NULL > ansDate Ticker Return 1 20110301 MSFT 0.05 2 20110302 MSFT 0.01 3 20110301 GOOG -0.01 4 20110302 GOOG 0.04 - Phil Spector Statistical Computing Facility Department of Statistics UC Berkeley spector at stat.berkeley.edu On Wed, 16 Mar 2011, chris99 wrote:> Hi group, > > I am trying to convert the organization of a data frame so I can do some > correlations between stocks, > > I have something like this: > > stock.returns <- > data.frame(rbind(c("MSFT","20110301",0.05),c("MSFT","20110302",0.01),c("GOOG","20110301",-0.01),c("GOOG","20110302",0.04))) > colnames(stock.returns) <- c("Ticker","Date","Return") > stock.returns > Ticker Date Return > 1 MSFT 20110301 0.05 > 2 MSFT 20110302 0.01 > 3 GOOG 20110301 -0.01 > 4 GOOG 20110302 0.04 > > > And want to convert it to this: > > stock.returns <- > data.frame(rbind(c("20110301",0.05,-0.01),c("20110302",0.01,0.04))) > colnames(stock.returns) <- c("Date","MSFT","GOOG") > stock.returns > Date MSFT GOOG > 1 20110301 0.05 -0.01 > 2 20110302 0.01 0.04 > > > Can anyone offer any suggestions? > > Thanks, > Chris > > -- > View this message in context: http://r.789695.n4.nabble.com/Reorganize-data-frame-tp3381929p3381929.html > Sent from the R help mailing list archive at Nabble.com. > > ______________________________________________ > 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. >
Try this: xtabs(Return ~ Date + Ticker, stock.returns) On Wed, Mar 16, 2011 at 11:37 AM, chris99 <cheakes at hotmail.com> wrote:> Hi group, > > I am trying to convert the organization of a data frame so I can do some > correlations between stocks, > > I have something like this: > > stock.returns <- > data.frame(rbind(c("MSFT","20110301",0.05),c("MSFT","20110302",0.01),c("GOOG","20110301",-0.01),c("GOOG","20110302",0.04))) > colnames(stock.returns) <- c("Ticker","Date","Return") > stock.returns > ?Ticker ? ? Date Return > 1 ? MSFT 20110301 ? 0.05 > 2 ? MSFT 20110302 ? 0.01 > 3 ? GOOG 20110301 ?-0.01 > 4 ? GOOG 20110302 ? 0.04 > > > And want to convert it to this: > > stock.returns <- > data.frame(rbind(c("20110301",0.05,-0.01),c("20110302",0.01,0.04))) > colnames(stock.returns) <- c("Date","MSFT","GOOG") > stock.returns > ? ? ?Date MSFT ?GOOG > 1 20110301 0.05 -0.01 > 2 20110302 0.01 ?0.04 > > > Can anyone offer any suggestions? > > Thanks, > Chris > > -- > View this message in context: http://r.789695.n4.nabble.com/Reorganize-data-frame-tp3381929p3381929.html > Sent from the R help mailing list archive at Nabble.com. > > ______________________________________________ > 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. >-- Henrique Dallazuanna Curitiba-Paran?-Brasil 25? 25' 40" S 49? 16' 22" O