http://pandas.pydata.org/pandas-docs/dev/merging.html 没有钩子功能来即时执行此操作。不过会很好...

我只会做一个单独的步骤，并使用 difflib getclosest_matches 在 2 个数据帧之一中创建一个新列，并在模糊匹配列上进行合并/连接

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

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

.

如果这些是列，同样，您可以应用于列，然后合并：

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)

请注意，这基本上是有效的，除非没有找到匹配项，或者任一列中都有 NaN。与其直接应用，我发现应用以下功能更容易。NaN 替代品的选择很大程度上取决于您的数据集。get_close_matches

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

我会使用 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

我编写了一个旨在解决这个问题的 Python 包：

pip install fuzzymatcher

您可以在此处找到存储库，在此处找到文档。

基本用法：

给定两个要模糊连接的数据帧和 ，可以编写以下内容：df_leftdf_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)

你可以使用 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 示例 - 最佳匹配联接示例笔记本
• 预联接示例 - 用于诊断联接问题的示例

用fuzzywuzzy

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

---

示例 datframe

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

我使用了 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 --> downlaod sqlite3.dll 从这里替换 python 或 anaconda 中的 DLL 文件 DLL 文件夹。

优点：

1. 工作速度更快。 就我而言，我将一个具有 3000 行的数据帧与具有 170,000 条记录的 anohter dataframe 进行了比较。这也使用跨文本的 SQLite3 搜索。比许多人都快
2. 可以跨多个列和 2 个数据帧进行检查。就我而言，我正在根据地址和公司名称寻找最接近的匹配项。有时，公司名称可能相同，但地址也是检查的好东西。
3. 为同一记录的所有最接近的匹配项提供分数。您可以选择截止分数。

缺点：

1. 原始软件包安装有问题
2. 还安装了所需的 C++ 和可视化工作室
3. 不适用于 64 位 anaconda/Python

有一个名为 的包，可以使用 、 和 方法。这里有一些很好的例子fuzzy_pandaslevenshteinjarometaphonebilenco

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

对于一般方法：fuzzy_merge

对于更一般的场景，我们要合并包含略有不同的字符串的两个数据帧中的列，以下函数使用difflib.get_close_matches和合并来模仿 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_matchdifflib.get_close_matches

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

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

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

我以非常少的方式使用，同时匹配 in 的现有行为和关键字。fuzzywuzzmergepandas

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

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)

用thefuzz

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

---

示例数据

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

TheFuzz 是 fuzzywuzzy 的新版本

为了在两个大表中模糊连接字符串元素，您可以这样做：

1. 使用 apply 逐行执行
2. 使用更快的并行、加速和可视化默认应用功能（带有彩色进度条）
3. 使用集合中的 OrderedDict 去除合并输出中的重复项并保留初始顺序
4. 增加限制以查看更多合并选项（存储在具有相似度百分比的元组列表中）thefuzz.process.extract

“*”可以代替仅返回一个最匹配的项（不指定任何限制）。但是，请注意，多个结果可能具有相同的相似度百分比，并且您只会得到其中一个结果。thefuzz.process.extractOnethefuzz.process.extract

'**' 不知何故，在开始实际应用之前，速度更快需要一两分钟。如果需要处理小表，可以跳过此步骤，改用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))

我发现这非常有效。功能描述中的详细信息：

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