# Adding patient demographics

## 1.1 Introduction

The OMOP CDM is a person-centric model. The person table contains records that uniquely identify each individual along with some of their demographic information. Below we create a mock CDM reference which, as is standard, has a person table which contains fields which indicate an individual’s date of birth, gender, race, and ethnicity. Each of these, except for date of birth, are represented by a concept ID (and as the person table contains one record per person these fields are treated as time-invariant).

    library(PatientProfiles)
    library(duckdb)
    library(dplyr)

    cdm <- mockPatientProfiles(numberIndividuals = 10000, source = "duckdb")

    cdm$person |>
      dplyr::glimpse()

    ## Rows: ??
    ## Columns: 5
    ## Database: DuckDB 1.4.1 [martics@Windows 10 x64:R 4.2.1/:memory:]
    ## $ person_id            <int> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15…
    ## $ gender_concept_id    <int> 8507, 8507, 8507, 8532, 8507, 8532, 8507, 8507, 8…
    ## $ year_of_birth        <int> 1923, 1957, 1935, 1943, 1970, 1946, 1950, 1937, 1…
    ## $ race_concept_id      <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
    ## $ ethnicity_concept_id <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…

As well as the person table, every CDM reference will include an observation period table. This table contains spans of times during which an individual is considered to being under observation. Individuals can have multiple observation periods, but they cannot overlap.

    cdm$observation_period |>
      dplyr::glimpse()

    ## Rows: ??
    ## Columns: 5
    ## Database: DuckDB 1.4.1 [martics@Windows 10 x64:R 4.2.1/:memory:]
    ## $ person_id                     <int> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 1…
    ## $ observation_period_start_date <date> 1923-01-01, 1957-01-01, 1935-01-01, 194…
    ## $ observation_period_end_date   <date> 1946-08-03, 1976-02-26, 1976-09-05, 196…
    ## $ period_type_concept_id        <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
    ## $ observation_period_id         <int> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 1…

When performing analyses we will often be interested in working with the person and observation period tables to identify individuals’ characteristics on some date of interest. PatientProfiles provides a number of functions that can help us do this.

## 1.2 Adding characteristics to OMOP CDM tables

Let’s say we’re working with the condition occurrence table.

    cdm$condition_occurrence |>
      glimpse()

    ## Rows: ??
    ## Columns: 6
    ## Database: DuckDB 1.4.1 [martics@Windows 10 x64:R 4.2.1/:memory:]
    ## $ person_id                 <int> 324, 9872, 1063, 3792, 734, 3906, 7415, 8265…
    ## $ condition_start_date      <date> 1906-04-30, 1952-09-25, 1990-12-31, 1970-07…
    ## $ condition_end_date        <date> 1935-07-30, 1961-01-02, 2002-11-19, 1977-11…
    ## $ condition_occurrence_id   <int> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 1…
    ## $ condition_concept_id      <int> 5, 9, 10, 10, 3, 10, 3, 3, 9, 10, 6, 5, 5, 3…
    ## $ condition_type_concept_id <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…

This table contains diagnoses of individuals and we might, for example, want to identify their age on their date of diagnosis. This involves linking back to the person table which contains their date of birth (split across three different columns). PatientProfiles provides a simple function for this. `addAge()` will add a new column to the table containing each patient’s age relative to the specified index date.

    cdm$condition_occurrence <- cdm$condition_occurrence |>
      addAge(indexDate = "condition_start_date")

    cdm$condition_occurrence |>
      glimpse()

    ## Rows: ??
    ## Columns: 7
    ## Database: DuckDB 1.4.1 [martics@Windows 10 x64:R 4.2.1/:memory:]
    ## $ person_id                 <int> 1, 5, 10, 11, 12, 14, 16, 19, 26, 30, 35, 38…
    ## $ condition_start_date      <date> 1943-10-01, 2001-12-09, 1927-09-07, 1909-04…
    ## $ condition_end_date        <date> 1944-08-17, 2002-03-11, 1931-02-28, 1916-07…
    ## $ condition_occurrence_id   <int> 4818, 4622, 4833, 2090, 2914, 4831, 4032, 19…
    ## $ condition_concept_id      <int> 5, 10, 1, 10, 4, 3, 5, 7, 6, 5, 5, 5, 6, 9, …
    ## $ condition_type_concept_id <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
    ## $ age                       <int> 20, 31, 11, 7, 4, 9, 4, 0, 1, 5, 4, 9, 27, 3…

As well as calculating age, we can also create age groups at the same time. Here we create three age groups: those aged 0 to 17, those 18 to 65, and those 66 or older.

    cdm$condition_occurrence <- cdm$condition_occurrence |>
      addAge(
        indexDate = "condition_start_date",
        ageGroup = list(
          "0 to 17" = c(0, 17),
          "18 to 65" = c(18, 65),
          ">= 66" = c(66, Inf)
        )
      )

    cdm$condition_occurrence |>
      glimpse()

    ## Rows: ??
    ## Columns: 8
    ## Database: DuckDB 1.4.1 [martics@Windows 10 x64:R 4.2.1/:memory:]
    ## $ person_id                 <int> 1, 5, 10, 11, 12, 14, 16, 19, 26, 30, 35, 38…
    ## $ condition_start_date      <date> 1937-07-04, 2001-12-09, 1927-09-07, 1909-04…
    ## $ condition_end_date        <date> 1945-04-02, 2002-03-11, 1931-02-28, 1916-07…
    ## $ condition_occurrence_id   <int> 2646, 4622, 4833, 2090, 2914, 4831, 4032, 19…
    ## $ condition_concept_id      <int> 10, 10, 1, 10, 4, 3, 5, 7, 6, 5, 5, 5, 6, 8,…
    ## $ condition_type_concept_id <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
    ## $ age                       <int> 14, 31, 11, 7, 4, 9, 4, 0, 1, 5, 2, 9, 27, 4…
    ## $ age_group                 <chr> "0 to 17", "18 to 65", "0 to 17", "0 to 17",…

By default, when adding age the new column will have been called “age” and will have been calculated using all available information on date of birth contained in the person. We can though also alter these defaults. Here, for example, we impose that month of birth is January and day of birth is the 1st for all individuals.

    cdm$condition_occurrence <- cdm$condition_occurrence |>
      addAge(
        indexDate = "condition_start_date",
        ageName = "age_from_year_of_birth",
        ageMissingMonth = 1,
        ageMissingDay = 1,
        ageImposeMonth = TRUE,
        ageImposeDay = TRUE
      )

    cdm$condition_occurrence |>
      glimpse()

    ## Rows: ??
    ## Columns: 9
    ## Database: DuckDB 1.4.1 [martics@Windows 10 x64:R 4.2.1/:memory:]
    ## $ person_id                 <int> 1, 5, 10, 11, 12, 14, 16, 19, 26, 30, 35, 38…
    ## $ condition_start_date      <date> 1937-07-04, 2001-12-09, 1927-09-07, 1909-04…
    ## $ condition_end_date        <date> 1945-04-02, 2002-03-11, 1931-02-28, 1916-07…
    ## $ condition_occurrence_id   <int> 2646, 4622, 4833, 2090, 2914, 4831, 4032, 19…
    ## $ condition_concept_id      <int> 10, 10, 1, 10, 4, 3, 5, 7, 6, 5, 5, 5, 6, 8,…
    ## $ condition_type_concept_id <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
    ## $ age                       <int> 14, 31, 11, 7, 4, 9, 4, 0, 1, 5, 2, 9, 27, 4…
    ## $ age_group                 <chr> "0 to 17", "18 to 65", "0 to 17", "0 to 17",…
    ## $ age_from_year_of_birth    <int> 14, 31, 11, 7, 4, 9, 4, 0, 1, 5, 2, 9, 27, 4…

As well as age at diagnosis, we might also want identify patients’ sex. PatientProfiles provides the `addSex()` function that will add this for us. Because this is treated as time-invariant, we will not have to specify any index variable.

    cdm$condition_occurrence <- cdm$condition_occurrence |>
      addSex()

    cdm$condition_occurrence |>
      glimpse()

    ## Rows: ??
    ## Columns: 10
    ## Database: DuckDB 1.4.1 [martics@Windows 10 x64:R 4.2.1/:memory:]
    ## $ person_id                 <int> 1, 5, 10, 11, 12, 14, 16, 19, 26, 30, 35, 38…
    ## $ condition_start_date      <date> 1943-10-01, 2001-12-09, 1927-09-07, 1909-04…
    ## $ condition_end_date        <date> 1944-08-17, 2002-03-11, 1931-02-28, 1916-07…
    ## $ condition_occurrence_id   <int> 4818, 4622, 4833, 2090, 2914, 4831, 4032, 19…
    ## $ condition_concept_id      <int> 5, 10, 1, 10, 4, 3, 5, 7, 6, 5, 5, 5, 6, 9, …
    ## $ condition_type_concept_id <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
    ## $ age                       <int> 20, 31, 11, 7, 4, 9, 4, 0, 1, 5, 4, 9, 27, 3…
    ## $ age_group                 <chr> "18 to 65", "18 to 65", "0 to 17", "0 to 17"…
    ## $ age_from_year_of_birth    <int> 20, 31, 11, 7, 4, 9, 4, 0, 1, 5, 4, 9, 27, 3…
    ## $ sex                       <chr> "Male", "Male", "Male", "Female", "Female", …

Similarly, we could also identify whether an individual was in observation at the time of their diagnosis (i.e. had an observation period that overlaps with their diagnosis date), as well as identifying how much prior observation time they had on this date and how much they have following it.

    cdm$condition_occurrence <- cdm$condition_occurrence |>
      addInObservation(indexDate = "condition_start_date") |>
      addPriorObservation(indexDate = "condition_start_date") |>
      addFutureObservation(indexDate = "condition_start_date")

    cdm$condition_occurrence |>
      glimpse()

    ## Rows: ??
    ## Columns: 13
    ## Database: DuckDB 1.4.1 [martics@Windows 10 x64:R 4.2.1/:memory:]
    ## $ person_id                 <int> 1, 5, 10, 11, 12, 14, 16, 19, 26, 30, 35, 38…
    ## $ condition_start_date      <date> 1943-10-01, 2001-12-09, 1927-09-07, 1909-04…
    ## $ condition_end_date        <date> 1944-08-17, 2002-03-11, 1931-02-28, 1916-07…
    ## $ condition_occurrence_id   <int> 4818, 4622, 4833, 2090, 2914, 4831, 4032, 19…
    ## $ condition_concept_id      <int> 5, 10, 1, 10, 4, 3, 5, 7, 6, 5, 5, 5, 6, 9, …
    ## $ condition_type_concept_id <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
    ## $ age                       <int> 20, 31, 11, 7, 4, 9, 4, 0, 1, 5, 4, 9, 27, 3…
    ## $ age_group                 <chr> "18 to 65", "18 to 65", "0 to 17", "0 to 17"…
    ## $ age_from_year_of_birth    <int> 20, 31, 11, 7, 4, 9, 4, 0, 1, 5, 4, 9, 27, 3…
    ## $ sex                       <chr> "Male", "Male", "Male", "Female", "Female", …
    ## $ in_observation            <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,…
    ## $ prior_observation         <int> 7578, 11665, 4267, 2670, 1615, 3596, 1593, 2…
    ## $ future_observation        <int> 1037, 1869, 2219, 10439, 9408, 5944, 8789, 1…

For these functions which work with information from the observation table, it is important to note that the results will be based on the observation period during which the index date falls within. Moreover, if a patient is not under observation at the specified date, `addPriorObservation()` and `addFutureObservation()` functions will return NA.

When checking whether someone is in observation the default is that we are checking whether someone was in observation on the index date. We could though expand this and consider a window of time around this date. For example here we add a variable indicating whether someone was in observation from 180 days before the index date to 30 days following it.

    cdm$condition_occurrence |>
      addInObservation(
        indexDate = "condition_start_date",
        window = c(-180, 30)
      ) |>
      glimpse()

    ## Rows: ??
    ## Columns: 13
    ## Database: DuckDB 1.4.1 [martics@Windows 10 x64:R 4.2.1/:memory:]
    ## $ person_id                 <int> 1, 5, 10, 11, 12, 14, 16, 19, 26, 30, 35, 38…
    ## $ condition_start_date      <date> 1943-10-01, 2001-12-09, 1927-09-07, 1909-04…
    ## $ condition_end_date        <date> 1944-08-17, 2002-03-11, 1931-02-28, 1916-07…
    ## $ condition_occurrence_id   <int> 4818, 4622, 4833, 2090, 2914, 4831, 4032, 19…
    ## $ condition_concept_id      <int> 5, 10, 1, 10, 4, 3, 5, 7, 6, 5, 5, 5, 6, 9, …
    ## $ condition_type_concept_id <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
    ## $ age                       <int> 20, 31, 11, 7, 4, 9, 4, 0, 1, 5, 4, 9, 27, 3…
    ## $ age_group                 <chr> "18 to 65", "18 to 65", "0 to 17", "0 to 17"…
    ## $ age_from_year_of_birth    <int> 20, 31, 11, 7, 4, 9, 4, 0, 1, 5, 4, 9, 27, 3…
    ## $ sex                       <chr> "Male", "Male", "Male", "Female", "Female", …
    ## $ prior_observation         <int> 7578, 11665, 4267, 2670, 1615, 3596, 1593, 2…
    ## $ future_observation        <int> 1037, 1869, 2219, 10439, 9408, 5944, 8789, 1…
    ## $ in_observation            <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,…

We can also specify a window and require that an individual is present for only some days within it. Here we add a variable indicating whether the individual was in observation at least a year in the future,

    cdm$condition_occurrence |>
      addInObservation(
        indexDate = "condition_start_date",
        window = c(365, Inf),
        completeInterval = FALSE
      ) |>
      glimpse()

    ## Rows: ??
    ## Columns: 13
    ## Database: DuckDB 1.4.1 [martics@Windows 10 x64:R 4.2.1/:memory:]
    ## $ person_id                 <int> 1, 5, 10, 11, 12, 14, 16, 19, 26, 30, 35, 38…
    ## $ condition_start_date      <date> 1943-10-01, 2001-12-09, 1927-09-07, 1909-04…
    ## $ condition_end_date        <date> 1944-08-17, 2002-03-11, 1931-02-28, 1916-07…
    ## $ condition_occurrence_id   <int> 4818, 4622, 4833, 2090, 2914, 4831, 4032, 19…
    ## $ condition_concept_id      <int> 5, 10, 1, 10, 4, 3, 5, 7, 6, 5, 5, 5, 6, 9, …
    ## $ condition_type_concept_id <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
    ## $ age                       <int> 20, 31, 11, 7, 4, 9, 4, 0, 1, 5, 4, 9, 27, 3…
    ## $ age_group                 <chr> "18 to 65", "18 to 65", "0 to 17", "0 to 17"…
    ## $ age_from_year_of_birth    <int> 20, 31, 11, 7, 4, 9, 4, 0, 1, 5, 4, 9, 27, 3…
    ## $ sex                       <chr> "Male", "Male", "Male", "Female", "Female", …
    ## $ prior_observation         <int> 7578, 11665, 4267, 2670, 1615, 3596, 1593, 2…
    ## $ future_observation        <int> 1037, 1869, 2219, 10439, 9408, 5944, 8789, 1…
    ## $ in_observation            <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,…

## 1.3 Adding characteristics to a cohort tables

The above functions can be used on both standard OMOP CDM tables and cohort tables. Note as the default index date in the functions is “cohort_start_date” we can now omit this.

    cdm$cohort1 |>
      glimpse()

    ## Rows: ??
    ## Columns: 4
    ## Database: DuckDB 1.4.1 [martics@Windows 10 x64:R 4.2.1/:memory:]
    ## $ cohort_definition_id <int> 2, 1, 3, 1, 2, 3, 2, 2, 3, 1, 3, 1, 3, 1, 1, 1, 2…
    ## $ subject_id           <int> 1381, 304, 780, 5229, 2976, 1678, 4227, 2689, 829…
    ## $ cohort_start_date    <date> 1968-12-11, 1991-01-10, 1944-10-04, 1990-03-14, …
    ## $ cohort_end_date      <date> 1978-12-04, 1993-09-12, 1962-02-15, 1997-04-19, …

    cdm$cohort1 <- cdm$cohort1 |>
      addAge(ageGroup = list(
        "0 to 17" = c(0, 17),
        "18 to 65" = c(18, 65),
        ">= 66" = c(66, Inf)
      )) |>
      addSex() |>
      addInObservation() |>
      addPriorObservation() |>
      addFutureObservation()

    cdm$cohort1 |>
      glimpse()

    ## Rows: ??
    ## Columns: 10
    ## Database: DuckDB 1.4.1 [martics@Windows 10 x64:R 4.2.1/:memory:]
    ## $ cohort_definition_id <int> 2, 1, 3, 1, 2, 3, 2, 2, 3, 1, 3, 1, 3, 1, 1, 1, 2…
    ## $ subject_id           <int> 1381, 304, 780, 5229, 2976, 1678, 4227, 2689, 829…
    ## $ cohort_start_date    <date> 1968-12-11, 1991-01-10, 1944-10-04, 1990-03-14, …
    ## $ cohort_end_date      <date> 1978-12-04, 1993-09-12, 1962-02-15, 1997-04-19, …
    ## $ age                  <int> 4, 33, 11, 37, 2, 20, 1, 9, 46, 24, 11, 3, 0, 8, …
    ## $ age_group            <chr> "0 to 17", "18 to 65", "0 to 17", "18 to 65", "0 …
    ## $ sex                  <chr> "Female", "Female", "Female", "Female", "Female",…
    ## $ in_observation       <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…
    ## $ prior_observation    <int> 1806, 12062, 4294, 13586, 795, 7563, 689, 3558, 1…
    ## $ future_observation   <int> 9188, 3051, 9987, 4586, 11818, 38, 5053, 14307, 1…

## 1.4 Getting multiple characteristics at once

The above functions, which are chained together, each fetch the related information one by one. In the cases where we are interested in adding multiple characteristics, we can add these all at the same time using the more general `addDemographics()` functions. This will be more efficient that adding characteristics as it requires fewer joins between our table of interest and the person and observation period tables.

    cdm$cohort2 |>
      glimpse()

    ## Rows: ??
    ## Columns: 4
    ## Database: DuckDB 1.4.1 [martics@Windows 10 x64:R 4.2.1/:memory:]
    ## $ cohort_definition_id <int> 2, 1, 1, 3, 3, 3, 3, 1, 2, 1, 2, 3, 2, 1, 3, 1, 3…
    ## $ subject_id           <int> 2672, 4523, 2332, 5789, 8053, 9705, 2973, 7382, 9…
    ## $ cohort_start_date    <date> 1944-09-07, 1922-05-20, 1971-03-12, 1988-04-28, …
    ## $ cohort_end_date      <date> 1950-04-05, 1931-07-03, 1998-02-28, 1988-10-23, …

    tictoc::tic()
    cdm$cohort2 |>
      addAge(ageGroup = list(
        "0 to 17" = c(0, 17),
        "18 to 65" = c(18, 65),
        ">= 66" = c(66, Inf)
      )) |>
      addSex() |>
      addInObservation() |>
      addPriorObservation() |>
      addFutureObservation()

    ## # Source:   table<og_247_1771930143> [?? x 10]
    ## # Database: DuckDB 1.4.1 [martics@Windows 10 x64:R 4.2.1/:memory:]
    ##    cohort_definition_id subject_id cohort_start_date cohort_end_date   age
    ##                   <int>      <int> <date>            <date>          <int>
    ##  1                    2       2672 1944-09-07        1950-04-05          4
    ##  2                    1       4523 1922-05-20        1931-07-03          4
    ##  3                    1       2332 1971-03-12        1998-02-28         25
    ##  4                    3       5789 1988-04-28        1988-10-23         17
    ##  5                    3       8053 1936-04-12        1949-04-22         18
    ##  6                    3       9705 1984-06-20        1985-03-09         11
    ##  7                    3       2973 1973-02-02        1973-06-27         18
    ##  8                    1       7382 1981-08-16        2005-09-07          4
    ##  9                    2       9543 1978-05-08        1986-08-12          5
    ## 10                    1       5460 1953-11-27        1954-08-20          8
    ## # ℹ more rows
    ## # ℹ 5 more variables: age_group <chr>, sex <chr>, in_observation <int>,
    ## #   prior_observation <int>, future_observation <int>

    tictoc::toc()

    ## 1.64 sec elapsed

    tictoc::tic()
    cdm$cohort2 |>
      addDemographics(
        age = TRUE,
        ageName = "age",
        ageGroup = list(
          "0 to 17" = c(0, 17),
          "18 to 65" = c(18, 65),
          ">= 66" = c(66, Inf)
        ),
        sex = TRUE,
        sexName = "sex",
        priorObservation = TRUE,
        priorObservationName = "prior_observation",
        futureObservation = FALSE,
      ) |>
      glimpse()

    ## Rows: ??
    ## Columns: 8
    ## Database: DuckDB 1.4.1 [martics@Windows 10 x64:R 4.2.1/:memory:]
    ## $ cohort_definition_id <int> 2, 2, 3, 3, 3, 2, 3, 3, 3, 3, 2, 2, 3, 2, 2, 2, 1…
    ## $ subject_id           <int> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15…
    ## $ cohort_start_date    <date> 1928-12-16, 1965-03-03, 1967-10-16, 1943-01-18, …
    ## $ cohort_end_date      <date> 1933-06-21, 1972-09-01, 1968-09-07, 1950-06-01, …
    ## $ age                  <int> 5, 8, 32, 0, 15, 12, 8, 5, 5, 6, 23, 19, 36, 26, …
    ## $ age_group            <chr> "0 to 17", "0 to 17", "18 to 65", "0 to 17", "0 t…
    ## $ sex                  <chr> "Male", "Male", "Male", "Female", "Male", "Female…
    ## $ prior_observation    <int> 2176, 2983, 11976, 17, 5798, 4678, 2981, 2073, 20…

    tictoc::toc()

    ## 0.66 sec elapsed

In our small mock dataset we see a small improvement in performance, but this difference will become much more noticeable when working with real data that will typically be far larger.