""" Backfill missing ftr / result_home / result_away in live_data CSVs using results from league_data (football-data.co.uk) CSVs. Usage: python _backfill_ftr.py (dry-run, prints what would change) python _backfill_ftr.py --write (actually writes updated CSVs) """ import sys, os, unicodedata import pandas as pd from pathlib import Path from datetime import datetime # ── Paths ──────────────────────────────────────────────────────────────── LIVE_DIR = Path(r"E:\soccer\live_data") LEAGUE_DIR = Path(r"E:\soccer\league_data") # ── Import team-name translation from the codebase ────────────────────── sys.path.insert(0, r"E:\soccer") from main import maps # loads team_map.json → maps.fdco_name() # Local FDCO overrides (from daily_signals.py), for names not yet in team_map.json FDCO_MAP = { 'Manchester United': 'Man United', 'Manchester City': 'Man City', 'Nottingham Forest': "Nott'm Forest", 'Newcastle United': 'Newcastle', 'Wolverhampton Wanderers': 'Wolves', 'Wolverhampton': 'Wolves', 'Leeds United': 'Leeds', 'West Ham United': 'West Ham', 'Sheffield Wednesday': 'Sheffield Weds', 'Oxford United': 'Oxford', 'West Bromwich Albion': 'West Brom', 'West Bromwich': 'West Brom', 'Peterborough United': 'Peterboro', 'Peterborough': 'Peterboro', 'Wimbledon': 'AFC Wimbledon', 'Eintracht Frankfurt': 'Ein Frankfurt', 'FC Köln': 'FC Koln', 'Borussia Dortmund': 'Dortmund', 'Borussia Mönchengladbach': "M'gladbach", 'Mönchengladbach': "M'gladbach", "FC St. Pauli": 'St Pauli', 'St. Pauli': 'St Pauli', 'Fortuna Düsseldorf': 'Fortuna Dusseldorf', 'Fortuna Dusseldorf': 'Fortuna Dusseldorf', 'Schalke 04': 'Schalke 04', '1. FC Nürnberg': 'Nurnberg', 'Nürnberg': 'Nurnberg', '1. FC Kaiserslautern': 'Kaiserslautern', 'Holstein Kiel': 'Holstein Kiel', 'Karlsruher SC': 'Karlsruhe', 'SV Elversberg': 'Elversberg', 'Athletic Club': 'Ath Bilbao', 'Athletic Bilbao': 'Ath Bilbao', 'Atlético Madrid': 'Ath Madrid', 'Atletico Madrid': 'Ath Madrid', 'Real Betis': 'Betis', 'Celta Vigo': 'Celta', 'Rayo Vallecano': 'Vallecano', 'Real Sociedad': 'Sociedad', 'Espanyol': 'Espanol', 'Deportivo de La Coruña': 'La Coruna', 'Deportivo de la Coruna': 'La Coruna', 'Deportivo La Coruna': 'La Coruna', 'Real Sociedad B': 'Sociedad B', 'Sporting Gijón': 'Sp Gijon', 'Sporting Gijon': 'Sp Gijon', 'AC Milan': 'Milan', 'Hellas Verona': 'Verona', 'Südtirol': 'Sudtirol', 'SS Juve Stabia': 'Juve Stabia', 'Paris Saint-Germain': 'Paris SG', 'PSG': 'Paris SG', 'Stade Brest': 'Brest', 'Stade Rennais FC': 'Rennes', 'Stade Rennais': 'Rennes', 'RC Strasbourg': 'Strasbourg', 'Strasbourg Alsace': 'Strasbourg', 'Saint-Étienne': 'St Etienne', 'AS Saint-Étienne': 'St Etienne', 'Fortuna Sittard': 'For Sittard', 'PSV Eindhoven': 'PSV Eindhoven', 'PSV': 'PSV Eindhoven', 'AZ Alkmaar': 'AZ Alkmaar', 'AZ': 'AZ Alkmaar', 'Go Ahead Eagles': 'Go Ahead Eagles', 'NAC Breda': 'NAC Breda', 'FC Utrecht': 'Utrecht', 'FC Groningen': 'Groningen', 'NEC Nijmegen': 'Nijmegen', 'FC Twente': 'Twente', 'Sparta Rotterdam': 'Sparta Rotterdam', 'Heracles Almelo': 'Heracles', 'Sporting CP': 'Sp Lisbon', 'SC Braga': 'Sp Braga', 'Braga': 'Sp Braga', 'Gil Vicente': 'Gil Vicente', 'Vicente Barcelos': 'Gil Vicente', 'Estrela Amadora': 'Estrela', 'Santa Clara': 'Santa Clara', 'Santa Clara Azores': 'Santa Clara', 'FC Porto': 'Porto', 'SL Benfica': 'Benfica', 'Göztepe': 'Goztep', 'Goztepe': 'Goztep', 'Goztepe Izmir': 'Goztep', 'İstanbul Başakşehir': 'Buyuksehyr', 'Istanbul Basaksehir': 'Buyuksehyr', 'Basaksehir': 'Buyuksehyr', 'Kocaeli': 'Kocaelispor', 'Olympiacos': 'Olympiakos', 'Aris Thessaloniki': 'Aris', 'AEK Athens': 'AEK', 'Panaitolikos': 'Panetolikos', 'Levadiakos': 'Levadeiakos', 'OFI Crete': 'OFI Crete', 'Heart of Midlothian': 'Hearts', 'St. Mirren': 'St Mirren', 'Dundee FC': 'Dundee', # Additional overrides for names seen in live_data 'Paris': 'Paris FC', # live_data "Paris" = Paris FC, NOT PSG 'Atromitos Athinon': 'Atromitos', 'Greuther Furth': 'Greuther Furth', 'VfL Bochum': 'Bochum', 'Eintracht Braunschweig': 'Braunschweig', 'Arminia Bielefeld': 'Bielefeld', 'Dynamo Dresden': 'Dresden', 'Sheffield Utd': 'Sheffield United', 'Los Angeles Galaxy': 'LA Galaxy', 'Seattle Sounders': 'Seattle Sounders', 'San Jose Earthquakes': 'San Jose Earthquakes', 'Portland Timbers': 'Portland Timbers', 'Sporting Kansas City': 'Sporting Kansas City', 'Vancouver Whitecaps': 'Vancouver Whitecaps', 'Houston Dynamo': 'Houston Dynamo', 'Linlithgow Rose': 'Linlithgow', 'Brora Rangers': 'Brora', 'Den Bosch': 'Den Bosch', 'De Graafschap': 'De Graafschap', 'Almere City FC': 'Almere City', 'Las Vegas Lights': 'Las Vegas Lights', 'Colorado Springs': 'Colorado Springs', 'Phoenix Rising': 'Phoenix Rising', 'Sacramento Republic': 'Sacramento Republic', 'FC Tulsa': 'FC Tulsa', } def fdco_name(api_name): """Translate API-Football name to football-data.co.uk name. Checks local FDCO_MAP first (has specific overrides), then maps.py, then as-is.""" if api_name in FDCO_MAP: return FDCO_MAP[api_name] mapped = maps.fdco_name(api_name) if mapped != api_name: return mapped return api_name def _fold(s): """Lowercase + strip accents for fuzzy matching.""" if not isinstance(s, str): return '' nfkd = unicodedata.normalize('NFKD', s) return ''.join(c for c in nfkd if not unicodedata.combining(c)).lower().strip() # ── Load league_data into a lookup dict ────────────────────────────────── def load_league_results(): """Load all league_data CSVs into a dict keyed by (div, date_str, home_folded, away_folded). Returns dict -> (FTHG, FTAG, FTR).""" results = {} for csv_path in sorted(LEAGUE_DIR.glob("*_2526.csv")): div = csv_path.stem.rsplit('_', 1)[0] # e.g. "D1" try: df = pd.read_csv(csv_path, encoding='utf-8-sig') except Exception as e: print(f" Warning: could not read {csv_path.name}: {e}") continue if 'FTR' not in df.columns or 'Date' not in df.columns: continue for _, row in df.iterrows(): if pd.isna(row.get('FTR')) or pd.isna(row.get('Date')): continue # Parse date (dd/mm/yyyy) to yyyy-mm-dd try: dt = datetime.strptime(str(row['Date']).strip(), '%d/%m/%Y') date_str = dt.strftime('%Y-%m-%d') except Exception: continue h = _fold(str(row['HomeTeam'])) a = _fold(str(row['AwayTeam'])) fthg = int(row['FTHG']) if pd.notna(row.get('FTHG')) else None ftag = int(row['FTAG']) if pd.notna(row.get('FTAG')) else None ftr = str(row['FTR']).strip() results[(div, date_str, h, a)] = (fthg, ftag, ftr) return results def find_result(league_results, div, game_date, home_api, away_api): """Try to find the game result in league_data. Tries exact div match first, then all divs for that country prefix.""" home_fdco = _fold(fdco_name(home_api)) away_fdco = _fold(fdco_name(away_api)) # Direct match on div + date + teams key = (div, game_date, home_fdco, away_fdco) if key in league_results: return league_results[key] # Try all divs for the same country (e.g. SP1, SP2 — in case league code differs) # Country prefix is the letters before the digit prefix = ''.join(c for c in div if c.isalpha()) for (d, dt, h, a), val in league_results.items(): if dt == game_date and d.startswith(prefix) and h == home_fdco and a == away_fdco: return val # Fuzzy: same date, either div, substring match on team names for (d, dt, h, a), val in league_results.items(): if dt != game_date: continue if not d.startswith(prefix): continue # Check if the fdco-translated names are substrings of each other if (home_fdco in h or h in home_fdco) and (away_fdco in a or a in away_fdco): return val return None def process_file(csv_path, league_results, write_mode): """Process one live_data CSV. Returns (filled_count, unfilled_list, corrupt_removed).""" df = pd.read_csv(csv_path, encoding='utf-8') game_date = csv_path.stem # e.g. "2026-04-26" fname = csv_path.name # Ensure columns exist for col in ['ftr', 'result_home', 'result_away']: if col not in df.columns: df[col] = None orig_len = len(df) # ── Remove corrupt blank-name rows ─────────────────────────────────── blank_mask = df['home'].isna() | (df['home'].astype(str).str.strip() == '') # Also catch rows where 'home' is a number (e.g. "0.19") def is_bogus_name(val): if pd.isna(val): return True s = str(val).strip() if s == '': return True try: float(s) return True # a number, not a team name except ValueError: return False bogus_mask = df['home'].apply(is_bogus_name) corrupt_count = bogus_mask.sum() if corrupt_count > 0: df = df[~bogus_mask].reset_index(drop=True) # ── Find rows missing ftr ──────────────────────────────────────────── missing_mask = df['ftr'].isna() if not missing_mask.any() and corrupt_count == 0: return 0, [], 0, False # Get unique (home, away) pairs that are missing missing_games = df.loc[missing_mask, ['home', 'away', 'league']].drop_duplicates(['home', 'away']) filled = 0 unfilled = [] for _, row in missing_games.iterrows(): home = row['home'] away = row['away'] league = row['league'] result = find_result(league_results, league, game_date, home, away) if result is not None: fthg, ftag, ftr = result game_mask = (df['home'] == home) & (df['away'] == away) df.loc[game_mask, 'ftr'] = ftr if fthg is not None: df.loc[game_mask, 'result_home'] = fthg if ftag is not None: df.loc[game_mask, 'result_away'] = ftag filled += 1 print(f" [filled] {home} vs {away} ({league}) -> {ftr} {fthg}-{ftag}") else: unfilled.append(f"{home} vs {away} ({league})") changed = filled > 0 or corrupt_count > 0 if write_mode and changed: df.to_csv(csv_path, index=False, encoding='utf-8') return filled, unfilled, corrupt_count, changed def main(): write_mode = '--write' in sys.argv if write_mode: print("=== WRITE MODE — files will be updated ===\n") else: print("=== DRY RUN — pass --write to actually update files ===\n") print("Loading league_data results...") league_results = load_league_results() print(f" Loaded {len(league_results)} game results from league_data/\n") # Process only files that contain the dates with known missing results target_dates = [ '2026-04-26', '2026-05-02', '2026-05-09', '2026-05-10', '2026-05-12', '2026-05-16', '2026-05-17', '2026-05-23', '2026-05-30', ] total_filled = 0 total_unfilled = [] total_corrupt = 0 files_changed = 0 for dt in target_dates: csv_path = LIVE_DIR / f"{dt}.csv" if not csv_path.exists(): print(f"[{dt}.csv] — file not found, skipping") continue print(f"[{dt}.csv]") filled, unfilled, corrupt, changed = process_file(csv_path, league_results, write_mode) total_filled += filled total_unfilled.extend([(dt, u) for u in unfilled]) total_corrupt += corrupt if changed: files_changed += 1 if corrupt: print(f" [corrupt] removed {corrupt} blank/bogus rows") if not unfilled and not filled and not corrupt: print(f" (no missing ftr)") print() # ── Summary ────────────────────────────────────────────────────────── print("=" * 60) print(f"Games filled: {total_filled}") print(f"Corrupt rows removed:{total_corrupt}") print(f"Files changed: {files_changed}") print(f"Still unfilled: {len(total_unfilled)}") if total_unfilled: print("\nUnfilled games (no match in league_data):") for dt, desc in total_unfilled: print(f" {dt}: {desc}") if not write_mode and (total_filled > 0 or total_corrupt > 0): print(f"\nRe-run with --write to apply changes.") if __name__ == '__main__': main()