def get_mutual_matches(home_team, away_team, date):
query = f"select home_team, home_score, away_team, away_score \
from match \
where date < '{date}' AND \
((home_team='{home_team}' AND away_team='{away_team}') OR (home_team='{away_team}' AND away_team='{home_team}'));"
with get_connection() as conn:
return pd.read_sql(query, conn)
def append_player_data(df):
dataset = []
for i in range(df.shape[0]):
row = df.iloc[i].to_dict()
for prefix in ["home", "away"]:
team_key = f"{prefix}_team"
query = get_player_attribute_query(row[team_key], row["date"])
with get_connection() as conn:
df_player_attribute = pd.read_sql(query, conn)
df_player_attribute = df_player_attribute.drop(
['id', 'date', 'nationality'], axis=1)
if prefix == "home":
new_names = [(i, "home_" + i)
for i in df_player_attribute.columns.values]
else:
new_names = [(i, "away_" + i)
for i in df_player_attribute.columns.values]
df_player_attribute.rename(columns=dict(new_names), inplace=True)
if df_player_attribute.shape[0] > 0:
player_dict = df_player_attribute.iloc[0].to_dict()
row = {**row, **player_dict}
dataset.append(row)
return pd.DataFrame(dataset)
def get_mutual_matches_between_dates(home_team, away_team, start, end):
query = f"select home_team, home_score, away_team, away_score \
from match \
where date > '{start}' AND date < '{end}' AND \
(home_team='{home_team}' OR away_team='{away_team}' OR home_team='{away_team}' OR away_team='{home_team}');"
with get_connection() as conn:
return pd.read_sql(query, conn)
def import_player_attributes(
filename='data/generated/team_level_player_data_daily.csv'):
player_stats = pd.read_csv(filename)
player_stats = player_stats.drop(['Unnamed: 0'], axis=1)
with get_connection() as conn:
player_stats.to_sql('player_attribute',
con=conn,
index=True,
index_label='id')
def import_match_results(filename='data/original/results.csv'):
match_results = pd.read_csv(filename)
match_results['date'] = pd.to_datetime(match_results['date'],
format='%Y-%m-%d')
match_results["year"] = match_results["date"].dt.year
match_results["simulation"] = False
match_results = match_results.reset_index()
match_results = match_results.drop(['neutral', 'index'], axis=1)
with get_connection() as conn:
match_results.to_sql('match',
con=conn,
index=False,
if_exists='append')
def get_match_elo_and_goal():
query = '''SELECT
m.*,
home_e.elo as "home_elo",
away_e.elo as "away_elo",
goal.goal_diff_with_away as "goal_diff_with_away",
goal.home_goals_with_away as "home_goals_with_away",
goal.away_goals_with_home as "away_goals_with_home",
goal.home_goal_mean as "home_goal_mean",
goal.away_goal_mean as "away_goal_mean"
FROM match AS m
LEFT JOIN elo_rating AS home_e
ON home_e.match_id=m.id AND home_e.team=m.home_team
LEFT JOIN elo_rating AS away_e
ON away_e.match_id=m.id AND away_e.team=m.away_team
LEFT JOIN goal_feature AS goal
ON goal.match_id=m.id
WHERE m.date >= '2006-08-30'
ORDER BY date;'''
with get_connection() as conn:
return pd.read_sql(query, conn)
def get_simulation_results():
simulations = pd.read_sql_query("""select * from match_simulation;""",
get_connection())
return simulations
def store_simulation_results(filename):
simulations = pd.read_sql_query("""select * from match_simulation;""",
get_connection())
simulations.to_csv(filename)