[R] Data frame reordering to time series

[Gabor Grothendieck]

On Sun, Aug 8, 2010 at 11:21 AM, steven mosher <moshersteven at gmail.com> wrote:
> Ok,
> I'm a bit confused by what you mean by "regularly spaced"
> After I do the  do.call I do get a data structure with all the times present
> and every time has a NA or a data value.
> Steve
>

regularly spaced means that every observation is one month later than
the prior.  If there are missing 6 month chunks or missing entire
years then the observations are not regularly spaced since there are
some months not present.

It works for me:

> Id<-c(rep(67543,4),rep(12345,3),rep(89765,5))
>  Years<-c(seq(1989,1992,by =1),1991,1993,1994,seq(1991,1995,by=1))
>  Values2<-c(12,NA,34,21,NA,65,23,NA,13,NA,13,14)
>  Values<-c(12,14,34,21,54,65,23,12,13,13,13,14)
>  Data<-data.frame(Index=Id,Year=Years,Jan=Values,Feb=Values/2,Mar=Values2,Apr=Values2,Jun=Values,July=Values/3,Aug=Values2,Sep=Values,
+  Oct=Values,Nov=Values,Dec=Values2)
>
> library(zoo)
> f <- function(x) {
+        dat <- x[-(1:2)]
+        tim <- as.yearmon(outer(x$Year, seq(0, length = ncol(dat))/12, "+"))
+        zoo(c(as.matrix(dat)), tim)
+ }
> do.call(cbind, by(Data, Data$Index, f))
            X12345    X67543    X89765
Jan 1989        NA 12.000000        NA
Feb 1989        NA  6.000000        NA
Mar 1989        NA 12.000000        NA
Apr 1989        NA 12.000000        NA
May 1989        NA 12.000000        NA
Jun 1989        NA  4.000000        NA
Jul 1989        NA 12.000000        NA
Aug 1989        NA 12.000000        NA
Sep 1989        NA 12.000000        NA
Oct 1989        NA 12.000000        NA
Nov 1989        NA 12.000000        NA
Jan 1990        NA 14.000000        NA
Feb 1990        NA  7.000000        NA
Mar 1990        NA        NA        NA
Apr 1990        NA        NA        NA
May 1990        NA 14.000000        NA
Jun 1990        NA  4.666667        NA
Jul 1990        NA        NA        NA
Aug 1990        NA 14.000000        NA
Sep 1990        NA 14.000000        NA
Oct 1990        NA 14.000000        NA
Nov 1990        NA        NA        NA
Jan 1991 54.000000 34.000000 12.000000
Feb 1991 27.000000 17.000000  6.000000
Mar 1991        NA 34.000000        NA
Apr 1991        NA 34.000000        NA
May 1991 54.000000 34.000000 12.000000
Jun 1991 18.000000 11.333333  4.000000
Jul 1991        NA 34.000000        NA
Aug 1991 54.000000 34.000000 12.000000
Sep 1991 54.000000 34.000000 12.000000
Oct 1991 54.000000 34.000000 12.000000
Nov 1991        NA 34.000000        NA
Jan 1992        NA 21.000000 13.000000
Feb 1992        NA 10.500000  6.500000
Mar 1992        NA 21.000000 13.000000
Apr 1992        NA 21.000000 13.000000
May 1992        NA 21.000000 13.000000
Jun 1992        NA  7.000000  4.333333
Jul 1992        NA 21.000000 13.000000
Aug 1992        NA 21.000000 13.000000
Sep 1992        NA 21.000000 13.000000
Oct 1992        NA 21.000000 13.000000
Nov 1992        NA 21.000000 13.000000
Jan 1993 65.000000        NA 13.000000
Feb 1993 32.500000        NA  6.500000
Mar 1993 65.000000        NA        NA
Apr 1993 65.000000        NA        NA
May 1993 65.000000        NA 13.000000
Jun 1993 21.666667        NA  4.333333
Jul 1993 65.000000        NA        NA
Aug 1993 65.000000        NA 13.000000
Sep 1993 65.000000        NA 13.000000
Oct 1993 65.000000        NA 13.000000
Nov 1993 65.000000        NA        NA
Jan 1994 23.000000        NA 13.000000
Feb 1994 11.500000        NA  6.500000
Mar 1994 23.000000        NA 13.000000
Apr 1994 23.000000        NA 13.000000
May 1994 23.000000        NA 13.000000
Jun 1994  7.666667        NA  4.333333
Jul 1994 23.000000        NA 13.000000
Aug 1994 23.000000        NA 13.000000
Sep 1994 23.000000        NA 13.000000
Oct 1994 23.000000        NA 13.000000
Nov 1994 23.000000        NA 13.000000
Jan 1995        NA        NA 14.000000
Feb 1995        NA        NA  7.000000
Mar 1995        NA        NA 14.000000
Apr 1995        NA        NA 14.000000
May 1995        NA        NA 14.000000
Jun 1995        NA        NA  4.666667
Jul 1995        NA        NA 14.000000
Aug 1995        NA        NA 14.000000
Sep 1995        NA        NA 14.000000
Oct 1995        NA        NA 14.000000
Nov 1995        NA        NA 14.000000