Remove Duplicates Without Losing Important Records: A Checklist

Remove Duplicates Without Losing Important Records: A Checklist

Overview

A concise checklist to safely remove duplicate records while preserving unique or important data. Use this when cleaning datasets in spreadsheets, databases, or CSV files.

Pre-cleaning

1. Backup: Make a copy of the original file or export a backup snapshot.
2. Scope: Define which fields determine a duplicate (e.g., email, ID, name+date).
3. Objective: Decide whether to fully delete duplicates or consolidate them (merge fields).

Detection

4. Identify exact duplicates: Find rows where all fields match.
5. Identify partial duplicates: Find rows matching on key fields but differing elsewhere.
6. Flag vs. delete: Mark suspected duplicates first instead of deleting immediately.

Prioritization rules (choose one)

7. Keep newest: Preserve the row with the latest timestamp.
8. Keep most complete: Preserve the row with fewest empty fields.
9. Keep highest priority source: Preserve rows from trusted sources over others.
10. Keep lowest ID: Preserve the record with the smallest primary key if IDs are meaningful.

Consolidation

11. Field-level merge: For duplicates with complementary data, create a merged record (e.g., prefer non-empty fields).
12. Audit trail: Record which records were merged and their original IDs.

Validation

13. Spot-check: Manually review a random sample of changes.
14. Integrity checks: Run consistency checks (unique constraints, referential integrity).
15. Compare counts: Verify expected reduction in row count and that key metrics (e.g., total revenue) remain reasonable.

Automated tools & commands

16. Excel/Sheets: Use Remove Duplicates, UNIQUE(), FILTER(), or conditional formatting to highlight duplicates.
17. SQL: Use GROUP BY, ROW_NUMBER() OVER (PARTITION BY …) to identify and delete/keep rows.
18. Python/pandas: Use df.dropduplicates(subset=[…], keep=…) or groupby + agg to consolidate.

sql
 ffON2NH02oMAcqyoh2UU MQCbz04ET5EljRmK3YpQ CPXAhl7VTkj2dHDyAYAf” data-copycode=“true” role=“button” aria-label=“Copy Code”>Copy CodeCopied
WITH ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY updatedat DESC) AS rn   FROM users )
DELETE FROM users WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

python
 ffON2NH02oMAcqyoh2UU MQCbz04ET5EljRmK3YpQ CPXAhl7VTkj2dHDyAYAf” data-copycode=“true” role=“button” aria-label=“Copy Code”>Copy CodeCopied
df[‘non_nulls’] = df.notnull().sum(axis=1)
df = df.sort_values(‘non_nulls’, ascending=False).drop_duplicates(subset=[‘email’], keep=‘first’)