CODEWITHSUNDEEP
rzoo
volatile
volatile

Reputation: 949

R zoo aggregate

I have a zoo object containing the following data :

                          Qty 
2013-11-25 12:41:21.33       2 
2013-11-25 12:41:22.25      2 
2013-11-25 12:41:22.75      75 
2013-11-25 12:41:24.22      3 
2013-11-25 12:41:25.22      1 
2013-11-25 12:41:26.22      1

I want to aggregate it by second, summing the values inside each second AND ALSO putting a quantity to zero for the seconds that are not in the sample, so that I have the following regular series

                          Qty 
2013-11-25 12:41:21         2 
2013-11-25 12:41:22        77 
2013-11-25 12:41:23        0 
2013-11-25 12:41:24        3 
2013-11-25 12:41:25        1 
2013-11-25 12:41:26        1

Notice the third row (trying to use aggregate as explained in the vignette, with a function that transforms original time stamps to get rid of millisecond precision fails in appearing that third row and in having a regular series)

I tried, thanks to answer below :

if a is my original zoo series 

b=aggregate(a,by=cut(as.POSIXct(floor(as.numeric(index(a))),origin="1970-01-01"‌​), breaks='1 sec'),FUN=sum)

But that gives Qty 2013-11-25 12:41:21 2 2013-11-25 12:41:22 77 2013-11-25 12:41:24 3 2013-11-25 12:41:25 1 2013-11-25 12:41:26 1

So without the row 

2013-11-25 12:41:23    0

which makes the series regular and which is the point of my question Thanks a lot for help

Upvotes: 2

Views: 1056

Answers (1)

G. Grothendieck
G. Grothendieck

Reputation: 270348

Read in the test data, aggregate it by seconds and then merge the aggregated series with a grid to fill it in:

# read test data

Lines <- "2013-11-25 12:41:21         2 
2013-11-25 12:41:22.25      2 
2013-11-25 12:41:22.75      75 
2013-11-25 12:41:24.22      3 
2013-11-25 12:41:25.22      1 
2013-11-25 12:41:26.22      1"

library(zoo)
z <- read.zoo(text = Lines, index = 1:2, tz = "")

# aggregate and merge with grid to fill it out 

ag <- aggregate(z, as.POSIXct(trunc(time(z), "sec")), sum)
g <- zoo(, seq(start(z), end(z), "sec"))
merge(ag, g, fill = 0)

Upvotes: 3

Related Questions