Adding table intersections

The hardware and bandwidth for this mirror is donated by dogado GmbH, the Webhosting and Full Service-Cloud Provider. Check out our Wordpress Tutorial.
If you wish to report a bug, or if you are interested in having us mirror your free-software or open-source project, please feel free to contact us at mirror[@]dogado.de.

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.

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

library(CDMConnector)
library(CodelistGenerator)
library(PatientProfiles)
library(dplyr)
library(ggplot2)

con <- DBI::dbConnect(duckdb::duckdb(),
  dbdir = CDMConnector::eunomia_dir()
)
cdm <- CDMConnector::cdm_from_con(con,
  cdm_schem = "main",
  write_schema = "main"
)

cdm <- generateConceptCohortSet(
  cdm = cdm,
  name = "ankle_sprain",
  conceptSet = list("ankle_sprain" = 81151),
  end = "event_end_date",
  limit = "all",
  overwrite = TRUE
)

cdm$ankle_sprain
#> # Source:   table<main.ankle_sprain> [?? x 4]
#> # Database: DuckDB v1.1.0 [root@Darwin 24.1.0:R 4.4.1//private/var/folders/pl/k11lm9710hlgl02nvzx4z9wr0000gp/T/RtmpMHXv6S/file117aa4e6fb786.duckdb]
#>    cohort_definition_id subject_id cohort_start_date cohort_end_date
#>                   <int>      <int> <date>            <date>         
#>  1                    1        239 1963-12-25        1964-01-08     
#>  2                    1        388 1965-06-18        1965-07-09     
#>  3                    1       1426 2013-10-27        2013-11-17     
#>  4                    1       1843 1992-04-21        1992-05-12     
#>  5                    1       2151 1926-05-31        1926-06-28     
#>  6                    1       2423 1987-11-30        1987-12-28     
#>  7                    1       2843 2001-07-06        2001-08-03     
#>  8                    1       3109 1971-02-09        1971-03-02     
#>  9                    1       3353 1943-10-19        1943-11-09     
#> 10                    1       3592 1973-12-11        1974-01-01     
#> # ℹ more rows

cdm$ankle_sprain |>
  addTableIntersectFlag(
    tableName = "condition_occurrence",
    window = c(-30, -1)
  ) |>
  tally()
#> # Source:   SQL [1 x 1]
#> # Database: DuckDB v1.1.0 [root@Darwin 24.1.0:R 4.4.1//private/var/folders/pl/k11lm9710hlgl02nvzx4z9wr0000gp/T/RtmpMHXv6S/file117aa4e6fb786.duckdb]
#>       n
#>   <dbl>
#> 1  1915

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
#> Database: DuckDB v1.1.0 [root@Darwin 24.1.0:R 4.4.1//private/var/folders/pl/k11lm9710hlgl02nvzx4z9wr0000gp/T/RtmpMHXv6S/file117aa4e6fb786.duckdb]
#> $ cohort_definition_id    <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…
#> $ subject_id              <int> 2843, 3820, 3509, 3047, 4121, 5170, 1335, 1568…
#> $ cohort_start_date       <date> 2001-07-06, 1989-08-16, 1943-01-11, 1989-04-2…
#> $ cohort_end_date         <date> 2001-08-03, 1989-09-20, 1943-02-01, 1989-05-2…
#> $ 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
#> Database: DuckDB v1.1.0 [root@Darwin 24.1.0:R 4.4.1//private/var/folders/pl/k11lm9710hlgl02nvzx4z9wr0000gp/T/RtmpMHXv6S/file117aa4e6fb786.duckdb]
#> $ cohort_definition_id    <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…
#> $ subject_id              <int> 2843, 3820, 3509, 3047, 4121, 5170, 1335, 1568…
#> $ cohort_start_date       <date> 2001-07-06, 1989-08-16, 1943-01-11, 1989-04-2…
#> $ cohort_end_date         <date> 2001-08-03, 1989-09-20, 1943-02-01, 1989-05-2…
#> $ 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
#> Database: DuckDB v1.1.0 [root@Darwin 24.1.0:R 4.4.1//private/var/folders/pl/k11lm9710hlgl02nvzx4z9wr0000gp/T/RtmpMHXv6S/file117aa4e6fb786.duckdb]
#> $ cohort_definition_id     <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, …
#> $ subject_id               <int> 239, 1426, 3820, 1211, 2846, 3330, 3509, 3047…
#> $ cohort_start_date        <date> 1963-12-25, 2013-10-27, 1989-08-16, 1966-12-…
#> $ cohort_end_date          <date> 1964-01-08, 2013-11-17, 1989-09-20, 1966-12-…
#> $ drug_exposure_m180_to_m1 <dbl> 2, 1, 1, 2, 2, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, …

cdm$ankle_sprain |>
  addTableIntersectDate(
    tableName = "drug_exposure",
    indexDate = "cohort_start_date",
    order = "last",
    window = c(-180, -1)
  ) |>
  glimpse()
#> Rows: ??
#> Columns: 5
#> Database: DuckDB v1.1.0 [root@Darwin 24.1.0:R 4.4.1//private/var/folders/pl/k11lm9710hlgl02nvzx4z9wr0000gp/T/RtmpMHXv6S/file117aa4e6fb786.duckdb]
#> $ cohort_definition_id     <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, …
#> $ subject_id               <int> 239, 1426, 1211, 2846, 3330, 3509, 3701, 1418…
#> $ cohort_start_date        <date> 1963-12-25, 2013-10-27, 1966-12-06, 1978-03-…
#> $ cohort_end_date          <date> 1964-01-08, 2013-11-17, 1966-12-20, 1978-04-…
#> $ drug_exposure_m180_to_m1 <date> 1963-10-21, 2013-05-14, 1966-10-29, 1977-10-…


cdm$ankle_sprain |>
  addTableIntersectDate(
    tableName = "drug_exposure",
    indexDate = "cohort_start_date",
    order = "last",
    window = c(-180, -1)
  ) |>
  glimpse()
#> Rows: ??
#> Columns: 5
#> Database: DuckDB v1.1.0 [root@Darwin 24.1.0:R 4.4.1//private/var/folders/pl/k11lm9710hlgl02nvzx4z9wr0000gp/T/RtmpMHXv6S/file117aa4e6fb786.duckdb]
#> $ cohort_definition_id     <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, …
#> $ subject_id               <int> 239, 1426, 1211, 2846, 3330, 3509, 3701, 1418…
#> $ cohort_start_date        <date> 1963-12-25, 2013-10-27, 1966-12-06, 1978-03-…
#> $ cohort_end_date          <date> 1964-01-08, 2013-11-17, 1966-12-20, 1978-04-…
#> $ drug_exposure_m180_to_m1 <date> 1963-10-21, 2013-05-14, 1966-10-29, 1977-10-…

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
#> Database: DuckDB v1.1.0 [root@Darwin 24.1.0:R 4.4.1//private/var/folders/pl/k11lm9710hlgl02nvzx4z9wr0000gp/T/RtmpMHXv6S/file117aa4e6fb786.duckdb]
#> $ cohort_definition_id              <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, …
#> $ subject_id                        <int> 239, 388, 1426, 1843, 2151, 2423, 28…
#> $ cohort_start_date                 <date> 1963-12-25, 1965-06-18, 2013-10-27,…
#> $ cohort_end_date                   <date> 1964-01-08, 1965-07-09, 2013-11-17,…
#> $ 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
#> Database: DuckDB v1.1.0 [root@Darwin 24.1.0:R 4.4.1//private/var/folders/pl/k11lm9710hlgl02nvzx4z9wr0000gp/T/RtmpMHXv6S/file117aa4e6fb786.duckdb]
#> $ cohort_definition_id <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…
#> $ subject_id           <int> 239, 388, 1426, 1843, 2151, 2423, 2843, 3109, 335…
#> $ cohort_start_date    <date> 1963-12-25, 1965-06-18, 2013-10-27, 1992-04-21, …
#> $ cohort_end_date      <date> 1964-01-08, 1965-07-09, 2013-11-17, 1992-05-12, …
#> $ proc_or_meas_0_to_0  <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…

These binaries (installable software) and packages are in development.
They may not be fully stable and should be used with caution. We make no claims about them.
Health stats visible at Monitor.