Getting Mean of all aggregated values for every quarter hour in dataframe and assigning

Question

I have some sampled data from a sensor with no particular time differences between samples looking like this:

> Y_cl[[1]]
     index       Date     time Glucose                 POS
10      11 2017-06-10 03:01:00     136 2017-06-10 00:01:00
14      15 2017-06-10 03:06:00     132 2017-06-10 00:06:00
18      19 2017-06-10 03:11:00     133 2017-06-10 00:11:00
22      23 2017-06-10 03:16:00     130 2017-06-10 00:16:00
26      27 2017-06-10 03:20:59     119 2017-06-10 00:20:59
30      31 2017-06-10 03:26:00     115 2017-06-10 00:26:00
34      35 2017-06-10 03:30:59     117 2017-06-10 00:30:59
38      39 2017-06-10 03:36:00     114 2017-06-10 00:36:00
42      43 2017-06-10 03:40:59     113 2017-06-10 00:40:59

The data is saved in the format of Dataframes stored in list Y_cl, each list element is for one day. I am trying to select ALL samples between every quarter hour of the clock and get the mean, resulting in 4 points for each hour of each day, mathematically defined (NOT CODE) as:

mean(Glucose(H:00 <Y_cl[[1]]$time< H:15))==> Glucose_av(H:00), 
mean(Glucose(H:15 <Y_cl[[1]]$time< H:30))==> Glucose_av(H:15), 
mean(Glucose(H:30 <Y_cl[[1]]$time< H:45))==> Glucose_av(H:30), 
mean(Glucose(H:45 <Y_cl[[1]]$time< (H+1):00))==>Glucose_av(H:45)

I have tried searching but have found links on how to select or cut every 15 minutes differences, while I need to group every hours data based on which quarter of the hour they are in, average, and assign the result to corresponding quarter. Y_cl[[1]]['POS'] is in standard POSIXct format. Any help would be appreciated.

Answer 1

I did it by flooring the result of the minutes of each time stamp divided by 15, where YPOS is the list within the time stamps for each day i with the list Y_cl exist:

SeI<- function(i){

*###seperate the hours from the minutes for use later and store in K1*
strftime(YPOS[[i]], format="%H")

K1<- (floor((as.numeric(strftime(YPOS[[i]], format="%M")))/15))*15

*###get the minutes and divide by 15, keeping the floor,multiplying by 15,store in K2*
K2<- strftime(YPOS[[i]], format="%Y-%m-%d %H", tz="GMT")

*###paste K1 and K2 together an save in POSTIXCT format as T_av*
TT<- paste0(K2, ':', K1)
T_av<- as.POSIXct(TT,format="%Y-%m-%d %H:%M", tz="GMT" )}

and then applying it over all days in the list:

lapply(1:length(Y_cl), function(i) SeI(i) )

My solution included taking the time stamps from the list Y_cl and saving it in YPOS.

Answer 2

Here is a solution using lubridate and plyr packages :

data$POS <- NULL
data$POS = as.POSIXct(paste(data$Date, data$time)) # POS correction
library(lubridate)
library(plyr)

data$day <- day(data$POS) # extract day
data$hour <- hour(data$POS) # extract hour
data$minute <- minute(data$POS) # extract minute

Create a new factor according to the quarter :

data$quarter <- NA
data$quarter[data$minute >= 0 &  data$minute < 15] <- "q1" # 1st quarter
data$quarter[data$minute >= 15 &  data$minute < 30] <- "q2" # 2ndquarter
data$quarter[data$minute >= 30 &  data$minute < 45] <- "q3" # 3rd quarter
data$quarter[data$minute >= 45 &  data$minute < 60] <- "q4" # 4th quarter

Summarize data for each quarter (compute mean of Glucose for each combination of day, hour and quarter) :

output <- ddply(data, c("day", "hour", "quarter"), summarise, result = mean(Glucose)) 

Result :

> output
  day hour quarter   result
1  10    3      q1 133.6667
2  10    3      q2 121.3333
3  10    3      q3 114.6667