def calculate_war(): player_q = """SELECT year, player_name, team_abb, ab, h, 2b, 3b, hr, bb, so, hbp, ibb, sh, sf, sb, cs FROM zips_offense """ player_qry = player_q player_data = db.query(player_qry) entries = [] for row in player_data: entry = {} year, player_name, team_abb, ab, h, _2, _3, hr, bb, so, hbp, ibb, sh, sf, sb, cs = row pa = ab + bb + hbp + ibb + sh + sf bb2 = bb + ibb _1 = h - _2 - _3 - hr team_abb = team_abb.upper() pf = float(helper.get_park_factors(team_abb, year - 1)) / float(100) babip = float((h - hr)) / float((ab + sh + sf - so - hr)) ops, wOBA, park_wOBA, OPS_plus, wrc, wrc27, wRC_plus, raa, oWAR = helper.get_zips_offensive_metrics( year - 1, pf, pa, ab, bb2, hbp, _1, _2, _3, hr, sb, cs) entry['year'] = year entry['player_name'] = player_name entry['team_abb'] = team_abb entry['pf'] = pf entry['pa'] = pa entry['babip'] = babip entry['OPS_plus'] = OPS_plus entry['park_wOBA'] = park_wOBA entry['wRC_plus'] = wRC_plus entries.append(entry) table = 'zips_WAR_hitters_comp' if entries != []: for i in range(0, len(entries), 1000): db.insertRowDict(entries[i:i + 1000], table, insertMany=True, replace=True, rid=0, debug=1) db.conn.commit()
def batters(year): player_q = """SELECT a.year , IFNULL(CONCAT(nm.right_fname, ' ', nm.right_lname), a.Player) AS player , a.team_abb , a.age , a.B as hand , a.PO , COALESCE(a.PA, c.PA) AS pa , a.ab , a.h , a.2b , a.3b , a.hr , a.bb , a.so , a.sb , a.cs , BA , OBP , SLG , BABIP , OPS_Plus , DEF , c.WAR , cv.yr1_WAR , cv.yr1_value , cv.yr2_WAR , cv.yr2_value , cv.yr3_WAR , cv.yr3_value , cv.yr4_WAR , cv.yr4_value , cv.yr5_WAR , cv.yr5_value , cv.yr6_WAR , cv.yr6_value , cv.yr7_WAR , cv.yr7_value , cv.yr8_WAR , cv.yr8_value FROM zips_fangraphs_batters_counting a JOIN( SELECT year , Player , MAX(post_date) AS post_date FROM zips_fangraphs_batters_counting WHERE 1 AND year = %s GROUP BY year, Player ) b USING (year,Player,post_date) LEFT JOIN zips_fangraphs_batters_rate c USING (year, Player, team_abb) LEFT JOIN name_mapper nm ON (1 AND a.Player = nm.wrong_name AND (nm.start_year IS NULL OR nm.start_year <= a.year) AND (nm.end_year IS NULL OR nm.end_year >= a.year) AND (nm.position = '' OR nm.position = a.PO) AND (nm.rl_team = '' OR nm.rl_team = a.team_abb) # AND (nm.nsbl_team = '' OR nm.nsbl_team = rbp.team_abb) ) LEFT JOIN name_mapper nm2 ON (nm.right_fname = nm2.right_fname AND nm.right_lname = nm2.right_lname AND (nm.start_year IS NULL OR nm.start_year = nm2.start_year) AND (nm.end_year IS NULL OR nm.end_year = nm2.end_year) AND (nm.position = '' OR nm.position = nm2.position) AND (nm.rl_team = '' OR nm.rl_team = nm2.rl_team) ) JOIN zips_FA_contract_value_batters cv ON (a.year = cv.year AND a.team_abb = cv.team_abb AND IFNULL(nm2.wrong_name, a.Player) = cv.Player ) ;""" player_qry = player_q % (year) # raw_input(player_qry) player_data = db.query(player_qry) entries = [] for row in player_data: entry = {} year, player_name, team_abb, age, hand, po, pa, ab, h, _2, _3, hr, bb, so, sb, cs, ba, obp, slg, babip, zOPS_Plus, DEF, WAR, yr1_WAR, yr1_value, yr2_WAR, yr2_value, yr3_WAR, yr3_value, yr4_WAR, yr4_value, yr5_WAR, yr5_value, yr6_WAR, yr6_value, yr7_WAR, yr7_value, yr8_WAR, yr8_value = row if pa is None: pa = ab + bb bb2 = bb hbp = 0 _1 = h - _2 - _3 - hr team_abb = team_abb.upper() pf = float(helper.get_park_factors(team_abb, year - 1)) / float(100) if po.lower() != 'c': scaledWAR = 600 * (float(WAR) / float(pa)) else: scaledWAR = 450 * (float(WAR) / float(pa)) ops, wOBA, park_wOBA, OPS_plus, wrc, wrc27, wRC_plus, raa, oWAR = helper.get_zips_offensive_metrics( year - 1, pf, pa, ab, bb2, hbp, _1, _2, _3, hr, sb, cs) entry['year'] = year entry['player_name'] = player_name entry['team_abb'] = team_abb entry['age'] = age entry['hand'] = hand entry['pos'] = po entry['pf'] = pf entry['pa'] = pa entry['ba'] = ba entry['obp'] = obp entry['slg'] = slg entry['zOPS_Plus'] = zOPS_Plus entry['DEF'] = DEF entry['zWAR'] = WAR entry['babip'] = babip entry['OPS_plus'] = OPS_plus entry['park_wOBA'] = park_wOBA entry['wRC_plus'] = wRC_plus entry['scaledWAR'] = scaledWAR entry['yr1_WAR'] = yr1_WAR entry['yr1_value'] = yr1_value entry['yr2_WAR'] = yr2_WAR entry['yr2_value'] = yr2_value entry['yr3_WAR'] = yr3_WAR entry['yr3_value'] = yr3_value entry['yr4_WAR'] = yr4_WAR entry['yr4_value'] = yr4_value entry['yr5_WAR'] = yr5_WAR entry['yr5_value'] = yr5_value entry['yr6_WAR'] = yr6_WAR entry['yr6_value'] = yr6_value entry['yr7_WAR'] = yr7_WAR entry['yr7_value'] = yr7_value entry['yr8_WAR'] = yr8_WAR entry['yr8_value'] = yr8_value entries.append(entry) table = 'zips_fangraphs_prep_FA_batters' print table if entries != []: for i in range(0, len(entries), 1000): db.insertRowDict(entries[i:i + 1000], table, insertMany=True, replace=True, rid=0, debug=1) db.conn.commit()