Data Wrangling II

Song of the Day

Computer Love -Kraftwerk

Main Ideas

To answer questions with data, we often need to use related data from many different datasets.
We can combine data from different sources using a well-chosen join function.

Coming Up

Homework #01 due Thursday 2-04 at 11:59 PM
Homework #02 assigned Thursday 2-04, due Thursday 2-11 at 11:59 PM
Lab #03 tomorrow
Complete Lab Team Formation Survey by 2-09 11:59 PM
Accept invitation to the GitHub organization (3 pending invitations)

Lecture Notes and Exercises

library(tidyverse)

Instead of working with a single dataset, usually you will have to work with many different related datasets. To answer research questions using related datasets, we need to develop tools to join datasets together.

There are many possible types of joins. All have the format something_join(x, y).

mutating joins

inner_join(): all rows from x where there are matching values in y
left_join(): all rows from x
right_join(): all rows from y
full_join(): all rows in x or y

filtering joins

semi_join(): returns rows from x with a match in y
anti_join(): returns rows from x without a match in y

x <- tibble(value = c(1, 2, 3),
            xcol = c("x1", "x2", "x3"))

y <- tibble(value = c(1, 2, 4),
            ycol = c("y1", "y2", "y4"))

x

## # A tibble: 3 x 2
##   value xcol 
##   <dbl> <chr>
## 1     1 x1   
## 2     2 x2   
## 3     3 x3

## # A tibble: 3 x 2
##   value ycol 
##   <dbl> <chr>
## 1     1 y1   
## 2     2 y2   
## 3     4 y4

We will demonstrate each of the joins on these small, toy datasets. Check out the slides for an animated version of these joins.

inner_join(x, y)

## # A tibble: 2 x 3
##   value xcol  ycol 
##   <dbl> <chr> <chr>
## 1     1 x1    y1   
## 2     2 x2    y2

left_join(x, y)

## # A tibble: 3 x 3
##   value xcol  ycol 
##   <dbl> <chr> <chr>
## 1     1 x1    y1   
## 2     2 x2    y2   
## 3     3 x3    <NA>

right_join(x, y)

## # A tibble: 3 x 3
##   value xcol  ycol 
##   <dbl> <chr> <chr>
## 1     1 x1    y1   
## 2     2 x2    y2   
## 3     4 <NA>  y4

full_join(x, y)

## # A tibble: 4 x 3
##   value xcol  ycol 
##   <dbl> <chr> <chr>
## 1     1 x1    y1   
## 2     2 x2    y2   
## 3     3 x3    <NA> 
## 4     4 <NA>  y4

semi_join(x, y)

## # A tibble: 2 x 2
##   value xcol 
##   <dbl> <chr>
## 1     1 x1   
## 2     2 x2

anti_join(x, y)

## # A tibble: 1 x 2
##   value xcol 
##   <dbl> <chr>
## 1     3 x3

How do the join functions above know to join x and y by value? Examine the names to find out.

names(x)

## [1] "value" "xcol"

names(y)

## [1] "value" "ycol"

The animations provided in today’s slides are very helpful for understanding the join functions.

NYC flights

We will again work with data from the nycflights13 package.

library(nycflights13)

Examine the documentation for the datasets airports, flights, and planes.

Question: How are these datasets related? Suppose you wanted to make a map of the route of every flight. What variables would you need from which datasets?

To make a map of the route of every flight we need the origin and dest from flights and each airport’s lat and lon from airports.

Let’s join flights to airports. These datasets have no variables in common so we will have to specify the variable to join by using by. Check out the documentation for more information.

flights %>% 
  left_join(airports, by = c("dest" = "faa"))

## # A tibble: 336,776 x 26
##     year month   day dep_time sched_dep_time dep_delay arr_time sched_arr_time
##    <int> <int> <int>    <int>          <int>     <dbl>    <int>          <int>
##  1  2013     1     1      517            515         2      830            819
##  2  2013     1     1      533            529         4      850            830
##  3  2013     1     1      542            540         2      923            850
##  4  2013     1     1      544            545        -1     1004           1022
##  5  2013     1     1      554            600        -6      812            837
##  6  2013     1     1      554            558        -4      740            728
##  7  2013     1     1      555            600        -5      913            854
##  8  2013     1     1      557            600        -3      709            723
##  9  2013     1     1      557            600        -3      838            846
## 10  2013     1     1      558            600        -2      753            745
## # … with 336,766 more rows, and 18 more variables: arr_delay <dbl>,
## #   carrier <chr>, flight <int>, tailnum <chr>, origin <chr>, dest <chr>,
## #   air_time <dbl>, distance <dbl>, hour <dbl>, minute <dbl>, time_hour <dttm>,
## #   name <chr>, lat <dbl>, lon <dbl>, alt <dbl>, tz <dbl>, dst <chr>,
## #   tzone <chr>

Practice

Create a new dataset dest_delays with the median arrival delay for each destination. Note this question does not require you to use joins.

dest_delays <- flights %>%
  group_by(dest) %>%
  summarise(delay = median(arr_delay, na.rm=TRUE))

dest_delays

## # A tibble: 105 x 2
##    dest  delay
##    <chr> <dbl>
##  1 ABQ    -5.5
##  2 ACK    -3  
##  3 ALB    -4  
##  4 ANC     1.5
##  5 ATL    -1  
##  6 AUS    -5  
##  7 AVL    -1  
##  8 BDL   -10  
##  9 BGR    -9  
## 10 BHM    -2  
## # … with 95 more rows

Create a new dataset by joining dest_delays and airports. Only include observations that have both delay and airport information. Note dest_delays and flights have no variables in common so you will need to specify the variables to join using by as in the example above.

dest_delays %>%
  inner_join(airports, by = c("dest" = "faa"))

Question: Are all of the variables in dest_delays included in the new dataset you created by joining dest_delays and airports? Use an appropriate join function to investigate this issue and determine what is going on here.

There are 105 airports in the dest_delays dataset, but when we inner_join to airports there are only 101 airports. There are four airports in the dest_delays data that are not matched in airports. Something weird is going on.

Let’s use an anti_join to help diagnose this issue. Recall anti_join returns all rows from x without a match in y, so it will return all rows in dest_delays that don’t have a match in airports.

dest_delays %>%
  anti_join(airports, by = c("dest" = "faa"))

## # A tibble: 4 x 2
##   dest  delay
##   <chr> <dbl>
## 1 BQN      -1
## 2 PSE       0
## 3 SJU      -6
## 4 STT      -9

A bit of googling reveals that these are airports in Puerto Rico (BQN, PSE, SJU) and the U.S. Virgin Islands (STT).

Is there a relationship between the age of a plane and its delays? The plane tail number is given in the tailnum variable in the flights dataset. The year the plane was manufactured is given in the year variable in the planes dataset.

Step #1: Start by finding the average arrival delay for each plane and store the resulting dataset in plane_delays.

plane_delays <- flights %>%
  group_by(tailnum) %>% 
  summarize(delay = mean(arr_delay, na.rm = TRUE))

Step #2: Join plane_delays to the planes data using an appropriate join and then use mutate to create an age variable. Note this data is from 2013.

plane_delays %>%
  left_join(planes, by = "tailnum") %>%
  mutate(age = 2013 - year)

## # A tibble: 4,044 x 11
##    tailnum  delay  year type  manufacturer model engines seats speed engine
##    <chr>    <dbl> <int> <chr> <chr>        <chr>   <int> <int> <int> <chr> 
##  1 D942DN  31.5      NA <NA>  <NA>         <NA>       NA    NA    NA <NA>  
##  2 N0EGMQ   9.98     NA <NA>  <NA>         <NA>       NA    NA    NA <NA>  
##  3 N10156  12.7    2004 Fixe… EMBRAER      EMB-…       2    55    NA Turbo…
##  4 N102UW   2.94   1998 Fixe… AIRBUS INDU… A320…       2   182    NA Turbo…
##  5 N103US  -6.93   1999 Fixe… AIRBUS INDU… A320…       2   182    NA Turbo…
##  6 N104UW   1.80   1999 Fixe… AIRBUS INDU… A320…       2   182    NA Turbo…
##  7 N10575  20.7    2002 Fixe… EMBRAER      EMB-…       2    55    NA Turbo…
##  8 N105UW  -0.267  1999 Fixe… AIRBUS INDU… A320…       2   182    NA Turbo…
##  9 N107US  -5.73   1999 Fixe… AIRBUS INDU… A320…       2   182    NA Turbo…
## 10 N108UW  -1.25   1999 Fixe… AIRBUS INDU… A320…       2   182    NA Turbo…
## # … with 4,034 more rows, and 1 more variable: age <dbl>

Step #3: Finally, create an effective visualization of the data.

plane_delays %>%
  left_join(planes, by = "tailnum") %>%
  mutate(age = 2013 - year) %>%
  ggplot(aes(x = age, y = delay)) + 
  geom_point() + 
  geom_smooth() +
  labs(title = "Mean arrival delay versus plane age",
       subtitle = "planes departing NYC airports in 2013",
       x = "Age of plane", y = "Mean arrival delay") + 
  theme_bw()

The data wrangling cheat sheet is extremely helpful.

Additional Resources

https://rstudio.com/wp-content/uploads/2015/02/data-wrangling-cheatsheet.pdf