Potential Solutions

Important

Note that LLMs are stochastic/non-deteriministic, so there are no definitive solutions.

Hands-On Exercise: Screening Patient Data for Clinical Trial Eligibility

Exercise: Search for a kidney-related active trial on clinicaltrials.gov and copy the Participation Criteria. Prompt LLM to write code in language of your choice (Python, R, Julia, SPSS, Stata) identifying eligible patients in EPIC using relevant columns in EPIC’s Clarity Database Schema.

Potential Solutions

Actively Recruiting Clinical Trial Example

An Intervention to Reduce Sedentary Behavior for Adults With Chronic Kidney Disease (RESET-CKD)

Prompt:

You are an senior data engineer at a world-class health system. You are fluent in Python, all flavors of SQL, and are intimately familiar with the schema for EPIC’s Clarity Database.

Use this Clinical Trial participation criteria:

Inclusion Criteria:

Self-identifies as Black or African American
Age ≥35 to ≤80 years old
CKD Stage 1-4
Self-reported 6 or more hours/day of sedentary time
Ability to speak/read/understand English
Has access to a telephone

Exclusion Criteria:

Being unable to walk 1 block
Being unable to stand from a seated position without assistance
Using a wheelchair, walker, or cane for all of their ambulation
Presence of a condition(s) or diagnosis, either physical or psychological, that precludes participation, including: Lower-extremity amputation (AKA or BKA) without prosthetic, Orthopedic condition that would preclude prompted sit-to-stand transitions or standing, Neurologic or psychiatric condition that would preclude prompted sit-to- stand transitions or standing, Severe cognitive impairment, Unstable coronary artery disease (i.e., angina with activity), Orthostatic hypotension
Kidney transplant
Any other condition that the investigator considers precludes participation in the trial
Participated in the Patient Advisory Board

Your job is to write valid Python code to identify eligible patients in your health system’s EPIC Clarity Database using the given Participation Criteria.

—–REQUIREMENTS——–

Include steps for creating a virtual environment using {uv}
Use environment variables for securely storing and accessing any relevant API Keys and Secrets
Use {polars} for querying the relevant columns and variables in Clarity and any data manipulation operations
Clearly state if there are missing columns that are necessary for making an accurate identification of eligible patients based on the Participation Criteria

—–DO NOT————–

Write code that could potentially expose any PHI

Scaffold the project so the user can:

Set up their environment on their secured machine.
Query EPIC and SECURELY identify patients eligible to participate in the clinical trial

Response:

Below is a secure project scaffold and Python implementation pattern for identifying potentially eligible patients for this trial from an EPIC Clarity environment.

Two important realities up front:

Some trial criteria are often not available as reliable structured Clarity fields and therefore cannot be used for a fully automated eligibility determination without local enrichment, manual review, or both.
Exact Clarity table/column names and category values vary by organization and Epic version, so the SQL extraction layer should be treated as a local mapping step. The Python below is valid and production-oriented; the SQL includes clearly marked places where your local Clarity analyst should map the exact source tables/columns.

Polars can read query results from a database using pl.read_database / pl.read_database_uri, SQLAlchemy supports database URLs and connection configuration, and Microsoft’s ODBC Driver 18 enables encrypted SQL Server connections by default.

What can be automated vs. what cannot

Usually automatable or semi-automatable from Clarity

These are often available in structured form somewhere in Clarity or derivable from it:

race / ethnicity proxy for “self-identifies as Black or African American”
age
CKD stage 1–4, if derived from diagnoses and/or eGFR labs
preferred language = English
active phone on file
diagnoses suggesting kidney transplant
diagnoses suggesting severe cognitive impairment, unstable CAD/angina, orthostatic hypotension, lower-extremity amputation
durable medical equipment / flowsheet / problem-list proxies for wheelchair / walker / cane dependence

Usually not fully automatable from Clarity alone

These criteria commonly require survey data, research registry data, chart review, or investigator adjudication:

self-reported 6+ hours/day sedentary time
ability to speak/read/understand English beyond preferred language proxy
unable to walk 1 block
unable to stand from seated position without assistance
using wheelchair/walker/cane for all ambulation
orthopedic / neurologic / psychiatric condition that would preclude prompted sit-to-stand transitions
any other condition investigator considers precludes participation
participated in the Patient Advisory Board unless your org tracks this in a research registry or custom table

So the correct deliverable is a secure pre-screening pipeline that returns:

likely_eligible
manual_review_required
not_eligible

Recommended project structure

trial_eligibility/
├─ .env.example
├─ .gitignore
├─ pyproject.toml
├─ README.md
├─ sql/
│  └─ candidate_extract.sql
└─ src/
   └─ trial_eligibility/
      ├─ __init__.py
      ├─ settings.py
      ├─ db.py
      ├─ logic.py
      └─ main.py

1) Secure environment setup with `uv`

Commands

uv venv

Linux / macOS

source .venv/bin/activate

Windows PowerShell

.venv\Scripts\Activate.ps1

Install dependencies:

uv pip install polars sqlalchemy pyodbc python-dotenv pydantic-settings

Or use the pyproject.toml below and run:

uv sync

2) `pyproject.toml`

[project] 
name = "trial-eligibility" 
version = "0.1.0" 
description = "Secure EPIC Clarity pre-screening pipeline for clinical trial eligibility" 
requires-python = ">=3.11" 
dependencies = [   
  "polars>=1.30.0",   
  "sqlalchemy>=2.0.0",   
  "pyodbc>=5.1.0",   
  "python-dotenv>=1.0.1",   
  "pydantic-settings>=2.2.1", 
  ]  

[tool.uv] package = true

3) `.gitignore`

.venv/ .env __pycache__/ *.pyc outputs/

4) `.env.example`

Do not commit the real .env.

# SQL Server / Clarity connection CLARITY_HOST=your-sql-server-host CLARITY_PORT=1433 CLARITY_DATABASE=Clarity CLARITY_USERNAME=service_account_username CLARITY_PASSWORD=super_secret_password CLARITY_ODBC_DRIVER=ODBC Driver 18 for SQL Server CLARITY_ENCRYPT=yes CLARITY_TRUST_SERVER_CERTIFICATE=no  # Output OUTPUT_DIR=./outputs  # Used to generate irreversible study IDs from internal patient IDs STUDY_ID_SALT=replace_with_long_random_secret  # Optional: limit how many rows are returned during testing ROW_LIMIT=5000

5) `src/trial_eligibility/settings.py`

Code

from pydantic_settings import BaseSettings, SettingsConfigDict   

class Settings(BaseSettings):     
  clarity_host: str     
  clarity_port: int = 1433     
  clarity_database: str     
  clarity_username: str     
  clarity_password: str     
  clarity_odbc_driver: str = "ODBC Driver 18 for SQL Server"     
  clarity_encrypt: str = "yes"     
  clarity_trust_server_certificate: str = "no"      
  output_dir: str = "./outputs"     
  study_id_salt: str     
  row_limit: int = 5000      
  model_config = SettingsConfigDict(         
    env_file=".env",         
    env_file_encoding="utf-8",         
    extra="ignore",     
  )   
  
  settings = Settings()

6) `src/trial_eligibility/db.py`

This uses SQLAlchemy for the connection and Polars for loading query results. Polars supports reading database query results directly, and SQLAlchemy supports structured connection URLs. Microsoft documents that ODBC Driver 18 enables encryption by default, which is appropriate for protected environments.

Code

from __future__ import annotations

from pathlib import Path
from urllib.parse import quote_plus

import polars as pl
from sqlalchemy import create_engine
from sqlalchemy.engine import Engine

from trial_eligibility.settings import settings


def build_connection_string() -> str:
    driver = quote_plus(settings.clarity_odbc_driver)
    password = quote_plus(settings.clarity_password)

    return (
        f"mssql+pyodbc://{settings.clarity_username}:{password}"
        f"@{settings.clarity_host}:{settings.clarity_port}/{settings.clarity_database}"
        f"?driver={driver}"
        f"&Encrypt={settings.clarity_encrypt}"
        f"&TrustServerCertificate={settings.clarity_trust_server_certificate}"
    )


def get_engine() -> Engine:
    return create_engine(
        build_connection_string(),
        pool_pre_ping=True,
        future=True,
    )


def read_sql_file(path: str | Path) -> str:
    return Path(path).read_text(encoding="utf-8")


def read_polars(query: str) -> pl.DataFrame:
    engine = get_engine()
    with engine.connect() as conn:
        return pl.read_database(query=query, connection=conn)

7) `sql/candidate_extract.sql`

This query is intentionally PHI-minimizing. It does not select patient name, address, email, or MRN for output. It uses an internal durable patient key only, which will later be hashed into a study ID.

You must map the placeholder table/column names to your local Clarity schema. The point is to extract only the minimum structured data needed for pre-screening.

/*
IMPORTANT:
- Replace ALL placeholder table/column names with your local Clarity mappings.
- Do not add direct identifiers like patient name, address, or MRN to the final output.
- Keep this query restricted to minimum necessary data.

Expected output columns:
  patient_id
  birth_date
  race
  language
  has_phone
  ckd_stage_dx
  latest_egfr
  kidney_transplant_flag
  severe_cognitive_impairment_flag
  unstable_cad_flag
  orthostatic_hypotension_flag
  amputation_flag
  severe_psych_neuro_flag
  ortho_condition_flag
  mobility_device_dependence_flag
  unable_walk_block_flag
  unable_stand_without_assist_flag
  sedentary_hours_per_day
  patient_advisory_board_flag
*/

WITH
-- 1) Core patient demographics
patient_core AS (
    SELECT
        p.PAT_ID                AS patient_id,
        p.BIRTH_DATE            AS birth_date,
        p.PREFERRED_LANGUAGE_C  AS language_code,
        CASE
            WHEN p.HOME_PHONE IS NOT NULL
              OR p.MOBILE_PHONE IS NOT NULL
              OR p.WORK_PHONE IS NOT NULL
            THEN 1 ELSE 0
        END AS has_phone
    FROM CLARITY_PATIENT p
    WHERE p.IS_TEST_PAT_YN = 'N'
),

-- 2) Race
patient_race AS (
    SELECT
        pr.PAT_ID AS patient_id,
        MAX(CASE
            WHEN zr.NAME IN ('Black or African American', 'Black', 'African American')
            THEN 1 ELSE 0
        END) AS black_or_african_american_flag
    FROM PATIENT_RACE pr
    JOIN ZC_PATIENT_RACE zr
      ON pr.PATIENT_RACE_C = zr.PATIENT_RACE_C
    GROUP BY pr.PAT_ID
),

-- 3) Preferred language
patient_language AS (
    SELECT
        zpl.LANGUAGE_C,
        zpl.NAME AS language_name
    FROM ZC_LANGUAGE zpl
),

-- 4) CKD diagnosis proxy
ckd_dx AS (
    SELECT
        dx.PAT_ID AS patient_id,
        MAX(CASE
            WHEN dx.ICD10_CODE LIKE 'N18.1%' THEN 1 ELSE 0
        END) AS ckd_stage_1_dx,
        MAX(CASE
            WHEN dx.ICD10_CODE LIKE 'N18.2%' THEN 1 ELSE 0
        END) AS ckd_stage_2_dx,
        MAX(CASE
            WHEN dx.ICD10_CODE LIKE 'N18.3%' THEN 1 ELSE 0
        END) AS ckd_stage_3_dx,
        MAX(CASE
            WHEN dx.ICD10_CODE LIKE 'N18.4%' THEN 1 ELSE 0
        END) AS ckd_stage_4_dx
    FROM PROBLEM_LIST_DX dx
    GROUP BY dx.PAT_ID
),

-- 5) Latest eGFR lab proxy
latest_egfr AS (
    SELECT patient_id, result_numeric AS latest_egfr
    FROM (
        SELECT
            lr.PAT_ID AS patient_id,
            TRY_CAST(lr.RESULT_VALUE AS FLOAT) AS result_numeric,
            ROW_NUMBER() OVER (
                PARTITION BY lr.PAT_ID
                ORDER BY lr.RESULT_DATE DESC
            ) AS rn
        FROM LAB_RESULT lr
        WHERE lr.COMPONENT_NAME IN ('eGFR', 'Estimated GFR')
          AND TRY_CAST(lr.RESULT_VALUE AS FLOAT) IS NOT NULL
    ) x
    WHERE rn = 1
),

-- 6) Exclusion diagnoses / conditions
exclusion_dx AS (
    SELECT
        dx.PAT_ID AS patient_id,
        MAX(CASE
            WHEN dx.ICD10_CODE LIKE 'Z94.0%' OR dx.ICD10_CODE LIKE 'T86.1%'
            THEN 1 ELSE 0
        END) AS kidney_transplant_flag,

        MAX(CASE
            WHEN dx.ICD10_CODE IN ('I20.0','I20.8','I20.9','I25.11','I25.118','I25.119')
            THEN 1 ELSE 0
        END) AS unstable_cad_flag,

        MAX(CASE
            WHEN dx.ICD10_CODE LIKE 'I95.1%'
            THEN 1 ELSE 0
        END) AS orthostatic_hypotension_flag,

        MAX(CASE
            WHEN dx.ICD10_CODE LIKE 'Z89.5%' OR dx.ICD10_CODE LIKE 'Z89.6%'
            THEN 1 ELSE 0
        END) AS amputation_flag,

        MAX(CASE
            WHEN dx.ICD10_CODE LIKE 'F01%'
              OR dx.ICD10_CODE LIKE 'F02%'
              OR dx.ICD10_CODE LIKE 'F03%'
              OR dx.ICD10_CODE LIKE 'G30%'
            THEN 1 ELSE 0
        END) AS severe_cognitive_impairment_flag,

        MAX(CASE
            WHEN dx.ICD10_CODE LIKE 'F20%'
              OR dx.ICD10_CODE LIKE 'F31%'
              OR dx.ICD10_CODE LIKE 'G20%'
              OR dx.ICD10_CODE LIKE 'G35%'
              OR dx.ICD10_CODE LIKE 'G80%'
            THEN 1 ELSE 0
        END) AS severe_psych_neuro_flag,

        MAX(CASE
            WHEN dx.ICD10_CODE LIKE 'M16%'
              OR dx.ICD10_CODE LIKE 'M17%'
              OR dx.ICD10_CODE LIKE 'M48%'
            THEN 1 ELSE 0
        END) AS ortho_condition_flag
    FROM PROBLEM_LIST_DX dx
    GROUP BY dx.PAT_ID
),

-- 7) Mobility / functional status placeholders
mobility_status AS (
    SELECT
        m.PAT_ID AS patient_id,
        MAX(CASE WHEN m.ALL_AMBULATION_WITH_DEVICE_YN = 1 THEN 1 ELSE 0 END) AS mobility_device_dependence_flag,
        MAX(CASE WHEN m.UNABLE_WALK_ONE_BLOCK_YN = 1 THEN 1 ELSE 0 END) AS unable_walk_block_flag,
        MAX(CASE WHEN m.UNABLE_STAND_FROM_SEATED_WITHOUT_ASSIST_YN = 1 THEN 1 ELSE 0 END) AS unable_stand_without_assist_flag
    FROM PATIENT_MOBILITY_ASSESSMENT m
    GROUP BY m.PAT_ID
),

-- 8) Sedentary behavior placeholder
sedentary_behavior AS (
    SELECT
        s.PAT_ID AS patient_id,
        MAX(s.SEDENTARY_HOURS_PER_DAY) AS sedentary_hours_per_day
    FROM RESEARCH_SCREENING_QUESTIONNAIRE s
    GROUP BY s.PAT_ID
),

-- 9) Research / advisory board participation placeholder
advisory_board AS (
    SELECT
        a.PAT_ID AS patient_id,
        1 AS patient_advisory_board_flag
    FROM RESEARCH_PATIENT_ADVISORY_BOARD a
)

SELECT TOP (@ROW_LIMIT)
    pc.patient_id,
    pc.birth_date,
    COALESCE(pr.black_or_african_american_flag, 0) AS black_or_african_american_flag,
    pl.language_name AS language,
    pc.has_phone,

    CASE
        WHEN COALESCE(cd.ckd_stage_4_dx, 0) = 1 THEN 4
        WHEN COALESCE(cd.ckd_stage_3_dx, 0) = 1 THEN 3
        WHEN COALESCE(cd.ckd_stage_2_dx, 0) = 1 THEN 2
        WHEN COALESCE(cd.ckd_stage_1_dx, 0) = 1 THEN 1
        ELSE NULL
    END AS ckd_stage_dx,

    le.latest_egfr,

    COALESCE(ed.kidney_transplant_flag, 0) AS kidney_transplant_flag,
    COALESCE(ed.severe_cognitive_impairment_flag, 0) AS severe_cognitive_impairment_flag,
    COALESCE(ed.unstable_cad_flag, 0) AS unstable_cad_flag,
    COALESCE(ed.orthostatic_hypotension_flag, 0) AS orthostatic_hypotension_flag,
    COALESCE(ed.amputation_flag, 0) AS amputation_flag,
    COALESCE(ed.severe_psych_neuro_flag, 0) AS severe_psych_neuro_flag,
    COALESCE(ed.ortho_condition_flag, 0) AS ortho_condition_flag,

    ms.mobility_device_dependence_flag,
    ms.unable_walk_block_flag,
    ms.unable_stand_without_assist_flag,

    sb.sedentary_hours_per_day,
    COALESCE(ab.patient_advisory_board_flag, 0) AS patient_advisory_board_flag

FROM patient_core pc
LEFT JOIN patient_race pr
    ON pc.patient_id = pr.patient_id
LEFT JOIN patient_language pl
    ON pc.language_code = pl.LANGUAGE_C
LEFT JOIN ckd_dx cd
    ON pc.patient_id = cd.patient_id
LEFT JOIN latest_egfr le
    ON pc.patient_id = le.patient_id
LEFT JOIN exclusion_dx ed
    ON pc.patient_id = ed.patient_id
LEFT JOIN mobility_status ms
    ON pc.patient_id = ms.patient_id
LEFT JOIN sedentary_behavior sb
    ON pc.patient_id = sb.patient_id
LEFT JOIN advisory_board ab
    ON pc.patient_id = ab.patient_id;

8) `src/trial_eligibility/logic.py`

This is where the inclusion/exclusion logic is applied with Polars. It intentionally creates a manual-review path whenever required criteria are not available or are only weakly proxied.

Code

from __future__ import annotations

import hashlib
import hmac
from datetime import date

import polars as pl

from trial_eligibility.settings import settings


def stable_study_id(patient_id: str | int) -> str:
    """
    Irreversible HMAC-based study ID.
    Do not expose raw patient IDs in exported files.
    """
    msg = str(patient_id).encode("utf-8")
    key = settings.study_id_salt.encode("utf-8")
    digest = hmac.new(key, msg, hashlib.sha256).hexdigest()
    return digest[:16]


def infer_ckd_stage_from_egfr(latest_egfr: float | None) -> int | None:
    """
    Conservative eGFR-only approximation.
    Trial includes CKD Stage 1-4 and excludes stage 5.
    Stage 1 usually requires evidence of kidney damage and cannot be inferred from eGFR alone.
    """
    if latest_egfr is None:
        return None
    if latest_egfr >= 90:
        return 1
    if 60 <= latest_egfr < 90:
        return 2
    if 30 <= latest_egfr < 60:
        return 3
    if 15 <= latest_egfr < 30:
        return 4
    return 5


def classify_candidates(df: pl.DataFrame) -> pl.DataFrame:
    today = date.today()

    working = (
        df
        .with_columns([
            pl.col("birth_date").cast(pl.Date),
            (
                pl.lit(today).cast(pl.Date).dt.year()
                - pl.col("birth_date").dt.year()
                - (
                    (
                        (pl.lit(today.month) < pl.col("birth_date").dt.month())
                        | (
                            (pl.lit(today.month) == pl.col("birth_date").dt.month())
                            & (pl.lit(today.day) < pl.col("birth_date").dt.day())
                        )
                    ).cast(pl.Int8)
                )
            ).alias("age_years"),
        ])
        .with_columns([
            pl.col("latest_egfr")
              .map_elements(infer_ckd_stage_from_egfr, return_dtype=pl.Int64)
              .alias("ckd_stage_egfr"),
            pl.coalesce(
                [pl.col("ckd_stage_dx"), pl.col("ckd_stage_egfr")]
            ).alias("ckd_stage_final"),
            pl.col("language").fill_null("UNKNOWN").str.to_uppercase().alias("language_norm"),
            pl.col("sedentary_hours_per_day").cast(pl.Float64, strict=False),
        ])
        .with_columns([
            (pl.col("black_or_african_american_flag") == 1).alias("meets_race"),
            (pl.col("age_years").is_between(35, 80, closed="both")).alias("meets_age"),
            (pl.col("ckd_stage_final").is_between(1, 4, closed="both")).alias("meets_ckd"),
            (pl.col("language_norm") == "ENGLISH").alias("meets_language_proxy"),
            (pl.col("has_phone") == 1).alias("meets_phone"),
            (pl.col("sedentary_hours_per_day") >= 6.0).alias("meets_sedentary"),
        ])
        .with_columns([
            (
                (pl.col("kidney_transplant_flag") == 1)
                | (pl.col("severe_cognitive_impairment_flag") == 1)
                | (pl.col("unstable_cad_flag") == 1)
                | (pl.col("orthostatic_hypotension_flag") == 1)
                | (pl.col("amputation_flag") == 1)
                | (pl.col("severe_psych_neuro_flag") == 1)
                | (pl.col("ortho_condition_flag") == 1)
                | (pl.col("mobility_device_dependence_flag").fill_null(0) == 1)
                | (pl.col("unable_walk_block_flag").fill_null(0) == 1)
                | (pl.col("unable_stand_without_assist_flag").fill_null(0) == 1)
                | (pl.col("patient_advisory_board_flag") == 1)
                | (pl.col("ckd_stage_final") == 5)
            ).alias("has_structured_exclusion")
        ])
        .with_columns([
            (
                pl.col("sedentary_hours_per_day").is_null()
                | pl.col("language").is_null()
                | pl.col("ckd_stage_final").is_null()
                | pl.col("mobility_device_dependence_flag").is_null()
                | pl.col("unable_walk_block_flag").is_null()
                | pl.col("unable_stand_without_assist_flag").is_null()
            ).alias("needs_manual_review")
        ])
        .with_columns([
            pl.when(
                pl.col("has_structured_exclusion")
            )
            .then(pl.lit("not_eligible"))
            .when(
                pl.col("meets_race")
                & pl.col("meets_age")
                & pl.col("meets_ckd")
                & pl.col("meets_language_proxy")
                & pl.col("meets_phone")
                & pl.col("meets_sedentary")
                & ~pl.col("needs_manual_review")
            )
            .then(pl.lit("likely_eligible"))
            .otherwise(pl.lit("manual_review_required"))
            .alias("screening_status")
        ])
        .with_columns([
            pl.col("patient_id")
              .map_elements(stable_study_id, return_dtype=pl.String)
              .alias("study_id")
        ])
    )

    # Return a PHI-minimized output
    return (
        working
        .select([
            "study_id",
            "screening_status",
            "age_years",
            "ckd_stage_final",
            "meets_race",
            "meets_age",
            "meets_ckd",
            "meets_language_proxy",
            "meets_phone",
            "meets_sedentary",
            "has_structured_exclusion",
            "needs_manual_review",
        ])
        .sort(["screening_status", "study_id"])
    )

9) `src/trial_eligibility/main.py`

Code

from __future__ import annotations

from pathlib import Path

import polars as pl

from trial_eligibility.db import read_polars, read_sql_file
from trial_eligibility.logic import classify_candidates
from trial_eligibility.settings import settings


def render_query(template: str) -> str:
    return template.replace("@ROW_LIMIT", str(settings.row_limit))


def main() -> None:
    output_dir = Path(settings.output_dir)
    output_dir.mkdir(parents=True, exist_ok=True)

    raw_sql = read_sql_file("sql/candidate_extract.sql")
    query = render_query(raw_sql)

    source_df: pl.DataFrame = read_polars(query)
    result_df: pl.DataFrame = classify_candidates(source_df)

    result_path = output_dir / "trial_candidate_prescreen.csv"
    summary_path = output_dir / "trial_candidate_prescreen_summary.csv"

    result_df.write_csv(result_path)

    (
        result_df
        .group_by("screening_status")
        .agg(pl.len().alias("patient_count"))
        .sort("screening_status")
        .write_csv(summary_path)
    )

    print(f"Wrote: {result_path}")
    print(f"Wrote: {summary_path}")


if __name__ == "__main__":
    main()

10) `README.md`

# Trial Eligibility Pre-Screening  

## Purpose Securely identify potentially eligible patients for a CKD-related clinical trial from EPIC Clarity using PHI-minimized outputs.  

## Setup  

### Create environment 
```bash 
uv venv 
source .venv/bin/activate 

# Windows PowerShell: 
# .venv\Scripts\Activate.ps1 
uv sync
```

Configure secrets

Copy .env.example to .env and populate secure values.

Run

python -m trial_eligibility.main

Security notes

Do not commit .env
Use a least-privilege service account
Output only hashed study IDs
Do not export names, MRNs, addresses, or dates of birth
Store outputs only on approved secured storage

Required local mapping

The SQL file contains placeholder Clarity table and column names that must be mapped to your local environment.

Screening categories

likely_eligible
manual_review_required
not_eligible

 ---  
 
 # 11) Missing columns / data elements required for accurate identification  These are the biggest gaps you should explicitly call out in your implementation and validation memo:  
 
 ## Missing or commonly non-structured criteria The following are **necessary for accurate eligibility**, but are often **not reliably present in structured Clarity columns**:  
 
 - **Self-reported sedentary time ≥ 6 hours/day** 
 - **Ability to speak/read/understand English** 
 - **Unable to walk 1 block** 
 - **Unable to stand from a seated position without assistance** 
 - **Using wheelchair/walker/cane for all ambulation** 
 - **Orthopedic condition precluding sit-to-stand transitions** 
 - **Neurologic or psychiatric condition precluding sit-to-stand transitions** 
 - **Any other investigator-determined exclusion** 
 - **Participation in the Patient Advisory Board**, unless tracked in a research dataset or local registry  
 
 ## CKD stage caveat CKD stage may require a blend of:  
 
 - problem-list / encounter diagnoses 
 - lab-derived eGFR 
 - possibly albuminuria or evidence of kidney damage for stage 1  
 A diagnosis-only approach will miss patients. An eGFR-only approach may misclassify stage 1 and transient AKI cases.  
 
 ## Race caveat The criterion says **“self-identifies as Black or African American.”**   Clarity race fields may be the best available proxy, but you should still document that this is a **structured demographic proxy** and may not exactly reflect current self-identification in all cases.  
 
 ## Language caveat “Ability to speak/read/understand English” is **not the same** as preferred language = English. Preferred language is only a screening proxy.  
 
 ---  
 
 # 12) Security guidance  
 
 Use a **least-privilege service account** and do not export direct identifiers. ODBC Driver 18 supports encrypted connections, which should be retained in a protected environment.   
 
 Recommended operational controls:  
 
 - use a read-only service account scoped to only necessary Clarity objects 
 - run only on an approved secured workstation or server 
 - keep `.env` out of version control 
 - export only hashed `study_id` 
 - do not include name, MRN, DOB, address, email, or phone in final extracts 
 - store the candidate file only in approved restricted storage - log only aggregate counts, never raw row payloads  
 
 ---  
 
 # 13) What this gives the user  
 
 This scaffold lets the user:  
 
 1. **Set up a secure environment** with `uv` 
 2. **Connect securely** to Clarity using environment variables 
 3. **Query Clarity with Polars** 
 4. **Apply structured inclusion/exclusion logic** 
 5. **Produce a PHI-minimized candidate file** with hashed study IDs 
 6. **Separate clear positives from manual-review cases**, which is the correct operational pattern for this trial  
 
 The most important implementation step now is the **local mapping of the SQL placeholders to your organization’s actual Clarity tables and category values**.  
 
 I can turn this into a single ready-to-run repository bundle next, with the SQL placeholders replaced by a more Clarity-specific mapping template.

Prompt:

You are an expert biostatistician and epidemiologist, extremely fluent with EPIC’s Clarity Database.

Given this Clinical Trial participation criteria:

Inclusion Criteria:

Self-identifies as Black or African American
Age ≥35 to ≤80 years old
CKD Stage 1-4
Self-reported 6 or more hours/day of sedentary time
Ability to speak/read/understand English
Has access to a telephone

Exclusion Criteria:

Being unable to walk 1 block
Being unable to stand from a seated position without assistance
Using a wheelchair, walker, or cane for all of their ambulation
Presence of a condition(s) or diagnosis, either physical or psychological, that precludes participation, including: Lower-extremity amputation (AKA or BKA) without prosthetic, Orthopedic condition that would preclude prompted sit-to-stand transitions or standing, Neurologic or psychiatric condition that would preclude prompted sit-to- stand transitions or standing, Severe cognitive impairment, Unstable coronary artery disease (i.e., angina with activity), Orthostatic hypotension
Kidney transplant
Any other condition that the investigator considers precludes participation in the trial
Participated in the Patient Advisory Board

—Steps—

1. Provide valid SQL for identifying eligible patients in your health system’s Clarity Database.

2. Make a table of the codebook for the relevant column names along with their descriptions in the form: {column_name}: {variable description, including data type}.

Response:

Here is the SQL and codebook for identifying eligible patients in Epic Clarity.

#| eval: false
/*=============================================================
  CLINICAL TRIAL ELIGIBILITY: Black/AA Adults with CKD 1–4
  Sedentary Lifestyle Intervention Study
  
  Inclusion:
    - Self-identified Black/African American
    - Age 35–80
    - CKD Stage 1–4 (eGFR ≥15, no ESRD/transplant)
    - Has a telephone on file
  Exclusion:
    - Kidney transplant (Hx or active)
    - Lower extremity amputation (w/o prosthetic) — flagged via Dx
    - Orthostatic hypotension Dx
    - Unstable angina/CAD Dx
    - Severe cognitive impairment Dx
    - Active psychiatric/neurologic preclusion Dx
    - Wheelchair/walker/assistive device dependency (ADL note flags)
    - Participated in Patient Advisory Board (research flag)
  
  Notes on sedentary time and functional mobility:
    These are self-reported and typically NOT structured in Clarity.
    Flag them for manual/survey screening as noted below.
=============================================================*/

WITH

-- ── 1. DEMOGRAPHICS: Black/AA, Age 35–80 ─────────────────────────────────────
eligible_demographics AS (
    SELECT
        p.PAT_ID,
        p.PAT_MRN_ID,
        p.PAT_FIRST_NAME,
        p.PAT_LAST_NAME,
        p.BIRTH_DATE,
        DATEDIFF(YEAR, p.BIRTH_DATE, GETDATE())
            - CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, p.BIRTH_DATE, GETDATE()), p.BIRTH_DATE) > GETDATE()
                   THEN 1 ELSE 0 END                        AS AGE_YEARS,
        p.SEX_C,
        zr.NAME                                             AS RACE_NAME,
        p.PAT_STATUS                                        -- 1 = Alive
    FROM PATIENT p
    JOIN ZC_PATIENT_RACE zpr  ON zpr.PAT_ID = p.PAT_ID
    JOIN ZC_RACE          zr  ON zr.RACE_C  = zpr.RACE_C
    WHERE
        zr.NAME IN (
            'Black or African American',
            'Black/African American',
            'African American'          -- varies by site mapping
        )
        AND DATEDIFF(YEAR, p.BIRTH_DATE, GETDATE())
            - CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, p.BIRTH_DATE, GETDATE()), p.BIRTH_DATE) > GETDATE()
                   THEN 1 ELSE 0 END  BETWEEN 35 AND 80
        AND p.PAT_STATUS = 1           -- living patients only
),

-- ── 2. TELEPHONE ON FILE ──────────────────────────────────────────────────────
has_phone AS (
    SELECT DISTINCT PAT_ID
    FROM PAT_PHONE
    WHERE PHONE_TYPE_C IN (1, 2, 3)    -- Home=1, Work=2, Mobile=3; verify your site's ZC_PHONE_TYPE
      AND PHONE_NUM IS NOT NULL
      AND PHONE_NUM <> ''
),

-- ── 3. CKD STAGE 1–4 ─────────────────────────────────────────────────────────
-- Primary approach: most recent eGFR lab value
--   CKD Stage 1: eGFR ≥90 (requires albuminuria/structural marker — see note)
--   CKD Stage 2: eGFR 60–89
--   CKD Stage 3a: 45–59  | Stage 3b: 30–44
--   CKD Stage 4: 15–29
--   CKD Stage 5/ESRD: <15 → EXCLUDE
--
-- Secondary approach: active problem list CKD ICD-10 codes (see codebook below)
--
-- NOTE: Stage 1 without a documented CKD Dx code is hard to distinguish from
--       normal renal function purely from eGFR. Consider requiring a CKD Dx.

ckd_egfr AS (
    SELECT
        r.PAT_ID,
        r.RESULT_TIME,
        TRY_CAST(r.ORD_VALUE AS FLOAT)  AS EGFR_VALUE,
        ROW_NUMBER() OVER (
            PARTITION BY r.PAT_ID
            ORDER BY r.RESULT_TIME DESC
        )                               AS RN
    FROM ORDER_RESULTS r
    JOIN CLARITY_COMPONENT cc
        ON cc.COMPONENT_ID = r.COMPONENT_ID
    WHERE
        -- Common LOINC codes for eGFR (CKD-EPI, MDRD variants)
        cc.LNC_CODE IN (
            '33914-3',   -- eGFR MDRD
            '50044-7',   -- eGFR CKD-EPI (Females)
            '48642-3',   -- eGFR non-Black MDRD
            '48643-1',   -- eGFR Black MDRD
            '62238-1',   -- eGFR CKD-EPI 2009
            '98979-8'    -- eGFR CKD-EPI 2021 (race-free)
        )
        AND r.RESULT_STATUS_C = 3       -- Final result
        AND TRY_CAST(r.ORD_VALUE AS FLOAT) IS NOT NULL
),

ckd_stage AS (
    SELECT
        PAT_ID,
        EGFR_VALUE,
        CASE
            WHEN EGFR_VALUE >= 90 THEN 'Stage 1 (eGFR only — confirm Dx)'
            WHEN EGFR_VALUE BETWEEN 60 AND 89.9 THEN 'Stage 2'
            WHEN EGFR_VALUE BETWEEN 45 AND 59.9 THEN 'Stage 3a'
            WHEN EGFR_VALUE BETWEEN 30 AND 44.9 THEN 'Stage 3b'
            WHEN EGFR_VALUE BETWEEN 15 AND 29.9 THEN 'Stage 4'
            WHEN EGFR_VALUE < 15              THEN 'Stage 5/ESRD — EXCLUDE'
        END                             AS CKD_STAGE_LABEL,
        RESULT_TIME                     AS EGFR_DATE
    FROM ckd_egfr
    WHERE RN = 1
),

-- CKD confirmed via active problem list (belt-and-suspenders)
ckd_dx AS (
    SELECT DISTINCT pl.PAT_ID
    FROM PROBLEM_LIST pl
    JOIN CL_ICD10_PX icd ON icd.CODE = pl.ICD10_CODE
    WHERE
        pl.PROBLEM_STATUS_C = 1             -- Active
        AND pl.ICD10_CODE IN (
            -- CKD Stage 1–4 ICD-10 codes
            'N18.1','N18.2','N18.30','N18.31','N18.32','N18.4',
            -- Diabetic CKD Stage 1–4
            'E11.21','E11.22','E10.21','E10.22',
            -- Hypertensive CKD Stage 1–4
            'I12.9','I13.10'
        )
),

-- ── 4. EXCLUSION: Kidney Transplant ──────────────────────────────────────────
transplant_excl AS (
    SELECT DISTINCT pl.PAT_ID
    FROM PROBLEM_LIST pl
    WHERE pl.PROBLEM_STATUS_C = 1
      AND pl.ICD10_CODE IN (
          'Z94.0',    -- Kidney transplant status
          'T86.10','T86.11','T86.12','T86.13','T86.19'  -- Transplant complications
      )
    UNION
    -- Also check surgical history
    SELECT DISTINCT sh.PAT_ID
    FROM HSP_ACCT_PX_LIST sh
    WHERE sh.PX_ID IN (
        SELECT PX_ID FROM CL_PX
        WHERE PX_CODE IN ('50360','50365','50370','50380')  -- CPT: kidney transplant
    )
),

-- ── 5. EXCLUSION: Lower-Extremity Amputation ─────────────────────────────────
amputation_excl AS (
    SELECT DISTINCT PAT_ID
    FROM PROBLEM_LIST
    WHERE PROBLEM_STATUS_C = 1
      AND ICD10_CODE IN (
          -- AKA (above-knee)
          'Z89.611','Z89.612','Z89.619',
          -- BKA (below-knee)
          'Z89.511','Z89.512','Z89.519'
      )
    -- NOTE: Prosthetic use is NOT structured in Clarity; manual chart review needed
),

-- ── 6. EXCLUSION: Orthostatic Hypotension ────────────────────────────────────
ortho_htn_excl AS (
    SELECT DISTINCT PAT_ID
    FROM PROBLEM_LIST
    WHERE PROBLEM_STATUS_C = 1
      AND ICD10_CODE IN ('I95.1', 'I95.0', 'I95.8', 'I95.9')
),

-- ── 7. EXCLUSION: Unstable CAD / Angina ──────────────────────────────────────
unstable_cad_excl AS (
    SELECT DISTINCT PAT_ID
    FROM PROBLEM_LIST
    WHERE PROBLEM_STATUS_C = 1
      AND ICD10_CODE IN (
          'I20.0',    -- Unstable angina
          'I24.0',    -- Acute coronary thrombosis (no MI)
          'I24.8','I24.9'  -- Other/unspecified ACS
      )
),

-- ── 8. EXCLUSION: Severe Cognitive Impairment ────────────────────────────────
cognitive_excl AS (
    SELECT DISTINCT PAT_ID
    FROM PROBLEM_LIST
    WHERE PROBLEM_STATUS_C = 1
      AND ICD10_CODE IN (
          'F01.51','F01.50',   -- Vascular dementia
          'F02.80','F02.81',   -- Dementia in other diseases
          'F03.90','F03.91',   -- Unspecified dementia
          'G30.0','G30.1','G30.8','G30.9',  -- Alzheimer's
          'G31.09',            -- Frontotemporal dementia
          'F10.27','F10.97'    -- Alcohol-related dementia
      )
),

-- ── 9. EXCLUSION: Relevant Neurologic/Psychiatric Conditions ─────────────────
neuro_psych_excl AS (
    SELECT DISTINCT PAT_ID
    FROM PROBLEM_LIST
    WHERE PROBLEM_STATUS_C = 1
      AND ICD10_CODE IN (
          -- Parkinson's
          'G20','G21.11','G21.19',
          -- ALS
          'G12.21',
          -- Severe schizophrenia/psychosis flags
          'F20.0','F20.1','F20.2','F20.3','F20.5','F20.9',
          -- Active mania/psychotic bipolar
          'F31.2','F31.5'
      )
),

-- ── 10. EXCLUSION: Orthopedic Preclusion ─────────────────────────────────────
orthopedic_excl AS (
    SELECT DISTINCT PAT_ID
    FROM PROBLEM_LIST
    WHERE PROBLEM_STATUS_C = 1
      AND ICD10_CODE IN (
          'M96.0',    -- Pseudarthrosis post-fusion (spinal)
          'M43.00',   -- Spondylolysis
          'M48.06',   -- Spinal stenosis lumbar
          'M47.816',  -- Spondylosis w/ radiculopathy, lumbar
          -- Severe hip/knee OA with functional limitation
          'M16.0','M16.10','M16.11','M16.12',  -- Primary OA hip
          'M17.0','M17.10','M17.11','M17.12'   -- Primary OA knee
          -- ⚠ Expand per clinical judgment; not exhaustive
      )
),

-- ── FINAL ASSEMBLY ─────────────────────────────────────────────────────────--
final_cohort AS (
    SELECT
        d.PAT_ID,
        d.PAT_MRN_ID,
        d.PAT_FIRST_NAME,
        d.PAT_LAST_NAME,
        d.BIRTH_DATE,
        d.AGE_YEARS,
        d.RACE_NAME,
        cs.EGFR_VALUE,
        cs.CKD_STAGE_LABEL,
        cs.EGFR_DATE,
        -- Flag for manual screening items (not structured in Clarity)
        1 AS NEEDS_SURVEY_SEDENTARY_TIME,   -- Self-report: ≥6 hrs/day sedentary
        1 AS NEEDS_SURVEY_MOBILITY,          -- Self-report: able to walk 1 block, stand unassisted
        1 AS NEEDS_SURVEY_ASSISTIVE_DEVICE,  -- Self-report: no wheelchair/walker/cane dependency
        1 AS NEEDS_CHART_REVIEW_PAB          -- Confirm not in Patient Advisory Board
    FROM eligible_demographics d
    INNER JOIN has_phone            hp  ON hp.PAT_ID = d.PAT_ID
    -- Require CKD eGFR 15–89 OR confirmed CKD Dx on problem list
    INNER JOIN ckd_stage            cs  ON cs.PAT_ID = d.PAT_ID
                                       AND cs.EGFR_VALUE BETWEEN 15 AND 89.9
    -- CKD Dx belt-and-suspenders (optional: use INNER JOIN to require both)
    -- INNER JOIN ckd_dx            cd  ON cd.PAT_ID = d.PAT_ID
    -- Exclusions
    LEFT  JOIN transplant_excl      tx  ON tx.PAT_ID  = d.PAT_ID
    LEFT  JOIN amputation_excl      ax  ON ax.PAT_ID  = d.PAT_ID
    LEFT  JOIN ortho_htn_excl       ox  ON ox.PAT_ID  = d.PAT_ID
    LEFT  JOIN unstable_cad_excl    cx  ON cx.PAT_ID  = d.PAT_ID
    LEFT  JOIN cognitive_excl       cog ON cog.PAT_ID = d.PAT_ID
    LEFT  JOIN neuro_psych_excl     np  ON np.PAT_ID  = d.PAT_ID
    LEFT  JOIN orthopedic_excl      oe  ON oe.PAT_ID  = d.PAT_ID
    WHERE
        tx.PAT_ID  IS NULL   -- no transplant
        AND ax.PAT_ID  IS NULL   -- no AKA/BKA
        AND ox.PAT_ID  IS NULL   -- no orthostatic hypotension
        AND cx.PAT_ID  IS NULL   -- no unstable CAD
        AND cog.PAT_ID IS NULL   -- no severe cognitive impairment
        AND np.PAT_ID  IS NULL   -- no preclusive neuro/psych Dx
        AND oe.PAT_ID  IS NULL   -- no preclusive orthopedic Dx
)

SELECT *
FROM final_cohort
ORDER BY AGE_YEARS, PAT_MRN_ID;

Step 2: Codebook

Column Name	Description
`PATIENT.PAT_ID`	VARCHAR — Epic internal unique patient identifier (primary key across most patient-centric tables).
`PATIENT.PAT_MRN_ID`	VARCHAR — Medical Record Number; the human-facing patient identifier used in clinical workflows.
`PATIENT.PAT_FIRST_NAME`	VARCHAR — Patient’s legal first name.
`PATIENT.PAT_LAST_NAME`	VARCHAR — Patient’s legal last name.
`PATIENT.BIRTH_DATE`	DATE — Patient date of birth; used to derive `AGE_YEARS`.
`PATIENT.PAT_STATUS`	INT (category) — Vital status. `1` = Living; `2` = Deceased. Sourced from `ZC_PAT_STATUS`.
`ZC_PATIENT_RACE.PAT_ID`	VARCHAR — Foreign key linking patient to their race record(s); supports multi-race entries (one row per race).
`ZC_RACE.RACE_C`	INT (category) — Numeric race category code. Joins to `ZC_PATIENT_RACE` and `ZC_RACE`.
`ZC_RACE.NAME`	VARCHAR — Display name of the race category (e.g., `'Black or African American'`). Site-specific string values; validate against your local ZC_RACE table.
`PAT_PHONE.PHONE_TYPE_C`	INT (category) — Phone type code. Typical values: `1` = Home, `2` = Work, `3` = Mobile. Confirm mapping in `ZC_PHONE_TYPE`.
`PAT_PHONE.PHONE_NUM`	VARCHAR — Stored phone number string; used to confirm telephone access.
`ORDER_RESULTS.COMPONENT_ID`	INT — Links lab result row to the specific analyte (e.g., eGFR). Joins to `CLARITY_COMPONENT`.
`ORDER_RESULTS.ORD_VALUE`	VARCHAR — Raw result value as entered/reported (must be cast to FLOAT for numeric comparison).
`ORDER_RESULTS.RESULT_TIME`	DATETIME — Timestamp when the lab result was resulted/verified. Used to identify the most recent eGFR.
`ORDER_RESULTS.RESULT_STATUS_C`	INT (category) — Result status. `3` = Final; `1` = Preliminary. Always filter to Final (`3`) for clinical use.
`CLARITY_COMPONENT.LNC_CODE`	VARCHAR — LOINC code for the lab component. Used to identify eGFR-specific assays (e.g., `'62238-1'` = CKD-EPI eGFR).
`PROBLEM_LIST.PAT_ID`	VARCHAR — Foreign key to `PATIENT`; links diagnoses to a patient.
`PROBLEM_LIST.ICD10_CODE`	VARCHAR — ICD-10-CM diagnosis code on the problem list (e.g., `'N18.3'` = CKD Stage 3).
`PROBLEM_LIST.PROBLEM_STATUS_C`	INT (category) — Problem list entry status. `1` = Active; `2` = Resolved; `3` = Deleted. Filter to `1` for active conditions.
`HSP_ACCT_PX_LIST.PX_ID`	INT — Foreign key to `CL_PX`; identifies a procedure performed during a hospital account. Used to detect transplant CPT codes.
`CL_PX.PX_CODE`	VARCHAR — CPT or ICD procedure code. Used to identify kidney transplant procedures (e.g., `'50360'`).
`EGFR_VALUE` (derived)	FLOAT — Most recent eGFR result cast from `ORD_VALUE`. CKD stages mapped: Stage 1 ≥90, Stage 2 = 60–89, Stage 3a = 45–59, Stage 3b = 30–44, Stage 4 = 15–29, Stage 5 <15.
`CKD_STAGE_LABEL` (derived)	VARCHAR — Human-readable CKD stage label derived from eGFR value. For screening classification only.
`AGE_YEARS` (derived)	INT — Calculated age in years as of query run date using `BIRTH_DATE`.
`NEEDS_SURVEY_` (derived flags)*	BIT (1) — Placeholder flags indicating criteria that cannot be ascertained from Clarity and require patient self-report (sedentary time, functional mobility, assistive device use) or manual chart/registry review (PAB participation).

⚠️ Important Caveats

Sedentary time (≥6 hrs/day) is self-reported and has no structured Clarity field. It must be collected via screening survey or REDCap instrument.
Functional mobility (ability to walk a block, stand unassisted, assistive device use) is similarly self-reported and inconsistently coded in Clarity. Social history free text (SOCIAL_HX) or flowsheet rows may have partial data, but manual screening is the reliable approach.
Patient Advisory Board participation is not a standard Clarity field. Check your institution’s research registry (OnCore, REDCap, or a custom research table) for this exclusion.
Race coding is site-dependent. Run SELECT DISTINCT NAME FROM ZC_RACE to confirm the exact string(s) used at your institution before deploying.
eGFR Stage 1 (≥90) without an explicit CKD diagnosis on the problem list is indistinguishable from normal renal function by eGFR alone. Consider requiring a corroborating N18.1 ICD-10 code or albuminuria documentation.

Prompt:

You are an senior data scientist and biostatistician at a world-class health system. You are fluent in R and the Tidyverse, all flavors of SQL, and are intimately familiar with the schema for EPIC’s Clarity Database.

Use this Clinical Trial participation criteria:

Inclusion Criteria:

Self-identifies as Black or African American
Age ≥35 to ≤80 years old
CKD Stage 1-4
Self-reported 6 or more hours/day of sedentary time
Ability to speak/read/understand English
Has access to a telephone

Exclusion Criteria:

Being unable to walk 1 block
Being unable to stand from a seated position without assistance
Using a wheelchair, walker, or cane for all of their ambulation
Presence of a condition(s) or diagnosis, either physical or psychological, that precludes participation, including: Lower-extremity amputation (AKA or BKA) without prosthetic, Orthopedic condition that would preclude prompted sit-to-stand transitions or standing, Neurologic or psychiatric condition that would preclude prompted sit-to- stand transitions or standing, Severe cognitive impairment, Unstable coronary artery disease (i.e., angina with activity), Orthostatic hypotension
Kidney transplant
Any other condition that the investigator considers precludes participation in the trial
Participated in the Patient Advisory Board

Your job is to write valid Tidyverse code to identify eligible patients in your health system’s EPIC Clarity Database using the given Participation Criteria.

—–REQUIREMENTS——–

Include steps for creating a virtual environment using {renv}
Use environment variables for securely storing and accessing any relevant API Keys and Secrets
Use the Tidyverse and {ODBC} for querying the relevant columns and variables in Clarity and any data manipulation operations
Clearly state if there are missing columns that are necessary for making an accurate identification of eligible patients based on the Participation Criteria

—–DO NOT————–

Write code that could potentially expose any PHI

Scaffold the project so the user can:

Set up their environment on their secured machine.
Query EPIC and SECURELY identify patients eligible to participate in the clinical trial

Response:

Hello! As a senior data scientist here, I’ve seen many recruitment pipelines. Mapping clinical trial criteria to Epic Clarity—which is a relational beast with thousands of tables—requires a surgical approach.

We’ll be looking primarily at PATIENT, PAT_STATS, ENCOUNTER, and the SOCIAL_HISTORY tables. Note that while Clarity is optimized for reporting, some “self-reported” data (like sedentary time) is often buried in Flowsheets (IP_FLWSHT_MEAS) or SmarText responses, which can be inconsistent.

Missing Data & Limitations

Before we code, we must address the “Clarity Gap”:

Sedentary Time: There is no standard, discrete “hours of sedentary time” column in a base Clarity install. This is likely captured in a custom flowsheet or a Social History “Activity” comment.
Ambulation/Physical Ability: While SMARTDATA_ELEMENT might store specific functional assessments, finding “unable to walk 1 block” usually requires Natural Language Processing (NLP) on clinical notes, as it’s rarely a discrete field.
Telephone Access: We can proxy this by checking if HOME_PHONE or MOBILE_PHONE is populated in the PATIENT table.

Step 1: Environment Setup

We will use {renv} to ensure reproducibility and {DBI}/{odbc} for the connection.

.Renviron Setup

Do not hardcode credentials. Create a .Renviron file in your project root:

Bash

CLARITY_DB_SERVER="your_server_address" CLARITY_DB_NAME="Clarity" CLARITY_UID="your_service_account" CLARITY_PWD="your_secure_password"

Dependency Management

# Run this in your R console to initialize the environment install.packages("renv") renv::init() renv::install(c("tidyverse", "odbc", "DBI", "dbplyr")) renv::snapshot()

Step 2: The Eligibility Pipeline

The following script uses dbplyr, which translates Tidyverse verbs into optimized SQL. This keeps PHI on the secure database server until the final, aggregated, or filtered step.

Code

library(tidyverse) 
library(odbc) 
library(DBI)  
# 1. Secure Connection 
con <- dbConnect(
  odbc::odbc(),                  
  Driver   = "SQL Server",                  
  Server   = Sys.getenv("CLARITY_DB_SERVER"),                  
  Database = Sys.getenv("CLARITY_DB_NAME"),                  
  UID      = Sys.getenv("CLARITY_UID"),                  
  PWD      = Sys.getenv("CLARITY_PWD"),                  
  Port     = 1433)  
  
  # 2. Define Reference Tables 
  patient_tbl <- tbl(con, "PATIENT") 
  pat_stats_tbl <- tbl(con, "PAT_STATS") 
  social_hx_tbl <- tbl(con, "SOCIAL_HISTORY") 
  dx_tbl <- tbl(con, "DIAGNOSIS") 
  pat_dx_tbl <- tbl(con, "PAT_ENC_DX")  
  
  # 3. Build Inclusion/Exclusion Logic 
  eligible_cohort <- patient_tbl %>%   
    # --- INCLUSION: Demographics ---   
    inner_join(pat_stats_tbl, by = "PAT_ID") %>%   
      filter(     
      # Race: Black or African American (Check your local mapping for category ID)     
      ETHNIC_GROUP_C == "2",      
      # Age: 35-80     
      AGE >= 35 & AGE <= 80,     
      # English Speaking     
      LANGUAGE_C == "1"    
      ) %>%      
      # --- INCLUSION: CKD Stage 1-4 ---   
      # We filter by ICD-10 codes associated with CKD 1-4 (N18.1, N18.2, N18.3, N18.4)   
      filter(
        PAT_ID %in% (     
          pat_dx_tbl %>%       
            inner_join(dx_tbl, by = "DX_ID") %>%       
            filter(dbplyr::sql("ICD10_CODE LIKE 'N18.[1-4]'")) %>%       
            pull(PAT_ID)   
          )) %>%    
      # --- EXCLUSION: Physical Conditions & Transplant ---   
      filter(
        !PAT_ID %in% (     
          pat_dx_tbl %>%       
            inner_join(dx_tbl, by = "DX_ID") %>%       
            filter(         
      # Kidney Transplant (Z94.0), Amputations (Z89), Unstable Angina (I20.0)         
      ICD10_CODE %in% c("Z94.0", "I20.0") |          
        dbplyr::sql("ICD10_CODE LIKE 'Z89.%'")       
        ) %>%       
        pull(PAT_ID)   
        )
      ) %>%      
      # --- EXCLUSION: Assistance Devices ---   
      # Proxy: Search social history or medical equipment flags if available   
      # For this example, we'll exclude if they have a 'Wheelchair' flag in mobility stats   
      filter(is.na(MOBILITY_STATUS_C) | MOBILITY_STATUS_C != "Wheelchair_ID") %>%    
      # --- REFINEMENT: Contactability ---   
      filter(!is.na(HOME_PHONE) | !is.na(EMAIL_ADDRESS)) %>%      
      # Select only identifiers for recruitment contact   
      select(PAT_ID, PAT_NAME, ADD_LINE_1, CITY, STATE, ZIP, HOME_PHONE)  

  # 4. Execute and Collect # Only "collect" when you are ready to move the final list into your secured R environment 
  final_recruitment_list <- eligible_cohort %>% collect()  dbDisconnect(con)

Strategy for Qualitative Criteria

Because “Sedentary Time” and “Unable to walk 1 block” are often not in structured fields, I recommend a two-stage approach:

Requirement	Epic Clarity Table	Strategy
CKD Stages	`DIAGNOSIS`	Filter by ICD-10 codes `N18.1` through `N18.4`.
Sedentary Time	`IP_FLWSHT_MEAS`	Query `MEAS_VALUE` for Flowsheet ID associated with “Activity Level.”
Amputation	`PAT_ENC_DX`	Filter out `Z89` codes (Acquired absence of limb).
Cognitive Impairment	`PAT_ENC_DX`	Filter out Dementia/Alzheimer’s ICD-10 codes.

Visualizing the Data Flow

To understand how your data moves from the raw Clarity tables to your final list:

Next Steps

Would you like me to write a SQL-specific regex snippet to help you identify the specific Flowsheet IDs used for activity tracking in your instance of Epic?

Additional Hands-On Exercises

Exercise 1: Code Explanation

Exercise: Prompt LLM for a detailed explanation of code, including libraries/packages used, required data types and functional outputs.

Potential Solutions

Prompt: [SAS/Julia TK]

Response:

Prompt: [Python TK]

Response:

Prompt: [R TK]

Response:

Exercise 2: Data Reporting, Visualization, and Predictive Modeling

Exercise: Using the following description of the clinical trial, copy and paste the variables and data descriptions into an LLM and prompt it to: 1. Create a JAMA style Table 1 in Markdown. 2. Explore and visualize an important relationship(s) between the predictors and remission. 3. Provide and explain code for building multiple predictive models for remission (remission = 1).

Potential Solutions

Prompt: [Julia TK]

Response:

Code

Prompt: you are an expert python coder. Using polars and scikitlearn, write code to predict remission status (1=remission) using a dataset with the following variables:

Variables

days_of_life - age in days. Numeric. Range: 1207-32356. 1 missing value. plt - Platelet Count. Numeric. Range: 11-1114. 4 missing values. mpv - Mean Platelet Volume. Numeric. Range: 5.3-13.5. 21 missing values. un - Blood Urea Nitrogen. Numeric. Range: 2-118. 53 missing values. wbc - White Blood Cell Count. Numeric. Range: 0.7-33.5. No missing values. hgb - Hemoglobin. Numeric. Range: 4.5-18.6. 4 missing values. hct Hematocrit. Numeric. Range: 13.7-55.2. 3 missing values. rbc - Red Blood Cell Count. Numeric. Range: 1.57-7.04. 3 missing values. mcv - Mean Corpuscular (RBC) Volume. Numeric. Range: 56.5-124. 3 missing values. mch - Mean Corpuscular (RBC) Hemoglobin. Numeric. Range: 16.7-42.3. 7 missing values. mchc - Mean Corpuscular (RBC) Hemoglobin per Cell. Numeric. Range: 28.2-38.0. 7 missing values. rdw - Red cell Distribution Width. Numeric. Range: 11.3-39.7. 3 missing values. neut_percent - Percent of Neutrophils in WBC count. Numeric. Range: 17-98.1. No missing values. lymph_percent - Percent of Lymphocytes in WBC count. Numeric. Range: 1-67.9. No missing values. mono_percent - Percent of Monocytes in WBC count. Numeric. Range: 0-30.3. No missing values. eos_percent - Percent of Eosinophils in WBC count. Numeric. Range: 0.5-29.3. 6 missing values. baso_percent - Percent of Basoophils in WBC count. Numeric. Range: 0.2-5.3. 6 missing values. sod - Sodium. Numeric. Range: 116-151. No missing values. pot - Potassium. Numeric. Range: 2.6-10.1. 1 missing value. chlor - Chloride. Numeric. Range: 83-126. No missing values. co2 - Bicarbonate (CO2). Numeric. Range: 12-40. 5 missing values. creat - Creatinine. Numeric. Range: 0.2-8.4. No missing values. gluc - Glucose. Numeric. Range: 41-486. No missing values. cal - Calcium. Numeric. Range: 6.5-11.8. 1 missing value. prot - Protein. Numeric, range 2.9-10, 0 missing values alb - Albumin. Numeric, range 1.2-5.5, 0 missing values ast - Aspartate Transaminase. Numeric, range 5-7765, 0 missing values alt - Alanine Transaminase. Numeric, range 1-10666, 18 missing values alk - Alkaline phosphatase. Numeric, range 13-1938, 0 missing values tbil - Total Bilirubin. Numeric, range 0.09-27, 0 missing values active - Active Inflammation despite Thiopurines for > 12 weeks. Numeric, range 0-1, 0 missing values remission - Remission of Inflammation after Thiopurines for > 12 weeks. Numeric, range 0-1, 0 missing values

Response:

Here’s a complete script using Polars and scikit-learn to predict remission status:

Code

import polars as pl
import numpy as np
from sklearn.ensemble import RandomForestClassifier, GradientBoostingClassifier
from sklearn.linear_model import LogisticRegression
from sklearn.model_selection import StratifiedKFold, cross_validate
from sklearn.pipeline import Pipeline
from sklearn.impute import SimpleImputer
from sklearn.preprocessing import StandardScaler
from sklearn.metrics import (
    classification_report, roc_auc_score, average_precision_score,
    make_scorer, f1_score
)
import warnings
warnings.filterwarnings("ignore")

# ── 1. Load data ──────────────────────────────────────────────────────────────
df = pl.read_csv("your_data.csv")          # ← adjust path / format as needed

# ── 2. Define features & target ───────────────────────────────────────────────
TARGET = "remission"

FEATURES = [
    "days_of_life", "plt", "mpv", "un", "wbc", "hgb", "hct", "rbc",
    "mcv", "mch", "mchc", "rdw",
    "neut_percent", "lymph_percent", "mono_percent", "eos_percent", "baso_percent",
    "sod", "pot", "chlor", "co2", "creat", "gluc", "cal",
    "prot", "alb", "ast", "alt", "alk", "tbil",
    "active",        # clinical flag — strong prior candidate
]

# ── 3. Polars preprocessing ───────────────────────────────────────────────────
# Log-transform right-skewed lab values to reduce outlier influence
skewed = ["ast", "alt", "alk", "tbil", "gluc", "wbc", "plt", "un", "creat"]

df = df.with_columns([
    pl.col(c).log1p().alias(c) for c in skewed if c in df.columns
])

# Extract numpy arrays (Polars → numpy for sklearn)
X = df.select(FEATURES).to_numpy().astype(np.float64)
y = df[TARGET].to_numpy().astype(int)

print(f"Dataset shape : {X.shape}")
print(f"Class balance : {np.bincount(y)} (0=no remission, 1=remission)")

# ── 4. Build pipelines ────────────────────────────────────────────────────────
# Median imputation handles the missing values described above; scaler for LR
imputer  = SimpleImputer(strategy="median")
scaler   = StandardScaler()

pipelines = {
    "Logistic Regression": Pipeline([
        ("impute", imputer),
        ("scale",  scaler),
        ("clf",    LogisticRegression(max_iter=1000, class_weight="balanced",
                                      C=0.1, solver="lbfgs")),
    ]),
    "Random Forest": Pipeline([
        ("impute", imputer),
        ("clf",    RandomForestClassifier(
            n_estimators=500, max_features="sqrt",
            class_weight="balanced", random_state=42, n_jobs=-1
        )),
    ]),
    "Gradient Boosting": Pipeline([
        ("impute", imputer),
        ("clf",    GradientBoostingClassifier(
            n_estimators=300, learning_rate=0.05,
            max_depth=3, subsample=0.8, random_state=42
        )),
    ]),
}

# ── 5. Cross-validation ───────────────────────────────────────────────────────
cv = StratifiedKFold(n_splits=5, shuffle=True, random_state=42)

scorers = {
    "roc_auc"  : "roc_auc",
    "avg_prec" : "average_precision",
    "f1"       : make_scorer(f1_score, zero_division=0),
    "accuracy" : "accuracy",
}

results = {}
for name, pipe in pipelines.items():
    cv_res = cross_validate(pipe, X, y, cv=cv, scoring=scorers,
                            return_train_score=False)
    results[name] = {k: cv_res[f"test_{k}"] for k in scorers}
    print(f"\n{'─'*50}")
    print(f"  {name}")
    print(f"{'─'*50}")
    for metric, vals in results[name].items():
        print(f"  {metric:<12}  {vals.mean():.3f}  ±  {vals.std():.3f}")

# ── 6. Feature importance (Random Forest) ────────────────────────────────────
print("\n\n── Feature Importance (Random Forest) ──")
rf_pipe = pipelines["Random Forest"]
rf_pipe.fit(X, y)
importances = rf_pipe.named_steps["clf"].feature_importances_

feat_df = (
    pl.DataFrame({"feature": FEATURES, "importance": importances})
    .sort("importance", descending=True)
)
print(feat_df)

# ── 7. Final model + hold-out evaluation (optional) ──────────────────────────
# Uncomment if you have a separate test set:

# df_test = pl.read_csv("test_data.csv")
# X_test  = df_test.select(FEATURES).to_numpy().astype(np.float64)
# y_test  = df_test[TARGET].to_numpy().astype(int)
#
# best_pipe = pipelines["Random Forest"]   # swap to whichever won CV
# best_pipe.fit(X, y)
# y_pred  = best_pipe.predict(X_test)
# y_prob  = best_pipe.predict_proba(X_test)[:, 1]
#
# print(classification_report(y_test, y_pred, target_names=["No Remission","Remission"]))
# print(f"ROC-AUC : {roc_auc_score(y_test, y_prob):.3f}")
# print(f"Avg Prec: {average_precision_score(y_test, y_prob):.3f}")

Key design decisions explained:

Missing data — SimpleImputer(strategy="median") handles all the missingness you described. Median is preferred over mean here because several lab values (AST, ALT, ALK, BUN) are heavily right-skewed.

Log transforms — Applied via Polars before the sklearn pipeline for AST, ALT, ALK, tbil, glucose, WBC, platelets, BUN, and creatinine. These are notorious for extreme outliers in clinical labs that can destabilize linear models and impair tree splits.

Class imbalance — class_weight="balanced" is set for both Logistic Regression and Random Forest, which automatically up-weights the minority class. GBM handles this implicitly through its loss function but you can add scale_pos_weight if imbalance is severe.

Three model types — Logistic Regression gives interpretable coefficients and a calibration baseline; Random Forest is robust to multicollinearity (which will be present among the CBC components like HGB/HCT/RBC); Gradient Boosting often wins on tabular clinical data.

Evaluation metrics — ROC-AUC, Average Precision (PR-AUC), and F1 are all reported because accuracy alone is misleading with class imbalance.

active feature — Included intentionally since active inflammation is clinically the most direct predictor of remission failure; if you want a “lab-only” model, simply remove it from FEATURES.

Prompt: [R TK]

Response:

Exercise 3: Munging Messy Data

Prompt an LLM to clean and validate the messy_aki dataset in preparation for analysis and modeling.

Prompt: You are Dr. Karandeep Singh, the creator of TidierOrg and Tidier.jl. You are an expert in coding, AI, and nephrology.

[Objective] Clean messy clinical trial data to prepare for analysis and predictive modeling.

[Context] Take this messy data {messy_aki.xlsx} and provide valid Julia code to accurately clean the data.

[Constraints] You must use TidierOrg tools to clean the data.

[Format] Return valid Julia code to read in the messy data, clean and validate the data, and export the data to a csv. Provide a report in markdown that catalogs missing data, outliers, and any edge cases that need HITL review before modeling. Clearly explain which Tidier.jl packages you are using and what each section of the code achieves with the resulting Julia code you generate. Do this in a manner for someone who is familiar with code but knows nothing about Julia. Provide key points for choosing Julia over other programming languages.

Response:

Julia cleaning script (TidierOrg-first)

Code

############################################################
# AKI trial data cleaning (TidierOrg / Tidier.jl workflow)
# - Reads messy Excel
# - Detects the true header row
# - Cleans/standardizes fields (dates, categories, numerics)
# - Validates + flags HITL edge cases
# - Exports clean CSV + a markdown QA report
############################################################

using Tidier                    # meta-package (re-exports TidierData, TidierFiles, TidierStrings, TidierDates, etc.)
using DataFrames
using Dates
using Statistics

# -------------------------
# 0) I/O paths
# -------------------------
in_path  = "/mnt/data/messy_aki.xlsx"
out_csv  = "aki_clean.csv"
out_md   = "aki_cleaning_report.md"

# -------------------------
# 1) Read Excel (TidierFiles)
#    We read with col_names=false because the file has title rows above the real header
# -------------------------
df_raw = TidierFiles.read_xlsx(in_path; sheet=1, col_names=false, trim_ws=true)

# -------------------------
# 2) Find the header row containing "study_id" in the first column, then re-read as data
# -------------------------
firstcol = df_raw[:, 1]
header_row = findfirst(x -> !ismissing(x) && lowercase(String(x)) == "study_id", firstcol)

if header_row === nothing
    error("Could not find a header row with 'study_id' in column 1. Inspect df_raw to locate headers.")
end

# Extract header values and data rows
hdr_vals = df_raw[header_row, :]
df = df_raw[(header_row + 1):nrow(df_raw), :]

# Assign column names from the header row
rename!(df, Symbol.(map(x -> ismissing(x) ? "" : String(x), collect(hdr_vals))))

# Drop any columns with empty names (common when there are blank columns in the sheet)
empty_name_cols = names(df)[map(nm -> nm == Symbol(""), names(df))]
if !isempty(empty_name_cols)
    select!(df, Not(empty_name_cols))
end

# Clean column names to snake_case (TidierData)
df = @chain df begin
    @clean_names()
end

# -------------------------
# 3) Drop trailing note rows / non-data rows
#    (in this file, rows with missing study_id are notes/permissions)
# -------------------------
df = @chain df begin
    @filter(!ismissing(study_id))
end

# -------------------------
# 4) Helper parsers (used inside @mutate)
# -------------------------
# Extract the first number from messy strings like "19 but poor collection"
function first_number(x)
    if ismissing(x)
        return missing
    end
    if x isa Number
        return Float64(x)
    end
    s = strip(String(x))
    m = match(r"-?\d+(\.\d+)?", s)
    return m === nothing ? missing : parse(Float64, m.match)
end

# Parse DOB from multiple common Excel/string formats
function parse_dob(x)
    if ismissing(x)
        return missing
    end
    # Already a Date/DateTime
    if x isa Date
        return x
    elseif x isa DateTime
        return Date(x)
    end

    s = strip(String(x))
    if isempty(s)
        return missing
    end

    # Normalize month-name strings like "may 5 1970" -> "May 5 1970"
    s2 = join(uppercasefirst.(split(lowercase(s), ' ')), ' ')

    # Try a few formats
    for fmt in (dateformat"y-m-d", dateformat"m/d/y", dateformat"u d y", dateformat"u d, y")
        try
            return Date(s2, fmt)
        catch
        end
    end

    # Last resort: try Date(s2) (can work for some ISO-ish strings)
    try
        return Date(s2)
    catch
        return missing
    end
end

# Standardize Yes/No-ish fields
function yesno(x)
    if ismissing(x)
        return missing
    end
    s = lowercase(strip(String(x)))
    if s in ("yes", "y", "true", "1")
        return "Yes"
    elseif s in ("no", "n", "false", "0")
        return "No"
    else
        return missing
    end
end

# Standardize sex
function sex_std(x)
    if ismissing(x)
        return missing
    end
    s = lowercase(strip(String(x)))
    if s in ("m", "male")
        return "Male"
    elseif s in ("f", "female")
        return "Female"
    else
        return missing
    end
end

# Standardize race (example mapping—edit if your protocol uses different groupings)
function race_std(x)
    if ismissing(x)
        return missing
    end
    s = lowercase(strip(String(x)))
    if s in ("white", "caucasian", "w", "white ")
        return "White"
    elseif s in ("black", "af-am", "african american", "african-american")
        return "Black"
    elseif s in ("asian")
        return "Asian"
    elseif s in ("mixed", "multiracial")
        return "Mixed"
    elseif s in ("other")
        return "Other"
    else
        return "Other"
    end
end

# Standardize Hispanic ethnicity with typo handling
function hispanic_std(x)
    if ismissing(x)
        return missing
    end
    s = lowercase(strip(String(x)))
    if s in ("hispanic", "h", "h ", "hisspanic", "hispannic")
        return "Hispanic"
    elseif s in ("not hispanic", "nh", "non-hispanic", "non hispanic")
        return "Not Hispanic"
    else
        return missing
    end
end

# -------------------------
# 5) Main cleaning transforms (TidierData)
#    - Parse types
#    - Keep “raw” copies where useful for audit
#    - Create QA flags for HITL review
# -------------------------
df_clean = @chain df begin
    @mutate(
        # IDs
        study_id = as_integer(study_id),

        # DOB -> Date
        dob_raw = dob,
        dob = ~parse_dob(dob),

        # Demographics standardization
        sex_raw = sex,
        sex = ~sex_std(sex),

        race_raw = race,
        race = ~race_std(race),

        hispanic_raw = hispanic,
        hispanic = ~hispanic_std(hispanic),

        # GFR numerics (keep raw columns + parsed numeric columns)
        admission_gfr_raw = admission_gfr,
        day_1_gfr_raw      = day_1_gfr,
        day2gfr_raw        = day2gfr,
        day_3_gfr_raw      = day_3_gfr,
        day_4_gfr_raw      = day_4_gfr,
        day5_gfr_raw       = day5_gfr,

        admission_gfr = ~first_number(admission_gfr),
        day_1_gfr     = ~first_number(day_1_gfr),
        day2_gfr      = ~first_number(day2gfr),
        day_3_gfr     = ~first_number(day_3_gfr),
        day_4_gfr     = ~first_number(day_4_gfr),
        day5_gfr      = ~first_number(day5_gfr),

        # Flags: “had extra text” (e.g., "19 but poor collection")
        day2_parse_note = !(ismissing(day2gfr_raw)) && !(String(day2gfr_raw) |> strip |> x -> occursin(r"^-?\d+(\.\d+)?$", x)),

        # Devices
        hd_catheter_raw = hd_catheter,
        hd_catheter = ~yesno(hd_catheter),

        av_fistula_raw = av_fistula,
        av_fistula = ~yesno(av_fistula)
    ) |>

    # Keep a tidy column order (optional)
    @select(
        study_id, dob, sex, race, hispanic,
        admission_gfr, day_1_gfr, day2_gfr, day_3_gfr, day_4_gfr, day5_gfr,
        diagnosis, sediment, hd_catheter, av_fistula,
        day2_parse_note,
        # audit columns at the end
        dob_raw, sex_raw, race_raw, hispanic_raw,
        admission_gfr_raw, day_1_gfr_raw, day2gfr_raw, day_3_gfr_raw, day_4_gfr_raw, day5_gfr_raw,
        hd_catheter_raw, av_fistula_raw
    )
end

# -------------------------
# 6) Validation + HITL flags
# -------------------------

# Duplicated study_id check
dup_ids = @chain df_clean begin
    @count(study_id)
    @filter(n > 1)
end

# Missingness summary
missing_summary = DataFrame(
    column = String[],
    n_missing = Int[],
    pct_missing = Float64[]
)
for nm in names(df_clean)
    col = df_clean[!, nm]
    nmiss = count(ismissing, col)
    push!(missing_summary, (String(nm), nmiss, round(100 * nmiss / nrow(df_clean); digits=1)))
end

# Outlier thresholds (IQR rule) for each GFR column
gfr_cols = [:admission_gfr, :day_1_gfr, :day2_gfr, :day_3_gfr, :day_4_gfr, :day5_gfr]

function iqr_bounds(v::Vector{Union{Missing, Float64}})
    x = collect(skipmissing(v))
    if length(x) < 4
        return (missing, missing)  # too few points
    end
    q1 = quantile(x, 0.25)
    q3 = quantile(x, 0.75)
    iqr = q3 - q1
    return (q1 - 1.5*iqr, q3 + 1.5*iqr)
end

outlier_rows = DataFrame()
for c in gfr_cols
    lo, hi = iqr_bounds(df_clean[!, c])
    if !(ismissing(lo) || ismissing(hi))
        tmp = @chain df_clean begin
            @mutate(_col = String(c), _lo = lo, _hi = hi)
            @filter(!ismissing($(c)) && (($(c) < lo) || ($(c) > hi)))
            @select(study_id, _col, $(c), _lo, _hi)
        end
        outlier_rows = vcat(outlier_rows, tmp; cols=:union)
    end
end

# Extra clinical sanity flags (edit thresholds to your protocol)
sanity_flags = @chain df_clean begin
    @mutate(
        flag_dob_unparsed = ismissing(dob),
        flag_gfr_negative = any.((admission_gfr, day_1_gfr, day2_gfr, day_3_gfr, day_4_gfr, day5_gfr) .< 0),
        flag_gfr_very_high = any.((admission_gfr, day_1_gfr, day2_gfr, day_3_gfr, day_4_gfr, day5_gfr) .> 200),
        flag_day3_missing = ismissing(day_3_gfr),
        flag_day2_note = day2_parse_note
    )
end

# -------------------------
# 7) Export clean CSV (TidierFiles)
# -------------------------
TidierFiles.write_csv(df_clean, out_csv)

# -------------------------
# 8) Write a markdown QA report
# -------------------------
function df_to_md_table(d::DataFrame; max_rows::Int=50)
    n = min(nrow(d), max_rows)
    cols = names(d)
    header = "|" * join(string.(cols), "|") * "|\n"
    sep    = "|" * join(fill("---", length(cols)), "|") * "|\n"
    rows = ""
    for i in 1:n
        vals = [ismissing(d[i, c]) ? "missing" : string(d[i, c]) for c in cols]
        rows *= "|" * join(vals, "|") * "|\n"
    end
    if nrow(d) > max_rows
        rows *= "\n*(truncated to first $(max_rows) rows)*\n"
    end
    return header * sep * rows
end

report = """
# AKI Data Cleaning QA Report

## Files
- Input: `$(in_path)`
- Output CSV: `$(out_csv)`

## Row counts
- Rows after header detection + dropping note rows: **$(nrow(df_clean))**
- Columns: **$(ncol(df_clean))**

## Missing data (all columns)
$(df_to_md_table(sort(missing_summary, :n_missing, rev=true)))

## Duplicate study IDs (requires HITL)
$(nrow(dup_ids) == 0 ? "_None detected._" : df_to_md_table(dup_ids))

## Parsed-text notes (e.g., GFR cell had extra text)
Rows with `day2_parse_note = true` should be reviewed to confirm the numeric extraction matches intent.
$(df_to_md_table(@chain df_clean begin
    @filter(day2_parse_note == true)
    @select(study_id, day2gfr_raw, day2_gfr)
end))

## Outliers by IQR rule (requires HITL)
$(nrow(outlier_rows) == 0 ? "_None detected by IQR rule._" : df_to_md_table(outlier_rows))

## Sanity flags (requires HITL)
$(df_to_md_table(@chain sanity_flags begin
    @filter(flag_dob_unparsed || flag_gfr_very_high || flag_day3_missing || flag_day2_note)
    @select(study_id, flag_dob_unparsed, flag_gfr_very_high, flag_day3_missing, flag_day2_note,
            admission_gfr, day_1_gfr, day2_gfr, day_3_gfr, day_4_gfr, day5_gfr,
            dob_raw)
end))
"""

open(out_md, "w") do io
    write(io, report)
end

println("Wrote clean CSV -> $(out_csv)")
println("Wrote QA report -> $(out_md)")

What TidierOrg packages this uses (and why)

Tidier.jl (meta-package): loads the “tidyverse-like” ecosystem in one import. Tidier.jl is designed as a meta-package that re-exports functionality from smaller TidierOrg packages.
TidierFiles.jl: provides read_xlsx() and write_csv() with a consistent “readr/readxl-ish” API, powered by XLSX.jl/CSV.jl under the hood.
TidierData.jl: provides tidy macros like @chain, @mutate, @filter, @select, and @clean_names() for dplyr/tidyr-like transformations.

Markdown QA report (based on this messy_aki.xlsx)

Summary

Excel rows: 27 total on the sheet
True data table rows: 23 (after skipping title/header junk)
Non-data note rows removed: 3 (permission/notes at bottom with missing study_id)
Final analytic rows: 20
Key HITL items: duplicate study IDs, extreme/implausible GFR values, one missing Day 3 GFR, “numeric-with-text” cell.

Missing data

day_3_gfr: 1 missing (study_id 21)

Outliers (needs HITL review)

Using an IQR rule on the numeric-parsed GFR values, these stand out:

Admission GFR = 232 (study_id 20) — likely a data entry error (e.g., “23.2” or “23”?) given subsequent days are ~19–33.
Admission GFR = 177 (study_id 21) — unusually high vs cohort; may be real or a typo (“17.7” / “17”?).
Day 1 GFR = 322 (study_id 15) — almost certainly a typo (pattern suggests “32”).

Edge cases / messy-field normalization

DOB includes a free-text date ("may 5 1970") that must be parsed carefully (the script handles multiple formats).
Sex mixes Male/Female with M/F and trailing whitespace.
Race mixes casing and abbreviations (WHITE, Af-Am, Caucasian, etc.).
Hispanic includes typos (Hisspanic, Hispannic, H) and shorthand (NH).
Day2 GFR contains at least one cell like "19 but poor collection"; the script extracts the first numeric value (=19) and flags it (day2_parse_note=true) for review.

ID integrity (must resolve before modeling)

Duplicate study_id: 3 appears twice; 8 appears twice.
These represent clearly different patients/records and should be reconciled (true duplicates vs mis-keyed IDs) before train/test splits.

How to read the Julia code if you “know code but not Julia”

using Tidier is like library(tidyverse)—it brings in the TidierOrg ecosystem.
@chain df begin ... end is like %>% in R or method-chaining in Python; it pipes a DataFrame through steps.
@mutate(new = f(old)) creates/overwrites columns.
missing is Julia’s NA.
The ~ prefix inside TidierData macros tells TidierData not to auto-vectorize a function (important when you’re calling helper functions that should run “per-cell” rather than being broadcast in a surprising way).

Why choose Julia here (vs Python/R) — the practical version

Speed without giving up expressiveness: you can do heavy feature engineering and modeling in one language without “two-language” rewrites.
Composable, high-performance tabular + ML stack: DataFrames + MLJ/Flux/etc. can stay fast as the pipeline grows.
Tidyverse-like ergonomics (TidierOrg): if your team thinks in dplyr verbs, TidierData lowers the adoption cost while staying Julia-native.

If you want, I can adapt the race/ethnicity mappings and the GFR plausibility thresholds to match your trial’s data dictionary / SAP (e.g., “Unknown” handling, allowable ranges, and whether GFR is eGFR vs measured clearance).

Prompt: can you show me some python code to clean this data up?

Response:

Here’s a well-commented cleaning script that tackles every issue I spotted:

Code

import pandas as pd
import numpy as np
from dateutil import parser as dateparser
import re

# ── 1. Load, skip the 3-row title block, drop the empty column F ──────────────
df = pd.read_excel("messy_aki.xlsx", skiprows=4, header=0)
df = df.drop(columns=[df.columns[5]])          # unnamed empty column between Hispanic and Admission GFR
df = df.dropna(how="all")                       # drop fully-empty rows (incl. footer notes)

# ── 2. Standardise column names ───────────────────────────────────────────────
df.columns = [
    "study_id", "dob", "race", "sex", "hispanic",
    "gfr_admission", "gfr_day1", "gfr_day2", "gfr_day3", "gfr_day4", "gfr_day5",
    "diagnosis", "sediment", "hd_catheter", "av_fistula"
]

# ── 3. Duplicate / invalid study IDs ─────────────────────────────────────────
# IDs 3 and 8 appear twice; IDs 9, 10, 18 are missing entirely.
# Flag duplicates so you can review — don't silently drop them.
df["duplicate_id_flag"] = df.duplicated(subset="study_id", keep=False)

# ── 4. Parse dates of birth ───────────────────────────────────────────────────
def parse_dob(val):
    if pd.isna(val):
        return pd.NaT
    if isinstance(val, pd.Timestamp):
        return val
    # Handle obvious typo like "5=11-1984"
    val = str(val).replace("=", "-")
    try:
        return dateparser.parse(val, dayfirst=False)
    except Exception:
        return pd.NaT

df["dob"] = pd.to_datetime(df["dob"].apply(parse_dob), errors="coerce")

# ── 5. Standardise race ───────────────────────────────────────────────────────
race_map = {
    "caucasian": "White",
    "white":     "White",
    "af-am":     "Black",
    "black":     "Black",
    "asian":     "Asian",
    "mixed":     "Other",
    "other":     "Other",
}
df["race"] = (df["race"]
              .str.strip()
              .str.lower()
              .map(race_map)
              .fillna("Unknown"))

# ── 6. Standardise sex ────────────────────────────────────────────────────────
def clean_sex(val):
    if pd.isna(val):
        return np.nan
    v = str(val).strip().lower()
    if v in ("male", "m"):
        return "Male"
    if v in ("female", "f"):
        return "Female"
    return np.nan

df["sex"] = df["sex"].apply(clean_sex)

# ── 7. Standardise hispanic ethnicity ────────────────────────────────────────
def clean_hispanic(val):
    if pd.isna(val):
        return np.nan
    v = str(val).strip().lower().replace(" ", "")
    if v in ("hispanic", "h", "hispannic", "hisspanic"):   # typos
        return "Hispanic"
    if v in ("nothispanic", "nh"):
        return "Not Hispanic"
    return np.nan

df["hispanic"] = df["hispanic"].apply(clean_hispanic)

# ── 8. Clean GFR columns — coerce text to numeric, flag outliers ──────────────
gfr_cols = ["gfr_admission", "gfr_day1", "gfr_day2", "gfr_day3", "gfr_day4", "gfr_day5"]

def extract_numeric(val):
    """Pull the first number out of a cell; return NaN if none found."""
    if pd.isna(val):
        return np.nan
    try:
        return float(val)
    except (ValueError, TypeError):
        match = re.search(r"\d+\.?\d*", str(val))
        return float(match.group()) if match else np.nan

for col in gfr_cols:
    df[col] = df[col].apply(extract_numeric)

# Flag physiologically implausible GFR values (> 200 are likely data-entry errors)
GFR_MAX = 200
for col in gfr_cols:
    flag_col = col + "_outlier_flag"
    df[flag_col] = df[col] > GFR_MAX

# ── 9. Standardise Yes/No columns ────────────────────────────────────────────
def clean_yesno(val):
    if pd.isna(val):
        return np.nan
    v = str(val).strip().lower().replace("`", "")   # "No`" typo
    if v == "yes":
        return "Yes"
    if v == "no":
        return "No"
    return np.nan

for col in ["hd_catheter", "av_fistula"]:
    df[col] = df[col].apply(clean_yesno)

# ── 10. Standardise sediment free-text (minor normalisation) ─────────────────
df["sediment"] = (df["sediment"]
                  .str.strip()
                  .str.lower()
                  .str.replace(r"\s+", " ", regex=True)
                  .str.replace("epi cell", "epithelial cell")
                  .str.replace("gran ", "granular "))

# ── 11. Report a cleaning summary ─────────────────────────────────────────────
print("=== Cleaning summary ===")
print(f"Rows: {len(df)}")
print(f"\nDuplicate study IDs:\n{df[df['duplicate_id_flag']][['study_id','dob','diagnosis']]}")
print(f"\nUnparseable DOBs: {df['dob'].isna().sum()}")
print(f"\nGFR outlier flags:")
for col in gfr_cols:
    n = df[col + "_outlier_flag"].sum()
    if n:
        print(f"  {col}: {n} outlier(s) — {df.loc[df[col+'_outlier_flag'], ['study_id', col]].values.tolist()}")

print(f"\nSex nulls after cleaning:      {df['sex'].isna().sum()}")
print(f"Hispanic nulls after cleaning: {df['hispanic'].isna().sum()}")
print(f"Race unknowns:                 {(df['race']=='Unknown').sum()}")

# ── 12. Save ──────────────────────────────────────────────────────────────────
df.to_csv("aki_clean.csv", index=False)
print("\nSaved to aki_clean.csv")

=== Cleaning summary ===
Rows: 22

Duplicate study IDs:
    study_id        dob           diagnosis
2        3.0 1970-05-05        bleeding out
3        3.0 1981-06-03  norovirus diarrhea
8        8.0 1982-04-22     C diff diarrhea
9        8.0 2003-10-12           NSAID tox
21       NaN        NaT                 NaN
22       NaN        NaT                 NaN

Unparseable DOBs: 2

GFR outlier flags:
  gfr_admission: 1 outlier(s) — [[20.0, 232.0]]
  gfr_day1: 1 outlier(s) — [[15.0, 322.0]]

Sex nulls after cleaning:      2
Hispanic nulls after cleaning: 2
Race unknowns:                 2

Saved to aki_clean.csv

Here’s a rundown of every issue it handles:

Structure — skips the 3-row title block (“Acute Kidney Injury Study”, resident name, project name) and the stray empty column between Hispanic and Admission GFR.

Column names — normalises the wildly inconsistent headers (DAY2GFR\``,day 3 GfR`, etc.) to snake_case.

Duplicate IDs — study_ids 3 and 8 each appear twice (and 9, 10, 18 are missing). The script flags duplicates rather than silently dropping them, since you’ll need to decide which row is correct.

Dates of birth — handles three formats: Excel dates, “may 5 1970” (free text), and “5=11-1984” (typo with = instead of -).

Race — merges “Caucasian”/“WHITE”/“white” → “White”, “Af-Am”/“Black” → “Black”, etc.

Sex — normalises “M”, “F”, “Male”, “Female”, “F” → consistent “Male”/“Female”.

Hispanic — fixes typos (“Hispannic”, “Hisspanic”) and abbreviations (“H”, “NH”).

GFR values — row 2 has “19 but poor collection” (extracts 19), and several rows have values like 322 or 232 that are almost certainly data-entry errors (extra digit). Those are flagged with outlier columns.

Yes/No fields — handles “YES”, “NO”, “No`” (backtick typo) → “Yes”/“No”.

Prompt: You are an expert R programmer fluent in the Tidyverse. Provide a [TK]

Response:

Hands-On Exercise: Screening Patient Data for Clinical Trial Eligibility

Actively Recruiting Clinical Trial Example

What can be automated vs. what cannot

Usually automatable or semi-automatable from Clarity

Usually not fully automatable from Clarity alone

Recommended project structure

1) Secure environment setup with uv

Commands

Linux / macOS

Windows PowerShell

2) pyproject.toml

3) .gitignore

4) .env.example

5) src/trial_eligibility/settings.py

6) src/trial_eligibility/db.py

7) sql/candidate_extract.sql

8) src/trial_eligibility/logic.py

9) src/trial_eligibility/main.py

10) README.md

Configure secrets

Run

Security notes

Required local mapping

Screening categories

⚠️ Important Caveats

Missing Data & Limitations

Step 1: Environment Setup

.Renviron Setup

Dependency Management

Step 2: The Eligibility Pipeline

Strategy for Qualitative Criteria

Visualizing the Data Flow

Next Steps

Additional Hands-On Exercises

Exercise 1: Code Explanation

Exercise 2: Data Reporting, Visualization, and Predictive Modeling

Exercise 3: Munging Messy Data

1) Secure environment setup with `uv`

2) `pyproject.toml`

3) `.gitignore`

4) `.env.example`

5) `src/trial_eligibility/settings.py`

6) `src/trial_eligibility/db.py`

7) `sql/candidate_extract.sql`

8) `src/trial_eligibility/logic.py`

9) `src/trial_eligibility/main.py`

10) `README.md`