Duplicate Records

September 2026 · 15 min read

Deduplication begins by deciding what one observation represents. Two rows can describe the same event, different events involving the same customer, or conflicting accounts of one event. Those cases need different decisions.

We will follow a small shipment log from candidate detection to an auditable result. The important output is not just a smaller table: it is a record of what was retained, what was confirmed as a copy, and what still needs investigation.

Separate record identity, event identity and entity identity

In this example, row_id identifies an imported record, event_id identifies a shipment event, and customer_id identifies the customer. One customer can have several events; one event can appear in several imported records.

Rows r1 and r2 agree on event E10 and its business fields. Row r3 describes a later event E11 for the same customer. Rows r4 and r5 both claim event E12 but disagree on the item count. Rows r7 and r8 have no event ID, so their identical fields alone do not establish that only one shipment occurred.

See what a duplicate key actually discards

A duplicate key is the set of fields compared for equality. The demonstration below applies the mechanical rule “keep the first row for each key.” It deliberately shows why that rule is insufficient as a complete cleaning policy.

Including row_id in an all-column comparison would make every imported row distinct. Comparing customer ID alone would merge legitimate events. Even a trustworthy event key requires a payload check before resolving repeated rows.

Separate confirmed copies from conflicts

Assume the source contract says each nonblank event ID identifies exactly one shipment, and required business fields have been validated. For this dataset, use the following policy:

• Same event, agreeing fields: retain one representative and map the other records to it. Here we choose the smallest row ID to make the result deterministic.
• Same event, conflicting fields: hold the whole group for review. An ingestion timestamp alone does not prove which item count is correct.
• Missing event ID: retain the records in a review set. Do not treat every missing key as the same event.

The representative-selection rule is a bookkeeping choice when the business fields agree. Resolving a conflict requires stronger evidence, such as a documented revision number or a correction from the source. Conflicting labels deserve the same investigation; majority voting among copied rows can amplify a bad label.

Python: return decisions as well as the clean rows

duplicated(..., keep=False) marks every member of an equal-key group. drop_duplicates selects a representative according to the specified order; it does not establish real-world identity. Use these operations within an explicit policy.

import pandas as pd

rows = [
    ("r1", "E10", "C01", "2026-09-01", 10),
    ("r2", "E10", "C01", "2026-09-01", 10),
    ("r3", "E11", "C01", "2026-09-02", 10),
    ("r4", "E12", "C02", "2026-09-01", 20),
    ("r5", "E12", "C02", "2026-09-01", 25),
    ("r6", "E13", "C03", "2026-09-03", 10),
    ("r7", "", "C04", "2026-09-04", 30),
    ("r8", "", "C04", "2026-09-04", 30),
]
df = pd.DataFrame(rows, columns=["row_id", "event_id", "customer_id", "day", "items"])

def resolve_events(frame):
    if frame["row_id"].isna().any() or not frame["row_id"].is_unique:
        raise ValueError("unique non-null import row IDs required")
    decisions = {}
    missing = frame["event_id"].isna() | frame["event_id"].eq("")
    for row_id in frame.loc[missing, "row_id"]:
        decisions[row_id] = "review_missing_key"
    payload = ["customer_id", "day", "items"]
    for event_id, group in frame.loc[~missing].groupby("event_id", sort=True):
        group = group.sort_values("row_id")
        if len(group[payload].drop_duplicates()) != 1:
            for row_id in group["row_id"]:
                decisions[row_id] = "review_conflict"
        else:
            representative = group.iloc[0]["row_id"]
            for row_id in group["row_id"]:
                decisions[row_id] = ("keep" if row_id == representative
                                     else "duplicate_of:" + representative)
    return decisions

decisions = resolve_events(df)
clean = df[df["row_id"].map(decisions).eq("keep")]
print(clean["row_id"].tolist())
for row_id in sorted(decisions):
    print(row_id, decisions[row_id])

['r1', 'r3', 'r6']
r1 keep
r2 duplicate_of:r1
r3 keep
r4 review_conflict
r5 review_conflict
r6 keep
r7 review_missing_key
r8 review_missing_key

Keep the review table and duplicate-to-representative mapping with the output. If another table is joined on a key expected to be unique, declare that expectation with a join check such as validate="many_to_one" in pandas.merge. A duplicate key on the right can otherwise multiply rows during the join.

Similarity creates candidates, not identity

When a stable identifier is absent or unreliable, record linkage compares several fields to propose matches. Spelling similarity can help find candidates, but matching names or addresses alone may merge different entities. Evaluate proposed matches against reviewed examples and consider the cost of false merges as well as missed matches.

Blocking restricts which pairs are compared, for example to records sharing a postal-code prefix. Eight records have 28 unordered pairs. Dividing them into disjoint blocks of sizes 3, 3 and 2 leaves only 3 + 3 + 1 = 7 within-block comparisons. A true match across those blocks would never reach the matcher; candidate coverage matters as well as speed.

Connected-components clustering groups everything linked by a path. It does not require every pair in the group to pass the matching threshold. Review the resulting clusters, especially links that bridge otherwise separate groups. A clustering rule is a further decision beyond scoring individual pairs.

Make the evaluation unit match the prediction task

Copies of the same observation in training and test data can make evaluation overly optimistic. Use reliable event identity and provenance when constructing splits. If the task is prediction for new customers, put each resolved customer in one group split.

If the task is predicting future events for existing customers, legitimate repeated events may belong in a time-based evaluation. Removing every repeated customer would change that task. Fit any learned matching model or tuned similarity threshold using permitted training and validation evidence; do not choose merges from test-label agreement.

Optional C++: resolve known event groups with the same policy

This implementation uses validated customer, date and item fields. It returns one decision per unique imported row ID, preserving conflicts and missing event keys for review.

#include <algorithm>
#include <iostream>
#include <map>
#include <set>
#include <stdexcept>
#include <string>
#include <tuple>
#include <vector>
struct Row { std::string id,event,customer,day; int items; };
std::map<std::string,std::string> resolve_events(const std::vector<Row>& rows) {
    std::set<std::string> ids;
    std::map<std::string,std::vector<Row>> groups;
    std::map<std::string,std::string> decisions;
    for (const auto& row:rows) {
        if (!ids.insert(row.id).second) throw std::invalid_argument("repeated row ID");
        if (row.event.empty()) decisions[row.id]="review_missing_key";
        else groups[row.event].push_back(row);
    }
    auto payload=[](const Row& r) { return std::tie(r.customer,r.day,r.items); };
    for (auto& entry:groups) {
        auto& group=entry.second;
        std::sort(group.begin(),group.end(),[](const Row& a,const Row& b){return a.id<b.id;});
        bool conflict=false;
        for (const auto& row:group) conflict=conflict || payload(row)!=payload(group.front());
        for (const auto& row:group)
            decisions[row.id]=conflict?"review_conflict":
                (row.id==group.front().id?"keep":"duplicate_of:"+group.front().id);
    }
    return decisions;
}
int main() {
    std::vector<Row> rows={
        {"r1","E10","C01","2026-09-01",10}, {"r2","E10","C01","2026-09-01",10},
        {"r3","E11","C01","2026-09-02",10}, {"r4","E12","C02","2026-09-01",20},
        {"r5","E12","C02","2026-09-01",25}, {"r6","E13","C03","2026-09-03",10},
        {"r7","","C04","2026-09-04",30}, {"r8","","C04","2026-09-04",30}
    };
    for (const auto& entry:resolve_events(rows))
        std::cout << entry.first << " " << entry.second << "\n";
}

The Python and C++ versions produce the same eight decisions. Reordering the inputs does not change the selected representative because the policy sorts by row ID. That determinism makes an audit reproducible; correctness still depends on the event-identity contract.

References and further reading

• Pandas: DataFrame.duplicated — subset keys and keep=False for inspecting every record in a duplicate group.
• Pandas: DataFrame.drop_duplicates — representative selection, input ordering and ignored index values.
• Pandas: merge — join validation and the consequences of repeated join keys.
• Splink: blocking — candidate generation, comparison counts and the tradeoff between coverage and computational cost.
• Splink: clustering — turning pairwise match predictions into connected components.
• Scikit-learn: grouped cross-validation — evaluating generalization when several observations belong to the same group.