merge zoo with many entries per date with lagged self using ticker
On Sat, Nov 13, 2010 at 10:03 PM, richard.c.herron at gmail.com
<richard.c.herron at gmail.com> wrote:
On Sat, Nov 13, 2010 at 16:38, Gabor Grothendieck <ggrothendieck at gmail.com> wrote:
On Sat, Nov 13, 2010 at 3:49 PM, richard.c.herron at gmail.com <richard.c.herron at gmail.com> wrote:
I have a zoo with monthly entries. For each month there are many entries, but only one entry per ticker. To to track the performance of decile portfolios I want to merge each entry with future dates of itself (i.e., lag). For example, here's AAPL
head(temp[which(temp$symbol=="AAPL1C"),])
? ? ? symbol oi ? ? ? ? ?ret ? ? ? ? ? decile 200806 AAPL1C ?8710943.20 -1.129018e-01 10 200807 AAPL1C ?7540246.46 -2.437297e-03 10 200808 AAPL1C 11366863.59 ?1.703864e-01 10 200809 AAPL1C ?9500119.05 -3.689558e-01 10 200810 AAPL1C ? 114082.48 -4.406700e-02 ?1 200811 AAPL1C ? 723159.24 -1.437163e-01 ?6 and it's future self
head(temp.lag[which(temp.lag$symbol=="AAPL1C"),])
? ? ? symbol oi ? ? ? ? ?ret ? ? ? ? ? decile 200806 AAPL1C ?7540246.46 -2.437297e-03 10 200807 AAPL1C 11366863.59 ?1.703864e-01 10 200808 AAPL1C ?9500119.05 -3.689558e-01 10 200809 AAPL1C ? 114082.48 -4.406700e-02 ?1 200810 AAPL1C ? 723159.24 -1.437163e-01 ?6 200811 AAPL1C ?1006345.25 -1.005402e-01 ?8 When I try to merge these two, I get the warning (of course) that I can't merge zoos with duplicate dates. Is there a way to merge on the "symbol" column, too? I could lag, convert both back to dataframe, join, then go back to zoo. Is there a better way?
Some clarification would be useful. ?What is the output that is desired? -- Statistics & Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com
Good point. Here's what the desired output looks like. Here .x are the current values and .y are the next period values. Here .y are one month ahead, but I'm interested in everything from -12 to +12 (thus my interest in the slick way to do it). This produces the desired output, but is tedious:
temp.df <- cbind(index(temp), as.data.frame(temp))
temp.lag.df <- cbind(index(temp.lag), as.data.frame(temp.lag))
temp.join.df <- merge(temp.df, temp.lag.df, by=c("date", "symbol"))
temp.join.zoo <- zoo(temp.join.df[, -1], index=temp.join.df[, 1])
head(temp.join.zoo[which(temp.join.zoo$symbol=="AAPL1C"), ])
?? ? ? symbol oi.x ? ? ? ?ret.x ? ? ? ? decile.x oi.y ? ? ? ?ret.y 200806 AAPL1C ?8710943.20 -1.129018e-01 10 ? ? ? ?7540246.46 -2.437297e-03 200807 AAPL1C ?7540246.46 -2.437297e-03 10 ? ? ? 11366863.59 ?1.703864e-01 200808 AAPL1C 11366863.59 ?1.703864e-01 10 ? ? ? ?9500119.05 -3.689558e-01 200809 AAPL1C ?9500119.05 -3.689558e-01 10 ? ? ? ? 114082.48 -4.406700e-02 200810 AAPL1C ? 114082.48 -4.406700e-02 ?1 ? ? ? ? 723159.24 -1.437163e-01 200811 AAPL1C ? 723159.24 -1.437163e-01 ?6 ? ? ? ?1006345.25 -1.005402e-01 ?? ? ? decile.y 200806 10 200807 10 200808 10 200809 ?1 200810 ?6 200811 ?8 When I sort on time zero to create a portfolio, I'd like to know the future performance of this portfolio on any given dimension. Thanks!
Could you supply the inputs using dput. That is: dput(myvariable) dput(myvariable2) etc. and following that your code and following that the result so that someone else can just copy whatever is in your message and paste it into their session and see the same result.
Statistics & Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com