R with Google Drive, and AWS

Intro

There is kind of a lot going on in this show-and-tell so I’m taking a moment up-front to give some why behind the what.

The first thing I’m providing here is a quick intro to my GitHub Pages Blog. The reason I’m providing this background is that I’m posting this tutorial to my GitHub blog. The content is written in an Rmarkdown, rendered to an html notebook, then pushed to the GitHub repository for my GitHub pages blog.

The second item on the agenda is the tidyverse::googledrive package. I don’t have a ton of interesting stuff to say about this package. I’m providing some code that works pretty well for me. These code samples come with the following caveat: so far everything about the tidyverse::googledrive package has worked for me without incident. I may not be super helpful in the troubleshooting arena as I haven’t really had to troubleshoot anything yet.

Google Drive is a pretty nice, low friction platform for cloud data storage/file sharing/collaboration. I’m guessing it’s the part of this vignette that people are most interested. I’m also guessing a lot of people are kind of like me, not really enamored with Google Drive but willing to give it a shot.

The third topic I’m touching on here is importing data from a MySQL database running on an Amazon cloud. I have a few other pretty involved blog posts on this topic so I’m really just planning to scratch the surface a little on this one. The main reason I’m doing this is that I think there are some limitations to the Google Drive workflow and I wanted to illustrate one pretty simple alternative.

Packages and Dependencies

library(googledrive)
library(stringr) # some string functions for cleaning EIDL data
library(dplyr)

Attaching package: 㤼㸱dplyr㤼㸲

The following objects are masked from 㤼㸱package:stats㤼㸲:

    filter, lag

The following objects are masked from 㤼㸱package:base㤼㸲:

    intersect, setdiff, setequal, union

library(DBI) # for some database interface methods
library(here) # manage directories

here() starts at C:/Users/aaron.mamula/Desktop/ResearchBlog/aaronmams.github.io

library(DT) # for pretty Rmarkdown tables

package 㤼㸱DT㤼㸲 was built under R version 4.0.3Registered S3 methods overwritten by 'htmltools':
  method               from         
  print.html           tools:rstudio
  print.shiny.tag      tools:rstudio
  print.shiny.tag.list tools:rstudio
Registered S3 method overwritten by 'htmlwidgets':
  method           from         
  print.htmlwidget tools:rstudio

GitHub Blog

I’ve written what I think is a pretty digestible walk-through on getting started using GitHub pages to host a research blog. This walk-through lives in the most unlikely of places, my research blog: Blogging on GitHub Pages with Jekyll.

I don’t want to (and I’m not really qualified to anyway) get hung-up on the particulars of webhosting with GitHub pages. What I want to emphasize at this stage is that, if you want to get a simple website/blog up and running, git/GitHub make it pretty easy. I did it in like 2 hours (possibly less):

1. forked an existing open-source GitHub repository that happened to be a Jekyll blog
2. changed the name of that repo to aaronmams.github.io

By naming the repository github-username.github.io GitHub knows to associate the content of that repo with the GitHub pages domain they automagically allocate to my GitHub account.

First point of order: you don’t have to do things the way I did them. You can build a website “from the ground up” if you want and put it up on GitHub pages by putting the source files in the GitHub repository called github-username.github.io. Emily Markowitz has a really cool personal website that she runs on GitHub pages.

Second point of order: the static site generator Jekyll plays no role in the day-to-day maintenance of my research blog. I manage new posts through R Studio & GitHub by writing new posts in Rmarkdown, rendering them to markdown (.md), and pushing them to the _posts/ sub-directory of the repository. The only Jekyll-centric element of my blog that really matters is that new posts must be named according to the convention: 2020-10-27-cool blog title. This is just how Jekyll demands that posts be named in order to recognize them as content to be rendered to the site.

Google Drive

Authentication/Connectivity

Probably the most important piece of the tidyverse::googledrive pipeline is the authentication process. Unfortunately, I don’t have much to say about this. Basically, the googledrive package pretty much did all the work for me.

Because it’s difficult to illustrate this authentication process “on-the-fly”, I did a quick 5 min. screen recording that’s available on YouTube, R-googledrive-authentication.

drive_auth()

Using an auto-discovered, cached token.
To suppress this message, modify your code or options to clearly consent to the use of a cached token.
See gargle's "Non-interactive auth" vignette for more details:
https://gargle.r-lib.org/articles/non-interactive-auth.html
The googledrive package is using a cached token for aaron.mamula@noaa.gov.

drive_user()

Auto-refreshing stale OAuth token.

Logged in as:
  *  displayName: Aaron Mamula - NOAA Federal
  * emailAddress: aaron.mamula@noaa.gov

The following resource on managing authentication tokens securely maybe also be a helpful troubleshooting resource if folks need it.

Find Resources

Using the drive_find() method we can explore Google Drive resources available in our session:

drive.stuff <- drive_find(n_max=10)
head(drive.stuff)

One potentially useful way to filter results from drive_find() is to search for resources of a particular type. Here, I’ll look for google sheet file types. Other arguments that can be provided include

• “csv”
• application/pdf

head(drive_find(type="spreadsheet",n_max=10)$name)

[1] "SWFSC WORKOUT"                                                                   
[2] "R Workshop Video Links"                                                          
[3] "R Workshop Detailed Schedule"                                                    
[4] "Registration: R Workshop for Economics and Human Dimensions Research (Responses)"
[5] "R for Social Scientists Pre-workshop Questionnaire (Responses)"                  
[6] "Workshop Info"                                                                   

The method drive_get() pulls down a lot of meta-type info on available resources.

example <- drive_get("R Workshop Video Links")
example

Note that the field drive_resource nests a rather large list in a tibble column.

names(example$drive_resource[[1]])

 [1] "kind"                         "id"                           "name"                        
 [4] "mimeType"                     "starred"                      "trashed"                     
 [7] "explicitlyTrashed"            "parents"                      "spaces"                      
[10] "version"                      "webViewLink"                  "iconLink"                    
[13] "hasThumbnail"                 "thumbnailLink"                "thumbnailVersion"            
[16] "viewedByMe"                   "viewedByMeTime"               "createdTime"                 
[19] "modifiedTime"                 "modifiedByMeTime"             "modifiedByMe"                
[22] "owners"                       "lastModifyingUser"            "shared"                      
[25] "ownedByMe"                    "capabilities"                 "viewersCanCopyContent"       
[28] "copyRequiresWriterPermission" "writersCanShare"              "permissions"                 
[31] "permissionIds"                "quotaBytesUsed"               "isAppAuthorized"             
[34] "exportLinks"                 

I don’t currently have a need for most of this info…but I can imagine that some people may want to look-up google drive resources by characteristics or properties. For example, I can see where it might be valuable to know who the last person to modify a file was:

example$drive_resource[[1]]$lastModifyingUser

$kind
[1] "drive#user"

$displayName
[1] "Aaron Mamula - NOAA Federal"

$me
[1] TRUE

$permissionId
[1] "11938931740274108721"

$emailAddress
[1] "aaron.mamula@noaa.gov"

File Downloads

The drive_find() and drive_get() methods are nice discovery tools. If we have actual source data that we would like to access from Google Drive, we need to bring it down into the workspace. For this we can use drive_download().

I have some data from the Small Business Administration on Covid relief from the Paycheck Protection Program and Economic Injury Disaster Loans Program. These data exist as a collection of .csv files currently housed in google drive. The following will download one of these data files into the data/ subdirectory of my current project.

# download the EIDL data from google drive into a local "data" sub-directory...note that I'm shipping these data off
# to a different project as I don't want to house source data inside my blog.

drive_download("Cara-R-Internship/CovidData/EIDLLoans1.csv",path="C:/Users/aaron.mamula/Desktop/R-Projects/PPP-EIDL-Analysis/data/EIDLLoans1.csv",
               overwrite=T)

File downloaded:
  * EIDLLoans1.csv
Saved locally as:
  * C:/Users/aaron.mamula/Desktop/R-Projects/PPP-EIDL-Analysis/data/EIDLLoans1.csv

Something that I found interesting is that the drive_download() method doesn’t actually require the specificity that I provided above. The method will find my EIDLLoans1.csv file whether I provide it nesting folder names or not:

drive_download("PPP Data 150k plus 080820.csv",path="C:/Users/aaron.mamula/Desktop/R-Projects/PPP-EIDL-Analysis/data/ppp.csv",
               overwrite=T)

File downloaded:
  * PPP Data 150k plus 080820.csv
Saved locally as:
  * C:/Users/aaron.mamula/Desktop/R-Projects/PPP-EIDL-Analysis/data/ppp.csv

The drive_download() function made a local copy of the .csv file in my data/ sub-directory. To access these data in the R Workspace we just use the familiar read.csv() method:

df <- read.csv("C:/Users/aaron.mamula/Desktop/R-Projects/PPP-EIDL-Analysis/data/ppp.csv")
str(df)

'data.frame':   662515 obs. of  16 variables:
 $ LoanRange    : chr  "d $350,000-1 million" "d $350,000-1 million" "d $350,000-1 million" "d $350,000-1 million" ...
 $ BusinessName : chr  "AERO BOX LLC" "BOYER CHILDREN'S CLINIC" "KIRTLEY CONSTRUCTION INC" "PLEASANT PLACES, INC." ...
 $ Address      : chr  "N/A" "1850 BOYER AVE E" "1661 MARTIN RANCH RD" "7684 Southrail Road" ...
 $ City         : chr  "N/A" "SEATTLE" "SAN BERNARDINO" "North Charleston" ...
 $ State        : chr  "" "" "" "" ...
 $ Zip          : int  NA 98112 92407 29420 29150 NA NA 32259 NA NA ...
 $ NAICSCode    : int  484210 NA 236115 561730 325510 424210 721110 813110 326199 423140 ...
 $ BusinessType : chr  "" "Non-Profit Organization" "Corporation" "Sole Proprietorship" ...
 $ RaceEthnicity: chr  "Unanswered" "Unanswered" "Unanswered" "Unanswered" ...
 $ Gender       : chr  "Unanswered" "Unanswered" "Unanswered" "Male Owned" ...
 $ Veteran      : chr  "Unanswered" "Unanswered" "Unanswered" "Non-Veteran" ...
 $ NonProfit    : chr  "" "Y" "" "" ...
 $ JobsReported : int  NA 75 21 73 62 NA NA 89 NA NA ...
 $ DateApproved : chr  "05/03/2020" "05/03/2020" "05/03/2020" "05/03/2020" ...
 $ Lender       : chr  "The Huntington National Bank" "Bank of America, National Association" "Bank of America, National Association" "Synovus Bank" ...
 $ CD           : chr  "" "WA-07" "CA-31" "SC-01" ...

#clean city names & clean loan range field
df <- df %>% mutate(city.name=toupper(trimws(City)),
                    state = toupper(trimws(State)),
                    LoanRange = trimws(substr(LoanRange,2,nchar(LoanRange)))) 

# find Santa Cruz County Businesses
ppp.sc <- df %>% filter(Zip %in% c(95001,95010,05018,95017,95019,95041,95060,
                                   95062,95065,96064,95066,95073,95003,95005,95007))

# Organize by JobsReported
ppp.sc <- ppp.sc %>% arrange(-JobsReported) 
ppp.sc %>% select(LoanRange,BusinessName,City,JobsReported,DateApproved)

What if one would like to import data from a .csv in google drive WITHOUT downloading a local version of the .csv?

Unfortunately, I don’t have a great answer for that. Here are a couple leads for you tho:

1. There is a googlesheets package that is part of the tidyverse. So if you’re ok having the data in a google sheet rather than a .csv this could be a good option.

2. There is probably a way to use read.csv() directly on the url. I haven’t had any success in this arena yet but it could just be that I’m not understanding what url string I should be using. This Stack Overflow question claims to have cracked the case.

AWS

This might seem like kind of a tangent but I think it’s related enough to the previous googledrive section to warrant inclusion. Here’s why: Google Drive is a nice, easy-to-use cloud platform for data storage. AWS is another cloud-based alternative that requires a little more set-up effort but results (I think) in a cleaner workflow.

I have a series of blog posts where I tried to do a reasonably thorough reporting on a self-guided AWS expedition I took a while back:

1. Connect to Amazon RDS w/MySQL Workbench
2. AWS Custom VPCs
3. Using a Virtual EC2 Server to Connect to RDS
4. Using Python Script to Connct to AWS RDS

DB Backstory

I got a bunch of needle logs from the City of Santa Cruz. These are reports that city workers fill out whenever they pick up a used needle from a public space. They write down an approximate address/description and how many needles were encountered. I received these data as hard copies and jammed them into .csv files. Then I looked up the addresses as best I could on google maps and created a look-up table with lat/long coordinates for each location.

The database currently has two tables:

1. needle_events which contains the individual needle encounters
2. location_geo which contains the lat/long coordinates for each location description.

DB Set-up

This a pretty detailed log of my first experience setting up a relational database on AWS. In the article I also link to a number of other tutorials that can be used to set up a simple relational database on Amazon’s RDS service.

This step is pretty much just toggling through and selecting from a menu of options provided to you by Amazon.

DB Connection

From here it’s totally feasible to connect to your database through R. I like to preface my R connection with a connection via some other database client. Since the DB I’m using here is a MySQL database, I used MySQL Workbench (a free MySQL client) to establish my initial connection. This has the added benefit of giving me a nice pointy-clicky interface with which to write new observations to database tables by pushing .csv files up.

The R connection is really simple (mostly because I don’t have much security fencing on this database): just need a database endpoint and some access credentials:

cn <- dbConnect(drv      = RMySQL::MySQL(), 
                username = "admin", 
                password = "nmfssocialscience", 
                host     = "mams-teaching-public.c65i4tmttvql.us-west-1.rds.amazonaws.com", 
                port     = 3306, 
                dbname   = "needle_waste")

Analysis

Once the database connection is established, it is pretty straightforward to import data and start doing stuff with it.

The dbGetQuery() method from the DBI package allows SQL syntax to be passed to the database engine and returns results to the R workspace. Here, I’m joining two tables from the database

df.joined <- dbGetQuery(cn, "SELECT * FROM needle_events inner join locations_geo on needle_events.LOCATION_RECORDED=locations_geo.LOCATION_RECORDED")
head(df.joined)

Misc. Takeaways

1. It seems that it is possible to post html and html notebook files to a Jekyll Blog. This is kinda cool, I have been rendering stuff to .md files before posting and it’s cool to know I don’t have to.

2. The googledrive package has some nice wrappers for working with Google Drive. I’m not seeing a lot of utility here that’s really game-changing for my workflow…but maybe if I work with it a little more I’ll warm-up to it.

3. I really like rendering tables using DT::datatable(dataframe) inside my Rmarkdowns. I just like to way it makes my tables look. I wasn’t able to do this with my GitHub blog. Something about this table formatting was generating html tags that couldn’t be rendered.

4. There’s probably a lot of red-tape type stuff involved in using AWS as federal employees. As evidenced by Amazon’s exiting public sector partnerships, it’s definitely possible to use Amazon’s cloud for gob’ment data. However, I don’t know any of the particulars about when/where/how it might be appropriate for federal employees to use AWS to warehouse data. I do know that

• AWS-RDS is not that hard to use once you get the hang of it, and
• it’s pretty smooth to pipe data into R from an Amazon cloud.

5. If you’re curious, the big roadblock for me in terms of actually using Jekyll to manage blog content was a Ruby version conflict between what Jekyll required and what my Mac OS had installed. For some reason this ate up like 2 weeks of my life and never got resolved. I’ve since updated to Mac Catalina (which is purported to ship with Ruby 2.6.3) so I’m guessing the issue would probably resolve itself if I cared enough to try and run Jekyll again….but since I don’t have to, I’m probably not gonna.

Unsolicited Commentary

I think a lot of us were moved (probably to varying degrees) by the Richard McElreath video that Roy recently distributed. The portion of the talk that resonated most with me with was the emphasis placed on proper data management/data curation practices. Basically, it’s hard for research to be reproducible if the data aren’t shareable. And it’s hard for data to be shareable if they exist as a potpourri of flat files on somebody’s hard-drive.

I’d like to think that what I presented here could kind of be marketed as a “starter kit” for working with data in “the cloud”…which is kind of an important skill for those interested in reproducible research.

Consider the following popular workflow for managing research projects with multiple contributors:

Github fork --> git clone --> R Studio code --> git commit --> git push/pull.

This is good for collaborative analysis but GitHub isn’t a great place for source data. So if the source data are being periodically updated, you need a data distribution platform that’s accessible to all project collaborators.

Google Drive and Amazon Web Services are two relatively low-cost cloud data storage platforms. This makes them nice options for practicing collaborative data management.