r - R:记录链接问题,所有字段组合在 1 列中
问题描述
我必须将数据集 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)
我希望无论在同一字段中以及无论姓名、城市和出生日期的顺序如何,记录都能匹配。
解决方案
不明白你的意思。即使使用“组合列”,我也会得到类似的结果
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
推荐阅读
- maven - Maven:NoClassDefFoundError,来自其他导入项目的依赖项与我的依赖项冲突
- azure - 我有大约 20 个 excel /pdf 类型的文件,可以从 Http Server 下载。我需要使用数据工厂将此文件加载到 Azure 存储中
- java - 编辑文件中的特定字符串?
- sql - 如果一个字符串的任何字符串char包含在另一个字符串中,如何在sql中搜索?
- azure - Azure Powershell 更改以检查 Azure Data Lake Storage Gen 1 中的文件并触发 BAT 脚本
- java - Java,TDD 不等于时为真?
- r - 我无法从 R 中正确保存图
- c# - UPW webrtc_PeerCC 到 PeerCC 浏览器
- javascript - Firestore 规则 - 查询文档是否存在 - 权限不足
- pdf - 在不更改布局和格式的情况下,在 python 中从 pdf 中提取文本的最佳方法是什么?