dplyr_db_script.R

library(dplyr)
library(RPostgreSQL)
library(httr)
library(Lahman)
library(ggplot2)

# connect to the db
con <- src_postgres(dbname="harlan", host="localhost", user="harlan")

# upload the Batting db
data(Batting)
dbWriteTable(con$con, "batting", Batting)

# grab Lee's data and upload it
global_dat <- content(GET("http://ldecola.net/projects/global/global.csv"),
                      type="text/csv")
dbWriteTable(con$con, "global", global_dat)

# create dplyr table objects
global <- tbl(con, "global")
batting <- tbl(con, "batting")

# windowing
batting %>% 
  group_by(yearID, lgID) %>%
  summarise(runs=sum(R)) %>%
  group_by(lgID) %>%
  arrange(yearID) %>%
  mutate(cumul_runs=cumsum(runs))

# local/remote
system.time(all_remote <- batting %>% 
  filter(teamID != 'BOS') %>%
  group_by(yearID) %>%
  mutate(cumul_runs=cumsum(R)) %>%
  select(yearID, R, cumul_runs) %>%
  collect())

system.time(half_local <- batting %>% 
  filter(teamID != 'BOS') %>%
  select(yearID, R) %>%
  collect() %>%
  group_by(yearID) %>%
  mutate(cumul_runs=cumsum(R)))


# function
tot_runs <- function(dat, year_range=c(2002,2012), ignore_redsox=FALSE) {
  ret <- (if (ignore_redsox) filter(dat, teamID != 'BOS') else dat) %>%
    filter(yearID >= year_range[[1]] & yearID <= year_range[[2]]) %>%
    summarize(tot_runs=sum(R)) %>% collect()
  ret[[1]][[1]]
}

# inspecting
qry <- batting %>% 
  group_by(yearID) %>%
  mutate(cumul_runs=cumsum(R)) %>%
  select(yearID, R, cumul_runs)

# collapse
qry <-filter(select(batting, yearID, R), yearID > 2010)
qry$query
collapse(qry)$query

# explain
qry <- batting %>% 
  filter(teamID != 'BOS') %>%
  group_by(yearID) %>%
  tally() %>%
  rename(year=yearID, runs=n) %>%
  inner_join(global, by='year') %>%
  select(year, runs, celsius) %>%
  arrange(year)

# sql literals
filter(batting, sql("\"playerID\" like 'finger%'")) %>% arrange(desc(yearID))

# views
dbGetQuery(con$con,
           "CREATE VIEW v_rollie AS SELECT * FROM batting WHERE \"playerID\" = 'fingero01'")
tbl(con, "v_rollie")
tbl(con, sql("select * from v_rollie"))

# indexes
dbGetQuery(con$con,
           "CREATE INDEX i_team ON batting (\"teamID\")")
explain(batting %>% filter(teamID == 'BOS'))

# temp tables
compute(filter(batting, playerID == 'fingero01'), name="tmp_rollie")
tbl(con, "tmp_rollie")
mydat <- data.frame(yearID=1973:1985, x=rnorm(n=length(1973:1985)))
copy_to(con, mydat, name="tmp_mydat")
left_join(tbl(con, "tmp_rollie"), tbl(con, "tmp_mydat"), by="yearID") %>% select(yearID, R, x)