5 minutes time interval up to 15 minutes average time interval

Question

I have a data frame as below with a 5 minute interval:

df Time P_21 P_22P_23P_24 1/1/2014 0:00 50 60 40 30 1/1/2014 0:05 100 120 80 60 1/1/2014 0:10 150 180 120 90 1/1/2014 0:15 45 52 36 32 1/1/2014 0:20 90 104 72 64 1/1/2014 0:25 135 156 108 96 1/1/2014 0:30 42 56 39 31 1/1/2014 0:35 84 112 78 62 1/1/2014 0:40 126 168 117 93 1/1/2014 0:45 50 60 40 30 1/1/2014 0:50 50 60 40 30 1/1/2014 0:55 50 60 40 30 1/1/2014 1:00 50 60 40 30 1/1/2014 1:05 50 60 40 30 

I want to make a 15 minute interval with its average value

 Time P_21P_22P_23P_24 1/1/2014 0:00 100 120 80 60 1/1/2014 0:15 90 104 72 64 1/1/2014 0:30 84 112 78 62 1/1/2014 0:45 50 60 40 30 1/1/2014 1:00 continue continue continue continue 

It will take an average of 00.05 and 10 (3 points). Please help me solve this problem.

Answer 1

Using the xts package to read your data as a real time series:

 library(xts) dx <- read.zoo(text='1/1/2014 0:00 50 60 40 30 1/1/2014 0:05 100 120 80 60 1/1/2014 0:10 150 180 120 90 1/1/2014 0:15 45 52 36 32 1/1/2014 0:20 90 104 72 64 1/1/2014 0:25 135 156 108 96 1/1/2014 0:30 42 56 39 31 1/1/2014 0:35 84 112 78 62 1/1/2014 0:40 126 168 117 93 1/1/2014 0:45 50 60 40 30 1/1/2014 0:50 50 60 40 30 1/1/2014 0:55 50 60 40 30 1/1/2014 1:00 50 60 40 30 1/1/2014 1:05 50 60 40 30',index=1:2,tz='',format="%d/%m/%Y %H:%M") 

Then a convenient period.apply to aggregate your ts for each time period:

  period.apply(dx,endpoints(dx,on = "mins",k=15),mean) # V3 V4 V5 V6 # 2014-01-01 00:10:00 100 120 80 60 # 2014-01-01 00:25:00 90 104 72 64 # 2014-01-01 00:40:00 84 112 78 62 # 2014-01-01 00:55:00 50 60 40 30 # 2014-01-01 01:05:00 50 60 40 30 

Answer 2

Here's an alternative using the data.table package

 library(data.table) setDT(df)[, Time := Time[1L], by = cumsum(as.POSIXlt(Time, format = "%m/%d/%Y %H:%M")$min %% 15 == 0)] df[, lapply(.SD, mean), by = Time] # Time P_21 P_22 P_23 P_24 # 1: 1/1/2014 0:00 100 120 80 60 # 2: 1/1/2014 0:15 90 104 72 64 # 3: 1/1/2014 0:30 84 112 78 62 # 4: 1/1/2014 0:45 50 60 40 30 # 5: 1/1/2014 1:00 50 60 40 30