PICdb MEDS Extraction ETL

This pipeline extracts the Paediatric Intensive Care (PIC) database v1.1.0 from PhysioNet into the MEDS format. PIC holds 12,881 neonatal and paediatric patients and 13,449 hospital admissions from the Children's Hospital of Zhejiang University School of Medicine (2010–2018). It was built from the ETL_MEDS_Template and uses the config-only layout introduced by MEDS-Extract 0.7, and suMEDS enriches its code metadata.

Usage

PIC is a credentialed dataset. You need a PhysioNet account that has signed the PIC data use agreement.

pip install git+https://github.com/rvandewater/PICdb_MEDS.git
export DATASET_DOWNLOAD_USERNAME=$PHYSIONET_USERNAME
export DATASET_DOWNLOAD_PASSWORD=$PHYSIONET_PASSWORD
meds-extract-run spec=PICdb output_dir=$MEDS_OUTPUT_DIR

When you run this, the program will:

1. Download the v1.1.0 tables (V1.1.0/*.csv, about 1.2 GB) from PhysioNet into $MEDS_OUTPUT_DIR/.meds_extract_run/raw_input. Files that already exist and match their checksums are skipped, so an interrupted download resumes. Pass download_dest_dir=$RAW_INPUT_DIR to keep the raw data somewhere durable and reuse it across runs.
2. Build the MEDS cohort from those files and write it to $MEDS_OUTPUT_DIR (data/ and metadata/). All transformations, joins and metadata extraction are declared in src/PICdb_MEDS/event_configs.yaml.
3. Enrich metadata/codes.parquet in place with suMEDS (installed with this package), as the spec's last stage, sumeds_enrich, using the rules this package ships: LOINC names, ICD-O-3 morphology tags and same-dataset diagnosis parents. If it cannot (no internet, say), the ETL still succeeds, unenriched, and suMEDS-enrich $MEDS_OUTPUT_DIR finishes it later (see Enriching code metadata with suMEDS).

For the plain ETL without step 3, add skip_stages=[sumeds_enrich].

If you already have the PhysioNet release on disk, skip the download and point input_dir at the release root, the directory that contains V1.1.0/:

meds-extract-run spec=PICdb output_dir=$MEDS_OUTPUT_DIR \
	do_download=false input_dir=/path/to/paediatric-intensive-care-database-1.1.0

To parallelize the stages, pass a runner file:

printf 'parallelize:\n  n_workers: 8\n  launcher: joblib\n' >runner.yaml
meds-extract-run spec=PICdb output_dir=$MEDS_OUTPUT_DIR stage_runner_fp=runner.yaml

Run meds-extract-run --help for every pipeline option and suMEDS-enrich --help for every enrichment option.

How this ETL is defined

The package contains no Python. The whole ETL is one MESSY (MEDS-Extract Specification Syntax YAML) file, src/PICdb_MEDS/event_configs.yaml. It declares:

• sources:: where the raw data lives (the PhysioNet release, restricted to V1.1.0/*.csv).
• etl:: the dataset name and sharding options, what metadata/dataset.json says about the dataset, and the suMEDS enrichment stage that runs after the ETL.
• One block per raw table: how each row becomes one or more MEDS events, and which dictionary tables describe the resulting codes.

pyproject.toml registers the file with MEDS-Extract's MEDS_extract.pipelines entry-point group under the name PICdb. That registration is what makes spec=PICdb work. A second entry point registers src/PICdb_MEDS/sumeds_rules.yaml with suMEDS under the same name, which is how the spec's enrichment stage finds them. No pre-MEDS step is needed: every PIC table already carries SUBJECT_ID, and every timestamp has the same format.

metadata/dataset.json gives the release's license and PhysioNet page (description_uri), and lists hadm_id, icustay_id, visit_id, oper_id and emr_id, PIC's own record keys, under raw_source_id_columns. MEDS-Extract lists every other extension column under other_extension_columns.

Events

A missing unit or susceptibility interpretation becomes UNK in the code. A row with a missing identifying component (for example, no organism) produces no event for that component.

Code metadata

metadata/codes.parquet describes every code except MEDS_BIRTH and MEDS_DEATH, and links codes to PIC's dictionary tables:

• description: one English description per code, from one of two routes.
  • PIC dictionaries: the English label from D_LABITEMS (labs), D_ICD_DIAGNOSES (diagnoses) or D_ITEMS (chart, output and intra-operative vital items, organisms, antibiotics, and the specimen IDs D_ITEMS lists). These are joined when the ETL runs; nothing from them is copied into this repository.
  • Templates: every other code gets a _self template in the event config. The template restates the code's parts in words and names the PIC column they come from, for example ICU admission; first care unit: PICU, paediatric ICU (PIC ICUSTAYS.FIRST_CAREUNIT). The wording follows the PhysioNet PIC documentation and the PIC paper (Zeng et al. 2020, CC BY 4.0). The paper names the five ICUs and says what INPUTEVENTS holds. The PhysioNet page explains the +/- symptom attribute and the "Other" ethnicity group. A specimen ID that D_ITEMS does not list gets a description saying so; its Chinese name is in spec_type_desc_cn.
  • MEDS_BIRTH and MEDS_DEATH have no description. MEDS-Extract 0.8 attaches no metadata to a literal code, and the MEDS standard defines what these two mean.
• parent_codes: LOINC/… for lab items with a well-formed LOINC code, and ICD10/… for diagnoses whose mapped code is a valid 3- or 4-character WHO ICD-10 code. Both come from PIC's own dictionaries (D_LABITEMS.LOINC_CODE, D_ICD_DIAGNOSES.ICD10_CODE). PIC's own extended codes (7+ characters) have no parent. Other vocabularies and same-dataset parents are added by suMEDS (next section).
• Chinese source terms: kept in their own columns (title_cn, label_cn, drug_name_cn, org_name_cn, spec_type_desc_cn, ab_name_cn, symptom_name_cn). Codes are always built from the English or ID columns.
• Extra dictionary fields: fluid, category and unit.

Enriching code metadata with suMEDS

External-ontology work lives in suMEDS, not in this ETL. The package ships the rules for it, src/PICdb_MEDS/sumeds_rules.yaml, registered in suMEDS's sumeds.rules entry-point group under the same name as the MEDS-Extract spec, PICdb, so --rules PICdb works for any suMEDS command. The spec runs them as its last stage:

etl:
  post_stages:
    - sumeds_enrich: {rules: PICdb}

The stage replaces metadata/codes.parquet only once the enriched table is complete, and it stays valid MEDS code metadata. By default the LOINC data comes from suMEDS's ontology packs, fetched from GitHub on first use and cached; no Athena download is needed.

metadata/dataset.json says whether a build is enriched: sumeds_enrichment.enriched is true (with the rules, pack source and coverage) or false (with the error and the retry command).

jq .sumeds_enrichment.enriched $MEDS_OUTPUT_DIR/metadata/dataset.json

Enrichment soft-fails: if it cannot run (a server without internet, for example), the ETL still succeeds with metadata/codes.parquet left exactly as MEDS-Extract writes it, and dataset.json records "enriched": false. The warning, with the command that finishes the enrichment, is in $MEDS_OUTPUT_DIR/.logs/pipeline.log, not on the console. MEDS-Extract marks the stage done either way, so running meds-extract-run again does not retry it. Enrich the build afterwards, with the rules and options of the failed run:

suMEDS-enrich $MEDS_OUTPUT_DIR
# Air-gapped: from a copy of the sumeds-ontologies packs directory
suMEDS-enrich $MEDS_OUTPUT_DIR --ontology-source /path/to/sumeds-ontologies/ontologies --offline

For the plain, unenriched ETL, leave the stage out with skip_stages=[sumeds_enrich]:

meds-extract-run spec=PICdb output_dir=$MEDS_OUTPUT_DIR skip_stages=[sumeds_enrich]

The stage takes the suMEDS-enrich options, spelled with underscores. Set them for one run with overrides=, as ++stage_cfg.<option> (the override reaches every stage of the pipeline, and only this one declares the option, hence the ++):

# Make a failed enrichment fail the run (exit code 1):
meds-extract-run spec=PICdb output_dir=$MEDS_OUTPUT_DIR "overrides=['++stage_cfg.strict=true']"
# Also look codes up in your own OHDSI Athena download:
meds-extract-run spec=PICdb output_dir=$MEDS_OUTPUT_DIR "overrides=['++stage_cfg.athena_csv=/path/to/athena']"
# Read the packs from a local copy (or set SUMEDS_ONTOLOGY_SOURCE=/path/to/ontologies):
meds-extract-run spec=PICdb output_dir=$MEDS_OUTPUT_DIR "overrides=['++stage_cfg.ontology_source=/path/to/ontologies']"

On a cluster whose compute nodes have no internet access, fill a cache on shared storage once from a login node, then run with offline, so nothing tries the network:

# The same cache on the login node and the compute nodes:
export XDG_CACHE_HOME=/shared/project/cache
# On the login node, download the pinned pack release (about 30 MB):
suMEDS-enrich --prefetch --rules PICdb
# On a compute node:
meds-extract-run spec=PICdb output_dir=$MEDS_OUTPUT_DIR "overrides=['++stage_cfg.offline=true']"

Enrichment never overwrites what the ETL wrote: PIC's own descriptions, Chinese terms and parent_codes stay as they are, and new parents are appended after the existing ones. For PICdb it adds:

• LOINC names: the LOINC/… parents of lab codes are looked up in suMEDS's LOINC pack. Each one found gets its official LOINC name in parent_names (a map from parent code to name), and the lab code gets vocabulary_id LOINC and its concept_code. LOINC terms have no parents in the pack, so no parents are added unless you set loinc_hierarchy (--loinc-hierarchy for suMEDS-enrich), which appends the LOINC Parts on the term's paths in the LOINC multiaxial hierarchy. The ICD10/… parents of diagnoses stay unnamed, because WHO ICD-10 may not be redistributed and has no pack; with an Athena download (athena_csv) they get their names and ancestors from Athena.
• ICD-O-3 morphology (picdb-icdo3-morphology): diagnoses coded in the Chinese edition of ICD-O-3 (M80000/3; the PIC paper notes that diagnoses use both ICD-10 and ICD-O-3) get the OMOP ICDO3 histology code (ICDO3//8000/3). suMEDS's versioned ontology data (sumeds-ontologies, pinned release) has no ICD-O-3 pack, so this is a tag that is validated only when Athena is configured (athena_csv).
• Same-dataset parents (picdb-diagnosis-prefix, picdb-admission-diagnosis-prefix): a diagnosis code gets the nearest shorter code of the same family that exists in the build, skipping dots, for example DIAGNOSIS//ICD10_CN//A18.000+ under DIAGNOSIS//ICD10_CN//A18.000, or a PIC extension such as P07.300x001 under P07.300. Diagnoses and admission diagnoses are linked separately.

The ETL alone writes no ICDO3 parents and no same-dataset parents; skip_stages=[sumeds_enrich] leaves them out. The same rules work with suMEDS … --rules PICdb to build a privacy-masked code catalog of the output.

Data notes

• Shifted dates. Dates are shifted 50–100 years into the future per patient. Years between 2049 and 2119 are expected, and intervals within a patient are preserved.
• Release layout. The v1.1.0 release ships the current tables under V1.1.0/. The gzipped files at the release root are the superseded v1.0.0 tables, and this ETL does not read them.
• Text encoding. Every v1.1.0 CSV is UTF-8, including the Chinese columns. The config declares etl.encoding: utf8, so a file in another encoding fails loudly. MEDS-Extract's default (auto) could instead decode it as cp1252, which garbles Chinese text.
• Swapped column names. In PRESCRIPTIONS, the CSV header DRUG_NAME holds the Chinese name and DRUG_NAME_EN the English one. The bundled SQL scripts name these columns differently.
• Many-to-many microbiology IDs. Item IDs and their Chinese names do not map one-to-one, so codes use the item IDs. Expect MEDS-Extract warnings that some codes have several *_cn values; those values are aggregated into lists.
• Uninformative columns. LANGUAGE, RELIGION and MARITAL_STATUS hold a single value across the release and are not extracted.
• Local diagnosis codes. Diagnoses use PIC's extended Chinese ICD-10 codes (ICD10_CN), so they do not yet line up with standard ICD-10 codes in other datasets. Valid standard codes are attached as parent_codes. Mapping equivalent codes to standard ICD-10 needs a MEDS-Extract feature; see #1.

Testing

PIC has no open demo release. Instead, tests/e2e_synthetic_test.py writes a few fully synthetic rows with PIC's exact headers and layout, runs meds-extract-run spec=PICdb over them, and checks the events, times, values and code metadata it produces. That includes checking that the output validates against meds.CodeMetadataSchema. tests/test_code_descriptions.py checks the config itself: every event block declares exactly one description, a _self description reads only the code's own parts, and parents use OMOP vocabulary prefixes. tests/test_sumeds_rules.py checks that the rules are registered under PICdb, loads sumeds_rules.yaml with suMEDS, which rejects a malformed rule, and applies each rule through suMEDS to synthetic codes built from the code templates in the event config. tests/test_run_with_enrichment.py runs meds-extract-run spec=PICdb with its enrichment stage over the synthetic release, offline against a tiny synthetic LOINC pack, and checks that the enrichment adds the expected names and parents, keeps everything the plain build (skip_stages=[sumeds_enrich]) wrote, and is recorded in dataset.json. It also runs against an empty pack source, to check the soft failure and its suMEDS-enrich retry, and with strict.

uv sync --group dev
uv run pytest tests/ -v
uv run pre-commit run --all-files

Summary statistics

tests/regression/full_summary_stats.json is a meds_summary_stats fingerprint of the MEDS dataset this ETL builds from the full PICdb 1.1.0 release with meds-extract-run spec=PICdb, so its code-metadata coverage includes the suMEDS enrichment. It covers subject, event and measurement counts, digests of the code vocabulary, per-subject, value and time distributions, code-metadata coverage, the splits and the schema. It names no code and reports no cell of fewer than 20 subjects and no minimum or maximum, which is what makes it safe to publish. Because it is committed next to the ETL, its diff shows what an ETL change did to the output, and two ETL versions can be compared without access to the data.

Regenerate it in every pull request that changes the output, from a build of that branch:

# Build the release with this checkout, then summarize it
scripts/summary_stats.sh full --input-dir $RAW_INPUT_DIR
# Or summarize a build of this checkout you already have
scripts/summary_stats.sh full --meds-dir $MEDS_OUTPUT_DIR

Both print how the statistics changed against the committed baseline before overwriting it, and refuse a build whose enrichment failed (finish it with suMEDS-enrich first). Add --compare to check a build without writing anything; it exits 1 when tests/regression/policy.yaml flags an error. Rebuilding the same commit reproduces the baseline exactly, so any finding is a change in the output. CI cannot rebuild the credentialed release, so the Summary stats workflow only warns when a pull request changes src/ (the config or the suMEDS rules) without regenerating the baseline. A MEDS-Extract, suMEDS or polars upgrade can also move the output, so check it with --compare.

Releasing

A release bumps the version in pyproject.toml with uv version --bump, commits it with the updated CITATION.cff and pushes a tag of that exact version, which publishes to PyPI and creates the GitHub release. See RELEASING.md for choosing the bump, the full release commands, and recovering from a failed release.

Citation

If you use this ETL, please cite the PIC database and its descriptor paper:

@article{PhysioNet-picdb-1.1.0,
  author = {Li, Haomin and Zeng, Xian and Yu, Gang},
  title = {{Paediatric Intensive Care database}},
  journal = {{PhysioNet}},
  year = {2020},
  month = nov,
  note = {Version 1.1.0},
  doi = {10.13026/32x9-wv38},
  url = {https://doi.org/10.13026/32x9-wv38}
}
@article{zengPICPaediatricspecificIntensive2020,
  title = {{{PIC}}, a Paediatric-Specific Intensive Care Database},
  author = {Zeng, Xian and Yu, Gang and Lu, Yang and Tan, Linhua and Wu, Xiujing and Shi, Shanshan and Duan, Huilong and Shu, Qiang and Li, Haomin},
  year = 2020,
  month = jan,
  journal = {Scientific Data},
  volume = {7},
  number = {1},
  pages = {14},
  publisher = {Nature Publishing Group},
  issn = {2052-4463},
  doi = {10.1038/s41597-020-0355-4},
  copyright = {2020 The Author(s)},
  langid = {english},
}

See also CITATION.cff.