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

[Dimitri Liakhovitski]

Wow, Gabor - that's amazing - thank you so much!
Dimitri

On Fri, Jul 9, 2010 at 10:22 AM, Gabor Grothendieck
<ggrothendieck at gmail.com> wrote:
> 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
>



-- 
Dimitri Liakhovitski
Ninah Consulting
www.ninah.com