Summarizing time series data

Question

I have a data file that is two columns, Date, and Time which I have converted to a date and time in R and a glucose column (example below). The data are provided every 5 minutes and I am trying to get an average over 24 hours and then from 11pm-6am and 6am-11pm.

I cannot figure out how to write code to get this data. I tried the apply.daily syntax to get the 24 hour average but its giving me an error.

Sample of data:

Datetime            Glucose
2018-03-07 23:01:04 154
2018-03-07 23:06:04 235
2018-03-07 23:11:04 232
2018-03-07 23:16:04 144
2018-03-07 23:21:04 134
2018-03-07 23:26:04 107
2018-03-07 23:31:04 108
2018-03-07 23:36:04 122
2018-03-07 23:41:04 143
2018-03-07 23:46:04 113
2018-03-07 23:51:04 115
2018-03-07 23:56:04 116
2018-03-08 00:01:04 117
2018-03-08 00:06:04 117
2018-03-08 00:11:04 114
2018-03-08 00:16:04 109

Answer 1

data.table approach (with custom sample data)

You probably will have to change the code defining the period, since you (i guess?) want the periode 23-06 to use 23 untill 06 the next day....

sample data

library( data.table )

#create sample data
dt <- fread("Datetime            Glucose
2018-03-07T22:01:04 154
2018-03-07T22:06:04 235
2018-03-07T22:11:04 232
2018-03-07T23:16:04 144
2018-03-07T23:21:04 134
2018-03-07T3:26:04 107
2018-03-07T23:31:04 108
2018-03-07T23:36:04 122
2018-03-07T23:41:04 143
2018-03-07T23:46:04 113
2018-03-07T23:51:04 115
2018-03-07T23:56:04 116
2018-03-08T00:01:04 117
2018-03-08T00:06:04 117
2018-03-08T00:11:04 114
2018-03-08T00:16:04 109", header = TRUE)
dt[ , Datetime := as.POSIXct( Datetime, format = "%Y-%m-%dT%H:%M:%S" ) ]

code

#create period 6-23 and 23-6
dt[ , period := ifelse( hour( Datetime ) >= 23 | hour( Datetime ) < 6 , "eleven-six", "six-eleven" )]

#daily mean
dt[, .( mean.Glucose = mean( Glucose) ), by = .( day = as.Date( Datetime, tz = "" ) ) ][]
#           day mean.Glucose
# 1: 2018-03-07     143.5833
# 2: 2018-03-08     114.2500

#mean per period
dt[, .( mean.Glucose = mean( Glucose) ), by = .( day = as.Date( Datetime, tz = "" ), period ) ][]
#           day     period mean.Glucose
# 1: 2018-03-07 six-eleven     207.0000
# 2: 2018-03-07 eleven-six     122.4444
# 3: 2018-03-08 eleven-six     114.2500

Answer 2

You want to look into the lubridate package. Here is a tidyverse approach using lubridate for various items.

1. Use ymd_hms to convert to times.
2. Use day and hour to create grouping categories to summarize on.

library(tidyverse)
library(lubridate)

df <- tribble(~date_time, ~glucose,
"2018-03-07 23:01:04",             154,
"2018-03-07 23:06:04",             235,
"2018-03-07 23:11:04",             232,
"2018-03-07 23:16:04",             144,
"2018-03-07 23:21:04",             134,
"2018-03-07 23:26:04",             107,
"2018-03-07 23:31:04",             108,
"2018-03-07 23:36:04",             122,
"2018-03-07 23:41:04",             143,
"2018-03-07 23:46:04",             113,
"2018-03-07 23:51:04",             115,
"2018-03-07 23:56:04",             116,
"2018-03-08 00:01:04",             117,
"2018-03-08 00:06:04",             117,
"2018-03-08 00:11:04",             114,
"2018-03-08 00:16:04",             109)


## Get daily average glucose
df %>% 
  mutate(date_time = ymd_hms(date_time),
         day = day(date_time)) %>% 
  group_by(day) %>% 
  summarize(mean_glucose = mean(glucose))

#> # A tibble: 2 x 2
#>     day mean_glucose
#>   <int>        <dbl>
#> 1     7         144.
#> 2     8         114.

## Get 11pm-6am and 6am-11pm averages
df %>% 
  mutate(date_time = ymd_hms(date_time),
         hour = hour(date_time),
         range = if_else(between(hour, 06, 23), "6am - 11pm", "11pm - 6am")) %>% 
  group_by(range) %>% 
  summarize(mean_glucose = mean(glucose))

#> # A tibble: 2 x 2
#>   range      mean_glucose
#>   <chr>             <dbl>
#> 1 11pm - 6am         114.
#> 2 6am - 11pm         144.

^{Created on 2019-01-02 by the reprex package (v0.2.1)}