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

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:

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

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

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.

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,
    }

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
  }
}

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

abc

as an invalid number;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.

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