[R] converting a data frame to ts objects

[Gabor Grothendieck]

On 5/16/07, Gabor Grothendieck <ggrothendieck at gmail.com> wrote:
> On 5/16/07, fatih ozgul <fatih.ozgul at gmail.com> wrote:
> > Dear R-ians
> >
> > I have a data frame like
> >
> > Person_id  Date/time        Count
> > ---------  ----------      -------
> > 123         20 May 1999      1
> > 123         21 May 1999      3
> > 222         1  Feb 2000      2
> > 222         3  Feb 2000      4
> >
> > I want to create ts objects for each person_id (i.e. 123 and 222). Time
> > series frequency can be in months and all starting from the same date (i.e.
> > May 1999).
> >
> > Rather than manually creating sum of counts for each month and creating ts
> > objects manually for each persons,
> >
> > Is there a function for creating ts objects automatically from such data?
> >
>
> Try this:
>
> # read in as data frame and fix up date
> Lines.raw <- "Person_id Date/time Count
> 123 20 May 1999 1
> 123 21 May 1999 3
> 222 1 Feb 2000 2
> 222 3 Feb 2000 4
> "
> library(zoo)
> DF <- read.table(textConnection(Lines.raw), skip = 1,
>  col.names = c("Person_id", "d", "b", "Y", "Count"))
> DF$Date.time <- as.Date(paste(DF$d, DF$b, DF$Y), "%d %b %Y")
>
> # aggregate counts over months, series and convert to "ts"
> f <- function(DF) aggregate(zoo(DF$Count), as.yearmon(DF$Date.time), sum)
> z <- do.call("merge.zoo", lapply(split(DF, DF$Person_id), f))
> frequency(z) <- 12
> as.ts(z)
>

A slight simplification based on zoo 1.3-1 (which just appeared on CRAN)
is the following.  It converts the month and year columns directly into
class "yearmon" eliminating the intermediate step of going via "Date" class.
Its the same number of lines but a couple of the lines are now simpler.

# read in as data frame and fix up date
Lines.raw <- "Person_id Date/time Count

123 20 May 1999 1
123 21 May 1999 3
222 1 Feb 2000 2
222 3 Feb 2000 4
"

library(zoo)
DF <- read.table(textConnection(Lines.raw), skip = 1,
 col.names = c("Person_id", "d", "b", "Y", "Count"))
DF$yearmon <- as.yearmon(paste(DF$b, DF$Y), "%b %Y")

# aggregate counts over months, merge series and convert to "ts"
ag <- function(DF) aggregate(zoo(DF$Count), DF$yearmon, sum)
z <- do.call("merge.zoo", lapply(split(DF, DF$Person_id), ag))
frequency(z) <- 12
as.ts(z)