问题描述
基于此链接,我试图进行模糊查找::
based on this link I was trying to do a fuzzy lookup : Apply fuzzy matching across a dataframe column and save results in a new column between 2 dfs:
import pandas as pd
df1 = pd.DataFrame(data={'Brand_var':['Johnny Walker','Guiness','Smirnoff','Vat 69','Tanqueray']})
df2 = pd.DataFrame(data={'Product':['J.Walker Blue Label 12 CC','J.Morgan Blue Walker','Giness blue 150 CC','tqry qiuyur qtre','v69 g nesscom ui123']})
我有2个dfs df1和df2,需要通过模糊查找/其他合适的方法进行映射.
I have 2 dfs df1 and df2 which needs to be mapped via a fuzzy lookup/any other method which suits.
以下是我正在使用的代码:
Below is the code I am using:
from fuzzywuzzy import fuzz
from fuzzywuzzy import process
compare = pd.MultiIndex.from_product([df1['Brand_var'],
df2['Product']]).to_series()
def metrics(tup):
return pd.Series([fuzz.ratio(*tup),
fuzz.token_sort_ratio(*tup)],
['ratio', 'token'])
compare.apply(metrics)
df = compare.apply(metrics).unstack().idxmax().unstack(0)
print(df)
下面是我的输出:
ratio token
----------------------------------------------------------
Giness blue 150 CC Guiness Guiness
J.Morgan Blue Walker Johnny Walker Johnny Walker
J.Walker Blue Label 12 CC Johnny Walker Johnny Walker
tqry qiuyur qtre Tanqueray Tanqueray
v69 g nesscom ui123 Guiness Guiness
预期输出:
ratio token
----------------------------------------------------------
Giness blue 150 CC Guiness Guiness
J.Morgan Blue Walker None None
J.Walker Blue Label 12 CC Johnny Walker Johnny Walker
tqry qiuyur qtre Tanqueray Tanqueray
v69 g nesscom ui123 Vat 69 Vat 69
有什么建议可以得到我想要的输出的更好的方法(不使用模糊模糊也可以)?
Any suggestions what could be a better approach(not using fuzzy wuzzy is also fine) to get my desired output?
先谢谢您. :)
推荐答案
以下带有规则的代码将为您提供预期的输出:
The below code with rules will give you expected output:
import pandas as pd
from fuzzywuzzy import fuzz
df1 = pd.DataFrame(data={'Brand_var':['Johnny Walker','Guiness','Smirnoff','Vat 69','Tanqueray']})
df2 = pd.DataFrame(data={'Product':['J.Walker Blue Label 12 CC','J.Morgan Blue Walker','Giness blue 150 CC','tqry qiuyur qtre','v69 g nesscom ui123']})
Guiness_Beer = ["Giness","Guiness","Gines"]
Johnny_Walker = ["J.Walker","J.walker"]
Tanqueray =["tqry","Tanqueray","tquery"]
Vat = ["69","Vat69","Vat 69"]
matched_names = []
for row in df1.index:
brand_name = df2.get_value(row,"Product")
Rule_Guiness = any(word in brand_name for word in Guiness_Beer)
Rule_Johnny_Walker = any(word in brand_name for word in Johnny_Walker)
Rule_Tanqueray = any(word in brand_name for word in Tanqueray)
Rule_Vat = any(word in brand_name for word in Vat)
if Rule_Guiness:
matched_names.append([brand_name,"Guiness"])
elif Rule_Johnny_Walker:
matched_names.append([brand_name,"Johnny Walker"])
elif Rule_Tanqueray:
matched_names.append([brand_name,"Tanqueray"])
elif Rule_Vat:
matched_names.append([brand_name,"Vat 69"])
else:
matched_names.append([brand_name,"None"])
df = pd.DataFrame(columns=['Product', 'Brand'], data=matched_names)
您可以对此进行更多修改,例如可以通过 excel 配置 Guiness_beer 之类的所有词典,并且以后不必触摸代码您要添加/减去/修改任何关键字.
You can do more modifications in this like all the dictionaries like Guiness_beer etc. can be configured through excel and you don't have to touch the code if in future you want to add/subtract/modify any keyword.
这篇关于2系列/df.列之间的模糊查找的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!