API tools faq
paste
Advertisement
Guest User

Untitled

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