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

[Dimitri Liakhovitski]

Actually,
I realized that my task was a bit more complicated as I have different
(let's call them) Markets and the dates repeat themselves across
markets. And the original code from Gabor gives an error - because
dates repeate themselves and apparently zoo cannot handle it. So, I
had to do program a way around it (below). It works.
However, I am wondering if there is a shorter/more elegant way of doing it?
Thank you!
Dimitri

### My original data frame is a bit more complicated - dates repeat
themselves for 2 markets:
monthly<-data.frame(month=c(20100301,20100401,20100501,20100301,20100401,20100501),monthly.value=c(100,200,300,10,20,30),market=c("Market
A","Market A", "Market A","Market B","Market B", "Market B"))
monthly$month<-as.character(monthly$month)
monthly$month<-as.Date(monthly$month,"%Y%m%d")
(monthly)

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
my.z.list<-NULL
for(i in 1:length(levels(monthly$market))){
  my.frame<-monthly[monthly$market %in% levels(monthly$market)[i],1:2]
  my.z.list[[i]] <- with(my.frame, zoo(monthly.value, month))
}

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

# use na.locf to fill in mondays and ave to distribute them
weekly<-NULL
for(i in 1:length(levels(monthly$market))){
  weekly[[i]] <- na.locf(my.z.list[[i]], xout = mondays)
  weekly[[i]][] <- ave(weekly[[i]], as.yearmon(mondays), FUN =
function(x) x[1]/length(x))
}
(weekly)

### Creating a data frame with markets stacked on top of each other -
like in the original monthly data frame:
for(i in 1:length(weekly)){
  weekly[[i]]<-as.data.frame(weekly[[i]])
  weekly[[i]]$week<-row.names(weekly[[i]])
  names(weekly[[i]])[1]<-"weekly.value"
  weekly[[i]]$market<-levels(monthly$market)[i]
}
weekly.data<-do.call(rbind,weekly)

That's it.
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