与R的模糊LEFT连接 [英] fuzzy LEFT join with R

问题描述

library(tidyverse)
library(fuzzyjoin)

df1 <- tibble(col1 = c("apple", "banana", "carrot"),
              col2 = as.numeric(0:2),
              col3 = as.numeric(0:2))
#> # A tibble: 3 x 3
#>   col1   col2  col3
#>   <chr> <int> <int>
#> 1 apple     0     0
#> 2 banana    1     1
#> 3 carrot    2     2

df2 <- tibble(col4 = c("app", "carr"), col5 = c(5, 9), matched = rep(TRUE, 2))
#> # A tibble: 2 x 3
#>   col4   col5 matched
#>   <chr> <dbl> <lgl>  
#> 1 app       5 TRUE   
#> 2 carr      9 TRUE 

我在df1和df2上方有两个数据帧. 我需要为df1创建新列，以告知每一行是否与df2中的条目匹配.

I've got two data frames above df1 and df2. I need to create a new column for df1 that tells whether each row matches with an entry in df2, or not.

我还必须进行模糊匹配，并且模糊性必须不区分大小写(因此自定义ci_str_detect函数):

I also have to fuzzy match, and the fuzziness need to be case insensitive (hence the custom ci_str_detect function):

ci_str_detect <- function(x, y){str_detect(x, regex(y, ignore_case = TRUE))}

df1 %>% 
  fuzzy_inner_join(df2, by = c("col1" = "col4"), match_fun = ci_str_detect)
#># A tibble: 2 x 6
#>  col1    col2  col3 col4   col5 matched
#>  <chr>  <dbl> <dbl> <chr> <dbl> <lgl>  
#>1 apple      0     0 app       5 TRUE   
#>2 carrot     2     2 carr      9 TRUE 

不幸的是，在这种情况下，模糊连接R包似乎只执行INNER JOIN，而不执行我需要的LEFT JOIN.

Unfortunately (in this case) the fuzzyjoin R package appears to only do INNER JOINs, and not the LEFT JOIN that I need.

最终我需要此输出:

#> # A tibble: 3 x 6
#>   col1    col2  col3 col4   col5  matched
#>   <chr>  <dbl> <dbl> <chr> <dbl>  <lgl>  
#> 1 apple      0     0 app       5  TRUE   
#> 2 banana     1     1 NA       NA  FALSE 
#> 3 carrot     2     2 carr      9  TRUE 

...和一个LEFT JOIN将提供如下所示的中间数据框，我可以将NA替换为FALSE以获得我最终想要的(直接在上方).

... and a LEFT JOIN would provide the intermediate data frame shown below, that I could replace NA with FALSE to get what I ultimately want (directly above).

#> # A tibble: 3 x 6
#>   col1    col2  col3 col4   col5  matched
#>   <chr>  <dbl> <dbl> <chr> <dbl>  <lgl>  
#> 1 apple      0     0 app       5  TRUE   
#> 2 banana     1     1 NA       NA  NA 
#> 3 carrot     2     2 carr      9  TRUE 

如何模糊LEFT加入R?

How can I fuzzy LEFT join in R?

推荐答案

Voila:)

fuzzy_left_join(df1, df2, match_fun = ci_str_detect, by = c(col1 = "col4"))

这篇关于与R的模糊LEFT连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！