6 Data Visualizations

6.1 Simple plot

Practice pushing the calculations to the database

Load the connections, dplyr, dbplyr, and config libraries

library(connections)
library(dplyr)
library(dbplyr)
library(config)

Use connection_open() to open a Database connection

con <- connection_open(
  RPostgres::Postgres(),
  host =  "localhost",
  user = get("user", config = "dev"),
  password = get("pwd", config = "dev"),
  port = 5432,
  dbname = "postgres",
  bigint = "integer"
)

Use tbl() to create a pointer to the v_orders table

orders <- tbl(con, in_schema("retail", "v_orders"))

Use collect() bring back the aggregated results into a “pass-through” variable called by_year
```
by_year <- orders %>%
  count(date_year) %>%
  collect()
```

Preview the by_year variable

by_year

## # A tibble: 3 x 2
##   date_year      n
##       <int>  <int>
## 1      2017 364317
## 2      2016 366796
## 3      2018 268934

Load the ggplot2 library
```
library(ggplot2)
```

Plot results using ggplot2

ggplot(by_year) +
  geom_col(aes(date_year, n))

Using the code in this section, create a single piped code set which also creates the plot

orders %>%
  count(date_year) %>%
  collect() %>%
  ggplot() +            # < Don't forget to switch to `+`
  geom_col(aes(date_year, n))

6.2 Plot in one code segment

Practice going from dplyr to ggplot2 without using pass-through variable, great for EDA

Summarize the order totals in a new variable called sales

orders %>%
  summarise(sales = sum(order_total))

## Warning: Missing values are always removed in SQL.
## Use `SUM(x, na.rm = TRUE)` to silence this warning
## This warning is displayed only once per session.

## # Source:   lazy query [?? x 1]
## # Database: postgres [rstudio_admin@localhost:5432/postgres]
##      sales
##      <dbl>
## 1 38014000

Summarize the order totals grouped by date_year in a new variable called sales

orders %>%
  group_by(date_year) %>%
  summarise(sales = sum(order_total))

## # Source:   lazy query [?? x 2]
## # Database: postgres [rstudio_admin@localhost:5432/postgres]
##   date_year    sales
##       <int>    <dbl>
## 1      2017 13911500
## 2      2016 13998800
## 3      2018 10103600

Summarize the order totals grouped by date_year in a new variable called sales and plot the results

orders %>%
  group_by(date_year) %>%
  summarise(sales = sum(order_total)) %>%
  ggplot() +
  geom_col(aes(date_year, sales))

Switch the calculation to reflect the average of the order sale total

orders %>%
  group_by(date_year) %>%
  summarise(sales = mean(order_total)) %>%
  ggplot() +
  geom_col(aes(date_year, sales))

## Warning: Missing values are always removed in SQL.
## Use `mean(x, na.rm = TRUE)` to silence this warning
## This warning is displayed only once per session.

6.3 Create a histogram

Use the dbplot package to easily create a histogram

Load the dbplot package
```
library(dbplot)
```
Use the dbplot_histogram() to build the histogram
```
orders %>%
  dbplot_histogram(order_total)
```

Adjust the binwidth to 10

orders %>%
  dbplot_histogram(order_total, binwidth = 10)

6.4 Raster plot

Use dbplot’s raster graph

Use a dbplot_raster() to visualize order_qty versus order_total
```
orders %>%
  dbplot_raster(order_qty, order_total)
```

Change the plot’s resolution to 10

orders %>%
  dbplot_raster(order_qty, order_total, resolution = 10)

6.5 Using the `compute` functions

Use the db_compute_raster() function to get the underlying results that feed the plot

locations <- orders %>%
  db_compute_raster2(customer_lon, customer_lat, resolution = 10)

Preview the locations variable

locations

## # A tibble: 58 x 5
##    customer_lon customer_lat `n()` customer_lon_2 customer_lat_2
##           <dbl>        <dbl> <dbl>          <dbl>          <dbl>
##  1        -122.         37.8 10819          -122.           37.8
##  2        -122.         37.7 22034          -122.           37.8
##  3        -122.         37.8 10906          -122.           37.8
##  4        -122.         37.8 11574          -122.           37.8
##  5        -122.         37.8 33725          -122.           37.8
##  6        -122.         37.8 20083          -122.           37.8
##  7        -122.         37.7 11475          -122.           37.7
##  8        -122.         37.7 23571          -122.           37.7
##  9        -122.         37.8 11416          -122.           37.8
## 10        -122.         37.8 11089          -122.           37.8
## # … with 48 more rows

Load the leaflet library
```
library(leaflet)
```
Pipe location into the leaflet() function, and then pipe that into the addTiles() function
```
locations %>%
  leaflet() %>% 
    addTiles() 
```
Add the addRectangles() function using the longitude and latitude variables

locations %>%
  leaflet() %>% 
    addTiles() %>%
    addRectangles(
      ~customer_lon, 
      ~customer_lat, 
      ~customer_lon_2,
      ~customer_lat_2
    )

Add the fillOpacity argument to the addRectangles() step, use n() as the value for it

locations %>%
  leaflet() %>% 
    addTiles() %>%
    addRectangles(
      ~customer_lon, 
      ~customer_lat, 
      ~customer_lon_2,
      ~customer_lat_2,
      fillOpacity = ~`n()`
    )

Modify fillOpacity to be calculated as a percentage against the maximum number of orders

locations %>%
  leaflet() %>% 
    addTiles() %>%
    addRectangles(
      ~customer_lon, 
      ~customer_lat, 
      ~customer_lon_2,
      ~customer_lat_2,
      fillOpacity = ~(`n()` / max(`n()`))
    )

Add the popup argument with the following instruction as its value: ~paste0("<p>No of orders: ",n(),"</p>")

locations %>%
  leaflet() %>% 
    addTiles() %>%
    addRectangles(
      ~customer_lon, 
      ~customer_lat, 
      ~customer_lon_2,
      ~customer_lat_2,
      fillOpacity = ~(`n()` / max(`n()`)),
      popup = ~paste0("<p>No of orders: ",  `n()`,"</p>")
    )

Disconnect from the database using connection_close
```
connection_close(con)
```