Recipe: 24-State Run Expectancy (RE24)

import retrosheetpy as rs

# Download and load 2024 play-by-play events
rs.to_duckdb("retro.duckdb", years=[2024], datasets=["events"])

import duckdb

con = duckdb.connect("retro.duckdb")

query = """
WITH half_innings AS (
    -- Total runs scored in each half-inning
    SELECT
        GAME_ID,
        INN_CT,
        BAT_HOME_ID,
        MAX(CASE WHEN BAT_HOME_ID = 0 THEN AWAY_SCORE_CT ELSE HOME_SCORE_CT END) -
        MIN(CASE WHEN BAT_HOME_ID = 0 THEN AWAY_SCORE_CT ELSE HOME_SCORE_CT END) AS runs_in_half_inning
    FROM events
    GROUP BY GAME_ID, INN_CT, BAT_HOME_ID
),
play_states AS (
    -- Base runners state encoded as string: '---', '1--', '-2-', '12-', '123', etc.
    SELECT
        e.GAME_ID,
        e.INN_CT,
        e.BAT_HOME_ID,
        e.OUTS_CT,
        CONCAT(
            CASE WHEN e.BASE1_RUN_ID IS NOT NULL AND e.BASE1_RUN_ID != '' THEN '1' ELSE '-' END,
            CASE WHEN e.BASE2_RUN_ID IS NOT NULL AND e.BASE2_RUN_ID != '' THEN '2' ELSE '-' END,
            CASE WHEN e.BASE3_RUN_ID IS NOT NULL AND e.BASE3_RUN_ID != '' THEN '3' ELSE '-' END
        ) AS base_state,
        -- Runs scored from this point forward until 3 outs
        h.runs_in_half_inning - (CASE WHEN e.BAT_HOME_ID = 0 THEN e.AWAY_SCORE_CT ELSE e.HOME_SCORE_CT END) AS future_runs
    FROM events e
    JOIN half_innings h
      ON e.GAME_ID = h.GAME_ID
     AND e.INN_CT = h.INN_CT
     AND e.BAT_HOME_ID = h.BAT_HOME_ID
)
SELECT
    base_state,
    ROUND(AVG(CASE WHEN OUTS_CT = 0 THEN future_runs END), 3) AS "0 Outs",
    ROUND(AVG(CASE WHEN OUTS_CT = 1 THEN future_runs END), 3) AS "1 Out",
    ROUND(AVG(CASE WHEN OUTS_CT = 2 THEN future_runs END), 3) AS "2 Outs",
    COUNT(*) AS total_plays
FROM play_states
GROUP BY base_state
ORDER BY "0 Outs" DESC;
"""

re24_table = con.execute(query).fetchdf()
print(re24_table)