Convert daily to weekly/monthly data with R

saradi picture saradi · May 8, 2013 · Viewed 33.7k times · Source

I have daily prices series over a wide range of products; I want to convert to a new dataframe with weekly or monthly data.

enter image description here

I first used xts in order to apply the to.weekly function...which works only for OHLC format. I am sure there may exist a function similar to to.weekly but for dataframe where the format is not OHLC.

There a different posts already related to this as the following: Does rollapply() allow an array of results from call to function? or Averaging daily data into weekly data

I am eventually using:

length(bra)

1 2416

test<-bra[seq(1,2416,7),]

Would there be a more efficient approach? Thanks.

Answer

Oscar Perpi&#241;&#225;n picture Oscar Perpiñán · May 8, 2013

Let's try with this data:

library(zoo)
tt <- seq(Sys.Date(), by='day', length=365)
vals <- data.frame(A=runif(365), B=rnorm(365), C=1:365)
z <- zoo(vals, tt)

Now I define a function which extracts the year and the number of the week (drop %Y if you don't need to distinguish between years):

week <- function(x)format(x, '%Y.%W')

You can use this function to aggregate the zoo object with mean (for example):

aggregate(z, by=week, FUN=mean)

which produces this result:

                A           B  C
2013.18 0.3455357  0.34129269  3
2013.19 0.4506297  0.57665133  9
2013.20 0.3950585  0.46197173 16
2013.21 0.5990886 -0.02689994 23
2013.22 0.5115043  0.18726564 30
2013.23 0.5327597  0.16250339 37