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

Можем ли мы что-нибудь сделать?

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

Шаг 1: Доступ к данным, находящимся в файлах

Нам нужен инструмент для обработки данных, инкапсулированных в плоский файл. В нашем примере это будет утилита Python. В Python есть множество универсальных модулей, которые могут нам помочь решить эту проблему. Самым первым является его способность легко читать различные типы файлов. Если данные находятся в текстовом файле, мы можем использовать CSV sniff, чтобы взять столько образцов строк, сколько пожелаем, предоставляя нам сложные характеристики текстового файла, такие как разделитель, стиль цитирования, заголовок, количество столбцов и т. д. На основе этого мы можем импортировать файл в Pandas Dataframe. Если данные находятся в файле Excel, это еще проще, так как мы можем напрямую импортировать их в Pandas Dataframe.

Шаг 2. Разберитесь с неуправляемыми структурами

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

Шаг 3. Определите типы данных для поиска

Каждый процесс имеет свою цель и ожидания. В этом упражнении мы собираемся идентифицировать примеры данных PII (личной информации) на примере Австралии (где это имеет значение). Давайте выполним профилирование, ища:

  • имя,
  • второе имя,
  • фамилия,
  • номер телефона,
  • Адрес электронной почты,
  • адрес,
  • Наименование фирмы,
  • Название компании,
  • деловой номер.

Шаг 4. Используйте двусторонний подход: заголовок и данные

Подход 1: В большинстве файлов данных у нас есть строка заголовка, которая в идеальном случае является уникальной, описательной и правильной. Мы не должны полагаться только на заголовок, но хороший заголовок может помочь уменьшить двусмысленность.

Подход 2: данные в текстовых файлах должны отражать шаблоны, которые мы ожидаем от данного типа данных.

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

Шаг 5: Получите некоторые показатели с помощью подхода 1

Во-первых, как узнать, есть ли вообще заголовок? Необходимо выполнить несколько проверок. Если данные находятся в таблице Excel (не только на листе, но и конкретно в таблице), Dataframe должен идентифицировать заголовок. Если бы мы использовали снифф CSV, он должен идентифицировать заголовок. В любом другом случае нам, возможно, придется выполнить предварительное профилирование: если тип данных первой строки всегда такой же, как и у остальных строк в файле, у нас нет заголовка.

Если мы решили, что у нас есть заголовок, давайте пройдемся по строке заголовка. В качестве базовой подготовки к сопоставлению мы должны преобразовать текст в верхний регистр, удалить все специальные символы [^A-Z], включая пробелы, и все готово. С помощью простого регулярного выражения мы можем сопоставить заголовок с различными соответствующими текстами, которые мы можем придумать, например, FIRSTNAME|GIVENNAME|FNAME|FIRSTN. Поскольку мы удалили все символы, кроме заглавных букв, любые другие варианты, например, тире, цифры или точки, уже учтены.

Мы можем применить ту же технику ко всем заголовкам столбцов. Результатом анализа является матрица True/False для каждого столбца и каждого шаблона.

Шаг 6: Получите некоторые показатели с помощью подхода 2

В рамках этого подхода мы можем измерить общие статистические характеристики данных, а также конкретные закономерности/правила.

Начнем с общих статистических характеристик. Например, Имя, Отчество, Фамилия, Название компании и Название компании: должны состоять преимущественно из текста [A-Z] с разной длиной. Напротив, рабочие номера и телефонные номера должны состоять в основном из цифр с небольшими различиями по длине.

Кроме того, мы должны проверить кардинальность и соотношение пустых/непустых полей. N/A, NaN и пустые строки могут считаться пустыми.

Захватывающая часть состоит в том, чтобы проверить шаблоны и правила. Для большинства метрик мы можем использовать регулярные выражения. Почему?

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

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

Давайте продолжим и внимательно изучим наши типы бизнес-данных:

  • Имя: будьте уверены, мы можем положиться на образец. Все, что нам нужно, это справочная база данных с большим количеством реальных лиц. Если наша команда управления данными согласна, мы можем использовать, например, локальную базу данных клиентов, полагаясь только на имена, встречающиеся не менее 5–10 раз и отделенные от остальных данных. При отсутствии такой возможности мы можем обратиться к бесчисленным альтернативам, например, в Австралии к файлу ABR, содержащему имена всех индивидуальных предпринимателей. Все, что нам нужно знать, это наиболее часто встречающиеся, скажем, 500 имен. Этот список может охватывать половину всех лиц; однако это необходимо проверить, чтобы увидеть, нужна ли нам большая выборка. Добавляя этот список к регулярному выражению (например, JOAN|JOE|JOHN| и т. д.), мы можем подсчитывать совпадения со значениями нашего столбца. Наша метрика будет пропорцией совпадающих значений и существующих значений.
  • Отчество: как мы можем себе представить, в этом столбце будет столько же совпадений с шаблоном имени, сколько и само имя. Однако это не единственная метрика, которую мы используем, и мы можем ожидать, что пустых полей в заданном столбце имени будет больше, чем в столбце имени.
  • Фамилия: фамилии иногда совпадают с образцом имени; однако мы можем повторить то же самое статистическое упражнение и с фамилиями. Наиболее часто встречающиеся 500 фамилий покроют значительную часть населения и дадут лишь небольшое количество совпадений с именами. Отметив здесь, что имена могут быть представлены в нескольких столбцах или в одном столбце. Проверка каждого столбца на наличие всех шаблонов, а также дополнительная проверка количества слов в столбце помогут принять автоматизированное решение.
  • Название компании и название компании: все эти значения можно найти в реестре, поэтому мы можем получить 500 наиболее часто используемых слов из каждого соответственно. Хотя два набора значений имеют много общего, названия компаний, как правило, содержат ссылку на тип организации, например. «inc», «ltd», четко различая две колонки.
  • Бизнес-номер, номер компании: эти значения должны иметь определенную длину и обычно содержать контрольную цифру. Нашим измерением будет количество полей, соответствующих критериям, деленное на количество полей с непустым значением.
  • Адрес электронной почты: учитывая, что все, что нам нужно знать, это содержит ли данный столбец (в основном) адреса электронной почты, самое простое регулярное выражение сделает это, проверив [email protected].
  • Номер телефона: если наш файл зависит от страны, все, что нам нужно проверить, это некоторые возможные шаблоны после удаления всех символов, которые не являются цифрами. Номер может начинаться с кода страны или может быть просто номером и должен иметь определенную длину. В зависимости от нашей страны у него также может быть множество дополнительных шаблонов для проверки. Помните, что если мы не можем уловить все возможные достоверные случаи, это нормально, наша метрика все равно должна быть убедительной. Мы можем пойти дальше, чтобы различать стационарные, мобильные, иностранные, факсимильные, специальные и т. д. номера.
  • Адрес: это сложный вопрос, потому что он может быть структурирован очень многими способами. Остановимся на его элементах:
  • Почтовый индекс: обычно его легче всего определить, например, в Австралии это четырехзначный номер, который может начинаться только с определенных цифр.
  • Страна: мы, вероятно, должны ожидать, что наша собственная страна будет отображаться в основном, хотя в международных условиях мы можем безопасно выполнить сопоставление шаблона регулярного выражения даже со всеми названиями стран.
  • Штат, провинция, округ: сопоставление с образцом должно работать; однако мы также должны учитывать короткую и длинную версии.
  • Город/Пригород: наш подход может быть таким же, как и с именами, однако ситуация немного сложнее. Список всех пригородов мы можем получить из открытых источников, однако шансы их появления в адресе явно не равны. Чтобы исправить это, мы должны связать наши данные с последней переписью, которая говорит нам, сколько людей проживает примерно в каждом пригороде. Но у этого решения есть и недостаток: если источник данных конкретно из региональных районов, мы можем не увидеть много совпадений. Таким образом, чтобы быть в безопасности, мы должны получить смесь обоих, наиболее населенных и наиболее часто встречающихся.
  • Улица, адресная строка: может показаться нелогичным, но ожидается, что эти столбцы будут предоставлять результаты при проверке имени, фамилии и пригорода. Кроме того, мы можем проверить типы улиц (такие как УЛИЦА|УЛИЦА|ДОРОГА|РД и т. д.), типы единиц (ЛОТ|МАГАЗИН|ЕДИЦА, …), а также абонентский ящик.
  • Составные поля адреса: поскольку адреса могут быть структурированы очень многими способами, мы должны ожидать, что время от времени будут появляться разные составные столбцы, например, полный адрес, строки с уличным адресом, комбинация пригород-штат-почтовый индекс и т. д.

Шаг 7: Оцените взятые показатели

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

В качестве иллюстрации рассмотрим этот упрощенный случай:

Отмечая, что мы должны ожидать увидеть грязные данные (например, недопустимые значения — это значения в неправильном столбце), также характер этого подхода приведет к ложным положительным совпадениям. Но это нормально: если взято достаточно метрик, их комбинация должна разобраться в классификации каждого столбца.

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

Или же мы можем просто создать свой собственный озвучивание вручную. Этот вариант явно менее универсален; однако отсутствие достаточного количества данных может заставить нас выбрать это простое решение.

Шаг 8: Примите тот факт, что случайные файлы будут создавать случайные проблемы

Хотя приведенное выше решение может обрабатывать большое количество случаев, оно не может решить все. Скорее всего, это не удастся, если:

  • данные не в разумном формате строки-x-столбца,
  • есть несколько столбцов для имен, адресов, телефонов и т. д. без явного различия того, что есть что,
  • есть неожиданные форматы, например, контактная информация — это один столбец со всеми типами контактной информации,
  • столбцы содержат списки данных (например, 3 телефонных номера), где мы ожидаем одно значение,
  • и т. д.

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