Charlson comorbidity profile (17 categories, Charlson index)

Flags the 17 Charlson categories (Quan et al. 2005 coding algorithms) in the look-back window before each cohort row's index date and scores them with the original Charlson 1987 weights. It is the R counterpart of Python omop_etl.extract_charlson_index(): both read the same curated code table, system.file("extdata", "comorbidity", "charlson_codes.csv", package = "omopduckdb") (sources, weights, hierarchy rules and known deviations are in PROVENANCE.md next to it), build the same SQL, and return the same columns and values.

Usage

extract_charlson_index(
  con,
  cohort_table = "cohort",
  cohort_id = NULL,
  person_col = "subject_id",
  index_date_col = "cohort_start_date",
  lookback_days = 365,
  source_and_standard = TRUE,
  hierarchy_adjusted = TRUE,
  exclude_index_diagnoses = TRUE,
  fallback_on_unmapped = FALSE,
  return_mapping_fidelity = FALSE
)

Arguments

con

Active DBI connection to DuckDB holding condition_occurrence and the cohort table (read-only is fine). concept_ancestor is looked up as concept_ancestor (main, a TEMP view or the connection's search_path, e.g. after omop_connect()), then as central_vocab.main.concept_ancestor.

cohort_table

Table or view with one row per index event (default "cohort"); may be schema-qualified.

cohort_id

Optional integer. Keep only rows with cohort_definition_id = cohort_id. If the table has no cohort_definition_id column a warning is raised and every row is profiled.

person_col

Person id column of cohort_table, joined to condition_occurrence.person_id. It must exist: columns are never guessed.

index_date_col

Index date column of cohort_table; must exist. It is cast to DATE to place the window.

lookback_days

Window [index - lookback_days, index): condition_start_date strictly before the index date and no more than lookback_days days earlier. NULL means all history before the index date.

source_and_standard

Also match condition_concept_id against the domain's SNOMED ancestors and their descendants (default TRUE). If concept_ancestor cannot be resolved on the connection (or is empty) a warning is raised and only the source side is used. FALSE uses the source side only, without a warning.

hierarchy_adjusted

Apply the hierarchy rules to the index and the condition count (default TRUE). FALSE gives the plain weighted sum and the plain count of raw flags.

exclude_index_diagnoses

Bedside-scoring guard (train/serve skew, default TRUE): diagnoses are coded by medical coders days to weeks after discharge, so a model trained on them cannot be scored at discharge. When TRUE, (a) condition records whose visit_occurrence_id equals the cohort row's visit_occurrence_id are never counted (when both tables have that column; cohorts from define_study_cohort() do), and (b) an index_date_col that looks like a discharge / end date is an error, since a window ending at discharge contains the index stay. The window is always [index - lookback_days, index).

fallback_on_unmapped

Logical; if TRUE, falls back to source-value matching if standard concept ancestor is unavailable (default FALSE).

return_mapping_fidelity

Logical; if TRUE, attaches mapping fidelity diagnostics attribute (default FALSE).

Value

A data.frame with the columns, in order: person_col, index_date_col, the 17 cci_<category> flags (integer 0/1: mi, chf, pvd, cevd, dementia, copd, rheum, pud, mild_liver, dm_uncomp, dm_comp, plegia, renal, malignancy, mod_severe_liver, mets, hiv), charlson_index (integer weighted sum) and cci_total_conditions (integer count of flagged categories).

Details

The result has one row per DISTINCT (person, index date) of the cohort table, ordered by person then index date. Persons without a qualifying condition still appear, with every flag 0 and an index of 0.

A condition record counts for a category when its condition_source_value, upper-cased with . and whitespace removed, STARTS WITH one of the category's ICD-10-CM / ICD-9-CM prefixes (I50 matches I50.9 and i 50.9; 4280 matches 428.0), or (source_and_standard) its condition_concept_id is a SNOMED ancestor of the category or a descendant of one in concept_ancestor. Only records dated strictly before the index date are used ([index - lookback_days, index)), so nothing at or after the index leaks in.

The profile is one statement of CTEs: nothing is created, so it works on read-only connections and leaves no objects behind.

Hierarchy. With hierarchy_adjusted = TRUE, mod_severe_liver supersedes mild_liver, dm_comp supersedes dm_uncomp and mets supersedes malignancy in the index and in the condition count; the 0/1 category columns always stay the raw flags.

Metastatic solid tumour has no standard-concept side and is detected from ICD source values only. Non-melanoma skin cancer cannot be excluded on the standard side. ICD-9-CM prefixes beginning with V can collide with ICD-10-CM transport-accident codes. All three caveats are described in PROVENANCE.md.

See also

extract_elixhauser_comorbidities()

Examples

con <- DBI::dbConnect(duckdb::duckdb())
#> duckdb keeps downloaded extensions and secrets in a temporary directory:
#> ℹ /tmp/RtmpFV3zps/duckdb
#> This is removed when the R session ends.
#> • Extensions are re-downloaded each session.
#> • Secrets are lost.
#> ℹ Run duckdb(shared_home = TRUE) (or create ~/.duckdb) to keep them (suitable for most users).
#> ℹ Run duckdb(shared_home = FALSE) to accept the temporary directory (and silence this message).
#> ℹ See ?duckdb_storage for details and alternatives.
DBI::dbExecute(con, "CREATE TABLE condition_occurrence AS
  SELECT 1 AS person_id, 0 AS condition_concept_id, DATE '2021-05-01' AS condition_start_date,
         'C78.0' AS condition_source_value")
#> [1] 1
DBI::dbExecute(con, "CREATE TABLE cohort AS
  SELECT 1 AS subject_id, DATE '2021-06-01' AS cohort_start_date")
#> [1] 1
extract_charlson_index(con, source_and_standard = FALSE)[
  , c("subject_id", "cci_mets", "charlson_index")]
#>   subject_id cci_mets charlson_index
#> 1          1        1              6
DBI::dbDisconnect(con, shutdown = TRUE)