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
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 >
On Sat, Jun 16, 2012 at 8:19 AM, 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 > >Here is a solution using zoo and aggregate.zoo: # dat is from Ken's post 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)) library(zoo) # split into columns by country z <- read.zoo(dat, index = 2, format = "%d/%m/%y", split = 1) # by month for each country aggregate(z, format(time(z), "%m"), mean, na.rm = TRUE) # by year and month for each country aggregate(z, as.yearmon, mean, na.rm = TRUE) -- Statistics & Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com