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

From: Gabor Grothendieck <ggrothendieck_at_gmail.com>
Date: Fri, 11 May 2007 04:21:30 -0400

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.
>



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. Received on Fri 11 May 2007 - 08:29:09 GMT

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Fri 11 May 2007 - 08:32:24 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.