API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Sep 18th, 2016
109
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 12.33 KB | None | 0 0
raw download clone embed print report
  1. retrieveYearlyBaseballStats <- function(year){
  2. library(RMySQL)
  3. library(ggplot2)
  4. library(dplyr)
  5. mydb = dbConnect(MySQL(), user='root', password='marinecore', dbname='fangraphs_stats_parallel', host='localhost')
  6. batter_sql <- "
  7. SELECT
  8. non_pitcher_game_logs.*,
  9. IF(non_pitcher_game_logs.home_or_away = 'opposing_team', non_pitcher_game_logs.Opp, non_pitcher_game_logs.Team) stadium,
  10. opp_fielding.*
  11. FROM
  12. (SELECT
  13. rotoguru_player_id,
  14. CONCAT(
  15. IF(REPLACE(Team, '@', '') > REPLACE(Opp, '@', ''), REPLACE(Team, '@', ''), REPLACE(Opp, '@', '')),
  16. '_', Date, '_',
  17. IF(REPLACE(Team, '@', '') > REPLACE(Opp, '@', ''), REPLACE(Opp, '@', ''), REPLACE(Team, '@', ''))) game_id,
  18. CASE
  19. WHEN REPLACE(Team, '@', '') = 'ANA' THEN 'Angels'
  20. WHEN REPLACE(Team, '@', '') = 'ARI' THEN 'Diamondbacks'
  21. WHEN REPLACE(Team, '@', '') = 'ATL' THEN 'Braves'
  22. WHEN REPLACE(Team, '@', '') = 'BAL' THEN 'Orioles'
  23. WHEN REPLACE(Team, '@', '') = 'BOS' THEN 'Red Sox'
  24. WHEN REPLACE(Team, '@', '') = 'CHC' THEN 'Cubs'
  25. WHEN REPLACE(Team, '@', '') = 'CHW' THEN 'White Sox'
  26. WHEN REPLACE(Team, '@', '') = 'CIN' THEN 'Reds'
  27. WHEN REPLACE(Team, '@', '') = 'CLE' THEN 'Indians'
  28. WHEN REPLACE(Team, '@', '') = 'COL' THEN 'Rockies'
  29. WHEN REPLACE(Team, '@', '') = 'DET' THEN 'Tigers'
  30. WHEN REPLACE(Team, '@', '') = 'FLA' THEN 'Marlins'
  31. WHEN REPLACE(Team, '@', '') = 'HOU' THEN 'Astros'
  32. WHEN REPLACE(Team, '@', '') = 'KCR' THEN 'Royals'
  33. WHEN REPLACE(Team, '@', '') = 'LAA' THEN 'Angels'
  34. WHEN REPLACE(Team, '@', '') = 'LAD' THEN 'Dodgers'
  35. WHEN REPLACE(Team, '@', '') = 'MIA' THEN 'Marlins'
  36. WHEN REPLACE(Team, '@', '') = 'MIL' THEN 'Brewers'
  37. WHEN REPLACE(Team, '@', '') = 'MIN' THEN 'Twins'
  38. WHEN REPLACE(Team, '@', '') = 'NYM' THEN 'Mets'
  39. WHEN REPLACE(Team, '@', '') = 'NYY' THEN 'Yankees'
  40. WHEN REPLACE(Team, '@', '') = 'OAK' THEN 'Athletics'
  41. WHEN REPLACE(Team, '@', '') = 'PHI' THEN 'Phillies'
  42. WHEN REPLACE(Team, '@', '') = 'PIT' THEN 'Pirates'
  43. WHEN REPLACE(Team, '@', '') = 'SDP' THEN 'Padres'
  44. WHEN REPLACE(Team, '@', '') = 'SEA' THEN 'Pirates'
  45. WHEN REPLACE(Team, '@', '') = 'SFG' THEN 'Giants'
  46. WHEN REPLACE(Team, '@', '') = 'STL' THEN 'Cardinals'
  47. WHEN REPLACE(Team, '@', '') = 'TEX' THEN 'Rangers'
  48. WHEN REPLACE(Team, '@', '') = 'TOR' THEN 'Blue Jays'
  49. WHEN REPLACE(Team, '@', '') = 'WSN' THEN 'Nationals'
  50. ELSE REPLACE(Team, '@', '') END Team_full,
  51. CASE
  52. WHEN REPLACE(Opp, '@', '') = 'ANA' THEN 'Angels'
  53. WHEN REPLACE(Opp, '@', '') = 'ARI' THEN 'Diamondbacks'
  54. WHEN REPLACE(Opp, '@', '') = 'ATL' THEN 'Braves'
  55. WHEN REPLACE(Opp, '@', '') = 'BAL' THEN 'Orioles'
  56. WHEN REPLACE(Opp, '@', '') = 'BOS' THEN 'Red Sox'
  57. WHEN REPLACE(Opp, '@', '') = 'CHC' THEN 'Cubs'
  58. WHEN REPLACE(Opp, '@', '') = 'CHW' THEN 'White Sox'
  59. WHEN REPLACE(Opp, '@', '') = 'CIN' THEN 'Reds'
  60. WHEN REPLACE(Opp, '@', '') = 'CLE' THEN 'Indians'
  61. WHEN REPLACE(Opp, '@', '') = 'COL' THEN 'Rockies'
  62. WHEN REPLACE(Opp, '@', '') = 'DET' THEN 'Tigers'
  63. WHEN REPLACE(Opp, '@', '') = 'FLA' THEN 'Marlins'
  64. WHEN REPLACE(Opp, '@', '') = 'HOU' THEN 'Astros'
  65. WHEN REPLACE(Opp, '@', '') = 'KCR' THEN 'Royals'
  66. WHEN REPLACE(Opp, '@', '') = 'LAA' THEN 'Angels'
  67. WHEN REPLACE(Opp, '@', '') = 'LAD' THEN 'Dodgers'
  68. WHEN REPLACE(Opp, '@', '') = 'MIA' THEN 'Marlins'
  69. WHEN REPLACE(Opp, '@', '') = 'MIL' THEN 'Brewers'
  70. WHEN REPLACE(Opp, '@', '') = 'MIN' THEN 'Twins'
  71. WHEN REPLACE(Opp, '@', '') = 'NYM' THEN 'Mets'
  72. WHEN REPLACE(Opp, '@', '') = 'NYY' THEN 'Yankees'
  73. WHEN REPLACE(Opp, '@', '') = 'OAK' THEN 'Athletics'
  74. WHEN REPLACE(Opp, '@', '') = 'PHI' THEN 'Phillies'
  75. WHEN REPLACE(Opp, '@', '') = 'PIT' THEN 'Pirates'
  76. WHEN REPLACE(Opp, '@', '') = 'SDP' THEN 'Padres'
  77. WHEN REPLACE(Opp, '@', '') = 'SEA' THEN 'Pirates'
  78. WHEN REPLACE(Opp, '@', '') = 'SFG' THEN 'Giants'
  79. WHEN REPLACE(Opp, '@', '') = 'STL' THEN 'Cardinals'
  80. WHEN REPLACE(Opp, '@', '') = 'TEX' THEN 'Rangers'
  81. WHEN REPLACE(Opp, '@', '') = 'TOR' THEN 'Blue Jays'
  82. WHEN REPLACE(Opp, '@', '') = 'WSN' THEN 'Nationals'
  83. ELSE REPLACE(Opp, '@', '') END Opp_full,
  84. Team,
  85. Opp,
  86. CASE
  87. WHEN Opp like '%%@%%' THEN Opp
  88. ELSE Team END home_or_away,
  89. Date,
  90. CAST(BO AS DECIMAL) BO,
  91. CAST(Pos AS DECIMAL) Pos,
  92. CAST(G AS DECIMAL) G,
  93. CAST(AB AS DECIMAL) AB,
  94. CAST(H AS DECIMAL) H,
  95. CAST(1B AS DECIMAL) 1B,
  96. CAST(2B AS DECIMAL) 2B,
  97. CAST(3B AS DECIMAL) 3B,
  98. CAST(HR AS DECIMAL) HR,
  99. CAST(R AS DECIMAL) R,
  100. CAST(RBI AS DECIMAL) RBI,
  101. CAST(BB AS DECIMAL) BB,
  102. CAST(IBB AS DECIMAL) IBB,
  103. CAST(SO AS DECIMAL) SO,
  104. CAST(HBP AS DECIMAL) HBP,
  105. CAST(SF AS DECIMAL) SF,
  106. CAST(SH AS DECIMAL) SH,
  107. CAST(GDP AS DECIMAL) GDP,
  108. CAST(SB AS DECIMAL) SB,
  109. CAST(CS AS DECIMAL) CS,
  110. CAST(AVG AS DECIMAL) AVG
  111. FROM
  112. game_logs_expanded_non_pitcher_game_logs_expanded
  113. WHERE
  114. Date > '%d-01-01' AND
  115. Date != 'Total' AND
  116. CONCAT(Opp, '_', Date) IS NOT NULL) non_pitcher_game_logs
  117. JOIN
  118. (SELECT
  119. CAST(ARM AS DECIMAL) opp_fielding_ARM,
  120. CAST(BIZ AS DECIMAL) opp_fielding_BIZ,
  121. CAST(DPR AS DECIMAL) opp_fielding_DPR,
  122. CAST(DRS AS DECIMAL) opp_fielding_DRS,
  123. CAST(Def AS DECIMAL) opp_fielding_Def,
  124. CAST(ErrR AS DECIMAL) opp_fielding_ErrR,
  125. CAST(Inn AS DECIMAL) opp_fielding_Inn,
  126. CAST(OOZ AS DECIMAL) opp_fielding_OOZ,
  127. CAST(Plays AS DECIMAL) opp_fielding_Plays,
  128. CAST(RZR AS DECIMAL) opp_fielding_RZR,
  129. CAST(RngR AS DECIMAL) opp_fielding_RngR,
  130. Team opp_fielding_Team,
  131. CAST(UZR AS DECIMAL) opp_fielding_UZR,
  132. CAST(UZR_150 AS DECIMAL) opp_fielding_UZR_150,
  133. CAST(pound AS DECIMAL) opp_fielding_pound,
  134. CAST(rARM AS DECIMAL) opp_fielding_rARM,
  135. CAST(rGDP AS DECIMAL) opp_fielding_rGDP,
  136. CAST(rGFP AS DECIMAL) opp_fielding_rGFP,
  137. CAST(rPM AS DECIMAL) opp_fielding_rPM,
  138. CAST(rSB AS DECIMAL) opp_fielding_rSB,
  139. Year opp_fielding_Year
  140. FROM
  141. teams_fielding_advanced
  142. WHERE
  143. Year = '%d'
  144. ) opp_fielding
  145. ON
  146. opp_fielding.opp_fielding_Team = non_pitcher_game_logs.Opp_full
  147. "
  148. batter_sql <- sprintf(batter_sql, year, year-1)
  149. rs <- dbSendQuery(mydb, batter_sql)
  150. df_batting <- fetch(rs, n=-1)
  151.  
  152.  
  153. pitcher_sql <- "
  154. SELECT
  155. pitcher_season_stats.*,
  156. pitcher_advanced_season_stats.pitcher_K_9,
  157. pitcher_advanced_season_stats.pitcher_BB_9,
  158. pitcher_advanced_season_stats.pitcher_K_BB,
  159. pitcher_advanced_season_stats.pitcher_HR_9,
  160. pitcher_advanced_season_stats.pitcher_K_percent_,
  161. pitcher_advanced_season_stats.pitcher_BB_percent_,
  162. pitcher_advanced_season_stats.pitcher_AVG,
  163. pitcher_advanced_season_stats.pitcher_WHIP,
  164. pitcher_advanced_season_stats.pitcher_BABIP,
  165. pitcher_advanced_season_stats.pitcher_LOB_percent_,
  166. pitcher_advanced_season_stats.pitcher_ERA_,
  167. pitcher_advanced_season_stats.pitcher_FIP_,
  168. pitcher_advanced_season_stats.pitcher_FIP,
  169. pitcher_for_game.game_id
  170. FROM
  171. (SELECT
  172. CAST(W AS DECIMAL) pitcher_season_W,
  173. CAST(L AS DECIMAL) pitcher_season_L,
  174. CAST(ERA AS DECIMAL) pitcher_season_ERA,
  175. CAST(G AS DECIMAL) pitcher_season_G,
  176. CAST(GS AS DECIMAL) pitcher_season_GS,
  177. CAST(CG AS DECIMAL) pitcher_season_CG,
  178. CAST(ShO AS DECIMAL) pitcher_season_ShO,
  179. CAST(SV AS DECIMAL) pitcher_season_SV,
  180. CAST(HLD AS DECIMAL) pitcher_season_HLD,
  181. CAST(BS AS DECIMAL) pitcher_season_BS,
  182. CAST(IP AS DECIMAL) pitcher_season_IP,
  183. CAST(TBF AS DECIMAL) pitcher_season_TBF,
  184. CAST(H AS DECIMAL) pitcher_season_H,
  185. CAST(R AS DECIMAL) pitcher_season_R,
  186. CAST(ER AS DECIMAL) pitcher_season_ER,
  187. CAST(HR AS DECIMAL) pitcher_season_HR,
  188. CAST(BB AS DECIMAL) pitcher_season_BB,
  189. CAST(IBB AS DECIMAL) pitcher_season_IBB,
  190. CAST(HBP AS DECIMAL) pitcher_season_HBP,
  191. CAST(WP AS DECIMAL) pitcher_season_WP,
  192. CAST(BK AS DECIMAL) pitcher_season_BK,
  193. CAST(SO AS DECIMAL) pitcher_season_SO,
  194. rotoguru_player_id pitcher_season_rotoguru_player_id,
  195. Team pitcher_Opp_full
  196. FROM
  197. season_pitcher_standard
  198. WHERE
  199. Team IN ('2 Teams','Rays', 'Angels', 'Diamondbacks' , 'Braves' , 'Orioles' , 'Red Sox' , 'Cubs', 'White Sox', 'Reds', 'Indians', 'Rockies', 'Tigers', 'Marlins', 'Astros', 'Royals', 'Angels', 'Dodgers', 'Marlins', 'Brewers', 'Twins', 'Mets', 'Yankees', 'Athletics', 'Phillies', 'Pirates', 'Padres', 'Pirates', 'Giants', 'Cardinals', 'Rangers', 'Blue Jays', 'Nationals') AND
  200. Season = '%d'
  201. ) pitcher_season_stats
  202. JOIN
  203. (SELECT
  204. CAST(K_9 AS DECIMAL) pitcher_K_9,
  205. CAST(BB_9 AS DECIMAL) pitcher_BB_9,
  206. CAST(K_BB AS DECIMAL) pitcher_K_BB,
  207. CAST(HR_9 AS DECIMAL) pitcher_HR_9,
  208. CAST(K_percent_ AS DECIMAL) pitcher_K_percent_,
  209. CAST(BB_percent_ AS DECIMAL) pitcher_BB_percent_,
  210. CAST(K_BB_percent_ AS DECIMAL) pitcher_K_BB_percent_,
  211. CAST(AVG AS DECIMAL) pitcher_AVG,
  212. CAST(WHIP AS DECIMAL) pitcher_WHIP,
  213. CAST(BABIP AS DECIMAL) pitcher_BABIP,
  214. CAST(LOB_percent_ AS DECIMAL) pitcher_LOB_percent_,
  215. CAST(ERA_ AS DECIMAL) pitcher_ERA_,
  216. CAST(FIP_ AS DECIMAL) pitcher_FIP_,
  217. CAST(FIP AS DECIMAL) pitcher_FIP,
  218. rotoguru_player_id
  219. FROM
  220. season_pitcher_advanced
  221. WHERE
  222. Team IN ('2 Teams','Rays', 'Angels', 'Diamondbacks' , 'Braves' , 'Orioles' , 'Red Sox' , 'Cubs', 'White Sox', 'Reds', 'Indians', 'Rockies', 'Tigers', 'Marlins', 'Astros', 'Royals', 'Angels', 'Dodgers', 'Marlins', 'Brewers', 'Twins', 'Mets', 'Yankees', 'Athletics', 'Phillies', 'Pirates', 'Padres', 'Pirates', 'Giants', 'Cardinals', 'Rangers', 'Blue Jays', 'Nationals') AND
  223. Season = '%d') pitcher_advanced_season_stats
  224. ON
  225. pitcher_advanced_season_stats.rotoguru_player_id = pitcher_season_stats.pitcher_season_rotoguru_player_id
  226. JOIN
  227. (SELECT
  228. rotoguru_player_id,
  229. CONCAT(
  230. IF(REPLACE(Team, '@', '') > REPLACE(Opp, '@', ''), REPLACE(Team, '@', ''), REPLACE(Opp, '@', '')),
  231. '_', Date, '_',
  232. IF(REPLACE(Team, '@', '') > REPLACE(Opp, '@', ''), REPLACE(Opp, '@', ''), REPLACE(Team, '@', ''))) game_id
  233. FROM
  234. game_logs_expanded_pitcher_games_logs_expanded
  235. WHERE
  236. Date > '%d-01-01' AND
  237. GS = 1 AND
  238. CONCAT(Team, '_', Date) IS NOT NULL) pitcher_for_game
  239. ON
  240. pitcher_for_game.rotoguru_player_id = pitcher_season_stats.pitcher_season_rotoguru_player_id"
  241. pitcher_sql <- sprintf(pitcher_sql, year-1, year-1, year)
  242. rs <- dbSendQuery(mydb, pitcher_sql)
  243. df_pitching <- fetch(rs, n=-1)
  244. df <- merge(df_pitching, df_batting, all=TRUE)
  245. dbDisconnect(mydb)
  246. return(df)
  247. }
  248.  
  249. df_recent <- retrieveYearlyBaseballStats(2016)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!