Data Mining: Первичная обработка данных при помощи СУБД. Часть 2

  • Tutorial
Каждые полчаса появляется новая статья с кричащим лозунгом Большие данные — «новая нефть»!. Просто находка для маркетинговых текстов. Большие Данные = Большая Нефть = Профит. Откуда взялось данное утверждение? Давайте выйдем за рамки штампа и копнем чуть глубже:
Одним из первых его употребил Майкл Палмер[1] еще в 2006 году:
Данные это просто сырая нефть. Она ценна, но без переработки она не может быть по-настоящему использована. Она должна быть превращена в газ, пластик, химикаты, и т.д., чтобы создать ценность, влекущую прибыльность; так и данные нужно проанализировать и «раскусить», чтобы они стали ценными.

Такое понимание трендового «Большие данные — новая нефть!» ближе к реальности чем к маркетингу. И совсем не отменяет высказывания Дизраели:
«Существуют три вида лжи: Есть ложь, наглая ложь и статистика».
Данная статья является продолжением топика Data Mining: Первичная обработка данных при помощи СУБД. Часть 1
Продолжим добычу!


Продолжение удаления двойников

В прошлой статье был дан небольшой пример кода, который позволяет избавляться от «двойников». Продолжим двигаться в том же направлении. Для успешной работы необходимо преобразовать также и таблицу с тестовыми данными:
--последовательность уже создана ранее 
--создаем таблицу с первичным ключом
select nextval('titanik_train_seq') as id, a.* 
into titanik_test_pk
from titanik_test a;

--разделяем поле с именем билета аналогично способу с тренировочными данными
select id,pclass,"name",sex,age,sibsp,parch,ticket,fare,cabin,embarked,
m[1] as ticket_type, m[2] as ticket_number
into titanik_test_1
from 
 (select id,pclass,"name",sex,age,sibsp,parch,ticket,fare,cabin,embarked,
   regexp_matches(ticket, '^\s*?(.*?)\s*?(\d*?)$') as m 
   from titanik_test_pk
 ) as a;

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

Применяем аналогичные операторы обновления к тестовой таблице(плюс добавим еще два, в конце, для замены элементов, которых не было в тренировочной таблице):
update  titanik_test_1 set ticket_type='A.5.' where ticket_type = 'A./5.';
update  titanik_test_1 set ticket_type='A.5.' where ticket_type = 'A/5';
update  titanik_test_1 set ticket_type='A.5.' where ticket_type = 'A/5.';
update  titanik_test_1 set ticket_type='A.5.' where ticket_type = 'A/S';
update  titanik_test_1 set ticket_type='A/4' where ticket_type = 'A/4.';
update  titanik_test_1 set ticket_type='A/4' where ticket_type = 'A4.';
update  titanik_test_1 set ticket_type='C.A.' where ticket_type = 'CA';
update  titanik_test_1 set ticket_type='C.A.' where ticket_type = 'CA.';
update  titanik_test_1 set ticket_type='SW/PP' where ticket_type = 'S.W./PP';
update  titanik_test_1 set ticket_type='SC/PARIS' where ticket_type = 'SC/Paris';
update  titanik_test_1 set ticket_type='SOTON/O.Q.' where ticket_type = 'SOTON/OQ';
update  titanik_test_1 set ticket_type='SOTON/O2' where ticket_type = 'STON/O 2.';
update  titanik_test_1 set ticket_type='SOTON/O2' where ticket_type = 'STON/O2.';
update  titanik_test_1 set ticket_type='W/C' where ticket_type = 'W./C.';
update  titanik_test_1 set ticket_type='W.E.P.' where ticket_type = 'WE/P';
update  titanik_test_1 set ticket_type='SOTON/O.Q.' where ticket_type = 'STON/OQ.';
update  titanik_test_1 set ticket_type='SC/PARIS' where ticket_type = 'S.C./PARIS';

Данные о билетах — обработали. Теперь необходимо по такому же принципу обработать оставшиеся текстовые данные:
Пол(sex) — двойников не обнаружено, в разделении не нуждается:
select sex, count(sex) from titanik_train_1 group by 1 order by 1 asc;

sex count
female 314
male 577

select sex, count(sex) from titanik_test_1 group by 1 order by 1 asc;

sex count
female 152
male 266

Каюты(cabin) — здесь интереснее:
--опасный запрос!
-- select cabin, count(cabin) from titanik_train_1 group by 1 order by 1 asc;
select cabin, count(id) from titanik_train_1 group by 1 order by 1 asc;

Если выполнить первый запрос(закомментированный), то получим довольно странное значение — 0 записей у которых не указана каюта. Это связано с особенностями работы агрегирующих функций. Не умеет правильно складывать пустые значения. А потому, пишем count(id). И получаем результат: 687 пассажиров с неуказанной каютой. Можно сделать предположение, что это «общий» отсек. И скорее всего для этих записей не указан класс билета.
Проверим наше предположение:
select id, cabin, ticket_type from titanik_train_1 where cabin ISNULL;
select id, cabin, ticket_type from titanik_train_1 where cabin NOTNULL;

Не подтвердилось. Вывело много строк, для которых указан тип билета. А наоборот(запрос номер 2)? Тоже не подтвердилось. Делаем вывод, что либо серия билета утеряна для определенного количества людей, либо показывает что-то другое, а не расположение человека в каюте или нет. То есть, несет дополнительную информацию. Возвращаемся к предыдущему запросу.
В выводе запросов по кабинам и количеству записей с группировкой, есть интересные строки:
cabin count
C23 C25 C27 4
C30 1
F G73 2
T 1

Во первых — информация о типе каюты(первая буква перед цифрами).
Во вторых, на один билет — несколько кают. И очень часто по нескольку человек в одной каюте с билетами, в которых указано несколько кают(читай мест). Это получается довольно интересные данные, которые нельзя игнорировать. Фактически это данные дублирующие родственников, но учитывающие, например друзей или знакомых, или коллег по работе — т.е. знакомых людей, готовых помогать друг другу. Также, получаем информацию, сколько человек было в каюте.
Вывод — добавляем поле тип кабины. И добавляем количество кают в билете. Также добавим поле количество человек в каютах.
Т.е. семья из 4х человек занимает 2 каюты. Или например, два разных человека занимают одну каюту. Количество данных растет!
Запросы которые это реализуют довольно сложные и требуют понимания работы регулярных выражений PREG в PostgreSQL.
Можно все вместить в один огромный запрос, но я решил разделить на две части. Часть один определяет тип каюты и количество кают на билет, а второй запрос определяет количество человек с такой же каютой(набором кают) в билете.
select id,survived,pclass,"name",sex,age,sibsp,parch,ticket,fare,cabin,embarked, cnt as cabin_cnt, cabin_type, ticket_type, ticket_number
into titanik_train_2
from (
select id, survived, pclass,"name",sex,age,sibsp,parch,ticket,fare,cabin,embarked, ticket_type, ticket_number,
regexp_matches(cabin, '^\s*?([A-Z]\d*)\s*?([A-Z]\d*)\s*?([A-Z]\d*)\s*?([A-Z]\d*)\s*?.*?$') as m, 4 as cnt, 
substring(cabin, 1, 1) as cabin_type
from titanik_train_1
UNION
select id, survived, pclass,"name",sex,age,sibsp,parch,ticket,fare,cabin,embarked, ticket_type, ticket_number,
regexp_matches(cabin, '^\s*?([A-Z]\d*)\s*?([A-Z]\d*)\s*?([A-Z]\d*)\s*?$') as m, 3 as cnt,
substring(cabin, 1, 1) as cabin_type
from titanik_train_1
UNION
select id, survived, pclass,"name",sex,age,sibsp,parch,ticket,fare,cabin,embarked, ticket_type, ticket_number,
regexp_matches(cabin, '^\s*?([A-Z]\d*)\s*?([A-Z]\d*)\s*?$') as m, 2 as cnt,
substring(cabin, 1, 1) as cabin_type
from titanik_train_1
UNION
select id, survived, pclass,"name",sex,age,sibsp,parch,ticket,fare,cabin,embarked, ticket_type, ticket_number,
regexp_matches(cabin, '^\s*?([A-Z]\d*)\s*?$') as m, 1 as cnt,
substring(cabin, 1, 1) as cabin_type
from titanik_train_1
UNION
select id, survived, pclass,"name",sex,age,sibsp,parch,ticket,fare,cabin,embarked, ticket_type, ticket_number,
NULL as m, 0 as cnt, NULL as cabin_type
from titanik_train_1 where cabin ISNULL)
as a;

В принципе, единственно сложный момент здесь — регулярное выражение. Как я его строил:
F С82 С45 — пример наименования которые нужно выхватить. Этот запрос строится из основного блока:
([A-Z]\d*) — [A-Z] означает что должен быть хоть один, буквенный большой символ, \d* — любое количество 0… цифр.

И второй запрос, который считает количество людей в каютах.
select a.*, b.cnt as cabin_people_cnt
into 
titanik_train_3
from 
titanik_train_2 a, (
select cabin as cabid, count(id) as cnt from titanik_train_1 group by 1) as b
where 
a.cabin = b.cabid OR (a.cabin ISNULL AND b.cabid ISNULL);
--Обновляем. Если кабин не указана - то ставим ноль вместо 687.
update titanik_train_3 set cabin_people_cnt=0 where cabin ISNULL;

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

Аналогично делаем для тестовых данных:
select id,pclass,"name",sex,age,sibsp,parch,ticket,fare,cabin,embarked, cnt as cabin_cnt, cabin_type, ticket_type, ticket_number
into titanik_test_2
from (
select id,  pclass,"name",sex,age,sibsp,parch,ticket,fare,cabin,embarked, ticket_type, ticket_number,
regexp_matches(cabin, '^\s*?([A-Z]\d*)\s*?([A-Z]\d*)\s*?([A-Z]\d*)\s*?([A-Z]\d*)\s*?.*?$') as m, 4 as cnt, 
substring(cabin, 1, 1) as cabin_type
from titanik_test_1
UNION
select id,  pclass,"name",sex,age,sibsp,parch,ticket,fare,cabin,embarked, ticket_type, ticket_number,
regexp_matches(cabin, '^\s*?([A-Z]\d*)\s*?([A-Z]\d*)\s*?([A-Z]\d*)\s*?$') as m, 3 as cnt,
substring(cabin, 1, 1) as cabin_type
from titanik_test_1
UNION
select id,  pclass,"name",sex,age,sibsp,parch,ticket,fare,cabin,embarked, ticket_type, ticket_number,
regexp_matches(cabin, '^\s*?([A-Z]\d*)\s*?([A-Z]\d*)\s*?$') as m, 2 as cnt,
substring(cabin, 1, 1) as cabin_type
from titanik_test_1
UNION
select id,  pclass,"name",sex,age,sibsp,parch,ticket,fare,cabin,embarked, ticket_type, ticket_number,
regexp_matches(cabin, '^\s*?([A-Z]\d*)\s*?$') as m, 1 as cnt,
substring(cabin, 1, 1) as cabin_type
from titanik_test_1
UNION
select id,  pclass,"name",sex,age,sibsp,parch,ticket,fare,cabin,embarked, ticket_type, ticket_number,
NULL as m, 0 as cnt, NULL as cabin_type
from titanik_test_1 where cabin ISNULL)
as a;

и вторая часть:
select a.*, b.cnt as cabin_people_cnt
into 
titanik_test_3
from 
titanik_test_2 a, (
select cabin as cabid, count(id) as cnt from titanik_test_1 group by 1) as b
where 
a.cabin = b.cabid OR (a.cabin ISNULL AND b.cabid ISNULL);

Осталось одно поле: порт посадки (embarked):
select embarked, count(id) from titanik_train_3 group by 1 order by 1 asc;
select embarked, count(id) from titanik_test_3 group by 1 order by 1 asc;

Результат такой — двойников не обнаружено, в разделении не нуждается:
embarked count
C 168
Q 77
S 644
2

Что делать с двумя записями где нет данных? Можно заменить случайными значениями, можно отбросить, можно поставить среднее. На выбор.

Выводы

В этой части мы предварительно подготовили текстовые данные в тренировочной и тестовой выборке. По времени, данная работа заняла порядка трех часов. От скачивания данных — до текущего момента.

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

Обновление
Часть третья: habrahabr.ru/post/165283
Часть четвертая: habrahabr.ru/post/173819
  • +9
  • 21,3k
  • 5
Поделиться публикацией

Комментарии 5

    0
    Если выполнить первый запрос(закомментированный), то получим довольно странное значение — 0 записей у которых не указана каюта. Это связано с особенностями работы агрегирующих функций. Не умеет правильно складывать пустые значения.

    На самом деле это фича, а не баг! Если вам нужно посчитать кол-во записей, используйте count(*). count(значение) считает именно количество значений, а null — отсутствие значения, его считать не надо. Эту особенность удобно использовать, если вам надо посчитать сущности с определенными свойствами, при этом не нужно ограничивать всю выборку в блоке where, так как это повлияет на выборку сущностей с другими свойствами:
    select
      count(*) as TOTAL,
      count(case when sex = 'male' then 1 else null end) as MALE_TOTAL,
      count(case when sex = 'female' then 1 else null end) as FEMALE_TOTAL,
      count(case when sex = 'male' and age>=60 then 1 else null end) as OLD_MALE_TOTAL,
      count(case when sex = 'female' and age>=60 then 1 else null end) as OLD_FEMALE_TOTAL,
      count(case when age<=16 then 1 else null end) as CHILDREN_TOTAL
    from titanik_test_1
    
      0
      Да, это та еще фича. Немало приятных моментов при отладке доставили null values.
      На счет count(*) — думал, но решил что лучше передать маленькое не nullable поле, целочисленное вместо *. Думаю с запасом на большие таблицы — * на миллионах будет помедленнее.
        0
        Для счета идеально подходит поле primary key — оно маленькое и гарантированно без null-ов (что вы и сделали). За postgres не скажу, но в Oracle нет никаких проблем с count(*) — он считает количество записей (строк), в содержимое записей даже не заглядывая, соответственно делает это быстро.
      0
      там в титанике какой то чел с первого сабмита вышел на первое место с 0.96651, ваще мощь -)
        0
        Да, жесть. Вот уже скоро допишу третью — четвертую часть, посмотрим какой будет результат. Хорошо если лучше базовых наборов :)

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

      Самое читаемое