Find Duplicate CSV Values with Python
Resumen
Pixel shares a Python standard-library script that checks a CSV column for leading or trailing whitespace and values that match after ignoring case and extra spaces. It reports affected lines and spelling variants, helping the community catch data-quality issues before imports or comparisons fail.
DEV Community
Find whitespace and case-variant duplicates in a CSV column with Python (stdlib only)
Messy whitespace and inconsistent capitalisation are two of the most common reasons a CSV import produces duplicates or silently fails to match. blue-shirt and blue-shirt (trailing space) look identical in a spreadsheet, but a system comparing strings sees two different values. This small script checks one column of a CSV and reports two things: values with leading or trailing whitespace values that are the same once you ignore case and extra spaces, but are spelled differently It uses only Python's standard library. The script import csv, sys
from collections import defaultdict
def check(path, column):
seen = defaultdict(list)
problems = []
with open(path, newline="", encoding="utf-8-sig") as f:
reader = csv.DictReader(f)
if column not in (reader.fieldnames or []):
sys.exit(f"Column {column!r} not found. Columns: {reader.fieldnames}")
for line_no, row in enumerate(reader, start=2):
value = row[column]
if value != value.strip():
problems.append((line_no, "leading/trailing whitespace", repr(value)))
key = " ".join(value.split()).casefold()
if key:
seen[key].append((line_no, value))
for key, hits in seen.items():
variants = {v for _, v in hits}
if len(hits) > 1 and len(variants) > 1:
lines = ", ".join(str(n) for n, _ in hits)
problems.append((hits[0][0], f"same value, different spelling (lines {lines})", repr(sorted(variants))))
return sorted(problems)
if name == "main":
if len(sys.argv) != 3:
sys.exit("usage: python ws.py file.csv column")
res = check(sys.argv[1], sys.argv[2])
for line, kind, detail in res:
print(f"line {line}: {kind}: {detail}")
print(f"{len(res)} problem(s) found")
Example Given this file: handle,title
blue-shirt,Blue Shirt
blue-shirt ,Blue shirt
red-hat,Red Hat
Red-Hat,Red hat
socks,Socks
Running python ws.py t.csv handle prints: line 2: same value, different spelling (lines 2, 3): ['blue-shirt', 'blue-shirt ']
line 3: leading/trailing whitespace: 'blue-shirt '
line 4: same value, different spelling (lines 4, 5): ['Red-Hat', 'red-hat']
3 problem(s) found
If the column name does not exist, it stops and lists the available columns instead of failing with a stack trace. Notes utf-8-sig handles the BOM that Excel adds when it saves CSV as UTF-8. Line numbers count the header as line 1, so they match what you see in a text editor (assuming no multi-line quoted fields). casefold() is used instead of lower() because it handles more Unicode cases. The script only reports problems; it never changes your file.

Powered by Readybot.io