Время прочтения: 5 мин.
Добрый день, друзья!
Устали фильтровать данные в excel? Тогда присоединяйтесь! Вы узнаете, как с помощью Python осуществить поиск строк в файле по ключевым словам в столбцах.
Несомненно, многие из нас не раз сталкивались с необходимостью фильтрации данных в файле excel, обычно мы делаем это через встроенный в программу фильтр, но бывают ситуации, когда нужно осуществить отбор строк по большому количеству условий (в нашем случае – по ключевым словам) сразу по нескольким столбцам.
Рассмотрим задачу более подробно. Например, у нас есть файл excel с обращениями клиентов (тысячи строк), который содержит следующие колонки: «ИНН клиента», «Дата обращения», «Обращение», «Решение». В столбце «Обращение» содержится текст обращения клиента, в столбце «Решение» — ответ на обращение. Суть обращений может быть абсолютно любой (например, в банковской сфере: кредитование, страхование, эквайринг и т.д).
Требуется с помощью поиска ключевых слов/сочетаний слов (например, «КАСКО», «ОСАГО», «автострахов», «залог…авто» и т.п.) в колонках «Обращение» и «Решение» выбрать обращения клиентов, которые относятся к страхованию автотранспорта. Нужные слова могут содержаться как в обоих столбцах, так и в одном из них.
Конечно, можно начать фильтровать данные колонки в excel, но это будет долго и трудоёмко, особенно, если слов для поиска подходящих обращений много (или столбцов, в которых необходимо найти ключевые слова). Поэтому для решения нашей задачи требуется более удобный инструмент – Python.
Ниже представлен код, с помощью которого мы отберем необходимые обращения клиентов:
#Импорт библиотек.
import pandas as pd
import numpy as np
import re
#Чтение исходного файла с данными.
df = pd.read_excel(r'ПУТЬ К ФАЙЛУ\Название исходного файла с данными.xlsx', dtype='str')
#Регулярные выражения.
#Шаблон (слова/сочетания слов, которые необходимо найти в столбцах).
r= r'(каско)|(осаго)|(страх.*?транспорт)|(транспорт.*?страх)|(страх.*?авто)|(авто.*?страх)|(залог.*?транспорт)|(транспорт.*?залог)|(залог.*?авто)|(авто.*?залог)|(автострахов)'
Подробно с информацией о модуле re, функциями и синтаксисом регулярных выражений в Python можно ознакомиться по следующим ссылкам: https://docs-python.ru/standart-library/modul-re-python/, https://docs-python.ru/standart-library/modul-re-python/sintaksis-reguljarnogo-vyrazhenija/.
Поясним, что «.*?» в выражении (страх.*?транспорт) ищет между «страх» и «транспорт» любое количество символов, вопросительный знак отключает жадность алгоритма поиска (поиск заканчивается как только находится первый «транспорт»).
#Для каждой строки ищем шаблон в столбце «Обращение».
obr = df['Обращение'].apply(lambda x: re.search(r, str(x).lower())) #другой вариант: obr = df[' Обращение '].str.lower().str.contains(r)
#Для каждой строки ищем шаблон в столбце «Решение».
otvet = df['Решение'].apply(lambda x: re.search(r, str(x).lower())) #другой вариант: otvet = df['Решение'].str.lower().str.contains(r)
#Для каждой строки проверяем наличие шаблона хотя бы в одном из столбцов «Обращение» и «Решение» (результат - True/False).
itog = np.any(np.array([~obr.isnull(), ~otvet.isnull()]), axis=0)
#Результат (оставляем только те строки в таблице, по которым получен результат True).
new_df = df[itog]
#Запись результата в excel.
new_df.to_excel('Название итогового файла.xlsx', index=False)
В результате получаем новый файл excel, в который полностью скопированы нужные нам обращения клиентов:
— в обращении клиента с ИНН 1111111111 в столбцах «Обращение» и «Решение» содержится слово «КАСКО»;
— в обращении клиента с ИНН 333333333333 в столбце «Решение» содержатся сочетания слов «залог…транспорт», «транспорт…страх», «залог…авто», «страх…авто»;
— в обращении клиента с ИНН 444444444444 в столбце «Обращение» содержатся сочетания слов «страх…транспорт»; «транспорт…залог».
Количество столбцов, в которых можно производить поиск ключевых слов, не ограничен – в приведенном примере их два, но у вас может быть больше.
При необходимости для каждого столбца можно задать свой шаблон для поиска слов:
#Шаблон 1 для столбца «Обращение».
r1= r'(каско)|(осаго)|(страх.*?транспорт)|(транспорт.*?страх)|(страх.*?авто)|(авто.*?страх)’
#Шаблон 2 для столбца «Решение».
r2= r'(залог.*?транспорт)|(транспорт.*?залог)|(залог.*?авто)|(авто.*?залог)|(автострахов)'
#Поиск шаблонов 1 и 2 в столбцах «Обращение» и «Решение» соответственно.
obr = df['Обращение'].apply(lambda x: re.search(r1, str(x).lower()))
otvet = df['Решение'].apply(lambda x: re.search(r2, str(x).lower()))
Если требуется выбрать строки, в которых ключевые слова содержатся и в том, и в другом столбце, то нужно заменить функцию any() на all():
itog = np.all(np.array([~obr.isnull(), ~otvet.isnull()]), axis=0)
Теперь рассмотрим ситуацию, когда у нас имеется несколько файлов excel с обращениями клиентов (с аналогичной структурой столбцов), и необходимо в каждом выбрать подходящие обращения.
Тогда код, с помощью которого мы отберем нужные строки, будет выглядеть так:
#Импорт библиотек.
import pandas as pd
import numpy as np
import os
import re
import warnings
#Игнорирование всех предупреждений.
warnings.filterwarnings('ignore')
#Путь к папке с исходными файлами.
path = r'ПУТЬ К ПАПКЕ С ФАЙЛАМИ '
#Регулярные выражения.
#Шаблон (слова/сочетания слов, которые необходимо найти в столбцах).
r= r'(каско)|(осаго)|(страх.*?транспорт)|(транспорт.*?страх)|(страх.*?авто)|(авто.*?страх)|(залог.*?транспорт)|(транспорт.*?залог)|(залог.*?авто)|(авто.*?залог)|(автострахов)'
#Создание папки, в которую будут сохраняться файлы с нужными обращениями.
os.makedirs('Нужные обращения', exist_ok=True)
#Получение списка полных имён для всех файлов xlsx в папке с исходными файлами.
docs = []
for root, _, files in os.walk(path):
for file in files:
if file.split('.')[-1] == 'xlsx':
docs.append(os.path.join(root, file))
print(f'В директории {path} \nобнаружено {len(docs)} файлов')
#Для каждого файла из списка производим его чтение, поиск шаблона в столбцах «Обращение» и «Решение», проверяем наличие шаблона хотя бы в одном из данных столбцов, оставляем только те строки в таблице, по которым получен результат True, записываем результат в excel в папку «Нужные обращения».
%%time
for i, doc in enumerate(docs):
df = pd.read_excel(doc)
print(f'{i+1} файл из {len(docs)} прочитан', end=' ')
obr = df['Обращение'].apply(lambda x: re.search(r, str(x).lower()))
otvet = df['Решение'].apply(lambda x: re.search(r, str(x).lower()))
itog = np.any(np.array([~obr.isnull(), ~otvet.isnull()]), axis=0)
df = df[itog]
df.to_excel(os.path.join('Нужные обращения', doc.split('\\')[-1]), index=False)
print('и обработан')
В результате создается папка «Нужные обращения», в которой содержатся новые файлы excel с полностью скопированными нужными обращениями клиентов. По количеству и названию данные файлы соответствуют исходным.
Таким образом, благодаря Python поиск строк в файлах по ключевым словам в столбцах становится быстрым и несложным делом. Приведенный код значительно ускоряет и упрощает работу аналитика в части фильтрации строк по большому количеству условий по нескольким столбцам.