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