Chapter 13 - Relational Data

library(tidyverse)
library(nycflights13)

Airlines

flights

airlines

airports

planes

weather

Check is there are any duplicate tailnumbers.

Duplicated?

weather %>% 
  count(year, month, day, hour, origin) %>% 
  filter(n > 1)

Duplicates?

Note there are duplicated dates for flights and tailnum in the flights dataset. This may be a problem.

flights %>% 
  count(year, month, day, flight) %>% 
  filter(n > 1)

flights %>% 
  count(year, month, day, tailnum) %>% 
  filter(n > 1)

Join airline name to the flights data.

flights2 <- flights %>% 
  select(year:day, hour, origin, dest, tailnum, carrier)
flights2

flights2 %>%
  select(-origin, -dest) %>% 
  left_join(airlines, by = "carrier")

Simple examples.

x <- tribble(
  ~key, ~val_x,
     1, "x1",
     2, "x2",
     3, "x3"
)
x

y <- tribble(
  ~key, ~val_y,
     1, "y1",
     2, "y2",
     4, "y3"
)
y

x %>% 
  inner_join(y, by = "key")

#> # A tibble: 2 x 3
#>     key val_x val_y
#>   <dbl> <chr> <chr>
#> 1     1 x1    y1   
#> 2     2 x2    y2

Duplicate keys.

x <- tribble(
  ~key, ~val_x,
     1, "x1",
     2, "x2",
     2, "x3",
     1, "x4"
)
x

y <- tribble(
  ~key, ~val_y,
     1, "y1",
     2, "y2"
)
y

left_join(x, y, by = "key")

Both with duplicate keys.

x <- tribble(
  ~key, ~val_x,
     1, "x1",
     2, "x2",
     2, "x3",
     3, "x4"
)
x

y <- tribble(
  ~key, ~val_y,
     1, "y1",
     2, "y2",
     2, "y3",
     3, "y4"
)
y

left_join(x, y, by = "key")