Download src/extract.py from AdryanAzul/Football-Match-Prediction: direct link, hf CLI and curl.
- Browser
- Download file 4.32 kB
-
https://huggingface.co/AdryanAzul/Football-Match-Prediction/resolve/main/src/extract.py
- Command line
-
hf download hf://AdryanAzul/Football-Match-Prediction/src/extract.py
-
curl -L -o extract.py https://huggingface.co/AdryanAzul/Football-Match-Prediction/resolve/main/src/extract.py
4.32 kB
| import os | |
| import sqlite3 | |
| import zipfile | |
| import pandas as pd | |
| import numpy as np | |
| print("--- INICIANDO PARTE 1: EXTRAÇÃO E ENGENHARIA DE DADOS ---") | |
| # 1. Garante que database.sqlite exista (extraindo de database.zip se necessário) | |
| if not os.path.exists('database.sqlite') and os.path.exists('database.zip'): | |
| print("Extraindo 'database.sqlite' a partir de 'database.zip'...") | |
| with zipfile.ZipFile('database.zip', 'r') as zf: | |
| zf.extract('database.sqlite') | |
| # Conexão ao banco de dados | |
| conn = sqlite3.connect('database.sqlite') | |
| # 2. Query de Habilidades (Rating Médio) | |
| # Criamos dinamicamente as strings para desempilhar (unpivot) os 11 jogadores | |
| home_unpivot = " UNION ALL ".join([f"SELECT match_api_id, home_team_api_id as team_id, home_player_{i} as player_id FROM Match" for i in range(1, 12)]) | |
| away_unpivot = " UNION ALL ".join([f"SELECT match_api_id, away_team_api_id as team_id, away_player_{i} as player_id FROM Match" for i in range(1, 12)]) | |
| query_rating = f""" | |
| WITH AllPlayers AS ( | |
| {home_unpivot} | |
| UNION ALL | |
| {away_unpivot} | |
| ), | |
| PlayerLatestRating AS ( | |
| SELECT player_api_id, AVG(overall_rating) as avg_rating | |
| FROM Player_Attributes | |
| GROUP BY player_api_id | |
| ) | |
| SELECT | |
| ap.match_api_id, | |
| ap.team_id, | |
| AVG(plr.avg_rating) as team_overall_rating | |
| FROM AllPlayers ap | |
| LEFT JOIN PlayerLatestRating plr ON ap.player_id = plr.player_api_id | |
| GROUP BY ap.match_api_id, ap.team_id | |
| """ | |
| print("1/4 Extraindo ratings dos jogadores (isso pode levar alguns segundos)...") | |
| df_ratings = pd.read_sql_query(query_rating, conn) | |
| # 3. Extraindo Partidas (AGORA INCLUINDO SEASON E LEAGUE_ID) | |
| print("2/4 Extraindo histórico de partidas...") | |
| query_matches = """ | |
| SELECT match_api_id, season, league_id, date, home_team_api_id, away_team_api_id, home_team_goal, away_team_goal | |
| FROM Match | |
| ORDER BY date ASC | |
| """ | |
| df_matches = pd.read_sql_query(query_matches, conn) | |
| conn.close() | |
| # 4. Criando Variável Alvo (Target) | |
| print("3/4 Calculando variável alvo e saldo histórico...") | |
| condicoes = [ | |
| (df_matches['home_team_goal'] > df_matches['away_team_goal']), | |
| (df_matches['home_team_goal'] == df_matches['away_team_goal']), | |
| (df_matches['home_team_goal'] < df_matches['away_team_goal']) | |
| ] | |
| df_matches['target'] = np.select(condicoes, [0, 1, 2], default=-1) | |
| # 5. Saldo de Gols Histórico do Confronto Direto (antes de cada partida, orientado ao mandante) | |
| team_a = np.minimum(df_matches['home_team_api_id'], df_matches['away_team_api_id']) | |
| team_b = np.maximum(df_matches['home_team_api_id'], df_matches['away_team_api_id']) | |
| df_matches['matchup'] = list(zip(team_a, team_b)) | |
| df_matches['home_goal_diff'] = df_matches['home_team_goal'] - df_matches['away_team_goal'] | |
| is_team_a_home = df_matches['home_team_api_id'] == team_a | |
| canonical_diff = np.where(is_team_a_home, df_matches['home_goal_diff'], -df_matches['home_goal_diff']) | |
| df_matches['_canonical_diff'] = canonical_diff | |
| # Soma acumulada apenas dentro do mesmo confronto, excluindo a partida atual | |
| cum_before = df_matches.groupby('matchup')['_canonical_diff'].cumsum() - df_matches['_canonical_diff'] | |
| df_matches['hist_goal_diff'] = np.where(is_team_a_home, cum_before, -cum_before) | |
| # 6. Mesclando médias aos jogos | |
| print("4/4 Mesclando dados e limpando tabela final...") | |
| df_matches = df_matches.merge(df_ratings, left_on=['match_api_id', 'home_team_api_id'], right_on=['match_api_id', 'team_id'], how='left')\ | |
| .rename(columns={'team_overall_rating': 'home_overall_rating'}).drop('team_id', axis=1) | |
| df_matches = df_matches.merge(df_ratings, left_on=['match_api_id', 'away_team_api_id'], right_on=['match_api_id', 'team_id'], how='left')\ | |
| .rename(columns={'team_overall_rating': 'away_overall_rating'}).drop('team_id', axis=1) | |
| # Selecionando colunas finais (Com season e league_id fundamentais para a Parte 3) | |
| df_final = df_matches[['match_api_id', 'season', 'league_id', 'date', 'home_team_api_id', 'away_team_api_id', | |
| 'home_overall_rating', 'away_overall_rating', 'hist_goal_diff', 'target']] | |
| # 7. Salvando arquivo base | |
| df_final.to_csv('dados_preparados.csv', index=False) | |
| print("\n=== SUCESSO! ===") | |
| print("Arquivo 'dados_preparados.csv' gerado com as novas colunas.") |