#!/usr/bin/env python3 """ Compute Player Period Splits from Quarter Stats Derives: 2H vs 1H performance, Q4 usage, ball-handler proxy, clutch efficiency Answers: "Curry 2H shooting?" "Luka Q4 usage?" "Who's the primary handler?" """ import psycopg2 from datetime import datetime def load_db_url(): with open('/var/www/html/eventheodds/.env', 'r') as f: for line in f: if line.startswith('SPORTS_DATABASE_URL='): return line.split('=', 1)[1].strip().split('?')[0] return '' def compute_player_period_splits(): conn = psycopg2.connect(load_db_url()) cur = conn.cursor() # Create PlayerPeriodSplits table cur.execute(''' CREATE TABLE IF NOT EXISTS "PlayerPeriodSplits" ( id SERIAL PRIMARY KEY, league VARCHAR(20) NOT NULL, season VARCHAR(20), "playerExternalId" VARCHAR(50), "playerName" VARCHAR(200), team VARCHAR(10), "totalGames" INTEGER DEFAULT 0, -- First half stats "firstHalfPts" NUMERIC, "firstHalfFgPct" NUMERIC, "firstHalfAst" NUMERIC, "firstHalfTo" NUMERIC, -- Second half stats "secondHalfPts" NUMERIC, "secondHalfFgPct" NUMERIC, "secondHalfAst" NUMERIC, "secondHalfTo" NUMERIC, -- Q4 specific "q4Pts" NUMERIC, "q4Fga" NUMERIC, "q4FgPct" NUMERIC, "q4Usage" NUMERIC, "q4AstToRatio" NUMERIC, -- Clutch specific "clutchPts" NUMERIC, "clutchFga" NUMERIC, "clutchFgPct" NUMERIC, "clutchAst" NUMERIC, "clutchTo" NUMERIC, -- Differentials "secondHalfDiff" NUMERIC, "q4Diff" NUMERIC, "clutchDiff" NUMERIC, -- Ball-handler proxy (assists + TOs = high touch rate) "ballHandlerScore" NUMERIC, "ballHandlerClassification" VARCHAR(30), -- Classifications "secondHalfType" VARCHAR(30), "clutchType" VARCHAR(30), "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT NOW(), UNIQUE(league, "playerExternalId", team, season) ) ''') conn.commit() print("PlayerPeriodSplits table created/verified") # Check if we have quarter stats cur.execute('SELECT COUNT(*) FROM "PlayerQuarterStats"') quarter_count = cur.fetchone()[0] if quarter_count == 0: print(" No PlayerQuarterStats data yet. Run scrape_nba_pbp.py first.") cur.close() conn.close() return print(f"\nComputing period splits from {quarter_count} quarter stat records...") cur.execute(''' WITH aggregated AS ( SELECT 'nba' as league, EXTRACT(YEAR FROM "gameDate")::text as season, "playerExternalId", MAX("playerName") as player_name, MAX(team) as team, COUNT(*) as games, -- First half AVG("q1Pts" + "q2Pts") as first_half_pts, CASE WHEN SUM("q1Fga" + "q2Fga") > 0 THEN SUM("q1Fgm" + "q2Fgm")::numeric / SUM("q1Fga" + "q2Fga") ELSE NULL END as first_half_fg_pct, AVG("q1Ast" + "q2Ast") as first_half_ast, AVG("q1To" + "q2To") as first_half_to, -- Second half AVG("q3Pts" + "q4Pts") as second_half_pts, CASE WHEN SUM("q3Fga" + "q4Fga") > 0 THEN SUM("q3Fgm" + "q4Fgm")::numeric / SUM("q3Fga" + "q4Fga") ELSE NULL END as second_half_fg_pct, AVG("q3Ast" + "q4Ast") as second_half_ast, AVG("q3To" + "q4To") as second_half_to, -- Q4 AVG("q4Pts") as q4_pts, AVG("q4Fga") as q4_fga, CASE WHEN SUM("q4Fga") > 0 THEN SUM("q4Fgm")::numeric / SUM("q4Fga") ELSE NULL END as q4_fg_pct, AVG("q4Fga" + "q4Fta" * 0.44 + "q4To") as q4_usage, CASE WHEN SUM("q4To") > 0 THEN SUM("q4Ast")::numeric / SUM("q4To") ELSE NULL END as q4_ast_to, -- Clutch AVG("clutchPts") as clutch_pts, AVG("clutchFga") as clutch_fga, CASE WHEN SUM("clutchFga") > 0 THEN SUM("clutchFgm")::numeric / SUM("clutchFga") ELSE NULL END as clutch_fg_pct, AVG("clutchAst") as clutch_ast, AVG("clutchTo") as clutch_to, -- Ball handler score (assists + TOs per game = touches) AVG("q1Ast" + "q2Ast" + "q3Ast" + "q4Ast" + "q1To" + "q2To" + "q3To" + "q4To") as handler_score FROM "PlayerQuarterStats" GROUP BY "playerExternalId", EXTRACT(YEAR FROM "gameDate") HAVING COUNT(*) >= 5 ) INSERT INTO "PlayerPeriodSplits" (league, season, "playerExternalId", "playerName", team, "totalGames", "firstHalfPts", "firstHalfFgPct", "firstHalfAst", "firstHalfTo", "secondHalfPts", "secondHalfFgPct", "secondHalfAst", "secondHalfTo", "q4Pts", "q4Fga", "q4FgPct", "q4Usage", "q4AstToRatio", "clutchPts", "clutchFga", "clutchFgPct", "clutchAst", "clutchTo", "secondHalfDiff", "q4Diff", "clutchDiff", "ballHandlerScore", "ballHandlerClassification", "secondHalfType", "clutchType") SELECT league, season, "playerExternalId", player_name, team, games, ROUND(first_half_pts::numeric, 1), ROUND(first_half_fg_pct::numeric, 3), ROUND(first_half_ast::numeric, 1), ROUND(first_half_to::numeric, 1), ROUND(second_half_pts::numeric, 1), ROUND(second_half_fg_pct::numeric, 3), ROUND(second_half_ast::numeric, 1), ROUND(second_half_to::numeric, 1), ROUND(q4_pts::numeric, 1), ROUND(q4_fga::numeric, 1), ROUND(q4_fg_pct::numeric, 3), ROUND(q4_usage::numeric, 1), ROUND(q4_ast_to::numeric, 2), ROUND(clutch_pts::numeric, 1), ROUND(clutch_fga::numeric, 1), ROUND(clutch_fg_pct::numeric, 3), ROUND(clutch_ast::numeric, 1), ROUND(clutch_to::numeric, 1), -- Differentials (2H - 1H) ROUND((second_half_pts - first_half_pts)::numeric, 1), ROUND((q4_pts - (first_half_pts + second_half_pts) / 4)::numeric, 1), ROUND((clutch_pts - (first_half_pts + second_half_pts) / 8)::numeric, 1), -- Ball handler ROUND(handler_score::numeric, 1), CASE WHEN handler_score >= 8 THEN 'PRIMARY_HANDLER' WHEN handler_score >= 5 THEN 'SECONDARY_HANDLER' WHEN handler_score >= 2 THEN 'OCCASIONAL_HANDLER' ELSE 'OFF_BALL' END, -- Classifications CASE WHEN second_half_pts > first_half_pts + 2 THEN 'SECOND_HALF_SCORER' WHEN first_half_pts > second_half_pts + 2 THEN 'FAST_STARTER' ELSE 'CONSISTENT' END, CASE WHEN clutch_pts >= 2 AND clutch_fg_pct >= 0.45 THEN 'CLUTCH_PERFORMER' WHEN clutch_pts >= 1.5 AND clutch_fg_pct < 0.35 THEN 'CLUTCH_CHUCKER' WHEN clutch_fga < 1 THEN 'CLUTCH_PASSIVE' ELSE 'CLUTCH_NEUTRAL' END FROM aggregated ON CONFLICT (league, "playerExternalId", team, season) DO UPDATE SET "totalGames" = EXCLUDED."totalGames", "firstHalfPts" = EXCLUDED."firstHalfPts", "secondHalfPts" = EXCLUDED."secondHalfPts", "q4Pts" = EXCLUDED."q4Pts", "q4Usage" = EXCLUDED."q4Usage", "clutchPts" = EXCLUDED."clutchPts", "clutchFgPct" = EXCLUDED."clutchFgPct", "secondHalfDiff" = EXCLUDED."secondHalfDiff", "ballHandlerScore" = EXCLUDED."ballHandlerScore", "ballHandlerClassification" = EXCLUDED."ballHandlerClassification", "secondHalfType" = EXCLUDED."secondHalfType", "clutchType" = EXCLUDED."clutchType", "updatedAt" = NOW() ''') count = cur.rowcount conn.commit() print(f" Computed {count} player period split records") # Summary stats cur.execute(''' SELECT "ballHandlerClassification", COUNT(*), ROUND(AVG("ballHandlerScore")::numeric, 1) as avg_score, ROUND(AVG("q4Usage")::numeric, 1) as avg_q4_usage FROM "PlayerPeriodSplits" WHERE "ballHandlerClassification" IS NOT NULL GROUP BY "ballHandlerClassification" ORDER BY COUNT(*) DESC ''') print("\nBall Handler Classification Distribution:") for row in cur.fetchall(): print(f" {row[0]}: {row[1]} players (avg score: {row[2]}, avg Q4 usage: {row[3]})") cur.execute(''' SELECT "secondHalfType", COUNT(*), ROUND(AVG("secondHalfDiff")::numeric, 1) as avg_diff FROM "PlayerPeriodSplits" WHERE "secondHalfType" IS NOT NULL GROUP BY "secondHalfType" ORDER BY COUNT(*) DESC ''') print("\nSecond Half Type Distribution:") for row in cur.fetchall(): print(f" {row[0]}: {row[1]} players (avg 2H diff: {row[2]:+.1f})") # Top Q4 performers cur.execute(''' SELECT "playerName", team, "q4Pts", "q4Usage", "clutchPts", "ballHandlerClassification" FROM "PlayerPeriodSplits" WHERE "q4Usage" >= 4 AND "totalGames" >= 10 ORDER BY "q4Pts" DESC LIMIT 10 ''') results = cur.fetchall() if results: print("\nTop Q4 Scorers (min 10 games, 4+ Q4 usage):") for row in results: print(f" {row[0]} ({row[1]}): {row[2]:.1f} Q4 pts, {row[3]:.1f} usage, {row[4]:.1f} clutch - {row[5]}") cur.execute('SELECT COUNT(*) FROM "PlayerPeriodSplits"') total = cur.fetchone()[0] cur.close() conn.close() print(f"\nPlayerPeriodSplits complete: {total} records") if __name__ == '__main__': compute_player_period_splits()