API tools faq
paste
Advertisement
Guest User

Untitled

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