Иногда приходится заниматься сравнением больших списков адресов, в которых адреса записаны совершенно по разному без внятных идентификаторов вроде номера объекта - есть только адрес. Один и тот же адрес может фигурировать в различных списках следующим образом:
"д. Малое Шилово, ул. Березовая, д. 7" и "Березовая 7_М Шилово".
"п. Ласьва, ул. Весенняя, д. 5" и "Весенняя 5_Ласьва".
"Луговой пер 5, Краснокамск г" и "г. Краснокамск, пер. Луговой, 5".
"д. Новая Ивановка, ул. Солнечная, 18" и "д.Новая Ивановка, ул.Солнечная, 18".
Уже выделенные отдельно адреса могут выглядеть как на скриншоте Экселя ниже. А пример поставленной задачи может звучать так: «В реестре поданных объектов отметить все согласованные объекты (из общего списка согласованных)».
Если отбросить вариант ручного исполнения и обратиться к скриптам, то мне видится всего два решения:
Использовать алгоритмы нечёткого сопоставления.
Использовать геокодинг адресов.
Варианты решения этой задачи
Первый вариант – использование алгоритмов нечёткого сопоставления (fuzzy matching). Эти алгоритмы позволяют сравнивать строки, учитывая возможные опечатки, разные порядок слов и сокращения. В нашем случае, алгоритм сможет распознать "д. Малое Шилово, ул. Березовая, д. 7" и "Березовая 7_М Шилово" как варианты одного и того же адреса, несмотря на различия в формате и сокращения. Fuzzy matching оценивает «схожесть» строк, выдавая число от 0 до 1, что позволяет гибко настраивать порог совпадения и находить соответствия даже при значительных расхождениях в написании. Это делает данный метод весьма эффективным для обработки больших списков адресов с вариативностью написания.
Второй подход – геокодинг. Этот метод преобразует текстовое описание адреса в географические координаты. Получив координаты для каждого адреса в обоих списках, можно сравнивать их близость и таким образом находить соответствия. Геокодинг полезен для проверки корректности адресов и выявления дубликатов, записанных по-разному. Однако, этот метод имеет существенные ограничения в контексте данной задачи. Во-первых, не все адреса могут быть найдены на картах. Если объект ещё строится, то адрес еще не внесен в картографические сервисы. Во-вторых, геокодинг может быть неточным, особенно в сельской местности. Таким образом, полагаться исключительно на геокодинг в данном случае рискованно.
Для нашей задачи, где требуется сравнить большие списки адресов с высокой вариативностью написания и наличием потенциально «несуществующих» адресов, алгоритмы нечёткого сопоставления представляются более подходящим решением. Они не требуют наличия адреса на карте и способны эффективно обрабатывать различные варианты написания одного и того же адреса. Гибкость настройки позволяет подобрать оптимальный баланс между точностью и полнотой поиска соответствий, минимизируя как ложноположительные, так и ложноотрицательные результаты. В то время как геокодинг может служить дополнительным инструментом для верификации результатов, основным методом сравнения адресов в данном случае следует выбрать fuzzy matching.
Подготовка данных
Прежде чем приступить к сравнению адресов, необходимо привести их к единому формату. Это значительно повысит точность алгоритмов нечёткого сопоставления. Различия в регистре, сокращениях, пунктуации и лишние пробелы могут помешать алгоритму правильно идентифицировать одинаковые адреса. Например, "д. Малое Шилово" и "малое шилово" будут рассматриваться как разные адреса, если не провести предварительную обработку.
Для обработки списков адресов используем Python с библиотеками pandas, openpyxl и fuzzywuzzy. pandas предоставляет удобные инструменты для работы с табличными данными, openpyxl позволяет читать и записывать файлы Excel, а fuzzywuzzy реализует алгоритмы нечёткого сопоставления.
def clean_address(address): print(f"Очистка адреса: {address}") # Вывод текущего адреса для очистки if pd.isnull(address): # Проверяем, является ли адрес пустым значением return None # Приведение к нижнему регистру address = address.lower() # Список замен с сохранением структуры replacements = [ (r"\bп/ст\b", ""), # Убираем "п/ст" (r"\bднт\b", ""), # Убираем "ДНТ" (r"\bснт\b", ""), # Убираем "СНТ" (r"\bднп\b", ""), # Убираем "ДНП" (r"\bкв-л\b", ""), # Убираем "кв-л" (r"\bпроезд\b", ""), # Убираем "проезд" (r"\bквартал\b", ""), # Убираем "квартал" (r"\bд\.\s?", ""), # Убираем "д." с пробелом (r"\bг\.\s?", ""), # Убираем "г." с пробелом (r"\bпер\.\s?", ""), # Убираем "пер." с пробелом (r"\bул\s?", ""), # Убираем "ул" с пробелом (r"\bп\.\s?", ""), # Убираем "п." с пробелом (r"\bс\.\s?", ""), # Убираем "с." с пробелом (r"\bст\.\s?", ""), # Убираем "ст." с пробелом (r"\bпр-д\b", "") # Убираем "пр-д" ] # Применение замен for pattern, replacement in replacements: address = re.sub(pattern, replacement, address) # Удаление текста в скобках address = re.sub(r"\([^)]*\)", "", address) # Убираем текст в скобках # Удаление лишних символов, но с сохранением структуры address = re.sub(r"[.,]", "", address) # Убираем точки и запятые address = re.sub(r"\s{2,}", " ", address) # Убираем множественные пробелы address = re.sub(r"[\"]", "", address) # Убираем кавычки address = address.strip() # Убираем про��елы по краям print(f"Очищенный адрес: {address}") # Вывод очищенного адреса return address
Для приведения адресов к единому формату используем функцию clean_address, представленную в коде выше. Она приводит адрес к нижнему регистру, удаляет сокращения (например, "д.", "ул.", "г."), текст в скобках, лишние пробелы и знаки препинания. Применение регулярных выражений обеспечивает гибкость и эффективность очистки. Функция также включает вывод исходного и очищенного адресов для контроля процесса обработки.
Перед началом работы необходимо установить упомянутые библиотеки. Это можно сделать с помощью pip:
pip install pandas openpyxl fuzzywuzzy
После установки библиотек и подготовки данных можно переходить к реализации алгоритма нечёткого сопоставления.
Основы работы с fuzzywuzzy
Библиотека fuzzywuzzy предоставляет несколько функций для сравнения строк, основанных на алгоритме Левенштейна. Этот алгоритм вычисляет минимальное количество операций (вставка, удаление, замена символов), необходимых для преобразования одной строки в другую. Чем меньше операций требуется, тем больше сходство между строками.
fuzzywuzzy предлагает три основные функции:
fuzz.ratio: Сравнивает строки целиком, учитывая порядок слов. Например, fuzz.ratio("ул. Ленина 10", "Ленина ул 10") вернёт относительно низкий балл, несмотря на то, что слова одинаковые, но расположены в разном порядке.
fuzz.partial_ratio: Ищет наиболее похожую подстроку. Полезно, когда одна строка является частью другой. Например, fuzz.partial_ratio("ул. Ленина 10", "г. Москва, ул. Ленина 10, кв 5") вернёт высокий балл, так как первая строка полностью содержится во второй.
fuzz.token_sort_ratio: Сначала сортирует слова в строках по алфавиту, а затем сравнивает их с помощью fuzz.ratio. Это позволяет игнорировать порядок слов. В нашем примере fuzz.token_sort_ratio("ул. Ленина 10", "Ленина ул 10") выдаст высокий балл, поскольку после сортировки строки станут идентичными.
# Функция для поиска совпадений с помощью fuzzy matching def match_address(row, approved_addresses): cleaned_address = row["cleaned_address"] if not cleaned_address: # Проверка, если адрес пустой (None или пустая строка) print("Пропущен пустой адрес") return None # Извлекаем цифры из текущего адреса current_digits = set(re.findall(r'\d+', cleaned_address)) if not current_digits: print(f"Адрес без цифр пропущен: {cleaned_address}") return None # Отфильтровываем список одобренных адресов, о��тавляя только те, где есть совпадающие цифры filtered_addresses = [ addr for addr in approved_addresses if current_digits & set(re.findall(r'\d+', addr)) ] if not filtered_addresses: print(f"Совпадений по цифрам не найдено для адреса: {cleaned_address}") return None print(f"Поиск совпадения для адреса: {cleaned_address}") # Лог текущего адреса result = process.extractOne(cleaned_address, filtered_addresses, scorer=fuzz.token_sort_ratio) if result: # Если совпадение найдено match, score = result print(f"Найдено совпадение: {match} с оценкой {score}") # Вывод найденного совпадения и оценки return match if score > 70 else None # Возвращаем совпадение только при достаточной точности else: print("Совпадений не найдено") return None
Использую fuzz.token_sort_ratio в сочетании с предварительной фильтрацией по совпадающим цифрам в адресах. Это позволяет существенно ускорить процесс и повысить точность сопоставления, так как сравниваются только те адреса, номера которых потенциально могут совпадать.
Порог сходства установлен на 70, что означает, что совпадение считается найденным, только если оценка fuzz.token_sort_ratio превышает это значение. Это позволяет отсеять ложные совпадения.
Скрипт для сопоставления списков разных адресов
Скрипт вначале загружает данные из файлов Excel с помощью библиотеки pandas, после загрузки скрипт очищает адреса в обоих списках, используя функцию clean_address, приводя их к единому формату.
Затем начинается процесс сопоставления. Для каждого адреса из реестра поданных объектов скрипт ищет соответствие в реестре согласованных объектов с помощью библиотеки fuzzywuzzy. Функция process.extractOne, используемая в коде, позволяет эффективно находить совпаденич в большом списке, применяя алгоритм token_sort_ratio. Предварительная фильтрация по совпадающим цифрам в адресах значительно ускоряет обработку больших списков.
Результаты сопоставления, включая найденный адрес и отметку о согласованности "➕" или нет "❌", добавляются в исходный реестр поданных объектов. Окончательный результат сохраняется в новый файл Excel.
Полный код:
# pip install pandas openpyxl fuzzywuzzy # Подробнее: https://habr.com/ru/articles/873242/ """ Иногда приходится заниматься сравнением больших списков адресов, в которых адреса записаны совершенно по-разному без внятных идентификаторов вроде номера объекта - есть только адрес. Один и тот же адрес может фигурировать в различных списках следующим образом: 📍 "д. Малое Шилово, ул. Березовая, д. 7" и "Березовая 7_М Шилово". 📍 "п. Ласьва, ул. Весенняя, д. 5" и "Весенняя 5_Ласьва". 📍 "Луговой пер 5, Краснокамск г" и "г. Краснокамск, пер. Луговой, 5". 📍 "д. Новая Ивановка, ул. Солнечная, 18" и "д.Новая Ивановка, ул.Солнечная, 18". Уже выделенные отдельно адреса могут выглядеть как на скриншоте Экселя. А пример поставленной задачи может звучать так: «В реестре поданных объектов отметить все согласованные объекты (из общего списка согласованных)». Если отбросить вариант ручного исполнения и обратиться к скриптам, то мне видится всего два решения: ✅ Использовать алгоритмы нечёткого сопоставления. ✅ Использовать геокодинг адресов. """ import sys sys.stdout.reconfigure(encoding='utf-8') import re import pandas as pd from fuzzywuzzy import fuzz, process def clean_address(address): print(f"Очистка адреса: {address}") # Вывод текущего адреса для очистки if pd.isnull(address): # Проверяем, является ли адрес пустым значением return None # Приведение к нижнему регистру address = address.lower() # Список замен с сохранением структуры replacements = [ (r"\bп/ст\b", ""), # Убираем "п/ст" (r"\bднт\b", ""), # Убираем "ДНТ" (r"\bснт\b", ""), # Убираем "СНТ" (r"\bднп\b", ""), # Убираем "ДНП" (r"\bкв-л\b", ""), # Убираем "кв-л" (r"\bпроезд\b", ""), # Убираем "проезд" (r"\bквартал\b", ""), # Убираем "квартал" (r"\bд\.\s?", ""), # Убираем "д." с пробелом (r"\bг\.\s?", ""), # Убираем "г." с пробелом (r"\bпер\.\s?", ""), # Убираем "пер." с пробелом (r"\bул\s?", ""), # Убираем "ул" с пробелом (r"\bп\.\s?", ""), # Убираем "п." с пробелом (r"\bс\.\s?", ""), # Убираем "с." с пробелом (r"\bст\.\s?", ""), # Убираем "ст." с пробелом (r"\bпр-д\b", "") # Убираем "пр-д" ] # Применение замен for pattern, replacement in replacements: address = re.sub(pattern, replacement, address) # Удаление текста в скобках address = re.sub(r"\([^)]*\)", "", address) # Убираем текст в скобках # Удаление лишних символов, но с сохранением структуры address = re.sub(r"[.,]", "", address) # Убираем точки и запятые address = re.sub(r"\s{2,}", " ", address) # Убираем множественные пробелы address = re.sub(r"[\"]", "", address) # Убираем кавычки address = address.strip() # Убираем пробелы по краям print(f"Очищенный адрес: {address}") # Вывод очищенного адреса return address # Функция для поиска совпадений с помощью fuzzy matching def match_address(row, approved_addresses): cleaned_address = row["cleaned_address"] if not cleaned_address: # Проверка, если адрес пустой (None или пустая строка) print("Пропущен пустой адрес") return None # Извлекаем цифры из текущего адреса current_digits = set(re.findall(r'\d+', cleaned_address)) if not current_digits: print(f"Адрес без цифр пропущен: {cleaned_address}") return None # Отфильтровываем список одобренных адресов, оставляя только те, где есть совпадающие цифры filtered_addresses = [ addr for addr in approved_addresses if current_digits & set(re.findall(r'\d+', addr)) ] if not filtered_addresses: print(f"Совпадений по цифрам не найдено для адреса: {cleaned_address}") return None print(f"Поиск совпадения для адреса: {cleaned_address}") # Лог текущего адреса result = process.extractOne(cleaned_address, filtered_addresses, scorer=fuzz.token_sort_ratio) if result: # Если совпадение найдено match, score = result print(f"Найдено совпадение: {match} с оценкой {score}") # Вывод найденного совпадения и оценки return match if score > 70 else None # Возвращаем совпадение только при достаточной точности else: print("Совпадений не найдено") return None # Загружаем данные из Excel-файлов print("Загрузка данных...") submitted_df = pd.read_excel("submitted.xlsx") # Реестр поданных объектов approved_df = pd.read_excel("approved.xlsx") # Реестр согласованных объектов # Очистка адресов в обоих реестрах print("Очистка адресов в таблицах...") submitted_df["cleaned_address"] = submitted_df["address"].apply(clean_address) approved_df["cleaned_address"] = approved_df["address"].apply(clean_address) # Формируем список очищенных адресов из реестра согласованных объектов approved_addresses = approved_df["cleaned_address"].dropna().tolist() # Ищем совпадения и добавляем их в реестр поданных объектов print("Сопоставление адресов...") submitted_df["matched_address"] = submitted_df.apply( match_address, approved_addresses=approved_addresses, axis=1 ) # Добавляем отметку о согласованности print("Добавление отметки о согласованности...") # Проверяем наличие совпадения и добавляем соответствующий символ submitted_df["is_approved"] = submitted_df["matched_address"].notnull().apply( lambda x: "➕" if x else "❌" ) # Сохраняем результат в новый Excel-файл print("Сохранение результатов...") submitted_df.to_excel("submitted_with_matches_v2.xlsx", index=False) print("Готово! Результаты сохранены в 'submitted_with_matches_v2.xlsx'.")
Результат работы скрипта:
Заключение
Автоматизация процесса сопоставления адресов с помощью Python позволяет значительно сэкономить время и исключить ошибки, связанные с человеческим фактором. Вместо утомительной ручной проверки скрипт быстро и точно обрабатывает большие объемы данных. Более того, представленный скрипт легко адаптируется под похожие задачи, требующие сравнения текстовых строк, например, со��оставление наименований товаров или данных клиентов.
Для повышения точности сопоставления можно рассмотреть комбинирование fuzzy matching с геокодингом. Если адрес можно успешно геокодировать, то координаты служат дополнительным критерием для подтверждения совпадения.
Буду рад обсудить возможные улучшения и ответы на ваши вопросы в комментариях.
Автор: Михаил Шардин
🔗 Моя онлайн-визитка
📢 Telegram «Умный Дом Инвестора»
20 января 2025 г.
