Project Plan - Advanced

This tutorial uses synthetic data only. Do not paste real EHR rows, individual-level records, PHI, PII, private paths, credentials, controlled-access data, or sensitive small-cell outputs into an LLM. Keep the code repository separate from protected data and run real-data workflows only inside the approved environment.

Scope and Method

Build a reproducible descriptive workflow for synthetic participants aged 20 years or older at each measurement. The exercise demonstrates joins, validation, deterministic record selection, and reporting. It does not provide clinical recommendations, pediatric BMI interpretation, or a validated phenotype.

Apply the general adult BMI categories below to unrounded calculated BMI. Demographic fields support descriptive audits of missingness and exclusions. Do not assign population-specific thresholds from broad EHR race labels or the harmonized categories in this example: a source’s named populations and purpose must match any proposed mapping. See the NICE BMI guidance and scope notes.

Data Description

The synthetic primary file is tab-delimited and can contain multiple measurements per person:

  • person_id: Synthetic person identifier
  • encounter_id: Synthetic encounter identifier
  • bmi: Reported BMI in kg/m², retained for comparison
  • height_cm: Height in centimeters
  • weight_kg: Weight in kilograms
  • measurement_date: Date of the measurement (YYYY-MM-DD)

A separate synthetic demographics file contains one row per person_id:

  • date_of_birth: Synthetic birth date (YYYY-MM-DD), used to determine age at measurement
  • age, age_bin: Optional source age fields; do not use these for eligibility unless their reference date is verified
  • deceased: Source indicator, with its coding documented
  • race, ethnicity, race_ethnicity, race_ethnicity_harmonized: Source demographic categories, preserved with a documented missingness policy
  • sex_gender: Source field with its definition retained; do not silently substitute one sex or gender construct for another
  • marital_status_name, zip3: Optional synthetic context fields, included only if needed for the exercise

The fixture imitates EHR data quality problems; it contains no patient records. Record all input schemas, units, category encodings, and the expected relationship between the files.

Task to Be Accomplished

  1. Data Ingestion and Joining
    • Read both files as TSV and enforce the required columns and types.
    • Require a unique demographic key. Report duplicate or conflicting person_id values and resolve them before joining; do not allow an accidental many-to-many join.
    • Join demographics to measurements and report unmatched identifiers separately.
    • Parse birth and measurement dates. Calculate agedays = measurement_date - date_of_birth per measurement, and determine completed years using calendar dates. Specify the calendar rule for February 29 birthdays and test it explicitly; do not divide days by 365 to decide eligibility.
  2. Eligibility, Missingness, and Cleaning
    • Retain measurements made on or after the participant’s 20th birthday. Flag and exclude records with missing or invalid dates, measurement before birth, or age below 20; report each reason.
    • Require finite, positive height and weight in the declared units. Reject missing values, NaN, infinity, nonpositive values, and unparseable units with explicit reason codes. Specify any additional plausible ranges before implementation.
    • Calculate bmi_calculated = weight_kg / (height_cm / 100)^2 without rounding, then require the result to be finite and positive (including after overflow or underflow). Use this value consistently for selection and categorization.
    • Preserve reported bmi and flag disagreement using a prespecified tolerance. State whether a missing reported BMI or a discrepancy excludes the measurement; do not silently switch between reported and calculated BMI.
    • Define outlier rules, required measurement counts, and exclusion decisions before coding. An outlier flag is an audit signal, not proof that a measurement is wrong.
    • If growthcleanr is selected, verify its installed version, supported adult population, required inputs, and documented settings before use. Do not infer compatibility from the presence of agedays or use pediatric defaults for this adult exercise. Record returned flags and the chosen flag-to-exclusion policy.
    • Preserve meaningful demographic categories. Document how blank, unknown, refused, and unmatched values are represented; retain separate reason codes where the source distinguishes them. Do not infer or fill demographic identity from other fields.
    • Write rejected measurements and their reason codes to a separate synthetic audit file. Maintain a row-level count ledger so multiple flags do not double-count excluded rows.
  3. Representative Record Selection
    • Compute each person’s median unrounded calculated BMI among eligible, retained measurements.
    • Select an observed row with the smallest absolute distance from that median, keeping its height, weight, and measurement date together.
    • Break equal-distance ties by earliest measurement date, then encounter_id in ascending order. Require a unique record key or resolve duplicate keys before selection.
    • Report people with no retained measurements separately. Verify that shuffling input rows does not change the selected output.
  4. Descriptive Categories
    • Apply these general adult BMI categories to bmi_calculated before any display rounding:
      • Underweight: BMI < 18.5 kg/m²
      • Normal range: 18.5 ≤ BMI < 25 kg/m²
      • Overweight: 25 ≤ BMI < 30 kg/m²
      • Obesity I: 30 ≤ BMI < 35 kg/m²
      • Obesity II: 35 ≤ BMI < 40 kg/m²
      • Obesity III: BMI ≥ 40 kg/m²
    • Optional height bins: Short (<150 cm), Average (150 to <180 cm), Tall (≥180 cm).
    • Optional weight bins: Light (<50 kg), Medium (50 to <80 kg), Heavy (80 to <100 kg), Very Heavy (≥100 kg).
    • Height and weight bins are arbitrary tutorial examples, not clinical standards. Keep labels and interval boundaries in the data dictionary.

Expected Output

  1. Cleaned Dataset
    • One TSV row per person with a retained measurement.
    • Include person_id, encounter_id, reported and calculated BMI, height, weight, measurement date, age at measurement, agedays, and the descriptive categories.
    • Include only the synthetic demographic fields needed for the declared audit, along with missingness indicators. Do not generate race-specific BMI categories.
  2. Summary Report and Audit Files
    • Report input and joined row counts, unmatched identifiers, ineligible ages, missing/invalid measurements, discrepancies, outlier flags, and final exclusions.
    • Distinguish counts of flags from counts of unique excluded rows and people. Report people with no retained records and the distribution of measurements per person.
    • Summarize retained BMI, height, weight, and categories overall and by declared demographic audit groups, showing missing/unknown categories explicitly. Use tableone only if it is part of the verified environment and report denominators and missing counts.
    • Describe group differences in missingness and exclusions without treating demographic labels as clinical risk rules. Retain synthetic rejected-row and no-retained-record outputs with reason codes.
  3. Data Dictionary and Run Record
    • Document column names, types, units, allowed values, missingness codes, formulas, joins, and category boundaries.
    • Record the selected cleaning rules, parameter values, package versions from the environment, fixture version, command, and verification date (YYYY-MM-DD).

Acceptance Criteria

Before implementation, record the exact smoke-test command and expected assertions against a synthetic fixture. It must cover:

  • Eligibility on the 20th birthday, exclusion the day before, the documented February 29 rule, and missing/invalid birth dates.
  • A one-to-many measurement join that preserves row counts; duplicate demographic keys must produce a clear validation failure.
  • BMI values at and just below 18.5, 25, 30, 35, and 40, proving that rounding affects only display.
  • Missing, zero, negative, NaN, and infinite height/weight values, plus calculation overflow/underflow, producing the specified reason codes before selection.
  • Missing demographic values that remain explicit in summaries and do not trigger alternative BMI thresholds.
  • Tied median distances, duplicate record keys, and shuffled input to verify deterministic selection of observed rows.
  • Reconciliation of unique excluded rows, retained rows, and final people without double-counting flags.
  • Empty eligible input producing the documented empty output schema and an informative summary.

Pause implementation of affected steps until unresolved cleaning thresholds, discrepancy policies, or package settings are specified. Report checks actually run and separate them from checks still proposed.