Detect Duplicate and Inconsistent Records: Worked Audit

Two rows that look alike do not automatically describe the same event. This workshop states an identity rule before deciding which records are duplicates. Five fictional observations contain both a repeated record and a contradictory pair. You will distinguish those cases, retain row references and explain why selecting the first conflicting measurement would invent an unsupported answer.

Documentation boundary

Python’s collections documentation describes Counter as a mapping for counting hashable objects. The exercise uses it to count repeated normalised row tuples. Counting occurrences does not decide which observations are legitimate duplicates; the fictional identity and conflict policies are stated independently.

Choose identity and normalisation rules

One observation is identified by site and day. For this fixture, surrounding site whitespace and letter case are declared formatting differences, so a and A refer to the same site. The measurement is not part of the identity key. Repeated identical key-and-value rows are redundant copies; two different values for one key are a conflict requiring review. Day labels are already controlled in this fixture.

Predict how many records will be retained, marked duplicate and quarantined. The three categories must reconcile to five. Also predict the key that needs a source correction. Do not choose the larger value, the smaller value or the last row simply because that makes the output convenient. The stated data provides no authority for selecting between four and seven.

Work the audit without erasing lineage

from collections import Counter

raw = [(" a ", "Mon", 8), ("A", "Mon", 8),
       ("B", "Mon", 4), ("B", "Mon", 7), ("C", "Tue", 0)]
normal = [(site.strip().upper(), day.strip(), value) for site, day, value in raw]
frequency = Counter(normal)
values = {}
for site, day, value in normal:
    values.setdefault((site, day), set()).add(value)
conflict_keys = {key for key, observed in values.items() if len(observed) > 1}
kept, duplicates, conflicts, seen = [], [], [], set()
for index, row in enumerate(normal, start=1):
    if row[:2] in conflict_keys:
        conflicts.append(index)
    elif row in seen:
        duplicates.append(index)
    else:
        kept.append((index, row))
        seen.add(row)
assert frequency[("A", "Mon", 8)] == 2
assert conflicts == [3, 4]
assert duplicates == [2]
assert kept == [(1, ("A", "Mon", 8)), (5, ("C", "Tue", 0))]
assert len(kept) + len(duplicates) + len(conflicts) == len(raw)
print("kept2; duplicate1; conflicts2")

The answer retains R1 and R5, marks R2 as the redundant copy, and quarantines R3 and R4. There are two retained rows, one duplicate and two conflicts. Their counts sum to five. The frequency of the normalised A-Monday-eight tuple is two, but only one representative is retained under the explicit first-copy policy. The original five-row input is preserved.

The B-Monday pair is fundamentally different. Its key identifies one supposed observation while its values disagree. Both records remain visible in the conflict output. A later correction could establish which value is authoritative, but this exercise does not supply that evidence. A quarantine is therefore an honest intermediate result, not a failure to finish the arithmetic.

Diagnose destructive shortcuts

If you convert whole rows to a set immediately, you lose source row references and have not resolved the B-Monday disagreement. If you construct a dictionary keyed only by site and day, a later row may replace an earlier one without an audit entry. Both shortcuts can produce a smaller dataset while concealing the decisions that created it. Inspect the lineage before accepting a shorter output as cleaner.

Normalisation also needs justification. The declared site rule allows trimming and uppercase conversion here. It does not authorise changing measurement eight to seven, merging different days or treating every identifier differing in punctuation as identical. Write each allowed transformation separately. A false merge can be harder to detect than a remaining duplicate because the original distinction may disappear from the derived output.

Handover and a controlled variation

Submit the original records, the normalised key table, the three disposition lists and the reconciliation equation. Explain why a zero measurement is not missing and why a conflict is not resolved by choosing a row arbitrarily. Your output should let another learner reconstruct which original rows produced each accepted observation. That is stronger evidence than presenting only the final two-row dataset.

For an extension, change R4 from seven to four. The B-Monday conflict disappears. R3 becomes the retained representative and R4 becomes a redundant copy under the same first-copy policy. The new counts are three retained, two duplicates and zero conflicts. Record the changed categories before rerunning the code. This variation checks whether your algorithm responds to a genuine resolution rather than always quarantining repeated keys.

Questions for this exercise

Official documentation

• Counter containers

Explore the Courses overview or use Contact for general enquiries. This is an original INTIAU editorial self-study exercise; no qualification is awarded.

View the study diagram