Checks a folder of submitted spreadsheets before anyone publishes a number from them, and exits non-zero when the numbers won't hold up.
I wrote this after spending a week working out why a published figure was wrong. It wasn't a bug in any report. Several organizations each send a workbook every quarter, the numbers get combined, and nobody was looking at the inputs. By the time anything looked odd downstream, three quarters of reports had already gone out.
Needs Python 3.11 or newer. Nothing else — no database, no API keys, no accounts.
git clone https://github.com/Ark2027/impact-data-quality
cd impact-data-quality
python -m venv .venv
source .venv/bin/activate # Windows: .venv\Scripts\activate
pip install -e .Then build the fixtures and audit them:
python scripts/generate_fixtures.py
python run_audit.py fixtures/submissions --config config/example.jsonThe fixtures are fictional and each carries a specific defect on purpose. Output:
NOT FIT TO PUBLISH 6 error(s), 4 warning(s), 118 rows in scope
Counted once per row:
Support received: 118
Coverage
source field answered coverage rate*
Northwest Partners Income band 30/30 100% 47%
Atlantic Community Fund Income band 0/21 0% n/a
* rate among rows that were answered, not among all rows
Exit code is 1 when something blocking is found, so it can sit in front of a publishing step and actually stop it.
Give people a row of tickboxes and some rows get several ticks. Sum each column, add the totals, and you have counted those rows more than once.
Support received: summing the columns gives 55, but only 30 rows tick any of them.
Counting each row once avoids overstating by 25.
That's an 83% overstatement on a number somebody was about to publish. Nobody notices, because each individual column total is correct. It's the addition that's wrong.
The audit reports both figures. You get the honest count and you get to see how far the naive one would have been out.
If a column was never filled in, the answer is "we don't know", not "0%". Those look identical once they reach a chart and they mean opposite things. An absent field is an error here, not a quietly-rendered zero.
Related, and the reason coverage is printed next to every rate: a rate calculated over the rows that happen to have data, presented as though it covered everything, is the other common way these reports mislead. So both numbers appear. rate_of_answered is the honest one. rate_of_all_rows is the one people quote by accident.
An error means the number would be wrong and no footnote fixes that. Missing dates, non-numeric amounts, a coverage field that's entirely absent, a workbook nobody expected.
A warning means say so and carry on. Stale files, overlapping flags, possible duplicates.
That split is the part that makes this usable. Treat everything as equally urgent and people learn to ignore all of it. Possible duplicates are a warning specifically because without a stable record id they're only possible, and two genuinely identical small awards on the same day do happen. Blocking on that would train everyone to skip the output.
Every file gets a SHA-256 hash, size, and modification time recorded in the manifest. Six months later, when someone asks which version of a spreadsheet produced a published figure, there's an answer.
Staleness is checked too. A file that hasn't changed in fifty days is usually a file somebody forgot to resubmit.
Everything domain-specific is in the config; the audit code knows nothing about what the data means. config/example.json is annotated.
The manifest matters more than it looks. Anything present in the folder but not listed gets flagged, because a stray q3-final-FINAL-v2.xlsx is exactly how totals quietly change between runs.
"check_all_that_apply": [
{ "name": "Support received", "columns": ["Technical Assistance", "Mentoring", "Training", "Referral"] }
],
"coverage": [
{ "name": "Income band", "column": "Income Band", "positive_words": ["low", "moderate"], "minimum_coverage": 0.8 }
]Column matching is deliberately loose. LMI Y/N, lmi y n and LMI all resolve to the same field, because submitted spreadsheets never agree on spelling and exact header matching guarantees a support ticket every quarter.
python tests/test_audit.py34 of them, standard library only.
Both, and they behave the same once loaded. .xlsx, .xlsm and .csv are all picked up, and the manifest just lists whichever the source actually sends. For a workbook the config's sheet name picks the sheet by loose match; a CSV holds one table so the sheet name is ignored.
CSVs are read as utf-8-sig first, then cp1252, then latin-1. That order is deliberate: Excel's "Save as CSV" writes cp1252 on plenty of Windows machines and UTF-8 with a BOM on others, and utf-8-sig handles the BOM case while passing plain UTF-8 through untouched. Assuming UTF-8 and hoping is how you get a crash on the first accented name.
The only thing a CSV can't have is the second sheet, so those rules are skipped silently. Submitting CSV is a valid choice, not a defect.
pandas treats n/a, NA, NULL and about fifteen other strings as missing when it reads a file. For an audit that's exactly backwards: a date somebody typed as n/a is unreadable, not blank, and the difference decides whether it gets reported or silently dropped from every total.
I found this the annoying way. My "unreadable date" fixture was being converted to NaN before any check saw it, so the check passed while doing precisely nothing. Reading now runs with keep_default_na=False and na_values=[], so what was in the cell is what gets judged.
Everything is read into memory. Fine for the sizes this is built for, which is dozens of files with thousands of rows each, not millions.
Duplicate detection compares every reported field. If two genuinely different records happen to agree on all of them, this cannot tell them apart, which is why it warns rather than blocks.
Column matching being loose cuts both ways. find_column(frame, "State") will happily match a column called Estate if no better candidate exists. It has not bitten me yet, but it could.
MIT