R：如何基于不完全相同的模糊匹配字符串左连接两个表-解网

问：

我正在尝试将表 1“人名”左联接到表 2“姓名”，并从表 2 的“工作组”列中获取值

df1 <- read.table(text="
Person_Name
PEREZ, MINDY
PEREZ, ABA
CLARKE, LINDA
THOMAS, NICOLE", header=T, sep="|") 

df2 <- read.table(text=
'Name Work_Group 
"Perez-Tie, Mindy" "Group A"
"Rulnick-Perez, Aba" "Group C"
"Mcabe-Clarke, Linda" "Group A"
"Thomas, Nicole" "Group B"', header=T)

这是我当前的代码，但它没有显示任何匹配项。我不确定我可能做错了什么，但任何帮助都会很棒！

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

result <- fuzzy_left_join(Table1,Table2, by = c("Person_Name" = "Name"), 
                          match_fun = ci_str_detect   )  %>%
  select(PersoN_Name,Name,Work_Group)
print(result)

R 数据帧模糊联接

library(tidyverse)
# make sure the two dataframes have the same format and the same columns
df1 <- df1 %>% 
mutate(Person_Name = str_to_title(Person_Name)) %>%
separate_wider_delim(Person_Name, delim=", ", names=c("Last_Name", "First_Name"))

df2 <- df2 %>%
mutate(Name = str_to_title(Name)) %>%
separate_wider_delim(Name, delim=", ", names=c("Last_Name", "First_Name"))

# Maybe the simplest way of doing things (but maybe not what you had in mind ;) )
left_join(df1, df2, by="First_Name")

# But this is more robust
cross_join(df1, df2) %>%
filter(str_detect(Last_Name.y, Last_Name.x), str_detect(First_Name.y, First_Name.x)) %>%
select(Last_Name = Last_Name.y, First_Name = First_Name.y, Work_Group)

简而言之，您拥有的代码的问题在于字符串不匹配。以第一行为例： “Perez， Mindy”与“Perez-tie， Mindy”不匹配，反之亦然（两者均在此处显示为小写，以使其更加明显）。您希望分别匹配名字和姓氏。

您可以查看的另一件事是包中的函数（我无法让它在所有行中正常工作，但它在您的情况下可能很有用）stringdist_joinfuzzyjoin

1赞 LMc 7/20/2023 #2

下面是一个选项，在匹配之前需要一些字符串操作：fuzzyjoin

根据@moodymudskipper的评论更新

library(fuzzyjoin)

rev_str <- \(x) paste(rev(x), collapse = " ")

df1$rev_name = sapply(strsplit(df1$Person_Name, ", "), rev_str)
df2$rev_name = sapply(strsplit(df2$Name, ", "), rev_str)

stringdist_left_join(df1, df2, by = "rev_name",
                     method = "jw",
                     p = 0.15,
                     distance_col = "distance",
                     ignore_case = TRUE,
                     max_dist = 0.15) |> 
  subset(select = -c(rev_name.x, rev_name.y, distance))

这使用 Jaro-Winkler 距离，您可以在其中添加权重（），为以相同方式开始的字符串提供加成。这就是为什么我首先颠倒了这些名字，因为在你的回复中，它们往往是准确的。在查看了的值后，我选择了距离截止值，我将其包含在代码中，以便您可以看到。您可能需要针对实际数据集进行调整。p0.15distance

注意：值接近于零表示字符串更相似。

输出

     Person_Name                Name Work_Group
1   PEREZ, MINDY    Perez-Tie, Mindy    Group A
2     PEREZ, ABA  Rulnick-Perez, Aba    Group C
3  CLARKE, LINDA Mcabe-Clarke, Linda    Group A
4 THOMAS, NICOLE      Thomas, Nicole    Group B

R：如何基于不完全相同的模糊匹配字符串左连接两个表

R: How to left join two tables based on fuzzy matching strings that are not exactly the same

评论

评论