Relational Data

pairs of tables

mutating joins

filtering joins

set operations

nycflights datasets

primary key and foreign key

troublesome reality

planes %>% 
  count(tailnum) %>% 
  filter(n > 1)

inner joins

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

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

outer joins

join many-to-many

duplicate keys

one table has duplicate key

> x
# A tibble: 4 x 2
    key val_x
  <dbl> <chr>
1     1    x1
2     2    x2
3     2    x3
4     1    x4
> y
# A tibble: 2 x 2
    key val_y
  <dbl> <chr>
1     1    y1
2     2    y2
> left_join(x, y, by="key")
# A tibble: 4 x 3
    key val_x val_y
  <dbl> <chr> <chr>
1     1    x1    y1
2     2    x2    y2
3     2    x3    y2
4     1    x4    y1

> right_join(x, y, by="key")
# A tibble: 4 x 3
    key val_x val_y
  <dbl> <chr> <chr>
1     1    x1    y1
2     1    x4    y1
3     2    x2    y2
4     2    x3    y2

both tables have duplicate keys

> x
# A tibble: 4 x 2
    key val_x
  <dbl> <chr>
1     1    x1
2     2    x2
3     2    x3
4     3    x4
> y
# A tibble: 4 x 2
    key val_y
  <dbl> <chr>
1     1    y1
2     2    y2
3     2    y3
4     3    y4
> left_join(x, y, by = "key")
# A tibble: 6 x 3
    key val_x val_y
  <dbl> <chr> <chr>
1     1    x1    y1
2     2    x2    y2
3     2    x2    y3
4     2    x3    y2
5     2    x3    y3
6     3    x4    y4

natural joins

> flights2 <- flights %>% 
+   select(year:day, hour, origin, dest, tailnum, carrier)
> flights2
# A tibble: 336,776 x 8
    year month   day  hour origin  dest tailnum carrier
   <int> <int> <int> <dbl>  <chr> <chr>   <chr>   <chr>
 1  2013     1     1     5    EWR   IAH  N14228      UA
 2  2013     1     1     5    LGA   IAH  N24211      UA
 3  2013     1     1     5    JFK   MIA  N619AA      AA
 4  2013     1     1     5    JFK   BQN  N804JB      B6
 5  2013     1     1     6    LGA   ATL  N668DN      DL
 6  2013     1     1     5    EWR   ORD  N39463      UA
 7  2013     1     1     6    EWR   FLL  N516JB      B6
 8  2013     1     1     6    LGA   IAD  N829AS      EV
 9  2013     1     1     6    JFK   MCO  N593JB      B6
10  2013     1     1     6    LGA   ORD  N3ALAA      AA
# ... with 336,766 more rows

join setup

join setup 2

filtering joins

semi_join()

subset of x containing only those rows of x for which the specified key has a match in y

x <- tribble(
  ~key, ~val_x,
     1, "x1",
     2, "x2",
     3, "x3"
)
y <- tribble(
  ~key, ~val_y,
     1, "y1",
     2, "y2",
     4, "y3"
)

> semi_join(x, y)
Joining, by = "key"
# A tibble: 2 x 2
    key val_x
  <dbl> <chr>
1     1    x1
2     2    x2

anti_join()

subset of x containing only those rows of x for which the specified key has NO match in y

set operations

> df1 <- tribble(
+   ~x, ~y,
+    1,  1,
+    2,  1
+ )
> df2 <- tribble(
+   ~x, ~y,
+    1,  1,
+    1,  2
+ )
> intersect(df1, df2)
# A tibble: 1 x 2
      x     y
  <dbl> <dbl>
1     1     1
> union(df1, df2)
# A tibble: 3 x 2
      x     y
  <dbl> <dbl>
1     1     2
2     2     1
3     1     1
> setdiff(df1, df2)
# A tibble: 1 x 2
      x     y
  <dbl> <dbl>
1     2     1

Canada NAFTA Imports and Exports