[R] Aggregate daily data into weekly sums

[Gabor Grothendieck]

Try this.  I have changed output format to yyy/mm/Weekwwwww so its ordered.

Lines <- "Date Amount
 6/1/2007      1
 6/1/2007      1
 6/4/2007      2
 6/5/2007      2
6/11/2007      3
6/12/2007      3
6/12/2007      3
6/13/2007      3
6/13/2007      3
6/18/2007      4
6/18/2007      4
6/25/2007      5
6/28/2007      5
"

# replace next line with
# DF <- read.table("myfile.dat", header = TRUE)
DF <- read.table(textConnection(Lines), header = TRUE)
DF$Date <- as.Date(DF$Date, "%m/%d/%Y")

# weeks since first Sunday after Epoch
# assumes week starts on Sunday.  Change 3 to 4 for Monday.
fmt <- function(x) {
	weeks <- function(x) as.numeric(x + 3) %/% 7 + 1
	sprintf("%s%05d", format(x, "%Y/%m/Week"), weeks(x) - weeks(x[1]) + 1)
}

aggregate(DF$Amount, list(Date = fmt(DF$Date)), sum)

# alternative to above using zoo.  DF and fmt are from above.
# Returns a zoo object.
library(zoo)
aggregate(zoo(DF$Amount), fmt(DF$Date), sum)

On 7/23/07, Jacques Wagnor <jacques.wagnor at gmail.com> wrote:
> Dear Lest,
>
> I have a two-variable data frame as follows (the time peirod of the
> actual data set is 10 years):
>
>        Date Amount
> 1   6/1/2007      1
> 2   6/1/2007      1
> 3   6/4/2007      2
> 4   6/5/2007      2
> 5  6/11/2007      3
> 6  6/12/2007      3
> 7  6/12/2007      3
> 8  6/13/2007      3
> 9  6/13/2007      3
> 10 6/18/2007      4
> 11 6/18/2007      4
> 12 6/25/2007      5
> 13 6/28/2007      5
>
>
> Basically, I would like to collapse the daily data into weekly sums
> such that the result should look like the following:
>
>              Date Amount
> 1  2007/6/Week1       2
> 2  2007/6/Week2       4
> 3  2007/6/Week3       15
> 4  2007/6/Week4       8
> 5  2007/6/Week5      10
>
> Does there already exist a function that aggregates the data at
> user-defined time frequency?
>
> Any pointers would be greatly appreciated.
>
> Jacques
>
> > version
>               _
> platform       i386-pc-mingw32
> arch           i386
> os             mingw32
> system         i386, mingw32
> status
> major          2
> minor          5.0
> year           2007
> month          04
> day            23
> svn rev        41293
> language       R
> version.string R version 2.5.0 (2007-04-23)
>
> ______________________________________________
> R-help at stat.math.ethz.ch 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.
>