Project Plan - Improved

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 protected data outside the code repository and run real-data workflows only in the approved environment.

Tutorial scope: Include only synthetic participants aged 20 years or older at measurement, determined from date of birth and measurement date. General adult BMI categories are descriptive labels for this exercise, not a clinical recommendation. Do not apply them to pediatric records or infer different thresholds from EHR race categories. See the BMI source and scope notes.

Data Description The source file contains rows of BMI-related information, which is tab-delimited. Each row includes:

  • A person’s unique identifier (person_id)
  • An encounter identifier (encounter_id)
  • A numerical BMI value (bmi)
  • The person’s height in centimeters (height_cm)
  • The person’s weight in kilograms (weight_kg)
  • The date of measurement (measurement_date)
  • The synthetic date of birth (date_of_birth), supplied in the input or a separate keyed file

The synthetic fixture imitates EHR measurement data, including multiple rows per person.

Task to Be Accomplished

  1. Read the Data
    • Import the tab-delimited file.
    • Convert columns to appropriate types (particularly for dates).
    • If date of birth comes from a separate file, require one demographic row per person_id and validate the join cardinality.
  2. Clean and Filter
    • Determine age at each measurement using calendar dates. Flag and exclude records with missing or invalid dates, measurement before birth, or age below 20; do not substitute age at extraction.
    • Require finite, positive heights and weights in the declared units; flag missing, nonfinite, nonpositive, or otherwise unreasonable values with reason codes.
    • Check for and flag large discrepancies between reported BMI and the BMI computed from height and weight.
    • Define the height/weight ranges and discrepancy tolerance before coding. Calculate unrounded BMI as weight_kg / (height_cm / 100)^2, require a finite positive result, and use it for comparisons, selection, and categories; round only display values.
    • Write the flagged entries in a separate file and continue only with the rows that pass these checks.
  3. Representative Record Selection
    • For each person, flag extreme outlier measurements using a prespecified IQR rule. Before implementation, record the multiplier, quantile method, minimum measurement count, and policy for zero IQR; report flags separately and state whether each flag excludes a row.
    • For each person, select one “typical” row based on the median BMI among the remaining records.
    • If there are no valid measurements left for a person, flag them and output them in a separate file.
    • If there are multiple valid measurements, select the one closest to the median BMI.
    • 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.
  4. Categorize BMI, Height, and Weight
    • Assign descriptive categories using unrounded calculated BMI, defined as:
      • Underweight: BMI < 18.5 kg/m²
      • Normal: 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²
    • Create illustrative height bins (Short, Average, Tall), defined as:
      • Short: height < 150 cm
      • Average: 150 cm ≤ height < 180 cm
      • Tall: height ≥ 180 cm
    • Create illustrative weight bins (Light, Medium, Heavy, etc.), defined as:
      • Light: weight < 50 kg
      • Medium: 50 kg ≤ weight < 80 kg
      • Heavy: 80 kg ≤ weight < 100 kg
      • Very Heavy: weight ≥ 100 kg
    • Height and weight bins are arbitrary tutorial examples; they do not represent clinical standards.

Expected Output

  1. Cleaned Dataset
    • One TSV file with one row per person:
      • The “typical” BMI, plus the height, weight, and date of that measurement.
      • Categorical variables for BMI, height, and weight.
  2. Summary Table
    • A summary about the flagged entries, including:
      • The number of rows removed due to missing or implausible values.
      • The number of rows removed due to large discrepancies between reported and calculated BMI.
      • The number of extreme outlier measurements removed.
      • The number of individuals with only invalid measurements.
      • The number of individuals with valid measurements.
      • The distribution of the number of BMI measurements (e.g., mean, median, standard deviation) per person.
    • A short overview of how many individuals fall into each BMI category (plus height/weight categories).
    • May be saved as a text or Markdown file.
  3. Data Dictionary
    • A separate text listing each column and a brief explanation (e.g., person_id: a unique identifier for the person).

Acceptance Checks

  • A measurement on the 20th birthday is eligible; the preceding day and missing/invalid birth dates are excluded with distinct reason codes.
  • Synthetic BMI values at 18.5, 25, 30, 35, and 40 enter the category beginning at that boundary. A value just below each boundary remains in the preceding category even if its displayed value rounds up.
  • Missing, nonfinite, and nonpositive measurements or calculated BMI are rejected with reason codes before selection.
  • Selection retains an observed row, uses the documented tie-breakers, and produces at most one row per eligible person; input order does not change the result.
  • Rejected rows retain reason codes, output counts reconcile with processing counts, and an empty eligible dataset produces the documented empty output schema.
  • Record the exact fixture command and expected assertions before implementation, then report the command result.