[R] interpolation to montly data

[Ken]

stef salvez <loggyedy <at> googlemail.com> writes:

> 
> I would like to clarify that since each observation is obtained every
> 28 days, each such observation is a 4-week average
> 
> thanks
> 
> On 6/16/12, stef salvez <loggyedy <at> googlemail.com> wrote:
> > I have a panel data set (in MS excel)  like the one below
> >
> >
> >  1         "23/11/08"            2
> > 1   "28/12/08"                   3
> > 1    "25/01/09"                   4
> > 1   "22/02/09"                   5
> > 1    "29/03/09"                  6
> > 1  "26/04/09"                   32
> > 1  "24/05/09"                   23
> > 1  "28/06/09"                   32
> > 2   "26/10/08"                45
> > 2  "23/11/08"                 46
> > 2  "21/12/08"               90
> > 2  "18/01/09"                54
> > 2  "15/02/09"                 65
> > 2   "16/03/09"               77
> > 2  "12/04/09"                    7
> > 2   "10/05/09"                   6
> >
> >
> >
> >
> > the start and end date of the time series for countries 1 and 2 are
> > different. For example, for country 1 the time series begins on
> > "23/11/08" while for country 2 the time series begins on "26-10-2008”.
> >
> > My data on prices are available every 28 days (or equivalently every 4
> > weeks). But in some cases I have jumps (35 days or 29 days instead of
> > 28 days). For example from the above table we have such jumps: from
> > "28/12/08" to "28/12/08" , from 22/02/09" to "29/03/09", etc
> >
> > My goal is to have a unified sequence of dates across countries. So,
> > to achieve this I want to apply the following solutions
> >
> > I want  to take what I have and calculate monthly average prices and also
> > report how many prices those averages are based on. I suppose that I
> > will still have gaps and may well need to interpolate.
> >
> > Please, I would be grateful to you if you could provide the exact code
> > for doing this
> >
> >
> > thanks a lot
> >
> 
> ______________________________________________
> R-help <at> 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.
> 

Hi Stef,
I'm a little confused on how you want to break up the values in "4 week" bins,
but if you wanted to keep it simple and do averages according to the month they
fall in you could use the plyr package with a custom utility function:

#load library
library(plyr)

# utility function
mean.var = function(df, var){ mean(df[[var]], na.rm = T)};

# create example data
dat <- data.frame("country" = c(rep(1,8), rep(2, 8)), 
                 "date" = c("23/11/08","28/12/08","25/01/09","22/02/09",
                          "29/03/09","26/04/09","24/05/09", "28/06/09",
                          "26/10/08","23/11/08","21/12/08","18/01/09",
                          "15/02/09","16/03/09","12/04/09","10/05/09"),
                 "price" = c(2,3,4,5,6,32,23,32,45,46,90,54,65,77,7,6))
# add month column to df
dat$month = substr(dat$date, 4,5)

#calculate average price by month across all countries and calculate monthly
#frequency and put output in one data frame
monthly.price = ddply(dat, .(month), mean.var, var = "price")
monthly.price = cbind(monthly.price, "month.freq" = as.vector(table(df$month)))
names(monthly.price) = c("month", "average.price", "month.freq")

HTH,
Ken