""" API-Football data pull — fetches match statistics for all fixtures. Saves to E:\soccer\raw_api\ (never OneDrive, avoids disk-full eviction). Resumes from done_fixtures.txt on restart. """ import requests, pandas as pd, numpy as np, time, os from pathlib import Path from datetime import datetime API_KEY = "81b487be73030f5dec980d2a1ef2c406" HEADERS = {"x-apisports-key": API_KEY} OUT_DIR = Path(r"E:\soccer\raw_api") STATS_CSV = OUT_DIR / "match_stats.csv" DONE_FILE = OUT_DIR / "done_fixtures.txt" FIXTURES_CSV = OUT_DIR / "fixtures_index.csv" FEATURES_CSV = OUT_DIR / "match_features.csv" DAILY_LIMIT = 7400 # leave a buffer below 7500 CHECKPOINT = 500 # save every N requests def get_requests_used(): url = "https://v3.football.api-sports.io/status" try: r = requests.get(url, headers=HEADERS, timeout=10) data = r.json() used = data['response']['requests']['current'] limit = data['response']['requests']['limit_day'] return used, limit except: return 0, 7500 def fetch_stats(fixture_id): url = f"https://v3.football.api-sports.io/fixtures/statistics" r = requests.get(url, headers=HEADERS, params={"fixture": fixture_id}, timeout=15) r.raise_for_status() return r.json().get("response", []) def parse_stats(fixture_id, fixture_row, response): rows = [] for team_data in response: team_name = team_data["team"]["name"] is_home = (team_name == fixture_row["home_team"]) stats = {s["type"]: s["value"] for s in team_data["statistics"]} def get(key, default=None): v = stats.get(key, default) if v is None: return default if isinstance(v, str) and v.endswith("%"): try: return float(v.rstrip("%")) except: return default try: return float(v) except: return default rows.append({ "fixture_id": fixture_id, "date": fixture_row["date"], "div": fixture_row["div"], "season": fixture_row["season"], "team": team_name, "is_home": int(is_home), "home_team": fixture_row["home_team"], "away_team": fixture_row["away_team"], "home_goals": fixture_row["home_goals"], "away_goals": fixture_row["away_goals"], "shots_on_goal": get("Shots on Goal"), "shots_off_goal": get("Shots off Goal"), "total_shots": get("Total Shots"), "blocked_shots": get("Blocked Shots"), "shots_inside": get("Shots insidebox"), "shots_outside": get("Shots outsidebox"), "fouls": get("Fouls"), "corners": get("Corner Kicks"), "offsides": get("Offsides"), "possession": get("Ball Possession"), "yellow_cards": get("Yellow Cards"), "red_cards": get("Red Cards"), "saves": get("Goalkeeper Saves"), "total_passes": get("Total passes"), "accurate_passes": get("Passes accurate"), "pass_pct": get("Passes %"), }) return rows def build_features(df): df = df.copy() df["date"] = pd.to_datetime(df["date"]) df = df.sort_values(["team", "date"]).reset_index(drop=True) stat_cols = ["shots_on_goal","shots_off_goal","total_shots","blocked_shots", "shots_inside","shots_outside","fouls","corners","offsides", "possession","yellow_cards","red_cards","saves", "total_passes","accurate_passes","pass_pct"] # Points df["pts"] = 0 win_mask = ((df["is_home"]==1) & (df["home_goals"] > df["away_goals"])) | \ ((df["is_home"]==0) & (df["away_goals"] > df["home_goals"])) draw_mask = df["home_goals"] == df["away_goals"] df.loc[win_mask, "pts"] = 3 df.loc[draw_mask, "pts"] = 1 df["gf"] = df.apply(lambda r: r["home_goals"] if r["is_home"] else r["away_goals"], axis=1) df["ga"] = df.apply(lambda r: r["away_goals"] if r["is_home"] else r["home_goals"], axis=1) roll_cols = ["pts","gf","ga"] + [c for c in stat_cols if c in df.columns] feat_rows = [] for team, grp in df.groupby("team"): grp = grp.sort_values("date").reset_index(drop=True) for w in [5, 10]: for col in roll_cols: if col in grp.columns: grp[f"api_{col}_w{w}"] = grp[col].shift(1).rolling(w, min_periods=1).mean() feat_rows.append(grp) feat_df = pd.concat(feat_rows, ignore_index=True) feat_cols = ["fixture_id","date","div","season","team","is_home", "home_team","away_team","home_goals","away_goals"] + \ [c for c in feat_df.columns if c.startswith("api_")] return feat_df[[c for c in feat_cols if c in feat_df.columns]] # ── Main ────────────────────────────────────────────────────────────────── print("=== API-Football Data Pull ===\n") print(f"Loading existing fixture index ({FIXTURES_CSV})") index_df = pd.read_csv(FIXTURES_CSV) print(f"\nTotal fixtures in index: {len(index_df):,}") print(index_df["div"].value_counts().to_string()) # Load done set done = set() if DONE_FILE.exists(): with open(DONE_FILE) as f: done = set(int(x) for x in f.read().split() if x.strip()) todo = index_df[~index_df["fixture_id"].isin(done)].reset_index(drop=True) print(f"\n=== Pulling match statistics ===\n") print(f"Fixtures to process: {len(todo):,} (already done: {len(done):,})") if len(todo) == 0: print("All fixtures already pulled!") else: used, limit = get_requests_used() print(f" API requests today: {used}/{limit} ({limit-used} remaining)") existing = pd.read_csv(STATS_CSV) if STATS_CSV.exists() else pd.DataFrame() new_rows = [] requests_today = used done_this_run = 0 fixture_lookup = index_df.set_index("fixture_id").to_dict("index") for i, row in todo.iterrows(): fid = int(row["fixture_id"]) if requests_today >= DAILY_LIMIT: # Wait for reset now = datetime.now() reset_hour = (now.hour // 24 + 1) * 24 wait_secs = max(0, (24 - now.hour) * 3600 - now.minute * 60 - now.second + 60) hrs = wait_secs // 3600; mins = (wait_secs % 3600) // 60 print(f"Approaching daily limit — stopping at {done_this_run} requests today") # Save what we have if new_rows: chunk = pd.DataFrame(new_rows) combined = pd.concat([existing, chunk], ignore_index=True) if len(existing) else chunk combined.to_csv(STATS_CSV, index=False) print(f"\nSaved {len(combined):,} team-match rows to {STATS_CSV}") with open(DONE_FILE, "a") as f: pass # already written incrementally print(f" Daily limit reached. Sleeping {hrs}h {mins}m until quota resets...") time.sleep(wait_secs) used, limit = get_requests_used() requests_today = used print(f" API requests today: {used}/{limit} ({limit-used} remaining)") retries = 0 while retries <= 3: try: response = fetch_stats(fid) requests_today += 1 done_this_run += 1 if response: parsed = parse_stats(fid, fixture_lookup[fid], response) new_rows.extend(parsed) # Mark done with open(DONE_FILE, "a") as f: f.write(f"{fid}\n") done.add(fid) # Checkpoint if done_this_run % CHECKPOINT == 0: chunk = pd.DataFrame(new_rows) combined = pd.concat([existing, chunk], ignore_index=True) if len(existing) else chunk combined.to_csv(STATS_CSV, index=False) existing = combined new_rows = [] print(f" Checkpoint: {len(existing):,} rows saved, {requests_today} requests today") used2, _ = get_requests_used() requests_today = used2 print(f" API requests today: {requests_today}/{limit} ({limit-requests_today} remaining)") time.sleep(1) # 1 req/sec — safe within rate limits break except requests.exceptions.HTTPError as e: status_code = e.response.status_code if e.response is not None else 0 if status_code == 429: wait = 30 * (2 ** retries) print(f" 429 rate limit — sleeping {wait}s (retry {retries+1}/3)") time.sleep(wait) retries += 1 elif status_code == 403: # Fixture not available on this plan — skip silently with open(DONE_FILE, "a") as f: f.write(f"{fid}\n") done.add(fid) done_this_run += 1 time.sleep(1) break else: print(f" Error fixture {fid}: {e}") time.sleep(2) break except Exception as e: print(f" Error fixture {fid}: {e}") time.sleep(2) break # Final save if new_rows: chunk = pd.DataFrame(new_rows) combined = pd.concat([existing, chunk], ignore_index=True) if len(existing) else chunk combined.to_csv(STATS_CSV, index=False) print(f"\nSaved {len(combined):,} team-match rows to {STATS_CSV}") elif len(existing): print(f"\n{len(existing):,} team-match rows already in {STATS_CSV}") print("\n=== Building rolling features ===") stats_df = pd.read_csv(STATS_CSV) feat_df = build_features(stats_df) feat_df.to_csv(FEATURES_CSV, index=False) feat_cols = [c for c in feat_df.columns if c.startswith("api_")] print(f"Features saved: {len(feat_df):,} rows -> {FEATURES_CSV}") print(f"Columns: {feat_cols}")