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