[R] distributing a value for a given month across the number of weeks in that month

[Gabor Grothendieck]

On Fri, Jul 9, 2010 at 9:35 AM, Dimitri Liakhovitski
<dimitri.liakhovitski at gmail.com> wrote:
> Hello!
>
> Any hint would be greatly appreciated.
> I have a data frame that contains (a) monthly dates and (b) a value
> that corresponds to each month - see the data frame "monthly" below:
>
> monthly<-data.frame(month=c(20100301,20100401,20100501),monthly.value=c(100,200,300))
> monthly$month<-as.character(monthly$month)
> monthly$month<-as.Date(monthly$month,"%Y%m%d")
> (monthly)
>
> I need to split each month into weeks, e.g., weeks that start on
> Monday (it could as well be Sunday - it does not really matter) and
> distribute the monthly value evenly across weeks. So, if a month has 5
> Mondays, then the monthly value should be dividied by 5, but if a
> month has only 4 weeks, then the monthly value should be divided by 4.
>
> The output I need is like this:
>
> week          weekly.value
> 2010-03-01   20
> 2010-03-08   20
> 2010-03-15   20
> 2010-03-22   20
> 2010-03-29   20
> 2010-04-05   50
> 2010-04-12   50
> 2010-04-19   50
> 2010-04-26   50
> 2010-05-03   60
> 2010-05-10   60
> 2010-05-17   60
> 2010-05-24   60
> 2010-05-31   60
>


There is new functionality in na.locf in the development version
of zoo that makes it particularly convenient to do this.

First create a zoo object z from monthly and get a vector of all
the mondays.  Then use na.locf to place the monthly value in each
monday and ave to distribute them out.


library(zoo)

# pull in development version of na.locf.zoo
source("http://r-forge.r-project.org/scm/viewvc.php/*checkout*/pkg/zoo/R/na.locf.R?revision=725&root=zoo")

# convert to zoo
z <- with(monthly, zoo(monthly.value, month))

# get sequence of all dates and from that get mondays
all.dates <- seq(start(z), as.Date(as.yearmon(end(z)), frac = 1), by = "day")
mondays <- all.dates[weekdays(all.dates) == "Monday"]

# use na.locf to fill in mondays and ave to distribute them
weeks <- na.locf(z, xout = mondays)
weeks[] <- ave(weeks, as.yearmon(mondays), FUN = function(x) x[1]/length(x))

# show output in a few different formats
weeks
as.data.frame(weeks)
data.frame(Monday = as.Date(time(weeks)), value = weeks)
data.frame(Monday = as.Date(time(weeks)), value = weeks, row.names = NULL)
plot(weeks)

The output looks like this:

> weeks
2010-03-01 2010-03-08 2010-03-15 2010-03-22 2010-03-29 2010-04-05 2010-04-12
        20         20         20         20         20         50         50
2010-04-19 2010-04-26 2010-05-03 2010-05-10 2010-05-17 2010-05-24 2010-05-31
        50         50         60         60         60         60         60
> as.data.frame(weeks)
           weeks
2010-03-01    20
2010-03-08    20
2010-03-15    20
2010-03-22    20
2010-03-29    20
2010-04-05    50
2010-04-12    50
2010-04-19    50
2010-04-26    50
2010-05-03    60
2010-05-10    60
2010-05-17    60
2010-05-24    60
2010-05-31    60


 data.frame(Monday = as.Date(time(weeks)), value = weeks, row.names = NULL)
       Monday value
1  2010-03-01    20
2  2010-03-08    20
3  2010-03-15    20
4  2010-03-22    20
5  2010-03-29    20
6  2010-04-05    50
7  2010-04-12    50
8  2010-04-19    50
9  2010-04-26    50
10 2010-05-03    60
11 2010-05-10    60
12 2010-05-17    60
13 2010-05-24    60
14 2010-05-31    60