Retrieve time windows • thipR

Guide index | Guide 3 of 5

This guide is standalone and uses invented records. A window joins a person’s records to a specific entry’s dates. It does not flag disease or decide eligibility.

1. Identify the source and entries

library(thipR)
library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
visits <- data.frame(
  patient = c("A", "A", "A", "B"),
  visit_date = as.Date(c("2020-01-01", "2020-01-10", "2020-01-11", "2020-01-10")),
  diagnosis = c("J06", "K35", "E11", "J06")
)
entries <- data.frame(
  pid = c("A", "A", "C"),
  entry_id = c("A-first", "A-second", "C-first"),
  index_date = as.Date(c("2020-01-10", "2020-01-11", "2020-01-10"))
)

One person can have several entries, but entry_id must uniquely identify each entry. Here source patient and cohort pid have different names; the mapping is explicitly source name = cohort name. Keys must have compatible types.

2. Plan a relative window, then collect

plan <- thip_window(
  visits, entries,
  by = c(patient = "pid"), entry = "entry_id",
  date = "visit_date", index = "index_date", window = c(-9L, 1L)
)
plan
#> THIP window plan: 3 entries; 2 people
#> Match source patient to cohort pid; record date visit_date; bounds [)
#> Execution pending. Use dplyr::collect(); source records have not been read.
records <- collect(plan, batch_size = 1L)
records
#>   patient visit_date diagnosis entry_id
#> 1       A 2020-01-01       J06  A-first
#> 2       A 2020-01-10       K35  A-first
#> 3       A 2020-01-10       K35 A-second
#> 4       A 2020-01-11       E11 A-second

date names the source record date; index names the cohort reference date. Both must be R Date values (or a supported SQL date source), not timestamps. The default [) includes the lower boundary and excludes the upper one. For A-first, 1–10 January match; 11 January does not. A record may match both of A’s windows. Original source columns remain unchanged; the result adds entry_id. Duplicates in the source are preserved, not silently deduplicated.

window means integer day offsets, not calendar months/years. Other bounds are [], (], and (). Missing source IDs/dates do not match. Invalid cohort IDs/dates raise an error.

3. Use explicit dates for study-end follow-up

followup_entries <- entries |>
  mutate(start_date = index_date, end_date = as.Date("2020-02-01"))
followup <- thip_window(
  visits, followup_entries, by = c(patient = "pid"), date = "visit_date",
  start = "start_date", end = "end_date"
) |>
  collect()
followup
#>   patient visit_date diagnosis entry_id
#> 1       A 2020-01-10       K35  A-first
#> 2       A 2020-01-11       E11  A-first
#> 3       A 2020-01-11       E11 A-second

Use either explicit start/end columns or relative index/window arguments. An end date can be years later; annual extraction must not break person timelines.

4. Keep entries with no matching records

matched <- records |> count(entry_id, name = "matched_rows")
roster <- entries |>
  left_join(matched, by = "entry_id") |>
  mutate(matched_rows = coalesce(matched_rows, 0L))
roster
#>   pid entry_id index_date matched_rows
#> 1   A  A-first 2020-01-10            2
#> 2   A A-second 2020-01-11            2
#> 3   C  C-first 2020-01-10            0

C has no source records but stays in the entry roster. Zero matches do not prove no disease or adequate observation. These are matched row counts, not distinct admission counts. Overlap and source duplicates matter if you count encounters.

Remote execution and batching

With a lazy THIP source, the connection is inherited from that source. Apply ordinary select() and filter() before thip_window(): the resulting plan is not a general dbplyr table. Printing a plan does not fetch rows; collect() executes it. No temporary DB tables are created.

batch_size counts distinct people, keeping all windows for one person together. It is not a row limit. Changing it does not change match semantics. All returned rows still occupy R memory; this is not disk streaming. Oversized SQL batches fail with guidance to reduce the batch size; failed queries do not return a partial successful result. For diagnostics, inspect attr(records, "window_report").

Next: flag conditions locally.