Create sequential datetimes in a data frame

Suppose you have a longitudinal data frame which contains non-sequential measurements over time. This function will create sequential datetimes by filling in the missing rows of datetimes (from the minimum datetime to the maximum datetime) on a minute, hour, day, week, month, quarter, or year basis. It will summarize variables recorded at a higher resolution than the chosen datetime basis (e.g. go from seconds to minute, minutes to hourly, etc.). The basic process will create a template dataframe of sequential datetimes, then join your original dataframe to this template.

Usage

date_seq(
  df,
  date,
  group,
  unit,
  summarize = list(numeric = function(x) {
     mean(x, na.rm = TRUE)
 }, character =
    function(x) {
     dplyr::first(x)
 }, factor = function(x) {
     dplyr::first(x)

    }, logical = function(x) {
     dplyr::first(x)
 }, date = function(x) {
     min(x,
    na.rm = TRUE)
 })
)

Arguments

df: A longitudinal data frame.
date: A string for the name of the datetime variable.
group: A character vector of grouping variables (such as subject ID).
unit: A string for the sequential time basis. An element of "second", "minute", "hour", "day", "week", "month", "quarter", and "year".
summarize: A named list of functions to use for summarization. Applies for the case when date is duplicated or when rounding to a lower resolution time unit.

Value

data.frame

Examples

#----------------------------------------------------------------------------
# date_seq() examples.
#----------------------------------------------------------------------------
library(bkmisc)

data <- data.frame(
  subject = c(1, 1, 1, 1, 2, 2, 2, 2),
  date = c(
    as.Date("2022-01-01"), as.Date("2022-01-01"), as.Date("2022-01-03"), as.Date("2022-01-08"),
    as.Date("2022-01-01"), as.Date("2022-01-01"), as.Date("2022-01-04"), as.Date("2022-01-06")
  ),
  character = letters[1:8],
  factor = factor(c("A", "B", "A", "B", "B", "A", "A", "B")),
  logical = c(TRUE, FALSE, TRUE, FALSE, FALSE, TRUE, TRUE, FALSE),
  numeric = c(10, 20, 30, 40, 15, 30, 45, 55),
  date_other = c(
    as.Date("2022-06-01"), as.Date("2022-06-02"), as.Date("2022-06-03"), as.Date("2022-06-04"),
    as.Date("2022-06-01"), as.Date("2022-06-02"), as.Date("2022-06-03"), as.Date("2022-06-04")
  )
)

date_seq(df = data, date = "date", group = "subject", unit = "day")
#>    subject       date character factor logical numeric date_other
#> 1        1 2022-01-01         a      A    TRUE    15.0 2022-06-01
#> 2        1 2022-01-02      <NA>   <NA>      NA      NA       <NA>
#> 3        1 2022-01-03         c      A    TRUE    30.0 2022-06-03
#> 4        1 2022-01-04      <NA>   <NA>      NA      NA       <NA>
#> 5        1 2022-01-05      <NA>   <NA>      NA      NA       <NA>
#> 6        1 2022-01-06      <NA>   <NA>      NA      NA       <NA>
#> 7        1 2022-01-07      <NA>   <NA>      NA      NA       <NA>
#> 8        1 2022-01-08         d      B   FALSE    40.0 2022-06-04
#> 9        2 2022-01-01         e      B   FALSE    22.5 2022-06-01
#> 10       2 2022-01-02      <NA>   <NA>      NA      NA       <NA>
#> 11       2 2022-01-03      <NA>   <NA>      NA      NA       <NA>
#> 12       2 2022-01-04         g      A    TRUE    45.0 2022-06-03
#> 13       2 2022-01-05      <NA>   <NA>      NA      NA       <NA>
#> 14       2 2022-01-06         h      B   FALSE    55.0 2022-06-04