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.

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.

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 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.
• 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. No pre-MEDS step is needed: every PIC table already carries SUBJECT_ID, and every timestamp has the same format.

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.7 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 (also installed as package data), which suMEDS applies to a finished build (this needs suMEDS 0.4 or later, pip install "sumeds>=0.4"):

suMEDS-enrich "$MEDS_OUTPUT_DIR/metadata/codes.parquet" -o codes-enriched.parquet \
	--rules src/PICdb_MEDS/sumeds_rules.yaml

The rules add, as VOCABULARY//CODE parents and same-dataset codes:

• 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.

The ETL alone writes no ICDO3 parents and no same-dataset parents; run suMEDS to get them.

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.
• 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 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.

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

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.