PSA Training Series github.com/precision-sustainable-ag

Database Querying with R — Brian W. Davis 2020-01-07

1. What is a DB? (video)
2. Getting started with R (video)
3. How to use a local DB (video)
4. How to use the PSA DB (video)

---

002 - Getting started with R

1 Getting started with R

First up, is R the only way to interact with databases? No, but it’s a scripting language and development environment specifically designed for ingesting, analyzing, and communicating data. You can achieve all the tasks in this series using Python, SAS, JavaScript, etc.

1.1 What are these documents?

1.1.1 Non-interactive HTML

In this series, there are two files for each step in the tutorial. One is called ###_Title.nb.html, and it’s a plain HTML file you can open in your browser. In that file, you will see plain text, as well as boxes of code. The grey boxes have code you can copy-paste into an R script, or line-by-line at the R console. The white boxes below show the output you should see after running each chunk. They look like this:

mean(rnorm(1000))

[1] -0.001956077

1.1.2 Interactive R Notebooks

There are also files called ###_Title.Rmd. These are special R scripts you can open in RStudio (see below). The text you’re reading now is in there, but the code chunks will look like this:

Within RStudio:

‍```{r}                     
mean(rnorm(1000))
‍```

If you click the “Run Current Chunk” button (in RStudio, not in the HTML document), that section of code will execute, and the R output will appear immediately below it in the document. You can play around with the code and see how the output is affected. Each chunk shares an environment with the other chunks in the document, so if you mess something up and want to run all the previous ones to reset, click the “Run All Chunks Above” button .

2 Download R

A mirror of the R core application is available at https://cran.rstudio.com/, and for Mac and Windows, setup is a breeze. Linux has a few challenges, usually revolving around installing packages from their source code instead of distributed binaries, so if you’re a Linux user, you probably have more expertise than I do on the topic.

2.1 Optionally RStudio

I strongly recommend using RStudio, available from https://rstudio.com/products/rstudio/download/#download. It’s an integrated development environment, or IDE, so you can see your code and output together. It also has lots of features for productivity, like autocomplete and syntax highlighting.

3 Installing packages

Base R is useful, but for this project we are going to take advantage of a few database-specific packages. Pay attention after you run each of these lines! When you run install.packages, lots of informative messages and download progress bars are printed, but there may be errors and warnings as well.

3.1 Download DBI

This package provides a backend for database drivers to communicate with R in a uniform way.

install.packages("DBI")

3.2 Download RPostgres

This package contains the driver to connect to PostgreSQL databases, like the PSA On-Farm DB.

WARNING: Due to a bug in the newest release of this package (1.2.0, as of late December 2019), connections to the database will fail. Instead, you need versions 1.1.3 or earlier. I’ve tested connections on Windows, MacOS, and Linux with 1.1.1, so that’s the instruction I’m giving here. I hope this will be resolved soon, and you will be able use the regular installation procedure to get the latest package.

# install.packages("RPostgres")

install.packages("devtools")
devtools::install_version(
  "RPostgres", 
  version = "1.1.1", 
  repos = "http://cran.us.r-project.org"
  )

3.3 Download RSQLite

This package contains both the driver and the database system itself for SQLite3. It’s a lightweight DB that you can run on your computer for testing and learning (in the next tutorial).

install.packages("RSQLite")

3.4 Optionally download dplyr and dbplyr

While not strictly required, I will be using the {dbplyr} interface to translate R code to the SQL backend. The syntax is much more user-friendly and intuitive, and it’s useful for your local analyses as well. There are many tutorials online for learning {dplyr}, so this will only be a crash course in what’s necessary.

A good resource for databases and R in general is https://db.rstudio.com/, which has sections for different database systems, how SQL translation works, etc.

install.packages("dplyr")
install.packages("dbplyr")

3.4.1 Even more optionally

There are some packages I use that you do not need for the basic outline of the tutorial. They’re mostly for demonstrating plots, or when I want to show you an illustrative code chunk. However, these are not required to just query the database. Of course, I obviously find them useful in other analyses beyond this tutorial. If you do want to run every single line of code that I do as you follow along though, here’s what you’ll see used:

3.4.1.1 Visualization:

install.packages("ggplot2")

3.4.1.2 Functional loops and string/datetime manipulation

install.packages("purrr")
install.packages("stringr")
install.packages("lubridate")

3.4.1.3 Mapping

install.packages("sf")
install.packages("rnaturalearthdata")
install.packages("leaflet")

4 What on earth is that funny looking %>%

In a lot of new R code, especially so-called {tidyverse}-flavored code, you’ll see something called a pipe operator, %>%. It’s for function composition, and you can think of it like reading your code out loud, and saying “then” every time you see it.

library(dplyr, warn.conflicts = FALSE)

rnorm(5) %>%   # generate 5 random numbers
  sort() %>%    # THEN sort those
  abs() %>%     # THEN take the absolute value of those
  sqrt()        # THEN take the square-root of those

[1] 0.5775481 0.9663637 1.2255812 1.2698123 1.5540718

This is identical to sqrt(abs(sort(rnorm(5)))). Whether you prefer the nested-parentheses style or the pipe-then style is up to you, just be aware that I’ll be using the pipes in some of these tutorials.

The pipe is especially useful for the table verbs of {dplyr}, which usually take a dataframe as their first argument, and return a dataframe as output. This way they can be chained together. Here’s an example of that:

iris %>% 
  filter(Species != "setosa") %>% 
  select(Petal.Length, Petal.Width, Species) %>% 
  mutate(Petal.Area = Petal.Length * Petal.Width) %>% 
  group_by(Species) %>% 
  summarize(m = mean(Petal.Area))

# A tibble: 2 x 2
  Species        m
  <fct>      <dbl>
1 versicolor  5.72
2 virginica  11.3 

This code says:

• Use the built-in iris dataset
• Keep only the rows where iris$Species is not “setosa”
• Keep only the columns for petal length/width and species
• Make a new column (Petal.Area) and add it to the right of the dataframe, multiplying petal length and width
• Separate the rows into groups based on what’s in the species column
• Summarize all the rows in each group to a single row, with an observation m that’s the mean of our new Petal.Area

If you’re following along with this code, I recommend running each chunk of lines up to the %>% to see how each step works, like this:

iris

iris %>% 
  filter(Species != "setosa") 

iris %>% 
  filter(Species != "setosa") %>% 
  select(Petal.Length, Petal.Width, Species) 

iris %>% 
  filter(Species != "setosa") %>% 
  select(Petal.Length, Petal.Width, Species) %>% 
  mutate(Petal.Area = Petal.Length * Petal.Width) 

iris %>% 
  filter(Species != "setosa") %>% 
  select(Petal.Length, Petal.Width, Species) %>% 
  mutate(Petal.Area = Petal.Length * Petal.Width) %>% 
  group_by(Species) 

iris %>% 
  filter(Species != "setosa") %>% 
  select(Petal.Length, Petal.Width, Species) %>% 
  mutate(Petal.Area = Petal.Length * Petal.Width) %>% 
  group_by(Species) %>% 
  summarize(m = mean(Petal.Area))

---

precisionsustainableag.org

---

Icons: fontawesome.com, BY 4.0 | This work, BY-SA 4.0

---