CSV Data Profiler

Answer three questions about any dataset: what is in it, what is wrong with it, and can I trust it for the intended use?

When to use

First contact with an unfamiliar dataset; a pipeline started producing odd results; validating a vendor delivery; pre-modelling sanity checks.

Do not use for: answering a specific analytical question (profile first, then analyse), or streaming/real-time data.

Workflow

0. Establish intent before profiling

Ask what the data is for — reporting, joining, training a model, migration. The intended use decides which defects are fatal versus cosmetic. If the user does not answer, profile generically and say so.

1. Run the profiler script, do not eyeball the file

Deterministic computation beats token-generated statistics.

python3 scripts/profile.py data.csv --output report.md --sample 200000

The script handles delimiter sniffing, encoding detection, mixed types and large files via sampling. Read references/interpretation.md for how to read edge cases in its output.

Only inspect the raw file directly when the script fails (malformed quoting, non-UTF-8 encoding, ragged rows) — then diagnose and re-run.

1b. Validate against expectations when a contract exists

If the user has a schema, a previous delivery or stated expectations, check the file against them rather than only describing it:

python3 scripts/validate_schema.py data.csv --schema expectations.json --format md

The schema file declares per-column type, nullability, ranges, allowed values, regex patterns and uniqueness. Generate a starting point from a known-good file with --infer, then edit the thresholds. This turns profiling into a repeatable gate you can run on every delivery.

2. Structural checks

3. Per-column profile

For every column report: inferred type, null count and %, distinct count, % unique, top 5 values with frequencies, and an example value.

Additionally:

• Numeric: min, p1, q1, median, mean, q3, p99, max, std, zero count, negative count, outliers by IQR (< q1 − 1.5·IQR or > q3 + 1.5·IQR).
• Categorical: full value set if ≤ 30 distinct, else top 20 + long-tail size; case/whitespace variants of the same value.
• Datetime: min, max, parse-failure count, future dates, epoch-zero sentinels (1970-01-01), mixed formats, timezone presence.
• Text: min/max/mean length, empty-string count, detected patterns (email, URL, UUID, phone, postcode), pattern-violation count.
• Boolean-ish: which encodings appear (true/false/1/0/Y/N/yes/no).

4. Quality issue catalogue

5. Cross-column checks

Pairwise correlation on numerics (flag |r| > 0.95); functional dependencies (A determines B → possible denormalisation); logical rules (end_date >= start_date, quantity * unit_price ≈ total); grain verification (which column combination is unique?).

6. Report

# Data Profile: <filename>

## Verdict
Fit for <intended use>: YES / WITH FIXES / NO — one-sentence reason.

## Overview
Rows, columns, size, encoding, delimiter, inferred grain, memory footprint.

## Critical issues        (blocks the intended use)
## Warnings               (needs a decision)
## Column reference       (the full table)
## Recommended cleaning   (ordered, with runnable code)
## Open questions for the data owner

Lead with the verdict. State counts and percentages, never "some rows".

Tools

python3 scripts/profile.py data.csv --output report.md --sample 200000
python3 scripts/validate_schema.py clean.csv --infer > expectations.json
python3 scripts/validate_schema.py new_delivery.csv --schema expectations.json

Both exit non-zero on failure, so they drop straight into a pipeline.

Worked example

transactions.csv — 1 284 302 rows × 14 columns.

Critical

1. customer_id — 99.7 % unique but not a key: 3 812 rows share IDs across different email values. Grain is actually (customer_id, order_date).
2. amount — stored as text; 41 209 values use a comma decimal separator ("1,50"). Naive float() casting drops them to null and understates revenue by ~3.2 %.
3. signup_date — 8 104 rows equal 1970-01-01 (epoch sentinel, not real dates).

Warnings

4. country — 214 distinct values for ~50 real countries: case and whitespace variants ("FR", "fr", "France ", "FRA").
5. discount_pct — max 340; 92 rows exceed 100.

Cleaning order: fix encoding → normalise placeholder nulls → cast types (locale-aware) → deduplicate → standardise categories → validate rules → re-profile.

Quality checklist

• Every column appears in the report, none skipped
• Where a contract exists, validate_schema.py was run and its failures reported
• All figures come from the script, not from estimation
• Percentages accompany every raw count
• Issues ranked by impact on the stated intended use
• Cleaning steps are runnable and ordered by dependency
• Sampling disclosed if the file was sampled
• No PII values reproduced in the report — mask or describe them