Каждые полчаса появляется новая статья с кричащим лозунгом Большие данные — «новая нефть»!. Просто находка для маркетинговых текстов. Большие Данные = Большая Нефть = Профит. Откуда взялось данное утверждение? Давайте выйдем за рамки штампа и копнем чуть глубже:
Одним из первых его употребил Майкл Палмер[1] еще в 2006 году:
Такое понимание трендового «Большие данные — новая нефть!» ближе к реальности чем к маркетингу. И совсем не отменяет высказывания Дизраели:
«Существуют три вида лжи: Есть ложь, наглая ложь и статистика».
Данная статья является продолжением топика Data Mining: Первичная обработка данных при помощи СУБД. Часть 1
Продолжим добычу!
В прошлой статье был дан небольшой пример кода, который позволяет избавляться от «двойников». Продолжим двигаться в том же направлении. Для успешной работы необходимо преобразовать также и таблицу с тестовыми данными:
В этом примере кода я решил использовать последовательность из предыдущей таблицы, для того, чтобы легче было объединять данные в случае необходимости потом. Разделение наименования билета на текст и серию проводится точно также.
Применяем аналогичные операторы обновления к тестовой таблице(плюс добавим еще два, в конце, для замены элементов, которых не было в тренировочной таблице):
Данные о билетах — обработали. Теперь необходимо по такому же принципу обработать оставшиеся текстовые данные:
Пол(sex) — двойников не обнаружено, в разделении не нуждается:
Каюты(cabin) — здесь интереснее:
Если выполнить первый запрос(закомментированный), то получим довольно странное значение — 0 записей у которых не указана каюта. Это связано с особенностями работы агрегирующих функций. Не умеет правильно складывать пустые значения. А потому, пишем count(id). И получаем результат: 687 пассажиров с неуказанной каютой. Можно сделать предположение, что это «общий» отсек. И скорее всего для этих записей не указан класс билета.
Проверим наше предположение:
Не подтвердилось. Вывело много строк, для которых указан тип билета. А наоборот(запрос номер 2)? Тоже не подтвердилось. Делаем вывод, что либо серия билета утеряна для определенного количества людей, либо показывает что-то другое, а не расположение человека в каюте или нет. То есть, несет дополнительную информацию. Возвращаемся к предыдущему запросу.
В выводе запросов по кабинам и количеству записей с группировкой, есть интересные строки:
Во первых — информация о типе каюты(первая буква перед цифрами).
Во вторых, на один билет — несколько кают. И очень часто по нескольку человек в одной каюте с билетами, в которых указано несколько кают(читай мест). Это получается довольно интересные данные, которые нельзя игнорировать. Фактически это данные дублирующие родственников, но учитывающие, например друзей или знакомых, или коллег по работе — т.е. знакомых людей, готовых помогать друг другу. Также, получаем информацию, сколько человек было в каюте.
Вывод — добавляем поле тип кабины. И добавляем количество кают в билете. Также добавим поле количество человек в каютах.
Т.е. семья из 4х человек занимает 2 каюты. Или например, два разных человека занимают одну каюту. Количество данных растет!
Запросы которые это реализуют довольно сложные и требуют понимания работы регулярных выражений PREG в PostgreSQL.
Можно все вместить в один огромный запрос, но я решил разделить на две части. Часть один определяет тип каюты и количество кают на билет, а второй запрос определяет количество человек с такой же каютой(набором кают) в билете.
В принципе, единственно сложный момент здесь — регулярное выражение. Как я его строил:
F С82 С45 — пример наименования которые нужно выхватить. Этот запрос строится из основного блока:
([A-Z]\d*) — [A-Z] означает что должен быть хоть один, буквенный большой символ, \d* — любое количество 0… цифр.
И второй запрос, который считает количество людей в каютах.
В результате получаем три дополнительных поля: тип каюты, количество кают на билет и количество людей в каюте.
Аналогично делаем для тестовых данных:
и вторая часть:
Осталось одно поле: порт посадки (embarked):
Результат такой — двойников не обнаружено, в разделении не нуждается:
Что делать с двумя записями где нет данных? Можно заменить случайными значениями, можно отбросить, можно поставить среднее. На выбор.
В этой части мы предварительно подготовили текстовые данные в тренировочной и тестовой выборке. По времени, данная работа заняла порядка трех часов. От скачивания данных — до текущего момента.
Эта часть получилась довольно внушительной по объему, потому продолжение в следующем посте. В следующем посте мы попытаемся уже сформировать таблицу с числовыми значениями вместо строковых. Если вдруг кто решит делать одновременно со мной, используя запросы и обрабатывая данные по этому туториалу — в коментах отвечу на вопросы. Жду критики.
Обновление
Часть третья: habrahabr.ru/post/165283
Часть четвертая: habrahabr.ru/post/173819
Одним из первых его употребил Майкл Палмер[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
