Re: [R] Quick question on merging two time-series of different frequencies
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 [EMAIL PROTECTED] 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.18524220.04525300 2000-01-05 1.05772490.04525300 2000-01-06 0.39403650.04525300 2000-01-07 -0.17429520.04525300 2000-01-09 -0.8321219 -0.68003282 2000-01-10 -0.8628410 -0.68003282 2000-01-11 1.51096800.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:[EMAIL PROTECTED] Sent: Thursday, May 10, 2007 10:42 PM To: Patnaik, Tirthankar [GWM-CIR] Cc: r-help@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
[R] 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 __ R-help@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.
Re: [R] Quick question on merging two time-series of different frequencies
?findInterval On 5/10/07, Patnaik, Tirthankar [EMAIL PROTECTED] 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@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? __ R-help@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.
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@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@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.
Re: [R] Quick question on merging two time-series of different frequencies
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.18524220.04525300 2000-01-05 1.05772490.04525300 2000-01-06 0.39403650.04525300 2000-01-07 -0.17429520.04525300 2000-01-09 -0.8321219 -0.68003282 2000-01-10 -0.8628410 -0.68003282 2000-01-11 1.51096800.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:[EMAIL PROTECTED] Sent: Thursday, May 10, 2007 10:42 PM To: Patnaik, Tirthankar [GWM-CIR] Cc: r-help@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@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
