Summarize Values By Product By Month XTS Object in R

Question

I have an xts object with 900 columns (x1-x900) of daily values, that I need to compute into monthly returns.

x <- data.frame(date=seq(as.Date("2016/7/4"), as.Date("2016/10/1"), "day"),x1=runif(90,.95,1.07),x2=runif(90,.95,1.07),
      x3=runif(90,.95,1.07),x4=runif(90,.95,1.07),x5=runif(90,.95,1.07),x6=runif(90,.95,1.07),x7=runif(90,.95,1.07))

Above is a sample of the data I am working with. What I need to do is get the product of the values for each month, and for each column, for 7/31/2016, 8/31/2016, 9/30/2016, etc. I do not want to use zoo::rollapply because the length of each month obviously is not fixed. I have tried summarize, aggregate, but I haven't figured this out, and I am trying to avoid having to do a "for" loop.

The end goal is to obtain a data.frame such as:

Date       x1      x2      x3       x4
7/31/16   1.03    0.98    1.01     1.03
8/31/16   1.01    0.95    1.03     1.01
9/30/16   0.97    1.02    0.94     0.98
10/31/16  0.99    0.98    1.01     1.04

FXQuantTrader · Answer 1 · 2016-10-13T23:45:08.653

3

xts.x <- xts(x[, !colnames(x) %in% "date"], order.by = x[, "date"])
xts.x.mthly <- apply.monthly(xts.x, FUN = function(x) unlist(lapply(x, prod)))

> xts.x.mthly
                  x1       x2        x3        x4        x5       x6        x7
2016-07-31 0.9924681 1.306556 1.0919181 0.8019117 1.3563864 1.853631 0.8563263
2016-08-31 1.4780971 1.946373 1.4265027 1.8508386 1.4926483 1.651613 1.4224733
2016-09-30 1.5926547 1.478231 1.0414107 1.4204825 1.2540149 1.374734 1.0768668
2016-10-01 1.0643725 1.005987 0.9813467 1.0545426 0.9964061 1.005145 1.0146190

# If you want data.frame output with explicit date column:
df.mthly <- data.frame("date" = index(xts.x.mthly), coredata(xts.x.mthly))

edited Oct 13 '16 at 23:45

answered Oct 13 '16 at 22:45

FXQuantTrader

6,821
3
36
67

1

Here is a quick comparison between my solution and FXQuantTrader's. No doubt his is much faster (about 4 times)! `library(microbenchmark) ; microbenchmark(Apom = { x %>% group_by(yearmon = paste(year(date), month(date), sep = "-")) %>% mutate(Date = max(date)) %>% group_by(Date) %>% summarise_each(funs(prod), - c(yearmon, date, Date)) }, FXQuantTrader = { xts.x <- xts(x[, !colnames(x) %in% "date"], order.by = x[, "date"]) apply.monthly(xts.x, FUN = function(x) unlist(lapply(x, prod))) })` – Aurèle Oct 13 '16 at 23:32

Aurèle · Answer 2 · 2016-10-13T22:51:08.243

Here is a solution with the dplyr and lubridate packages:

set.seed(1) ; x <- data.frame(date=seq(as.Date("2016/7/4"), as.Date("2016/10/1"), "day"),x1=runif(90,.95,1.07),x2=runif(90,.95,1.07),
                x3=runif(90,.95,1.07),x4=runif(90,.95,1.07),x5=runif(90,.95,1.07),x6=runif(90,.95,1.07),x7=runif(90,.95,1.07))
library(dplyr) ; library(lubridate)
x %>% 
  group_by(yearmon = paste(year(date), month(date), sep = "-")) %>% 
  summarise_each(funs(prod), - c(date, yearmon))

It leaves out the part where you'd want to get the last day of the month. Hopefully it is still helpful.

(Edit: for the missing part, here is a workaround:

x %>% 
  group_by(yearmon = paste(year(date), month(date), sep = "-")) %>% 
  mutate(Date = max(date)) %>% 
  group_by(Date) %>% 
  summarise_each(funs(prod), - c(yearmon, date, Date))

And for the missing part: http://stackoverflow.com/questions/8333838/how-do-you-generate-a-sequence-of-the-last-day-of-the-month-over-two-years-in-r — m-dz, Oct 13 '16 at 22:36

Summarize Values By Product By Month XTS Object in R

2 Answers2