CODEWITHSUNDEEP
raggregate
Malang
Malang

Reputation: 291

Aggregating daily content

I've been attempting to aggregate (some what erratic) daily data. I'm actually working with csv data, but if i recreate it - it would look something like this:

library(zoo)

dates <- c("20100505", "20100505", "20100506", "20100507")
val1 <- c("10", "11", "1", "6")
val2 <- c("5", "31", "2", "7")

x <- data.frame(dates = dates, val1=val1, val2=val2)
z <- read.zoo(x, format = "%Y%m%d")

Now i'd like to aggregate this on a daily basis (notice that some times there are >1 datapoint for a day, and sometimes there arent.

I've tried lots and lots of variations, but i cant seem to aggregate, so for instance this fails:

aggregate(z, as.Date(time(z)), sum)
# Error in Summary.factor(2:3, na.rm = FALSE) : sum not meaningful for factors

There seems to be a lot of content regarding aggregate, and i've tried a number of versions but cant seem to sum this on a daily level. I'd also like to run cummax and cumulative averages in addition to the daily summing.

Any help woudl be greatly appreciated.

Update

The code I am actually using is as follows:

z <- read.zoo(file = "data.csv", sep = ",", header = TRUE, stringsAsFactors = FALSE, blank.lines.skip = T, na.strings="NA", format = "%Y%m%d");

It seems my (unintentional) quotation of the numbers above is similar to what is happening in practice, because when I do:

aggregate(z, index(z), sum)
#Error in Summary.factor(25L, na.rm = FALSE) : sum not meaningful for factors

There a number of columns (100 or so), how can i specify them to be as.numeric automatically ? (stringAsFactors = False doesnt appear to work?)

Upvotes: 3

Views: 6557

Answers (4)

G. Grothendieck
G. Grothendieck

Reputation: 269461

Convert the character columns to numeric and then use read.zoo making use of its aggregate argument:

> x[-1] <- lapply(x[-1], function(x) as.numeric(as.character(x)))
> read.zoo(x, format = "%Y%m%d", aggregate = sum)
             val1 val2
2010-05-05   21   36
2010-05-06    1    2
2010-05-07    6    7

Upvotes: 1

Dirk is no longer here
Dirk is no longer here

Reputation: 368191

You started on the right path but made a couple of mistakes.

First, zoo only consumes matrices, not data.frames. Second, those need numeric inputs:

> z <- zoo(as.matrix(data.frame(val1=c(10,11,1,6), val2=c(5,31,2,7))), 
+          order.by=as.Date(c("20100505","20100505","20100506","20100507"),
+                           "%Y%m%d"))
Warning message:
In zoo(as.matrix(data.frame(val1 = c(10, 11, 1, 6), val2 = c(5,  :
  some methods for "zoo" objects do not work if the index entries in 
  'order.by' are not unique

This gets us a warning which is standard in zoo: it does not like identical time indices.

Always a good idea to show the data structure, maybe via str() as well, maybe run summary() on it:

> z
           val1 val2
2010-05-05   10    5
2010-05-05   11   31
2010-05-06    1    2
2010-05-07    6    7

And then, once we have it, aggregation is easy:

> aggregate(z, index(z), sum)
           val1 val2
2010-05-05   21   36
2010-05-06    1    2
2010-05-07    6    7
>

Upvotes: 4

Henrik
Henrik

Reputation: 14450

Or you aggregate before using zoo (val1 and val2 need to be numeric though).

x <- data.frame(dates = dates, val1=as.numeric(val1), val2=as.numeric(val2))
y <- aggregate(x[,2:3],by=list(x[,1]),FUN=sum)

and then feed y into zoo.

You avoid the warning:)

Upvotes: 5

Joshua Ulrich
Joshua Ulrich

Reputation: 176648

val1 and val2 are character strings. data.frame() converts them to factors. Summing factors doesn't make sense. You probably intended:

x <- data.frame(dates = dates, val1=as.numeric(val1), val2=as.numeric(val2))
z <- read.zoo(x, format = "%Y%m%d")
aggregate(z, as.Date(time(z)), sum)

which yields:

           val1 val2
2010-05-05   21   36
2010-05-06    1    2
2010-05-07    6    7

Upvotes: 1

Related Questions