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

Каждые полчаса появляется новая статья с кричащим лозунгом Большие данные — «новая нефть»!. Просто находка для маркетинговых текстов. Большие Данные = Большая Нефть = Профит. Откуда взялось данное утверждение? Давайте выйдем за рамки штампа и копнем чуть глубже:
Одним из первых его употребил Майкл Палмер[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

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

Выводы

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

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