Upgrade to Pro — share decks privately, control downloads, hide ads and more …

googlesheets talk at UseR2015

googlesheets talk at UseR2015

0a4f62e90c976eeb44d33add75cca5af?s=128

Jennifer (Jenny) Bryan

June 02, 2015
Tweet

More Decks by Jennifer (Jenny) Bryan

Other Decks in Programming

Transcript

  1. googlesheets

  2. useR! 2015 Dr. Jennifer (Jenny) Bryan Dept. of Statistics &

    Michael Smith Laboratories, UBC jenny@stat.ubc.ca @JennyBryan http://stat545-ubc.github.io @STAT545 http://www.stat.ubc.ca/~jenny/ @jennybc googlesheets Google Spreadsheets R API
  3. + googlesheets = R wrapper around the Sheets & Drive

    APIs +
  4. None
  5. Why would a UseR use a Google Sheet? collaboration -

    link, don’t attach! built-in functions to import web data - importxml(), importhtml(), importfeed() magic templates - TAGS, Amazon Price Tracker web services - ifttt.com
  6. Why important to have an R interface to Google Sheets?

    Just. Because. Lightweight data store for Shiny
  7. The participant who loses the most weight (as measured by

    the final weight as a percentage of his starting weight) shall be declared the winner. Jim Craig
  8. Craig won :(

  9. “Examples of permitted services include, but are not limited to,

    cooking, gardening, handiwork, caddying, office work, serving food and drinks or running errands.”
  10. ~1800 consecutive days of weight data

  11. iOS Android enter data from a phone enter data w/o

    WiFi
  12. library(googlesheets) ... jc_ss <- gs_title("jim-craig-weight-loss") jc_dat <- gs_read(jc_ss) jc_dat <-

    jc_dat %>% mutate(date = as.Date(date, format = "%m/%d/%Y")) %>% gather("who", "weight", Jim, Craig) p <- ggplot(jc_dat, aes(x = date, y = weight, color = who)) + geom_point(alpha = 0.35) + geom_smooth(se = FALSE, lwd = 2) + scale_y_continuous(labels = lbs_formatter) print(direct.label(p))
  13. None
  14. I wanted live stats!

  15. vn_ss <- gs_title("2015-05-23_seaRM-at-vanNH") #> Sheet successfully identifed: "2015-05-23_seaRM-at-vanNH" game_play <-

    gs_read(vn_ss, ws = 10, range = cell_limits(c(2, NA), c(1, 2))) #> Accessing worksheet titled "10" head(game_play) #> Source: local data frame [6 x 2] #> #> Offense Defense #> 1 NA 42P #> 2 21pu NA #> 3 NA 29pu #> 4 NA 31 #> 5 NA 29 #> 6 NA 31
  16. point_info <- vn_ss %>% gs_read_cellfeed(ws = 10, range = "D1:D4")

    %>% gs_simplify_cellfeed(col_names = FALSE) #> Accessing worksheet titled "10" point_info #> D1 D2 D3 D4 #> "Seattle Rainmakers" "1" "3:12:00" "1:49:00"
  17. install.packages(“googlesheets”)   library(devtools)   install_github(“jennybc/googlesheets”) not there yet but it’s

    in process
  18. gs_*

  19. > gs_ls("Gap") Source: local data frame [4 x 10] sheet_title

    author perm version updated 1 Gapminder 2007 Can Write gspreadr rw new 2015-04-15 19:33:55 2 Gapminder gspreadr rw new 2015-03-23 20:59:10 3 Gapminder_old gspreadr rw new 2015-03-23 20:23:06 4 Gapminder by Continent joannaazhao rw new 2015-01-20 22:23:56 Variables not shown: sheet_key (chr), ws_feed (chr), alternate (chr), self (chr), alt_key (chr)
  20. ss  <-­‐  gs_title("Gapminder")   ss  <-­‐  gs_key("1BzfL0...")   ss  <-­‐

     gs_url("https://docs.google.com/spreadsheets/d/1BzfL0.../" ) register a Sheet
  21. > ss <- gs_title("Gapminder") > ss Spreadsheet title: Gapminder Date

    of googlesheets registration: 2015-06-02 04:32:55 GMT Date of last spreadsheet update: 2015-03-23 20:34:08 GMT visibility: private permissions: rw version: new Contains 5 worksheets: (Title): (Nominal worksheet extent as rows x columns) Africa: 625 x 6 Americas: 301 x 6 Asia: 397 x 6 Europe: 361 x 6 Oceania: 25 x 6 Key: 1HT5B8SgkKqHdqHJmn5xiuaC04Ngb7dG9Tv94004vezA
  22. gs_gap()   gs_gap_key()   gs_gap_url() Gapminder Sheet available for examples,

    testing, practice
  23. ss  <-­‐  gs_gap()   ss  <-­‐  gs_gap_key()  %>%  gs_key()  

    ss  <-­‐  gs_gap_url()  %>%  gs_url()   ss  <-­‐  gs_gap()   ss  <-­‐  gs_key(gs_gap_key())   ss  <-­‐  gs_url(gs_gap_url())
  24. gs_upload() .xls .xlsx .xlt .xltx .xltm .xlsm .csv .tsv .txt

    .tab .ods
  25. gs_download() .csv .pdf .xlsx

  26. gs_new()           gs_copy()   gs_upload()

  27. gs_delete()   gs_grepdel()   gs_vecdel()

  28. gs_ws_*

  29. gs_ws_ls()   gs_ws_new()   gs_ws_rename()   gs_ws_delete()

  30. gs_read()

  31. gs_read(...,  ws  =  3,  ...)   gs_read(...,  ws  =  “Europe”,

     ...)   gs_read(...,  range  =  "D12:F15")   gs_read(...,  range  =  "R1C12:R6C15")   gs_read(...,  range  =  cell_limits(c(1,  6),  c(1,  15)))   gs_read(...,  range  =  cell_limits(c(3,  NA),  c(NA,  10)))   gs_read(...,  range  =  cell_rows(1:100))   gs_read(...,  range  =  cell_cols(3:8))   gs_read(...,  range  =  cell_cols("B:MZ"))   gs_read(...,  range  =  anchored("B4",  dim  =  c(2,  10)))
  32. gs_new()   gs_ws_new()   gs_edit_cells()   gs_add_row()

  33. gs_auth()   gs_user()

  34. Technical bits of interest flexible auth flow: - interactive -

    unattended “batch” use - Shiny use we are actually testing - against the Sheets and Drive APIs - including operations that require authentication - on Travis-CI
  35. https://github.com/hrbrmstr/nifffty https://ifttt.com

  36. =IMPORTXML(URL, XPATH) https://jennybc.shinyapps.io/03_craigslist-lost-and-found

  37. https://github.com/jennybc/googlesheets/tree/master/inst/shiny-examples Sample apps

  38. thanks Joanna Zhao! also Karthik Ram, Hadley Wickham, Noam Ross