Можно ли сделать слияние нечетких совпадений с питонами пандами?
У меня есть два DataFrames, которые я хочу объединить на основе столбца. Однако из-за альтернативного написания, различного числа пробелов, отсутствия / наличия диакритических знаков я хотел бы иметь возможность объединяться, если они похожи друг на друга.
Подойдет любой алгоритм подобия (soundex, Levenshtein, difflib's).
Скажем, один 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
16 ответов
Подобно предложению @locojay, вы можете подать заявку difflib "s 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)
С помощью 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):
'''
df_1 is the left table to join
df_2 is the right table to join
key1 is the key column of the left table
key2 is the key column of the right table
threshold is how close the matches should be to return a match
limit is the amount of matches will get returned, these are sorted high to low
'''
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
Я написал пакет Python, который призван решить эту проблему:
pip install fuzzymatcher
Вы можете найти репо здесь и документы здесь.
Основное использование:
Учитывая два кадра данных df_left а также df_right, к которой вы хотите присоединиться, вы можете написать следующее:
from fuzzymatcher import link_table, 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)
Я бы использовал Jaro-Winkler, потому что это один из наиболее эффективных и точных алгоритмов приблизительного сопоставления строк, доступных в настоящее время [ Cohen, et al. ], [ Винклер].
Вот как я бы сделал это с 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
Для общего подхода: fuzzy_merge
Для более общего сценария, в котором мы хотим объединить столбцы из двух фреймов данных, которые содержат немного разные строки, следующая функция использует difflib.get_close_matches вместе с merge чтобы имитировать функциональность панд 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 x
Вот несколько вариантов использования двух образцов данных:
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
Также обратите внимание, что 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 только если он действительно содержит какие-либо совпадения.
http://pandas.pydata.org/pandas-docs/dev/merging.html не имеет функции подключения, чтобы сделать это на лету. Было бы хорошо, хотя...
Я просто сделал бы отдельный шаг и использовал бы difflib getclosest_matches, чтобы создать новый столбец в одном из 2 кадров данных и объединить / объединить столбец с нечетким соответствием
Есть пакет под названием 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
Я использовал пакет 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)
Ошибки, которые вы можете получить
- ZeroDivisionError: деление с плавающей запятой на ноль ---> Чтобы решить эту проблему, перейдите по этой ссылке
- OperationalError: нет такого модуля:fts4 -> загрузите sqlite3.dll отсюда и замените файл DLL в папке с библиотеками DLL python или anaconda.
Плюсы:
- Работает быстрее. В моем случае я сравнил один фрейм данных с 3000 строками с другим фреймом данных с 170000 записей. Также используется поиск SQLite3 по тексту. Так быстрее многих
- Может проверять несколько столбцов и 2 фрейма данных. В моем случае я искал наиболее близкое совпадение по адресу и названию компании. Иногда название компании может быть таким же, но адрес тоже стоит проверить.
- Дает вам очки для всех ближайших матчей для одной и той же записи. вы выбираете, какой счет отсечения.
минусы:
- При установке исходного пакета возникают ошибки
- Также установлены обязательные C++ и визуальные студии
- Не работает для 64-битной анаконды /Python
С использованием
Используя отличный пакет SeatGeek, который использует расстояние Левенштейна. Это работает с данными, хранящимися в столбцах. Он добавляет совпадения в виде строк, а не столбцов, чтобы сохранить аккуратный набор данных, и позволяет легко добавлять дополнительные столбцы в выходной фрейм данных.
Образец данных
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гораздо быстрее, если вы дополнительно установите
python-Levenshteinпакет тоже.
Вы можете использовать d6tjoin для этого
import d6tjocin.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 examples - Блокнот с лучшими совпадениями
- Примеры PreJoin - Примеры диагностики проблем соединения
Как правило, это работает в основном, за исключением случаев, когда совпадений не найдено или если в каждом столбце есть 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
я использовал
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)
Для более сложных случаев использования для сопоставления строк со многими столбцами вы можете использовать
recordlinkage пакет.
recordlinkage предоставляет все инструменты для нечеткого соответствия строк между
pandasфреймы данных, которые помогают дедуплицировать ваши данные при слиянии. Я написал подробную статью о пакете здесь
если ось соединения является числовой, это также можно использовать для сопоставления индексов с заданным допуском:
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
TheFuzz — это новая версия fuzzywuzzy .
Чтобы выполнить нечеткое соединение строковых элементов в двух больших таблицах, вы можете сделать это:
- Используйте применить для перехода по строке
- Используйте быстрее для распараллеливания, ускорения и визуализации функции применения по умолчанию (с цветным индикатором выполнения)
- Используйте OrderedDict из коллекций, чтобы избавиться от дубликатов в выводе слияния и сохранить первоначальный порядок
- Увеличьте лимит , чтобы увидеть больше вариантов слияния (хранится в списке кортежей с % сходства)
'*' Вы можете использоватьthefuzz.process.extractOneвместоthefuzz.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