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()