Fuzzy matching gives unmatched data

Question

Hi I want to merge df1 and df2 based on x.

df1<-tibble(x=c("TRP OVERSEAS STOCK |","PIMCO TOTAL RETURN FUND"),y=c(1,2))

df2<-tibble(x=c("AB Portfolios: AB Growth Fund; Class K Shares","PIMCO TOTAL RETURN FUND"),z=c(2020,2021))

However, when I use

fuzzy_join(df1, df2, match_fun = function(x,y) str_detect(y, x), by = "x")

It gives me the following output,

"TRP OVERSEAS STOCK |" should not be matched with anything. But they are listed in the output. I'm wondering why does that happen and how to solve this issue? Thank you!!

Could you please elaborate which package you use for `fuzzy_join`? — Sandwichnick, Apr 27 '22 at 07:08

AndrewGB · Answer 1 · 2022-04-27T07:26:52.963

The issue is with |, as it is being used as an "or". So, it is just joining to the other rows. However, we could remove it first, then still use fuzzyjoin.

library(fuzzyjoin)
library(tidyverse)

df1 %>% 
  mutate(x = str_replace_all(x, "[|]", "")) %>% 
  fuzzy_join(., df2, match_fun = function(x,y) str_detect(y, x), by = "x")

Or if you don't want to load more packages, then we could make the change in base R first.

df1$x <- gsub("[|]", "", df1$x)

fuzzy_join(df1, df2, match_fun = function(x,y) str_detect(y, x), by = "x")

Output

  x.x                         y x.y                         z
  <chr>                   <dbl> <chr>                   <dbl>
1 PIMCO TOTAL RETURN FUND     2 PIMCO TOTAL RETURN FUND  2021

Interesting. Thank you Andrew! This is really helpful! – Jane Apr 27 '22 at 07:29 — Jane, Apr 27 '22 at 07:29

score 1 · Answer 2 · answered Apr 27 '22 at 07:37

You have to decide whether to fuzzyjoin or not. If fuzzyjoin follow the answer by @AndrewGB and also Fuzzy matching two data frames your last question.

And if not fuzzyjoin then:

df2 %>% inner_join(df1, by = "x")

  x                           z     y
  <chr>                   <dbl> <dbl>
1 PIMCO TOTAL RETURN FUND  2021     2

Fuzzy matching gives unmatched data

2 Answers2