Будь Героем рабочего места! Автоматизируйте надоедливую задачу очистки орфографии и форматирования категориальных текстовых столбцов с помощью нечеткого соответствия.

Около десяти лет назад у друга семьи возникла интересная проблема. Он был судебно-медицинским экспертом и отвечал за очистку таблиц с данными для отчетов. Он хотел агрегировать статистику по ключевым столбцам, таким как первичные и вторичные причины смерти. Проблема, с которой он столкнулся, заключалась в том, что эти записи были набраны вручную, а вариации в формате и орфографии вызвали у него крайнюю трудность. Каждый месяц он тратил часы на очистку всех этих наборов данных. Не тот, кто зря тратит время, он хотел решение.

В то время я знал только о классификации, основанной на правилах. В наши дни я легко могу порекомендовать разные методы решения этой проблемы. С помощью всего лишь нескольких строк кода и некоторой базовой проверки человеком большая часть этой работы может быть полностью автоматизирована. При правильной настройке несопоставленные поля могут быть помечены для проверки человеком.

Эта проблема

Вместо того, чтобы исследовать такой темный набор данных, сосредоточенный на причинах смерти, давайте рассмотрим гораздо более яркий сценарий. Хранилище данных (в данном случае электронная таблица Excel) содержит текстовое поле, обозначающее праздник в США. Сотрудники вводят это поле вручную и используют различные варианты написания и сокращения. Вы хотите сопоставить эти текстовые значения с основным списком праздников США. Затем вы можете вернуться и обновить источник данных по своему усмотрению.

Данные

Для этого сценария я создал две таблицы. Одна электронная таблица содержит столбец значений праздничных дней, которые ввели сотрудники. Я также включил столбец «Проверка», чтобы я мог проверить результаты на соответствие моим ожидаемым результатам.

Другая таблица содержит главный список праздников США с предпочтительным написанием.

Я загрузил данные и создал как фреймы данных pandas, так и списки Python.

import pandas as pd  
import numpy as np
from platform import python_version
print('python version: ' + python_version())
path = '<path to your files>'
# create dataframes
official_list_df   = pd.read_excel(path + 'holiday official list.xlsx')
hand_typed_list_df = pd.read_excel(path + 'holidays.xlsx')
# create lists
official_list         = official_list_df['Holiday Name'].fillna('*').to_list()
hand_typed_list       = hand_typed_list_df['Holiday'].fillna('*').to_list()
validation_list       = hand_typed_list_df['Validation'].fillna('*').to_list()

Возможные решения

Я собираюсь рассмотреть два пакета Python, fuzzymatcher и fuzzywuzzy. У каждого пакета есть свои преимущества, и тот, который вы выберете, может зависеть от ваших предпочтений.

нечеткое сопоставление

Fuzzymatcher был разработан для сопоставления текстовых столбцов между двумя фреймами данных Pandas, объединенными по крайней мере в одном столбце. Этот простой интерфейс удобен, потому что Pandas - очень популярный в использовании пакет.

Дополнительная информация о fuzzymatcher:

Код был прост - объедините два фрейма данных и определите объединяемые столбцы:

import fuzzymatcher
hand_typed_list_join_on = [‘Holiday’]
official_list_join_on = [‘Holiday Name’]
matched = fuzzymatcher.fuzzy_left_join(hand_typed_list_df,
 official_list_df,
 hand_typed_list_join_on, #left join on
 official_list_join_on, #right join on
 left_id_col=’Holiday’,
 right_id_col=’Holiday Name’)
matched

Результаты смешанные, но работоспособные. Ваш любящий Сайнфельд сотрудник, входящий в Festivus, не был найден правильно. Дни МЛК, ветеранов и президента были самыми проблемными.

Система подсчета очков не интуитивно понятна. На первый взгляд, я бы решил отбросить все совпадения со значениями меньше нуля. По результатам я вижу, что отрицательные значения не являются серьезным препятствием.

Давайте посмотрим на следующий пакет.

fuzzywuzzy (самое интересное название пакета)

Нечеткая информация:

Я добавил дополнительный код для создания красиво отформатированного фрейма выходных данных, который оценивает результаты и уточняет, действует ли соответствие, как ожидалось, на основе набора оценок нижнего предела.

Основываясь на нескольких итерациях, я решил, что набрал более 86 баллов, чтобы принять совпадение.

from fuzzywuzzy import fuzz
from fuzzywuzzy import process
# create a results dataset so we can review
results_df = pd.DataFrame(columns = ['Hand_Typed_Name', 'Matched_Name', 'Match_Score', 'Allow_Flag'
 , 'Validation_value', 'Matched_Flag', 'Success_Code'])
# iterate over each element on the lists
for index, row in hand_typed_list_df.iterrows():
    result = process.extractOne(row.Holiday, official_list)
# does the potential match value equal the validation value?
    if str(result[0]) == row.Validation:
        res = 'Yes'
    else:
        res = 'No'
# does the match score meet the minimum threshold set?
    if result[1] > 86:
        allow = 'Yes'
    else:
        allow = 'No'
# are my match and scoring cutoff flags consistant?
    if res == allow:
        final = 'Passed'
    else:
        final = 'Needs Review'
     
# append the data to the results dataframe  
    new_row = {  'Hand_Typed_Name'   : str(row.Holiday)
               , 'Matched_Name'      : str(result[0])
               , 'Match_Score'       : str(result[1])
               , 'Allow_Flag'        : str(allow)
               , 'Validation_value'  : str(row.Validation)
               , 'Matched_Flag'      : str(res)
               , 'Success_Code'      : str(final)
               }
    results_df = results_df.append(new_row, ignore_index=True)
    
results_df.head(40)

Схема подсчета очков намного проще для понимания, чем fuzzmatcher. Как видно из результатов, это не точная наука относительно порогового значения. Значение MKL (индекс 26) получило низкий балл 57, но совпадение было успешным. С другой стороны, есть три результата из 86. Один успешно прошел, а два других - нет.

Заключение

Использование нечеткого сопоставления для очистки наборов данных - удобный навык на большинстве рабочих мест. Почти всегда есть беспорядочные текстовые поля, которые какой-нибудь бедняга вручную обновляет каждый месяц перед тем, как отчитаться. Создайте автоматизированный процесс, который будет выполнять 80% работы, прежде чем перекладывать на человека работу в крайних случаях. Ты будешь офисным героем!