[R] Unflatten a table in R

[Marc Schwartz]

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