r - 基于字符串模糊匹配的连接数据框

Question

library(tidyverse)
library(fuzzyjoin)
df1 <- tibble(col1 = c("Apple Shipping", "Banana Shipping", "FedEX USA Ground",
                       "FedEx USA Commercial", "FedEx International"),
              col2 = 1:5)
#> # A tibble: 5 x 2
#>   col1                  col2
#>   <chr>                <int>
#> 1 Apple Shipping           1
#> 2 Banana Shipping          2
#> 3 FedEX USA Ground         3
#> 4 FedEx USA Commercial     4
#> 5 FedEx International      5

df2 <- tibble(col3 = c("Banana", "FedEX USA"), col4 = c(700, 900))
#> # A tibble: 2 x 2
#>   col3       col4
#>   <chr>     <dbl>
#> 1 Banana      700
#> 2 FedEX USA   900

上面显示了我正在使用的两个数据框。我想在 col1 和 col3 上模糊连接它们，以得出类似于下面直接显示的内容。基本上规则是，“如果 col3 中的所有文本都在 col1 中的任何一个中，则将其视为匹配”。

#> # A tibble: 3 x 4
#>   col1                  col2  col3      col4
#>   <chr>                <int>  <chr>    <int>
#> 1 Banana Shipping          2  Banana     700
#> 2 FedEX USA Ground         3  FedEx USA  900
#> 3 FedEx USA Commercial     4  FedEx USA  900

This older SO question appears to offer the solution ，但在这种情况下似乎不太有效，我收到如下所示的错误:

df1 %>% regex_inner_join(df2, by = c(string = "col3"))
#> Error: All columns in a tibble must be 1d or 2d objects:
#> * Column `col` is NULL
#> Run `rlang::last_error()` to see where the error occurred.

library(stringr)
df1 %>% fuzzy_inner_join(df2, by = c("string" = "col3"), match_fun = str_detect)
#> Error: All columns in a tibble must be 1d or 2d objects:
#> * Column `col` is NULL
#> Run `rlang::last_error()` to see where the error occurred.

如何使用 R 执行此模糊连接？

Answer 1

也许这就是您正在寻找的东西？

library(dplyr)
library(fuzzyjoin)
library(stringr)
df1 %>% fuzzy_inner_join(df2,by=c("col1" = "col3"),match_fun = str_detect)
## A tibble: 2 x 4
#  col1              col2 col3       col4
#  <chr>            <int> <chr>     <dbl>
#1 Banana Shipping      2 Banana      700
#2 FedEX USA Ground     3 FedEX USA   900

如果您想忽略大小写，您可以定义自己的str_detect。

my_str_detect <- function(x,y){str_detect(x,regex(y, ignore_case = TRUE))}
df1 %>% fuzzy_inner_join(df2,by=c("col1" = "col3"),match_fun = my_str_detect)
## A tibble: 3 x 4
#  col1                  col2 col3       col4
#  <chr>                <int> <chr>     <dbl>
#1 Banana Shipping          2 Banana      700
#2 FedEX USA Ground         3 FedEX USA   900
#3 FedEx USA Commercial     4 FedEX USA   900

要获得奖励积分，您可以使用 this question 中的 agrepl .

您可以修改 max.distance = 参数，并可能添加 cost =。请参阅 help(agrepl) 了解更多信息。

my_match_fun <- Vectorize(function(x,y) agrepl(x, y, ignore.case=TRUE, max.distance = 0.7, useBytes = TRUE))
df1 %>% fuzzy_inner_join(df2,by=c("col1" = "col3"),match_fun = my_match_fun)
## A tibble: 4 x 4
#  col1                  col2 col3       col4
#  <chr>                <int> <chr>     <dbl>
#1 Banana Shipping          2 Banana      700
#2 FedEX USA Ground         3 FedEX USA   900
#3 FedEx USA Commercial     4 FedEX USA   900
#4 FedEx International      5 FedEX USA   900