📆 4 способа сравнить даты типа datetime

Работа с датой и временем - неотъемлемая и объемная часть аналитики. Но, несмотря на все возможности, которые предоставляет PostgreSQL, подсчет разницы между датами и временем может быть настоящим вызовом.

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

Дано: две даты: '2022-02-03 16:00:00', '2022-01-20 08:00:00'

Задание: найти разницу между ними в днях и/или часах.

Казалось бы, почему в PostgreSQL нет функции date_diff? Но на самом деле, PostgreSQL предоставляет даже больше функций для работы с датами, чем просто date_diff.


🔵 Первый вариант: считаем «в лоб»

Просто вычитаем одну дату из другой (первая карточка - 1 блок кода)

Получим интервал 14 days 08:00:00. Если нам понадобится получить только количество дней - запросто вытащим их с помощью extract() (первая карточка - 2 блок кода).

А если нам необходима разница в часах?

Extract (HOUR …) вернет нам 8, что очевидно - 16:00-8:00, но нам нужен другой результат, придется усложнять запрос (первая карточка - 3 блок кода).

И получим наши заветные 344 часа.


🔴 Второй вариант

PostgreSQL предлагает еще одну функцию - date_part. Extract() - это стандарт SQL, date_part() - специфичен для Postgres. И мы можем переписать наш запрос с помощью неё, но на самом деле, эти функции ничем не отличаются, кроме типа результата: extract() возвращает numeric, а date_part() - double precision.

Но это все выглядит так нагромождённо (вторая карточка - 1 блок кода), как будто должно быть что-то проще. И это так!


🟢 Третий вариант

Во-первых, у нас есть AGE(). Эта функция дает подробный ответ о том, сколько времени прошло между двумя событиями. Если вам нужен простой ответ и сразу - это ваш выбор (вторая карточка - 2 блок кода).


🟡 Четвертый вариант

Но это все-таки не совсем наш случай, мы не получим тут разницу в часах. Поэтому мы возвращаемся к extract(), только на этот раз доставать мы будем нетривиальное EPOCH (третья карточка).

Справка: EPOCH преобразовывает разницу между двумя временными точками в секунды, затем мы преобразовываем секунды обратно в ту шкалу времени, которая нам необходима.

💥Этот вариант более эффективен, чем date_part(), а вывод более детализирован, чем с AGE(). При этом учитываются разное число дней в месяцах, изменения часовых поясов (если бы они у нас были) и переходы на летнее время.

Конечно, не нужно применять все эти методы одновременно. Важно выбрать наиболее подходящий способ к вашей ситуации! Старайтесь оставлять ваши запросы простыми там, где это возможно, и усложнять там, где этого не избежать.


⏰ Мы в Симуляторе "Аналитик данных" целый урок посвятили работе с датой и временем в SQL. Попробуйте демо 🙃

🔥 3 способа собрать статистику по сгруппированным строкам

Как аналитики данных, вы наверняка сталкивались с непростой задачей обработки огромных данных в Pandas, пытаясь вычленить какой-то смысл из них. Например, анализируя данные продаж, мы можем сравнивать средние продажи по магазинам, но что нам это даст без других метрик? Зная, например, количество продаж по каждому из сгруппированных магазинов, мы бы лучше понимали изменчивость результатов.

🔵 Простой подход

К счастью, Pandas умеет легко группировать данные в подмножества и доставать полезную статистику из каждой группы. Давайте начнем с простого примера: мы хотим сгруппировать данные по магазинам и датам, и получить некоторые статистические показатели по другим столбцам. Можно сделать это с помощью groupby (карточка 1)

Однако, если некоторые из столбцов содержат нулевые значения, при вычислении, например, среднего значения, Pandas просто отбросит их и даже не сообщит нам об этом, что даст фактически неверное количество обработанных строк.

🔴 Более точный подход

Чтобы такого не происходило, и чтобы иметь больше контроля над агрегирующими функциями, можем немного немного изменить подход (карточка 2).

Сначала мы получаем размер каждой группы, используя метод size(), который возвращает серию с количеством записей в каждой группе. Затем преобразуем его в DataFrame с помощью to_frame() и присваиваем ему имя. И далее с помощью agg() вычисляем среднее, медиану и минимальное значение для каждой группы. А чтобы все объединить используем join().

Так мы можем получать статистику по одному или сразу нескольким столбцам.

💡 Но у нас есть для вас кое-что еще!

В дополнение к традиционным агрегациям существует удивительный инструмент - Skimpy, который дает исчерпывающую статистическую сводку, всего за одну строку кода. Хотите посмотреть? (карточка 3 и 4)

Skimpy - отличный инструмент для EDA, он показывает куда больше, чем просто describe(), поэтому очень рекомендуем попробовать его самостоятельно. Всегда лучше один раз попробовать, чем сто раз увидеть. Безусловно, важно уверенно оперировать агрегациями, но и упростить себе работу с инструментами вроде skimpy тоже бывает полезно.

#python #pandas

🔥 Cекреты функции sorted() в Python

Итак, в Python существует функция sorted(), которая используется для сортировки данныx. sorted() упорядочивает данные таким образом, чтобы было легче провести анализ и извлечь нужную информацию. Например, в аналитике, сортировка клиентов по возрасту, территориальной принадлежности и другим критериям - далеко не редкая задача.

Для сортировки списка объектов по одному из их атрибутов, как раз можем использовать функцию sorted() в Python. Она отсортирует любой итерируемый объект: список, кортеж или множество.

🟧 Например, у нас есть список клиентов с разными атрибутами, у каждого - имя, возраст и доход:

client_list = [
    {'name': 'John', 'age': 25, 'income': 50000},
    {'name': 'Mary', 'age': 30, 'income': 70000},
    {'name': 'Bob', 'age': 35, 'income': 40000},
    {'name': 'Alice', 'age': 27, 'income': 60000}
]

И мы хотим отсортировать этот список по возрастанию возраста. Мы не получим нужный результат если просто передадим наш список в sorted(). Нужно дополнительно указать ключ сортировки.

>> Ключ сортировки - это функция, которая принимает каждый элемент массива и возвращает значение, по которому будет производиться сортировка.

🟨 В нашем случае мы будем использовать лямбда-функцию, которая возвращает значение атрибута 'age' или 'income' каждого клиента в списке. Мы напишем key=lambda x: x['age'], чтобы указать, что мы хотим отсортировать список по возрастанию возраста клиентов. Если понадобится отсортировать список по другому атрибуту, то мы поменяем ключ на key=lambda x: x['нужный атрибут'].

Итак, используем лямбда-функцию, чтобы указать ключ сортировки. Она вернет значение 'age' для каждого клиента в списке:

sorted(client_list, key=lambda x: x['age'])

🟦 Аналогично, если вы хотите отсортировать список клиентов по убыванию их доходов - добавьте параметр reverse=True:

sorted(client_list, key=lambda x: x['income'], reverse=True)

🟪 Абсолютно таким же образом с помощью sorted() мы сможем отсортировать список объектов класса по некоторому атрибуту. Нам просто понадобится передать в key нужный нам атрибут: key=lambda x: x.age.

Таким образом, функция sorted() сокращает время на написание дополнительного кода и этим облегчает работу.

#python

🔥 Считаем скользящее среднее в PostgreSQL

Расчет скользящего среднего - один из наиболее распространенных методов анализа временных рядов. В PostgreSQL для решения этой задачи можно и нужно использовать оконные функции.

🟡 Для примера, допустим у нас есть таблица sales с данными о продажах за определенный период времени:

date        | amount
------------+-------
2022-01-01  | 100
2022-01-02  | 200
2022-01-03  | 150
2022-01-04  | 50
2022-01-05  | 300

Чтобы посчитать скользящее среднее за последние три дня, удобнее всего использовать оконную функцию AVG(), указав определенный диапазон строк (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW):

SELECT 
  date, 
  amount, 
  AVG(amount) OVER (
    ORDER BY date
    ROWS BETWEEN 2 PRECEDING 
    AND CURRENT ROW
  ) AS avg
FROM sales;

В результате получим таблицу, где для каждой строки будет рассчитано значение скользящего среднего за последние три дня:

date        | amount | avg
------------+--------+--------
2022-01-01  | 100    | 100.00
2022-01-02  | 200    | 150.00
2022-01-03  | 150    | 150.00
2022-01-04  | 50     | 133.33
2022-01-05  | 300    | 166.67

🔵 Давайте разберем более подробно выражение ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. Эта конструкция часто вызывает вопросы.

Данный параметр определяет рамки (окно) для выполнения оконных функций. В частности, он указывает, какие строки будут включены в вычисление агрегирующей функции.

Например, в выражении ROWS BETWEEN 2 PRECEDING AND CURRENT ROW используется два ключевых слова - PRECEDING и CURRENT. Ключевое слово CURRENT определяет текущую строку, а PRECEDING - N предшествующих строк. Существует еще FOLLOWING, что означает - следующие строки.

Таким образом, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW означает, что в окно будет включена текущая строка и две предыдущие строки.

Этот параметр может быть любым, например:

- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW - включить все строки от начала таблицы до текущей строки
- ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING - включить текущую строку и ее соседние строки
- RANGE BETWEEN '1 day' PRECEDING AND '1 day' FOLLOWING - включить все строки, которые находятся в интервале от одного дня до другого

🟢 Вот так просто использование оконных функций позволяет рассчитывать скользящее среднее и другие агрегирующие функции без создания временных таблиц. Главное, знать что и как использовать 🙂

#sql

🔥 Чек-лист по созданию гипотез

🎓 Недавно наш студент поделился интересными мыслями о том, как создавать качественные гипотезы. И знаете что? Он был настолько крут, что мы решили поделиться его идеями с вами.

Хотите узнать, какой должна быть гипотеза? Тогда читайте дальше!

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

Например, "Пиар влияет на продажи" или "У компании должен быть солидный сайт" - никак не гипотезы. Без проверки они останутся домыслами.

✅ Второй критерий - влияние на метрики. Хорошая гипотеза должна влиять на какую-то метрику. Это значит, что мы можем понять, подтвердилась ли она или нет, изменилась ли метрика в ходе эксперимента.

Например, "Сделаем рассылку по базе с акцией" - плохая гипотеза, а "Если сделаем рассылку, получим 10 лидов" - хорошая гипотеза.

✅ Рискованность. Хорошая гипотеза должна быть неочевидной и рискованной. Чем более неожиданным будет результат, тем больше шансов на успех.

Например, "Если мы уже знаем, что с рассылки будет 10 лидов", то это не гипотеза, а факт.

✅ Быстрота проверки. Хорошая гипотеза должна быть проверяема за короткий период времени. Если эксперимент займет слишком много времени, мы потратим слишком много ресурсов, а результаты могут потерять свою актуальность.

Например, "Давайте за полгода переделаем сайт, чтобы конверсия выросла на 2 процентных пункта" - плохая гипотеза, а "Давайте изменим оффер на сайте за 1 час, чтобы конверсия выросла на 0.2 процентных пункта за неделю" - хорошая гипотеза.

✅ Дешевизна. Если проверка гипотезы стоит слишком дорого, то это может оказаться неэффективным для бизнеса. Поэтому лучше делать ставку на более дешевые эксперименты и быстрые результаты.

Например, если вы хотите сделать мобильное приложение за 1.5 млн.рублей, чтобы понять, будут ли востребованы цифровые стилисты - это плохая гипотеза. Если же за 5 тысяч рублей, а лучше бесплатно, соберете лендинг на Тильде, чтобы проверить, то это - хорошая гипотеза.

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

❗️ Эти советы точно помогут вам лучше понимать формирование гипотез и стать настоящими мастерами. Ведь проверка гипотез - это ключевой инструмент для роста бизнеса, а значит, и вашей карьеры. И пусть каждый эксперимент приближает вас к вашим целям!


📈 На Симуляторе "Аналитик данных" мы особое внимание уделяем способам расчета и пониманию продуктовых метрик, проверке гипотез и проведению A/B тестов. Попробуйте демо-главу!

#analytics #продуктовые_метрики

🔥 Апгрейдим Pandas: создаем условные столбцы без лишних циклов

Кто работал с большими объемами данных в Pandas, наверняка знает, что обработать столбцы по условию может оказаться довольно затратной операцией. Мы часто слышим: используйте векторизацию! А что это, где это найти? Объясняем.

> Векторизация - это способ обработки данных, при котором операции применяются не поочередно к каждому элементу массива, а сразу ко всему массиву. Многие методы pandas, в общем говоря, уже векторизованы и вам не нужно об этом задумываться, НО!

❓ Каким методом вы бы воспользовались для фильтрации столбца по условию?

🔵 Самый распространенный способ

Очень часто для этого используют apply(), да и не только для фильтрации, а и для применения какой-либо функции к столбцу. Очень нужный и полезный метод, иногда незаменимый.

Тем не менее apply() - это не что иное, как усовершенствованный цикл. И из-за этого теряется вся суть векторизации.

💡 Давайте покажем вам как можно создать условный столбец более эффективно - векторизованным способом:

Для начала создадим DataFrame с 10 миллионами случайных чисел от 0 до 1:

import pandas as pd
import numpy as np

df = pd.DataFrame(np.random.random((10**7,1)).round(2), columns = ['col1'])

🔴 Сразу посмотрим как поведет себя apply(). Определим функцию, которая для каждого числа будет возвращать 'Class A', если число больше 0.5, и 'Class B' в противном случае:

def assign_class(num):
  if num > 0.5:
    return 'Class A'
  return 'Class B'

Передадим её в apply() и замерим время:

%timeit a = df.col1.apply(assign_class)

# 1.96 s ± 302 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

И чем больше будут данные, тем более печальным будет время. ☹️

🟢 Более удобный способ

Выход из ситуации - np.where(). Он позволяет обрабатывать все гораздо быстрее, как раз являясь одним из многих инструментов векторизации:

%timeit a = np.where(df['col1']>0.5, 'Class A', 'Class B')

# 282 ms ± 14.8 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

Существенное преимущество налицо!

Базовый синтаксис: numpy.where(condition [, x, y]).

Condition - условие; если оно выполняется то будут выбраны элементы из x; в противном случае - если это false, будут взяты элементы из y.

🟣 Так что, если хотите обработать большие объемы данных в Pandas быстро и без лишних циклов, однозначно используйте np.where(). А мы, со своей стороны cделаем все возможное, чтобы с такими инструментами вас знакомить: в Симуляторе “Аналитик данных” в модуле Python мы 2 главы уделяем самым популярным библиотекам Python: Pandas и Numpy!

#python #pandas #numpy

🚀 Быстрый лайфхак для всех Юпитер-юзеров!

Признавайтесь все, кто работает с Jupyter Notebook, всегда ли у ваших ноутбуков осмысленные имена? Или у вас тоже есть огромная стопка блокнотов "Без названия"?

Всё бы наверное ничего, но как быть, когда вам срочно нужен определенный код из неизвестно какого ноутбука? Ох и работа вам предстоит в таком случае!

🟢 Но у нас есть маленький хитрый способ, который поможет сэкономить кучу времени и уберечь от ручного поиска!

nbcommands - набор инструментов, предоставляющий команды для взаимодействия с ноутбуками прямо из терминала. С помощью nbcommands можно быстро искать код, просматривать ячейки, объединять блокноты и многое другое.

Попробуем? Для начала запустите следующую команду в терминале:

!pip install nbcommands

А затем используйте nbgrep для поиска конкретного кода по всем вашим Jupyter Notebook:

nbgrep "import pandas" ./

./ означает: ищем по всем ноутбукам текущей директории, а "import pandas" - строка, которую ищем. Также можно передать имя определенного ноутбука и искать только в нем, например:

nbgrep "import pandas" notebook.ipynb

🟣 Кроме того, nbcommands также предоставляет несколько интересных команд: быстрый просмотр первых или последних ячеек в ноутбуке, объединение ноутбуков и другие. Планируются и новые улучшения, например: поиск различий в двух ноутбуках и интерактивное редактирование.

Так вот мы к чему, старайтесь давать вашим ноутбукам осмысленные имена. Конечно у всех были беззаботные времена с 3 ноутбуками, в которых не запутаться, но изначально такая стратегия губительна!

Но если уже завал - вы знаете чем пользоваться для поиска бриллиантов в недрах ваших блокнотов! 😉

#лайфхак

🔥 Оператор ROLLUP в PostgreSQL

Одним из самых основных операторов в любом диалекте SQL является GROUP BY. Любой, кто часто взаимодействует с базами данных, наверняка использовал его тысячи раз для подсчета всевозможных агрегаций (сумм, средних значений и тд).

Но в аналитике часто требуется посмотреть на статистические показатели в разных «разрезах». Например, для анализа продаж интересно узнать сколько товаров продано по каждому бренду и сегменту, а также общее количество. Такой, промежуточный итог. Думаете мы будем рассказывать об Excel? Могли бы, но нет! 🙃

Мы расскажем о достаточно редко используемой, но очень полезной вещи в PostgreSQL - ROLLUP. Это в некоторым смысле часть GROUP BY, которая формирует промежуточные итоги для каждого указанного элемента и общий итог.

🟢 Посмотрим на примерах

Допустим, нам необходимо получить общую сумму товаров, а также сумму проданных по каждому бренду и сегменту. Мы можем написать следующий запрос:

SELECT
    brand,
    segment,
    SUM(quantity)
    FROM
        sales
    GROUP BY
        ROLLUP (
        brand, segment)
    ORDER BY
        brand,
        segment;

Результаты запросов смотрите в карточках под постом.

При использовании ROLLUP строки со значениями NULL и будут промежуточными итогами, а последняя строка - общим итогом.

❗️Причем обратите внимание на иерархию: сначала brand, потом segment.

Мы можем их и поменять:

SELECT
    segment,
    brand,
    SUM(quantity)
    FROM
        sales
    GROUP BY
        ROLLUP (
        segment, brand)
    ORDER BY
        segment,
        brand;

🔵 Синтаксис

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

SELECT
    c1,
    c2,
    c3,
    aggregate(c4)
    FROM
        table_name
    GROUP BY
        ROLLUP (
        c1, c2, c3);

Данные из FROM и WHERE группируются отдельно для каждого заданного набора группировки. Затем для каждой группы вычисляются агрегатные функции, как и при использовании простого GROUP BY, а в конце результаты объединяются.

Например, для GROUP BY ROLLUP(c1, c2, c3) результатом запроса будет объединение результатов GROUP BY по:

- c1, c2, c3;
- c1, c2;
- c1;
- все строки выборки.

📅 ROLLUP с датами

Довольно часто ROLLUP используют с датами, давайте посмотрим на такой пример, чтобы закрепить:

SELECT
    EXTRACT(YEAR FROM rental_date)  y,
    EXTRACT(MONTH FROM rental_date) M,
    EXTRACT(DAY FROM rental_date)   d,
    COUNT(rental_id)
    FROM
        rental
    GROUP BY
        ROLLUP (
        EXTRACT(YEAR FROM rental_date),
        EXTRACT(MONTH FROM rental_date),
        EXTRACT(DAY FROM rental_date)
        );

🟣 На самом деле, ROLLUP редко встретишь в обучениях или статьях - довольно специфическая вещь. Но она позволяет агрегировать не только по столбцам, но и по подгруппам и по определенным наборам управлять иерархией. Формируя такие запросы мы можем куда подробнее видеть взаимосвязь данных и их комбинации, что дает нам большее понимание происходящего.

#sql

🔥 Нестандартная ситуация со словарями

Мы знаем, что вы - любите Python, а еще больше любите необычные фишки. Но знали ли вы, что при работе со словарями могут возникать довольно не интуитивные ситуации?

❓ Посмотрите, чему будет равен my_dict?

my_dict = {
    1.0: 'One (float)',
    1: 'One (int)',
    True: 'One (bool)',
    '1': 'One (string)'
}

# {1.0: 'One (bool)', '1': 'One (string)'}

Несмотря на добавление 4 отдельных ключей в словарь, можете ли вы объяснить, почему он оставляет только два из них?

✅ Мы знаем, что ключи должны быть уникальными и неизменяемыми. В данном случае нет никаких сомнений в том, что 1.0, 1 и True имеют разные типы данных.

type(1.0), type(1), type(True)

# (float, int, bool)

Если посмотреть на их id, убедимся что и они различны:

id(1.0), id(1), id(True)

# (139644507527152, 139645051222320, 9758592)

❌ Но дело не только в этом! Ключи должны быть еще хешируемы. Для поиска ключа, Python использует именно его хэш-значение, а не id. Значит посмотрим на их хэш-значения:

hash(1.0), hash(1), hash(True)

# (1, 1, 1)

Вот и получается, поскольку они имеют одно и то же хэш-значение, словарь рассматривает их как одни и те же ключи.

📎 Но обратите еще внимание на итоговые ключ и значение: ключ - 1.0, в то время как значение соответствует ключу True.

❓Как так получилось?

Дело в том, что сначала 1.0 добавляется в качестве ключа, а значение устанавливается 'One (float)'. Затем, добавляя ключ 1, python распознает его как эквивалентное хэш-значение. И значение, соответствующее 1.0, перезаписывается на 'One (int)', в то время как ключ (1.0) сохраняется как есть.

Наконец, при добавлении True снова получаем эквивалентность хэша с существующим ключом 1.0. И снова, значение, которое изначально было 'One (float)' и обновлено до 'One (int)' на предыдущем шаге, перезаписывается на 'One (bool)'.

❓А почему сохранен строковый ключ '1'?

Тут все просто: он имеет уникальное хеш-значение, отличное от других ключей.

💥 Надеемся, что этот пост дал вам чуть более полное понимание работы словарей. Приходите к нам на Симулятор "Аналитик данных", где мы с нуля обучаем SQL и Python и делимся полезными фишками!

#python