← pandas: reliable tabular analytics Progress & review ↗

Typorithm · medium

Canonical keys without merging distinct external IDs

pythonpandasnumerical-computingindependent-practice

Saved on this device

Full lesson · 4 learning steps Open any exercise for focused practice

Step 1 of 4 · intro card

Canonical keys without merging distinct external IDs

Concept and contract

Normalize a documented company key while preserving opaque job identity.

A durable key identifies an entity across row order, display changes and feed refreshes. A company label alone is not necessarily unique, and a job title is rarely a safe vacancy identifier. This lesson uses a composite key: a documented case-insensitive company code plus an opaque case-sensitive job ID. The normalization rules differ because the two fields have different meanings. Whitespace cleanup is not a license to lowercase every identifier.

The contract accepts string fields, trims both, normalizes company case and rejects blank or duplicate composite keys. It does not infer a match from similar titles or addresses. Ambiguous identity is an error rather than a convenient merge.

Mechanism and worked example

Validate the original field types before converting to nullable string columns.

Validate the original field types before converting to nullable string columns. Otherwise an integer identifier might be silently converted after leading zeros were already lost. Strip company and job_id separately, normalize only company, then detect empty values and duplicated composite keys. Return the normalized pair in input order. Selecting the explicit two-column schema also works for an empty batch.

Trace company Acme with job ABC and company acme with job abc. These are two distinct jobs because the opaque ID preserves case. Company Acme with ABC repeated under different company capitalization is a duplicate under this declared namespace.

Complete worked example

python

import pandas as pd

def inspect_example(rows):
    if any(type(r.get("company")) is not str or type(r.get("job_id")) is not str for r in rows):
        raise ValueError("string keys required")
    frame = pd.DataFrame(rows, columns=["company", "job_id"]).astype("string")
    frame["company"] = frame["company"].str.strip().str.lower()
    frame["job_id"] = frame["job_id"].str.strip()
    if frame.eq("").any().any() or frame.duplicated(["company", "job_id"]).any():
        raise ValueError("blank or duplicate key")
    return frame

Reasoning: Validate the original field types before converting to nullable string columns. Otherwise an integer identifier might be silently converted after leading zeros were already lost. Strip company and job_id separately, normalize only company, then detect empty values and duplicated composite keys. Return the normalized pair in input order. Selecting the explicit two-column schema also works for an empty batch. Trace company Acme with job ABC and company acme with job abc. These are two distinct jobs because the opaque ID preserves case. Company Acme with ABC repeated under different company capitalization is a duplicate under this declared namespace.

Open in local Python playground ↗

Focused decision: context is in the complete example

python

frame["company"] = frame["company"].str.strip().str.lower()
frame["job_id"] = frame["job_id"].str.strip()
duplicates = frame.duplicated(["company", "job_id"])

Focused decision: Type this short table decision after reading its complete context. The complete imports, table construction, validation, and result checks remain in the worked example. Typing records practice; only the downloadable computer lab executes Python.

Counterexamples and common mistakes

The controlled mistake lowercases job_id too.

The controlled mistake lowercases job_id too. It collapses distinct case-sensitive identities and can turn a valid two-role input into a duplicate error. Another tempting shortcut concatenates strings without a separator or structured encoding: pairs such as ab plus c and a plus bc can collide. Keeping two named columns avoids that ambiguity during pandas operations.

Do not turn an absent ID into a generated row number and then call the result canonical. A row ordinal changes when sorting or filtering. If a source has no stable identifier, a separate, documented matching process with uncertainty and provenance is needed.

Controlled mistake: inspect the failing table contract

python

import pandas as pd

def inspect_example(rows):
    if any(type(r.get("company")) is not str or type(r.get("job_id")) is not str for r in rows):
        raise ValueError("string keys required")
    frame = pd.DataFrame(rows, columns=["company", "job_id"]).astype("string")
    frame["company"] = frame["company"].str.strip().str.lower()
    frame["job_id"] = frame["job_id"].str.strip().str.lower()
    if frame.eq("").any().any() or frame.duplicated(["company", "job_id"]).any():
        raise ValueError("blank or duplicate key")
    return frame

Reasoning: The controlled mistake lowercases job_id too. It collapses distinct case-sensitive identities and can turn a valid two-role input into a duplicate error. Another tempting shortcut concatenates strings without a separator or structured encoding: pairs such as ab plus c and a plus bc can collide. Keeping two named columns avoids that ambiguity during pandas operations. Do not turn an absent ID into a generated row number and then call the result canonical. A row ordinal changes when sorting or filtering. If a source has no stable identifier, a separate, documented matching process with uncertainty and provenance is needed.

Open in local Python playground ↗

Independent practice

Change the company display convention from lowercase to uppercase.

Change the company display convention from lowercase to uppercase. Preserve case-sensitive job IDs, trimming, duplicate detection and input order. The namespace remains case-insensitive because every company value is normalized by the same rule; only its chosen canonical presentation changes.

Predict how Acme, ACME and whitespace around acme behave. Then compare ABC with abc in the job field. The task should preserve that distinction. Do not implement the requested company change by normalizing every text column together, because the explicit difference between business fields is the central guarantee of this lesson.

Step 1: Use uppercase canonical company codes while preserving case-sensitive job IDs. Step 2: Retain the working imports, input validation and table schemas. Change the stated policy, run the supplied practice cases, then explain the newly accepted or rejected boundary.

Practice starter: solve before reading the solution

python

import pandas as pd

def inspect_example(rows):
    if any(type(r.get("company")) is not str or type(r.get("job_id")) is not str for r in rows):
        raise ValueError("string keys required")
    frame = pd.DataFrame(rows, columns=["company", "job_id"]).astype("string")
    frame["company"] = frame["company"].str.strip().str.lower()
    frame["job_id"] = frame["job_id"].str.strip()
    if frame.eq("").any().any() or frame.duplicated(["company", "job_id"]).any():
        raise ValueError("blank or duplicate key")
    return frame

# Independent task: Use uppercase canonical company codes while preserving case-sensitive job IDs.

Reasoning: Use uppercase canonical company codes while preserving case-sensitive job IDs.

Open in local Python playground ↗

Hint 1: Find the one field or branch that owns this policy.
Hint 2: Compare the first changed acceptance case with the retained baseline.

Solution, tests and trade-offs

The solution changes only the company case method.

The solution changes only the company case method. Exact table assertions verify normalized keys, ordered rows and string dtypes. Duplicate composite pairs must still fail even when the original company spellings differ. Numeric, missing and blank identifiers remain invalid, while an empty input batch retains the typed schema.

The tests also contain two distinct job IDs differing only in case. This catches over-normalization rather than merely checking that a duplicate was rejected somewhere. In production, canonicalization versions may require reconciliation with already stored keys; this exercise teaches a local rule and does not authorize rewriting historical job URLs or deleting observations.

Practice solution and acceptance checks

python

import pandas as pd

def inspect_example(rows):
    if any(type(r.get("company")) is not str or type(r.get("job_id")) is not str for r in rows):
        raise ValueError("string keys required")
    frame = pd.DataFrame(rows, columns=["company", "job_id"]).astype("string")
    frame["company"] = frame["company"].str.strip().str.upper()
    frame["job_id"] = frame["job_id"].str.strip()
    if frame.eq("").any().any() or frame.duplicated(["company", "job_id"]).any():
        raise ValueError("blank or duplicate key")
    return frame

Reasoning: The solution changes only the company case method. Exact table assertions verify normalized keys, ordered rows and string dtypes. Duplicate composite pairs must still fail even when the original company spellings differ. Numeric, missing and blank identifiers remain invalid, while an empty input batch retains the typed schema. The tests also contain two distinct job IDs differing only in case. This catches over-normalization rather than merely checking that a duplicate was rejected somewhere. In production, canonicalization versions may require reconciliation with already stored keys; this exercise teaches a local rule and does not authorize rewriting historical job URLs or deleting observations.

Open in local Python playground ↗

Interview reasoning and next step

Explain how a composite key differs from a display label and why normalization belongs to each field's documented semantics.

Explain how a composite key differs from a display label and why normalization belongs to each field's documented semantics. Describe the consequences of losing leading zeros, applying case folding to opaque IDs or replacing identity with a current row position. Show which collision test would catch each mistake.

On the PC, type the separate normalization steps and recall the duplicate-key subset. The app questions focus on identity rather than string trivia. Next, distinguish repeated observations of one stable role from genuinely separate roles, choosing a deterministic retained observation without throwing away historical evidence.

Optional stretch — after the core task works: Add one independently reasoned boundary case and explain its expected value before running it.

Computer practice: https://applydjinn.com/typorithm/pandas-lab-20261106.zip. Install pinned dependencies in an isolated Python environment. Run verify.py, then change the working exercise baseline and run --practice pd-keys. The real pandas tables, exact schemas, unchanged inputs, CSV parser options and owned in-memory SQL reconciliation use owned fixtures only. No production database, feed, account or paid service is used. This lab executes Python; the PC typing trainer and native reading progress do not execute it.

Normalize a documented company key while preserving opaque job identity.