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

Данная серия посвящена анализу данных для поиска закономерностей. В качестве примера используется одна из обучающих задач сообщества спортивного анализа данных Kaggle. Хотя размеры данных для задачи не большие, методы обработки, которые будут рассматриваться вполне применимы для больших объемов данных.
После выполнения Часть 1 и Части 2 сформировались две таблицы, содержащие преобразованные данные.
titanik_test_3 и titanik_train_3.

Структура полей у них различается на одно поле — survived, значение которого нам предстоит определить для тестового набора данных. Вот код описывающий структуру таблицы titanik_train_3

CREATE TABLE titanik_train_3
(
  id bigint,
  survived integer,
  pclass integer,
  name character varying(255),
  sex character varying(255),
  age double precision,
  sibsp integer,
  parch integer,
  ticket character varying(255),
  fare double precision,
  cabin character varying(255),
  embarked character varying(255),
  cabin_cnt integer,
  cabin_type text,
  ticket_type text,
  ticket_number text,
  cabin_people_cnt integer
)

Фактически, стоит задача превратить таблицу с символьно-числовыми данными — в таблицу с только числовым представлением. Поможет нам в этом создание словарей данных и сводные таблицы. Для этого, числовые данные перенесем в том же виде в котором они и были, а символьные — закодируем.
Важнейшим условием использования словарей, является полное покрытие значений. Потому, оптимально на этом этапе(хотя в принципе можно и ранее) слить таблицы в одну. А в недостающее поле поставить NULL.
Учитывая что для создания первичного ключа использоваласть одна и таже последовательность, проблем быть не должно. Это делается при помощи оператора UNION.

select a.* into titanik_full_1 from (
select * from  titanik_train_3
union
select 
id,
NULL::integer as survived,
  pclass, "name",  sex ,  age ,  sibsp ,  parch, ticket,fare,cabin,embarked,cabin_cnt,cabin_type,ticket_type,ticket_number,
  cabin_people_cnt
 from  titanik_test_3
) as a;

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

 select a.* into titanik_full_2 from (
 select id, survived, pclass::float, age::float, sibsp::float, parch::float, fare::float, cabin_cnt::float, CAST(ticket_number as float) as ticket_number, cabin_people_cnt::float
 from titanik_full_1 where ticket_number != '' 
 union
 select id, survived, pclass, age, sibsp, parch, fare, cabin_cnt, 0 as ticket_number, cabin_people_cnt
 from titanik_full_1 where ticket_number = '' ) as a;

Получаем таблицу titanik_full_2, которая выглядит таким образом:

CREATE TABLE titanik_full_2
(
  id bigint,
  survived integer,
  pclass integer,
  age double precision,
  sibsp integer,
  parch integer,
  fare double precision,
  cabin_cnt integer,
  ticket_number integer,
  cabin_people_cnt bigint
)

Теперь в эту таблицу мы будем добавлять по полю, которое будет означать, есть то, либо иное значение у свойства для этой строки. Такие таблицы называются сводными(pivot tables), только немного не такими как обычно, поля-values будут принимать либо 0 либо 1. Схематически это тоборажено на рисунке:

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

CREATE OR REPLACE FUNCTION sparse_matrix_generator(
tablename_source character varying, 
tablename_dest character varying, 
field_name character varying)
RETURNS integer AS
$$
DECLARE
  pgst_object  REFCURSOR;
  unival character varying;
BEGIN
  OPEN pgst_object FOR EXECUTE 'select distinct '||field_name ||' from '||tablename_source ||' where ' || field_name ||' NOTNULL';
    LOOP
      FETCH pgst_object INTO unival;
      EXIT WHEN NOT FOUND;
      EXECUTE 'ALTER TABLE '|| tablename_dest ||' ADD COLUMN "'|| field_name||unival ||'" smallint NOT NULL DEFAULT 0';
      EXECUTE 'UPDATE '||tablename_dest||' SET "'||field_name||unival|| '"= 1 FROM ' ||tablename_source||
      ' WHERE '||tablename_dest||'.id = '||tablename_source||'.id and '||field_name||' = '''||unival||'''';
    END LOOP;
  RETURN 0;
END;
$$
LANGUAGE 'plpgsql';

Применяется эта функция так:

select sparse_matrix_generator('titanik_full_1', 'titanik_full_2', 'cabin_type');
select sparse_matrix_generator('titanik_full_1', 'titanik_full_2', 'ticket_type');
select sparse_matrix_generator('titanik_full_1', 'titanik_full_2', 'embarked');
select sparse_matrix_generator('titanik_full_1', 'titanik_full_2', 'sex');

Таким образом у нас теперь есть разреженная матрица размерностью в 58 столбцов. Необходимо пронормировать ее и отделить тестовую и тренировочную выборки по полю survived.
Есть разные способы нормирования. Для разных методов анализа данных есть разные требования к выборке. Воспользуемся одним из наиболее простых, минимаксным нормированием. Суть вкратце такова: минимум будет 0, максимум: 1, а все остальное расоложено пропорционально между ними. Для этого напишем функцию:

CREATE OR REPLACE FUNCTION minmax_normalizer(tablename_source character varying, field_name character varying)
  RETURNS integer AS
$BODY$
DECLARE
  pgst_object  REFCURSOR;
  maxval float;
  minval float;
  C RECORD;
BEGIN
  EXECUTE 'select min("'||field_name ||'") as minval, max("'||field_name ||'") as maxval from '|| tablename_source INTO C;
  maxval := C.maxval;
  minval := C.minval;
  EXECUTE 'UPDATE '||tablename_source||' SET "'||field_name||'"=("'||field_name||'"-$1)/($2-$1)' USING minval, maxval;
  RETURN 0;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
 

И применим ее к полям таблицы, которые нужно нормализовать:

select minmax_normalizer('titanik_full_2', 'pclass');
select minmax_normalizer('titanik_full_2', 'age');
select minmax_normalizer('titanik_full_2', 'sibsp');
select minmax_normalizer('titanik_full_2', 'parch');
select minmax_normalizer('titanik_full_2', 'fare');
select minmax_normalizer('titanik_full_2', 'cabin_cnt');
select minmax_normalizer('titanik_full_2', 'ticket_number');
select minmax_normalizer('titanik_full_2', 'cabin_people_cnt');
 

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

select * into titanik_test_final from 
titanik_full_2 where survived isnull;
alter table titanik_test_final drop column survived;
 

для тестовой выборки и соответственно:

select * into titanik_train_final from 
titanik_full_2 where survived notnull;

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

CREATE OR REPLACE FUNCTION null_normalizer(tablename_source character varying)
  RETURNS integer AS
$BODY$
DECLARE
  pgst_object  REFCURSOR;
  fieldval character varying;
  count_null integer;
  field_avg float;
BEGIN
  OPEN pgst_object FOR EXECUTE 'select column_name from information_schema.columns where'||
  ' table_name='''||tablename_source||'''';
    LOOP
      FETCH pgst_object INTO fieldval;
      EXIT WHEN NOT FOUND;
      count_null := 0;
      EXECUTE 'select count(id) from '||tablename_source||' where "'||fieldval||'" isnull' into count_null;
      IF count_null > 0 THEN
	  raise notice 'field: %', fieldval;
	  EXECUTE 'select avg('||fieldval||') from '||tablename_source INTO field_avg; 
	  EXECUTE 'UPDATE '||tablename_source||' set '||fieldval||'= $1 where '||fieldval||' isnull' using field_avg;
      END IF;      
    END LOOP;
  RETURN 0;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

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

select null_normalizer('titanik_test_final');
select null_normalizer('titanik_train_final');

Результирующая таблица получилась достаточно большая и разреженная:

CREATE TABLE titanik_test_final
(
  id bigint,
  pclass double precision,
  age double precision,
  sibsp double precision,
  parch double precision,
  fare double precision,
  cabin_cnt double precision,
  ticket_number double precision,
  cabin_people_cnt double precision,
  "cabin_typeF" smallint,
  "cabin_typeB" smallint,
  "cabin_typeG" smallint,
  "cabin_typeC" smallint,
  "cabin_typeT" smallint,
  "cabin_typeD" smallint,
  "cabin_typeE" smallint,
  "cabin_typeA" smallint,
  "ticket_typeSW/PP" smallint,
  "ticket_typeC" smallint,
  "ticket_typePC" smallint,
  "ticket_typeAQ/3." smallint,
  "ticket_typeSC/A.3" smallint,
  "ticket_typeS.O.C." smallint,
  "ticket_typeS.O./P.P." smallint,
  "ticket_typeSC/AH" smallint,
  "ticket_typeSOTON/O2" smallint,
  "ticket_typeC.A." smallint,
  "ticket_typeW/C" smallint,
  "ticket_typeS.C./A.4." smallint,
  "ticket_typeFa" smallint,
  "ticket_typeLP" smallint,
  "ticket_typeSCO/W" smallint,
  "ticket_typeF.C." smallint,
  "ticket_typeA.5." smallint,
  "ticket_typeSC/AH Basle" smallint,
  "ticket_typeSC/A4" smallint,
  "ticket_typeS.C./PARIS" smallint,
  "ticket_typeS.O.P." smallint,
  "ticket_typeLINE" smallint,
  "ticket_typeSO/C" smallint,
  "ticket_typeP/PP" smallint,
  "ticket_typeAQ/4" smallint,
  "ticket_typeSC" smallint,
  "ticket_typeW.E.P." smallint,
  "ticket_typeSOTON/O.Q." smallint,
  "ticket_typeA/4" smallint,
  "ticket_typeSC/PARIS" smallint,
  "ticket_typeA. 2." smallint,
  "ticket_typeF.C.C." smallint,
  "ticket_typeS.P." smallint,
  "ticket_typePP" smallint,
  "ticket_typeC.A./SOTON" smallint,
  "embarkedC" smallint,
  "embarkedQ" smallint,
  "embarkedS" smallint,
  sexfemale smallint,
  sexmale smallint
)

Для выведение в текстовые файлы используем скрипт:

COPY titanik_train_final to '/tmp/titanik_train_final.csv';
COPY titanik_test_final to '/tmp/titanik_test_final.csv';

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

Автор: shadoof

Источник