15 Minute Period for Time Series

Question

I have the chunk of code below where I am trying to fill the missing minutes in my data df_stuff by joining it to a time series which has all minutes for an entire year. I would actually like to aggregate this data at 15 minute intervals instead of minute. Does anyone know a simple way of doing this? I was looking at to.minutes15 from the xts package but it seems to have problems with my POSIXct format time series.

Code:

library("sqldf")

##Filling Gaps in time by minute
myTZ <- "America/Los_Angeles"
tseries <- seq(as.POSIXct("2015-01-01 00:00:00", tz=myTZ),
               as.POSIXct("2015-12-31 23:59:00", tz=myTZ), by="min")

df2 <- data.frame(SeqDateTime=tseries)
finaldf <- sqldf("select df2.SeqDateTime,
                  median(df_stuff.brooms) as broomsTot
                  from df2
                  left outer join df_stuff on df2.SeqDateTime = df_stuff.broomTime
                  group by df2.SeqDateTime
                  order by df2.SeqDateTime asc")

Data:

df_stuff <- structure(list(brooms = c(27, 53, 10, 55, 14, 49, 26, 
13, 12, NA, NA, 23, 28, 31, NA, 46, NA, 13, NA, 33, 12, 4, 28, 
34, 0, 24, 7, 31, 33, 37, 56, 41, 50, 55, 41, 15, 23, 26, 14, 
27, 22, 41, 48, 19, 28, 11, 11, NA, 49, NA), broomTime =   structure(c(1423970100, 
1424122200, 1424136180, 1424035260, 1424141580, 1424122440, 1423274580, 
1424129580, 1424146320, 1429129320, 1429032060, 1429142940, 1428705000, 
1429142460, 1429128720, 1429204560, 1422909480, 1424137200, 1424042100, 
1424149620, 1424131920, 1424108940, 1424144820, 1424040600, 1424119620, 
1424148660, 1443593040, 1443657120, 1424125860, 1424223120, 1424235240, 
1424232720, 1424234940, 1424234640, 1424230440, 1424115300, 1429208280, 
1429131720, 1429148460, 1429151040, 1424129760, 1424125380, 1424123220, 
1424137380, 1424115780, 1424219340, 1424131560, 1424233560, 1424224920, 
1443640800), class = c("POSIXct", "POSIXt"), tzone = "")), .Names =   c("brooms", 
"broomTime"), row.names = c(NA, 50L), class = "data.frame")

Answer 1

You can summarize by any amount of time interval by using cut within the group_by function in dplyr.

library(dplyr)
ans <- finaldf %>% 
         group_by(SeqDateTime = cut(SeqDateTime, breaks = "15 min"))  %>% 
         summarize(broomsTot = sum(as.numeric(broomsTot), na.rm = TRUE))

head(ans)
Source: local data frame [6 x 2]

          SeqDateTime broomsTot
               (fctr)     (dbl)
1 2015-01-01 02:00:00         0
2 2015-01-01 02:15:00         0
3 2015-01-01 02:30:00         0
4 2015-01-01 02:45:00         0
5 2015-01-01 03:00:00         0
6 2015-01-01 03:15:00         0

Answer 2

I can assure you that xts does not have problem with your POSIXct time series. xts uses POSIXct for its internal time index.

Here's how to join df_stuff with a 1-minute series and then aggregate that result to a 15-minute series.

library(xts)
# create xts object
xts_stuff <- with(df_stuff, xts(brooms, broomTime))
# merge with empty xts object that contains a regular 1-minute index
xts_stuff_1min <- merge(xts_stuff, xts(,tseries))
# aggregate to 15-minutes
ep15 <- endpoints(xts_stuff_1min, "minutes", 15)
final_df <- period.apply(xts_stuff_1min, ep15, median, na.rm=TRUE)