问题描述：

我有两个 DataFrame，我想根据列合并它们。但是，由于拼写不同、空格数不同、变音符号存在/不存在，我希望只要它们彼此相似就可以合并。

任何相似性算法都可以（soundex、Levenshtein、difflib）。

假设一个 DataFrame 包含以下数据：

df1 = DataFrame([[1],[2],[3],[4],[5]], index=['one','two','three','four','five'], columns=['number'])

       number
one         1
two         2
three       3
four        4
five        5

df2 = DataFrame([['a'],['b'],['c'],['d'],['e']], index=['one','too','three','fours','five'], columns=['letter'])

      letter
one        a
too        b
three      c
fours      d
five       e

然后我想得到结果 DataFrame

       number letter
one         1      a
two         2      b
three       3      c
four        4      d
five        5      e

解决方案 1：

与@locojay 建议类似，您可以将 应用于difflib的get_close_matches索引df2，然后应用join：

In [23]: import difflib 

In [24]: difflib.get_close_matches
Out[24]: <function difflib.get_close_matches>

In [25]: df2.index = df2.index.map(lambda x: difflib.get_close_matches(x, df1.index)[0])

In [26]: df2
Out[26]: 
      letter
one        a
two        b
three      c
four       d
five       e

In [31]: df1.join(df2)
Out[31]: 
       number letter
one         1      a
two         2      b
three       3      c
four        4      d
five        5      e

。

如果这些是列，那么您可以按照相同的方式应用于该列merge：

df1 = DataFrame([[1,'one'],[2,'two'],[3,'three'],[4,'four'],[5,'five']], columns=['number', 'name'])
df2 = DataFrame([['a','one'],['b','too'],['c','three'],['d','fours'],['e','five']], columns=['letter', 'name'])

df2['name'] = df2['name'].apply(lambda x: difflib.get_close_matches(x, df1['name'])[0])
df1.merge(df2)

解决方案 2：

使用fuzzywuzzy

由于该包中没有示例fuzzywuzzy，下面是我编写的一个函数，它将根据您作为用户设置的阈值返回所有匹配项：

数据框示例

df1 = pd.DataFrame({'Key':['Apple', 'Banana', 'Orange', 'Strawberry']})
df2 = pd.DataFrame({'Key':['Aple', 'Mango', 'Orag', 'Straw', 'Bannanna', 'Berry']})

# df1
          Key
0       Apple
1      Banana
2      Orange
3  Strawberry

# df2
        Key
0      Aple
1     Mango
2      Orag
3     Straw
4  Bannanna
5     Berry

模糊匹配函数

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
    
    return df_1

在数据框上使用我们的函数： #1

from fuzzywuzzy import fuzz
from fuzzywuzzy import process

fuzzy_merge(df1, df2, 'Key', 'Key', threshold=80)

          Key       matches
0       Apple          Aple
1      Banana      Bannanna
2      Orange          Orag
3  Strawberry  Straw, Berry

在数据框上使用我们的函数： #2

df1 = pd.DataFrame({'Col1':['Microsoft', 'Google', 'Amazon', 'IBM']})
df2 = pd.DataFrame({'Col2':['Mcrsoft', 'gogle', 'Amason', 'BIM']})

fuzzy_merge(df1, df2, 'Col1', 'Col2', 80)

        Col1  matches
0  Microsoft  Mcrsoft
1     Google    gogle
2     Amazon   Amason
3        IBM         

安装：

点数

pip install fuzzywuzzy

蟒蛇

conda install -c conda-forge fuzzywuzzy

解决方案 3：

我编写了一个 Python 包来解决这个问题：

pip install fuzzymatcher

您可以在此处找到 repo ，并在此处找到文档。

基本用法：

给定两个想要模糊连接的数据框df_left和，可以编写以下内容：df_right

from fuzzymatcher import link_table, fuzzy_left_join

# Columns to match on from df_left
left_on = ["fname", "mname", "lname",  "dob"]

# Columns to match on from df_right
right_on = ["name", "middlename", "surname", "date"]

# The link table potentially contains several matches for each record
fuzzymatcher.link_table(df_left, df_right, left_on, right_on)

或者如果你只想链接到最接近的匹配：

fuzzymatcher.fuzzy_left_join(df_left, df_right, left_on, right_on)

解决方案 4：

我会使用 Jaro-Winkler，因为它是目前性能最高、最准确的近似字符串匹配算法之一 [ Cohen, et al. ], [ Winkler ]。

以下是我使用水母包中的 Jaro-Winkler 进行操作的方法：

def get_closest_match(x, list_strings):

  best_match = None
  highest_jw = 0

  for current_string in list_strings:
    current_score = jellyfish.jaro_winkler(x, current_string)

    if(current_score > highest_jw):
      highest_jw = current_score
      best_match = current_string

  return best_match

df1 = pandas.DataFrame([[1],[2],[3],[4],[5]], index=['one','two','three','four','five'], columns=['number'])
df2 = pandas.DataFrame([['a'],['b'],['c'],['d'],['e']], index=['one','too','three','fours','five'], columns=['letter'])

df2.index = df2.index.map(lambda x: get_closest_match(x, df1.index))

df1.join(df2)

输出：

    number  letter
one     1   a
two     2   b
three   3   c
four    4   d
five    5   e

解决方案 5：

对于一般方法：fuzzy_merge

对于更一般的情况，我们想要合并两个包含略微不同的字符串的数据框中的列，以下函数使用difflib.get_close_matches以及merge来模仿 pandas 的功能，merge但具有模糊匹配：

import difflib 

def fuzzy_merge(df1, df2, left_on, right_on, how='inner', cutoff=0.6):
    df_other= df2.copy()
    df_other[left_on] = [get_closest_match(x, df1[left_on], cutoff) 
                         for x in df_other[right_on]]
    return df1.merge(df_other, on=left_on, how=how)

def get_closest_match(x, other, cutoff):
    matches = difflib.get_close_matches(x, other, cutoff=cutoff)
    return matches[0] if matches else None

以下是两个示例数据框的一些用例：

print(df1)

     key   number
0    one       1
1    two       2
2  three       3
3   four       4
4   five       5

print(df2)

                 key_close  letter
0                    three      c
1                      one      a
2                      too      b
3                    fours      d
4  a very different string      e

通过上面的例子，我们可以得到：

fuzzy_merge(df1, df2, left_on='key', right_on='key_close')

     key  number key_close letter
0    one       1       one      a
1    two       2       too      b
2  three       3     three      c
3   four       4     fours      d

我们可以使用以下方法进行左连接：

fuzzy_merge(df1, df2, left_on='key', right_on='key_close', how='left')

     key  number key_close letter
0    one       1       one      a
1    two       2       too      b
2  three       3     three      c
3   four       4     fours      d
4   five       5       NaN    NaN

对于右连接，我们将左数据框中所有不匹配的键都设置为None：

fuzzy_merge(df1, df2, left_on='key', right_on='key_close', how='right')

     key  number                key_close letter
0    one     1.0                      one      a
1    two     2.0                      too      b
2  three     3.0                    three      c
3   four     4.0                    fours      d
4   None     NaN  a very different string      e

另请注意，如果截止范围内没有匹配的项目，将返回一个空列表。在共享示例中，如果我们将最后一个索引更改为：difflib.get_close_matches df2

print(df2)

                          letter
one                          a
too                          b
three                        c
fours                        d
a very different string      e

我们会收到一个index out of range错误：

df2.index.map(lambda x: difflib.get_close_matches(x, df1.index)[0])

IndexError：列表索引超出范围

为了解决这个问题，上述函数get_closest_match将通过索引返回的列表（difflib.get_close_matches 只有当它实际包含任何匹配时）返回最接近的匹配。

解决方案 6：

http://pandas.pydata.org/pandas-docs/dev/merging.html没有钩子函数可以动态执行此操作。不过会很好...

我只需执行一个单独的步骤并使用 difflib getclosest_matches 在两个数据框之一中创建一个新列，然后在模糊匹配的列上进行合并/连接

解决方案 7：

有一个名为的包可以fuzzy_pandas使用levenshtein、jaro和方法。这里有一些很好的例子metaphone`bilenco`

import pandas as pd
import fuzzy_pandas as fpd

df1 = pd.DataFrame({'Key':['Apple', 'Banana', 'Orange', 'Strawberry']})
df2 = pd.DataFrame({'Key':['Aple', 'Mango', 'Orag', 'Straw', 'Bannanna', 'Berry']})

results = fpd.fuzzy_merge(df1, df2,
            left_on='Key',
            right_on='Key',
            method='levenshtein',
            threshold=0.6)

results.head()

  Key    Key
0 Apple  Aple
1 Banana Bannanna
2 Orange Orag

解决方案 8：

我使用了 Fuzzymatcher 包，效果很好。请访问此链接了解更多详细信息。

使用以下命令安装

pip install fuzzymatcher

以下是示例代码（已由上面的 RobinL 提交）

from fuzzymatcher import link_table, fuzzy_left_join

# Columns to match on from df_left
left_on = ["fname", "mname", "lname",  "dob"]

# Columns to match on from df_right
right_on = ["name", "middlename", "surname", "date"]

# The link table potentially contains several matches for each record
fuzzymatcher.link_table(df_left, df_right, left_on, right_on)

你可能会遇到的错误

1. ZeroDivisionError：浮点数除以零--->请参阅此
   链接来解决它

2. OperationalError：没有这样的模块：fts4 -->从这里下载 sqlite3.dll并替换你的 python 或 anaconda DLLs 文件夹中的 DLL 文件。

优点：

1. 运行速度更快。 在我的例子中，我将一个包含 3000 行的数据框与另一个包含 170,000 条记录的数据框进行了比较。这也使用 SQLite3 文本搜索。因此比许多

2. 可以跨多个列和 2 个数据框进行检查。就我而言，我正在寻找基于地址和公司名称的最接近匹配项。有时，公司名称可能相同，但地址也是值得检查的。

3. 为同一记录的所有最接近的匹配项提供分数。您可以选择截止分数。

缺点：

1. 原始软件包安装存在缺陷

2. 还需要安装 C++ 和 Visual Studio

3. 不适用于 64 位 anaconda/Python

解决方案 9：

使用thefuzz

使用 SeatGeek 的优秀软件包thefuzz，它利用了 Levenshtein 距离。这适用于以列保存的数据。它将匹配项添加为行而不是列，以保留整洁的数据集，并允许将其他列轻松拉到输出数据框中。

示例数据

df1 = pd.DataFrame({'col_a':['one','two','three','four','five'], 'col_b':[1, 2, 3, 4, 5]})

    col_a   col_b
0   one     1
1   two     2
2   three   3
3   four    4
4   five    5

df2 = pd.DataFrame({'col_a':['one','too','three','fours','five'], 'col_b':['a','b','c','d','e']})

    col_a   col_b
0   one     a
1   too     b
2   three   c
3   fours   d
4   five    e

用于进行匹配的函数

def fuzzy_match(
    df_left, df_right, column_left, column_right, threshold=90, limit=1
):
    # Create a series
    series_matches = df_left[column_left].apply(
        lambda x: process.extract(x, df_right[column_right], limit=limit)            # Creates a series with id from df_left and column name _column_left_, with _limit_ matches per item
    )

    # Convert matches to a tidy dataframe
    df_matches = series_matches.to_frame()
    df_matches = df_matches.explode(column_left)     # Convert list of matches to rows
    df_matches[
        ['match_string', 'match_score', 'df_right_id']
    ] = pd.DataFrame(df_matches[column_left].tolist(), index=df_matches.index)       # Convert match tuple to columns
    df_matches.drop(column_left, axis=1, inplace=True)      # Drop column of match tuples

    # Reset index, as in creating a tidy dataframe we've introduced multiple rows per id, so that no longer functions well as the index
    if df_matches.index.name:
        index_name = df_matches.index.name     # Stash index name
    else:
        index_name = 'index'        # Default used by pandas
    df_matches.reset_index(inplace=True)
    df_matches.rename(columns={index_name: 'df_left_id'}, inplace=True)       # The previous index has now become a column: rename for ease of reference

    # Drop matches below threshold
    df_matches.drop(
        df_matches.loc[df_matches['match_score'] < threshold].index,
        inplace=True
    )

    return df_matches

使用函数并合并数据

import pandas as pd
from thefuzz import process

df_matches = fuzzy_match(
    df1,
    df2,
    'col_a',
    'col_a',
    threshold=60,
    limit=1
)

df_output = df1.merge(
    df_matches,
    how='left',
    left_index=True,
    right_on='df_left_id'
).merge(
    df2,
    how='left',
    left_on='df_right_id',
    right_index=True,
    suffixes=['_df1', '_df2']
)

df_output.set_index('df_left_id', inplace=True)       # For some reason the first merge operation wrecks the dataframe's index. Recreated from the value we have in the matches lookup table

df_output = df_output[['col_a_df1', 'col_b_df1', 'col_b_df2']]      # Drop columns used in the matching
df_output.index.name = 'id'

id  col_a_df1   col_b_df1   col_b_df2
0   one         1           a
1   two         2           b
2   three       3           c
3   four        4           d
4   five        5           e

解决方案 10：

需要注意的是，这基本上是可行的，除非未找到匹配项，或者任一列中都有 NaN。get_close_matches我发现，与直接应用相比，应用以下函数更容易。NaN 替换的选择在很大程度上取决于您的数据集。

def fuzzy_match(a, b):
    left = '1' if pd.isnull(a) else a
    right = b.fillna('2')
    out = difflib.get_close_matches(left, right)
    return out[0] if out else np.NaN

解决方案 11：

你可以使用d6tjoin

import d6tjoin.top1
d6tjoin.top1.MergeTop1(df1.reset_index(),df2.reset_index(),
       fuzzy_left_on=['index'],fuzzy_right_on=['index']).merge()['merged']

`index number index_right letter
0 one 1 one a
1 two 2 too b
2 three 3 three c
3 four 4 fours d
4 five 5 five e`

它具有多种附加功能，例如：

• 检查连接质量，连接前和连接后

• 自定义相似度函数，例如编辑距离与汉明距离

• 指定最大距离

• 多核计算

有关详细信息，请参阅

• MergeTop1 示例- 最佳匹配连接示例笔记本

• PreJoin 示例- 诊断连接问题的示例

解决方案 12：

我已经fuzzywuzz以极简的方式使用了，同时匹配现有的行为和merge关键字pandas。

只需指定您接受的threshold匹配（在0和之间100）：

from fuzzywuzzy import process

def fuzzy_merge(df, df2, on=None, left_on=None, right_on=None, how='inner', threshold=80):
    
    def fuzzy_apply(x, df, column, threshold=threshold):
        if type(x)!=str:
            return None
        
        match, score, *_ = process.extract(x, df[column], limit=1)[0]
            
        if score >= threshold:
            return match

        else:
            return None
    
    if on is not None:
        left_on = on
        right_on = on

    # create temp column as the best fuzzy match (or None!)
    df2['tmp'] = df2[right_on].apply(
        fuzzy_apply, 
        df=df, 
        column=left_on, 
        threshold=threshold
    )

    merged_df = df.merge(df2, how=how, left_on=left_on, right_on='tmp')
    
    del merged_df['tmp']
    
    return merged_df

使用示例数据尝试一下：

df1 = pd.DataFrame({'Key':['Apple', 'Banana', 'Orange', 'Strawberry']})

df2 = pd.DataFrame({'Key':['Aple', 'Mango', 'Orag', 'Straw', 'Bannanna', 'Berry']})

fuzzy_merge(df, df2, on='Key', threshold=80)

解决方案 13：

对于更复杂的用例，可以使用recordlinkage包来匹配具有多列的行。recordlinkage提供所有工具来模糊匹配数据框之间的行，这有助于在合并时对数据进行重复数据删除。我在这里pandas写了一篇关于这个包的详细文章

解决方案 14：

如果连接轴是数字，这也可以用于匹配具有指定容差的索引：

def fuzzy_left_join(df1, df2, tol=None):
    index1 = df1.index.values
    index2 = df2.index.values

    diff = np.abs(index1.reshape((-1, 1)) - index2)
    mask_j = np.argmin(diff, axis=1)  # min. of each column
    mask_i = np.arange(mask_j.shape[0])

    df1_ = df1.iloc[mask_i]
    df2_ = df2.iloc[mask_j]

    if tol is not None:
        mask = np.abs(df2_.index.values - df1_.index.values) <= tol
        df1_ = df1_.loc[mask]
        df2_ = df2_.loc[mask]

    df2_.index = df1_.index

    out = pd.concat([df1_, df2_], axis=1)
    return out

解决方案 15：

TheFuzz是fuzzywuzzy的新版本

为了模糊连接两个大表中的字符串元素，您可以执行以下操作：

1. 使用 apply 逐行执行

2. 使用 Swifter 进行并行、加速和可视化默认应用功能（带彩色进度条）

3. 使用集合中的 OrderedDict 来删除合并输出中的重复项并保持初始顺序

4. 增加限制以thefuzz.process.extract查看更多合并选项（存储在具有相似度百分比的元组列表中）

您可以使用 '*'thefuzz.process.extractOne而不是thefuzz.process.extract来返回一个最匹配的项目（不指定任何限制）。但是，请注意，多个结果可能具有相同的相似度百分比，您只会得到其中一个。

'*' Swifter 需要一两分钟才能开始实际应用。如果您需要处理小型表格，则可以跳过此步骤，而只需使用Progress_apply*

from thefuzz import process
from collections import OrderedDict
import swifter    


def match(x):
    matches = process.extract(x, df1, limit=6)
    matches = list(OrderedDict((x, True) for x in matches).keys())
    print(f'{x:20} : {matches}')

    return str(matches)


df1 = df['name'].values
df2['matches'] = df2['name'].swifter.apply(lambda x: match(x))

解决方案 16：

我发现这个非常有效。函数描述中的详细信息：

from fuzzywuzzy.process import extract

def efficient_matching(df1,
                       col1,
                       df2,
                       col2,
                       limit=3,
                       length_diff=3,
                       first_letter_match=2
                       ):
    """
    For each name that we want to find matches for, it's more efficient to only look at a subset of potential matches. 
    One way to narrow down all the matches to potential matches is length. Here are 2 methods:
    1. If the name is "Markos", we don't need to check how similar markos is to names with length less than 4 or 
    more than 8. This window is determined by length_diff.
    2. We consider names from the corpus whose first 2 letters are similar the first letters of the name we want to find
    the match for.

    limit: Gives how many closest matches to return.
    """
    df1[col1] = df1[col1].astype(str)
    df2[col2] = df2[col2].astype(str)

    df1['_len_'] = df1[col1].apply(len)
    df2['_len_'] = df2[col2].apply(len)
    df2 = df2[df2['_len_'] >= 2]

    matches = df1[[col1, '_len_']].apply(lambda x: 
                                        extract(x[0], 
                                                df2[
                                                    ((df2['_len_'] - x[1]).abs() < length_diff) &
                                                    (df2[col2].str[:first_letter_match]==x[0][:first_letter_match])
                                                    ][col2].tolist(), 
                                                limit = limit
                                                ), 
                                        axis=1
                                        ) 
    return matches