--- title: Fix nflfastR Kickoff Convention Swap for Score, EPA/WPA, and Probability Fields type: task status: completed description: >- Complete the incomplete posteam convention swap for kickoff plays in the nflfastR import -- timeout, score, EPA/WPA, and probability fields need swapping/negation to match the pos_team=kicking team convention, affecting ~76K plays across all years. base_uri: user:task/league/fix-nflfastr-kickoff-pos-team.md created_at: '2026-03-17T23:13:21.087Z' entity_id: 84bb2431-8d62-434a-bc97-3e7d3ef8fa0e finished_at: '2026-04-05T19:13:04.908Z' observations: [] owner_identity_uri: █████████████████████████ priority: Medium public_read: true relations: - subtask_of [[██████████████████████-███-██████-███████]] started_at: '2026-04-05T18:56:26.318Z' tags: - user:tag/league-xo-football.md updated_at: '2026-04-05T19:13:04.908Z' visibility_analyzed_at: '2026-11-05T23:49:25.698Z' --- # Implementation Plan: Fix nflfastR Kickoff Field Convention Swap ## Overview - Complete the incomplete posteam convention swap for kickoff plays in the nflfastR import script - The `pos_team`/`off`/`def` swap was implemented in commit `b0c35a21` (2025-11-19), but timeout, score, EPA/WPA, and probability fields were never adjusted - Affects ~76K kickoff plays across all years (2000-2025) and corrupts team EPA aggregations downstream ## Background - nflfastR consistently uses `posteam = receiving team` on kickoffs; the league database convention is `pos_team = kicking team` - `get_offensive_team()` at `scripts/import-plays-nflfastr.mjs:55-58` correctly swaps `pos_team`/`off`/`def` for kickoffs - Five formatter functions (`format_epa_data`, `format_wpa_data`, `format_timeout_data`, `format_score_data`, `format_probability_data`) pass through raw nflfastR values without adjusting for the kickoff convention - Confirmed by data: kickoff return TDs show `epa = +6-7` attributed to the kicking team (should be negative) - Team EPA aggregations in `team-stats-from-plays-column-definitions.mjs` include kickoff plays without filtering, so kicking teams get credited with the receiving team's EPA - The play_changelog records 179 `pos_team` corrections (146 on kickoff plays) from when the original swap was deployed ## Design - Add a `is_kickoff` flag check to each affected formatter function, following the pattern in `get_offensive_team()` - Score/timeout fields: swap paired values (pos <-> def) on kickoffs - EPA/WPA/EP fields: negate posteam-relative values on kickoffs so positive = good for kicking team - `wp`: convert via `1 - wp` to flip from receiving team's win probability to kicking team's - Probability fields: swap paired own/opp fields on kickoffs - Home/away-anchored fields are unaffected and left as-is - Backfill existing data via SQL UPDATE, then run full nflfastR re-import for all historical years with `--ignore_nflfastr_field_conflicts` to ensure consistency - No downstream query changes needed -- fixing the data at the source makes all aggregations correct ## Notes - The `--ignore_nflfastr_field_conflicts` flag overwrites only nflfastR-exclusive fields, which is appropriate since the affected fields (EPA, WPA, probabilities, scores, timeouts) are all nflfastR-sourced - SQL backfill runs first to immediately correct the data; the re-import then validates and fills any gaps - `ep_succ` is derived from `success` in nflfastR (EPA > 0 from receiving team perspective) -- after negation, re-derive as `epa > 0` - Fields safe to skip (home/away-anchored): `total_home_epa`, `total_away_epa`, `home_wp`, `away_wp`, `vegas_home_wp`, `vegas_home_wpa`, and all `total_home_*`/`total_away_*` cumulative fields - The NFL v1 import (`import-plays-nfl-v1.mjs`) already has its own kickoff swap at lines 173-184; it does not import EPA/WPA fields so no changes needed there ## Plan ### Import Script Fix - [x] **Add kickoff detection to `format_score_data()`** (line 533) - Swap `posteam_score` <-> `defteam_score` when `play.play_type === 'kickoff'` - Swap `posteam_score_post` <-> `defteam_score_post` - Negate `score_differential` and `score_differential_post` - [x] **Add kickoff detection to `format_timeout_data()`** (line 524) - Swap `posteam_timeouts_remaining` <-> `defteam_timeouts_remaining` when `play.play_type === 'kickoff'` - `home_to_rem`, `away_to_rem`, `to`, `to_team` are unaffected - [x] **Add kickoff detection to `format_epa_data()`** (line 449) - Negate: `epa`, `ep`, `qb_epa`, `air_epa`, `yac_epa`, `comp_air_epa`, `comp_yac_epa`, `xyac_epa` - Re-derive `ep_succ` after negation (nflfastR `success` = EPA > 0 from receiving team perspective) - Skip (home/away-anchored): all `total_home_*` and `total_away_*` fields - [x] **Add kickoff detection to `format_wpa_data()`** (line 475) - Negate: `wpa`, `vegas_wpa`, `air_wpa`, `yac_wpa`, `comp_air_wpa`, `comp_yac_wpa` - Convert `wp`: use `1 - wp` to flip to kicking team perspective - Convert `vegas_wp`: use `1 - vegas_wp` - Skip (home/away-anchored): `home_wp`, `away_wp`, `home_wp_post`, `away_wp_post`, `vegas_home_wp`, `vegas_home_wpa`, all `total_home_*`/`total_away_*` fields - [x] **Add kickoff detection to `format_probability_data()`** (line 544) - Swap pairs: `fg_prob` <-> `opp_fg_prob`, `td_prob` <-> `opp_td_prob`, `safety_prob` <-> `opp_safety_prob` - Skip (symmetric/not applicable on kickoffs): `no_score_prob`, `extra_point_prob`, `two_conv_prob`, `xpass_prob`, `pass_oe`, `cp`, `cpoe` ### Data Backfill - [x] **Write SQL backfill script for kickoff plays** - Target all kickoff plays: `WHERE play_type = 'KOFF'` (database uses uppercase play type codes) - Swap score pairs: `SET pos_score = def_score, def_score = pos_score` (same for `_post` variants) - Swap timeout pairs: `SET pos_to_rem = def_to_rem, def_to_rem = pos_to_rem` - Negate EPA fields: `SET epa = -epa, ep = -ep, qb_epa = -qb_epa` etc. - Re-derive `ep_succ` from negated `epa` - Negate WPA fields: `SET wpa = -wpa, vegas_wpa = -vegas_wpa` etc. - Convert win probability: `SET wp = 1 - wp, vegas_wp = 1 - vegas_wp` - Negate score differentials: `SET score_diff = -score_diff, score_diff_post = -score_diff_post` - Swap probability pairs: `SET fg_prob = opp_fg_prob, opp_fg_prob = fg_prob` etc. - Verify partitioned table behavior -- test on a single year partition first - [x] **Run SQL backfill** - Snapshot sample kickoff plays before (return TDs, touchbacks) for validation - Execute the backfill SQL - Verify return TD plays now have negative EPA, correct score attribution ### Historical Re-Import - [x] **Re-import all historical years via nflfastR** - Run `node scripts/import-plays-nflfastr.mjs --all --ignore_nflfastr_field_conflicts -d` to force-download and overwrite nflfastR-exclusive fields - This validates the backfill and fills any gaps where the SQL missed edge cases - Monitor for conflicts logged during import ### Validation - [x] **Verify correctness after re-import** - Check kickoff return TDs have negative EPA (bad for kicking team) - Check touchbacks have near-zero or slightly positive EPA (neutral/good for kicking team) - Verify `pos_score`/`def_score` match expected teams on a sample of kickoff plays - Verify team EPA aggregations shift in the expected direction - Cross-check a few plays against raw nflfastR CSV data to confirm transformation accuracy