"""Pre-match features for every match in matches_joined.csv (ML experiment). Run after ml_join.py, with the isolated env: E:\\soccer\\ml_env\\Scripts\\python.exe research\\ml_features.py Every feature is known BEFORE kickoff. Team form is built from each club's previous matches only (shift, then exponentially weighted), across divisions, so a promoted club carries its lower-division form with it. Groups (column prefix): elo_ pre-match Elo (ml_elo.py) h_/a_ home/away team form, EWM half-life 5 matches: goals, shots, shots on target, corners, points, and Understat xG/npxG/PPDA/deep completions where the league has them; plus rest days bk_ bookmaker implied probabilities, de-vigged: market-average opening and closing, Pinnacle closing, and the open->close move k_ Kalshi: de-vigged mid at KO-24h / -6h / -1h / -15m / KO, the ask and bid at KO (what a bet would actually pay), volume and open interest tv_ Transfermarkt squad market value: top-18 / top-11 players by value on the club's books at the last month-end before the match (first tiers only; valuations frozen 2026-07-06) sf_ SoFIFA squad ratings from the latest snapshot on or before the match (added once that download finishes) Writes research/ml_data/features.csv. """ from pathlib import Path import numpy as np import pandas as pd ML = Path(r'E:\soccer\research\ml_data') HALF_LIFE = 5 K_OFFSETS = {'24h': pd.Timedelta(hours=24), '6h': pd.Timedelta(hours=6), '1h': pd.Timedelta(hours=1), '15m': pd.Timedelta(minutes=15), 'ko': pd.Timedelta(0)} def devig(df, cols): # a few files carry stray text ('#') in odds cells; treat it as missing inv = 1 / df[cols].apply(pd.to_numeric, errors='coerce') return inv.div(inv.sum(axis=1), axis=0) def team_form(m): """Long table (one row per team per match) -> EWM of previous matches.""" home = pd.DataFrame({'mid': m.index, 'date': m.Date, 'team': m.HomeTeam, 'home': 1, 'gf': m.FTHG, 'ga': m.FTAG, 'sf': m.HS, 'sa': m.AS, 'stf': m.HST, 'sta': m.AST, 'cf': m.HC, 'ca': m.AC, 'xgf': m.us_home_xg, 'xga': m.us_away_xg, 'npxgf': m.us_home_np_xg, 'npxga': m.us_away_np_xg, 'ppda': m.us_home_ppda, 'ppda_opp': m.us_away_ppda, 'deepf': m.us_home_deep_completions, 'deepa': m.us_away_deep_completions}) away = pd.DataFrame({'mid': m.index, 'date': m.Date, 'team': m.AwayTeam, 'home': 0, 'gf': m.FTAG, 'ga': m.FTHG, 'sf': m.AS, 'sa': m.HS, 'stf': m.AST, 'sta': m.HST, 'cf': m.AC, 'ca': m.HC, 'xgf': m.us_away_xg, 'xga': m.us_home_xg, 'npxgf': m.us_away_np_xg, 'npxga': m.us_home_np_xg, 'ppda': m.us_away_ppda, 'ppda_opp': m.us_home_ppda, 'deepf': m.us_away_deep_completions, 'deepa': m.us_home_deep_completions}) t = pd.concat([home, away]).sort_values(['team', 'date', 'mid']).reset_index(drop=True) t['pts'] = np.select([t.gf > t.ga, t.gf == t.ga], [3, 1], 0) stats = ['gf', 'ga', 'sf', 'sa', 'stf', 'sta', 'cf', 'ca', 'pts', 'xgf', 'xga', 'npxgf', 'npxga', 'ppda', 'ppda_opp', 'deepf', 'deepa'] g = t.groupby('team', sort=False) for c in stats: # shift(1): only matches BEFORE this one. ewm skips NaN (a league # without Understat simply leaves the xG columns empty). t[f'f_{c}'] = g[c].transform(lambda s: s.shift(1).ewm(halflife=HALF_LIFE, min_periods=3).mean()) t['f_n'] = g.cumcount() # matches seen so far t['f_rest'] = g['date'].diff().dt.days.clip(upper=30) feat = [f'f_{c}' for c in stats] + ['f_n', 'f_rest'] h = t[t.home == 1].set_index('mid')[feat].add_prefix('h_') a = t[t.home == 0].set_index('mid')[feat].add_prefix('a_') return h.join(a) def bookmaker(m): out = pd.DataFrame(index=m.index) for name, cols in {'avgc': ['AvgCH', 'AvgCD', 'AvgCA'], 'avgo': ['AvgH', 'AvgD', 'AvgA'], 'psc': ['PSCH', 'PSCD', 'PSCA'], 'b365c': ['B365CH', 'B365CD', 'B365CA']}.items(): p = devig(m, cols) out[[f'bk_{name}_h', f'bk_{name}_d', f'bk_{name}_a']] = p.to_numpy() for s in 'hda': out[f'bk_move_{s}'] = out[f'bk_avgc_{s}'] - out[f'bk_avgo_{s}'] out['bk_over25_c'] = devig(m, ['AvgC>2.5', 'AvgC<2.5']).iloc[:, 0] out['bk_ahc_line'] = m['AHCh'] return out def squad_value(f): """Transfermarkt squad value per club at month-end snapshots. A player counts for the club named on his latest valuation as of the snapshot; valuations are revised a few times a year, so month-ends are finer than the data itself. """ v = pd.read_csv(ML / 'transfermarkt' / 'player_valuations.csv.gz', usecols=['player_id', 'date', 'market_value_in_eur', 'current_club_id'], parse_dates=['date']).sort_values('date') snaps = [] for me in pd.date_range('2019-06-30', '2026-07-31', freq='ME'): cur = v[v.date <= me].groupby('player_id').tail(1) cur = cur[cur.date > me - pd.Timedelta(days=550)] # retired / gone vals = cur.sort_values('market_value_in_eur', ascending=False).groupby('current_club_id') snaps.append(pd.DataFrame({ 'snap': me, 'top18': vals.market_value_in_eur.apply(lambda x: x.head(18).sum()), 'top11': vals.market_value_in_eur.apply(lambda x: x.head(11).sum()), 'n': vals.size()}).reset_index()) sn = pd.concat(snaps).rename(columns={'current_club_id': 'club'}).sort_values('snap') out = pd.DataFrame(index=f.index) for side, col in (('h', 'tm_home_club_id'), ('a', 'tm_away_club_id')): q = f[[col, 'Date']].dropna().rename(columns={col: 'club'}) q['club'] = q.club.astype(int) q = q.reset_index().sort_values('Date') j = pd.merge_asof(q, sn, left_on='Date', right_on='snap', by='club', direction='backward') j = j.set_index('index') out[f'tv_{side}_top18'] = np.log10(j.top18.clip(lower=1e5)) out[f'tv_{side}_top11'] = np.log10(j.top11.clip(lower=1e5)) out['tv_diff18'] = out.tv_h_top18 - out.tv_a_top18 return out _STOP = {'fc', 'cf', 'afc', 'sc', 'ac', 'club', 'de', 'cd', 'ud', 'sd', 'rc', 'ca', 'sv', 'vfb', 'vfl', 'tsg', 'fk', 'sk', 'as', 'ss', 'us', 'ssc', 'the', 'football', 'calcio', 'kv', 'krc', 'rsc', 'kaa', 'kv', 'bk', 'if', 'spor', 'kulubu', '1'} def _norm(x): import unicodedata x = unicodedata.normalize('NFKD', str(x)).encode('ascii', 'ignore').decode().lower() x = ''.join(ch if ch.isalnum() else ' ' for ch in x) return ' '.join(t for t in x.split() if t not in _STOP) def _sim(a, b): from difflib import SequenceMatcher a, b = _norm(a), _norm(b) if not a or not b: return 0.0 if a in b or b in a: return 0.95 return SequenceMatcher(None, a, b).ratio() def sofifa_team_map(sf, spine): """SoFIFA (league, team_id) -> league_data name, per division. SoFIFA has no fixtures, so co-occurrence cannot work. Instead, within each division, SoFIFA clubs and the clubs league_data has in that division within a year of the snapshots are paired one-to-one (Hungarian assignment) by the best name similarity over every alias the other sources gave us (Understat / Transfermarkt / Kalshi names from name_map.csv, and team_map.json). Pairs below 0.6 similarity are dropped and printed. """ import json from scipy.optimize import linear_sum_assignment alias = {} nm = pd.read_csv(ML / 'name_map.csv') nm = nm[nm.ok] for fd, a, b in zip(nm.fd_name, nm.src_name, nm.get('src_label', nm.src_name)): alias.setdefault(fd, {fd}).update(x for x in (a, b) if isinstance(x, str) and not x.isdigit()) tmap = json.loads(Path(r'E:\soccer\data\team_map.json').read_text(encoding='utf-8')) for k, v in tmap.items(): if isinstance(v, dict) and v.get('fdco'): alias.setdefault(v['fdco'], {v['fdco']}).update([k] + list(v.get('aliases') or [])) out, dropped = {}, [] for div, g in sf.groupby('fdco_league'): lo, hi = g.update_dt.min() - pd.Timedelta(days=365), g.update_dt.max() + pd.Timedelta(days=365) sp = spine[(spine.Div == div) & spine.Date.between(lo, hi)] fd = sorted(set(sp.HomeTeam) | set(sp.AwayTeam)) teams = g.drop_duplicates('team_id')[['team_id', 'team_name']].to_numpy() if not fd or not len(teams): continue S = np.array([[max(_sim(tn, a) for a in alias.get(f, {f})) for f in fd] for _, tn in teams]) r, c = linear_sum_assignment(-S) for i, j in zip(r, c): if S[i, j] >= 0.6: out[(div, teams[i][0])] = fd[j] else: dropped.append((div, teams[i][1], fd[j], round(S[i, j], 2))) if dropped: print(f'sofifa: {len(dropped)} clubs left unmapped (best pair below 0.6):') for d in dropped: print(' ', d) return out def _money(x): x = str(x).replace('€', '').strip() mult = 1e6 if x.endswith('M') else 1e3 if x.endswith('K') else 1 try: return float(x.rstrip('MK')) * mult except ValueError: return np.nan def sofifa(f, spine): """SoFIFA squad ratings from the latest snapshot on or before each match.""" path = ML / 'sofifa_player_ratings.csv' if not path.exists(): return pd.DataFrame(index=f.index) sf = pd.read_csv(path) sf['update_dt'] = pd.to_datetime(sf.update_date, format='%b %d, %Y') mp = sofifa_team_map(sf, spine) sf['fd'] = [mp.get((d, t)) for d, t in zip(sf.fdco_league, sf.team_id)] sf = sf[sf.fd.notna()].copy() sf['val'] = sf.vl.map(_money) pos = sf.positions.fillna('').str.split().str[0] sf['line'] = np.select([pos == 'GK', pos.isin(['CB', 'LB', 'RB', 'LWB', 'RWB']), pos.isin(['CDM', 'CM', 'CAM', 'LM', 'RM'])], ['gk', 'def', 'mid'], 'att') rows = [] for (dt_, fd), g in sf.groupby(['update_dt', 'fd']): g = g.sort_values('oa', ascending=False) top = lambda line, n: g[g.line == line].oa.head(n).mean() rows.append({'snap': dt_, 'team': fd, 'top11': g.oa.head(11).mean(), 'depth': g.oa.iloc[11:18].mean(), 'gk': top('gk', 1), 'def': top('def', 4), 'mid': top('mid', 4), 'att': top('att', 3), 'age11': g.head(11).ae.mean(), 'pot11': g.head(11).pt.mean(), 'val18': np.log10(max(g.val.head(18).sum(), 1e5))}) snap = pd.DataFrame(rows).sort_values('snap') out = pd.DataFrame(index=f.index) for side, col in (('h', 'HomeTeam'), ('a', 'AwayTeam')): q = f[['Date', col]].rename(columns={col: 'team'}).reset_index().sort_values('Date') j = pd.merge_asof(q, snap, left_on='Date', right_on='snap', by='team', direction='backward', tolerance=pd.Timedelta(days=120)).set_index('index') for c in ('top11', 'depth', 'gk', 'def', 'mid', 'att', 'age11', 'pot11', 'val18'): out[f'sf_{side}_{c}'] = j[c] out['sf_diff11'] = out.sf_h_top11 - out.sf_a_top11 return out def kalshi(m): """Mid / bid / ask per side at fixed offsets before kickoff. A candle is stamped with the END of its period, so the bar stamped at KO covers the minute before kickoff. Bars exist only where the book existed; the value at time t is the last bar at or before t (a quiet book simply kept its quote), but never older than 3h for the 1-minute offsets or 12h for KO-24h, so a stale quote from the day before cannot pose as current. """ km = m[m.kalshi_event.notna()] if km.empty: return pd.DataFrame(index=m.index) need = set(km.kalshi_tk_home) | set(km.kalshi_tk_tie) | set(km.kalshi_tk_away) def load(name): # The 1-minute file is ~750MB across all 38 series; read it in chunks # and keep only the tickers of matches that joined, then index by # ticker once (filtering the whole table per match took hours). parts = [c[c.ticker.isin(need)] for c in pd.read_csv( ML / 'kalshi_history' / name, chunksize=2_000_000, usecols=['ticker', 'ts_utc', 'bid_close', 'ask_close', 'volume', 'open_interest'])] d = pd.concat(parts) d['ts'] = pd.to_datetime(d.ts_utc, utc=True).dt.tz_convert(None).astype('int64') // 10**9 d = d.sort_values(['ticker', 'ts']) return {tk: (g.ts.to_numpy(), g[['bid_close', 'ask_close', 'volume', 'open_interest']].to_numpy(float)) for tk, g in d.groupby('ticker', sort=False)} c1, ch = load('candles_1m.csv'), load('candles_1h.csv') empty = (np.array([], dtype='int64'), np.empty((0, 4))) def last_at(series, t, tol): ts, v = series i = np.searchsorted(ts, t, side='right') - 1 return v[i] if i >= 0 and ts[i] > t - tol else None rows = [] for mid, r in km.iterrows(): ko = int(pd.Timestamp(r.kalshi_kickoff_utc).timestamp()) rec = {'mid': mid} for side, tk in (('h', r.kalshi_tk_home), ('d', r.kalshi_tk_tie), ('a', r.kalshi_tk_away)): b1, bh = c1.get(tk, empty), ch.get(tk, empty) for lab, off in K_OFFSETS.items(): t = ko - int(off.total_seconds()) x = last_at(bh, t, 12 * 3600) if lab == '24h' else last_at(b1, t, 3 * 3600) if x is not None: bid, ask = x[0], x[1] rec[f'k_mid_{lab}_{side}'] = (bid + ask) / 2 if lab == 'ko': rec[f'k_bid_{side}'], rec[f'k_ask_{side}'] = bid, ask rec[f'k_oi_{side}'] = x[3] ts, v = b1 pre = (ts <= ko) & (ts > ko - 3 * 3600) rec[f'k_vol3h_{side}'] = v[pre, 2].sum() if pre.any() else np.nan rows.append(rec) k = pd.DataFrame(rows).set_index('mid') for lab in K_OFFSETS: # de-vig the mids cols = [f'k_mid_{lab}_{s}' for s in 'hda'] if all(c in k for c in cols): tot = k[cols].sum(axis=1) k[f'k_overround_{lab}'] = tot - 1 for s in 'hda': k[f'k_p_{lab}_{s}'] = k[f'k_mid_{lab}_{s}'] / tot return k def main(): m = pd.read_csv(ML / 'matches_joined.csv', parse_dates=['Date'], low_memory=False) base = m[['Div', 'season', 'Date', 'Time', 'HomeTeam', 'AwayTeam', 'FTHG', 'FTAG', 'FTR', 'elo_home', 'elo_away', 'elo_diff', 'elo_exp_home', 'kalshi_event', 'kalshi_kickoff_utc', 'tm_game_id', 'tm_home_club_id', 'tm_away_club_id']].copy() f = base.join(team_form(m)).join(bookmaker(m)) f = f.join(squad_value(f)) f = f.join(sofifa(f, m)) k = kalshi(m) f = f.join(k) f.to_csv(ML / 'features.csv', index=False) kw = f[f.Date >= '2025-05-01'] print(f'features: {len(f)} rows x {f.shape[1]} cols -> {ML / "features.csv"}') print(f'since 2025-05-01: {len(kw)} matches, {kw.kalshi_event.notna().sum()} with Kalshi, ' f'{kw.get("k_p_ko_h", pd.Series(dtype=float)).notna().sum()} with a Kalshi price at KO') print('\nfill rate of key features since 2025-05-01:') for c in ['elo_diff', 'h_f_sf', 'h_f_xgf', 'tv_diff18', 'sf_diff11', 'bk_avgc_h', 'bk_psc_h', 'k_p_24h_h', 'k_p_1h_h', 'k_p_ko_h', 'k_ask_h']: if c in kw: print(f' {c:12} {kw[c].notna().mean():6.1%}') if __name__ == '__main__': main()