Building a SNOMED-to-ICD-10 Crosswalk Table in Python

Before any resolver can translate a clinical concept into a billing code, it needs a crosswalk table that is loaded correctly, version-pinned, and physically laid out for fast keyed lookup — and getting that construction step wrong silently corrupts every downstream mapping decision. This page is the build-time companion to SNOMED CT to ICD-10 mapping strategies: that page covers how the decision-table resolver evaluates mapRule and mapPriority at request time; here we focus strictly on the artifact it reads from — taking the NLM-published der2_iisssccRefset_ExtendedMapFull RF2 file and turning it into a queryable, immutable store with the right indexes. Nothing here evaluates a rule; it produces the table those rules live in.

The distinction matters because the crosswalk is reference data with a long shelf life. A record authored today may be re-adjudicated against this table years from now, so the build has to be reproducible, the version has to be stamped into every row, and each new SNOMED release has to add a generation rather than overwrite the last. Get the load, the filter, and the physical layout right once, and the resolver becomes a pure function of its inputs.

RF2 source column to crosswalk column (quick reference)

The RF2 ExtendedMapFull file is a tab-delimited refset export with a fixed column order. You do not keep all of it — the refset bookkeeping columns (id, effectiveTime, moduleId, refsetId) exist to version the release itself, not to drive a lookup. The table below is the projection to keep: every column you carry into the crosswalk, the name to give it, and why it earns its place. Load the whole file as strings, then select down to this set.

Two columns are load-bearing beyond their obvious role. active is a filter, never a stored value: the Full refset is cumulative and contains every historical state of every row, so a build that forgets active == "1" will double-count concepts and resurrect retired targets. And snomed_version does not exist in the file at all — you synthesize it from the release you are loading, because it is the other half of the compound lookup key that keeps historical records stable.

Implementation pattern

The build is a short, deterministic pipeline: read the tab-delimited file as strings, filter to active rows, project and rename to the crosswalk schema, stamp the version, sort into the canonical order the resolver expects, then persist to a columnar store plus a lookup index. The diagram traces those stages and the two artifacts they produce.

The core builder reads the file with dtype=str — this is non-negotiable, because SNOMED concept ids exceed 53-bit float precision and ICD-10 targets like S72.90XA are not numbers at all. Pandas will silently coerce a numeric-looking id column to float and corrupt the trailing digits unless every column is forced to string on read.

import pandas as pd

# RF2 ExtendedMapFull column order is fixed by the SNOMED refset spec.
KEEP = {
    "referencedComponentId": "concept_id",
    "mapGroup": "map_group",
    "mapPriority": "map_priority",
    "mapRule": "map_rule",
    "mapAdvice": "map_advice",
    "mapTarget": "map_target",
    "correlationId": "correlation_id",
}


def build_crosswalk(rf2_path: str, snomed_version: str) -> pd.DataFrame:
    """Construct a queryable SNOMED-to-ICD-10 crosswalk from one RF2 release.

    Reads the tab-delimited ExtendedMapFull file as strings, keeps only
    active rows, projects to the crosswalk schema, stamps the release
    version, and sorts into (concept, group, priority) resolution order.
    """
    df = pd.read_csv(
        rf2_path,
        sep="\t",
        dtype=str,             # concept ids and ICD-10 targets are NOT numbers
        keep_default_na=False,  # empty mapTarget must stay "", not become NaN
    )

    # 1. Honour the RF2 active flag. The Full refset is cumulative and holds
    #    every historical row state; only active == "1" is current truth.
    df = df[df["active"] == "1"].copy()

    # 2. Project to the crosswalk schema and rename.
    df = df[list(KEEP)].rename(columns=KEEP)

    # 3. Stamp the release version onto every row — the file has no such column.
    df["snomed_version"] = snomed_version

    # 4. Sort into the order the resolver evaluates. map_group / map_priority
    #    are numeric-in-string, so sort on an integer view, not lexically
    #    (otherwise "10" sorts before "2").
    df = df.assign(
        _g=df["map_group"].astype(int),
        _p=df["map_priority"].astype(int),
    ).sort_values(["concept_id", "_g", "_p"]).drop(columns=["_g", "_p"])

    return df.reset_index(drop=True)

With the frame built, persist it two ways for two access patterns. A columnar parquet dataset partitioned by snomed_version gives cheap version-scoped scans and lets a new release land as a new partition directory without touching the old one. A lightweight lookup index keyed by snomed_version|concept_id gives O(1) point reads for the resolver’s hot path, so it never scans the table per message.

from pathlib import Path

def persist_crosswalk(df: pd.DataFrame, root: str) -> None:
    """Write the crosswalk as a version-partitioned parquet dataset plus a
    SQLite point-lookup index keyed by version|concept."""
    root_path = Path(root)

    # Parquet: one partition per release; existing generations are untouched.
    df.to_parquet(
        root_path / "crosswalk",
        partition_cols=["snomed_version"],
        index=False,
    )

    # SQLite: an index for point reads on the compound key. Rows for one
    # concept stay contiguous and pre-sorted so the resolver reads a slice.
    import sqlite3
    con = sqlite3.connect(root_path / "crosswalk.sqlite")
    df.to_sql("crosswalk", con, if_exists="append", index=False)
    con.execute(
        "CREATE INDEX IF NOT EXISTS ix_lookup "
        "ON crosswalk (snomed_version, concept_id, map_group, map_priority)"
    )
    con.commit()
    con.close()

The compound index column order is deliberate: leading with snomed_version then concept_id makes the version-pinned point read a single index seek, and trailing with map_group, map_priority means the rows come back already in resolution order — the resolver reads a contiguous, pre-sorted slice and never sorts at request time. Building the store this way keeps the resolver a pure function: same version, same concept, same rows, every time.

Validation and testing

A crosswalk build is reference-data ETL, so its tests are structural invariants rather than behavioural assertions. Two checks catch the failure modes that actually reach production: a row count that drifts from the release manifest (a silent parse or filter bug), and a concept group with no terminal fall-through row (which would drop patients whose context matches no guard). Assert both at build time and fail the pipeline before the artifact is published.

def validate_crosswalk(df: pd.DataFrame, manifest_active_rows: int) -> None:
    """Structural gates on a freshly built crosswalk. Raise, never warn."""
    # 1. Row count must equal the release manifest's active-row count.
    assert len(df) == manifest_active_rows, (
        f"row count {len(df)} != manifest {manifest_active_rows}: "
        "check active filter and delimiter parsing"
    )

    # 2. Every (concept, group) must end in a terminal fall-through row so no
    #    patient context can fall off the end of the decision table.
    terminal = df["map_rule"].str.upper().str.match(r"\s*(TRUE|OTHERWISE)")
    groups = df.groupby(["concept_id", "map_group"])
    missing = [k for k, g in groups if not terminal[g.index].any()]
    assert not missing, f"{len(missing)} group(s) lack a terminal rule: {missing[:5]}"

    # 3. Version stamp is present and singular for this generation.
    assert df["snomed_version"].nunique() == 1, "mixed versions in one build"

Wire these into pytest against a frozen fixture slice of the RF2 file so a malformed release, a shifted column, or a manifest mismatch fails CI instead of the live resolver. The manifest count comes from the SNOMED release’s own record-count files; treat any deviation as a hard stop, because a crosswalk that is off by even one row is off by an unknown one.

import pytest

def test_build_is_deterministic(tmp_path):
    df1 = build_crosswalk("fixtures/extmap_slice.txt", "20240301")
    df2 = build_crosswalk("fixtures/extmap_slice.txt", "20240301")
    pd.testing.assert_frame_equal(df1, df2)          # reproducible
    validate_crosswalk(df1, manifest_active_rows=len(df1))
    assert (df1["concept_id"].str.len() > 0).all()   # no empty keys

Gotchas and compliance constraints

• The Full refset is cumulative — the active filter is the whole game. ExtendedMapFull contains every historical state of every row, so skipping active == "1" inflates the table with superseded and retired mappings that will resolve to wrong or withdrawn ICD-10 targets. Filter on load, never store the flag, and let the row-count gate against the manifest catch a filter that regressed. If you need current-state-only semantics without the history, the Snapshot refset is an alternative, but the Full file plus an explicit active filter is the reproducible, auditable choice.
• Pin the version into the row, not just the filename. Historical records must resolve against the crosswalk generation that was active when they were authored. Stamping snomed_version on every row and keying the lookup by version|concept is what stops a March release from retroactively rewriting last year’s assignments. A biannual refresh adds a new parquet partition and a new set of index rows — a new generation — and never mutates or deletes an existing one. The old generation stays byte-for-byte reproducible for any future audit.
• The crosswalk is reference data, not PHI — but keep the boundary clean. The der2_iisssccRefset_ExtendedMapFull file contains only public terminology and licensed code mappings; no patient information touches this build, so the table itself carries no HIPAA obligation and can live in ordinary artifact storage. The compliance line sits one step downstream: the output of applying this crosswalk to a patient record — which SNOMED source, which crosswalk version, which row produced a submitted ICD-10 code — is auditable provenance and must be captured on the mapping side. Building the crosswalk deterministically and stamping an immutable snomed_version is what makes that downstream provenance reconstructable years later.

Build checklist

• Read as strings — dtype=str and keep_default_na=False so concept ids and empty mapTarget values survive intact.
• Active filter applied — only active == "1" rows are stored; the flag itself is dropped.
• Version stamped — every row carries a synthesized snomed_version; the lookup key is version|concept.
• Sorted for the resolver — rows ordered by (concept_id, map_group, map_priority) on an integer view, not lexically.
• Row count reconciled — build fails if the active-row total differs from the release manifest.
• Terminal rows present — every concept group ends in a TRUE/OTHERWISE fall-through.
• Refresh is additive — a new release adds a generation; existing partitions are immutable.

Related

• SNOMED CT to ICD-10 mapping strategies — the parent guide: how the decision-table resolver evaluates the rows this table stores.
• Mapping LOINC codes to clinical lab results — the sibling lab-vocabulary crosswalk with the same load, version-pin, and persistence discipline.
• FHIR terminology server integration — the $translate fallback for concepts a locally built crosswalk cannot resolve.
• FHIR & HL7 v2 Standards Architecture for Clinical ETL — the parent architecture overview.