AdryanAzul's picture
Publish Football Match Prediction release
72a3423 verified
Raw History Blame Contribute Delete
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.")