2 заметки с тегом

statistics

Нормализация данных через запрос в SQL

Время чтения текста – 8 минут

Главный принцип анализа данных GIGO (от англ. garbage in — garbage out, дословный перевод «мусор на входе — мусор на выходе») говорит нам о том, что ошибки во входных данных всегда приводят к неверным результатам анализа. От того, насколько хорошо подготовлены данные, зависят результаты всей вашей работы.

Например, перед нами стоит задача подготовить выборку для использования в алгоритме машинного обучения (модели k-NN, k-means, логической регрессии и др). Признаки в исходном наборе данных могут быть в разном масштабе, как, например, возраст и рост человека. Это может привести к некорректной работе алгоритма. Такого рода данные нужно предварительно масштабировать.

В данном материале мы рассмотрим способы масштабирования данных через запрос в SQL: масштабирование методом min-max, min-max для произвольного диапазона и z-score нормализация. Для каждого из методов мы подготовили по два примера написания запроса — один с помощью подзапроса SELECT, а второй используя оконную функцию OVER().

Для работы возьмем таблицу students с данными о росте учащихся.

name height
Иван 174
Петр 181
Денис 199
Ксения 158
Сергей 179
Ольга 165
Юлия 152
Кирилл 188
Антон 177
Софья 165

Min-Max масштабирование

Подход min-max масштабирования заключается в том, что данные масштабируются до фиксированного диапазона, который обычно составляет от 0 до 1. В данном случае мы получим все данные в одном масштабе, что исключит влияние выбросов на выводы.

Выполним масштабирование по формуле:

Умножаем числитель на 1.0, чтобы в результате получилось число с плавающей точкой.

SQL-запрос с подзапросом:

SELECT height, 
       1.0 * (height-t1.min_height)/(t1.max_height - t1.min_height) AS scaled_minmax
  FROM students, 
      (SELECT min(height) as min_height, 
              max(height) as max_height 
         FROM students
      ) as t1;

SQL-запрос с оконной функцией:

SELECT height, 
       (height - MIN(height) OVER ()) * 1.0 / (MAX(height) OVER () - MIN(height) OVER ()) AS scaled_minmax
  FROM students;

В результате мы получим переменные в диапазоне [0...1], где за 0 принят рост самого невысокого учащегося, а 1 рост самого высокого.

name height scaled_minmax
Иван 174 0.46809
Петр 181 0.61702
Денис 199 1
Ксения 158 0.12766
Сергей 179 0.57447
Ольга 165 0.2766
Юлия 152 0
Кирилл 188 0.76596
Антон 177 0.53191
Софья 165 0.2766

Масштабирование для заданного диапазона

Вариант min-max нормализации для произвольных значений. Не всегда, когда речь идет о масштабировании данных, диапазон значений находится в промежутке между 0 и 1.
Формула для вычисления в этом случае такая:

Это даст нам возможность масштабировать данные к произвольной шкале. В нашем примере пусть а=10.0, а b=20.0.

SQL-запрос с подзапросом:

SELECT height, 
       ((height - min_height) * (20.0 - 10.0) / (max_height - min_height)) + 10 AS scaled_ab
  FROM students,
      (SELECT MAX(height) as max_height, 
              MIN(height) as min_height
         FROM students  
      ) t1;

SQL-запрос с оконной функцией:

SELECT height, 
       ((height - MIN(height) OVER() ) * (20.0 - 10.0) / (MAX(height) OVER() - MIN(height) OVER())) + 10.0 AS scaled_ab
  FROM students;

Получаем аналогичные результаты, что и в предыдущем методе, но данные распределены в диапазоне от 10 до 20.

name height scaled_ab
Иван 174 14.68085
Петр 181 16.17021
Денис 199 20
Ксения 158 11.2766
Сергей 179 15.74468
Ольга 165 12.76596
Юлия 152 10
Кирилл 188 17.65957
Антон 177 15.31915
Софья 165 12.76596

Нормализация с помощью z-score

В результате z-score нормализации данные будут масштабированы таким образом, чтобы они имели свойства стандартного нормального распределения — среднее (μ) равно 0, а стандартное отклонение (σ) равно 1.

Вычисляется z-score по формуле:

SQL-запрос с подзапросом:

SELECT height, 
       (height - t1.mean) * 1.0 / t1.sigma AS zscore
  FROM students,
      (SELECT AVG(height) AS mean, 
              STDDEV(height) AS sigma
         FROM students
        ) t1;

SQL-запрос с оконной функцией:

SELECT height, 
       (height - AVG(height) OVER()) * 1.0 / STDDEV(height) OVER() AS z-score
  FROM students;

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

name height zscore
Иван 174 0.01488
Петр 181 0.53582
Денис 199 1.87538
Ксения 158 -1.17583
Сергей 179 0.38698
Ольга 165 -0.65489
Юлия 152 -1.62235
Кирилл 188 1.05676
Антон 177 0.23814
Софья 165 -0.65489

Бесплатные курсы математики для аналитиков и инженеров данных

Время чтения текста – 6 минут

Сейчас в интернете доступно огромное количество платных образовательных материалов, которые обещают сделать из вас аналитика. Иногда это так, и некоторые программы действительно дают хороший набор скилл-сетов, которые необходимы аналитику.

Порой можно встретить точку зрения, что аналитику и вовсе не надо знать SQL и Python. С моей точки зрения, наоборот, следует стараться прокачивать себя в разных направлениях, в том числе и в хард-скиллах. Другой большой спор предполагает, что аналитику не нужна математическая база, и он может эффективно решать задачи, набравшись исключительно хард-скиллов. Я считаю, что это громадное заблуждение. Спойлер — далее в тексте у меня есть таблетка от этого.

К примеру, на мой взгляд, тяжело рассуждать о вероятности оттока, не понимая, что такое теория вероятностей. Сложно обсуждать медиану и нормальность распределения, не понимая математической статистики. Не разобраться в SVD-разложении, не понимая линейную алгебру, и не посчитать градиент функции, не разбираясь в математическом анализе. Некоторые возразят: ну так аналитику это и не требуется, ведь в Python / R / Matlab всё уже реализовано. Действительно, для стартовой позиции, наверное, всё так, можно взять готовый алгоритм, написать пару команд, и, вау, ты построил модель линейной регрессии. Но что делать дальше? Как разобраться в сути математического аппарата при изменении конкретных деталей модели?

Сегодня интернет позволяет нам подарить себе бесплатно второе высшее образование, и начинающему аналитику следует начать именно с него, прежде чем покупать курсы по анализу данных. Буквально недавно я прослушал ещё раз все фундаментальные курсы, которыми хочу поделиться. Прелесть в том, что все эти материалы абсолютно бесплатны. И несмотря на то, что всё нижеизложенное я изучал в ВУЗе 15 лет назад, повторить изученные материалы крайне полезно (все же за 15 лет многое забывается). Кроме того, получение подобных знаний тренирует тот самый аналитический склад ума и математическую подготовку.

В посте предлагаю вам бесплатные фундаментальные курсы от американских именитых ВУЗов, с которыми вы можете стартовать обучение по направлению аналитика данных. Единственное требование — знание английского языка. Но если у вас до сих пор нет этого, обязательно сперва изучите английский и после возвращайтесь к посту :)

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

МАТЕМАТИЧЕСКИЙ АНАЛИЗ (M.I.T.)

В английском языке имеет название Calculus, совершенно потрясающий по подбору материалов и объяснению курс от MIT в трех частях:

  1. Дифференцирование
  2. Интегрирование
  3. Система координат и ряды

ЛИНЕЙНАЯ АЛГЕБРА (Georgia Tech)

Курс в четырех частях от одного из ведущих мировых ВУЗов в Computer Science: Georgia Tech.

  1. Линейные уравнения
  2. Матричная алгебра
  3. Детерминанты и собственные значения
  4. Ортогональность, симметричные матрицы и SVD

ТЕОРИЯ ВЕРОЯТНОСТЕЙ И МАТЕМАТИЧЕСКАЯ СТАТИСТИКА (Georgia Tech)

Курс в четырех частях от одного из ведущих мировых ВУЗов в Computer Science: Georgia Tech.

  1. Введение в теорию вероятностей
  2. Случайные величины
  3. Введение в статистику
  4. Доверительные интервалы и гипотезы

ВЫЧИСЛЕНИЯ В PYTHON (Georgia Tech)

Курс в четырех частях от одного из ведущих мировых ВУЗов в Computer Science: Georgia Tech.

  1. Основы
  2. Управляющие структуры
  3. Структуры данных
  4. Объектные алгоритмы

R ДЛЯ АНАЛИЗА ДАННЫХ (Harvard)

Курс в семи частях от профессора из Гарварда

  1. Основы R
  2. Визуализация
  1. Теория вероятностей
  2. Вывод и моделирование
  3. Полезные инструменты
  4. Форматирование данных
  5. Линейная регрессия
  6. Машинное обучение
  7. Итоги курса
 Нет комментариев    519   11 мес   analysis   maths   probability   python   r   statistics