CSV Duplicate Row Remover
How it works
Duplicate rows are a ubiquitous data quality problem originating from multi-source data merges, repeated ETL imports, form re-submissions, or report exports that include the same record multiple times. Removing duplicates is typically the second step in any data cleaning pipeline, after whitespace normalization.
**Exact vs. key-based deduplication** Exact deduplication removes rows where every column value is identical. Key-based deduplication removes rows that share the same value in one or more specified "key" columns (e.g., email address, order ID, customer ID), keeping only the first or last occurrence. Key-based deduplication is more powerful and appropriate when rows may have minor differences in non-key fields due to data entry variation.
**Keeping first vs. last** When multiple rows share a key, "keep first" retains the earliest occurrence (useful for preserving original records). "Keep last" retains the most recent occurrence (useful when updates are appended to the same file over time β the last row represents the current state). Pandas drop_duplicates(keep='first'/'last') implements this exactly.
**Case sensitivity** Duplicate detection is typically case-sensitive by default: "john@example.com" and "John@example.com" would be treated as different. For email addresses and identifiers, case-insensitive comparison (normalize to lowercase before comparing) produces better results. This tool applies case-normalization to string key columns for more accurate deduplication.
Frequently Asked Questions
- Keep first: preserves the original record, useful when the CSV is append-only and earlier rows are authoritative. Keep last: preserves the most recent state, useful when the file is built by appending updates (each row is an event and the last one represents current state). For dimension tables (customer master, product catalog), 'keep last' is typically correct. For fact tables (transactions, events), duplicates should be investigated rather than silently removed.
- Key-based deduplication compares only the selected key columns (e.g., email address or order_id), keeping one row per unique key value, even if other columns differ between duplicate rows. In pandas: df.drop_duplicates(subset=['email'], keep='first'). This is more powerful than exact deduplication because it handles cases where duplicate records have minor variations in non-key fields (different capitalization, whitespace, or timestamp).
- Near-duplicates are rows that are very similar but not exactly identical β 'John Smith' vs 'Jon Smith', '123 Main St' vs '123 Main Street'. Detecting these requires fuzzy matching, not exact comparison. Use Levenshtein distance or Jaro-Winkler similarity to identify rows where key fields are >90% similar. Python's fuzzywuzzy (or rapidfuzz) library, or Record Linkage Toolkit, implements this. This tool handles exact duplicates; fuzzy deduplication requires a more sophisticated pipeline.
- Yes β the 'keep first' or 'keep last' choice depends on row order being meaningful. If the CSV has no meaningful order (exported in arbitrary sequence), the choice between first/last is arbitrary. Sort the CSV by a timestamp or version column before deduplicating if you want the chronologically first or last occurrence.