[R] roll weekly data to monthly level

[Gabor Grothendieck]

Try this. First we read in the data.  In reality you would use
the commented out read line.   Then use chron to convert
the times to chron dates and as.yearmon from zoo to convert
them to year/months.  Finally aggregate and sort.

> Lines <- "ID    time           y
+ 1    2/01/2008    4
+ 1    2/09/2008    12
+ 1    9/01/2008    8
+ 2    1/06/2008    3
+ 2    3/01/2008    4
+ 2    3/09/2008    9
+ 2    6/03/2008    4
+ 3    1/02/2008    3
+ 3    1/10/2008    8
+ 3    2/02/2008    7
+ 3    2/10/2008    3"
>
> library(zoo) # as.yearmon
> library(chron) # chron
>
> # DF <- read.table("myfile.dat", header = TRUE, as.is = TRUE)
> DF <- read.table(textConnection(Lines), header = TRUE, as.is = TRUE)
> DF$yearmon <- as.yearmon(chron(DF$time))
> res <- aggregate(DF["y"], DF[c("ID", "yearmon")], sum)
> res[order(res[, 1], res[, 2]), ]
  ID  yearmon  y
3  1 Feb 2008 16
7  1 Sep 2008  8
1  2 Jan 2008  3
5  2 Mar 2008 13
6  2 Jun 2008  4
2  3 Jan 2008 11
4  3 Feb 2008 10
>


On Mon, Jan 12, 2009 at 4:57 PM, liujb <liujulia7 at yahoo.com> wrote:
>
> Dear R users:
>
> I have a data set that looks something like this:
> ID    time           y
> 1    2/01/2008    4
> 1    2/09/2008    12
> 1    9/01/2008    8
> 2    1/06/2008    3
> 2    3/01/2008    4
> 2    3/09/2008    9
> 2    6/03/2008    4
> 3    1/02/2008    3
> 3    1/10/2008    8
> 3    2/02/2008    7
> 3    2/10/2008    3
>
> I'd like to sum up the weekly data to the monthly level, so that it looks
> something like this:
>
> ID    time       y
> 1    2/2008    16
> 1    9/2008    8
> 2    1/2008    3
> 2    3/2008    13
> 2    6/2008    4
> 3    1/2008    11
> 3    2/2008    10
>
>
> What is the best way to do it?
>
> Time must be character. How do I truncate a character so that I can remove
> the date and only keep the month and year?
>
> Thank you very much in advance.
> Julia
> --
> View this message in context: http://www.nabble.com/roll-weekly-data-to-monthly-level-tp21424700p21424700.html
> Sent from the R help mailing list archive at Nabble.com.
>
> ______________________________________________
> 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.
>