APPLYDJINN
← pandas: reliable tabular analyticsProgress & review ↗

Typorithm · medium

Deterministic deduplication of repeated observations

pythonpandasnumerical-computingindependent-practice
Saved on this device
Full lesson · 4 learning steps Open any exercise for focused practice
1

Step 1 of 4 · intro card

Deterministic deduplication of repeated observations

0%

Concept and contract

Choose one role observation with explicit chronology and a tie-breaker.

Repeated observations of a role are not automatically separate vacancies. Deduplication needs an identity key and a rule for which observation represents the current view. Input order is not a reliable chronology because different feeds and retries can arrive in different orders. This lesson chooses the newest timestamp, breaking equal timestamps with an explicitly supplied sequence number, then presents one row per ID in deterministic ID order.

The function returns a derived view only. It does not delete historical records, rewrite SEO URLs or claim that matching titles prove identity. The observation sequence is trusted fixture metadata, not an inferred ranking of publishers.

Mechanism and worked example

Validate nonblank string IDs, nonnegative integer sequences and uniqueness of each ID-sequence pair.

Validate nonblank string IDs, nonnegative integer sequences and uniqueness of each ID-sequence pair. Parse updated timestamps as UTC using an explicit ISO8601 format. Sort by ID, timestamp and sequence, then drop duplicate IDs while retaining the last row. Finally project ID and nullable score and reset the presentation index. A stable sort is requested so the rule remains understandable even before every tie is eliminated.

Trace two observations of a and one of b, supplied out of time order. The newest a must win regardless of which row arrived last in the list. An equal-time a observation uses the larger sequence value.

Complete worked example

python

import pandas as pd

def inspect_example(rows):
    if any(type(r.get("id")) is not str or not r["id"].strip() or type(r.get("seq")) is not int or r["seq"] < 0 for r in rows):
        raise ValueError("invalid observation identity")
    frame = pd.DataFrame(rows, columns=["id", "updated", "seq", "score"])
    if frame.duplicated(["id", "seq"]).any():
        raise ValueError("duplicate observation sequence")
    frame = frame.astype({"id": "string", "score": "Float64"})
    frame["updated"] = pd.to_datetime(frame["updated"], format="ISO8601", utc=True, errors="raise")
    ordered = frame.sort_values(["id", "updated", "seq"], kind="stable")
    latest = ordered.drop_duplicates("id", keep="last")
    return latest[["id", "score"]].reset_index(drop=True)
Reasoning
Validate nonblank string IDs, nonnegative integer sequences and uniqueness of each ID-sequence pair. Parse updated timestamps as UTC using an explicit ISO8601 format. Sort by ID, timestamp and sequence, then drop duplicate IDs while retaining the last row. Finally project ID and nullable score and reset the presentation index. A stable sort is requested so the rule remains understandable even before every tie is eliminated. Trace two observations of a and one of b, supplied out of time order. The newest a must win regardless of which row arrived last in the list. An equal-time a observation uses the larger sequence value.
Open in local Python playground ↗

Focused decision: context is in the complete example

python

ordered = frame.sort_values(["id", "updated", "seq"], kind="stable")
latest = ordered.drop_duplicates("id", keep="last")
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 drops duplicates in input order.

The controlled mistake drops duplicates in input order. That can preserve an older observation merely because it arrived later, producing a stale score without any visible exception. Another mistake drops duplicate complete rows rather than duplicate IDs, leaving several observations of the same role when one field changed. A third assumes that sort stability is itself a business tie-breaker. Stability only preserves input order for equal keys.

An invalid timestamp must not quietly become the newest observation or disappear through coercion. Reject it under this strict contract and inspect it separately in a real importer's rejected-record path.

Controlled mistake: inspect the failing table contract

python

import pandas as pd

def inspect_example(rows):
    if any(type(r.get("id")) is not str or not r["id"].strip() or type(r.get("seq")) is not int or r["seq"] < 0 for r in rows):
        raise ValueError("invalid observation identity")
    frame = pd.DataFrame(rows, columns=["id", "updated", "seq", "score"])
    if frame.duplicated(["id", "seq"]).any():
        raise ValueError("duplicate observation sequence")
    frame = frame.astype({"id": "string", "score": "Float64"})
    frame["updated"] = pd.to_datetime(frame["updated"], format="ISO8601", utc=True, errors="raise")
    ordered = frame
    latest = ordered.drop_duplicates("id", keep="last")
    return latest[["id", "score"]].reset_index(drop=True)
Reasoning
The controlled mistake drops duplicates in input order. That can preserve an older observation merely because it arrived later, producing a stale score without any visible exception. Another mistake drops duplicate complete rows rather than duplicate IDs, leaving several observations of the same role when one field changed. A third assumes that sort stability is itself a business tie-breaker. Stability only preserves input order for equal keys. An invalid timestamp must not quietly become the newest observation or disappear through coercion. Reject it under this strict contract and inspect it separately in a real importer's rejected-record path.
Open in local Python playground ↗

Independent practice

Change the retained view to the earliest observation per role.

Change the retained view to the earliest observation per role. Keep the same chronological sort and sequence tie-breaker, but retain the first sorted observation. Preserve the final ID ordering and output schema. The task is one declared selection-policy change, not a request to erase or rewrite the input history.

Predict a role with two different timestamps and another role with an equal-time tie. The earliest policy takes the smaller sequence at an equal timestamp because that is the defined total order. Compare these predictions with the latest-policy worked example before editing the one keep argument.

Step 1: Return the earliest observation per ID using the same explicit sort and tie-breaker. 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("id")) is not str or not r["id"].strip() or type(r.get("seq")) is not int or r["seq"] < 0 for r in rows):
        raise ValueError("invalid observation identity")
    frame = pd.DataFrame(rows, columns=["id", "updated", "seq", "score"])
    if frame.duplicated(["id", "seq"]).any():
        raise ValueError("duplicate observation sequence")
    frame = frame.astype({"id": "string", "score": "Float64"})
    frame["updated"] = pd.to_datetime(frame["updated"], format="ISO8601", utc=True, errors="raise")
    ordered = frame.sort_values(["id", "updated", "seq"], kind="stable")
    latest = ordered.drop_duplicates("id", keep="last")
    return latest[["id", "score"]].reset_index(drop=True)

# Independent task: Return the earliest observation per ID using the same explicit sort and tie-breaker.
Reasoning
Return the earliest observation per ID using the same explicit sort and tie-breaker.
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 keep="last" to keep="first" after the explicit sort.

The solution changes keep="last" to keep="first" after the explicit sort. Tests supply unsorted observations, equal timestamps, empty input and malformed identity metadata. Exact retained IDs and scores make stale selection visible; output length alone is insufficient. The lab also verifies that the original list of observations remains unchanged.

A real system may choose a different authority rule, such as an official source over a mirror. That rule belongs in the sort keys and provenance contract, not in an accidental ingestion order. Keep the selected view separate from retained observation history so a future reconciliation rule can be audited without losing previous evidence.

Practice solution and acceptance checks

python

import pandas as pd

def inspect_example(rows):
    if any(type(r.get("id")) is not str or not r["id"].strip() or type(r.get("seq")) is not int or r["seq"] < 0 for r in rows):
        raise ValueError("invalid observation identity")
    frame = pd.DataFrame(rows, columns=["id", "updated", "seq", "score"])
    if frame.duplicated(["id", "seq"]).any():
        raise ValueError("duplicate observation sequence")
    frame = frame.astype({"id": "string", "score": "Float64"})
    frame["updated"] = pd.to_datetime(frame["updated"], format="ISO8601", utc=True, errors="raise")
    ordered = frame.sort_values(["id", "updated", "seq"], kind="stable")
    latest = ordered.drop_duplicates("id", keep="first")
    return latest[["id", "score"]].reset_index(drop=True)
Reasoning
The solution changes keep="last" to keep="first" after the explicit sort. Tests supply unsorted observations, equal timestamps, empty input and malformed identity metadata. Exact retained IDs and scores make stale selection visible; output length alone is insufficient. The lab also verifies that the original list of observations remains unchanged. A real system may choose a different authority rule, such as an official source over a mirror. That rule belongs in the sort keys and provenance contract, not in an accidental ingestion order. Keep the selected view separate from retained observation history so a future reconciliation rule can be audited without losing previous evidence.
Open in local Python playground ↗

Interview reasoning and next step

Explain why drop_duplicates is incomplete without a key and retention rule.

Explain why drop_duplicates is incomplete without a key and retention rule. Distinguish entity identity, observation identity, event time and ingestion order. Show how an equal-time tie becomes deterministic, and describe which contradictory metadata should be rejected instead of hidden by a sort. Explain why deduplicating a view is different from deleting history.

On the PC, type the ordered deduplication and recall the keep policy. In the app, choose which observation survives the declared rule. Next, join role observations to a company dimension while validating that the join cannot multiply roles unexpectedly.

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

Choose one role observation with explicit chronology and a tie-breaker.