00_data_audit.md

NSBA Processed-Data Integrity Audit

NSBA Processed-Data Integrity Audit

Auditor role: adversarial data-integrity reviewer (tried to break the parsers). Date: 2026-05-30 Method: independent recompute of every processed table from the raw .xlsx using /home/david/code/nsba/.venv/bin/python, plus hand-verification of 5 random games and targeted teardown of every anomaly. Audit driver: scripts/audit_data.py (read-only; writes nothing to data/raw).

Verdict

TRUSTWORTHY ENOUGH TO MODEL ON, with documented carve-outs. The parsers are faithful: combine reconstructs exactly (0 mismatches) and 181/193 games reconcile exactly. Every one of the 12 reconcile failures is a defect in the source spreadsheet (unplayed template tabs, joke/junk sheets, or sheets whose running-score formula was reset mid-game), not a parser bug. There is no confusion between combine (pyramidal) and game (standard) scoring. The main thing a modeler must do before fitting: filter games on reconciles == True (and drop 0-0 templates), and treat a handful of nsba3 games as team-level-only.

No blocker issues. Issues below are major (must handle before modeling) or minor (cosmetic / documented limitation).

CHECK 1 — Do parsed game scores reconstruct the displayed final scores?

• games_meta.score_a/score_b are copied verbatim from each sheet's "Final Score" cell. 5 random hand-checks all matched the raw cell exactly (Colin vs Xpoes 84-60, na vs ok 96-50, Connor vs Cryo 92-44, limitless-usmic 86-46, devansh v sheep b 122-60).
• Independent recompute of team totals from tossups_long + bonuses_long agrees with games_meta.calc_* on all 193 games (the long tables and the meta calc columns are internally consistent — no divergence between the published events and the published per-game calc).
• The parser's event-sum reconciliation flags 12/193 games as not reconciling. I verified each by hand:

Root causes (all in the source, not the parser): - Segmented / reset running scores (e.g. dv vs sb): the sheet's running-score column resets twice (rows q12 and q23), so the FINAL cell reflects only the last segment (65-28) while the leftover buzz cells from earlier segments remain. The parser naively sums every numeric buzz cell → ~2x inflation. The final score is right; the per-event / per-player rows for these games are inflated. - Unplayed template tabs: 33-row template copies left in the workbook with leftover demo buzzes; Final Score = 0-0, Winner banner = "Tie". - RR11 junk: someone entered fractional "scores" (0.2, 9.629, 9.9, 0.01); the parser correctly clamps tossup cells to {-4,0,4} and so diverges from the garbage final cell.

Hand-reconstruction of clean games confirms parser logic is correct for BOTH layouts: limitless-usmic (paired/nsba3) → 86-46 ✓ ; Connor vs Cryo (wide/nsba2) → 92-44 ✓ , independently summed.

Severity: MAJOR — not because the parser is wrong, but because downstream code must (a) gate on reconciles == True, and (b) not use game_player_box / tossups_long rows from the 5 real-but-inflated nsba1 games (56 player-box rows) or the 5 unplayed 0-0 templates. The published final scores are fine.

CHECK 2 — Combine pyramidal scoring vs game standard scoring (no cross-contamination)

• Combine points distribution: {0:390, 2:805, 3:324, 4:169, -1:2} — i.e. the pyramidal set {0,2,3,4} as designed. The only two -1 values are a genuine source quirk (scorers typed the penalty into the points cell for nsba1 Avaneesh/Chem3 and nsba1 Harry Jin/Phys2); reconstruction still matches the official total, so harmless.
• Combine penalty distribution: {-1:511, 0:1} — all penalties are signed negative (nsba1's positive-count penalties were correctly negated). No positive penalties leak through.
• Combine overall reconstruct (Σ points + Σ penalty) vs overall_score: 0 mismatches across all players that have an overall.
• Game tossup_points: strictly {-4, 0, 4} (737 / 2174 / 3453). Zero values outside that set → no pyramidal points contaminating game data.
• Game bonus_points: 1692 are the canonical 10; the ~30 non-10 values (0.01, 0.2, 9.629, 18.84, -40, …) are entirely inside the RR11 junk games and the segmented nsba1 sheets — i.e. source garbage, isolated to already-flagged non-reconciling games.

Severity: MINOR (the two -1 combine cells; otherwise clean). The two systems are correctly kept separate.

CHECK 3 — Double-counting of tossups / bonuses

• No (game, tossup, team) has >1 bonus row → bonuses are not duplicated.
• 26 bonus rows assigned to a team that did NOT get that tossup correct. 24 of these are inside the RR11 junk / forfeit sheets. The 2 legitimate ones (nsba1 Round3 aa vs sb q14, nsba1 Round8 na vs sb q8) are source scorekeeping errors: team A buzzed correctly but the 10-pt bonus was typed into team B's bonus column. Both games still reconcile (the sheet's own final formula also gave B the bonus), so the bad attribution is baked into the source. Effect: 2 bonuses credited to the wrong team in bonuses_long.
• 7 games carry >25 distinct tossup numbers (up to 33) — all are the nsba1 Playoff template/segmented sheets already flagged in Check 1 (33-row templates). No clean game exceeds 25.

Severity: MINOR — confined to already-flagged junk games, plus 2 isolated mis-attributed bonuses.

CHECK 4 — Category-label normalization

• Game tossups normalize to {b, ch, cs, ess, m, p} plus legacy ene (nsba1 energy). Stray raw values that fell through the map: ban (1 row), en (4 rows, nsba3 Round 5 — almost certainly "ene"/energy typo or mis-key), jhg, jkj, jkjk (6 rows total, nsba1 Play-In — keyboard mash in the subject cell). Total ≈ 12 mislabeled rows out of 6,364. 152 tossups have a null subject (dead tossups / blank subject cell), which is expected for unbuzzed questions.
• Combine categories normalize cleanly to {bio, chem, cs, ess, math, phys} in both combine_question_long and combine_player_category. No strays.

Note: the two scoring systems intentionally use different code vocabularies (games: b/ch/cs/ess/m/p; combine: bio/chem/cs/ess/math/phys). This is consistent within each system but a join across systems needs an explicit crosswalk (b↔bio, ch↔chem, m↔math, p↔phys). Document this.

Severity: MINOR (≈12 junk subject cells; cross-system crosswalk needed).

CHECK 5 — Season mapping (2022/2023/2025 → nsba1/2/3)

• Game-folder names == season labels in games_meta: {nsba1_2022, nsba2_2023, nsba3_2025} — exact match.
• Per-season game counts: nsba1=75, nsba2=65, nsba3=53.
• Combine seasons: nsba1=61, nsba2=51, nsba3=91, nsba4=52 players.
• nsba4 has combine but no game folder (correct — drafting it now).

Severity: NONE — mapping is correct.

CHECK 6 — Identity-map collisions / wrong merges

• 646 canonical players. match_method: discord_stem=291 (conf 1.0), real_name=52, fuzzy_firstname=30, ambiguous_firstname=11 (correctly not merged), singleton=526. 65 rows flagged needs_review.
• No clustering errors found. I hunted for clusters mixing >1 distinct real first-name: only 2, both correct (Gabe L=Gabriel Levin; Kaiwen/Kaiwen Yao joined to discord handle Lockheed Martin#2051 / lockheedmartin — verified the same person's joke handle across nsba2+nsba3, confidence 1.0).
• Cosmetic bug (canonical_name picker): looks_like_realname() treats a two-word discord display handle as a "real name", so 3 clusters get a display handle as their canonical_name even though a real name exists:
• P0368 → "Lockheed Martin" (should be "Kaiwen Yao")
• P0148 → "bucketofjava" (should be "McNair Shah")
• P0595 → "thegodlybob" (should be "McWei Wei") The clustering is right; only the display label is wrong. Fix: in pick_name, prefer a raw_name that is a real name over a discord_tag-derived one, or exclude tags that came from the discord_tag column.
• Ambiguous bare-first-name game entries (Connor, daniel, evan, …) are correctly left unmerged with candidate lists in identity_needs_review.csv.

Severity: MINOR (3 wrong display labels; no wrong merges).

CHECK 7 — Missing players / empty tabs / silently dropped rows

• Combine player tabs: for nsba1 (61), nsba3 (91), nsba4 (52) the count of player tabs exactly equals the count of overall rows AND the count of distinct players with question rows. No tab silently dropped. (The "absent from both" list printed by the audit is a false alarm — it compares tab title to the in-sheet Name cell which differs; every tab is in fact present under its in-sheet name. Verified counts match: 61/61/61, 91/91/91, 52/52/52.)
• nsba2 combine has NO per-question data — its category sheets are aggregate-only, so combine_question_long has 0 nsba2 rows by design. The aggregate parser still captures all 51 players × 6 categories (306 category rows, 0 nulls, CS included) and 51 overall scores (0 nulls). This is a source limitation, not a drop: you cannot get pyramidal Q1-4 granularity for nsba2.
• 4 nsba3 Round-1 games have NO player names (scorer left placeholder slots AS/A1/A2/...). The parser correctly emits team-level tossup/bonus rows (games reconcile) but 0 player-box rows for them. So 123 tossup buzzes carry a team but null player — expected and correctly handled; just be aware these 4 games contribute nothing to player-level box scores.
• game_player_box: 1,465 rows, 242 distinct players, 0 null toss_points.

Severity: MINOR (nsba2 pyramidal granularity missing; 4 nsba3 games are team-level only — both are source limitations, faithfully represented).

CHECK 8 — Does CS appear as a game tossup subject in nsba2 & nsba3?

Confirmed: CS replaces Energy starting nsba2; nsba1 still uses legacy ene. This matches the ground-truth spec exactly.

Severity: NONE — correct.

Issue summary (tagged)

Recommended guardrails before modeling

1. Always filter games_meta on reconciles == True for any team- or player-level aggregation, AND drop score_a==0 & score_b==0 games. This removes all 12 problem games (the 5 inflated real games, 5 templates, RR11 junk). 181 clean games remain.
2. When using game_player_box/tossups_long, join back to games_meta and exclude reconciles==False game_ids so the 56 inflated box rows never enter player stats.
3. Add a crosswalk for the two subject vocabularies (b↔bio, ch↔chem, m↔math, p↔phys, cs↔cs, ess↔ess; ene = legacy energy, nsba1 only).
4. Treat nsba2 combine as category-level only; treat the 4 named nsba3 games as team-level only.
5. (Optional) Fix pick_name/looks_like_realname so a real name always beats a discord display handle for canonical_name.

NSBA Draft Analytics · embargoed until after the SSB draft · ← hub