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