on 11/13/2008 09:49 AM Brigid Mooney wrote: > Hi All, > > I'm pretty new to R, so would really appreciate it if someone could point me > in the right direction on this problem. > > I am trying to "unflatten" a table in R, and can't seem to find a function > or method to complete this task, (hopefully efficiently). > > My data table is full of historical stock-market data. Daily, each > ticker-symbol has four data points: open, close, high, and low. > > Right now the table looks like the following. (Note, each ticker symbol has > a unique numeric 'SymbolID'. > > SymbolID MarketDate Open Close High Low > 1 4 11/3/2008 1.5790 1.5788 1.5790 1.5788 > 2 4 11/4/2008 1.5891 1.5892 1.5892 1.5891 > 3 4 11/5/2008 1.5937 1.5931 1.5937 1.5931 > 4 4 11/6/2008 1.5727 1.5727 1.5727 1.5727 > 5 4 11/7/2008 1.5673 1.5669 1.5673 1.5669 > 6 5 11/3/2008 0.8433 0.8435 0.8435 0.8433 > 7 5 11/4/2008 0.8672 0.8672 0.8672 0.8672 > 8 5 11/5/2008 0.8597 0.8594 0.8597 0.8594 > 9 5 11/6/2008 0.8412 0.8410 0.8412 0.8410 > 10 5 11/7/2008 0.8407 0.8409 0.8411 0.8407 > ... > > I'm envisioning a solution with a two-way lookup, something like: > > SymbolID 11/3/2008 11/4/2008 11/5/2008 ... > 4 (open, close, high,low) > 5 > ... > where each entry in the table is actually a vector of the four points for > that symbol and date. > > or even something like > > SymbolID 11/3/2008-open 11/3/2008-close 11/3/2008-high 11/3/2008-low > 11/4/2008-open ... > 4 ... > where in this case, each entry would just be the appropriate numeric entry > from above. > > > Again, any help or suggestions on this one is greatly appreciated... > > Thanks!
One potential approach is to use reshape(): > reshape(DF, timevar = "MarketDate", idvar = "SymbolID", direction = "wide", v.names = c("Open", "Close", "High", "Low")) SymbolID Open.11/3/2008 Close.11/3/2008 High.11/3/2008 Low.11/3/2008 1 4 1.5790 1.5788 1.5790 1.5788 6 5 0.8433 0.8435 0.8435 0.8433 Open.11/4/2008 Close.11/4/2008 High.11/4/2008 Low.11/4/2008 1 1.5891 1.5892 1.5892 1.5891 6 0.8672 0.8672 0.8672 0.8672 Open.11/5/2008 Close.11/5/2008 High.11/5/2008 Low.11/5/2008 1 1.5937 1.5931 1.5937 1.5931 6 0.8597 0.8594 0.8597 0.8594 Open.11/6/2008 Close.11/6/2008 High.11/6/2008 Low.11/6/2008 1 1.5727 1.5727 1.5727 1.5727 6 0.8412 0.8410 0.8412 0.8410 Open.11/7/2008 Close.11/7/2008 High.11/7/2008 Low.11/7/2008 1 1.5673 1.5669 1.5673 1.5669 6 0.8407 0.8409 0.8411 0.8407 See ?reshape for more information. There is also the 'reshape' package by Hadley Wickham (http://had.co.nz/reshape/). HTH, Marc Schwartz ______________________________________________ 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.