Автор: Крис Моффит
Перевод: Лао Ци
Рекомендации по книгам, связанным с этой статьей: "Подготовка данных и проектирование признаков"
введение
Слияние наборов данных — обычная операция в науке о данных. Для двух наборов данных с общим идентификатором вы можете объединить их, используя обычные методы, предусмотренные в Pandas, но как объединить два набора данных, если у них нет общего уникального идентификатора? Именно этот вопрос предстоит рассмотреть в данной статье. Для этого часто используются два термина: объединение записей и нечеткое сопоставление, например, попытки объединить разные файлы данных на основе имен людей или объединить данные, которые содержат только названия и адреса организаций и т. д., используйте «соединения записей». и выполнено "нечеткое сопоставление".
合并没有共同特征的数据，是比较常见且具有挑战性的业务，很难系统地解决，特别是当数据集很大时。如果用人工的方式，使用Excel和查询语句等简单方法能够实现，但这无疑要有很大的工作量。 Как решить? Python此时必须登场。 Python中有两个库，它们能轻而易举地解决这种问题，并且可以用相对简单的API支持复杂的匹配算法。
Первая библиотека называется fuzzymatcher и использует простой интерфейс для соединения двух записей DataFrame на основе их вероятностей, а вторая библиотека называется набором инструментов RecordLinkage и предоставляет мощный набор инструментов для автоматического подключения журнала и дедупликации данных.
В этой статье мы узнаем, как использовать эти два инструмента (или две библиотеки) для сопоставления двух разных наборов данных, а именно наборов данных, основанных на информации об имени и адресе. Кроме того, мы кратко узнаем, как эти методы сопоставления можно использовать для дедупликации данных.
проблема
Любой может столкнуться с подобными проблемами, просто пытаясь объединить разрозненные наборы данных. В простом примере ниже в системе есть запись о клиенте, и нам нужно определить, совпадают ли данные без использования общедоступного идентификатора. (Две записи, отмеченные стрелками на рисунке ниже, являются объектами для сопоставления, и у них нет общего идентификатора.)
Основываясь на небольшом выборочном наборе данных и нашей интуиции, две записи с номером записи 18763 и записью номер A1278 выглядят одинаково. Мы знаем, что Brothers, Bro, Lane и LN эквивалентны, поэтому для людей этот процесс относительно прост. Однако попытка справиться с этим с помощью логики в программировании — непростая задача.
По моему опыту, большинство людей подумали бы об использовании Excel, просмотре различных компонентов адреса и поиске наилучшего совпадения на основе штата, номера улицы или почтового индекса. В некоторых случаях это работает. Однако мы можем захотеть использовать более сложный метод сравнения строк, для которого несколько лет назад я написал пакет под названием fuzzywuzzy.
Проблема заключается в том, что эти алгоритмы (например, Левенштейна, Дамерау-Левенштейна, Яро-Винклера, q-gram, косинус) требуют больших вычислительных ресурсов и не могут масштабироваться при большом количестве совпадений в больших наборах данных.
Если вам интересно узнать больше математических подробностей об этих концепциях, вы можете проверить их в Википедии, которая также рассматривается в этой статье. Наконец, в этой статье более подробно обсуждаются методы сопоставления строк.
К счастью, есть некоторые инструменты Python, которые могут помочь нам реализовать эти методы и решить некоторые из этих сложных проблем.
данные
В этой статье мы будем использовать данные больниц США. Этот набор данных был выбран, потому что данные больницы имеют некоторую уникальность, из-за которой их трудно сопоставить:
- Многие больницы имеют схожие названия в разных городах (Сент-Лукс, Сент-Мэри, Комьюнити-больница, которая очень похожа на «Юнион-госпиталь» во многих городах нашей страны)
- В пределах города больница может занимать несколько кварталов, поэтому адрес может быть неоднозначным.
- Рядом с больницей часто находится много клиник и других сопутствующих учреждений.
- Больницы также приобретаются, и часто меняются названия, что усложняет процесс обработки данных.
- Наконец, в США тысячи медучреждений, поэтому этот вопрос сложно масштабировать.
В этих примерах у меня есть два набора данных. Первый — это внутренний набор данных, который содержит основные номера больничных счетов, имена и информацию о владельцах.
Второй набор данных содержит информацию о больницах (с функциями Provider), а также данные о выписке из больницы и стоимости медицинского страхования для определенных процедур по поводу сердечной недостаточности.
Приведенные выше наборы данных взяты с сайтов Medicare.gov и CMS.gov и прошли простую очистку данных.
Проект в этой статье был опубликован на экспериментальной онлайн-платформе. Пожалуйста, подпишитесь на общедоступную учетную запись WeChat «Laoqi Classroom» и ответьте: #Имя+Номермобильного+Дело#. Обратите внимание, что символ # не должен теряться, иначе невозможно будет найти ответную информацию.
Наш бизнес-сценарий: теперь у нас есть данные о возмещении больничных расходов и данные внутреннего счета, и нам нужно сопоставить их, чтобы анализировать пациентов в каждой больнице на большем количестве уровней. В этом примере у нас есть 5339 больничных счетов и информация о возмещении расходов для 2697 больниц. Однако для двух типов наборов данных нет общего идентификатора, поэтому мы посмотрим, сможем ли мы использовать упомянутый ранее инструмент для объединения двух наборов данных на основе информации о названии и адресе больницы.
Метод 1: пакет Fuzzymather
В первом подходе мы попробуем использовать пакет fuzzymatcher, который использует возможности полнотекстового поиска sqlite для сопоставления записей в двух разных кадрах данных.
Установить fuzzymatcher легко, если вы установите его с помощью conda, зависимости будут автоматически обнаружены для установки, или вы можете использовать pip для установки fuzzymatcher. Учитывая вычислительную нагрузку этих алгоритмов, вы захотите использовать как можно больше скомпилированных компонентов C, которые можно реализовать с помощью conda.
После того, как все настроено, мы импортируем данные и помещаем их в DataFrames:
import pandas as pd
from pathlib import Path
import fuzzymatcher
hospital_accounts = pd.read_csv('hospital_account_info.csv')
hospital_reimbursement = pd.read_csv('hospital_reimbursement.csv')
Ниже представлена информация об учетной записи больницы:
Here is the reimbursement information:
Вот информация о возмещении:
Поскольку столбцы имеют разные имена, нам нужно определить, какие столбцы соответствуют DataFrame слева и справа, DataFrame слева для информации об учетной записи больницы и DataFrame справа для информации о возмещении.
left_on = ["Facility Name", "Address", "City", "State"]
right_on = [
"Provider Name", "Provider Street Address", "Provider City",
"Provider State"
]
Теперь используйте fuzzymatcher вfuzzy_left_joinФункция находит совпадение:
matched_results = fuzzymatcher.fuzzy_left_join(hospital_accounts,
hospital_reimbursement,
left_on,
right_on,
left_id_col='Account_Num',
right_id_col='Provider_Num')
Незаметно fuzzymatcher определяет наилучшее совпадение для каждой комбинации. Для этого набора данных мы проанализировали более 14 миллионов комбинаций. На моем ноутбуке процесс занял 2 минуты 11 секунд.
Переменнаяmatched_resultsСсылочный объект DataFrame содержит все данные, объединенные вместе иbest_match_score- Данные этой функции используются для оценки качества соответствующего соединения.
Вот подмножество этих столбцов с 5 лучшими совпадениями, переупорядоченными для удобства чтения:
cols = [
"best_match_score", "Facility Name", "Provider Name", "Address", "Provider Street Address",
"Provider City", "City", "Provider State", "State"
]
matched_results[cols].sort_values(by=['best_match_score'], ascending=False).head(5)
Оценка совпадения по первому пункту составляет 3,09, что, безусловно, выглядит как хорошее совпадение. Вы можете видеть, что для клиники Мэйо в Красном крыле функцияFacility NameиProvider Nameв основном одинаковы, и наблюдения подтверждают, что это совпадение хорошо подходит.
Мы также можем видеть, где матчи не работают хорошо:
matched_results[cols].sort_values(by=['best_match_score'], ascending=True).head(5)
Вот некоторые плохие оценки и вопиющие несоответствия:
Этот пример подчеркивает часть проблемы, заключающейся в том, что один набор данных включает данные из Пуэрто-Рико, а другой нет. Это несоответствие ясно показывает, что вам нужно убедиться, что вы действительно понимаете данные и насколько это возможно, прежде чем пытаться сопоставить Clean и фильтровать.
Мы видели несколько крайних случаев. Теперь взгляните на некоторые матчи со счетом менее 0,8, они могут быть более сложными:
matched_results[cols].query("best_match_score <= .80").sort_values(
by=['best_match_score'], ascending=False).head(5)
Приведенный выше пример показывает, как некоторые совпадения можно сделать более двусмысленными, например, совпадает ли ADVENTIST HEALTH UKIAH VALLEY с МЕДИЦИНСКИМ ЦЕНТРОМ UKIAH VALLEY? В зависимости от вашего набора данных и потребностей вам необходимо найти правильный баланс между автоматической и ручной проверкой совпадений.
В целом, fuzzymatcher — полезный инструмент для наборов данных среднего размера. Если размер выборки превышает 10 000 строк, расчет займет много времени, поэтому вам необходимо хорошо спланировать. Тем не менее, fuzzymatcher работает хорошо, особенно в сочетании с Pandas, что делает его отличным инструментом.
Метод 2: набор инструментов RecordLinkage
Инструментарий RecordLinkage предоставляет еще один мощный набор инструментов для соединения записей в наборах данных и выявления повторяющихся записей в данных.
Его основные функции заключаются в следующем:
- Возможность определить типы сопоставления для каждого столбца на основе типа данных столбца.
- Используйте «блоки», чтобы ограничить пул потенциальных совпадений
- Используйте алгоритм подсчета очков, чтобы обеспечить ранжирование совпадений
- Различные алгоритмы измерения сходства строк
- Контролируемые и неконтролируемые методы обучения
- Несколько методов очистки данных
Компромисс заключается в том, что эти операции слишком сложны, чтобы управлять этими результатами данных только для дальнейшей проверки. Однако все эти шаги выполняются стандартными командами Panda, так что не бойтесь.
все еще можно использоватьpipдля установки библиотеки. Мы будем использовать предыдущий набор данных, но установим индекс столбца при чтении данных, что упростит интерпретацию последующих объединений данных.
import pandas as pd
import recordlinkage
hospital_accounts = pd.read_csv('hospital_account_info.csv', index_col='Account_Num')
hospital_reimbursement = pd.read_csv('hospital_reimbursement.csv', index_col='Provider_Num')
Поскольку RecordLinkage имеет больше параметров конфигурации, нам потребуется несколько шагов для определения правил подключения. Первым шагом является созданиеindexerОбъект:
indexer = recordlinkage.Index()
indexer.full()
# 输出
WARNING:recordlinkage:indexing - performance warning - A full index can result in large number of record pairs.
Это предупреждение указывает на разницу между библиотеками ссылок журналов и нечеткими сопоставлениями. С помощью соединений записей мы можем гибко влиять на количество оцениваемых пар записей. вызвать индексный объектfullметод, который может вычислить все возможные пары записей (мы знаем, что количество этих пар записей превышает 14M). Я расскажу о других вариантах чуть позже, но давайте перейдем к полному указателю, чтобы увидеть, как он работает.
Следующим шагом является создание всех потенциальных записей-кандидатов, которые необходимо проверить:
candidates = indexer.index(hospital_accounts, hospital_reimbursement)
print(len(candidates))
# 输出
14399283
Эта быстрая проверка просто подтверждает общее количество сравниваемых записей.
Теперь, когда мы определили левый и правый наборы данных и все наборы данных-кандидатов, мы можем использоватьCompare()Сравнивать.
compare = recordlinkage.Compare()
compare.exact('City', 'Provider City', label='City')
compare.string('Facility Name',
'Provider Name',
threshold=0.85,
label='Hosp_Name')
compare.string('Address',
'Provider Street Address',
method='jarowinkler',
threshold=0.85,
label='Hosp_Address')
features = compare.compute(candidates, hospital_accounts,
hospital_reimbursement)
Выберите несколько функций выше, используйте их, чтобы определить точное соответствие городу, и выполнитеstringВ методе также устанавливается порог. В дополнение к этим необязательным параметрам вы также можете определить другие параметры, такие как числа, даты и географические координаты. Дополнительные примеры см. в документации.
Последний шаг — использоватьcomputeМетод сравнивает все признаки. В этом примере мы использовали полный индекс, что заняло 3 минуты 41 секунду.
Ниже приведена схема оптимизации.Здесь есть важное понятие,то есть блоки.Использование блоков может уменьшить количество сравниваемых записей. Например, если мы хотим сравнить только больницы в одном и том же штате, мы можем использоватьStateБлок создания столбца:
indexer = recordlinkage.Index()
indexer.block(left_on='State', right_on='Provider State')
candidates = indexer.index(hospital_accounts, hospital_reimbursement)
print(len(candidates))
# 输出
475830
в соответствии сStateПри разделении кандидаты будут отфильтрованы, чтобы содержать только те, которые имеют одинаковое значение состояния, и после фильтрации останется только 475 830 записей. Если мы запустим тот же код сравнения, это займет всего 7 секунд. Отличный способ ускориться!
В этом наборе данныхStateДанные чистые, но если они немного запутаны, вы можете использовать другой алгоритм разбиения на фрагменты, напримерSortedNeighborhood, чтобы уменьшить влияние некоторых небольших орфографических ошибок.
Например, что, если название штата содержит «Теннесси» и «Теннесси»? Предыдущий блок недействителен, но его можно использоватьsortedneighbourhoodспособ справиться с этой проблемой.
indexer = recordlinkage.Index()
indexer.sortedneighbourhood(left_on='State', right_on='Provider State')
candidates = indexer.index(hospital_accounts, hospital_reimbursement)
print(len(candidates))
# 输出
998860
Вышеприведенный пример,sortedneighbourhoodОбработка 998 860 записей заняла 15,9 секунды, что кажется разумным.
Независимо от того, какой метод вы используете, ниже показан результат, который представляет собой DataFrame.
Этот кадр данных отображает результаты всех сравнений, по одному результату сравнения на строку в кадрах данных счетов и возмещения. Эти элементы соответствуют определенным нами сравнениям, где 1 соответствует совпадению, а 0 — несоответствию.
Поскольку у большого количества записей нет совпадений, трудно увидеть, сколько совпадений у нас может быть, поэтому вы можете сложить отдельные баллы, чтобы увидеть, насколько хорошо работают совпадения.
features.sum(axis=1).value_counts().sort_index(ascending=False)
# 输出
3.0 2285
2.0 451
1.0 7937
0.0 988187
dtype: int6
Теперь мы знаем, что есть 988187 строк без совпадений, 7937 хотя бы с одним совпадением, 451 с 2 совпадениями и 2285 с 3 совпадениями.
Чтобы упростить дальнейший анализ, давайте возьмем все записи с 2 или 3 совпадениями и добавим общий балл:
potential_matches = features[features.sum(axis=1) > 1].reset_index()
potential_matches['Score'] = potential_matches.loc[:, 'City':'Hosp_Address'].sum(axis=1)
Вот объяснение результата: строка с индексом 1,Account_NumСтоимость 26270,Provider_NumЗначение 868740 показывает, что строка совпадает с точки зрения города, названия больницы и адреса больницы.
Присмотритесь к содержимому этих двух записей:
hospital_accounts.loc[26270,:]
Facility Name SCOTTSDALE OSBORN MEDICAL CENTER
Address 7400 EAST OSBORN ROAD
City SCOTTSDALE
State AZ
ZIP Code 85251
County Name MARICOPA
Phone Number (480) 882-4004
Hospital Type Acute Care Hospitals
Hospital Ownership Proprietary
Name: 26270, dtype: object
hospital_reimbursement.loc[868740,:]
Provider Name SCOTTSDALE OSBORN MEDICAL CENTER
Provider Street Address 7400 EAST OSBORN ROAD
Provider City SCOTTSDALE
Provider State AZ
Provider Zip Code 85251
Total Discharges 62
Average Covered Charges 39572.2
Average Total Payments 6551.47
Average Medicare Payments 5451.89
Name: 868740, dtype: object
да. Кажется, они совпадают.
Теперь, когда мы знаем совпадения, нам нужно скорректировать данные, чтобы упростить проверку всех данных. Я создам запрос имени и адреса для соединения для каждого набора данных.
hospital_accounts['Acct_Name_Lookup'] = hospital_accounts[[
'Facility Name', 'Address', 'City', 'State'
]].apply(lambda x: '_'.join(x), axis=1)
hospital_reimbursement['Reimbursement_Name_Lookup'] = hospital_reimbursement[[
'Provider Name', 'Provider Street Address', 'Provider City',
'Provider State'
]].apply(lambda x: '_'.join(x), axis=1)
account_lookup = hospital_accounts[['Acct_Name_Lookup']].reset_index()
reimbursement_lookup = hospital_reimbursement[['Reimbursement_Name_Lookup']].reset_index()
Теперь объедините данные с информацией об учетной записи:
account_merge = potential_matches.merge(account_lookup, how='left')
Наконец, объедините с данными возмещения:
final_merge = account_merge.merge(reimbursement_lookup, how='left')
Взгляните на окончательные данные:
cols = ['Account_Num', 'Provider_Num', 'Score',
'Acct_Name_Lookup', 'Reimbursement_Name_Lookup']
final_merge[cols].sort_values(by=['Account_Num', 'Score'], ascending=False)
Демонстрируемый здесь метод отличается от fuzzymatcher, который часто содержит несколько результатов сопоставления. Например, учетная запись с номером 32725 может сопоставить два соответствующих элемента:
final_merge[final_merge['Account_Num']==32725][cols]
В этом случае кто-то должен выяснить, какое совпадение является лучшим. К счастью, все данные легко сохранить в Excel для дальнейшего анализа:
final_merge.sort_values(by=['Account_Num', 'Score'],
ascending=False).to_excel('merge_list.xlsx',
index=False)
Как видно из этого примера, набор инструментов RecordLinkage более гибкий и простой в настройке, чем fuzzymatcher. RecordLinkage не идеален, например, для отдельных лиц RecordLinkage необходимо выполнить больше шагов, чтобы завершить сравнение данных.
дедуплицировать данные
Еще одно применение RecordLinkage — поиск повторяющихся записей в наборе данных.Этот процесс очень похож на сопоставление, за исключением того, что вы передаете DataFrame самому себе.
Давайте посмотрим на пример с использованием аналогичного набора данных:
hospital_dupes = pd.read_csv('hospital_account_dupes.csv', index_col='Account_Num')
Затем создайте индексный объект и основывайте его наStateвоплощать в жизньsortedneighbourhood.
dupe_indexer = recordlinkage.Index()
dupe_indexer.sortedneighbourhood(left_on='State')
dupe_candidate_links = dupe_indexer.index(hospital_dupes)
Проверьте наличие повторяющихся записей по городу, имени и адресу:
compare_dupes = recordlinkage.Compare()
compare_dupes.string('City', 'City', threshold=0.85, label='City')
compare_dupes.string('Phone Number',
'Phone Number',
threshold=0.85,
label='Phone_Num')
compare_dupes.string('Facility Name',
'Facility Name',
threshold=0.80,
label='Hosp_Name')
compare_dupes.string('Address',
'Address',
threshold=0.85,
label='Hosp_Address')
dupe_features = compare_dupes.compute(dupe_candidate_links, hospital_dupes)
Поскольку сравнивается только один DataFrame, результирующий DataFrame сAccount_Num_1иAccount_Num_2:
Вот наш метод подсчета очков:
dupe_features.sum(axis=1).value_counts().sort_index(ascending=False)
3.0 7
2.0 206
1.0 7859
0.0 973205
dtype: int64
Добавьте столбец результатов:
potential_dupes = dupe_features[dupe_features.sum(axis=1) > 1].reset_index()
potential_dupes['Score'] = potential_dupes.loc[:, 'City':'Hosp_Address'].sum(axis=1)
Ниже приведен пример:
Есть большая вероятность, что эти записи являются дубликатами, давайте посмотрим на один из наборов, чтобы увидеть, являются ли они одной и той же записью:
hospital_dupes.loc[51567, :]
Facility Name SAINT VINCENT HOSPITAL
Address 835 SOUTH VAN BUREN ST
City GREEN BAY
State WI
ZIP Code 54301
County Name BROWN
Phone Number (920) 433-0112
Hospital Type Acute Care Hospitals
Hospital Ownership Voluntary non-profit - Church
Name: 51567, dtype: object
hospital_dupes.loc[41166, :]
Facility Name ST VINCENT HOSPITAL
Address 835 S VAN BUREN ST
City GREEN BAY
State WI
ZIP Code 54301
County Name BROWN
Phone Number (920) 433-0111
Hospital Type Acute Care Hospitals
Hospital Ownership Voluntary non-profit - Church
Name: 41166, dtype: object
Да, наблюдения показывают, что это, скорее всего, повторяющиеся записи с похожими именами и адресами, а в телефонном номере отсутствует только одна цифра.
Как видите, это мощный и относительно простой инструмент для проверки данных и дублирования записей.
Расширенное использование
В дополнение к методам сопоставления, показанным здесь, RecordLinkage включает несколько методов машинного обучения для сопоставления записей. Я призываю заинтересованных читателей прочитать примеры в документации.
Одна из очень удобных функций: есть браузерный инструмент, который можно использовать для генерации пар записей для алгоритмов машинного обучения.
Оба пакета, описанные в этой статье, содержат некоторые функции для предварительной обработки данных, чтобы сделать сопоставление более надежным.
Суммировать
При обработке данных часто бывает сложно связать текстовые поля, такие как «имя» и «адрес», с разными записями. Экосистема Python содержит две полезные библиотеки, которые сопоставляют записи из нескольких наборов данных с использованием различных алгоритмов.
Fuzzymatcher выполняет полнотекстовый поиск, просто сопоставляя два кадра данных вместе, объединяя записи по вероятности. Если у вас есть большие наборы данных или вам нужно использовать более сложную логику сопоставления, RecordLinkage — это очень мощный набор инструментов для объединения данных и удаления дубликатов.
Оригинальная ссылка:PB Python.com/record — Линкольн…
Найдите общедоступный номер технических вопросов и ответов: класс Лао Ци
Чтобы всем было проще читать и запрашивать ресурсы этой общедоступной учетной записи WeChat, ответьте:Лао Цидля отображения каталога услуг этого официального аккаунта.