def process_wc(year, timestamp):
print '\tdetermining wild card winners'
teams_query = """SELECT year, team_name, team_abb, division, wild_card, total_playoff_games_played, strength_type
FROM __in_playoff_probabilities
WHERE update_time = (SELECT MAX(update_time) FROM __in_playoff_probabilities)
AND wild_card != 0;"""
res = db.query(teams_query)
for row in res:
year, team_name, team_abb, division, wild_card, total_playoff_games_played, strength_type = row
lg = division[:2]
oppn_qry = """SELECT team_name, team_abb, division, wild_card, total_playoff_games_played, strength_type
FROM __in_playoff_probabilities
WHERE update_time = (SELECT MAX(update_time) FROM __in_playoff_probabilities)
AND wild_card != 0
AND year = %s
AND total_playoff_games_played = %s
AND left(division,2) = '%s'
AND strength_type = '%s'
AND team_name != '%s';"""
oppn_query = oppn_qry % (year, total_playoff_games_played, lg,
strength_type, team_name)
oppns = db.query(oppn_query)
win_wc = []
for oppn in oppns:
oppn_name, oppn_abb, oppn_division, oppn_wild_card, foo, foo = oppn
matchup_prob = 1
series_id = '%sWC' % (lg)
series_wins, series_losses = get_series_data(
series_id, team_name, oppn_name, strength_type)
team_winProb = get_single_game_win_prob(team_abb, oppn_abb,
strength_type, year)
series_games = 1
series_prob = get_series_prob(series_games, series_wins,
series_losses, team_winProb)
win_wc.append(matchup_prob * series_prob)
win_wc = sum(win_wc)
db.updateRow(
{'win_wc': win_wc},
"__in_playoff_probabilities",
("team_name", "year", "total_playoff_games_played",
"strength_type"),
(team_name, year, total_playoff_games_played, strength_type),
operators=['=', '=', '=', '='])
db.conn.commit()
def adjust_probabilities(prob_dict, col_name, sum_to, _type):
bool_temp = False
final_vals = {}
while bool_temp is False:
bool_temp = True
prob_sum = 0.0
for tm, values in prob_dict.items():
curr_prob, max_prob, tm_bool, year, games_played = values
if tm_bool is False:
prob_sum += curr_prob
prob_factor = prob_sum / sum_to
for tm, values in prob_dict.items():
curr_prob, max_prob, tm_bool, year, games_played = values
if prob_factor == 0:
adj_val = curr_prob
else:
adj_val = curr_prob / prob_factor
if (adj_val > max_prob and tm_bool is False):
sum_to = sum_to - max_prob
bool_temp = False
prob_dict[tm] = [curr_prob, max_prob, True, year, games_played]
final_vals[tm] = [max_prob, year, games_played]
elif tm_bool is True:
final_vals[tm] = [max_prob, year, games_played]
else:
prob_dict[tm] = [
curr_prob, max_prob, False, year, games_played
]
final_vals[tm] = [adj_val, year, games_played]
for tm, vals in final_vals.items():
val, year, games_played = vals
db.updateRow({col_name: val},
"__playoff_probabilities",
("team_name", "strength_type", "year", "games_played"),
(tm, _type, year, games_played),
operators=['=', '=', '=', '='])
db.conn.commit()
def process_ds(year, timestamp):
print "\tmake division series"
query = """SELECT year, team_name, total_playoff_games_played, strength_type,
win_division+IFNULL(win_wc,0)
FROM __in_playoff_probabilities
WHERE update_time = (SELECT MAX(update_time) FROM __in_playoff_probabilities);"""
res = db.query(query)
for row in res:
year, team_name, total_playoff_games_played, strength_type, make_ds = row
db.updateRow(
{'make_ds': make_ds},
"__in_playoff_probabilities",
("team_name", "year", "total_playoff_games_played",
"strength_type"),
(team_name, year, total_playoff_games_played, strength_type),
operators=['=', '=', '=', '='])
db.conn.commit()
def process_ds(year):
print "make division series"
for _type in ('roster', 'projected'):
# print '\t', _type
query = "SELECT team_abb, team_name, win_division+win_wc, year, games_played FROM __playoff_probabilities JOIN (SELECT team_abb, MAX(year) AS year, MAX(games_played) AS games_played FROM __playoff_probabilities GROUP BY team_abb, year) t2 USING (team_abb, year, games_played) WHERE strength_type = '%s' AND year = %s;" % (
_type, year)
res = db.query(query)
col_name = 'make_ds'
for row in res:
team_abb, team_name, div_prob, year, games_played = row
db.updateRow(
{col_name: div_prob},
"__playoff_probabilities",
("team_name", "strength_type", "year", "games_played"),
(team_name, _type, year, games_played),
operators=['=', '=', '=', '='])
db.conn.commit()
def process_champion(year, timestamp):
pass
print "\twin world series"
team_query = """SELECT team_abb, team_name, year, total_playoff_games_played, strength_type, division,
make_ws
FROM __in_playoff_probabilities
WHERE update_time = (SELECT MAX(update_time) FROM __in_playoff_probabilities);"""
# raw_input(team_query)
team_res = db.query(team_query)
for team_row in team_res:
team_abb, team_name, year, total_playoff_games_played, strength_type, team_division, make_ws = team_row
lg = team_division[:2]
oppn_qry = """SELECT team_abb, team_name,
make_ws
FROM __in_playoff_probabilities
WHERE update_time = (SELECT MAX(update_time) FROM __in_playoff_probabilities)
AND total_playoff_games_played = %s
AND strength_type = '%s'
AND LEFT(division,2) != '%s'
AND year = %s;"""
oppn_query = oppn_qry % (total_playoff_games_played, strength_type, lg,
year)
# raw_input(oppn_query)
oppn_res = db.query(oppn_query)
win_ws = []
for oppn_row in oppn_res:
oppn_abb, oppn_name, oppn_ws = oppn_row
matchup_prob = float(make_ws) * float(oppn_ws)
series_id = 'WS'
series_wins, series_losses = get_series_data(
series_id, team_name, oppn_name, strength_type)
team_winProb = get_single_game_win_prob(team_abb, oppn_abb,
strength_type, year)
series_games = 7
series_prob = get_series_prob(series_games, series_wins,
series_losses, team_winProb)
win_ws.append(matchup_prob * series_prob)
win_ws = sum(win_ws)
db.updateRow(
{'win_ws': win_ws},
"__in_playoff_probabilities",
("team_name", "year", "total_playoff_games_played",
"strength_type"),
(team_name, year, total_playoff_games_played, strength_type),
operators=['=', '=', '=', '='])
db.conn.commit()
def process_ws(year, timestamp):
print "\tmake world series"
team_query = """SELECT team_abb, team_name, year, total_playoff_games_played, strength_type, division,
IF(top_seed + IFNULL(win_wc,0) > 0, 1, 0) as 'top_round',
IF(win_division-top_seed > 0, 1, 0) as 'middle_round',
make_cs
FROM __in_playoff_probabilities
WHERE update_time = (SELECT MAX(update_time) FROM __in_playoff_probabilities);"""
# raw_input(team_query)
team_res = db.query(team_query)
ws_dict = {}
for team_row in team_res:
team_abb, team_name, year, total_playoff_games_played, strength_type, team_division, top_round, middle_round, make_cs = team_row
lg = team_division[:2]
oppn_qry = """SELECT team_abb, team_name,
IF(top_seed + IFNULL(win_wc,0) > 0, 1, 0) as 'top_round',
IF(win_division-top_seed > 0, 1, 0) as 'middle_round',
make_cs
FROM __in_playoff_probabilities
WHERE update_time = (SELECT MAX(update_time) FROM __in_playoff_probabilities)
AND total_playoff_games_played = %s
AND strength_type = '%s'
AND LEFT(division,2) = '%s'
AND team_name != '%s'
AND year = %s;"""
oppn_query = oppn_qry % (total_playoff_games_played, strength_type, lg,
team_name, year)
# raw_input(oppn_query)
oppn_res = db.query(oppn_query)
make_ws = []
for oppn_row in oppn_res:
oppn_abb, oppn_name, oppn_top_round, oppn_middle_round, oppn_cs = oppn_row
matchup_prob = float(make_cs) * float(oppn_cs) * (
float(top_round) * float(oppn_middle_round) +
float(middle_round) * float(oppn_top_round))
series_id = '%sCS' % (lg)
series_wins, series_losses = get_series_data(
series_id, team_name, oppn_name, strength_type)
team_winProb = get_single_game_win_prob(team_abb, oppn_abb,
strength_type, year)
series_games = 7
series_prob = get_series_prob(series_games, series_wins,
series_losses, team_winProb)
make_ws.append(matchup_prob * series_prob)
make_ws = sum(make_ws)
db.updateRow(
{'make_ws': make_ws},
"__in_playoff_probabilities",
("team_name", "year", "total_playoff_games_played",
"strength_type"),
(team_name, year, total_playoff_games_played, strength_type),
operators=['=', '=', '=', '='])
db.conn.commit()
def process_cs(year, timestamp):
print "\tmake championship series"
team_query = """SELECT team_abb, team_name, year, strength_type, total_playoff_games_played,
division, top_seed, win_division, IFNULL(win_wc,0)
FROM __in_playoff_probabilities
WHERE update_time = (SELECT MAX(update_time) FROM __in_playoff_probabilities);"""
# raw_input(team_query)
team_res = db.query(team_query)
cs_dict = {}
for team_row in team_res:
team_abb, team_name, year, strength_type, total_playoff_games_played, team_division, top_seed, win_div, win_wc = team_row
win_2_3_seed = float(win_div) - float(top_seed)
lg = team_division[:2]
oppn_qry = """SELECT team_abb, team_name, top_seed, win_division, IFNULL(win_wc,0),
IF(division != '%s', win_division-top_seed, 0) as 'middle_seed'
FROM __in_playoff_probabilities
WHERE update_time = (SELECT MAX(update_time) FROM __in_playoff_probabilities)
AND total_playoff_games_played = %s
AND strength_type = '%s'
AND LEFT(division,2) = '%s'
AND team_name != '%s'
AND year = %s;"""
oppn_query = oppn_qry % (team_division, total_playoff_games_played,
strength_type, lg, team_name, year)
# raw_input(oppn_query)
oppn_res = db.query(oppn_query)
make_cs = []
for oppn_row in oppn_res:
oppn_abb, oppn_name, oppn_top, oppn_div, oppn_wc, oppn_2_3_seed = oppn_row
# probability of top_seed * (oppn_wc | not wc)
if (1.0 - float(win_wc)) == 0:
matchup1_prob = 0.0
else:
matchup1_prob = float(top_seed) * float(oppn_wc) / (
1.0 - float(win_wc))
# probability of wc * (oppn_top_seed | not top_seed)
if (1.0 - float(top_seed)) == 0:
matchup2_prob = 0.0
else:
matchup2_prob = float(win_wc) * float(oppn_top) / (
1.0 - float(top_seed))
matchup3_prob = float(win_2_3_seed) * float(oppn_2_3_seed)
matchup_prob = matchup1_prob + matchup2_prob + matchup3_prob
series_id = '%sDS' % (lg)
series_wins, series_losses = get_series_data(
series_id, team_name, oppn_name, strength_type)
team_winProb = get_single_game_win_prob(team_abb, oppn_abb,
strength_type, year)
series_games = 5
series_prob = get_series_prob(series_games, series_wins,
series_losses, team_winProb)
# print oppn_name, matchup1_prob, matchup2_prob, matchup3_prob, series_prob
make_cs.append(matchup_prob * series_prob)
make_cs = sum(make_cs)
db.updateRow(
{'make_cs': make_cs},
"__in_playoff_probabilities",
("team_name", "year", "total_playoff_games_played",
"strength_type"),
(team_name, year, total_playoff_games_played, strength_type),
operators=['=', '=', '=', '='])
db.conn.commit()