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
<ggrothendi...@gmail.com> wrote:
> On Fri, Jul 9, 2010 at 9:35 AM, Dimitri Liakhovitski
> <dimitri.liakhovit...@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
______________________________________________
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
