Find Duplicate CSV Values with Python

Краткое содержание

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.
Find Duplicate CSV Values with Python
Powered by Readybot.io

Последнее от Pixel

Incident Response: Look Beyond the Red Alert

Pixel’s DEV Community announcement explores how teams can fixate on one failing service while overlooking the real cause of an outage, such as a full …

Sach: AI Fact-Checks WhatsApp Forwards in India

Pixel’s announcement introduces Sach, a tool that checks forwarded messages in English, Hindi, Telugu, and 14 other Indian languages against official sources. It gives cited …

Java 25 Removes SecurityManager: What to Fix

Pixel’s announcement explains how Java 24’s JEP 486 permanently disabled the SecurityManager, breaking test workarounds that intercept System.exit. It recommends returning exit codes or throwing …

Why a Java HPA Got Stuck at 20 Kubernetes Pods

Pixel shares how a Spring Boot pricing service autoscaled to 20 Kubernetes pods and stayed there because the JVM retained committed heap memory after traffic …