R help - May 2007 - Quick question on merging two time-series of different frequencies

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. 

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

?findInterval

On 5/10/07, Patnaik, Tirthankar <tirthankar.patnaik at citi.com>
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.
>
> 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.
>

-- 
Jim Holtman
Cincinnati, OH
+1 513 646 9390

What is the problem you are trying to solve?

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

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