xts: Transfer/expand values to higher periodicity
On Fri, Aug 11, 2023 at 3:22?PM Mike <mike9 at posteo.nl> wrote:
First I convert a higher periodicity xts (e.g. day/hour) to a lower
one (e.g. week/day). Then I calculate values (e.g. the mean of the
low/high) on the lower periodicity xts.
Now I like to transfer and expand the calculated low periodicity
values into the corresponding bars of the high periodicity xts.
Or less abstract: Project numbers of each week into the corresponding
time frames (of mostly 5 bars) of the original daily xts.
My minimal reproducible example:
data(sample_matrix)
x.daily <- as.xts(sample_matrix)
# Limit sample to weekdays
x.daily <- x.daily[.indexwday(x.daily) %in% 1:5]
x.weekly <- to.weekly(x.daily, name=NULL)
# Example calculation
x.weekly <- cbind (x.weekly, rowMeans(x.weekly[,2:3]))
colnames(x.weekly) <- c('O','H','L','C','M')
head(x.weekly)
O H L C M
2007-01-05 50.03978 50.42188 49.95041 50.33459 50.18615
2007-01-12 50.03555 50.35980 49.80454 50.28519 50.08217
2007-01-19 50.61724 50.77336 50.40269 50.41278 50.58802
2007-01-26 50.36008 50.43875 49.94052 50.07024 50.18963
2007-02-02 49.85624 50.53490 49.76308 50.36928 50.14899
2007-02-09 50.52389 50.89683 50.45977 50.67686 50.67830
The new x.daily.new[,5] should look like this:
Open High Low Close M
2007-01-02 50.03978 50.11778 49.95041 50.11778 50.18615\
2007-01-03 50.23050 50.42188 50.23050 50.39767 50.18615|-Values
2007-01-04 50.42096 50.42096 50.26414 50.33236 50.18615| for week
2007-01-05 50.37347 50.37347 50.22103 50.33459 50.18615/
2007-01-08 50.03555 50.10363 49.96971 49.98806 50.08217\
2007-01-09 49.99489 49.99489 49.80454 49.91333 50.08217|
2007-01-10 49.91228 50.13053 49.91228 49.97246 50.08217|-Values
2007-01-11 49.88529 50.23910 49.88529 50.23910 50.08217| for week
2007-01-12 50.21258 50.35980 50.17176 50.28519 50.08217/
2007-01-15 50.61724 50.68583 50.47359 50.48912 50.58802\
2007-01-16 50.62024 50.73731 50.56627 50.67835 50.58802|
2007-01-17 50.74150 50.77336 50.44932 50.48644 50.58802|-Values
2007-01-18 50.48051 50.60712 50.40269 50.57632 50.58802| for week
2007-01-19 50.41381 50.55627 50.41278 50.41278 50.58802/
...
How can I achieve that?
Thanks for the reproducible example. It makes it a lot easier to help! Note that the index for x.daily is POSIXct even though it's daily data. You need to make sure it's Date in your case or you'll have timezone issues with this approach. x.daily <- as.xts(sample_matrix) # convert to Date index index(x.daily) <- as.Date(index(x.daily)) Now you can merge your daily series with the weekly series and use na.locf() to fill the gaps. x.daily.new <- merge(x.daily, x.weekly$M, fill = na.locf) The first 3 observations are NA because there's no observation for the first day of the week at the beginning of the series. You can fill those values backward by setting fromLast = TRUE) x.daily.new <- na.locf(x.daily.new, fromLast = TRUE) x.daily.new ## Open High Low Close M ## 2007-01-02 50.03978 50.11778 49.95041 50.11778 50.18615 ## 2007-01-03 50.23050 50.42188 50.23050 50.39767 50.18615 ## 2007-01-04 50.42096 50.42096 50.26414 50.33236 50.18615 ## 2007-01-05 50.37347 50.37347 50.22103 50.33459 50.18615 ## 2007-01-08 50.03555 50.10363 49.96971 49.98806 50.18615 ## 2007-01-09 49.99489 49.99489 49.80454 49.91333 50.18615 ## 2007-01-10 49.91228 50.13053 49.91228 49.97246 50.18615 ## 2007-01-11 49.88529 50.23910 49.88529 50.23910 50.18615 ## 2007-01-12 50.21258 50.35980 50.17176 50.28519 50.08217 ## 2007-01-15 50.61724 50.68583 50.47359 50.48912 50.08217 ## ... ## 2007-06-18 47.43470 47.56336 47.36424 47.36424 47.33332 ## 2007-06-19 47.46055 47.73353 47.46055 47.67220 47.33332 ## 2007-06-20 47.71126 47.81759 47.66843 47.66843 47.33332 ## 2007-06-21 47.71012 47.71012 47.61106 47.62921 47.33332 ## 2007-06-22 47.56849 47.59266 47.32549 47.32549 47.57154 ## 2007-06-25 47.20471 47.42772 47.13405 47.42772 47.57154 ## 2007-06-26 47.44300 47.61611 47.44300 47.61611 47.57154 ## 2007-06-27 47.62323 47.71673 47.60015 47.62769 47.57154 ## 2007-06-28 47.67604 47.70460 47.57241 47.60716 47.57154 ## 2007-06-29 47.63629 47.77563 47.61733 47.66471 47.45484
Mike
_______________________________________________ R-SIG-Finance at r-project.org 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.
Joshua Ulrich | about.me/joshuaulrich FOSS Trading | www.fosstrading.com