如何使用Spark在另一个数据集中找到高度相似的观测值 [英] How to find highly similar observations in another dataset using Spark

问题描述

我有两个csv文件.文件1:

I have two csv files. File 1:

D,FNAME,MNAME,LNAME,GENDER,DOB,snapshot
2,66M,J,Rock,F,1995,201211.0
3,David,HM,Lee,M,,201211.0
6,66M,,Rock,F,,201211.0
0,David,H M,Lee,,1990,201211.0
3,Marc,H,Robert,M,2000,201211.0
6,Marc,M,Robert,M,,201211.0
6,Marc,MS,Robert,M,2000,201211.0
3,David,M,Lee,,1990,201211.0
5,Paul,ABC,Row,F,2008,201211.0
3,Paul,ACB,Row,,,201211.0
4,David,,Lee,,1990,201211.0
4,66,J,Rock,,1995,201211.0

文件2:

PID,FNAME,MNAME,LNAME,GENDER,DOB,FNAMELNAMEMNAMEGENDERDOB
S2,66M,J,Rock,F,1995,66MRockJF1995
S3,David,HM,Lee,M,1990,DavidLeeHMM1990
S0,Marc,HM,Robert,M,2000,MarcRobertHMM2000
S1,Marc,MS,Robert,M,2000,MarcRobertMSM2000
S6,Paul,Row,M,2008,PaulRowM2008
S7,Sam,O,Baby,F,2018,SamBabyOF2018

例如，我想用文件1中的 MarcHRobertM2000 提取文件2中的那些高度相似的观测值. 我的预期输出将是:

For example, I want to extract those highly similar observations in File 2 with MarcHRobertM2000 in File 1. My expected output will be:

S0,Marc,HM,Robert,M,2000,MarcRobertHMM2000
S1,Marc,MS,Robert,M,2000,MarcRobertMSM2000

我使用了以下代码:

sqlContext.registerDataFrameAsTable(df2,'table')
query=""" SELECT PID, FNAMELNAMEMNAMEGENDERDOB, similarity(lower(FNAMELNAMEMNAMEGENDERDOB), 'MarcHRobertM2000') as sim
    FROM table
    WHERE sim>0.7 """
df=sqlContext.sql(query)

看起来SQL中的similarity在sqlcontext中不起作用.我不知道如何解决它.另外，文件2很大，大约5 GB，因此我没有在python中使用fuzzywuzzy.并且soundex不令人满意.你可以帮帮我吗?谢谢你.

It looks like the similarity in SQL does not work in sqlcontext. I have no idea how to fix it. In addition, File 2 is big, around 5 GB so I did not use the fuzzywuzzy in python. And soundex is not satisfying. Could you help me? Thank you.

推荐答案

您可以使用Levenshtein距离函数来检查相似性.

you can use Levenshtein distance function to check the similarity.

请参考以下代码

query=""" SELECT PID, FNAMELNAMEMNAMEGENDERDOB, levenshtein(FNAMELNAMEMNAMEGENDERDOB, 'MarcHRobertM2000') as sim
    FROM table
    WHERE sim < 4 """

还请检查 https ://medium.com/@mrpowers/fuzzy-matching-in-spark-with-soundex-and-levenshtein-distance-6749f5af8f28 以便阅读.

这篇关于如何使用Spark在另一个数据集中找到高度相似的观测值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！