AI-assisted ELSA-to-OMOPCDM Pipeline

Abstract

ELSA Harmonisation & OMOP CDM CSV Generation Python tooling that harmonises the ELSA (English Longitudinal Study of Ageing) dataset into a single consolidated table and turns it into OMOP CDM v5.4 data CSVs, together with a machine-readable provenance log of every transformation applied. This repository is part of a research project intended for publication in the Springer Nature Computer Science 2026 journal as an extension of the previous HEALTHINF 2026 conference proceedings work. Scope of this version. This release covers only the harmonisation and OMOP-shaped CSV generation stages of the ELSA-to-OMOP work — i.e. everything up to and including the OMOP CDM CSV files. It does not cover loading those CSVs into a database, the OMOP vocabulary load, USAGI mapping, the PostgreSQL/source_to_concept_map steps, or the R/ETL-Synthea scaffolding. Those remain in the original ELSA-to-OMOPCDM repository and are unchanged. Data access & licensing This repository contains no ELSA data and no access credentials. ELSA data must be obtained through the proper channels at the UK Data Service, and all use must comply with the UK Data Service End User Licence Agreement. The data is safeguarded: access requires accepting the UKDS End User Licence Agreement. Redistribution of ELSA data, and commercial use, are prohibited unless explicitly permitted by the data owner or their nominee. You may use this code internally to process ELSA data you are authorised to access. Users of the dataset additionally agree not to link (or attempt to link) ELSA Wave 0 data to the Health Survey for England (HSE), not to use the data to identify participants, and not to identify the geography from which the sample was drawn. Citation is required — see Citation below. This work was supported by the SHIELD project (project number: 101156751), funded by the European Commission under the Health cluster of the Horizon Europe framework. Views and opinions expressed are those of the author(s) only and do not necessarily reflect those of the European Union or the European Commission. Neither the European Union nor the granting authority can be held responsible for them. Requirements Python 3.9+ pandas, numpy Optional, only needed for the variable-discovery stage: PyPDF2 — PDF documentation parsing (without it, PDFs are skipped) striprtf — improves RTF extraction tqdm — progress bars (a no-op fallback is used if absent) A running Ollama instance with the nomic-embed-text model, for semantic search only pip install pandas numpy PyPDF2 striprtf tqdm # semantic search only: ollama pull nomic-embed-text Inputs you provide Input Used by Notes ELSA .rtf data dictionaries discovery stages one per documentation file ELSA .pdf documentation semantic search (optional) skipped if PyPDF2 absent ELSA .tab data files consolidate.py the actual wave data (tab-separated) CONCEPT.csv table2omop.py OMOP vocabulary download from Athena (tab-separated despite the .csv name) Repository contents Pipeline scripts semantic_targeted_research.py — Semantic variable & documentation search. Parses the ELSA RTF dictionaries and PDFs and, given free-text queries, retrieves the most similar variables and documentation passages using Ollama embeddings. Used for manual variable discovery before the pipeline proper. Writes results to search_results/. variable_targeted_research.py — Targeted variable extraction + coding workbench. Selects variables from a curated wishlist (edit the lists at the top of the file) and produces a single self-contained HTML workbench you open in a browser to review each variable, include/exclude it, and assign a standardised vocabulary code (SNOMED CT, LOINC, ICD-10/11, ATC, RxNorm, OPCS-4, Read/CTV3, or custom). You then export the decisions as a CSV/JSON crosswalk. Reuses the RTF parser from semantic_targeted_research.py. consolidate.py — Builds the final consolidated table: one row per (idauniq, wave), with each harmonised variable emitted as its value plus companion _code / _valuecode / _date columns. Identifier/weight columns are split into a separate per-respondent index file. Consumes a value-map JSON (see note below) and the directory of .tab files. Every decision is recorded in the harmonisation log. table2omop.py — Converts the consolidated table into OMOP CDM v5.4 data CSVs. Each variable's concept code is resolved via CONCEPT.csv; the concept's domain_id routes it to the right table. Emits person, observation_period, visit_occurrence, condition_occurrence, drug_exposure, procedure_occurrence, measurement, observation, and death. Supporting modules (imported, not run directly) elsa_io.py — Shared helpers: ELSA wave↔year mapping, value canonicalisation, and the wave-resolution logic that decides how each .tab file's rows are assigned to a wave (harmonised cross-wave file, single-wave filename, in-file wave column, or unknown). harmonization_log.py — Emits the machine-readable provenance/quality log as JSON-LD (PROV-O), Markdown (human-readable audit report), and TSV (flat log). Uses PROV-O, DQV, DCAT, Dublin Core, DDI-RDF Discovery, SKOS, and P-Plan vocabularies. Utility variable_groups.py — Quick lookup over a variable_classification.csv: lists clinical (insert-role) variables with no concept_system and those coded in ICD-10. Workflow ELSA .rtf / .pdf ──▶ semantic_targeted_research.py (optional: discover variables) │ ▼ variable_targeted_research.py (HTML coding workbench) │ export ▼ variable→concept crosswalk (CSV/JSON) │ ▼ [ value-mapping step → elsa_valuemap.json ] ⚠ see note │ ELSA .tab ──────────────┼──▶ consolidate.py ──▶ consolidated.csv + index file └▶ harmonization_log.{jsonld,report.md,tsv} │ CONCEPT.csv ────────────┴──▶ table2omop.py ──▶ ./omop_cdm/*.csv (OMOP CDM v5.4) 1. (Optional) Discover variables python semantic_targeted_research.py --root /path/to/elsa \ --queries "sleep quality" "loneliness" # or: --queries-file queries.txt (one query per line; # comments allowed) 2. Extract & code variables python variable_targeted_research.py --root /path/to/elsa --out wanted_variables.html Open wanted_variables.html in a browser, make your include/exclude and coding decisions, then use Export mapping to save the crosswalk. Progress autosaves to the browser and can be reloaded from an exported file. 3. Produce the value map ⚠️ consolidate.py consumes a value-map JSON (elsa_valuemap.json) that assigns each variable a role (insert / index / date / filter / ignore), its concept code, per-value → concept mappings, and any date-source / filter references. The script that produces this JSON (value_mapping_workbench.py) is not part of this upload — add it here, or provide elsa_valuemap.json from your own process, before running Stage 4. 4. Consolidate + provenance log python consolidate.py \ --valuemap elsa_valuemap.json \ --tab-dir ./tab \ --out consolidated.csv \ --extra extra_variables.json \ # optional: variables missed by the workbench --log-dir ./out Outputs: consolidated.csv (one row per idauniq/wave), a separate per-respondent index file (identifiers + longitudinal weights), and harmonization_log.jsonld / harmonization_report.md / harmonization_log.tsv. Use --format parquet to write the table as Parquet instead of CSV. 5. Generate OMOP CDM CSVs python table2omop.py \ --consolidated consolidated.csv \ --concept ./vocabulary/CONCEPT.csv \ --out-dir ./omop_cdm Outputs ./omop_cdm/*.csv for the OMOP CDM v5.4 tables listed above. From here, loading into a database, vocabulary population, and USAGI mapping are handled by the (separate, unchanged) database/ETL part of the original repository. 6. Database generation and upload See the previous version for the method to initialize the database and upload these records to it. Provenance & reproducibility Every inconsistency detected and transformation applied during consolidation is recorded by harmonization_log.py in three synchronised forms — JSON-LD (PROV-O compatible), a Markdown audit report, and a flat TSV — so the harmonisation is auditable and the mapping decisions are traceable back to source variables and waves. Citation Users of this code are required to cite this Zenodo repository. Once the related HEALTHINF 2026 publication is available, it should be cited alongside this repository. Acknowledgements This ETL design (from a harmonised file to OMOP CDM) draws inspiration from the OHDSI ETL-Synthea project, which informed aspects of the mapping and transformation logic.