csv-diff-reconcile

Diff two CSV/TSV/JSON tables — and act on the difference.

cdiff is a hard fork of simonw/csv-diff (Apache-2.0). Upstream answers "what changed?" in a form a human reads. This fork also answers it in a form a pipeline can act on: composite keys, a comparison policy, machine-readable cell-level records, and an exit-code contract you can put in CI.

$ cdiff yesterday.csv today.csv --key id,region --ignore-case --numeric-tolerance 0.01

See it work

Same two files, twice. Strict mode reports 4 rows changed (9 cells), 1 added, 1 removed — including 980.50 vs 980.5 and Nordic US vs nordic us. With the comparison policy on, 2 rows changed, 1 added, 1 removed remain: what a reconciliation job actually wants to see.

Machine-readable output, the exit-code contract, and the duplicate-key guard:

Markdown summaries for PR comments, plus the test suite:

---

1. Why this fork exists

Upstream is deliberately small (212 lines of library code) and does one thing well. Running it against real reconciliation work — nightly exports, supplier feeds, API dumps vs database extracts — surfaces problems that are recorded in its own issue tracker and are not fixed:

Upstream issue	Symptom when you hit it	What this fork does
#22 #23 #36	--key accepts one column only, so any table with a composite primary key cannot be diffed	--key id,region builds a composite key
#31 #54	Two rows with the same key: the last one silently wins, the earlier row vanishes with no warning	duplicate keys abort with the offending line numbers; --on-duplicate first|last opts into merging
#52 #53	Comparing two header-only (empty) CSVs raises StopIteration	returns an empty result, exit 0
#41	Any single cell over 128 KB raises _csv.Error: field larger than field limit	the limit is raised at import; 200 KB cells load fine
#19	Files are opened as UTF-8 only; a GBK export from a Chinese office suite produces mojibake or UnicodeDecodeError	encoding auto-detected (utf-8-sig → utf-8 → gb18030), --encoding to override
#20	Cleo vs cleo is reported as a change	--ignore-case
#21	--format is the only delimiter control; an odd |-delimited export cannot be read	--delimiter
#39	Output is human text or a nested JSON blob; no flat, loadable format	--output csv|jsonl|json|markdown|table
—	Nothing to consume in CI: upstream always exits 0	--fail-on any|changed|added|removed

Two more gaps are not in the tracker but matter just as much for reconciliation, and are new here:

• 5 vs 5.00 vs 5 are three different values upstream. --numeric-tolerance 0 makes them equal; --ignore-whitespace handles the padding.
• A renamed column produces N changed rows plus a phantom column add/remove. --map-column old_name new_name compares the two columns directly.

Scope note: upstream issue #40 (Error when column name contains "..") is already fixed in upstream 1.2. It was verified during this work and is not claimed as a fix here.

---

2. Install

pip install -e .

Requires Python ≥ 3.8. Only runtime dependency is click; upstream's dictdiffer dependency is no longer needed.

The console command is cdiff. The library is still importable as csv_diff, so code written against upstream keeps working — see §6.

---

3. Usage

3.1 The reconciliation loop

# Last night's export vs tonight's
cdiff data/tuesday.csv data/wednesday.csv --key id,region --output json -o report.json

# Fail a CI job if any value changed (new rows are fine)
cdiff baseline.csv current.csv --key id --fail-on changed

# Same thing via environment variable, e.g. in a GitHub Actions step
CDIFF_FAIL_ON=any cdiff a.csv b.csv --key id,region

Exit codes — the whole contract:

Code	Meaning
0	No differences (or differences exist but --fail-on is none, the default)
1	Differences found and --fail-on selected them
2	Bad input: unreadable file, missing key column, duplicate keys, invalid JSON, --strict warnings

--fail-on defaults to none so that existing upstream scripts keep their behaviour. Opt in explicitly, or set CDIFF_FAIL_ON.

3.2 Composite keys

$ cat previous.csv
id,region,amount
1,EU,100
1,US,200

$ cdiff previous.csv current.csv --key id,region --output table
change   key     column  previous  current
-------  ------  ------  --------  -------
changed  1 | US  amount  200       250

--key id alone would abort here, because 1 appears twice — which is the correct outcome, and exactly what upstream cannot tell you.

3.3 Comparison policy

$ cdiff before.csv after.csv --key id
change   key  column  previous    current
-------  ---  ------  ----------  ----------
changed  1    name    Cleo        cleo
changed  1    score   5           5.00
changed  2    note      spaced    spaced

$ cdiff before.csv after.csv --key id --ignore-case --ignore-whitespace --numeric-tolerance 0
change  key  column  previous  current
------  ---  ------  --------  -------

Same two files, four "differences" or zero — the second answer is the one a reconciliation job wants.

Option	Effect
--ignore-case	Case-fold before comparing
--ignore-whitespace	Strip and collapse whitespace runs
--numeric-tolerance N	Equal if within N; 0 means numerically equal (5 == 5.00)
--ignore-column a,b / --ignore-column '*'	Leave columns out entirely
--map-column OLD NEW	Compare OLD (previous) against NEW (current)
--empty-as-null	"", null, none, na, n/a, nan all mean "missing"

3.4 Report formats

--output	Shape	Use it for
text (default)	Upstream's exact human format	Reading in a terminal; unchanged from upstream
table	Fixed-width, CJK-aware alignment	Terminal review of wide tables
csv	One row per changed cell	Loading into a spreadsheet or SQL table
jsonl	One JSON object per line	Streaming into a log pipeline
json	{summary, column_stats, duplicates, added, removed, changed}	Programmatic consumption
markdown	GitHub-flavoured tables	PR comment / $GITHUB_STEP_SUMMARY

--json is kept separately and still emits upstream's exact 5-key structure.

3.5 Cross-format diffing

# CSV export on the left, JSON API dump on the right
cdiff export.csv api.json --key id --output markdown

Format is chosen per file from its extension, so the two sides may differ. Keys are matched as text, so a CSV 1 lines up with a JSON 1.

---

4. Library use

from csv_diff import load_table, reconcile, Normalizer

previous = load_table("tuesday.csv", key="id,region")
current = load_table("wednesday.csv", key="id,region")

normalizer = Normalizer(
    ignore_case=True,
    ignore_whitespace=True,
    numeric_tolerance="0.01",
    ignore_columns=["updated_at"],
    column_map={"old_name": "new_name"},
)

result = reconcile(previous, current, normalizer=normalizer)

print(result.total_differences)
print(result.stat("amount").changes)         # per-column change count
print(result.duplicates["previous"])        # duplicate keys, with line numbers
for entry in result.changed:
    print(entry["display_key"], entry["changes"])

Table is a dict subclass, so len(), indexing and equality all behave as they did upstream. It carries extra metadata: columns, encoding, key_columns, line_numbers, duplicates, ragged_rows, skipped_blank.

---

5. Performance

Measured on this machine (Windows, Python 3.13.14, warm cache, best of 5). Reproduce with python bench/vs_upstream.py; the script asserts that both implementations report the same number of changed rows before comparing times, so the comparison is not apples-to-oranges.

Dataset: 6 columns (id, region, name, amount, qty, note), every 10th row has one changed cell.

Rows	File size / side	Fork load+reconcile	Throughput	Peak memory
1,000	0.04 MB	0.013 s	76,000 rows/s	1.6 MB
10,000	0.45 MB	0.130 s	77,000 rows/s	16.2 MB
50,000	2.43 MB	0.377 s	127,000 rows/s	85.1 MB
200,000	10.22 MB	1.532 s	131,000 rows/s	340.8 MB

(median of 7 runs, minimum shown for throughput; measured when the machine's fixed CPU probe read 0.098 s.)

Fork vs upstream, single-column key, paired runs on the same input:

Rows	upstream csv-diff 1.2	this fork	overhead
50,000	0.321 s	0.357 s	+8% … +14%

The fork is roughly 10% slower on the plain case. That is the honest cost of duplicate detection, encoding sniffing, empty-file handling and line-number tracking, which upstream does not do at all. The gap was measured at +89% in the first working version; two rounds of profiling brought it down (§8).

Cost of turning the policy on (50,000 rows): full normalisation (case + whitespace + tolerance) costs about +40% over strict. Rendering is negligible by comparison: JSON 0.010 s, JSONL 0.024 s, CSV 0.018 s, Markdown 0.001 s.

Measurement honesty: absolute times on this machine swung by up to 4× between runs depending on background load (a fixed CPU probe ranged from 0.098 s to 0.62 s during this session). The figures above were taken at 0.098 s probe time. Under load the same workload took 1.07 s instead of 0.377 s. Re-run python bench/stable.py and python bench/vs_upstream.py on your own hardware; the scripts print the probe reading alongside the timings so you can judge whether a number is trustworthy.

---

6. Compatibility with upstream

The upstream test suite (24 tests) runs unmodified and passes. Compatibility was treated as a hard constraint, not an aspiration:

• load_csv(fp, key=None, dialect=None), load_json(fp, key=None), compare(previous, current, show_unchanged=False) and human_text(result, key, singular, plural, current, extras) keep their signatures and return shapes.
• --json still emits the exact 5-key dictionary.
• Default text output is character-for-character upstream's.
• Exit code 0 on differences is preserved; --fail-on is opt-in.
• Warnings go to stderr and are off by default (--warnings), so nothing new appears in stdout.
• JSON integer ids stay int keys, matching upstream's behaviour.

Where a new behaviour would have contradicted upstream, it is opt-in via a flag rather than a changed default.

---

7. Code provenance

Every file in this repository is accounted for:

File	Origin
csv_diff/loader.py	New. No upstream equivalent
csv_diff/normalise.py	New. No upstream equivalent
csv_diff/compare.py	New, with compare() reimplemented to be drop-in compatible with upstream's
csv_diff/report.py	human_text() / human_row() / human_extras() copied verbatim from upstream csv_diff/__init__.py; all other renderers new
csv_diff/cli.py	Rewritten. Upstream's 10 options kept with identical semantics; 16 new options added
csv_diff/__init__.py	Rewritten as a facade. Upstream's four public functions are re-exported with compatible behaviour
tests/test_cli.py, tests/test_csv_diff.py, tests/test_human_text.py	Copied verbatim from upstream, unmodified, as the compatibility guard
tests/test_reconcile.py, tests/test_cli_reconcile.py	New. 79 tests for the added behaviour
bench/*.py	New
LICENSE	Upstream's Apache-2.0 text, unmodified
NOTICE	New. Attribution and list of modified files, per Apache-2.0 §4(b)/(c)

Line counts:

	lines
Upstream library code carried over (csv_diff/__init__.py original, 212 + cli.py 82)	294
Upstream test code carried over (byte-identical)	643
New library code (loader 467 + normalise 205 + compare 419 + report 376 + cli 470 + __init__ 71)	2,008
New test code (79 tests)	795
Benchmarks	296

The 2,008 figure includes the three upstream report functions copied into report.py (~90 lines), so genuinely new library code is roughly 1,900 lines. The test count and line counts above are from wc -l and pytest, not estimates.

---

8. Design notes

Why duplicate keys are an error, not a warning. Silently dropping a row is the worst possible behaviour for reconciliation: the diff reports "no change" for a row that is simply missing. Line numbers are included because the fix is almost always to extend the key.

Why line numbers are tracked. --output csv has a line column so a flagged difference can be opened directly at the offending row of the source file. Upstream discards this information during loading.

Why the composite key uses \x1f. Unit Separator cannot appear in CSV or JSON text, so there is no ambiguity with a column value that happens to contain a comma. It is expanded to | only for display.

Why Table subclasses dict. Upstream's tests assert load_csv(...) == {...}. Subclassing keeps that true while allowing metadata to ride along, which avoided forking every call site.

Why the policy is opt-in. Defaulting --ignore-case on would make the tool quietly miss genuine case-only changes in identifiers. The default stays strict; the flags document the intent at the call site.

Performance work (measured, not guessed). Profiling with cProfile showed reconcile at 1.02 s and _row_changes at 0.88 s of a 1.56 s run. Fixes, in order:

1. A C-level dict equality pre-filter per row (upstream does the same), so unchanged rows never enter per-cell comparison.
2. Normalizer.is_identity — when the policy is the default strict one, skip every per-cell policy call.
3. Cache canonical_column lookups (605k calls were being made).
4. Hoist loop invariants in the loader and drop a redundant blank-line scan that was costing an extra pass over every row.

Result: 50,000 rows went from 0.718 s to 0.377 s, and the gap to upstream from +89% to about +10%.

---

9. Known limitations

These are real and not worked around:

1. Both files are held in memory. Peak RSS at 200,000 rows is ~341 MB. A streaming implementation would need a sort-merge join; that is a different architecture, not a tweak. Large-file work is out of scope here.
2. Encoding detection is a guess by try-decode. A GBK file that happens to be valid UTF-8 will be read as UTF-8. --encoding is the escape hatch, and --warnings reports which encoding was chosen.
3. Numeric tolerance uses Decimal. Very large exponents or values with more than ~28 significant digits may round. Values that are not numeric are never coerced, so 1,234 stays text.
4. --map-column is one-to-one. Merging two old columns into one new column is not supported.
5. --ignore-case also folds the key. That is usually what you want, but two ids differing only in case will be merged, and --on-duplicate decides which survives.
6. Markdown/table reports truncate at --max-rows (default 50) for review; the JSON/CSV/JSONL reports are never truncated.
7. No streaming stdin for JSON. - is read as CSV only.
8. Column-order changes are not reported as a difference. Upstream does not either; only added/removed columns are reported.
9. "Changes by column" counts raw differences, not business impact. In the example data, updated_at shows up as the most changed column (4 cells) simply because every touched row has a new timestamp. The report tells you where the differences are, not which ones matter — use --ignore-column to remove the noise before reading the ranking.
10. --ignore-case shows the folded key in the table report. With --ignore-case the displayed key is the case-folded form (a-1001 | eu rather than A-1001 | EU), because that is what matching happened on. The JSON/CSV reports keep the original row values.

---

10. Development

pytest -q                      # 103 tests
python bench/benchmark.py --all
python bench/stable.py         # median-of-N, prints a CPU probe reading
python bench/vs_upstream.py    # needs ../research2/csv-diff-main vendored
python tools/make_banner.py    # regenerates docs/screenshots/hero.png
python tools/make_shots.py     # regenerates the three terminal screenshots

CI

.github/workflows/test.yml runs on every push:

• test — 9 combinations (Ubuntu / Windows / macOS × Python 3.9 / 3.11 / 3.13), running pytest, a CLI smoke test, and an assertion that the exit-code contract still holds (--fail-on any must return 1 with differences and 0 without).
• upstream-compat — pins the SHA-256 of the four upstream test files. If a future commit edits one of them, the compatibility claim in §6 stops being true and this job fails. That is the point: the claim is enforced, not asserted in prose.

The screenshot tools call the real cdiff binary and read their numbers back from --output json, so a caption cannot drift away from what the tool actually reports.

---

11. License

Apache-2.0. This is a derivative work of csv-diff by Simon Willison; see LICENSE and NOTICE. Upstream's copyright notice is retained, and NOTICE lists every file that was modified.