Adding table intersections

So far we’ve seen that we can add variables indicating intersections based on cohorts or concept sets. One additional option we have is to simply add an intersection based on a table.

In this example, CohortConstructor is used to create the ankle sprain cohort. PatientProfiles then adds table intersections to the resulting valid cohort table; it does not create or modify cohort definitions.

Let’s again create a cohort containing people with an ankle sprain.

library(CodelistGenerator)
library(PatientProfiles)
library(dplyr)
library(CohortConstructor)
library(ggplot2)
library(omock)

cdm <- mockCdmFromDataset(datasetName = "GiBleed", source = "duckdb")

cdm$ankle_sprain <- conceptCohort(
  cdm = cdm,
  name = "ankle_sprain",
  conceptSet = list("ankle_sprain" = 81151)
)
cdm$ankle_sprain
#> # A query:  ?? x 4
#> # Database: DuckDB 1.5.6 [unknown@Linux 7.0.0-1017-azure:R 4.6.1//tmp/Rtmp0lxNSi/file18a337655ead.duckdb]
#>    cohort_definition_id subject_id cohort_start_date cohort_end_date
#>                   <int>      <int> <date>            <date>         
#>  1                    1       1611 2013-11-29        2013-12-20     
#>  2                    1       1796 2016-05-01        2016-06-05     
#>  3                    1       3843 2007-11-19        2007-12-10     
#>  4                    1       4532 1998-08-17        1998-09-07     
#>  5                    1        564 2006-08-19        2006-09-09     
#>  6                    1       1097 2012-02-24        2012-03-09     
#>  7                    1       1361 1974-07-02        1974-07-30     
#>  8                    1       2002 1976-04-06        1976-05-04     
#>  9                    1       4395 1982-07-10        1982-07-24     
#> 10                    1       4872 1960-09-20        1960-10-11     
#> # ℹ more rows

cdm$ankle_sprain |>
  addTableIntersectFlag(
    tableName = "condition_occurrence",
    window = c(-30, -1)
  ) |>
  glimpse()
#> Rows: ??
#> Columns: 5
#> $ cohort_definition_id           <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, …
#> $ subject_id                     <int> 3694, 3490, 97, 1486, 4847, 4847, 5165,…
#> $ cohort_start_date              <date> 1993-04-26, 1954-11-24, 1992-09-04, 19…
#> $ cohort_end_date                <date> 1993-05-24, 1954-12-29, 1992-10-09, 19…
#> $ condition_occurrence_m30_to_m1 <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, …

We can use table intersection functions to check whether someone had a record in the drug exposure table in the 30 days before their ankle sprain. If we set targetStartDate to “drug_exposure_start_date” and targetEndDate to “drug_exposure_end_date” we are checking whether an individual had an ongoing drug exposure record in the window.

cdm$ankle_sprain |>
  addTableIntersectFlag(
    tableName = "drug_exposure",
    indexDate = "cohort_start_date",
    targetStartDate = "drug_exposure_start_date",
    targetEndDate = "drug_exposure_end_date",
    window = c(-30, -1)
  ) |>
  glimpse()
#> Rows: ??
#> Columns: 5
#> $ cohort_definition_id    <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…
#> $ subject_id              <int> 564, 4942, 97, 3064, 5165, 3869, 404, 1992, 14…
#> $ cohort_start_date       <date> 2006-08-19, 1998-04-16, 1992-09-04, 1997-01-3…
#> $ cohort_end_date         <date> 2006-09-09, 1998-05-21, 1992-10-09, 1997-03-0…
#> $ drug_exposure_m30_to_m1 <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…

Meanwhile if we set we set targetStartDate to “drug_exposure_start_date” and targetEndDate to “drug_exposure_start_date” we will instead be checking whether they had a drug exposure record that started during the window.

cdm$ankle_sprain |>
  addTableIntersectFlag(
    tableName = "drug_exposure",
    indexDate = "cohort_start_date",
    window = c(-30, -1)
  ) |>
  glimpse()
#> Rows: ??
#> Columns: 5
#> $ cohort_definition_id    <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…
#> $ subject_id              <int> 564, 4942, 97, 3064, 5165, 3869, 404, 1992, 14…
#> $ cohort_start_date       <date> 2006-08-19, 1998-04-16, 1992-09-04, 1997-01-3…
#> $ cohort_end_date         <date> 2006-09-09, 1998-05-21, 1992-10-09, 1997-03-0…
#> $ drug_exposure_m30_to_m1 <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…

As before, instead of a flag, we could also add count, date, or days variables.

cdm$ankle_sprain |>
  addTableIntersectCount(
    tableName = "drug_exposure",
    indexDate = "cohort_start_date",
    window = c(-180, -1)
  ) |>
  glimpse()
#> Rows: ??
#> Columns: 5
#> $ cohort_definition_id     <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, …
#> $ subject_id               <int> 1611, 1796, 564, 2002, 4872, 2176, 5172, 1334…
#> $ cohort_start_date        <date> 2013-11-29, 2016-05-01, 2006-08-19, 1976-04-…
#> $ cohort_end_date          <date> 2013-12-20, 2016-06-05, 2006-09-09, 1976-05-…
#> $ drug_exposure_m180_to_m1 <dbl> 1, 1, 1, 1, 1, 2, 1, 2, 1, 1, 1, 1, 1, 1, 2, …

cdm$ankle_sprain |>
  addTableIntersectDate(
    tableName = "drug_exposure",
    indexDate = "cohort_start_date",
    order = "last",
    window = c(-180, -1)
  ) |>
  glimpse()
#> Rows: ??
#> Columns: 5
#> $ cohort_definition_id     <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, …
#> $ subject_id               <int> 1611, 1796, 564, 2002, 4872, 2176, 5172, 1334…
#> $ cohort_start_date        <date> 2013-11-29, 2016-05-01, 2006-08-19, 1976-04-…
#> $ cohort_end_date          <date> 2013-12-20, 2016-06-05, 2006-09-09, 1976-05-…
#> $ drug_exposure_m180_to_m1 <date> 2013-08-11, 2015-11-28, 2006-07-20, 1975-11-…


cdm$ankle_sprain |>
  addTableIntersectDate(
    tableName = "drug_exposure",
    indexDate = "cohort_start_date",
    order = "last",
    window = c(-180, -1)
  ) |>
  glimpse()
#> Rows: ??
#> Columns: 5
#> $ cohort_definition_id     <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, …
#> $ subject_id               <int> 1611, 1796, 564, 2002, 4872, 2176, 5172, 1334…
#> $ cohort_start_date        <date> 2013-11-29, 2016-05-01, 2006-08-19, 1976-04-…
#> $ cohort_end_date          <date> 2013-12-20, 2016-06-05, 2006-09-09, 1976-05-…
#> $ drug_exposure_m180_to_m1 <date> 2013-08-11, 2015-11-28, 2006-07-20, 1975-11-…

In these examples we’ve been adding intersections using the entire drug exposure concept table. However, we could have subsetted it before adding our table intersection. For example, let’s say we want to add a variable for acetaminophen use among our ankle sprain cohort. As we’ve seen before we could use a cohort or concept set for this, but now we have another option - subset the drug exposure table down to acetaminophen records and add a table intersection.

acetaminophen_cs <- getDrugIngredientCodes(
  cdm = cdm,
  name = c("acetaminophen")
)

cdm$acetaminophen_records <- cdm$drug_exposure |>
  filter(drug_concept_id %in% !!acetaminophen_cs[[1]]) |>
  compute()

cdm$ankle_sprain |>
  addTableIntersectFlag(
    tableName = "acetaminophen_records",
    indexDate = "cohort_start_date",
    targetStartDate = "drug_exposure_start_date",
    targetEndDate = "drug_exposure_end_date",
    window = c(-Inf, Inf)
  ) |>
  glimpse()
#> Rows: ??
#> Columns: 5
#> $ cohort_definition_id              <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, …
#> $ subject_id                        <int> 1611, 1796, 3843, 4532, 564, 1097, 1…
#> $ cohort_start_date                 <date> 2013-11-29, 2016-05-01, 2007-11-19,…
#> $ cohort_end_date                   <date> 2013-12-20, 2016-06-05, 2007-12-10,…
#> $ acetaminophen_records_minf_to_inf <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, …

Beyond this table intersection provides a means if implementing a wide range of custom analyses. One more example to show this is provided below, where we check whether individuals have a measurement or procedure record on the date of their ankle sprain.

cdm$proc_or_meas <- union_all(
  cdm$procedure_occurrence |>
    select("person_id",
      "record_date" = "procedure_date"
    ),
  cdm$measurement |>
    select("person_id",
      "record_date" = "measurement_date"
    )
) |>
  compute()

cdm$ankle_sprain |>
  addTableIntersectFlag(
    tableName = "proc_or_meas",
    indexDate = "cohort_start_date",
    targetStartDate = "record_date",
    targetEndDate = "record_date",
    window = c(0, 0)
  ) |>
  glimpse()
#> Rows: ??
#> Columns: 5
#> $ cohort_definition_id <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…
#> $ subject_id           <int> 1611, 1796, 3843, 4532, 564, 1097, 1361, 2002, 43…
#> $ cohort_start_date    <date> 2013-11-29, 2016-05-01, 2007-11-19, 1998-08-17, …
#> $ cohort_end_date      <date> 2013-12-20, 2016-06-05, 2007-12-10, 1998-09-07, …
#> $ proc_or_meas_0_to_0  <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…