在 Pandas 中使用 difflib SequenceMatcher 比率进行合并 [英] Using difflib SequenceMatcher ratio to merge in Pandas
问题描述
我想弄清楚是否有一种方法可以根据 difflib SequenceMatcher 比率在 Pandas 中对字符串进行模糊合并.基本上,我有两个如下所示的数据框:
I'm trying to figure out if there's a way to do fuzzy merges of string in Pandas based on the difflib SequenceMatcher ration. Basically, I have two dataframes that look like this:
df_a
company address merged
Apple PO Box 3435 1
df_b
company address
Apple Inc PO Box 343
我想像这样合并:
df_c = pd.merge(df_a, df_b, how = 'left', on = (difflib.SequenceMatcher(None, df_a['company'], df_b['company']).ratio() > .6) and (difflib.SequenceMatcher(None, df_a['address'], df_b['address']).ratio() > .6)
有一些帖子与我正在寻找的内容相近,但没有一个适合我想做的事情.关于如何使用 difflib 进行这种模糊合并的任何建议?
There are a few posts that are close to what I'm looking for, but none of them work with what I want to do. Any suggestions on how to do this kind of fuzzy merge using difflib?
推荐答案
可能有用的方法:测试所有列值组合的部分匹配.如果有匹配项,则为 df_b 分配一个键以进行合并
Something that might work: test for partial matches for all combinations of column values. If there is a match assign a key to df_b for merging
df_a['merge_comp'] = df_a['company'] # we will use these as the merge keys
df_a['merge_addr'] = df_a['address']
for comp_a, addr_a in df_a[['company','address']].values:
for ixb, (comp_b, addr_b) in enumerate(df_b[['company','address']].values)
if difflib.SequenceMatcher(None,comp_a,comp_b).ratio() > .6:
df_b.ix[ixb,'merge_comp'] = comp_a # creates a merge key in df_b
if difflib.SequenceMatcher(None,addr_a, addr_b).ratio() > .6:
df_b.ix[ixb,'merge_addr'] = addr_a # creates a merge key in df_b
现在可以合并了
merged_df = pandas.merge(df_a,df_b,on=['merge_addr','merge_comp'],how='inner')
这篇关于在 Pandas 中使用 difflib SequenceMatcher 比率进行合并的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!