CODEWITHSUNDEEP
rtime-seriesxtszoo
AndMan21
AndMan21

Reputation: 533

How do I aggregate and sum irregular time-series data based on a desired regular time interval in R?

I have rainfall data at very irregular intervals. Every time it records 0.01 inches of rain, the data logger records the time down to seconds. A few data points look like this:

datetime <- as.POSIXct(as.character(c("2/5/15 16:28:38", "2/5/15 16:29:36", "2/5/15 16:29:41", "2/5/15 16:30:00")), format="%m/%d/%y %H:%M:%S")
value <- rep(0.01, 4)
df <- data.frame(datetime, value)
df


>                  datetime  value
>     1 2015-02-05 16:28:38  0.01
>     2 2015-02-05 16:29:36  0.01
>     3 2015-02-05 16:29:41  0.01
>     4 2015-02-05 16:30:00  0.01

I was trying to get the hang of zoo and xts, but to no avail. My end goal is to sum the "values" by even minute, like so:

2015-02-05 16:27  0
2015-02-05 16:28  0.01
2015-02-05 16:29  0.02
2015-02-05 16:30  0.01
2015-02-05 16:31  0

Does anybody have any general guidance on this? I would be much appreciative.

Upvotes: 1

Views: 1074

Answers (1)

G. Grothendieck
G. Grothendieck

Reputation: 269461

Read the data frame into a zoo object and calculate the times truncated to the minute, cumsum'ing over the values by minute. Then calculate a sequence of times from one minute before to one minute after the times in our data, remove any times already in the data and merge it filling with zeros. If you don't really need the added zero times then stop after computing zcum:

library(zoo)

z <- read.zoo(df, tz = "")
mins <- trunc(time(z), "mins")
zcum <- ave(z, mins, FUN = cumsum)

rng <- range(mins)
tt <- seq(rng[1] - 60, rng[2] + 60, by = "mins")
tt <- tt[ ! format(tt) %in% format(mins) ]
merge(zcum, zoo(, tt), fill = 0)

giving:

2015-02-05 16:27:00 2015-02-05 16:28:38 2015-02-05 16:29:36 2015-02-05 16:29:41 
               0.00                0.01                0.01                0.02 
2015-02-05 16:30:00 2015-02-05 16:31:00 
               0.01                0.00

Upvotes: 1

Related Questions