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