Detailed Player-Based Model Analysis

        from dotenv import load_dotenv
        import os
        import pandas as pd
        from sqlalchemy import create_engine
        from sklearn.linear_model import LogisticRegression, LogisticRegressionCV
        from sklearn.model_selection import train_test_split
        from sklearn.preprocessing import StandardScaler, PolynomialFeatures
        from sklearn.metrics import accuracy_score, roc_auc_score, confusion_matrix
        import numpy as np
        import re
    

# helper function to sort rounds
def get_round_order(row):
    # Handle missing/null values
    if not row['round'] or pd.isnull(row['round']):
        return None
    # Match regular rounds: 'R1', 'R23', etc.
    match = re.match(r'R(\d+)$', row['round'])
    if match:
        return int(match.group(1))
    # Finals 
    finals_order = {
        'REF': 100,  # Elimination Final (week 1)
        'RQF': 101,  # Qualifying Final (week 1)
        'RSF': 102,  # Semi Final (week 2)
        'RPF': 103,  # Preliminary Final (week 3)
        'RGF': 104,  # Grand Final (week 4),
    }
    code = row['round'][1:]  # Removes leading "R"
    return finals_order.get(code, 999)
    

h_cutoff = 195
def assign_position_group(row):
    pos = row['position'].lower().replace(' ', '') if row['position'] else ''
    if 'defender' in pos:
        if row['height_cm'] is not None and row['height_cm'] >= 195:
            return ['tall_defender']
        elif row['height_cm'] is not None and row['height_cm'] < 195:
            return ['small_defender']
        else:
            return ['small_defender']  # fallback if height unknown
    if pos == 'midfield,forward':
        return ['midfield,forward']
    return pos.split(',') if pos else []
    

# Add in win/loss helper function
def get_team_win(row):
    if row['team_id'] == row['home_team_id']:
        return 1 if row['home_result'] == 'W' else 0
    elif row['team_id'] == row['away_team_id']:
        return 1 if row['away_result'] == 'W' else 0
    else:
        return None
    

# Load in the tables
players = pd.read_sql('SELECT id as player_id, full_name, height_cm, position FROM players', engine)
player_game_stats = pd.read_sql('SELECT * FROM player_game_stats', engine)
games = pd.read_sql('SELECT id AS game_id, season_year, round, game_date, home_team_id, away_team_id, home_result, away_result FROM games', engine)
games['round_number'] = games.apply(get_round_order, axis=1)

# Clean position data, with midfield,forward being its own group
def custom_position_split(pos_str):
    if not pos_str or pd.isnull(pos_str):
        return []
    pos_str = pos_str.lower().replace(' ', '')
    if pos_str == 'midfield,forward':
        return ['midfield,forward']
    return pos_str.split(',')

players['position_group'] = players.apply(assign_position_group, axis=1)
players_exploded = players.explode('position_group')
players_exploded = players_exploded[players_exploded['position_group'] != '']

# Merge tables to get results
pgs = player_game_stats.merge(
    games[['game_id', 'home_team_id', 'away_team_id', 'home_result', 'away_result']],
    on='game_id', how='left'
)
# Add in the positions from the position data above
pgs = pgs.merge(
    players_exploded[['player_id', 'position_group']],
    on='player_id', how='left'
)

# Add in a couple ratios: % of disposals that are kicks, % of disposals that are contested
pgs['pct_contested'] = pgs['contested_possessions'] / pgs['disposals']
pgs['pct_kicks'] = pgs['kicks'] / pgs['disposals']

# Avoid any 0 disposal games
pgs.loc[pgs['disposals'] == 0, 'pct_contested'] = None
pgs.loc[pgs['disposals'] == 0, 'pct_kicks'] = None

# Create the stats column
stat_cols = [
    'kicks', 'marks', 'handballs', 'goals', 'behinds', 'hit_outs', 'tackles',
    'rebounds', 'inside_50', 'clearances', 'clangers', 'frees_for', 'frees_against',
    'contested_possessions', 'uncontested_possessions', 'contested_marks', 'marks_inside_50',
    'one_percenters', 'bounces', 'goal_assists', 'pct_contested', 'pct_kicks'
]
    

# Build out a dataframe
positions = pgs['position_group'].unique()
correlation_results = []

for pos in positions:
    pgs_pos = pgs[pgs['position_group'] == pos]
    if len(pgs_pos) < 30:
        continue  
    for stat in stat_cols:
        if stat in pgs_pos.columns:
            vals = pgs_pos[stat].dropna()
            if len(vals.unique()) > 1:
                corr = pgs_pos[stat].corr(pgs_pos['won'])
                correlation_results.append({'position_group': pos, 'stat': stat, 'correlation': corr, 'n_games': len(pgs_pos)})

cor_df = pd.DataFrame(correlation_results)
cor_df = cor_df.sort_values(['position_group', 'correlation'], ascending=[True, False])

display(cor_df)
    

# For each role conduct logistic regression
roles = ['forward', 'midfield', 'midfield,forward', 'ruck', 'tall_defender', 'small_defender']

# Dictionary to store coefficients for each role
role_coef_dict = {}

for role in roles:
    pgs_role = pgs[pgs['position_group'] == role].dropna(subset=stat_cols + ['won'])

    if len(pgs_role) < 50:
        print(f"Skipping {role} (not enough samples: {len(pgs_role)})")
        continue

    X = pgs_role[stat_cols].fillna(0)
    y = pgs_role['won']

    # Standardize
    scaler = StandardScaler()
    X_scaled = scaler.fit_transform(X)

    # Logistic regression
    model = LogisticRegression(max_iter=1000)
    model.fit(X_scaled, y)

    coefs = pd.Series(model.coef_[0], index=stat_cols)
    coefs = coefs.sort_values(key=np.abs, ascending=False)
  
    top = coefs.head(25)  # Change to view top #s
    role_coef_dict[role] = top

    print(f"\n{role.upper()} TOP COEFFICIENTS:\n")
    display(top)
    

    pgs_period = pgs.merge(
    games[['game_id', 'season_year', 'round_number', 'game_date']],
    on='game_id', how='left'
)


# Filter for the target period: 2022 to round 9, 2025
pgs_period = pgs_period[
    ((pgs_period['season_year'] > 2021) & 
    ((pgs_period['season_year'] < 2025) | 
     ((pgs_period['season_year'] == 2025) & (pgs_period['round_number'] <= 9))))
]

roles = ['forward', 'midfield', 'midfield,forward', 'ruck', 'tall_defender', 'small_defender']

top_players_per_role = {}
role_data_dict = {}
print(f"Number of games in dataset: {len(pgs_period)}")

for role in roles:
    coefs = role_coef_dict[role]
    stat_cols_role = list(coefs.index)

    # Subset for position and drop missing
    df_role = pgs_period[pgs_period['position_group'] == role].copy()
    df_role = df_role.dropna(subset=stat_cols_role)
    if len(df_role) < 10:
        print(f"Skipping {role} (not enough samples: {len(df_role)})")
        continue

    # Merge in full_name for later lookup
    df_role = df_role.merge(players[['player_id', 'full_name', 'height_cm']], on='player_id', how='left')

    # Standardize stats using mean and std from THIS PERIOD
    scaler = StandardScaler()
    X_scaled = scaler.fit_transform(df_role[stat_cols_role])
    df_role_std = pd.DataFrame(X_scaled, columns=stat_cols_role, index=df_role.index)

    # Calculate impact score for each game
    df_role['impact_score'] = df_role_std.mul(coefs, axis=1).sum(axis=1)

    # Aggregate by player (average impact_score, games played)
    player_scores = (
        df_role.groupby('player_id')
        .agg(
            impact_score=('impact_score', 'mean'),
            games_played=('impact_score', 'count')
        )
        .reset_index()
        .merge(players[['player_id', 'full_name', 'height_cm']], on='player_id', how='left')
        .sort_values('impact_score', ascending=False)
    )

    # Put in minimum games of 15
    player_scores = player_scores[player_scores['games_played'] > 15]
    player_scores = player_scores.sort_values('impact_score', ascending=False)

    print(f"\nTop 5 {role.upper()}S:")
    display(player_scores.head(5)[['full_name', 'games_played', 'impact_score']])
    players_to_filter = 5
    top_players_per_role[role] = player_scores.head(players_to_filter)  #
    role_data_dict[role] = df_role.copy()

# Polynomial regression analysis with L1 regularisation
roles = ['forward', 'midfield', 'midfield,forward', 'ruck', 'tall_defender', 'small_defender']

poly_results_dict = {}

for role in roles:
    df = pgs_period[pgs_period['position_group'] == role].dropna(subset=stat_cols + ['won']).copy()
    if len(df) < 50:
        print(f"Skipping {role}: not enough samples ({len(df)})")
        continue

    X_raw = df[stat_cols]
    y = df['won']

    # Standardise
    scaler = StandardScaler()
    X_scaled = scaler.fit_transform(X_raw)

    # Polynomial features (degree=2 includes squares and pairwise interactions)
    poly = PolynomialFeatures(degree=2, include_bias=False, interaction_only=False)
    X_poly = poly.fit_transform(X_scaled)
    poly_feature_names = poly.get_feature_names_out(stat_cols)

    # Fit logistic regression
    model = LogisticRegressionCV(
        Cs=10,
        penalty='l1',
        solver='saga',      
        max_iter=5000,
        cv=5,
        scoring='accuracy',
        n_jobs=-1
    )
    model.fit(X_poly, y)

    # Coefficients as series
    coefs = pd.Series(model.coef_[0], index=poly_feature_names)
    coefs = coefs[coefs != 0].sort_values(key=np.abs, ascending=False)
    poly_results_dict[role] = coefs

    print(f"\n{role.upper()} – TOP POLYNOMIAL COEFFICIENTS")
    display(coefs.head(20))
    

        # Looking at top players to sense check model
top_players_per_role = {}

# Filter for the target period: 2022 to round 9, 2025
"""
pgs_period = pgs_period[
    ((pgs_period['season_year'] > 2021) & 
    ((pgs_period['season_year'] < 2025) | 
     ((pgs_period['season_year'] == 2025) & (pgs_period['round_number'] <= 9))))
]
"""


for role in roles:
    coefs = poly_results_dict.get(role)
    if coefs is None or len(coefs) == 0:
        print(f"Skipping {role}: no coefficients found")
        continue
    poly_feature_names = list(coefs.index)
    
    # Subset for the role and time period, drop NaNs on original stats
    df_role = pgs_period[pgs_period['position_group'] == role].dropna(subset=stat_cols).copy()
    if len(df_role) < 10:
        print(f"Skipping {role}: not enough samples ({len(df_role)})")
        continue

    # Standardize and create polynomial features
    scaler = StandardScaler()
    X_scaled = scaler.fit_transform(df_role[stat_cols])
    poly = PolynomialFeatures(degree=2, include_bias=False, interaction_only=False)
    X_poly = poly.fit_transform(X_scaled)
    poly_feature_names_all = poly.get_feature_names_out(stat_cols)
    X_poly_df = pd.DataFrame(X_poly, columns=poly_feature_names_all, index=df_role.index)

    # Only keep the features that survived L1 regularization
    X_poly_selected = X_poly_df[poly_feature_names]

    # Calculate impact score
    impact_score = X_poly_selected.mul(coefs, axis=1).sum(axis=1)
    df_role['impact_score'] = impact_score

    # Merge in player info
    df_role = df_role.merge(players[['player_id', 'full_name', 'height_cm']], on='player_id', how='left')

    # Group by player and get top 10 (with >= 10 games)
    player_scores = (
        df_role.groupby('player_id')
        .agg(
            impact_score=('impact_score', 'mean'),
            games_played=('impact_score', 'count')
        )
        .reset_index()
        .merge(players[['player_id', 'full_name', 'height_cm']], on='player_id', how='left')
        .sort_values('impact_score', ascending=False)
    )
    player_scores = player_scores[player_scores['games_played'] >= 10]
    # print(f"Number of games in dataset: {len(pgs_period)}")
    top_players_per_role[role] = player_scores.head(10)
    print(f"\nTop 10 {role.upper()}S:")
    display(player_scores.head(5)[['full_name', 'games_played', 'impact_score']])
    

# add in impact depending on the model type
model_type = "logistic"  # choose logistic or poly

impact_rows = []

for role in roles:
    if model_type == "logistic":
        coefs = role_coef_dict.get(role)
        if coefs is None or len(coefs) == 0:
            continue
        stat_cols_role = list(coefs.index)

        df_role = pgs_period[pgs_period['position_group'] == role].copy()
        df_role = df_role.dropna(subset=stat_cols_role)
        if df_role.empty:
            continue

        scaler = StandardScaler()
        X_scaled = scaler.fit_transform(df_role[stat_cols_role])
        df_role_std = pd.DataFrame(X_scaled, columns=stat_cols_role, index=df_role.index)
        df_role['impact_score'] = df_role_std.mul(coefs, axis=1).sum(axis=1)

    elif model_type == "poly":
        coefs = poly_results_dict.get(role)
        if coefs is None or len(coefs) == 0:
            continue
        stat_cols_poly = list(coefs.index)

        # Use original stat_cols for polynomial expansion
        df_role = pgs_period[pgs_period['position_group'] == role].copy()
        df_role = df_role.dropna(subset=stat_cols)
        if df_role.empty:
            continue

        scaler = StandardScaler()
        X_scaled = scaler.fit_transform(df_role[stat_cols])

        poly = PolynomialFeatures(degree=2, include_bias=False, interaction_only=False)
        X_poly = poly.fit_transform(X_scaled)
        poly_feature_names = poly.get_feature_names_out(stat_cols)
        X_poly_df = pd.DataFrame(X_poly, columns=poly_feature_names, index=df_role.index)

        # Only use features selected by L1 regularization (nonzero coefficients)
        X_poly_selected = X_poly_df[stat_cols_poly]
        df_role['impact_score'] = X_poly_selected.mul(coefs, axis=1).sum(axis=1)

    else:
        raise ValueError(f"Unknown model_type: {model_type}")

    impact_rows.append(df_role)

# Concatenate all roles back into a single DataFrame
pgs_period_with_score = pd.concat(impact_rows).sort_values(['player_id', 'game_date'])

# Start to build out team model
pgs_period_with_score = pgs_period_with_score.sort_values(['player_id', 'game_date'])

# Get the rolling game average of players with a minimum period of games
rolling_games = 30
minimum_games = 5
pgs_period_with_score['impact_rolling'] = (
    pgs_period_with_score.groupby('player_id')['impact_score']
    .transform(lambda x: x.shift(1).rolling(window=rolling_games, min_periods=minimum_games).mean())
)

# Find the position average for when players don't meet 15 games with a 10% penalty so the player is treated as slightly below average
replacement_factor = 0.9 
pos_avg = (
    pgs_period_with_score.groupby('position_group')['impact_score']
    .mean()
    .mul(replacement_factor)
    .rename('pos_avg_score')
    .reset_index()
)

# Drop in case of a rerun (solves a bug)
if 'pos_avg_score' in pgs_period_with_score.columns:
    pgs_period_with_score = pgs_period_with_score.drop(columns=['pos_avg_score'])

# Merge position scores into pgs_period
pgs_period_with_score = pgs_period_with_score.merge(pos_avg, on='position_group', how='left')
pgs_period_with_score['impact_for_model'] = pgs_period_with_score['impact_rolling'].fillna(pgs_period_with_score['pos_avg_score'])

# Merge into teams
pgs_period_merge = pgs_period_with_score[['player_id', 'game_id', 'team_id', 'impact_for_model']]
team_game_impacts = (
    pgs_period_merge.groupby(['game_id', 'team_id'])['impact_for_model']
    .mean()  # or sum(), depending on your philosophy
    .reset_index()
)

# Merge team impacts into games model
games_model = games.merge(
    team_game_impacts.rename(columns={'team_id': 'home_team_id', 'impact_for_model': 'home_team_impact'}),
    on=['game_id', 'home_team_id'], how='left'
).merge(
    team_game_impacts.rename(columns={'team_id': 'away_team_id', 'impact_for_model': 'away_team_impact'}),
    on=['game_id', 'away_team_id'], how='left'
)
games_model['impact_diff'] = games_model['home_team_impact'] - games_model['away_team_impact']
games_model['winner'] = (games_model['home_result'] == 'W').astype(int)

# Build out the model to predict winners
df = games_model.dropna(subset=['home_team_impact', 'away_team_impact', 'impact_diff', 'winner']).copy()

X = df[['impact_diff']]
y = df['winner']

# split out training data
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, shuffle=True, random_state=42)

player_impact_model = LogisticRegression(max_iter=1000)
player_impact_model.fit(X_train, y_train)

y_pred = player_impact_model.predict(X_test)
y_prob = player_impact_model.predict_proba(X_test)[:, 1]

print(f"Accuracy: {accuracy_score(y_test, y_pred):.3f}")
print(f"ROC AUC: {roc_auc_score(y_test, y_prob):.3f}")
print("Confusion matrix:")
print(confusion_matrix(y_test, y_pred))