DATA_DICTIONARY_PROCESSED.md

Data Dictionary — processed

Processed Data Dictionary

Tidy tables written by scripts/parse_games.py to data/processed/. Source: every game .xlsx under data/raw/games/{nsba1_2022,nsba2_2023,nsba3_2025}/. NSBA4 has no games yet (we are drafting it), so no game data here.

Processed Games

Scoring is STANDARD Science Bowl (NOT the pyramidal Combine scoring): - Toss-up correct = +4 - Toss-up neg (wrong interrupt or wrong toss-up answer) = -4 - A wrong-but-non-penalty buzz is recorded as 0 - Bonus (only after a correct toss-up) = +10

A team's final score = (sum of its toss-up points) + (sum of its bonus points). We reconstruct this from the parsed events and check it against the score the sheet records (see "Reconciliation" below).

Two physical scoresheet layouts

The parser auto-detects which layout a sheet uses.

• "wide" layout — NSBA1 (2022) & NSBA2 (2023). Row 1 = team names (C1 = team A / "home", M1 = team B / "away"). Row 2 = headers; player names in C2:J2 (team A, up to ~6) and M2:T2 (team B). Toss-up rows start at row 3. Per row: A = question number, B = subject. The cell under a player's name holds that player's toss-up points for that question (4 / -4 / 0). Center columns: K = team A bonus, L = team A running score; U = team B bonus, V = team B running score. A Final Score label row (column A) ends the toss-up block and holds the recorded final scores (C = team A, M = team B); a Player TUH row below it holds per-player tossups-heard counts. Toss-up count varies: NSBA1 round games = 23, NSBA2 = 24, and several NSBA1 playoff sheets run 32-33.

• "paired T/B" layout — NSBA3 (2025). Row 2: D2 = team A name, R2 = team B name. K3 / N3 = recorded final scores. Row 4 = player names; row 5 = sub-headers T (points) / B (boolean). Each player occupies two columns: a T column holding toss-up points (4 / -4 / 0) and a B column (boolean). Center block: L = TOSSUP number, M = SUBJECT, K = team A bonus, N = team B bonus. Data rows 6-30 (25 slots, not all used). Team A player T-columns = A,C,E,G,I; team B = O,Q,S,U,W. Placeholder slot labels (AS, A1..A3, AC, BS, B1..) are treated as "no player".

One row per BUZZ (important)

A single toss-up can carry multiple buzzes: a neg by one team opens the question to the other team, and the sheets sometimes record two cells on the same team in one row. Therefore tossups_long has one row per buzz event, not one per question. Toss-ups nobody buzzed get a single row with team/player_raw null and tossup_points = 0. This is what makes reconciliation exact: summing tossup_points over a team recovers its true toss-up total.

Subject codes

Canonical: b (Biology), ch (Chemistry), cs (Computer Science), ess (Earth/Space), m (Math), p (Physics). NSBA replaced the legacy "Energy" category with Computer Science. NSBA1 still uses an ene slot (legacy Energy) — kept as-is, NOT remapped to cs. A few junk subject strings exist in the raw sheets (jkj, jhg, ban, en, ...) and are passed through unchanged; downstream code should filter to the 6 canonical codes (+ene).

games_meta.csv — one row per game

tossups_long.csv — one row per BUZZ (or one per dead toss-up)

bonuses_long.csv — one row per bonus awarded

game_player_box.csv — one row per (game, player) that buzzed or was on the floor

Reconciliation result

181 / 193 games (93.8%) reconcile exactly (calc == recorded score for both teams). All 12 non-reconciling games are due to corrupted/incomplete source sheets, not parser error:

• NSBA1 incomplete playoffs (4): Playoff 1/bb vs kk, Playoff 1/aa vs kk, Playoff 2/cn vs sb, Playoff 2/a vs kk — the Final Score cells were left at 0 although buzzes were recorded.
• NSBA1 corrupted-tally playoffs (5): Play-In/cn vs ok, Play-In/aa vs dd, Playoff 1/dv vs sb, Playoff 1/aa vs st, Playoff 1/cn vs me — the running score column (and hence the Final Score cell) was edited into a non-monotonic / stale state, so neither the recorded score nor the running column equals the sum of the buzz cells.
• NSBA2 RR11 troll/corrupted sheets (3): yared v xpoes, coby vs cryo, caleb vs hansen — these sheets contain non-Science-Bowl buzz/bonus values (e.g. 0.1, 2.718, 20, 40, -40, fractional running totals like 99.318). The data itself is corrupt.

Filter with reconciles == True before any quantitative modeling.

Lineup recoverability (gates a later plus-minus model)

What the sheets let us recover differs sharply by season:

So a per-tossup, substitution-aware plus-minus model is only fully feasible for NSBA1. For NSBA2 it is possible only on the ~13 games with TUH. For NSBA3 we know who buzzed each toss-up but not who was seated (subs are allowed), so on-floor plus-minus is not recoverable from these sheets — only buzz-attributed stats. tossups_present is the column that encodes this: non-null = lineup known; null = only buzzers known.

Regeneration

/home/david/code/nsba/.venv/bin/python scripts/parse_games.py

Prints counts, the reconciliation rate, and the list of non-reconciling games.

Processed Combine

Tidy tables written by scripts/parse_combine.py to data/processed/. Source: the four combine workbooks under data/raw/combine/ (nsba1_2022, nsba2_2023, nsba3_2025, nsba4_2026 — the last is IN PROGRESS).

The combine is a tryout used to seed the snake draft. Scoring is PYRAMIDAL (NOT the standard Science-Bowl scoring used in games): - 6 categories × 4 questions = 24 questions per player. - Each question's points reflect buzz-earliness: +4 (superpower) / +3 (power) / +2 (correct). A skipped/unanswered question is blank (null) or 0. - A wrong WXYZ multiple-choice answer is a −1 penalty, stored in penalty. - The question number (1–4) roughly encodes difficulty within a category, so it is preserved per-question for a later IRT model. - The combine is GAMEABLE — strong players sometimes "tank" — so treat scores as a seed signal, not ground truth.

Per-season layout quirks (handled by the parser)

player_raw is kept exactly as written in each source (tab Name cell, falling back to the tab title). Player names are not unique within a season (e.g. nsba2 has two distinct "Owen"/"Aditya"/"Rohan"/"Rishabh", separable by discord_tag). Identity reconciliation across these handles is a separate job.

Category codes

This file uses bio, chem, ess, math, phys, cs (note: longer codes than the games tables, which use b/ch/m/p). NSBA1 has no cs.

combine_question_long.csv — one row per (player, category, question 1–4)

combine_player_category.csv — one row per (player, category)

combine_player_overall.csv — one row per (player)

Validation

For nsba3 and nsba4, each player's overall is reconstructed from the per-question points + penalty and checked against the workbook's Overall sheet (matched by name). 0 mismatches where the Overall cell is populated. Additional internal checks (all pass with 0 mismatches): - reconstructed sum == the tab's Combined/Cum total cell — all 91 nsba3, 52 nsba4, and 61 nsba1 tabs; - sum of question points + penalty per category == category_total (nsba1/3/4); - nsba2: each Ovr row's per-category totals sum to its recorded Overall (51/51).

The only apparent "overall != sum-of-categories" cases are nsba2's 4 shared-name pairs noted above — an artifact of aggregating on first-name only; the stored rows are individually correct and distinguished by discord_tag.

Row counts (current run)

nsba4 eligibility (in progress): 5 paid + eligible, 1 paid-but-no-combine matched player whose tab is unmatched to the Overall sheet is conservatively left non-eligible.

Regeneration

/home/david/code/nsba/.venv/bin/python scripts/parse_combine.py

Prints per-table and per-season row counts and the validation mismatch list.

Science Bowl baseline (SSB-2026) — natural-roster reference data

Written by scripts/extract_scibowl.py from the scibowl.live local dataset scibowl-org/stats/ssb-2026-combined (schema_version 1, generated 2026-05-15; combined from Johns Hopkins Invitational + Stanford College+ SB + Stanford SB). Raw grain is one row per buzz; we aggregate to stable identities.

Real Science Bowl uses 6 categories — Biology, Chemistry, Physics, Math, Earth/Space (ESS), and Energy. There is no Computer Science (CS is NSBA-only). 5 categories overlap NSBA; Energy is extra. This data is the natural (school-formed) baseline for A16 (coverage) and A18 (natural vs drafted).

Scoring is STANDARD Science Bowl, identical to NSBA games above (TU +4 / neg −4 / end-wrong 0 / bonus +10). Celerity = fraction of the question stem unread at a correct buzz = (word_count − word_index)/word_count, clamped [0,1]; higher = earlier/faster; only defined for correct tossups.

Identity caveat: raw player_id/team_id are PER-GAME identifiers (one person → many ids). We aggregate on (name, team, tournament) for players and (team, tournament) for teams. Validation: every team's reconstructed total_buzz_points exactly equals its recorded game points_for (100% reconcile).

scibowl_player_stats.csv (one row per stable player, 366 rows)

Tossups only. In Science Bowl, bonuses are answered by the team (their buzz rows carry a blank player_id), so bonuses are not individually attributable and appear only in the team table. The player table is therefore a pure tossup profile. - player_name, team_name, tournament — stable identity key. - tu_buzz, tu_correct, tu_neg — tossup buzzes / correct / negs. - tu_conv_pct = tu_correct / tu_buzz. - avg_celerity — mean celerity over the player's correct tossups (blank if none). - tu_net_points — 4·tu_correct − 4·tu_neg (tossup-only; no bonus points here). - nsba_name_guess — conservative exact full-name match to an NSBA combine player (case-insensitive, first+last required); blank if no match. 23 distinct matches. Treat as a hint, not a confirmed link — name collisions are possible. - <cat>_tu_correct, <cat>_points for each of the 6 categories (biology, chemistry, physics, math, earth_space, energy); tossup-only.

scibowl_team_stats.csv (one row per stable team, 84 rows)

• team_name, tournament, games, wins, losses, win_pct.
• points_for, points_against — summed game scores (placement proxy; no official final standings ship with the dataset, so win_pct / points_for are the result metric).
• tu_buzz/tu_correct/tu_neg/tu_conv_pct, bonus_att/bonus_correct, avg_celerity.
• total_buzz_points — reconstructed; equals points_for exactly (validation).
• n_subjects_covered — count of the 6 subjects with ≥2 correct tossups (breadth).
• breadth_hhi — Herfindahl index of positive subject-point shares; ~0.167 = perfectly balanced, 1.0 = one-subject team. Lower = broader/more generalist.
• <cat>_tu_correct, <cat>_points per category.

scibowl_subject_coverage.csv (one row per team×subject, 504 rows)

• team_name, tournament, subject (one of the 6, uppercase).
• subject_tu_correct, subject_points — depth in that subject.
• subject_points_share — that subject's share of the team's positive points.
• n_distinct_answerers — # players with ≥1 correct TU in the subject (depth of bench).
• has_capable_answerer — 1 if subject_tu_correct ≥ 2.
• is_punted — 1 if the team has 0 correct tossups in the subject.

Regeneration

/home/david/code/nsba/.venv/bin/python scripts/extract_scibowl.py

Does not modify the scibowl-org repo (read-only source).

Analytical spine: player_season_master.csv + team_season.csv

Written by scripts/build_master.py. This is the backbone downstream analyses read. Built only from GAME data (standard Science-Bowl scoring), with the combine attached as a link, never mixed into the value stats.

Clean game set (the filter everything inner-joins to)

games_meta filtered to reconciles == True AND not(score_a==0 & score_b==0) = 180 games (nsba1=66, nsba2=61, nsba3=53). Note: games_meta has 181 reconciling games; the not-both-zero guard intentionally drops one extra — nsba2 RR10|cryo v caleb, a genuine 0-0 result — leaving 180. This follows the stated guardrail filter exactly. The 12 non-reconciling games (5 inflated real games + corrupted/template/RR11 junk) are excluded. The 4 nsba3 Round-1 games are team-level-only (buzzes recorded but no player names): they contribute to team_season but add zero player rows (240 nsba3 toss-up points live only at team level).

Team-name canonicalization (applied before anything else)

Some teams appear under case/spacing variants that would split their win% and roster (Mount Ellis/mount ellis; no like blue/nolikeblue; nsba3 Chonk/chonk, Zootopia/zootopia). build_master.py folds team names within each season on an alphanumeric-only key and rewrites every team string (in games_meta, tossups_long, bonuses_long, game_player_box) to the most-frequent original spelling for that key. This merged 8 split team-seasons (1 in nsba1, 7 in nsba3), taking team_season from 49 to 41 rows. Conservative: only exact case/whitespace collisions merge; distinct names are never combined.

Identity & value-stat conventions

• Players join via canonical_players.csv on (source='game', season, player_raw=raw_name) → canonical_id (game raw_names are unique within season; all 241 clean-game player-seasons match, 0 losses).
• tossups_faced (the PPTF denominator) = sum of tossups_present for the player's games where the sheet recorded it, plus season_mean_tossups_per_game for each game where it did not. tuh_source flags which case applies. Lineup TUH is on the sheet for all nsba1 games and ~13 nsba2 games; nsba3 (paired layout) records no TUH, so those rows are estimated. PPTF = toss_points / tossups_faced — toss-up points earned per toss-up the player was present for. (For nsba3 this is a games×rate proxy, not exact heard-count.)
• Per-player toss_points reconciles exactly with the buzz-attributed sum in tossups_long for all three seasons (3752 / 3596 / 2596).
• Per-category pts_<subj> come from buzz attribution in tossups_long. Their sum equals toss_points except for 3 player-seasons where ≤8 total points sit on buzzes with a blank/junk subject (nan/en) in the source sheet — a documented residual, not a bug.
• Subject codes follow the games tables: b ch cs ess m p (+ ene = nsba1 legacy Energy). cs is non-null only in nsba2/nsba3; ene only in nsba1.

player_season_master.csv — one row per (canonical_id, season) with GAME data (207 rows)

nsba1=75, nsba2=60, nsba3=72. 181 distinct canonical players (some span seasons).

Combine link: 114 / 207 player-seasons link to combine (nsba1=8/75, nsba2=46/60, nsba3=60/72). nsba1 is low by nature — nsba1 game sheets use nicknames/handles while the combine stores real names, so few resolve. The link is built (a) on unambiguous (season, player_raw) in combine-source canonical rows, then (b) by discord_tag for the 4 nsba2 shared-name pairs whose combine rows were only resolved as game-source rows. Qualitative signals: 17 player-seasons carry a flag (only the 12 nsba4 signal handles that also have game history, spread across their seasons).

team_season.csv — one row per (season, team) (41 rows)

nsba1=12, nsba2=13, nsba3=16.

Validation: each season's games sums to exactly 2× clean games (132/122/106); every player's (season, team) exists in team_season; mean subject_hhi = 0.216.

Regeneration

/home/david/code/nsba/.venv/bin/python scripts/build_master.py

Prints clean-game counts, row counts, combine-link counts, and join-loss accounting.

Draft tables (draft_picks.csv, draft_rosters.csv, draft_eligibility.csv)

Source: data/raw/draft/{nsba1_2022_draft, nsba2_2023_draft, nsba3_2025_tournament_hub}.xlsx. Built by scripts/parse_draft.py. Picks are joined to canonical_players.csv (prefer exact discord-tag match; else name+season fuzzy; cross-season name fallback last). Matches are never forced — unmatched picks keep a blank canonical_id and match_confidence = 0.

draft_picks.csv — one row per draft pick

Round derivation / inferred team counts (documented): - nsba1_2022 — 12 teams, 6 rounds, 72 picks. Clean arithmetic snake: round = ceil(overall_pick / 12). Round 1 = forward captain order, round 2 reverses, etc. - nsba2_2023 — 12 teams, picks 1–61 (#31 vacant → 60 real picks). round = ceil(overall_pick / 12). Team count inferred from distinct teams on the Draft sheet. - nsba3_2025 — 14 teams. Round taken from the authoritative side-grid in the Draft sheet (captains = round 1; numbered picks 1–73 mapped to rounds 2–7 by the grid), NOT pure arithmetic, because trades make the linear numbering non-uniform.

draft_rosters.csv — one row per (season, team)

draft_eligibility.csv — one row per eligible/paid player

Per-season data oddities (recorded as-is, not silently corrected)

• nsba1: no discord tags (nickname/first-name labels only) → name-only fuzzy matching, ~82% matched; 13 unmatched picks are players absent from the canonical nsba1 roster (combine/game/scibowl never captured them). One stray captain label Peter (pick 55, 1 pick) and Eli carrying 7 picks are recorded source quirks (snake-order/trade noise). Cols D/E of the sheet are a separate side pool and are ignored.
• nsba2: pick #31 is vacant (forfeited/skipped — null in both Draft and Picks sheets); one junk row 'ur mom' (no pick/tag/team) dropped. 100% tag-matched.
• nsba3: team nicknames normalized (lagomorphaexe→lagomorphaxae, euna k→polaris, trailing-space c h o n k). fishnu captain (Vishnu Mangipudi, oof7373) patched in from a no-team row. Side grid lists 3 traded round-7 picks (71 Aravind M, 72 Rishabh V, 73 Richard Z) that are absent/incomplete in the linear list — known source gap.

Regeneration

/home/david/code/nsba/.venv/bin/python scripts/parse_draft.py

Prints per-season pick counts, inferred team counts, match rates + methods, per-team pick-count sanity (4–6 expected), the unmatched list, and eligibility totals.

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