与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屋!