File size: 4,320 Bytes
72a3423
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
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.")