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 offensive_war(year):
player_q = """SELECT
player_name,
team_abb,
position,
age,
pa,
ab,
(h-2b-3b-hr) as 1b, 2b, 3b, hr, r, rbi, bb, k, hbp, sb, cs, ops, babip
FROM register_batting_primary
JOIN register_batting_secondary USING (year, player_name, team_abb, position, age)
JOIN register_batting_analytical USING (year, player_name, team_abb, position, age)
WHERE year = %s;
"""
player_qry = player_q % (year)
player_data = db.query(player_qry)
entries = []
for row in player_data:
entry = {}
entry['year'] = year
player_name, team_abb, position, age, pa, ab, _1b, _2b, _3b, hr, r, rbi, bb, k, hbp, sb, cs, ops, babip = row
entry['player_name'] = player_name
entry['team_abb'] = team_abb
entry['position'] = position
# if player_name[len(player_name)-1:] == "*":
# bats = 'l'
# elif player_name[len(player_name)-1:] == "#":
# bats = 's'
# else:
# bats = 'r'
# entry['bats'] = bats
entry['age'] = age
entry['pa'] = pa
team_abb = team_abb.upper()
pf = float(helper.get_park_factors(team_abb, year)) / float(100)
entry['pf'] = pf
entry['ops'] = ops
entry['babip'] = babip
foo, wOBA, park_wOBA, OPS_plus, wrc, wrc27, wRC_plus, raa, oWAR = helper.get_offensive_metrics(
year, pf, pa, ab, bb, hbp, _1b, _2b, _3b, hr, sb, cs)
entry['wOBA'] = wOBA
entry['park_wOBA'] = park_wOBA
entry['OPS_plus'] = OPS_plus
entry['wrc'] = wrc
entry['wRC_27'] = wrc27
entry['wRC_plus'] = wRC_plus
entry['raa'] = raa
entry['oWAR'] = oWAR
entries.append(entry)
table = 'processed_compWAR_offensive'
if entries != []:
db.insertRowDict(entries, table, replace=True, insertMany=True, rid=0)
db.conn.commit()
def pitchers(year):
player_q = """SELECT a.year
, IFNULL(CONCAT(nm.right_fname, ' ', nm.right_lname), a.Player) AS player
, a.team_abb
, a.age
, T as hand
, ERA
, a.G
, a.GS
, IP
, H
, ER
, HR
, BB
, SO
, k_9
, bb_9
, hr_9
, bb_pct
, k_pct
, BABIP
, ERA_Plus
, ERA_minus
, COALESCE(a.FIP, c.FIP) AS FIP
, 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_pitchers_counting a
JOIN(
SELECT year
, Player
, MAX(post_date) AS post_date
FROM zips_fangraphs_pitchers_counting
WHERE 1
AND year = %s
GROUP BY year, Player
) b USING (year,Player,post_date)
LEFT JOIN zips_fangraphs_pitchers_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 nm2.start_year = nm2.start_year)
AND (nm.end_year IS NULL OR nm2.end_year = nm2.end_year)
AND (nm.position = '' OR nm2.position = nm2.position)
AND (nm.rl_team = '' OR nm2.rl_team = nm2.rl_team)
)
JOIN zips_FA_contract_value_pitchers 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, era, g, gs, ip, h, er, hr, bb, k, k_9, bb_9, hr_9, bb_pct, k_pct, babip, zera_plus, zera_minus, zfip, zwar, 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
r = er
if (gs >= 20 or float(gs) / float(g) > 0.8):
pos = 'SP'
else:
pos = 'RP'
team_abb = team_abb.upper()
pf = float(helper.get_park_factors(team_abb, year - 1)) / float(100)
if float(bb) == 0:
if float(k) > 0:
k_bb = 99.0
else:
k_bb = 0.0
else:
k_bb = (float(k) / float(bb))
fip_const = float(
helper.get_zips_average_pitchers(year - 1, 'fip_const'))
FIP = ((((13 * float(hr)) + (3 * float(bb)) -
(2 * float(k))) / float(ip)) + fip_const)
park_FIP, FIP_min, FIP_WAR = helper.get_zips_pitching_metrics(
FIP, ip, year - 1, pf, g, gs, 'fip')
ERA = float(era)
park_ERA, ERA_min, ERA_WAR = helper.get_zips_pitching_metrics(
ERA, ip, year - 1, pf, g, gs, 'era')
if pos == 'SP':
FIP_WAR = 32 * (float(FIP_WAR) / float(gs))
ERA_WAR = 32 * (float(ERA_WAR) / float(gs))
elif pos == 'RP':
FIP_WAR = float(FIP_WAR)
ERA_WAR = float(ERA_WAR)
if k_pct is not None and bb_pct is not None:
k_minus_bb_pct = float(k_pct) - float(bb_pct)
else:
k_minus_bb_pct = None
entry['year'] = year
entry['player_name'] = player_name
entry['team_abb'] = team_abb
entry['age'] = age
entry['hand'] = hand
entry['pos'] = pos
entry['pf'] = pf
entry['g'] = g
entry['gs'] = gs
entry['ip'] = ip
entry['babip'] = babip
entry['k_9'] = k_9
entry['bb_9'] = bb_9
entry['k_bb'] = k_bb
entry['hr_9'] = hr_9
entry['k_pct'] = k_pct
entry['bb_pct'] = bb_pct
entry['k_minus_bb_pct'] = k_minus_bb_pct
entry['zERA_plus'] = zera_plus
entry['zERA_minus'] = zera_minus
entry['zFIP'] = zfip
entry['zWAR'] = zwar
entry['FIP'] = FIP
entry['park_FIP'] = park_FIP
entry['FIP_minus'] = FIP_min
entry['FIP_WAR'] = FIP_WAR
entry['ERA'] = era
entry['park_ERA'] = park_ERA
entry['ERA_minus'] = ERA_min
entry['ERA_WAR'] = ERA_WAR
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_pitchers'
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()
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()
def calculate_war():
player_q = """SELECT
year,
player_name,
team_abb,
age,
g,
gs,
era,
ip,
h, r, er, bb, so, hr
FROM zips_pitching
"""
player_qry = player_q
player_data = db.query(player_qry)
entries = []
for row in player_data:
entry = {}
year, player_name, team_abb, age, g, gs, era, ip, h, r, er, bb, k, hr = row
print year, player_name
team_abb = team_abb.upper()
pf = float(helper.get_park_factors(team_abb, year - 1)) / float(100)
if ip == 0:
k_9 = 0.0
if bb > 0:
bb_9 = 99.0
k_bb = 99.0
else:
bb_9 = 0.0
k_bb = 0.0
if hr > 0:
hr_9 = 99.0
else:
hr_9 = 0.0
else:
k_9 = (float(k) / float(ip)) * 9
bb_9 = (float(bb) / float(ip)) * 9
hr_9 = (float(hr) / float(ip)) * 9
if bb == 0:
if k > 0:
k_bb = 99.0
else:
k_bb = 0.0
else:
k_bb = (float(k) / float(bb))
fip_const = float(
helper.get_zips_average_pitchers(year - 1, 'fip_const'))
FIP = ((((13 * float(hr)) + (3 * float(bb)) -
(2 * float(k))) / float(ip)) + fip_const)
park_FIP, FIP_min, FIP_WAR = helper.get_zips_pitching_metrics(
FIP, ip, year - 1, pf, g, gs, 'fip')
ERA = float(era)
park_ERA, ERA_min, ERA_WAR = helper.get_zips_pitching_metrics(
ERA, ip, year - 1, pf, g, gs, 'era')
entry['year'] = year
entry['player_name'] = player_name
entry['team_abb'] = team_abb
entry['age'] = age
entry['pf'] = pf
entry['ip'] = ip
entry['k_9'] = k_9
entry['bb_9'] = bb_9
entry['k_bb'] = k_bb
entry['hr_9'] = hr_9
entry['FIP'] = FIP
entry['park_FIP'] = park_FIP
entry['FIP_minus'] = FIP_min
entry['FIP_WAR'] = FIP_WAR
entry['ERA'] = era
entry['park_ERA'] = park_ERA
entry['ERA_minus'] = ERA_min
entry['ERA_WAR'] = ERA_WAR
entries.append(entry)
table = 'zips_WAR_pitchers'
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 pitching_war(year):
player_q = """SELECT
player_name,
team_abb,
position,
age,
g,
gs,
era,
ROUND(ip) + (10 * (ip - ROUND(ip)) / 3) as ip,
h, r, er, bb, k, hr
FROM register_pitching_primary
WHERE year = %s;
"""
player_qry = player_q % (year)
player_data = db.query(player_qry)
entries = []
for row in player_data:
entry = {}
player_name, team_abb, position, age, g, gs, era, ip, h, r, er, bb, k, hr = row
entry['year'] = year
entry['player_name'] = player_name
entry['team_abb'] = team_abb
entry['position'] = position
throws = None
entry['throws'] = throws
entry['age'] = age
entry['ip'] = ip
team_abb = team_abb.upper()
pf = float(helper.get_park_factors(team_abb, year)) / float(100)
entry['pf'] = pf
if ip == 0:
k_9 = 0.0
if bb > 0:
bb_9 = 99.0
k_bb = 99.0
else:
bb_9 = 0.0
k_bb = 0.0
if hr > 0:
hr_9 = 99.0
else:
hr_9 = 0.0
else:
k_9 = (float(k) / float(ip)) * 9
bb_9 = (float(bb) / float(ip)) * 9
hr_9 = (float(hr) / float(ip)) * 9
if bb == 0:
if k > 0:
k_bb = 99.0
else:
k_bb = 0.0
else:
k_bb = (float(k) / float(bb))
entry['k_9'] = k_9
entry['bb_9'] = bb_9
entry['k_bb'] = k_bb
entry['hr_9'] = hr_9
fip_const = float(helper.get_league_average_pitchers(
year, 'fip_const'))
if ip == 0:
FIP = 99.99
else:
FIP = ((((13 * float(hr)) + (3 * float(bb)) -
(2 * float(k))) / float(ip)) + fip_const)
entry['FIP'] = FIP
park_FIP, FIP_min, FIP_WAR = helper.get_pitching_metrics(
FIP, ip, year, pf, g, gs, 'fip')
entry['park_FIP'] = park_FIP
entry['FIP_minus'] = FIP_min
entry['FIP_WAR'] = FIP_WAR
ERA = float(era)
entry['ERA'] = ERA
park_ERA, ERA_min, ERA_WAR = helper.get_pitching_metrics(
ERA, ip, year, pf, g, gs, 'era')
entry['park_ERA'] = park_ERA
entry['ERA_minus'] = ERA_min
entry['ERA_WAR'] = ERA_WAR
entries.append(entry)
table = 'processed_WAR_pitchers'
if entries != []:
db.insertRowDict(entries, table, replace=True, insertMany=True, rid=0)
db.conn.commit()
