How to Investigate Duplicate IDs Without Losing Valid Data

A repeated identifier looks like an obvious cleaning problem until two rows with the same ID contain different dates, statuses or amounts. Deleting one row may remove a valid event, while keeping both may inflate counts and totals. The safe response is not “drop duplicates.” It is a short investigation that establishes what the identifier means, classifies each repeated group and records why any row was removed.

Start by defining what one row represents

Before examining duplicates, state the dataset’s grain: the real-world thing represented by one row. A table might contain one row per employee, one row per employee per month or one row per employment event. The same employee ID has a different uniqueness requirement in each case.

Consider this example:

| employee_id | effective_date | status | department | |---|---|---|---| | E104 | 2026-01-01 | Active | Finance | | E104 | 2026-01-01 | Active | Finance | | E219 | 2026-01-01 | Active | Sales | | E219 | 2026-02-15 | Leave | Sales | | E337 | 2026-01-01 | Active | Support | | E337 | 2026-01-01 | Active | Operations |

If the grain is one row per employee, every repeated ID needs attention. If the grain is one row per employee event, the two E219 rows may both be valid because their dates and statuses differ.

Write the expected key as one or more columns. A monthly employee snapshot might use (employee_id, snapshot_month), not employee_id alone. Testing the wrong key can make legitimate history appear defective.

Also identify where the key originates. An identifier assigned by a trusted source system deserves different treatment from a name-based key assembled during a spreadsheet export. A generated row number is generally not evidence that the underlying entities are unique.

Normalize identifiers before counting them

Formatting inconsistencies can conceal duplicates. For example, E104, e104 and E104 may identify the same employee. Numeric-looking identifiers can also be damaged when software removes leading zeros.

Create a normalized comparison value without immediately overwriting the original column:

1import pandas as pd23df = pd.read_csv("employees.csv", dtype={"employee_id": "string"})4df["employee_id_check"] = (5    df["employee_id"]6      .str.strip()7      .str.upper()8)

Reading the ID as a string helps preserve values such as 00127. The temporary employee_id_check column lets you compare normalized values while retaining the source representation for investigation.

Normalization must reflect the identifier’s documented rules. Case folding is appropriate only if IDs are case-insensitive. Removing punctuation can be dangerous when AB-12 and AB12 are intentionally different identifiers. Do not apply broad transformations merely to make the duplicate count smaller.

Missing IDs require a separate decision. Multiple blank identifiers do not prove that the rows describe the same entity. Treat “missing key” and “repeated known key” as distinct quality problems.

Classify repeated groups instead of treating them alike

Use duplicated with keep=False to surface every member of a repeated group:

1duplicate_rows = df[2    df.duplicated(subset=["employee_id_check"], keep=False)3].sort_values(["employee_id_check", "effective_date"])

Then classify the groups into practical categories:

1. Exact row duplicates. Every relevant field matches. These often result from repeated exports, file concatenation or accidental copy-and-paste operations.
2. Valid repeated entities. The ID repeats because the table contains events, transactions or snapshots. These rows may satisfy the actual composite key.
3. Conflicting duplicates. The expected key matches, but one or more business fields disagree. The E337 rows in the example conflict on department.
4. Versioned records. Several rows represent revisions, and a timestamp or version field determines which record is current.
5. Formatting collisions. Distinct source strings become equal after approved normalization.

A useful conflict review compares the number of distinct values in important columns:

1conflict_summary = (2    duplicate_rows3    .groupby("employee_id_check")4    .agg(5        rows=("employee_id_check", "size"),6        department_values=("department", "nunique"),7        status_values=("status", "nunique"),8        date_values=("effective_date", "nunique")9    )10)

This summary does not decide which row is correct. It tells you which duplicate groups need a business rule or source-system check. Include fields that matter to the analysis; harmless differences in an export timestamp should not receive the same weight as conflicting payment amounts.

Choose a resolution rule for each class

Only remove rows after identifying a defensible rule.

For exact duplicates, retaining one copy may be appropriate. Specify the columns that define “exact” rather than relying on every incidental field. For versioned records, keep the row selected by an authoritative version number, update timestamp or source priority. Check whether timestamps are complete and consistently zoned before using them.

Conflicting duplicates usually require escalation or quarantine. Choosing the first row is not a resolution rule because file order can change. Choosing the last row is valid only when the ordering field reliably represents recency.

For valid repeated entities, correct the uniqueness test rather than deleting data. If an employee can have several dated events, validate the composite key:

1key_columns = ["employee_id_check", "effective_date"]2remaining_key_duplicates = df.duplicated(subset=key_columns, keep=False)

Keep an exception table for unresolved groups. It should include the key, conflicting rows, reason for review and eventual decision. This prevents uncertain records from silently entering an analysis while preserving evidence needed to resolve them.

Verify that deduplication preserved the dataset’s meaning

A successful script run is not proof that the result is correct. Compare the dataset before and after cleaning.

Record at least:

• input and output row counts;
• number of affected identifier groups;
• rows removed, retained or quarantined;
• the rule applied to each category;
• totals for important measures before and after;
• uniqueness of the intended key after resolution;
• unresolved exceptions.

Reconcile metrics at a useful level. If the dataset contains invoice amounts, compare totals by month or business unit, not only the grand total. A grand total can remain unchanged even when values are assigned to the wrong entity.

Retest the key explicitly:

1has_duplicate_keys = cleaned_df.duplicated(2    subset=["employee_id_check", "effective_date"],3    keep=False4).any()

The desired result depends on the grain. A remaining duplicate is acceptable only when the documented model allows it. Save the validation result with the cleaning rules so a later reviewer can understand what was tested.

Use this duplicate-ID investigation checklist

Before publishing the cleaned dataset, confirm that you have:

• stated the grain in plain language;
• identified the correct single-column or composite key;
• preserved the original identifier during normalization;
• separated missing IDs from repeated known IDs;
• reviewed every member of duplicate groups;
• distinguished exact, valid, conflicting and versioned records;
• avoided first-row or last-row selection without an ordering rule;
• retained unresolved conflicts in an exception table;
• reconciled important counts and measures;
• documented the final uniqueness test.

The actionable next step is to select one dataset with repeated IDs and build a small classification table before deleting anything. Once each group has a category and an explicit resolution rule, deduplication becomes a reproducible data-quality decision rather than an irreversible guess.