Relational Data

Load packages

library(tidyverse)

Look at the first table band_members

glimpse(band_members)

## Rows: 3
## Columns: 2
## $ name <chr> "Mick", "John", "Paul"
## $ band <chr> "Stones", "Beatles", "Beatles"

Look at the second table band_instruments

glimpse(band_instruments) 

## Rows: 3
## Columns: 2
## $ name  <chr> "John", "Paul", "Keith"
## $ plays <chr> "guitar", "bass", "guitar"

Using inner_join

inner_join(band_instruments, band_members, by = "name")

## # A tibble: 2 × 3
##   name  plays  band   
##   <chr> <chr>  <chr>  
## 1 John  guitar Beatles
## 2 Paul  bass   Beatles

Using left_join

band_instruments %>% 
  left_join(band_members, by = "name")

## # A tibble: 3 × 3
##   name  plays  band   
##   <chr> <chr>  <chr>  
## 1 John  guitar Beatles
## 2 Paul  bass   Beatles
## 3 Keith guitar <NA>

Using right_join

band_instruments %>% 
  right_join(band_members, by = "name")

## # A tibble: 3 × 3
##   name  plays  band   
##   <chr> <chr>  <chr>  
## 1 John  guitar Beatles
## 2 Paul  bass   Beatles
## 3 Mick  <NA>   Stones

Using full_join

band_instruments %>% 
  full_join(band_members, by = "name")

## # A tibble: 4 × 3
##   name  plays  band   
##   <chr> <chr>  <chr>  
## 1 John  guitar Beatles
## 2 Paul  bass   Beatles
## 3 Keith guitar <NA>   
## 4 Mick  <NA>   Stones

Duplicate keys

x <- tribble(
  ~key, ~val_x,
     1, "x1",
     2, "x2",
     2, "x3"
)
y <- tribble(
  ~key, ~val_y,
     1, "y1",
     2, "y2"
)
left_join(x, y, by = "key")

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

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

## # A tibble: 5 × 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

Defining the key columns

Using default key

inner_join(band_members, band_instruments)

## # A tibble: 2 × 3
##   name  band    plays 
##   <chr> <chr>   <chr> 
## 1 John  Beatles guitar
## 2 Paul  Beatles bass

Using a character vector

inner_join(band_members, band_instruments, by = "name")

## # A tibble: 2 × 3
##   name  band    plays 
##   <chr> <chr>   <chr> 
## 1 John  Beatles guitar
## 2 Paul  Beatles bass

Using a named character vector

inner_join(band_members, band_instruments, by = c("name" = "name"))

## # A tibble: 2 × 3
##   name  band    plays 
##   <chr> <chr>   <chr> 
## 1 John  Beatles guitar
## 2 Paul  Beatles bass

Semi Join

semi_join(band_members, band_instruments, by = c("name" = "name"))

## # A tibble: 2 × 2
##   name  band   
##   <chr> <chr>  
## 1 John  Beatles
## 2 Paul  Beatles

Anti Join

anti_join(band_members, band_instruments, by = c("name" = "name"))

## # A tibble: 1 × 2
##   name  band  
##   <chr> <chr> 
## 1 Mick  Stones

Intersect

df1 <- tribble(
  ~x, ~y,
   1,  1,
   2,  1
)
df2 <- tribble(
  ~x, ~y,
   1,  1,
   1,  2
)

intersect(df1, df2)

## # A tibble: 1 × 2
##       x     y
##   <dbl> <dbl>
## 1     1     1

Union

union(df1, df2)

## # A tibble: 3 × 2
##       x     y
##   <dbl> <dbl>
## 1     1     1
## 2     2     1
## 3     1     2

setdiff

setdiff(df1, df2)

## # A tibble: 1 × 2
##       x     y
##   <dbl> <dbl>
## 1     2     1