R :记录链接问题，所有字段合并在 1 列中

Question

我必须将数据集 A 中的 A 列与数据集 B 中的 B 列匹配。但是不同的变量不在单独的字段中（列 a、b、c），而是在同一个字段中。

我一直在看包 RecordLinkage 和 fastLink 它们在字段分开时效果很好。

单独的字段 ：

# make dataframe 1
fname <- c("ash", "aalok", "aaron", "adam", "adrian", "ajay")
lname <- c("perry", "phillips", "picardo", "pinck", "pinnick-flood", "pledger")
dob <- c(1957, 1971, 1948, 1961, 1972, 2000)
city <- c("Oakland", "Piedmont", "Pleasanton", "San Leandro", "San Lorenzo", "Melbourne")
street <- c(" 100th ave", " 107th ave", " 10th ave", " 159th ave", " 165th ave apt 112", " 167th ave")    
# make dataframe 2
fname2 <- c("ashley", "aaloknath", "aron", "adam", "adrian", "ajaay")
lname2 <- c("perry", "philips", "picardo", "pinnck", "pinnick flood", "pleedger")
dob2 <- c(1950, 1971, 1948, 1900, 1972, 2000)
city2 <- c("Oakland City", "Piedmont", "Pleasanton city", "San Leandro", "San Lorenzo", "Melbourne")
street2 <- c(" 100 ave", " 107th ave", " 100 ave", " 159th ave", " 1652 ave apt 112", " 167th")

df1 <- data.frame(fname, lname, dob, city, street)
df2 <- data.frame(fname2, lname2, dob2, city2, street2)
# change order of rows
df2 <- df2[c(6, 3, 2, 4, 5, 1), ]
# columns must have same name
names(df2) <- names(df1)

快速链接示例

library(stringdist)
library(Rcpp)
library(fastLink)
matches.out <- fastLink(
  dfA = df1,
  dfB = df2,
  varnames = c("fname", "lname", "dob", "city", "street"),
  stringdist.match = c("fname", "lname", "city", "street"),
  numeric.match = "dob"
)

> matches.out$matches
  inds.a inds.b
1      6      1
2      3      2
3      2      3
4      4      4
5      5      5
6      1      6

记录链接示例

a <- compare.linkage(df1, df2)
# Calculate M and U weights using the EM algorithm
b <- emWeights(a, cutoff = 0.8)
summary(b)
allPairs <- getPairs(b)
head(allPairs)
# Determine two thresholds
finalPairs <- getPairs(b, max.weight = 100, min.weight = 0)
head(finalPairs)
  id  fname         lname  dob        city             street             Weight
1  4   adam         pinck 1961 San Leandro          159th ave                   
2  4   adam        pinnck 1900 San Leandro          159th ave  30.35517990295238
3                                                                               
4  5 adrian pinnick-flood 1972 San Lorenzo  165th ave apt 112                   
5  5 adrian pinnick flood 1972 San Lorenzo   1652 ave apt 112  24.99000510744970

组合字段的问题：

matchA <- c("ash perry 1957 Oakland 100th ave", "aalok  1971 phillips Piedmont 107th ave", "aaron picardo Pleasanton 1948 10th ave")
df3 <- data.frame(matchA)
matchB <- c("1950 picard aron Pleasanton City 10 ave", "aalok   philips Piedmont 1971 107th ave", "ashley perry Oakland City 1950 100 ave")
df4 <- data.frame(matchB)

我希望记录是匹配的，无论是否在同一字段中，无论名称，城市和dob的顺序如何。

Answer 1

不确定你的意思。即使使用"组合列"，我也得到了类似的结果

library(fastLink)
data(samplematch)
fastLink::fastLink(dfA = dfA, dfB = dfB, 
                   varnames = colnames(dfA),
                   return.all = TRUE) -> out
# concatenate all columns
apply(dfA, 1, paste, collapse = " ")-> dfAall
dfA$all <- dfAall
apply(dfB, 1, paste, collapse = " ")-> dfBall
dfB$all <- dfBall
# run on concatenated column
fastLink::fastLink(dfA = dfA, dfB = dfB, 
                   varnames = c('all'),
                   return.all = TRUE) -> out2
# compare results
out$matches == out2$matches