def create_pre_match_table(gamedate, serie, team, homeaway, c, conn):
#print(team)
base_table = []
full_data = []
home_data = []
away_data = []
last_five_data = []
last_match_data = []
streak_table = []
score_data = []
#BASE TABLE
base_table.append(gamedate)
seasonYear = int(gamedate[0:4])
if int(gamedate[5:7]) > 6:
seasonYear += 1
base_table.append(seasonYear)
base_table.append(serie)
base_table.append(team)
team_short = get_short_team_name(team)
base_table.append(team_short)
base_table.append(homeaway)
# Get last 5 games
c.execute(
"SELECT GAMEID FROM teamgames where seasonid = ? and serie = ? and GAMEDATE < ? and TEAM = ? ORDER BY GAMEDATE DESC LIMIT 5",
[seasonYear, serie, gamedate, team])
games5 = c.fetchall()
if len(games5) > 0:
g5 = games5[len(games5) - 1][0]
g1 = games5[0][0]
#FULL SEASON DATA
c.execute(
"""SELECT COUNT(GAMEID) as n_games,
SUM(CASE WHEN OUTCOME = 1 THEN 1 else 0 end) as WINS,
SUM(CASE WHEN OUTCOME = 2 THEN 1 else 0 end) as OTWINS,
SUM(CASE WHEN OUTCOME = 3 THEN 1 else 0 end) as OTLOSSES,
SUM(CASE WHEN OUTCOME = 4 THEN 1 else 0 end) as LOSSES,
SUM(SCORE1) as SCORED,
SUM(SCORE2) as CONCEDED,
SUM(PENALTY1) as PENALTY,
SUM(PENALTY2) as PENALTY_AGAINST,
SUM(SHOTS1) as SHOTS,
SUM(SHOTS2) as SHOTS_AGAINST,
SUM(SCORE11) as SCORED11,
SUM(SCORE12) as SCORED12,
SUM(SCORE13) as SCORED13,
SUM(SCORE21) as SCORED21,
SUM(SCORE22) as SCORED22,
SUM(SCORE23) as SCORED23
FROM
TEAMGAMES
WHERE
TEAM = ? and
GAMEDATE < ? and
SEASONID = ?""", [team, gamedate, seasonYear])
full_data = c.fetchall()
#HOME GAMES DATA
c.execute(
"""SELECT COUNT(GAMEID) as n_games,
SUM(CASE WHEN OUTCOME = 1 THEN 1 else 0 end) as WINS,
SUM(CASE WHEN OUTCOME = 2 THEN 1 else 0 end) as OTWINS,
SUM(CASE WHEN OUTCOME = 3 THEN 1 else 0 end) as OTLOSSES,
SUM(CASE WHEN OUTCOME = 4 THEN 1 else 0 end) as LOSSES,
SUM(SCORE1) as SCORED,
SUM(SCORE2) as CONCEDED,
SUM(PENALTY1) as PENALTY,
SUM(PENALTY2) as PENALTY_AGAINST,
SUM(SHOTS1) as SHOTS,
SUM(SHOTS2) as SHOTS_AGAINST
FROM
TEAMGAMES
WHERE
TEAM = ? and
GAMEDATE < ? and
SEASONID = ? and
HOMEAWAY = ?""", [team, gamedate, seasonYear, 'H'])
home_data = c.fetchall()
#AWAY GAMES DATA
c.execute(
"""SELECT COUNT(GAMEID) as n_games,
SUM(CASE WHEN OUTCOME = 1 THEN 1 else 0 end) as WINS,
SUM(CASE WHEN OUTCOME = 2 THEN 1 else 0 end) as OTWINS,
SUM(CASE WHEN OUTCOME = 3 THEN 1 else 0 end) as OTLOSSES,
SUM(CASE WHEN OUTCOME = 4 THEN 1 else 0 end) as LOSSES,
SUM(SCORE1) as SCORED,
SUM(SCORE2) as CONCEDED,
SUM(PENALTY1) as PENALTY,
SUM(PENALTY2) as PENALTY_AGAINST,
SUM(SHOTS1) as SHOTS,
SUM(SHOTS2) as SHOTS_AGAINST
FROM
TEAMGAMES
WHERE
TEAM = ? and
GAMEDATE < ? and
SEASONID = ? and
HOMEAWAY = ?""", [team, gamedate, seasonYear, 'A'])
away_data = c.fetchall()
#LAST FIVE DATA
c.execute(
"""SELECT COUNT(GAMEID) as n_games,
SUM(CASE WHEN OUTCOME = 1 THEN 1 else 0 end) as WINS,
SUM(CASE WHEN OUTCOME = 2 THEN 1 else 0 end) as OTWINS,
SUM(CASE WHEN OUTCOME = 3 THEN 1 else 0 end) as OTLOSSES,
SUM(CASE WHEN OUTCOME = 4 THEN 1 else 0 end) as LOSSES,
SUM(SCORE1) as SCORED,
SUM(SCORE2) as CONCEDED,
SUM(PENALTY1) as PENALTY,
SUM(PENALTY2) as PENALTY_AGAINST,
SUM(SHOTS1) as SHOTS,
SUM(SHOTS2) as SHOTS_AGAINST
FROM
TEAMGAMES
WHERE
TEAM = ? and
GAMEDATE < ? and
SEASONID = ? and
GAMEID >= ?""", [team, gamedate, seasonYear, g5])
last_five_data = c.fetchall()
#LAST GAME DATA
c.execute(
"""SELECT COUNT(GAMEID) as n_games,
SUM(CASE WHEN OUTCOME = 1 THEN 1 else 0 end) as WINS,
SUM(CASE WHEN OUTCOME = 2 THEN 1 else 0 end) as OTWINS,
SUM(CASE WHEN OUTCOME = 3 THEN 1 else 0 end) as OTLOSSES,
SUM(CASE WHEN OUTCOME = 4 THEN 1 else 0 end) as LOSSES,
SUM(SCORE1) as SCORED,
SUM(SCORE2) as CONCEDED,
SUM(PENALTY1) as PENALTY,
SUM(PENALTY2) as PENALTY_AGAINST,
SUM(SHOTS1) as SHOTS,
SUM(SHOTS2) as SHOTS_AGAINST
FROM
TEAMGAMES
WHERE
TEAM = ? and
SEASONID = ? and
GAMEID = ?""", [team, seasonYear, g1])
last_match_data = c.fetchall()
#STREAK DATA
c.execute(
"SELECT HOMEAWAY, OUTCOME FROM TEAMGAMES WHERE SEASONID = ? AND TEAM = ? AND GAMEDATE < ? order by gamedate DESC",
[seasonYear, team, gamedate])
outcomelist = c.fetchall()
c.execute(
"SELECT HOMEAWAY, OUTCOME FROM TEAMGAMES WHERE SEASONID = ? AND TEAM = ? AND GAMEDATE < ? AND HOMEAWAY = ? order by gamedate DESC",
[seasonYear, team, gamedate, 'H'])
h_outcomelist = c.fetchall()
c.execute(
"SELECT HOMEAWAY, OUTCOME FROM TEAMGAMES WHERE SEASONID = ? AND TEAM = ? AND GAMEDATE < ? AND HOMEAWAY = ? order by gamedate DESC",
[seasonYear, team, gamedate, 'A'])
a_outcomelist = c.fetchall()
streak1 = 0
streak12 = 0
streak4 = 0
streak34 = 0
hstreak1 = 0
hstreak12 = 0
hstreak4 = 0
hstreak34 = 0
astreak1 = 0
astreak12 = 0
astreak4 = 0
astreak34 = 0
i = 0
while i < len(outcomelist) and outcomelist[i][1] in ['1']:
streak1 += 1
i += 1
i = 0
while i < len(outcomelist) and outcomelist[i][1] in ['1', '2']:
streak12 += 1
i += 1
i = 0
while i < len(outcomelist) and outcomelist[i][1] in ['4']:
streak4 += 1
i += 1
i = 0
while i < len(outcomelist) and outcomelist[i][1] in ['3', '4']:
streak34 += 1
i += 1
i = 0
while i < len(h_outcomelist) and h_outcomelist[i][1] in ['1']:
hstreak1 += 1
i += 1
i = 0
while i < len(h_outcomelist) and h_outcomelist[i][1] in ['1', '2']:
hstreak12 += 1
i += 1
i = 0
while i < len(h_outcomelist) and h_outcomelist[i][1] in ['4']:
hstreak4 += 1
i += 1
i = 0
while i < len(h_outcomelist) and h_outcomelist[i][1] in ['3', '4']:
hstreak34 += 1
i += 1
i = 0
while i < len(a_outcomelist) and a_outcomelist[i][1] in ['1']:
astreak1 += 1
i += 1
i = 0
while i < len(a_outcomelist) and a_outcomelist[i][1] in ['1', '2']:
astreak12 += 1
i += 1
i = 0
while i < len(a_outcomelist) and a_outcomelist[i][1] in ['4']:
astreak4 += 1
i += 1
i = 0
while i < len(a_outcomelist) and a_outcomelist[i][1] in ['3', '4']:
astreak34 += 1
i += 1
streak_table.append(streak1)
streak_table.append(streak12)
streak_table.append(streak4)
streak_table.append(streak34)
streak_table.append(hstreak1)
streak_table.append(hstreak12)
streak_table.append(hstreak4)
streak_table.append(hstreak34)
streak_table.append(astreak1)
streak_table.append(astreak12)
streak_table.append(astreak4)
streak_table.append(astreak34)
#SCORE DATA
[score1, form_score1, last_seasons_score1,
player_score1] = calculate_team_strength(team, gamedate, "", c)
score_data.append(score1)
score_data.append(form_score1)
score_data.append(last_seasons_score1)
score_data.append(player_score1)
return [
base_table, full_data, home_data, away_data, last_five_data,
last_match_data, streak_table, score_data
]
def get_model1_data(serie, seasonYear, gamedate, home_team, away_team, c,
conn):
from calcFunctions import calculate_team_strength
score1 = calculate_team_strength(home_team, gamedate, "", c)[3]
score2 = calculate_team_strength(away_team, gamedate, "", c)[3]
c.execute(
"SELECT HOMETEAM, COUNT(GAMEID), AVG(SCORE1), AVG(OFF_SCORE_GAME1), AVG(DEF_SCORE_GAME1) FROM (SELECT * FROM GOALS_FOREST_TABLE_1 WHERE GAMEDATE < ? AND SEASONID = ? AND (HOMETEAM = ?) ORDER BY GAMEDATE DESC LIMIT 6)",
[gamedate, seasonYear, home_team])
hth_data = c.fetchall()
c.execute(
"SELECT AWAYTEAM, COUNT(GAMEID), AVG(SCORE2), AVG(OFF_SCORE_GAME2), AVG(DEF_SCORE_GAME2) FROM (SELECT * FROM GOALS_FOREST_TABLE_1 WHERE GAMEDATE < ? AND SEASONID = ? AND (AWAYTEAM = ?) ORDER BY GAMEDATE DESC LIMIT 4)",
[gamedate, seasonYear, home_team])
hta_data = c.fetchall()
if (hth_data[0][1] + hta_data[0][1]
) > 0 and hth_data[0][0] != None and hta_data[0][0] != None:
home_team_off = (hth_data[0][1] * hth_data[0][3] + hta_data[0][1] *
hta_data[0][3]) / (hth_data[0][1] + hta_data[0][1])
home_team_def = (hth_data[0][1] * hth_data[0][4] + hta_data[0][1] *
hta_data[0][4]) / (hth_data[0][1] + hta_data[0][1])
home_team_off = home_team_off * (
hth_data[0][1] + hta_data[0][1]) / 10 + score1 / 15 * (
10 - (hth_data[0][1] + hta_data[0][1])) / 10
home_team_def = home_team_def * (
hth_data[0][1] + hta_data[0][1]) / 10 + (1 - score1 / 12) * (
10 - (hth_data[0][1] + hta_data[0][1])) / 10
else:
home_team_off = score1 / 15
home_team_def = 1 - score1 / 12
c.execute(
"SELECT HOMETEAM, COUNT(GAMEID), AVG(SCORE1), AVG(OFF_SCORE_GAME1), AVG(DEF_SCORE_GAME1) FROM (SELECT * FROM GOALS_FOREST_TABLE_1 WHERE GAMEDATE < ? AND SEASONID = ? AND (HOMETEAM = ?) ORDER BY GAMEDATE DESC LIMIT 4)",
[gamedate, seasonYear, away_team])
hth_data = c.fetchall()
c.execute(
"SELECT AWAYTEAM, COUNT(GAMEID), AVG(SCORE2), AVG(OFF_SCORE_GAME2), AVG(DEF_SCORE_GAME2) FROM (SELECT * FROM GOALS_FOREST_TABLE_1 WHERE GAMEDATE < ? AND SEASONID = ? AND (AWAYTEAM = ?) ORDER BY GAMEDATE DESC LIMIT 6)",
[gamedate, seasonYear, away_team])
hta_data = c.fetchall()
if (hth_data[0][1] + hta_data[0][1]
) > 0 and hth_data[0][0] != None and hta_data[0][0] != None:
away_team_off = (hth_data[0][1] * hth_data[0][3] + hta_data[0][1] *
hta_data[0][3]) / (hth_data[0][1] + hta_data[0][1])
away_team_def = (hth_data[0][1] * hth_data[0][4] + hta_data[0][1] *
hta_data[0][4]) / (hth_data[0][1] + hta_data[0][1])
away_team_off = away_team_off * (
hth_data[0][1] + hta_data[0][1]) / 10 + score2 / 15 * (
10 - (hth_data[0][1] + hta_data[0][1])) / 10
away_team_def = away_team_def * (
hth_data[0][1] + hta_data[0][1]) / 10 + (1 - score2 / 12) * (
10 - (hth_data[0][1] + hta_data[0][1])) / 10
else:
away_team_off = score2 / 15
away_team_def = 1 - score2 / 12
return home_team_off, home_team_def, away_team_off, away_team_def
def re_score(seasonYear, serie):
c.execute("SELECT GAMEID from schedule where SEASONID = ? and SERIE = ?",
[seasonYear, serie])
gameVector = c.fetchall()
t = 0
if len(gameVector) > 0:
n = len(gameVector)
for j in range(0, len(gameVector)):
c.execute("DELETE FROM teamscore where gameid = ?",
[gameVector[j][0]])
conn.commit()
c.execute("SELECT TEAM, GAMEDATE FROM TEAMGAMES WHERE GAMEID = ?",
[gameVector[j][0]])
tgt = c.fetchall()
[final_team_score, points, season_points, player_score_final
] = calculate_team_strength(tgt[0][0], tgt[0][1], "", c)
c.execute("SELECT * FROM TEAMSCORE WHERE GAMEID = ? AND TEAM = ?",
[gameVector[j][0], tgt[0][0]])
ts = c.fetchall()
if len(ts) > 0:
c.execute(
"UPDATE TEAMSCORE SET SCORE, FORM_SCORE = ?, LAST_SEASONS_SCORE = ?, PLAYER_SCORE = ? WHERE GAMEID = ? AND TEAM = ?",
[
final_team_score, points, season_points,
player_score_final, gameVector[j][0], tgt[0][0]
])
else:
c.execute(
"INSERT INTO TEAMSCORE (SEASONID, SERIE, GAMEID, GAMEDATE, TEAM, SCORE, FORM_SCORE, LAST_SEASONS_SCORE, PLAYER_SCORE) VALUES (?,?,?,?,?,?,?,?,?)",
[
seasonYear, serie, gameVector[j][0], tgt[0][1],
tgt[0][0], final_team_score, points, season_points,
player_score_final
])
[final_team_score, points, season_points, player_score_final
] = calculate_team_strength(tgt[1][0], tgt[1][1], "", c)
c.execute("SELECT * FROM TEAMSCORE WHERE GAMEID = ? AND TEAM = ?",
[gameVector[j][0], tgt[1][0]])
ts = c.fetchall()
if len(ts) > 0:
c.execute(
"UPDATE TEAMSCORE SET SCORE, FORM_SCORE = ?, LAST_SEASONS_SCORE = ?, PLAYER_SCORE = ? WHERE GAMEID = ? AND TEAM = ?",
[
final_team_score, points, season_points,
player_score_final, gameVector[j][0], tgt[1][0]
])
else:
c.execute(
"INSERT INTO TEAMSCORE (SEASONID, SERIE, GAMEID, GAMEDATE, TEAM, SCORE, FORM_SCORE, LAST_SEASONS_SCORE, PLAYER_SCORE) VALUES (?,?,?,?,?,?,?,?,?)",
[
seasonYear, serie, gameVector[j][0], tgt[1][1],
tgt[1][0], final_team_score, points, season_points,
player_score_final
])
conn.commit()
[final_team_score, points, season_points, player_score_final
] = calculate_team_strength(tgt[1][0], tgt[1][1], "", c)
# Add score to lineups
c.execute(
"SELECT TEAM, NUMBER, FORNAME, SURNAME, GAMEDATE FROM lineups where GAMEID = ?",
[gameVector[j][0]])
lineups = c.fetchall()
for i in range(0, len(lineups)):
c.execute(
"SELECT * from lineups where GAMEID = ? and TEAM = ? and NUMBER = ?",
[gameVector[j][0], lineups[i][0], lineups[i][1]])
lineup = c.fetchall()
score = create_game_rating(lineup, lineups[i][0], c, conn)
#print(score)
if len(score) < 4:
score = ['0', '0', '0', '0']
c.execute(
"UPDATE lineups SET SCORE = ?, FINALSCORE = ?, OFFSCORE = ?, DEFSCORE = ? WHERE GAMEID = ? and TEAM = ? and NUMBER = ?",
[
score[0], score[1], score[2], score[3],
gameVector[j][0], lineups[i][0], lineups[i][1]
])
t += 1
print(
str(seasonYear) + " " + str(t) + "/" + str(n) +
" scores updated")
conn.commit
c.close
for i in range(0, len(upd)):
c.execute(
"UPDATE EXP_SHOTS_TABLE SET ACT_GOALS1 = ?, ACT_GOALS2 = ? WHERE GAMEID = ?",
[upd[i][1], upd[i][2], upd[i][0]])
conn.commit()
c.execute("SELECT HOMETEAM, AWAYTEAM, GAMEDATE FROM EXP_SHOTS_TABLE")
upd = c.fetchall()
count = 0
for i in range(0, len(upd)):
[a, b, d, hts] = calculate_team_strength(upd[i][0], upd[i][2], "", c)
[a, b, d, ats] = calculate_team_strength(upd[i][1], upd[i][2], "", c)
c.execute(
"UPDATE EXP_SHOTS_TABLE SET SCORE1 = ?, SCORE2 = ? WHERE HOMETEAM = ? AND AWAYTEAM = ? AND GAMEDATE = ?",
[hts, ats, upd[i][0], upd[i][1], upd[i][2]])
count += 1
print(count)
conn.commit()
#re_score(2016,'SHL')
#re_score(2016,'HA')
#re_score(2017,'SHL')
#re_score(2017,'HA')
def scrape_sh(seasonID, seasonYear, serie, score_update):
#Vectors to scrape in first step
gameVector = []
venueVector = []
audVector = []
dateVector = []
lineVector = []
#Read in season schedule
scheduleUrl = "http://stats.swehockey.se/ScheduleAndResults/Schedule/" + str(
seasonID)
response = urllib.urlopen(scheduleUrl)
page_source = str(response.read())
#Establish connection to database
import sqlite3
conn = sqlite3.connect('hockeystats.db')
c = conn.cursor()
#Check if vectors exist
c.execute("SELECT * FROM schedule where SEASONID = ? and SERIE = ?",
[seasonYear, serie])
sc = c.fetchall()
if len(sc) > 0:
c.execute("DELETE FROM SCHEDULE")
#If vectors dont exist then get vectors
page_source = page_source.replace("\\xc3\\xa5", "å")
page_source = page_source.replace("\\xc3\\xa4", "ä")
page_source = page_source.replace("\\xc2\\xa0", " ")
page_source = page_source.replace("\\xc3\\xa9", "é")
page_source = page_source.replace("\\xc3\\xb6", "ö")
page_source = page_source.replace("\\xc3\\x84", "Ä")
page_source = page_source.replace("\\xc3\\x85", "Å")
page_source = page_source.replace("\\xc3\\x96", "Ö")
page_source = page_source.replace("\\r", " ")
page_source = page_source.replace("\\n", " ")
#Update rosters
update_rosters(seasonID, seasonYear, serie, c, conn)
for i in range(1, len(page_source) - 10):
if isnumber(page_source[i:i +
4]) and page_source[i + 4] == '-' and isnumber(
page_source[i + 5:i + 7]) and page_source[
i + 7] and isnumber(
page_source[i + 8:i + 10]):
currdate = page_source[i:i + 10]
if page_source[i:i + 8] == "/Events/":
gameID = 0
for j in range(1, 10):
if isnumber(page_source[i + 8 + j]) == False:
if gameID == 0:
gameID = page_source[i + 8:i + 8 + j]
gameVector.append(gameID)
dateVector.append(currdate)
audience = ""
tds = get_td_content(
page_source[i:max(len(page_source) - 10, i + 200)])
inserted = 0
for j in range(0, 10):
if isnumber(tds[j]) and inserted == 0:
inserted = 1
audVector.append(int(tds[j]))
venueVector.append(tds[j + 1])
for j in range(0, len(gameVector)):
c.execute(
"INSERT INTO schedule (SEASONID, SERIE, GAMEID, GAMEDATE, AUD, VENUE) VALUES (?,?,?,?,?,?)",
[
seasonYear, serie, gameVector[j], dateVector[j], audVector[j],
venueVector[j]
])
c.execute("SELECT GAMEID from schedule where SEASONID = ? and SERIE = ?",
[seasonYear, serie])
gameVector = c.fetchall()
c.execute("SELECT GAMEDATE from schedule where SEASONID = ? and SERIE = ?",
[seasonYear, serie])
dateVector = c.fetchall()
c.execute("SELECT AUD from schedule where SEASONID = ? and SERIE = ?",
[seasonYear, serie])
audVector = c.fetchall()
c.execute("SELECT VENUE from schedule where SEASONID = ? and SERIE = ?",
[seasonYear, serie])
venueVector = c.fetchall()
conn.commit()
########################################################################################################################
################################ Get game specific statistics (Lineups) ##########################################
########################################################################################################################
#Loop through games
for j in range(0, len(gameVector)):
#Check if game already has been updated, then skip update
c.execute("SELECT * FROM lineups where GAMEID = ?", [gameVector[j][0]])
check = c.fetchall()
if len(check) == 0:
stats = get_stats(gameVector[j][0], dateVector[j][0])
lineups = get_lineups(gameVector[j][0], audVector[j][0],
venueVector[j][0], seasonYear, stats[2],
stats[3])
[refs, lines] = get_refs(gameVector[j][0], audVector[j][0],
venueVector[j][0], seasonYear)
# Create stats table
c.execute("SELECT GAMEID as GAMEID FROM stats where GAMEID = ?",
[stats[0]])
hits = c.fetchall()
if len(hits) == 0:
c.execute(
"""INSERT INTO
stats (
SEASONID,SERIE,GAMEID,GAMEDATE,HOMETEAM,AWAYTEAM,HOMESCORE,AWAYSCORE,HOMESHOTS,AWAYSHOTS,HOMESAVES,AWAYSAVES,HOMEPENALTY,AWAYPENALTY,HSCORE1,HSCORE2,HSCORE3,HSCORE4,ASCORE1,ASCORE2,ASCORE3,ASCORE4,
HSHOTS1,HSHOTS2,HSHOTS3,HSHOTS4,ASHOTS1,ASHOTS2,ASHOTS3,ASHOTS4,HSAVES1,HSAVES2,HSAVES3,HSAVES4,ASAVES1,ASAVES2,ASAVES3,ASAVES4,HPENALTY1,HPENALTY2,HPENALTY3,HPENALTY4,APENALTY1,APENALTY2,APENALTY3,
APENALTY4, HOMEPP, AWAYPP)
VALUES
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""",
(seasonYear, serie, stats[0], stats[1][0:10], stats[2],
stats[3], stats[4], stats[5], stats[6], stats[7],
stats[8], stats[9], stats[10], stats[11], stats[12],
stats[13], stats[14], stats[15], stats[16], stats[17],
stats[18], stats[19], stats[20], stats[21], stats[22],
stats[23], stats[24], stats[25], stats[26], stats[27],
stats[28], stats[29], stats[30], stats[31], stats[32],
stats[33], stats[34], stats[35], stats[36], stats[37],
stats[38], stats[39], stats[40], stats[41], stats[42],
stats[43], stats[44], stats[45]))
else:
pass
conn.commit()
#Create lineup table
for i in range(0, len(lineups)):
c.execute(
"SELECT ID as ID FROM lineups where GAMEID = ? and TEAM = ? and NUMBER = ? and FORNAME = ? and SURNAME = ?",
[
lineups[i][0], lineups[i][1], lineups[i][2],
lineups[i][3], lineups[i][4]
])
hits = c.fetchall()
c.execute("SELECT ID as ID FROM lineups")
ids = c.fetchall()
if len(ids) > 0:
id = max(ids)[0] + 1
else:
id = 1
if len(hits) == 0:
c.execute(
"""INSERT INTO
lineups (
ID,GAMEID,SEASONID,SERIE,AUDIENCE,VENUE,HOMETEAM,AWAYTEAM,TEAM,GAMEDATE,NUMBER,FORNAME,SURNAME,POSITION,START_PLAYER,
GOALS, PPGOALS, SHGOALS, ASSISTS, PLUS, MINUS, PENALTY, INPOWERPLAY, INBOXPLAY, SHOTSAT, SAVES, SCORE, FINALSCORE, SCORE5, GOALS5, ASSIST5, GAMES5, SCORE_CURRENT, GOALS_CURRENT, ASSIST_CURRENT, GAMES_CURRENT)
VALUES
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)""",
(id, lineups[i][0], lineups[i][9], serie,
lineups[i][7], lineups[i][8], stats[2], stats[3],
lineups[i][1], stats[1][0:10], lineups[i][2],
lineups[i][3], lineups[i][4], lineups[i][5],
lineups[i][6]))
else:
pass
conn.commit()
c.execute(
"SELECT PERSONNR from rosters where SEASONID = ? and TEAM = ? and NUMBER = ?",
[seasonYear, lineups[i][1], lineups[i][2]])
personnr = c.fetchall()
persnr = ''
if len(personnr) > 0:
persnr = personnr[0][0]
c.execute(
"UPDATE lineups SET PERSONNR = ? WHERE SEASONID = ? and TEAM = ? and NUMBER = ? and FORNAME = ? and SURNAME = ?",
[
persnr, seasonYear, lineups[i][1], lineups[i][2],
lineups[i][3], lineups[i][4]
])
conn.commit()
# Get events data from each game
events = get_actions(gameVector[j][0], audVector[j][0],
venueVector[j][0], seasonYear, stats[2],
stats[3], c)
# Create event table
for i in range(0, len(events)):
c.execute(
"SELECT ID FROM events where GAMEID = ? and TIME = ? and EVENT = ? and TEAM = ? and NUMBER = ? and FORNAME = ? and SURNAME = ?",
[
events[i][0], events[i][2], events[i][3], events[i][4],
events[i][5], events[i][7], events[i][6]
])
hits = c.fetchall()
c.execute("SELECT ID as ID FROM events")
ids = c.fetchall()
if len(ids) > 0:
id = max(ids)[0] + 1
else:
id = 1
if len(hits) == 0:
c.execute(
"""SELECT PERSONNR FROM rosters WHERE SEASONID = ? and TEAM = ? and NUMBER = ? and FORNAME = ? AND SURNAME = ? """,
(seasonYear, events[i][4], events[i][5], events[i][7],
events[i][6]))
personnr = c.fetchall()
if personnr == []:
pnr = ""
else:
pnr = personnr[0][0]
c.execute(
"""INSERT INTO
events (
ID,GAMEID,SEASONID,AUDIENCE,VENUE,PERIOD,TIME,EVENT,TEAM,NUMBER,PERSONNR,FORNAME,SURNAME,EXTRA1,EXTRA2)
VALUES
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""",
(id, events[i][0], events[i][12], events[i][10],
events[i][11], events[i][1], events[i][2],
events[i][3], events[i][4], events[i][5], pnr,
events[i][7], events[i][6], events[i][8],
events[i][9]))
else:
pass
conn.commit()
# Create ref table
if serie == "SHL":
for i in range(0, len(events)):
c.execute(
"SELECT GAMEID as GAMEID FROM refs where GAMEID = ?",
[stats[0]])
hits = c.fetchall()
if len(hits) == 0:
c.execute(
"""INSERT INTO
refs (
GAMEID,SEASONID,HOMETEAM,AWAYTEAM,REF1,REF2,LINE1,LINE2)
VALUES
(?,?,?,?,?,?,?,?)""",
(stats[0], seasonYear, refs[0], refs[1], stats[2],
stats[3], lines[0], lines[1]))
else:
pass
conn.commit()
print(str(stats[0]) + " lineups loaded")
#Update lineups with stats
c.execute(
"SELECT TEAM, NUMBER, FORNAME, SURNAME, GAMEDATE FROM lineups where GAMEID = ?",
[gameVector[j][0]])
lineups = c.fetchall()
for i in range(0, len(lineups)):
c.execute(
"SELECT SUM(CASE WHEN EVENT = ? then 1 else 0 end) as X FROM events where GAMEID = ? and TEAM = ? and NUMBER = ?",
['Goal', gameVector[j][0], lineups[i][0], lineups[i][1]])
goals = c.fetchall()[0][0]
if goals == None:
goals = 0
c.execute(
"SELECT SUM(CASE WHEN EVENT = ? and EXTRA1 = ? then 1 else 0 end) as X FROM events where GAMEID = ? and TEAM = ? and NUMBER = ?",
[
'Goal', 'PP', gameVector[j][0], lineups[i][0],
lineups[i][1]
])
PP = c.fetchall()[0][0]
if PP == None:
PP = 0
c.execute(
"SELECT SUM(CASE WHEN EVENT = ? and EXTRA1 = ? then 1 else 0 end) as X FROM events where GAMEID = ? and TEAM = ? and NUMBER = ?",
[
'Goal', 'SH', gameVector[j][0], lineups[i][0],
lineups[i][1]
])
SH = c.fetchall()[0][0]
if SH == None:
SH = 0
c.execute(
"SELECT SUM(CASE WHEN EVENT = ? then 1 else 0 end) as X FROM events where GAMEID = ? and TEAM = ? and NUMBER = ?",
['Assist', gameVector[j][0], lineups[i][0], lineups[i][1]])
assist = c.fetchall()[0][0]
if assist == None:
assist = 0
c.execute(
"SELECT SUM(CASE WHEN EVENT = ? then 1 else 0 end) as X FROM events where GAMEID = ? and TEAM = ? and NUMBER = ?",
['1', gameVector[j][0], lineups[i][0], lineups[i][1]])
plus = c.fetchall()[0][0]
if plus == None:
plus = 0
c.execute(
"SELECT SUM(CASE WHEN EVENT = ? then 1 else 0 end) as X FROM events where GAMEID = ? and TEAM = ? and NUMBER = ? and (extra1 = ? or extra1 = ?)",
[
'-1', gameVector[j][0], lineups[i][0], lineups[i][1],
'', 'PP'
])
minus = c.fetchall()[0][0]
if minus == None:
minus = 0
c.execute(
"SELECT SUM(CASE WHEN EVENT = ? then CAST(EXTRA2 as INT) else 0 end) as X FROM events where GAMEID = ? and TEAM = ? and NUMBER = ?",
[
'Penalty', gameVector[j][0], lineups[i][0],
lineups[i][1]
])
penalty = c.fetchall()[0][0]
if penalty == None:
penalty = 0
c.execute(
"SELECT SUM(CASE WHEN EXTRA1 = ? then 1 else 0 end) as X FROM events where GAMEID = ? and TEAM = ? and NUMBER = ?",
['PP', gameVector[j][0], lineups[i][0], lineups[i][1]])
activePP = c.fetchall()[0][0]
if activePP == None:
activePP = 0
elif activePP > 1:
activePP = 1
c.execute(
"SELECT SUM(CASE WHEN EXTRA1 = ? then 1 else 0 end) as X FROM events where GAMEID = ? and TEAM = ? and NUMBER = ?",
['SH', gameVector[j][0], lineups[i][0], lineups[i][1]])
activeBP = c.fetchall()[0][0]
if activeBP == None:
activeBP = 0
elif activeBP > 1:
activeBP = 1
#Addera kod för shots/saves
c.execute(
"SELECT EXTRA1, EXTRA2 from events where EVENT = ? and GAMEID = ? and TEAM = ? and NUMBER = ?",
[
'Keeper stat', gameVector[j][0], lineups[i][0],
lineups[i][1]
])
golieStats = c.fetchall()
if golieStats == []:
shotsAt = 0
saves = 0
else:
shotsAt = golieStats[0][0]
saves = golieStats[0][1]
c.execute(
"UPDATE lineups SET GOALS = ?, PPGOALS = ?, SHGOALS = ?, ASSISTS = ?, PLUS = ?, MINUS = ?, PENALTY = ?, INPOWERPLAY = ?, INBOXPLAY = ?, SHOTSAT = ?, SAVES = ? WHERE GAMEID = ? and TEAM = ? and NUMBER = ?",
[
goals, PP, SH, assist, plus, minus, penalty, activePP,
activeBP, shotsAt, saves, gameVector[j][0],
lineups[i][0], lineups[i][1]
])
conn.commit()
# Update lineups with old stats
# Last five games
c.execute(
"SELECT SCORE, GOALS, ASSISTS, GAMEDATE, TEAM, FORNAME, SURNAME FROM lineups WHERE GAMEDATE < ? and GAMEDATE > ? and FORNAME = ? and SURNAME = ? and PERSONNR = ? ORDER BY GAMEDATE DESC",
[
lineups[i][4],
transform_date(lineups[i][4], 20), lineups[i][2],
lineups[i][3], persnr
])
output = np.array(c.fetchall())
games5 = min(len(output), 5)
score5 = 0
goals5 = 0
assist5 = 0
for k in range(0, games5):
score5 += float(output[k][0]) / games5
goals5 += float(output[k][1]) / games5
assist5 += float(output[k][2]) / games5
# Current season
c.execute(
"SELECT SCORE, GOALS, ASSISTS, GAMEDATE, TEAM, FORNAME, SURNAME FROM lineups WHERE SEASONID = ? and FORNAME = ? and SURNAME = ? and PERSONNR = ? ORDER BY GAMEDATE DESC",
[seasonYear, lineups[i][2], lineups[i][3], persnr])
output = np.array(c.fetchall())
gamescurr = len(output)
scorecurr = 0
goalscurr = 0
assistcurr = 0
for k in range(0, gamescurr):
scorecurr += float(output[k][0]) / gamescurr
goalscurr += float(output[k][1]) / gamescurr
assistcurr += float(output[k][2]) / gamescurr
c.execute(
"""UPDATE lineups SET SCORE5 = ?, GOALS5 = ?, ASSIST5 = ?, SCORE_CURRENT = ?, GOALS_CURRENT = ?, ASSIST_CURRENT = ?, GAMES5 = ?, GAMES_CURRENT = ?
WHERE GAMEID = ? and PERSONNR = ? and FORNAME = ? and SURNAME = ?""",
[
score5, goals5, assist5, scorecurr, goalscurr,
assistcurr, games5, gamescurr, gameVector[j][0],
persnr, lineups[i][2], lineups[i][3]
])
conn.commit()
#Create teamgames table
create_teamgames(seasonYear, serie, c)
print(str(stats[0]) + " stats, events loaded")
else:
print("Game already loaded")
if t_count == 1:
t += 1
print(str(t) + "/" + str(len(gameVector)) + " done")
#Update score for games
for j in range(0, len(gameVector)):
c.execute("SELECT GAMEID FROM TEAMSCORE WHERE GAMEID = ?",
[gameVector[j][0]])
check = c.fetchall()
if score_update == "Full" or len(check) == 0:
# Add score to lineups
c.execute(
"SELECT TEAM, NUMBER, FORNAME, SURNAME, GAMEDATE, HOMETEAM, AWAYTEAM FROM lineups where GAMEID = ?",
[gameVector[j][0]])
lineups = c.fetchall()
for i in range(0, len(lineups)):
c.execute(
"SELECT * from lineups where GAMEID = ? and TEAM = ? and NUMBER = ?",
[gameVector[j][0], lineups[i][0], lineups[i][1]])
lineup = c.fetchall()
score = create_game_rating(lineup, lineups[i][0], c, conn)
if len(score) < 4:
score = ['0', '0', '0', '0']
c.execute(
"UPDATE lineups SET SCORE = ?, FINALSCORE = ?, OFFSCORE = ?, DEFSCORE = ? WHERE GAMEID = ? and TEAM = ? and NUMBER = ?",
[
score[0], score[1], score[2], score[3],
gameVector[j][0], lineups[i][0], lineups[i][1]
])
#Calculate team strenght
if len(lineups) > 0:
# Check score home team
[team_strenght, form_score, last_seasons_score, player_score
] = calculate_team_strength(lineups[0][5], lineups[0][4], "",
c)
c.execute(
"SELECT * FROM TEAMSCORE WHERE GAMEDATE = ? AND TEAM = ?",
[lineups[0][4], lineups[0][5]])
chk = c.fetchall()
if len(chk) == 0:
c.execute(
"INSERT INTO TEAMSCORE (SEASONID, SERIE, GAMEID, GAMEDATE, TEAM, SCORE, FORM_SCORE, LAST_SEASONS_SCORE, PLAYER_SCORE) VALUES (?,?,?,?,?,?,?,?,?)",
[
seasonYear, serie, gameVector[j][0], lineups[0][4],
lineups[0][5], team_strenght, form_score,
last_seasons_score, player_score
])
else:
c.execute(
"UPDATE TEAMSCORE SET SCORE = ?, FORM_SCORE = ?, LAST_SEASONS_SCORE = ?, PLAYER_SCORE = ? WHERE SEASONID = ? AND SERIE = ? AND GAMEID = ? AND TEAM = ?",
[
team_strenght, form_score, last_seasons_score,
player_score, seasonYear, serie, gameVector[j][0],
lineups[0][5]
])
#Check score away team
[team_strenght, form_score, last_seasons_score, player_score
] = calculate_team_strength(lineups[0][6], lineups[0][4], "",
c)
c.execute(
"SELECT * FROM TEAMSCORE WHERE GAMEDATE = ? AND TEAM = ?",
[lineups[0][4], lineups[0][6]])
chk = c.fetchall()
if len(chk) == 0:
c.execute(
"INSERT INTO TEAMSCORE (SEASONID, SERIE, GAMEID, GAMEDATE, TEAM, SCORE, FORM_SCORE, LAST_SEASONS_SCORE, PLAYER_SCORE) VALUES (?,?,?,?,?,?,?,?,?)",
[
seasonYear, serie, gameVector[j][0], lineups[0][4],
lineups[0][6], team_strenght, form_score,
last_seasons_score, player_score
])
else:
c.execute(
"UPDATE TEAMSCORE SET SCORE = ?, FORM_SCORE = ?, LAST_SEASONS_SCORE = ?, PLAYER_SCORE = ? WHERE SEASONID = ? AND SERIE = ? AND GAMEID = ? AND TEAM = ?",
[
team_strenght, form_score, last_seasons_score,
player_score, seasonYear, serie, gameVector[j][0],
lineups[0][6]
])
print("Score updated")
conn.commit()
# Update pre-game info
for j in range(0, len(gameVector)):
c.execute("SELECT GAMEID FROM EXP_SHOTS_TABLE WHERE GAMEID = ?",
[gameVector[j][0]])
check = c.fetchall()
if len(check) == 0:
c.execute("SELECT HOMETEAM, AWAYTEAM FROM STATS WHERE GAMEID = ?",
[gameVector[j][0]])
teams = c.fetchall()
create_pre_match_analysis(dateVector[j][0], seasonID, serie,
teams[0][0], teams[0][1],
gameVector[j][0], c, conn)
def create_pre_match_analysis(gamedate, seasonID, serie, hometeam, awayteam,
gameid, c, conn):
# Calculate season
seasonYear = int(gamedate[0:4])
if int(gamedate[5:7]) > 6:
seasonYear += 1
modelSeasonYear = seasonYear
#Code to use newer models on old data if no old model is available
if modelSeasonYear < 2017:
modelSeasonYear = 2017
print(serie, hometeam, awayteam, gamedate)
run_game_data = 0
run_player_data = 1
curr_lineup = []
starting_keeper_home = ["", ""]
starting_keeper_away = ["", ""]
if gameid == "":
gameid = get_live_games(seasonID, gamedate, hometeam, awayteam)
#get values based on old data
curr_lineup = get_lineups(gameid, 0, "", seasonYear, hometeam, awayteam)
starting_keeper_home = get_starting_keeper(hometeam, curr_lineup)
starting_keeper_away = get_starting_keeper(awayteam, curr_lineup)
if run_game_data == 1:
[
base_table1, full_data1, home_data1, away_data1, last_five_data1,
last_match_data1, streak_table1, score_data1
] = create_pre_match_table(gamedate, serie, hometeam, "H", c, conn)
[
base_table2, full_data2, home_data2, away_data2, last_five_data2,
last_match_data2, streak_table2, score_data2
] = create_pre_match_table(gamedate, serie, awayteam, "A", c, conn)
score1 = score_data1[3]
score2 = score_data2[3]
# Get datatables from create_pre_match_tables
[
ave_home_shots, ave_home_shots_against, ave_score_shot_home,
ave_conceded_shot_home, ave_away_shots, ave_away_shots_against,
ave_score_shot_away, ave_conceded_shot_away, average_goal_percent
] = get_expected_shots(full_data1, home_data1, away_data2, full_data2,
home_data2, score_data1, score_data2, serie, c,
conn, gameid, gamedate, seasonYear)
print("Scores:", score1, score2)
#Get the correct lineup if available, recalculate scores based on lineup
if gameid != "":
curr_lineup = get_lineups(gameid, 0, "", seasonYear, hometeam,
awayteam)
new_score1 = 0
new_score2 = 0
new_score1 = calculate_team_strength(hometeam, gamedate,
curr_lineup, c)
new_score2 = calculate_team_strength(awayteam, gamedate,
curr_lineup, c)
print("New Scores:", new_score1[3], new_score2[3])
score1 = new_score1[3]
score2 = new_score2[3]
#Get starting keeper
starting_keeper_home = get_starting_keeper(hometeam, curr_lineup)
starting_keeper_away = get_starting_keeper(awayteam, curr_lineup)
# Get actual scores
act_home_goals = 0
act_away_goals = 0
c.execute(
"SELECT HSCORE1 + HSCORE2 + HSCORE3, ASCORE1 + ASCORE2 + ASCORE3 FROM STATS WHERE GAMEID = ?",
[gameid])
scores = c.fetchall()
if len(scores) > 0:
act_home_goals = scores[0][0]
act_away_goals = scores[0][1]
####################################################################################################################################################################
### MODEL nGOALS FOR 1 & 2 ###
####################################################################################################################################################################
home_team_off, home_team_def, away_team_off, away_team_def = get_model1_data(
serie, modelSeasonYear, gamedate, hometeam, awayteam, c, conn)
inputs = pd.DataFrame([[
abs(score1 / score2 - 1), home_team_off, away_team_off,
home_team_def, away_team_def
]])
nGoals = get_nGoals_model(modelSeasonYear, inputs, c)
print("nGoals: ", nGoals)
####################################################################################################################################################################
### MODEL 1 ###
####################################################################################################################################################################
# Get shots, first base function then adjusted function
home_shots_temp, away_shots_temp = get_shots_goals_linreg(
modelSeasonYear, [
ave_home_shots, ave_home_shots_against, ave_away_shots,
ave_away_shots_against, score_data1[3], score_data2[3]
], gameid, serie, c, conn)
home_shots, away_shots = get_adjusted_shots(home_shots_temp,
away_shots_temp, hometeam,
awayteam, gamedate,
seasonYear, c, conn)
#home_shots, away_shots = get_adjusted_shots_against(home_shots_temp2, away_shots_temp2, hometeam, awayteam, gamedate,seasonYear, c, conn)
#print("Expected shots:", home_shots, away_shots)
# Get goals from shots, first base function then basic adjustment
home_goals1, away_goals1 = get_efficiency_model_linreg(
modelSeasonYear,
[home_shots, away_shots, score1 - score2, average_goal_percent],
gameid, serie, c)
home_goals1 *= 0.975 # Basic adjustment
away_goals1 *= 0.995 # Basic adjustment
print("Model 1 expected goals:", home_goals1, away_goals1)
####################################################################################################################################################################
### MODEL 2 ###
####################################################################################################################################################################
# Update model data
#update_forest_data_1(serie, c, conn) #(If historic values need to be rerun for model)
home_team_off, home_team_def, away_team_off, away_team_def = get_model1_data(
serie, modelSeasonYear, gamedate, hometeam, awayteam, c, conn)
inputs = pd.DataFrame(
[[home_team_off, home_team_def, away_team_off, away_team_def]]) #
#print(inputs)
# Get odds model 2
diff1 = get_outcome_model1(serie, modelSeasonYear, inputs,
c) # seasonYear = 2019
#print(diff1)
home_goals2 = nGoals / 2 + diff1 / 2
away_goals2 = nGoals / 2 - diff1 / 2
print("Model 2 expected goals:", home_goals2, away_goals2)
####################################################################################################################################################################
### MODEL 3 ###
####################################################################################################################################################################
# Update model data
#update_forest_data_2(serie, seasonYear, c, conn) #(If historic values need to be rerun for model)
comb_score_home = get_model2_data(score1, full_data1, home_data1,
last_five_data1, last_match_data1,
'H')
comb_score_away = get_model2_data(score2, full_data2, away_data2,
last_five_data2, last_match_data2,
'A')
#print("Comb scores:", comb_score_home, comb_score_away)
inputs = pd.DataFrame([[comb_score_home, comb_score_away]])
# Get odds model 3
diff2 = get_outcome_model2(serie, modelSeasonYear, inputs,
c) # seasonYear = 2019
home_goals3 = nGoals / 2 + diff2 / 2
away_goals3 = nGoals / 2 - diff2 / 2
print("Model 3 expected goals:", home_goals3, away_goals3)
####################################################################################################################################################################
### COMBINE MODELS ###
####################################################################################################################################################################
home_goals = home_goals1 * 5 / 10 + home_goals2 * 2 / 10 + home_goals3 * 3 / 10
away_goals = away_goals1 * 5 / 10 + away_goals2 * 2 / 10 + away_goals3 * 3 / 10
results, odds1X2, odds45 = get_result_matrix(home_goals, away_goals)
c.execute(
"UPDATE EXP_SHOTS_TABLE SET EXP_SHOTS1 = ?, EXP_SHOTS2 = ?, EXP_GOALS1 = ?, EXP_GOALS2 = ?, ODDS1 = ?, ODDSX = ?, ODDS2 = ? WHERE GAMEID = ?",
[
home_shots, away_shots, home_goals, away_goals,
odds1X2['1'][0], odds1X2['X'][0], odds1X2['2'][0], gameid
])
conn.commit()
c.execute(
"SELECT SUM(ACT_GOALS1 + ACT_GOALS2), SUM(EXP_GOALS1 + EXP_GOALS2) FROM EXP_SHOTS_TABLE WHERE SEASON = ? AND GAMEDATE < ? AND SERIE = ?",
[seasonYear, gamedate, serie])
goals_year = c.fetchall()
exp_ratio = 1
if goals_year[0][0] != None and goals_year[0][1] != None:
if goals_year[0][0] > 0 and goals_year[0][1] > 0:
exp_ratio = goals_year[0][1] / goals_year[0][0]
if goals_year[0][0] < 100:
exp_ratio = exp_ratio**(goals_year[0][0] / 100)
exp_ratio = (exp_ratio - 1) / 4 + 1
print("Exp ratio", exp_ratio)
home_goals /= exp_ratio # Adjustment based on expectency ratio
away_goals /= exp_ratio # Adjustment based on expectency ratio
print("Expected goals combined models:", home_goals, away_goals)
# Get result matrix and match odds
results, odds1X2, odds45 = get_result_matrix(home_goals, away_goals)
result_odds = 1 / results
#print(result_odds)
print(odds1X2)
print(odds45)
else:
c.execute(
"SELECT ODDS1, ODDSX, ODDS2 FROM EXP_SHOTS_TABLE WHERE GAMEID = ?",
[gameid])
odds = c.fetchall()
if len(odds) > 0:
odds1X2 = pd.DataFrame([[odds[0][0], odds[0][1], odds[0][2]]],
columns=['1', 'X', '2'])
else:
odds1X2 = pd.DataFrame([[0.43, 0.22, 0.35]],
columns=['1', 'X', '2'])
results = []
odds45 = [[0.5, 0.5]]
home_goals = 2.25
away_goals = 2.25
act_home_goals = 0
act_away_goals = 0
####################################################################################################################################################################
### TIME FOR PLAYERS DATA NOW ###
####################################################################################################################################################################
if run_player_data == 1:
# Get keeper stats
keeper_stat_home_temp = get_keeper_data(hometeam, gamedate, seasonYear,
starting_keeper_home, c, conn)
keeper_stat_away_temp = get_keeper_data(awayteam, gamedate, seasonYear,
starting_keeper_away, c, conn)
#If available get the correct keepers to start
if starting_keeper_home[0] != "":
keeper_stat_home = keeper_stat_home_temp[
keeper_stat_home_temp['Forname'] == starting_keeper_home[0]]
keeper_stat_home = keeper_stat_home_temp[
keeper_stat_home_temp['Surname'] == starting_keeper_home[1]]
else:
keeper_stat_home = keeper_stat_home_temp[
keeper_stat_home_temp['Forname'] == "GK"]
if starting_keeper_away[0] != "":
keeper_stat_away = keeper_stat_away_temp[
keeper_stat_away_temp['Forname'] == starting_keeper_away[0]]
keeper_stat_away = keeper_stat_away_temp[
keeper_stat_away_temp['Surname'] == starting_keeper_away[1]]
else:
keeper_stat_away = keeper_stat_away_temp[
keeper_stat_away_temp['Forname'] == "GK"]
if keeper_stat_home.empty:
keeper_stat_home = keeper_stat_home_temp[
keeper_stat_home_temp['Forname'] == "GK"]
if keeper_stat_away.empty:
keeper_stat_away = keeper_stat_away_temp[
keeper_stat_away_temp['Forname'] == "GK"]
print(keeper_stat_home.to_string())
print(keeper_stat_away.to_string())
# Get player stats
cl_home = pd.DataFrame()
cl_away = pd.DataFrame()
#if there are lineups ready add them here
if curr_lineup != []:
cl = pd.DataFrame(curr_lineup,
columns=[
'Gameid', 'Team', 'Number', 'Forname',
'Surname', 'Line', 'Starting', 'Audience',
'Venue', 'Season'
])
cl['Personnr'] = ""
cl_home = cl[['Forname', 'Surname', 'Personnr',
'Line']][cl['Team'] == hometeam]
cl_away = cl[['Forname', 'Surname', 'Personnr',
'Line']][cl['Team'] == awayteam]
home_player_stat = get_player_data(hometeam, gameid, gamedate,
odds1X2['1'][0], keeper_stat_away,
seasonYear, serie, cl_home, c, conn)
away_player_stat = get_player_data(awayteam, gameid, gamedate,
odds1X2['2'][0], keeper_stat_home,
seasonYear, serie, cl_away, c, conn)
#print(home_player_stat.to_string())
#print(away_player_stat.to_string())
model1_home = get_model1_features(home_player_stat, c)
model1_away = get_model1_features(away_player_stat, c)
print(model1_home.to_string())
print(model1_away.to_string())
return results, odds1X2, odds45, home_goals, away_goals, act_home_goals, act_away_goals