← pandas: reliable tabular analytics Progress & review ↗

Typorithm · medium

Merging with cardinality, null and provenance checks

pythonpandasnumerical-computingindependent-practice

Saved on this device

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

Step 1 of 4 · intro card

Merging with cardinality, null and provenance checks

Concept and contract

Enrich roles without multiplying them or silently matching missing keys.

A join can be numerically correct yet count the wrong population when keys repeat on both sides. A role-to-company lookup normally expects many roles to one company row. Express that expectation with validate="many_to_one" instead of checking the result only after multiplication occurs. State how unmatched company keys should appear and whether missing join keys are allowed. Pandas can match null keys to each other, unlike an ordinary SQL equality join.

This contract rejects missing company keys, permits unmatched nonmissing role keys and retains those roles through a left join. A provenance flag shows which rows actually found a company record.

Mechanism and worked example

Construct typed role and company tables.

Construct typed role and company tables. Require unique nonmissing role IDs, nonmissing company IDs on both sides and unique company-dimension IDs. Merge with an explicit on column, left join, cardinality validation and indicator. Convert the indicator to a nullable matched flag and project a stable output schema. Unmatched names remain missing; they do not become an invented company.

Trace two roles at one known company and a third at an unknown company. The output contains exactly three roles, two matched flags and one missing name. Two dimension rows for the same company must be rejected rather than produce six role rows.

Complete worked example

python

import pandas as pd

def inspect_example(roles, companies):
    left = pd.DataFrame(roles, columns=["id", "company_id"]).astype("string")
    right = pd.DataFrame(companies, columns=["company_id", "name"]).astype("string")
    if left["id"].isna().any() or left["id"].duplicated().any() or left["company_id"].isna().any() or right["company_id"].isna().any() or right["company_id"].duplicated().any():
        raise ValueError("ambiguous or missing identity")
    joined = left.merge(right, on="company_id", how="left", validate="many_to_one", indicator=True)
    joined["matched"] = joined["_merge"].eq("both").astype("boolean")
    return joined[["id", "name", "matched"]].reset_index(drop=True)

Reasoning: Construct typed role and company tables. Require unique nonmissing role IDs, nonmissing company IDs on both sides and unique company-dimension IDs. Merge with an explicit on column, left join, cardinality validation and indicator. Convert the indicator to a nullable matched flag and project a stable output schema. Unmatched names remain missing; they do not become an invented company. Trace two roles at one known company and a third at an unknown company. The output contains exactly three roles, two matched flags and one missing name. Two dimension rows for the same company must be rejected rather than produce six role rows.

Open in local Python playground ↗

Focused decision: context is in the complete example

python

joined = left.merge(right, on="company_id", how="left",
                    validate="many_to_one", indicator=True)
matched = joined["_merge"].eq("both")

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 counterexample changes the left join to an inner join and silently loses unknown-company roles.

The controlled counterexample changes the left join to an inner join and silently loses unknown-company roles. Another dangerous mistake omits cardinality validation when a dimension has duplicate keys. A third permits null keys, allowing pandas to match unavailable company identifiers together and attach a name without actual identity evidence. These defects require different fixtures; a single happy-path join cannot detect them all.

Do not rely on overlapping column names to infer join keys. Explicit on communicates which business relationship is intended. Name collisions in non-key columns should also receive an explicit suffix or projection policy rather than accidental overwriting.

Controlled mistake: inspect the failing table contract

python

import pandas as pd

def inspect_example(roles, companies):
    left = pd.DataFrame(roles, columns=["id", "company_id"]).astype("string")
    right = pd.DataFrame(companies, columns=["company_id", "name"]).astype("string")
    if left["id"].isna().any() or left["id"].duplicated().any() or left["company_id"].isna().any() or right["company_id"].isna().any() or right["company_id"].duplicated().any():
        raise ValueError("ambiguous or missing identity")
    joined = left.merge(right, on="company_id", how="inner", validate="many_to_one", indicator=True)
    joined["matched"] = joined["_merge"].eq("both").astype("boolean")
    return joined[["id", "name", "matched"]].reset_index(drop=True)

Reasoning: The controlled counterexample changes the left join to an inner join and silently loses unknown-company roles. Another dangerous mistake omits cardinality validation when a dimension has duplicate keys. A third permits null keys, allowing pandas to match unavailable company identifiers together and attach a name without actual identity evidence. These defects require different fixtures; a single happy-path join cannot detect them all. Do not rely on overlapping column names to infer join keys. Explicit on communicates which business relationship is intended. Name collisions in non-key columns should also receive an explicit suffix or projection policy rather than accidental overwriting.

Open in local Python playground ↗

Independent practice

Add a strict matched-only presentation by filtering the indicator after the validated left join.

Add a strict matched-only presentation by filtering the indicator after the validated left join. Preserve the same key validation, cardinality check and output columns. This intentionally changes the visible population, so make the new rule explicit instead of simply substituting an undocumented inner join and discarding the provenance reasoning.

Predict the known and unknown-company rows. The changed result excludes only unmatched roles. Duplicate dimension keys and missing join keys remain errors before any filtering occurs. This keeps the exercise small while reinforcing that a display filter does not excuse accepting ambiguous or invalid relationship data.

Step 1: Present only matched roles after performing the validated join. 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(roles, companies):
    left = pd.DataFrame(roles, columns=["id", "company_id"]).astype("string")
    right = pd.DataFrame(companies, columns=["company_id", "name"]).astype("string")
    if left["id"].isna().any() or left["id"].duplicated().any() or left["company_id"].isna().any() or right["company_id"].isna().any() or right["company_id"].duplicated().any():
        raise ValueError("ambiguous or missing identity")
    joined = left.merge(right, on="company_id", how="left", validate="many_to_one", indicator=True)
    joined["matched"] = joined["_merge"].eq("both").astype("boolean")
    return joined[["id", "name", "matched"]].reset_index(drop=True)

# Independent task: Present only matched roles after performing the validated join.

Reasoning: Present only matched roles after performing the validated join.

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 filters _merge equal to both before computing the final projection.

The solution filters _merge equal to both before computing the final projection. Exact expected frames test unmatched roles, several roles per company, invalid dimension duplication and missing keys. The output is compared with its declared nullable text and boolean types, not only its length. The original inputs remain unchanged.

The example deliberately validates dimension uniqueness before requesting validate as a second documented boundary. That makes invalid metadata produce a consistent ValueError for learners, while the merge argument records the relational invariant at the operation itself. In a larger pipeline, preserve unmatched and rejected counts for reconciliation even if a particular screen shows only matched rows.

Practice solution and acceptance checks

python

import pandas as pd

def inspect_example(roles, companies):
    left = pd.DataFrame(roles, columns=["id", "company_id"]).astype("string")
    right = pd.DataFrame(companies, columns=["company_id", "name"]).astype("string")
    if left["id"].isna().any() or left["id"].duplicated().any() or left["company_id"].isna().any() or right["company_id"].isna().any() or right["company_id"].duplicated().any():
        raise ValueError("ambiguous or missing identity")
    joined = left.merge(right, on="company_id", how="left", validate="many_to_one", indicator=True)
    joined = joined.loc[joined["_merge"].eq("both")]
    joined["matched"] = joined["_merge"].eq("both").astype("boolean")
    return joined[["id", "name", "matched"]].reset_index(drop=True)

Reasoning: The solution filters _merge equal to both before computing the final projection. Exact expected frames test unmatched roles, several roles per company, invalid dimension duplication and missing keys. The output is compared with its declared nullable text and boolean types, not only its length. The original inputs remain unchanged. The example deliberately validates dimension uniqueness before requesting validate as a second documented boundary. That makes invalid metadata produce a consistent ValueError for learners, while the merge argument records the relational invariant at the operation itself. In a larger pipeline, preserve unmatched and rejected counts for reconciliation even if a particular screen shows only matched rows.

Open in local Python playground ↗

Interview reasoning and next step

Explain one-to-one, one-to-many, many-to-one and many-to-many joins with concrete row counts.

Explain one-to-one, one-to-many, many-to-one and many-to-many joins with concrete row counts. Describe how pandas null matching differs from SQL equality and why that matters for identity. Show how indicator helps reconcile losses and enrichment coverage. Distinguish a valid unmatched role from an invalid ambiguous company mapping.

Type the short validated merge on the PC and recall its cardinality argument. In the app, reason about retained roles and matched flags. Next, aggregate the retained population with separate counts for roles and observed measurements so a mean is not mistaken for complete coverage.

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

Enrich roles without multiplying them or silently matching missing keys.