R dplyr mutate-joins 变异连接

内部联接

inner_join() 仅保留 x 中在 y 中具有匹配键的观察结果。

内连接最重要的属性是任一输入中不匹配的行不会包含在结果中。这意味着通常内部联接在大多数分析中并不适用，因为它很容易丢失观察结果。

外连接

三个外连接保留至少出现在一个 DataFrame 中的观察结果：

• left_join() 将所有观察结果保留在 x 中。

• right_join() 将所有观察结果保留在 y 中。

• full_join() 将所有观察结果保留在 x 和 y 中。

inner_join(
  x,
  y,
  by = NULL,
  copy = FALSE,
  suffix = c(".x", ".y"),
  ...,
  keep = NULL
)

# S3 method for data.frame
inner_join(
  x,
  y,
  by = NULL,
  copy = FALSE,
  suffix = c(".x", ".y"),
  ...,
  keep = NULL,
  na_matches = c("na", "never"),
  multiple = "all",
  unmatched = "drop",
  relationship = NULL
)

left_join(
  x,
  y,
  by = NULL,
  copy = FALSE,
  suffix = c(".x", ".y"),
  ...,
  keep = NULL
)

# S3 method for data.frame
left_join(
  x,
  y,
  by = NULL,
  copy = FALSE,
  suffix = c(".x", ".y"),
  ...,
  keep = NULL,
  na_matches = c("na", "never"),
  multiple = "all",
  unmatched = "drop",
  relationship = NULL
)

right_join(
  x,
  y,
  by = NULL,
  copy = FALSE,
  suffix = c(".x", ".y"),
  ...,
  keep = NULL
)

# S3 method for data.frame
right_join(
  x,
  y,
  by = NULL,
  copy = FALSE,
  suffix = c(".x", ".y"),
  ...,
  keep = NULL,
  na_matches = c("na", "never"),
  multiple = "all",
  unmatched = "drop",
  relationship = NULL
)

full_join(
  x,
  y,
  by = NULL,
  copy = FALSE,
  suffix = c(".x", ".y"),
  ...,
  keep = NULL
)

# S3 method for data.frame
full_join(
  x,
  y,
  by = NULL,
  copy = FALSE,
  suffix = c(".x", ".y"),
  ...,
  keep = NULL,
  na_matches = c("na", "never"),
  multiple = "all",
  relationship = NULL
)

其他连接：cross_join()、filter-joins、nest_join()

band_members %>% inner_join(band_instruments)
#> Joining with `by = join_by(name)`
#> # A tibble: 2 × 3
#>   name  band    plays 
#>   <chr> <chr>   <chr> 
#> 1 John  Beatles guitar
#> 2 Paul  Beatles bass  
band_members %>% left_join(band_instruments)
#> Joining with `by = join_by(name)`
#> # A tibble: 3 × 3
#>   name  band    plays 
#>   <chr> <chr>   <chr> 
#> 1 Mick  Stones  NA    
#> 2 John  Beatles guitar
#> 3 Paul  Beatles bass  
band_members %>% right_join(band_instruments)
#> Joining with `by = join_by(name)`
#> # A tibble: 3 × 3
#>   name  band    plays 
#>   <chr> <chr>   <chr> 
#> 1 John  Beatles guitar
#> 2 Paul  Beatles bass  
#> 3 Keith NA      guitar
band_members %>% full_join(band_instruments)
#> Joining with `by = join_by(name)`
#> # A tibble: 4 × 3
#>   name  band    plays 
#>   <chr> <chr>   <chr> 
#> 1 Mick  Stones  NA    
#> 2 John  Beatles guitar
#> 3 Paul  Beatles bass  
#> 4 Keith NA      guitar

# To suppress the message about joining variables, supply `by`
band_members %>% inner_join(band_instruments, by = join_by(name))
#> # A tibble: 2 × 3
#>   name  band    plays 
#>   <chr> <chr>   <chr> 
#> 1 John  Beatles guitar
#> 2 Paul  Beatles bass  
# This is good practice in production code

# Use an equality expression if the join variables have different names
band_members %>% full_join(band_instruments2, by = join_by(name == artist))
#> # A tibble: 4 × 3
#>   name  band    plays 
#>   <chr> <chr>   <chr> 
#> 1 Mick  Stones  NA    
#> 2 John  Beatles guitar
#> 3 Paul  Beatles bass  
#> 4 Keith NA      guitar
# By default, the join keys from `x` and `y` are coalesced in the output; use
# `keep = TRUE` to keep the join keys from both `x` and `y`
band_members %>%
  full_join(band_instruments2, by = join_by(name == artist), keep = TRUE)
#> # A tibble: 4 × 4
#>   name  band    artist plays 
#>   <chr> <chr>   <chr>  <chr> 
#> 1 Mick  Stones  NA     NA    
#> 2 John  Beatles John   guitar
#> 3 Paul  Beatles Paul   bass  
#> 4 NA    NA      Keith  guitar

# If a row in `x` matches multiple rows in `y`, all the rows in `y` will be
# returned once for each matching row in `x`.
df1 <- tibble(x = 1:3)
df2 <- tibble(x = c(1, 1, 2), y = c("first", "second", "third"))
df1 %>% left_join(df2)
#> Joining with `by = join_by(x)`
#> # A tibble: 4 × 2
#>       x y     
#>   <dbl> <chr> 
#> 1     1 first 
#> 2     1 second
#> 3     2 third 
#> 4     3 NA    

# If a row in `y` also matches multiple rows in `x`, this is known as a
# many-to-many relationship, which is typically a result of an improperly
# specified join or some kind of messy data. In this case, a warning is
# thrown by default:
df3 <- tibble(x = c(1, 1, 1, 3))
df3 %>% left_join(df2)
#> Joining with `by = join_by(x)`
#> Warning: Detected an unexpected many-to-many relationship between `x` and `y`.
#> ℹ Row 1 of `x` matches multiple rows in `y`.
#> ℹ Row 1 of `y` matches multiple rows in `x`.
#> ℹ If a many-to-many relationship is expected, set `relationship =
#>   "many-to-many"` to silence this warning.
#> # A tibble: 7 × 2
#>       x y     
#>   <dbl> <chr> 
#> 1     1 first 
#> 2     1 second
#> 3     1 first 
#> 4     1 second
#> 5     1 first 
#> 6     1 second
#> 7     3 NA    

# In the rare case where a many-to-many relationship is expected, set
# `relationship = "many-to-many"` to silence this warning
df3 %>% left_join(df2, relationship = "many-to-many")
#> Joining with `by = join_by(x)`
#> # A tibble: 7 × 2
#>       x y     
#>   <dbl> <chr> 
#> 1     1 first 
#> 2     1 second
#> 3     1 first 
#> 4     1 second
#> 5     1 first 
#> 6     1 second
#> 7     3 NA    

# Use `join_by()` with a condition other than `==` to perform an inequality
# join. Here we match on every instance where `df1$x > df2$x`.
df1 %>% left_join(df2, join_by(x > x))
#> # A tibble: 6 × 3
#>     x.x   x.y y     
#>   <int> <dbl> <chr> 
#> 1     1    NA NA    
#> 2     2     1 first 
#> 3     2     1 second
#> 4     3     1 first 
#> 5     3     1 second
#> 6     3     2 third 

# By default, NAs match other NAs so that there are two
# rows in the output of this join:
df1 <- data.frame(x = c(1, NA), y = 2)
df2 <- data.frame(x = c(1, NA), z = 3)
left_join(df1, df2)
#> Joining with `by = join_by(x)`
#>    x y z
#> 1  1 2 3
#> 2 NA 2 3

# You can optionally request that NAs don't match, giving a
# a result that more closely resembles SQL joins
left_join(df1, df2, na_matches = "never")
#> Joining with `by = join_by(x)`
#>    x y  z
#> 1  1 2  3
#> 2 NA 2 NA