DEV Community

Kalamari0227
Kalamari0227

Posted on

How I check duplicate IDs and missing cells in CSV with Python

#python #data #tutorial #csv

AI disclosure: This article was prepared with AI assistance and reviewed against the runnable sample and its reproducibility test.

CSV quality problems often appear before a full validation framework is necessary. A quick first pass can answer three useful questions:

  1. How many rows did we receive?
  2. Are IDs duplicated?
  3. Which columns contain blank cells?

The following example uses only Python's standard library and synthetic data.

Example CSV 

id,amount,qty,category
A001,10.00,1,books
A002,5.50,2,toys
A003,,3,books
A001,10.00,1,books
A004,7.25,4,home
A005,abc,2,toys
A006,0,1,accessories
Enter fullscreen mode Exit fullscreen mode

There are seven rows, one repeated ID, and one blank value in amount. Notice that abc is not blank—it is a separate numeric-validation problem. Keeping those concepts separate prevents a simple missing-value check from making claims it cannot support.

A limited quality check 

import csv
from collections import Counter
from pathlib import Path


def profile_csv(path: Path, id_field: str = "id") -> dict:
    with path.open("r", encoding="utf-8-sig", newline="") as handle:
        reader = csv.DictReader(handle)
        rows = [dict(row) for row in reader]
        columns = list(reader.fieldnames or [])

    id_counts = Counter(
        row.get(id_field, "").strip()
        for row in rows
        if row.get(id_field, "").strip()
    )
    duplicate_id_count = sum(
        count - 1 for count in id_counts.values() if count > 1
    )
    missing_cells = {
        column: sum(1 for row in rows if not str(row.get(column, "")).strip())
        for column in columns
    }

    return {
        "row_count": len(rows),
        "columns": columns,
        "duplicate_id_count": duplicate_id_count,
        "missing_cells": missing_cells,
    }
Enter fullscreen mode Exit fullscreen mode

For the sample above, the result is:

{
  "row_count": 7,
  "columns": ["id", "amount", "qty", "category"],
  "duplicate_id_count": 1,
  "missing_cells": {
    "id": 0,
    "amount": 1,
    "qty": 0,
    "category": 0
  }
}
Enter fullscreen mode Exit fullscreen mode

What this check does not do

A small script should state its limits clearly. This example does not:

  • convert numeric strings;
  • flag abc as an invalid number;
  • separate invalid rows;
  • expose an HTTP endpoint;
  • validate against an external schema;
  • make the data production-safe.

Those tasks require explicit rules and deeper tests. The useful design lesson is to return observable facts first, then add stricter behavior only when the data contract is known.

Reproducibility

The runnable preview repository contains the synthetic CSV, expected JSON response, and a no-dependency test: https://github.com/Kalamari0227/eidolon-csv-quality-preview

The expanded local HTTP API package, with numeric summaries and default/strict modes, is available here: https://ethanlee20.gumroad.com/l/dmuomd

Top comments (0)