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.")