Эта серия посвящена анализу данных для поиска закономерностей.
В качестве примера использована одна из обучающих задач сообщества по анализу спортивных данных Kaggle. Хотя размер данных для задачи невелик, методы обработки, которые будут рассмотрены, вполне применимы для больших объемов данных.
После выполнения Часть 1 И Часть 2 были сформированы две таблицы, содержащие преобразованные данные.
titanik_test_3 и titanik_train_3. Их полевая структура отличается одним полем — выжил, значение которого нам предстоит определить для тестового набора данных.
Вот код, описывающий структуру таблицы 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
)
Теперь мы добавим в эту таблицу поле, которое будет означать, имеет ли свойство то или иное значение для данной строки.
Такие таблицы называются сводными, лишь немного отличаются от обычных, поля значений будут принимать либо 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 столбцов.
Необходимо его нормализовать и разделить тестовую и обучающую выборки по выжившим полям.
Существуют разные способы нормирования.
Различные методы анализа данных предъявляют разные требования к выборке.
Воспользуемся одним из самых простых способов — минимаксной нормализацией.
Суть такова: минимум будет 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
Теги: #Интеллектуальный анализ данных #kaggle #Большие данные #titanic #разреженная матрица #postgresql #сводные таблицы #sql #Интеллектуальный анализ данных #Большие данные
-
Управление Шторами Через Интерфейс Rs-485
19 Oct, 24 -
Ручной Телефон. Концепция
19 Oct, 24 -
Обзор 5 Популярных Почтовых Сервисов
19 Oct, 24
