5 minutes interval time to 15 minutes time interval mean data

Question

I have a data frame as mentioned bellow with 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 15 minute interval with its mean 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 mean value of 00,05 and 10 ( 3 datas). Please help me to solve this.

Answer 1

Using 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 the handy period.apply to aggregate your ts for each period of time:

 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