【Coding】Pandas实现VLOOKUP模糊搜索匹配

【Coding】Pandas实现VLOOKUP模糊搜索匹配

1. 问题描述

如果想利用pandas在python中实现Series文本中的模糊匹配而非精准匹配，可以利用fuzzy实现。

2. 数据样例

df1 = pd.DataFrame([['Apple','A'],['Banana','B'],['Orange','C'],['Strawberry','D'],['Mango','G']], columns=['Fruits','AA'])
df2 = pd.DataFrame([['Aple','a'],['Bannanna','b'],['Orag','c'],['Strawb','d']], columns=['Fruits','aa'])

#df1
       Fruits AA
0       Apple  A
1      Banana  B
2      Orange  C
3  Strawberry  D
4       Mango  G
#df2
     Fruits aa
0      Aple  a
1  Bannanna  b
2      Orag  c
3    Strawb  d

3. FuzzyMatching函数

# pip install fuzzywuzzy
# pip install python-Levenshtein
import pandas as pd
from fuzzywuzzy import fuzz
from fuzzywuzzy import process

def fuzzy_merge(df_1, df_2, key1, key2, threshold=90, limit=2):
    """
    :param df_1: the left table to join
    :param df_2: the right table to join
    :param key1: key column of the left table
    :param key2: key column of the right table
    :param threshold: how close the matches should be to return a match, based on Levenshtein distance
    :param limit: the amount of matches that will get returned, these are sorted high to low
    :return: dataframe with boths keys and matches
    """
    s = df_2[key2].tolist()
    m = df_1[key1].apply(lambda x: process.extract(x, s, limit=limit))
    df_1['matches'] = m
    m2 = df_1['matches'].apply(lambda x: ', '.join([i[0] for i in x if i[1] >= threshold]))
    df_1['matches'] = m2
    df_1['merge_key'] = df_1['matches']
    df_2['merge_key'] = df_2[key2]
    df = pd.merge(df_1, df_2, how='left',on='merge_key')
    return df

4. 模糊搜索匹配

df = fuzzy_merge(df1, df2, 'Fruits', 'Fruits', 90)
df.drop(['matches','merge_key'],axis=1,inplace=True)
#df
     Fruits_x AA  Fruits_y   aa
0       Apple  A      Aple    a
1      Banana  B  Bannanna    b
2      Orange  C      Orag    c
3  Strawberry  D    Strawb    d
4       Mango  G       NaN  NaN

参考资料