Skip to contents

Merge Two Data Frames by Closest Time

Source: R/Merge_ByClosestTime.R
Merge_ByClosestTime.Rd

This function merges two data frames based on the closest time in the specified time columns. It optionally merges using one or more matching variables (e.g., IDs). The resulting merged data frame contains the closest time matches and time differences.

Usage

Merge_ByClosestTime(
  DataFrame1,
  DataFrame2,
  TimeVar1,
  TimeVar2,
  keys = NULL,
  is_date = FALSE,
  MergeBy = lifecycle::deprecated()
)

Arguments

DataFrame1

A data frame containing the first set of data.

DataFrame2

A data frame containing the second set of data.

TimeVar1

The name of the time variable in DataFrame1 (as a string).

TimeVar2

The name of the time variable in DataFrame2 (as a string).

keys

Optional. Character vector of variable(s) to merge by. Must exist in BOTH data frames and be in the same order.

is_date

Logical. Indicates whether the time variables are dates (TRUE) or POSIXct (FALSE).

MergeBy

Deprecated (since 19.15.0). Use keys instead.

Value

A list with:

merged_dataframe

Data frame with closest time matches

time_differences

Vector of time differences

Checking the match

A merge like this always produces a match, however far away it had to reach, so the time gaps are what decide whether a match is usable. The sign gives the direction: positive means the second record came after the first.

In the example, three participants are matched within two weeks, while the fourth has only a lab predating the visit by more than seven months - that row should be dropped rather than analyzed as if the two measurements were contemporaneous. Always inspect time_differences before using the merged data.

Examples

# Clinic visits with blood pressure
visits <- data.frame(
  id         = c("A", "B", "C", "D"),
  visit_date = as.Date(c("2024-03-01", "2024-05-20", "2024-02-10",
                         "2024-04-01")),
  sbp        = c(120, 135, 128, 142)
)

# Lab draws (multiple per participant, on different dates)
labs <- data.frame(
  id         = c("A", "A", "B", "B", "C", "D"),
  lab_date   = as.Date(c("2024-01-05", "2024-03-10", "2024-01-20",
                         "2024-06-01", "2024-02-15", "2023-08-15")),
  creatinine = c(0.9, 1.1, 0.8, 1.0, 1.2, 1.4)
)

ShowTable <- function(x, caption = NULL) {
  htmltools::browsable(htmltools::HTML(as.character(
    kableExtra::kable_styling(
      knitr::kable(x, format = "html", caption = caption),
      bootstrap_options = c("striped", "hover", "condensed"),
      full_width = FALSE
    )
  )))
}

# The two tables do not line up
ShowTable(visits, "Clinic visits")

Clinic visits
 
 id 
    visit_date 
    sbp 
  
 A 
    2024-03-01 
    120 
  
 B 
    2024-05-20 
    135 
  
 C 
    2024-02-10 
    128 
  
 D 
    2024-04-01 
    142 
  
ShowTable(labs, "Lab draws")

Lab draws
 
 id 
    lab_date 
    creatinine 
  
 A 
    2024-01-05 
    0.9 
  
 A 
    2024-03-10 
    1.1 
  
 B 
    2024-01-20 
    0.8 
  
 B 
    2024-06-01 
    1.0 
  
 C 
    2024-02-15 
    1.2 
  
 D 
    2023-08-15 
    1.4 
  

# For each visit, attach the lab drawn closest in time (within participant)
res <- Merge_ByClosestTime(
  visits, labs,
  TimeVar1 = "visit_date",
  TimeVar2 = "lab_date",
  keys = "id",
  is_date = TRUE
)

# One row per visit, with the nearest lab attached
ShowTable(res$merged_dataframe, "Visits with nearest lab")

Visits with nearest lab
 
 id 
    visit_date 
    sbp 
    lab_date 
    creatinine 
  
 A 
    2024-03-01 
    120 
    2024-03-10 
    1.1 
  
 B 
    2024-05-20 
    135 
    2024-06-01 
    1.0 
  
 C 
    2024-02-10 
    128 
    2024-02-15 
    1.2 
  
 D 
    2024-04-01 
    142 
    2023-08-15 
    1.4 
  

# The gap for each match, which decides whether it is usable
ShowTable(
  data.frame(
    id = res$merged_dataframe$id,
    visit_date = res$merged_dataframe$visit_date,
    DaysToNearestLab = as.numeric(res$time_differences)
  ),
  "Time gap between each visit and its matched lab"
)

Time gap between each visit and its matched lab
 
 id 
    visit_date 
    DaysToNearestLab 
  
 A 
    2024-03-01 
    9 
  
 B 
    2024-05-20 
    12 
  
 C 
    2024-02-10 
    5 
  
 D 
    2024-04-01 
    -230