[R-SIG-Finance] zoo: how to find for series x closest day in series y?

[Gabor Grothendieck]

Example 4d on the sqldf home page shows how:
http://sqldf.googlecode.com

Here it is modified to work with your example data.

(Note that its important to use different column names in DFx and DFy
since it can otherwise foul up sqldf's class assignment heuristic in
this case.  If for some reason you must use names that are the same in
both DFx and DFy then use the method = "raw" argument to sqldf and
convert the date columns to Date yourself.  See sqldf home page for
more info.)

library(zoo)
library(sqldf)

x <- zoo(1:3,as.Date(c("1992-12-13", "1997-05-12", "1997-07-13")))
y <- zoo(1:5,as.Date(c("1992-12-15", "1992-12-16", "1997-05-10","1997-05-19",
"1997-07-13")))

DFx <- data.frame(x = coredata(x), xt = index(x))
DFy <- data.frame(y = coredata(y), yt = index(y))

out <- sqldf("select x, y, xt, yt from DFx x, DFy y
  where abs(xt - yt) =
  (select min(abs(xt - y2.yt)) from DFy y2)")

ix <- !duplicated(out[,3])
zoo(out[ix, 1:2], out[ix, 3])


On Fri, Apr 23, 2010 at 5:10 AM, Matthieu Stigler
<matthieu.stigler at gmail.com> wrote:
> Hi
>
> I have two series, x  and y, which don't have the same index. I then need to
> find which in y is the closest day in x. How can do I do this in a clean
> way? I have here a dirty for loop solution...
> library(zoo)
> #my series
> x<-zoo(1:3,as.Date(c("1992-12-13", "1997-05-12", "1997-07-13")))
> y<-zoo(1:5,as.Date(c("1992-12-15", "1992-12-16", "1997-05-10","1997-05-19",
> "1997-07-13")))
>
> #but index is not always the same:
> index(x)%in%index(y)
>
> #my (dirty?) solution
> xnew<-x
> for(i in which(!index(x)%in%index(y)))
> xnew[i]<-which.min(abs(as.numeric(index(x)[i]-index(y))))
> xnew
>
> Is there something cleaner one could use to find the closest available day?
>
> Thanks a lot!
>
> Matthieu
>
> _______________________________________________
> R-SIG-Finance at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-sig-finance
> -- Subscriber-posting only. If you want to post, subscribe first.
> -- Also note that this is not the r-help list where general R questions
> should go.
>