Aggregating weekly (7 day) data to monthly in R

Question

I have data measured over a 7 day period. Part of the data looks as follows:

start wk    end wk      X1
2/1/2004    2/7/2004    89
2/8/2004    2/14/2004   65
2/15/2004   2/21/2004   64
2/22/2004   2/28/2004   95
2/29/2004   3/6/2004    79
3/7/2004    3/13/2004   79

I want to convert this weekly (7 day) data into monthly data using weighted averages of X1. Notice that some of the 7 day X1 data will overlap from one month to the other (X1=79 for the period 2/29 to 3/6 of 2004).

Specifically I would obtain the February 2004 monthly data (say, Y1) the following way

(7*89 + 7*65 + 7*64 + 7*95 + 1*79)/29 = 78.27

Does R have a function that will properly do this? (to.monthly in the xts library DOES NOT do what I need) If, not what is the best way to do this in R?

Answer 1

If you are willing to get rid of "end week" from your DF, apply.monthly will work like a charm.

DF.xts <- xts(DF$X1, order.by=DF$start_wk)

DF.xts.monthly <- apply.monthly(DF.xts, "sum")

Then you can always recreate end dates if you absolutely need them by adding 30.

Answer 2

Convert the data to daily data and then aggregate:

Lines <- "start end X1
2/1/2004    2/7/2004    89
2/8/2004    2/14/2004   65
2/15/2004   2/21/2004   64
2/22/2004   2/28/2004   95
2/29/2004   3/6/2004    79
3/7/2004    3/13/2004   79
"

library(zoo)

# read data into data frame DF
DF <- read.table(text = Lines, header = TRUE)

# convert date columns to "Date" class
fmt <- "%m/%d/%Y"
DF <- transform(DF, start = as.Date(start, fmt), end = as.Date(end, fmt))

# convert to daily zoo series
to.day <- function(i) with(DF, zoo(X1[i], seq(start[i], end[i], "day")))
z.day <- do.call(c, lapply(1:nrow(DF), to.day))

# aggregate by month
aggregate(z.day, as.yearmon, mean)

The last line gives:

Feb 2004 Mar 2004 
78.27586 79.00000