[R-SIG-Finance] merge zoo with many entries per date with lagged self using ticker

[Gabor Grothendieck]

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