[R] Quick question on merging two time-series of different frequencies

[Gabor Grothendieck]

Its not clear to me what you want but play around with these
forms of merge.zoo:

merge(x, y) # all = TRUE
merge(x, y, all = FALSE)
merge(x, y, all = c(TRUE, FALSE))
merge(x, y, all = c(FALSE, TRUE))


On 5/11/07, Patnaik, Tirthankar <tirthankar.patnaik at citi.com> wrote:
> Achim,
>        Thanks so much! I should've probably explained what I was trying
> to do better (Just an LOCF!). As you've correctly pointed out I'm trying
> to merge two time series where the first series is daily (price data),
> and the second series is irregular (earnings, balance-sheet data).
>
> While using an LOCF would certainly solve my problem (In SAS, one used
> the DOW (Dorfman-Whitlock) loop for this purpose, but I didn't know it
> existed in R), to account for the possibility of a timestamp in the
> irregular times series _not_ having an equivalent in the regular series
> (wherein the na.locf would introduce an NA in the regular series), I
> just inner-join the regular series with the result.
>
> > x <- zoo(rnorm(11), as.Date("2000-01-01") + c(1:6,8:10))
>
> Regular series: Doesn't have 7.
>
> > y <- zoo(rnorm(4), as.Date("2000-01-01") + c(0, 3, 7, 10))
>
> Irregular series: Has 7.
>
> > z <- merge(x, y)
>
> Full join between x and y.
>
> > z1 <- na.locf(z)
>
> The LOCF. Fills up the regular series too.
>
> > z2 <- merge(x,na.locf(merge(x,y))[,2],all=FALSE)
>
> Getting rid of the extraneous observation in x.
>
> Results:
>
> > x
> 2000-01-02 2000-01-03 2000-01-04 2000-01-05 2000-01-06 2000-01-07
> 2000-01-09
>  1.2194692 -0.6081166 -1.1852422  1.0577249  0.3940365 -0.1742952
> -0.8321219
> 2000-01-10 2000-01-11
> -0.8628410  1.5109680
> > y
>  2000-01-01  2000-01-04  2000-01-08  2000-01-11
> -0.03331723  0.04525300 -0.68003282  0.20282930
> > z
>                    x           y
> 2000-01-01         NA -0.03331723
> 2000-01-02  1.2194692          NA
> 2000-01-03 -0.6081166          NA
> 2000-01-04 -1.1852422  0.04525300
> 2000-01-05  1.0577249          NA
> 2000-01-06  0.3940365          NA
> 2000-01-07 -0.1742952          NA
> 2000-01-08         NA -0.68003282
> 2000-01-09 -0.8321219          NA
> 2000-01-10 -0.8628410          NA
> 2000-01-11  1.5109680  0.20282930
> > z1
>                    x           y
> 2000-01-02  1.2194692 -0.03331723
> 2000-01-03 -0.6081166 -0.03331723
> 2000-01-04 -1.1852422  0.04525300
> 2000-01-05  1.0577249  0.04525300
> 2000-01-06  0.3940365  0.04525300
> 2000-01-07 -0.1742952  0.04525300
> 2000-01-08 -0.1742952 -0.68003282
> 2000-01-09 -0.8321219 -0.68003282
> 2000-01-10 -0.8628410 -0.68003282
> 2000-01-11  1.5109680  0.20282930
> attr(,"na.action")
> [1] 1
> attr(,"class")
> [1] "omit"
> > z2
>                    x na.locf(merge(x, y))[, 2]
> 2000-01-02  1.2194692               -0.03331723
> 2000-01-03 -0.6081166               -0.03331723
> 2000-01-04 -1.1852422                0.04525300
> 2000-01-05  1.0577249                0.04525300
> 2000-01-06  0.3940365                0.04525300
> 2000-01-07 -0.1742952                0.04525300
> 2000-01-09 -0.8321219               -0.68003282
> 2000-01-10 -0.8628410               -0.68003282
> 2000-01-11  1.5109680                0.20282930
>
> Could you also give a quick example of a left- and right join in zoo? I
> tried all.x=TRUE, and all.y=FALSE but they don't seem to work.
>
> Best,
> -Tir
>
> --
> Tirthankar Patnaik
> India Strategy
> Citigroup Investment Research
> +91-22-6631 9887
>
>
> -----Original Message-----
> From: Achim Zeileis [mailto:Achim.Zeileis at wu-wien.ac.at]
> Sent: Thursday, May 10, 2007 10:42 PM
> To: Patnaik, Tirthankar [GWM-CIR]
> Cc: r-help at stat.math.ethz.ch
> Subject: Re: [R] Quick question on merging two time-series of different
> frequencies
>
> On Thu, 10 May 2007, Patnaik, Tirthankar  wrote:
>
> > Hi,
> >       A quick beginner's question. I have two time series, A with
> daily
> > data, and another B with data at varying frequencies, but mostly
> > annual. Both the series are sorted ascending.
> >
> > I need to merge these two series together in the following way: For
> > any entry of A, the lookup should match with B until we find an entry
> > of B that's larger than A's.
>
> I'm not sure what exactly you want. I assume that A and B are not the
> observations but the corresponding time stamps, right?
>
> In any case, I guess that the "zoo" package will have some useful
> functionality for what you want, e.g., if you have data like
>  library("zoo")
>  set.seed(123)
>  x <- zoo(rnorm(11), as.Date("2000-01-01") + 0:10)
>  y <- zoo(rnorm(4), as.Date("2000-01-01") + c(0, 3, 7, 10)) then you
> can merge them with
>  z <- merge(x, y)
> and then eliminate NAs, e.g. by
>  na.locf(z)
> or you could aggregate() the "x" series so that it becomes a monthly
> series or something like that.
>
> See
>  vignette("zoo", package = "zoo")
> for more information.
>
> Best,
> Z
>
> > For all A[i], i = 1,...,N and B[j], j=1,...M
> >
> > Match with B[j] where A[i] <= B[j]
> >
> > When A[i] > B[j], match with B[j+1] where A[i] <= B[j+1]
> >
> > Basically the less-frequent attributes stay true for a stock while the
>
> > daily prices change.
> >
> > One example of this is the vlookup function in Excel with the TRUE
> > option.
> >
> > So we have an implementation of this in R?
> >
> > TIA and best,
> > -Tir
> >
> > ______________________________________________
> > R-help at stat.math.ethz.ch 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.
> >
> >
>
> ______________________________________________
> R-help at stat.math.ethz.ch 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.
>