0. Load and profile

dx <- load_t2dm(data_path)
glimpse(dx)
## Rows: 26,059
## Columns: 19
## $ sid.x         <chr> "S2096098", "S2274441", "S2808630", "S8925741", "S359436…
## $ encounter_id  <chr> "E5717954224", "E9342373335", "E2399703749", "E496129127…
## $ enc_type      <chr> "Office Visit", "Office Visit", "Office Visit", "Office …
## $ SPECIALTY     <chr> "Primary Care", "Primary Care", "Podiatry", "Podiatry", …
## $ ADMIT_TIME    <chr> "16OCT18:00:00:00", "10DEC18:00:00:00", "09NOV18:00:00:0…
## $ TELEMED_VISIT <int> 0, 1, 0, 1, 0, 1, 0, 1, 0, 0, 1, 0, 0, 1, 1, 1, 0, 1, 1,…
## $ DX_priority   <int> 2, 1, 2, 1, 2, 3, 3, 1, 1, 2, 2, 1, 2, 1, 2, 2, 1, 2, 1,…
## $ PRIMARY_DX_YN <chr> "N", "Y", "N", "N", "N", "N", "N", "Y", "Y", "Y", "N", "…
## $ DX_NAME       <chr> "Type 2 diabetes mellitus with diabetic nephropathy, wit…
## $ icd10         <chr> "E11.21", "E11.621, L97.509, Z79.4", "B37.3", "E11.22, Z…
## $ insurance     <chr> "Medicaid", "Self-pay / Uninsured", "Medicare", "Self-pa…
## $ Patient_Race  <chr> "White", "Black or African American", "Black or African …
## $ Patient_sex   <chr> "Male", "Male", "Male", "Male", "Male", "Female", "Male"…
## $ dob           <chr> "24JUL1951", "23DEC1996", "14MAR1960", "08APR1956", "03A…
## $ index_date    <chr> "10/26/2015", "12/24/2016", "05/21/2013", "12/03/2015", …
## $ pat_port      <int> 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 0,…
## $ admit_date    <date> 2018-10-16, 2018-12-10, 2018-11-09, 2016-10-14, 2016-11…
## $ dob_date      <date> 1951-07-24, 1996-12-23, 1960-03-14, 1956-04-08, 1956-04…
## $ index_dt      <date> 2015-10-26, 2016-12-24, 2013-05-21, 2015-12-03, 2013-06…

1. Rows, patients and encounters

size_summary <- tibble(
  rows       = nrow(dx),
  patients   = n_distinct(dx$sid.x),
  encounters = n_distinct(dx$encounter_id)
)
size_summary
enc_per_patient <- dx %>%
  distinct(sid.x, encounter_id) %>%
  count(sid.x, name = "n_encounters")

summary(enc_per_patient$n_encounters)
##    Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
##   1.000   1.000   1.000   1.002   1.000   2.000
enc_per_patient %>% count(n_encounters, name = "patients") %>%
  mutate(pct = round(100 * patients / sum(patients), 1))
# Diagnoses (rows) per encounter
dx_per_enc <- dx %>% count(encounter_id, name = "n_rows")
summary(dx_per_enc$n_rows)
##    Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
##       1       1       1       1       1       1
# Does any encounter ID belong to more than one patient? It should not.
dx %>% distinct(encounter_id, sid.x) %>% count(encounter_id) %>% filter(n > 1) %>% nrow()
## [1] 0
ggplot(dx_per_enc, aes(n_rows)) +
  geom_histogram(binwidth = 1, fill = "#3B6EA8", colour = "white") +
  labs(x = "Diagnoses per encounter", y = "Encounters",
       title = "How many diagnosis rows does each visit have?") +
  theme_minimal()

2. Columns with no useful variation

variation <- dx %>%
  select(-admit_date, -dob_date, -index_dt) %>%
  summarise(across(everything(), list(
    n_distinct = ~ n_distinct(.x, na.rm = TRUE),
    top_share  = ~ max(table(.x)) / sum(!is.na(.x))
  ), .names = "{.col}__{.fn}")) %>%
  pivot_longer(everything(), names_to = c("column", ".value"), names_sep = "__") %>%
  mutate(flag = case_when(n_distinct <= 1 ~ "constant",
                          top_share >= 0.95 ~ "near-constant (>=95% one value)",
                          TRUE ~ "")) %>%
  arrange(desc(top_share))
variation
# Distribution of the near-constant flags
dx %>% count(enc_type)
dx %>% count(TELEMED_VISIT) %>% mutate(pct = round(100 * n / sum(n), 2))
dx %>% count(pat_port)      %>% mutate(pct = round(100 * n / sum(n), 2))

enc_type is constant and was reattached after synthesis, so it carries no information and should be dropped. TELEMED_VISIT and pat_port are near-constant; they can be kept as candidate features but will contribute little and may be unstable in small subgroups.

3. Missingness

missing_tbl <- dx %>%
  summarise(across(everything(), ~ sum(is.na(.x)))) %>%
  pivot_longer(everything(), names_to = "column", values_to = "n_missing") %>%
  mutate(pct_missing = round(100 * n_missing / nrow(dx), 3)) %>%
  arrange(desc(n_missing))
missing_tbl
# Parse failures: values present in the raw column but NA after date parsing
tibble(
  admit_parse_fail = sum(!is.na(dx$ADMIT_TIME) & is.na(dx$admit_date)),
  dob_parse_fail   = sum(!is.na(dx$dob)        & is.na(dx$dob_date)),
  index_parse_fail = sum(!is.na(dx$index_date) & is.na(dx$index_dt))
)
# Do missing icd10 and DX_NAME occur on the same rows?
dx %>% count(icd10_missing = is.na(icd10), name_missing = is.na(DX_NAME))
# Internal consistency: PRIMARY_DX_YN should be "Y" exactly when DX_priority == 1
dx %>% count(DX_priority_is_1 = DX_priority == 1, PRIMARY_DX_YN)
# Encounters with no primary diagnosis, or more than one
dx %>% group_by(encounter_id) %>%
  summarise(n_primary = sum(PRIMARY_DX_YN == "Y", na.rm = TRUE)) %>%
  count(n_primary)
# Exact duplicate rows
sum(duplicated(dx))
## [1] 0

4. Race and insurance: “Unknown” and “Unreported / Declined”

Counts are at the patient level, since a row-level count overweights patients with many diagnoses.

# Is race/insurance consistent within a patient?
dx %>% group_by(sid.x) %>%
  summarise(n_race = n_distinct(Patient_Race), n_ins = n_distinct(insurance),
            n_sex = n_distinct(Patient_sex), n_dob = n_distinct(dob)) %>%
  summarise(across(starts_with("n_"), ~ sum(.x > 1), .names = "patients_with_multiple_{.col}"))
pt_demo <- dx %>% distinct(sid.x, Patient_Race, insurance, Patient_sex) %>%
  group_by(sid.x) %>% slice(1) %>% ungroup()

race_tbl <- pt_demo %>% count(Patient_Race, sort = TRUE) %>%
  mutate(pct = round(100 * n / sum(n), 2))
race_tbl
race_tbl %>%
  filter(Patient_Race %in% c("Unknown", "Unreported / Declined") | is.na(Patient_Race)) %>%
  summarise(patients = sum(n), pct = sum(pct))
ins_tbl <- pt_demo %>% count(insurance, sort = TRUE) %>%
  mutate(pct = round(100 * n / sum(n), 2))
ins_tbl
# Are unknown race values concentrated in particular payers?
pt_demo %>%
  mutate(race_unknown = Patient_Race %in% c("Unknown", "Unreported / Declined") | is.na(Patient_Race)) %>%
  group_by(insurance) %>%
  summarise(patients = n(), pct_race_unknown = round(100 * mean(race_unknown), 1)) %>%
  arrange(desc(pct_race_unknown))

5. Age check

ages <- dx %>%
  distinct(sid.x, dob_date, index_dt) %>%
  mutate(age_at_index = age_years(dob_date, index_dt))

summary(ages$age_at_index)
##    Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
##   14.00   41.00   52.00   50.77   60.00   98.00
age_flags <- ages %>%
  mutate(flag = case_when(
    is.na(age_at_index)       ~ "missing / unparseable",
    dob_date > index_dt       ~ "born after index date",
    age_at_index < 18         ~ "under 18 (unusual for adult T2DM cohort)",
    age_at_index > 100        ~ "over 100",
    TRUE                      ~ "plausible"
  ))
age_flags %>% count(flag)
ggplot(ages, aes(age_at_index)) +
  geom_histogram(binwidth = 2, fill = "#3B6EA8", colour = "white") +
  geom_vline(xintercept = c(18, 100), linetype = "dashed", colour = "firebrick") +
  labs(x = "Age at index date (years)", y = "Patients",
       title = "Age at cohort entry", subtitle = "Dashed lines: 18 and 100") +
  theme_minimal()

# Visit dates relative to index date and to each other
dx %>% distinct(sid.x, encounter_id, admit_date, index_dt, dob_date) %>%
  summarise(
    earliest_visit       = min(admit_date, na.rm = TRUE),
    latest_visit         = max(admit_date, na.rm = TRUE),
    earliest_index       = min(index_dt, na.rm = TRUE),
    latest_index         = max(index_dt, na.rm = TRUE),
    visits_before_index  = sum(admit_date < index_dt, na.rm = TRUE),
    visits_before_birth  = sum(admit_date < dob_date, na.rm = TRUE)
  )

Notes for the memo

Use the output above to pick your three issues. Strong candidates usually include:

  • How race is recorded (the share of Unknown / Unreported, and whether it clusters by payer), because it limits the fairness audit in Week 4.
  • The structure of the data (rows are diagnoses, not patients; most patients have very few encounters), which drives how features must be built and limits what “history” the model can see.
  • Columns that are constant or were reattached after synthesis, and any implausible ages or date orderings.
  • Small insurance categories (e.g., Correctional Health, Workers Compensation) that cannot support subgroup estimates.