Регулярные выражения как способ решения задач в SQL

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

Использование регулярных выражений для выбора определенных ячеек таблицы используется в SQL не так часто, как могло бы. И очень зря — этот инструмент легко позволяет найти в таблице нужные значения, так как использует шаблон для поиска последовательности метасимволов в тексте. Такие задачи встречаются как в различных тренажерах или на собеседованиях на позиции аналитика, так и в реальной практике аналитиков, которые работают в базах SQL. Подобные шаблоны для поиска определенных элементов и последовательностей в тексте используются в самых разных областях. Например, на многих сайтах существует проверка email-адреса, который вы вводите при регистрации, на соответствие стандартному шаблону.

Как это сделать, мы разберёмся постепенно, а пока давайте начнем с самого начала: с определения.

Что такое «регулярное выражение»?
Регулярное выражение — последовательность букв и/или символов, которая может встречаться в слове. Например, есть достаточно простое регулярное выражение “bat”. Оно читается как буква b, за которой следует буква a и t, и этому шаблону соответствуют такие слова, как, bat, combat и batalion.
Давайте разберем несколько типовых задачек, чтобы вам было понятнее, как правильно работать с регулярными выражениями в SQL. Для решения всех задач, которые мы сегодня рассмотрим, мы будем использовать функцию regexp_matches(), которая будет сравнивать значения в ячейках с шаблоном, который задается внутри этой функции.

Количество гласных букв в выражении

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

with example_table as (select * from (values (1, 'google'), (2, 'yahoo'), (3, 'bing'), (4, 'rambler')) 
as map(id, source_type))

select source_type, count(1) from (
select *, regexp_matches(source_type,'([aeiou])','g') as pattern from example_table ) as t
group by source_type

Количество согласных букв в выражении

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

with example_table as (select * from (values (1, 'google'), (2, 'yahoo'), (3, 'bing'), (4, 'rambler')) 
as map(id, source_type))

select source_type, count(1) from (
select *, regexp_matches(source_type,'([^aeiou])','g') as pattern from example_table ) as t
group by source_type

Количество цифр в выражении равно 3

Если нужно найти конкретное число определенных символов в выражении, то в конце запроса нужно указать оператор HAVING.

with example_table as (select * from (values (1, '1a2s3d'), (2, 'qw12e'), (3, 'q56we1651qwe'), (4, 'qw4e2')) 
as map(id, source_type))

select source_type, COUNT(*) from (
select *, regexp_matches(source_type,'\d','g') as pattern from example_table ) as t
GROUP BY source_type
HAVING COUNT(*) = 3

В номере телефона есть два дефиса

Теперь давайте перейдем к более конкретным запросам, которые могут пригодиться в реальной практике. Например, у аналитика может стоять задача найти все номера телефона, в которых присутствует два или более дефисов.
В первом блоке кода мы создаем тестовую таблицу, затем считаем количество дефисов в каждой ячейке (ячейки без дефисов не включаются в финальную таблицу), а после этого проставляем значения True/False относительно условия на количество дефисов. Сделать это можно с помощью оператора CASE WHEN COUNT ().

with example_table as (
  select * from (
    values 
    (1, '8931-123-456'), 
    (2, '8931123-456'), 
    (3, '+7812123456'), 
    (4, '8-931-123-42-24')
  )
as map(id, source_type))

select source_type, CASE WHEN COUNT(1) >= 2 THEN 'True' ELSE 'False' END from (
select *, regexp_matches(source_type,'-','g') as pattern from example_table ) as t
GROUP BY 1

Все имена, которые написаны с большой буквы

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

with example_table as (
  select * from (
    values 
    (1, 'alex'), 
    (2, 'Alex'), 
    (3, 'Vasya'), 
    (4, 'petya')
  )
as map(id, source_type))

select source_type from (
select *, regexp_matches(source_type,'^[A-Z]','g') as pattern from example_table ) as t
GROUP BY 1

Вывести номера телефонов, которые попадают под паттерн +71234564578

Последней задачей мы разберем поиск телефонных номеров в списке. Для этого нам нужно найти те значения, которые начинаются со знака “+”, затем идет цифра 7 и 10 любых цифр после этого.

with example_table as (
  select * from (
    values 
    (1, '+7(931)1234546'), 
    (2, '+79312991809'), 
    (3, '89311234565'), 
    (4, '244-02-38')
  )
as map(id, source_type))

select source_type from (
select *, regexp_matches(source_type,'^\+7[0-9]{10}','g') as pattern from example_table ) as t
GROUP BY 1

Вывести все настоящие email-адреса

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

with example_table as (
    select * from (
    values
        (1, 'email.asd@ya.ru'),
        (2, 'something@new.ru'),
        (3, '@ya.ru'),
        (4, 'asdasd'),
        (5, '_asdasdasd@mail.ru'),
        (6, 'asd_asdas@mail.ru'),
        (7, '.asdasd@mail.ru'),
        (8, '007asd@email.com')
        ) as map(id, source_type)
)
​
select source_type 
from (
    select source_type, regexp_matches(source_type, '^[^_.0-9][a-z0-9._]+@[a-z]+\.[a-z]+$')
    from example_table ) as t

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

Три способа рассчитать накопленную сумму в SQL

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

Расчет накопленной (или кумулятивной, что то же самое) суммы SQL — это очень распространенный запрос, который часто используют в анализе финансов, динамики прибыли и прочих показателей компании. В сегодняшней статье вы узнаете, что такое накопленная сумма и как можно написать SQL-запрос для ее вычисления.

Если вы вдруг являетесь начинающим пользователем SQL, то давайте, как в школьной задаче, поймем, что нам дано и что нам необходимо найти. Накопленная сумма — это совокупная сумма предыдущих чисел в столбце. Давайте посмотрим на пример ниже, чтобы точно знать, какой результат мы ожидаем увидеть в итоге. Итак, существует таблица leftjoin.daily_sales_sample, в которой есть всего два столбца date и revenue. По столбцу revenue нам нужно рассчитать накопленную сумму и записать результат в отдельный столбец.

Что у нас есть?

Date Revenue
10.11.2021 1200
11.11.2021 1600
12.11.2021 800
13.11.2021 3000

Что мы хотим найти?

Date Revenue Cumulative Revenue
10.11.2021 1200 1200 ↓
11.11.2021 1600 2800↓
12.11.2021 800 3600 ↓
13.11.2021 3000 6600

На графике две этих переменных выглядят следующим образом:

Итак, без лишних слов, давайте приступать к решению задачи.

Способ 1 — Идеальный — Используем оконные функции

Итак, если в базе данных можно пользоваться оконными функциями, то жизнь хороша и прекрасна. С их помощью можно написать простой запрос, который будет суммировать значения из столбца revenue по мере увеличения даты и сразу вернет нам таблицу с кумулятивной суммой в столбце, который мы назвали total.

SELECT
	date,
	revenue,
	SUM(revenue) OVER (ORDER BY date asc) as total
FROM leftjoin.daily_sales_sample 
ORDER BY date;

Способ 2 — Хитрый — Решение без оконных функций

Вполне возможно, что вам понадобится решить такую задачу без использования оконных функций. К примеру, если вы используете MySQL (до 8 версии) или любую другую БД, в которой оконных функций нет. Тогда решение задачи чуть усложняется. Однако, вы ведь знаете, что нет ничего невозможного?
Чтобы провернуть все то же самое без оконных функций, нужно использовать INNER JOIN для присоединения таблицы к себе самой. Так, к каждой строке таблицы мы присоединяем строки, которые соответствуют всем предыдущим датам до текущей даты включительно. В нашем примере, для 10 ноября — 10 ноября, для 11 ноября — 10 и 11 ноября и так далее. Промежуточный запрос будет выглядеть вот так:

SELECT * 
FROM leftjoin.daily_sales_sample ds1 
INNER JOIN leftjoin.daily_sales_sample ds2 on ds1.date>=ds2.date
ORDER BY ds1.date, ds2.date;

А его результат:

Date 1 Revenue 1 Date 2 Revenue 2
10.11.2021 1200 10.11.2021 1200
11.11.2021 1600 10.11.2021 1200
11.11.2021 1600 11.11.2021 1600
12.11.2021 800 10.11.2021 1200
12.11.2021 800 11.11.2021 1600
12.11.2021 800 12.11.2021 800
13.11.2021 300 10.11.2021 1200
13.11.2021 300 11.11.2021 1600
13.11.2021 300 12.11.2021 800
13.11.2021 300 13.11.2021 300

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

SELECT
	ds1.date,
	ds1.revenue,
	SUM(ds2.revenue) as total
FROM leftjoin.daily_sales_sample ds1 
INNER JOIN leftjoin.daily_sales_sample ds2 on ds1.date>=ds2.date
GROUP BY ds1.date, ds1.revenue
ORDER BY ds1.date;

Способ 3 — Специфический — Решение с помощью массивов в ClickHouse

Если вы используете Clickhouse, то в этой системе есть специальная функция, которая может помочь рассчитать кумулятивную сумму. Для начала, нам нужно преобразовать все столбцы таблицы в массивы и рассчитать показатель «Moving Sum» для столбца revenue.

SELECT groupArray(date) dates, groupArray(revenue) as revs, 
groupArrayMovingSum(revenue) AS total
FROM (SELECT date, revenue FROM leftjoin.daily_sales_sample
	  ORDER BY date)

Спасибо Дмитрию Титову из Altinity за комментарий про сортировку в подзапросе

Так, мы получим три массива значений:

dates revs total
[’10.11.2021’,’11.11.2021’,’12.11.2021’,’13.11.2021’] [1200, 1600, 800, 300] [1200, 2800, 3600, 3900]

Но три массива, которые записаны в ячейки — это не то, что мы хотим получить, хотя значения этих массивов уже абсолютно соответствуют искомому результату. Теперь массивы нужно привести обратно к табличному виду с помощью функции ARRAY JOIN.

SELECT dates, revs, total FROM
(SELECT groupArray(date) dates, groupArray(revenue) as revs, 
groupArrayMovingSum(revenue) AS total
FROM (SELECT date, revenue FROM leftjoin.daily_sales_sample
	  ORDER BY date)) as t
ARRAY JOIN dates, revs, total;

Бонус — Оконные функции в Clickhouse

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

SELECT date, runningAccumulate(revenue)
  FROM 
  (
    SELECT date, sumState(revenue) AS revenue
    FROM leftjoin.daily_sales_sample
    GROUP BY date 
    ORDER BY date ASC
  )
ORDER BY date

Если вы столкнетесь с необходимостью рассчитать кумулятивную сумму в SQL, то теперь вы сможете решить эту задачу, в какой бы системе управления баз данных ни была организована работа :)

 1 комментарий    1404   1 мес   clickhouse   mysql   postgresql   sql

How to: Yandex Datasphere

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

Yandex DataSphere — среда для ML-разработки, которая сочетает в себе привычный интерфейс Jupyter Notebook, технологию бессерверных вычислений и возможность бесшовного использования разных конфигураций вычислительных ресурсов.

Большую часть информации, которой мы с вами поделимся, мы почерпнули из закрытого практикума, который проводила команда Яндекса, куда был также приглашен спикер Александр Волков из компании Glowbyte. В процессе работы мы опирались на запись практикума, который был посвящен нюансам работы в Yandex DataSphere, а также задавали вопросы Александру и специалистам Яндекса напрямую, когда заходили в тупик и нуждались в помощи профессионала.

Теперь, пройдя долгий и сложный путь от задумки до реализации, мы готовы рассказать, как задеплоить модель с помощью одного из сервисов Yandex.Cloud — DataSphere.

Создание проекта и ноутбука

  1. Для начала, создаем проект DataSphere в Yandex.Cloud.
  2. В проекте создаем ноутбук.
     

     
  3. В целом, работа с ноутбуками в DataSphere будет интуитивно понятна, если вы уже сталкивались, например, с Jupyter Notebook. Стоит лишь отметить, что можно выбрать конфигурацию машины, на которой будет работать ваш ноутбук. По дефолту стоит самая простая.
     

     

Еще, в начале использования сервиса важно понимать, как происходит тарификация. Если коротко, то размер оплаты зависит от времени использования сервиса и от мощности выбранной машины. При прогоне ячеек учитывается только время, затраченное на вычисления, при развертывании — время от создания деплоя до его удаления (даже если сервис простаивает). После регистрации Yandex.Cloud предлагает пробный период, которого вполне хватит для тестовых проектов. А дальше, конечно, придется платить.

Загрузка модели

Мы будем использовать модель-заглушку для демонстрации работоспособности деплоя, но для начала нам хотелось бы отдельно остановиться на нюансах загрузки обученной модели в проект DataSphere.

  1. Можно воспользоваться, например, Яндекс.Диском. Для этого надо зарегистрировать приложение в Яндекс ID, после запомнить его ID и пароль.
  2. Для безопасности пароль можно положить в “секреты проекта”.
  3. Для загрузки файлов модели можно воспользоваться следующим кодом:
    !mkdir model
    import os
    from cloud_ml.storage.api import Storage
    disk = Storage.ya_disk(application_id=<your-app-id>, 
                           application_secret=os.environ[<your-secret-name>])
    disk.get_dir(<path-to-your-model-folder-at-ydisk>, './model')
    Если же необходимые вам файлы лежат в Github-репозитории, то все еще проще — надо только выполнить в ноутбуке команду:
    !git clone ‘<github-repository-url>’

Код модели

Давайте сделаем простой генератор случайных целых чисел, принимающий на вход границы:

input_data = {'left_bound': 1, 'right_bound': 5}
from random import randint 

def generate_value(input_data):
         return {'generated_value': randint(input_data['left_bound'], 
                                            input_data['right_bound'])}

output_data = generate_value(input_data)

Первую строку вынесем в отдельную ячейку (это нужно для деплоя).
При необходимости, вы можете загрузить специальные библиотеки/версии библиотек с помощью команды %pip install...

Деплой

  1. Для начала, нужно создать Docker-образ. Стоит отметить несколько моментов:
    • Build path — это необязательное поле, но при этом его значение не выставлено по дефолту, так что советуем указать хотя бы ‘.’.
    • Docker file будет сгенерирован за вас, однако, вы можете его дополнить так: 
      • Скопировать файлы из своего проекта в Docker-образ.
        Например, команда ADD model model скопирует файлы из папки model (первый аргумент) в вашем проекте в директорию /model/ (первый слэш важен!) в Docker-образе (второй аргумент). Команду стоит прописать вне блока RUN\. 
      • Добавить установку необходимых вам библиотек с помощью команды pip install … &&\ (прописать это нужно внутри блока RUN\). 
  2. Затем нужно создать реестр для хранения Docker-образов (если у вас его еще нет). Для этого переходим в рабочий каталог в облаке, выбираем сервис Container Registry и там создаем реестр.
  3. Далее нужно провести аутентификацию в Container Registry (если вы этого еще не сделали). Это можно сделать разными способами, которые описаны в документации. Мы воспользуемся способом аутентификации через сервисный аккаунт с помощью авторизованных ключей. Не забудьте его создать дополнительно, если решите использовать тот же способ. Процесс аутентификации достаточно прост:
    • Сначала нужно сгенерировать ключи с помощью Yandex.Cloud CLI:
      yc iam key create --service-account-name <service-account-name> -o key.json
    • Затем провести авторизацию с помощью этих ключей (Docker должен быть запущен):
      cat key.json | docker login --username json_key --password-stdin cr.yandex
    • Далее нужно записать содержимое файла key.json в “секрет проекта”.
    Полезные ссылки по работе с YC CLI и сервисными аккаунтами:
    Работа с командной строкой
    Работа с сервисными аккаунтами
     
  4. После этого можно запушить Docker-образ в реестр. Для этого нужно выполнить следующую команду в ноутбуке (да-да, решетка в начале, все верно):
    #!:docker-publish <datasphere-image-name>:<datasphere-image-tag> cr.yandex/<cloud-registry-path>:<tag> 
    Где:
    <datasphere-image-name> – Репозиторий, указанный при создании Docker-образа 
    <datasphere-image-tag> – Тег, указанный при создании Docker-образа 
    <cloud-registry-path> – Идентификатор реестра, который можно посмотреть в Container Registry
    <tag> – Тег, который вы можете придумать сами
  5. Вам предложат ввести Registry username и Secret name of password. Если вы проводили аутентификацию тем способом, что был описан выше, то сначала нужно ввести json_key, а затем название секрета, в котором было сохранено содержимое файла key.json. После этого в реестре должен появиться ваш загруженный образ.
  6. Остается создать ноду из ячейки с кодом.
    Важные моменты:
    • Прогоните все необходимые ячейки с кодом перед сохранением контрольной точки. 

    • Если вам нужна определенная конфигурация машины, пропишите это в ячейке, которую будете деплоить, например #!g1.1 (иначе будет использоваться дефолтная конфигурация c1.4). 
    • Для нашего примера при создании ноды в Output variables необходимо указать name: output_data, type: dict. Аналогично для Input variables. 
    • Необходимо указать Kernel Docker Image 
      • Image path — путь до загруженного в реестр образа (можно нажать на тег образа в реестре, и путь будет скопирован). 
      • Username и Password secret — то, что мы указывали, когда пушили образ в реестр. 
    • После создания у ноды должен быть статус Active, но придется немного подождать, пока можно будет к ней обращаться — в ноде должен быть указан инстанс со статусом HEALTHY (или несколько статусов, если вы выбрали такую конфигурацию). 

Обращение к модели

  1. Обращаться к ноде можно с помощью IAM-токена или API-ключа. Пример кода для обращения к ноде можно посмотреть в GitHub Стоит отметить, что в функции call_node есть небольшой дефект: переменная data должна заполняться следующим образом:
    data = {
        "folder_id": folder_id,
        "node_id": node_id,
        "input": {'input_data': input_params}
    }
    Значение для заполнения переменной node_id можно найти сразу после имени ноды, если выбрать ее из списка на вкладке Nodes. folder_id — идентификатор каталога, в котором был создан проект.
  2. Если вам не надо менять окружение, загруженное в Docker-образ, то вы можете задеплоить другой код, т. е. повторно выполнить только пункт 6 предыдущего раздела.
 Нет комментариев    454   1 мес   yandex

Как выбрать самые красивые цвета для визуализации данных

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

Перевод статьи “How to pick more beautiful colors for your data visualizations”

Лиза Шарлотта Мут написала очень полезную статью про выбор цветов для визуализации на Datawrapper, которой мы просто не можем с вами не поделиться. Если хотите прочесть статью в оригинале, то переходите на страницу источника, а если вам комфортнее ознакомиться с материалом на русском языке, то оставайтесь здесь и продолжайте читать.

Иногда бывает трудно выбрать хорошие цвета для диаграмм, не так ли? Сегодня я расскажу вам о том, как делать это проще.
Я хочу, чтобы вы чувствовали больше уверенности в выборе цвета и, если вам кажется, что у вас нет чувства цвета, я попытаюсь помочь вам найти нужные цвета. Мы поговорим о типичных цветовых ошибках, которые я вижу на практике, и о том, как их избежать.
Эта статья не для вас, если вы пытаетесь найти хорошие градиенты или оттенки. Но если вам нужно найти красивые характерные цвета, чтобы выделять разные категории (например, континенты, отрасли, виды птиц), то эта статья вам точно поможет.

Категориальные данные в визуализациях FiveThirtyEight, Nadieh Bremer, The Pudding, New York Times, The Economist и Akkurat

Базовые определения цвета

Я часто упоминаю такие термины, как насыщенность, яркость и оттенок. Цветовые пространства HSB (оттенок, насыщенность, яркость) или HSV (оттенок, насыщенность, значение) достаточно хорошо подходят для их проверки:
Оттенок варьируется от 0° до 360° — это типичный цветовой круг:
Насыщенность варьируется от 0% (серый) до 100% (супер красочный!):
Яркость/значение варьируются от 0% (черный) до 100% (фактический цвет):
Для того чтобы преобразовать цвета из HEX (например, #cc0000) или RGB (например, rgb (207, 176, 58)) в HSB/HSV, используйте такой инструмент, как colorizer.org.
Родственником HSB/HSV является цветовое пространство HCL. Используются те же параметры (Hue, Chroma = Saturation, Lightness), но параметры ближе к тому, «как мы действительно видим цвета». Datawrapper использует цветовое пространство HCL для выбора палитры цветов:

Так как вы не найдете параметры цвета HCL в Adobe Photoshop или colorizer.org, то каждый раз, когда я упоминаю градусы (например, 0°) или проценты, я буду говорить о цветовом пространстве HSB/HSV.
Теперь, когда мы разобрались с базой, давайте погрузимся в тему цвета в визуализации.

Расширьте свое понимание цветов

Вы можете подумать: «Так, нужно выбрать пять цветов для моей диаграммы. Поэтому я использую зеленый, желтый, синий и красный. И… эээ… может быть, оранжевый? Или фиолетовый!»
Если вы с детства совсем не задумывались о цветах, раскрашивая книжку-раскраску, то все вполне логично. Итак, сегодня я здесь, чтобы раскрыть вам большой секрет: цветов гораздо больше.
Посмотрите на этот рисунок и сравните его цвета с основными — :

Они отличаются, не так ли? Красный, который использует Надие (автор рисунка) отличается от классического красного . Зеленый... вы можете вообще назвать его зеленым ?
Поэтому, прежде чем вводить ограничивающие нас правила, позвольте мне немного вас напугать: вы можете использовать тысячи цветов. Например, вот это желтовато-красный и синевато-красный цвета. Есть серый цвет , а также, холодный серый и теплый серый . А еще есть синий. Огромное количество оттенков синего! Как, к примеру, этот , этот , этот , этот и вот этот . И это мы даже не говорили про желтый и оранжевый.
У вас всегда есть множество вариантов. Это означает, что вы можете оставаться в небольшой области цветового круга и при этом вариативность будет колоссальной. Это значит:

Не стоит плясать по всему цветовому кругу

Нет необходимости включать сразу все оттенки цветового круга, например, , в одну визуализацию. Диаграмма будет выглядеть более профессионально — и, следовательно, вызывать больше доверия — когда в ней используется только несколько оттенков и комплементарных цветов.
Когда же смотреть на цветовой круг? Это может быть полезно при поиске гармоничных сочетаний с помощью Adobe Color или Color Calculator.

Гармонии из Color Calculator

Инструменты выше помогают выбрать различные гармонии. Последний инструмент называется «квадратный» или «четырехугольный». Не используйте его. Это приведет к использованию слишком большого количества оттенков, а мы стремимся этого избежать.

В этом видео показано, как я использовала инструмент цвета Paletton, начиная с квадратной гармонии и уменьшая расстояние между точками. Обратите внимание, насколько гармоничнее становятся цветовые сочетания.
Если расстояние в обеих парах цветов становится достаточно маленьким, вы, скорее всего, используете комплементарные цвета. И это отличный выбор! Множество комплементарных цветовых пар прекрасно сочетаются друг с другом. Если сомневаетесь, используйте комплементарные цвета и соседние с ними.
Итак, давайте посмотрим, как это выглядит в Adobe Color.

Комплементарные цвета из Adobe Color

Наши цвета противоположны друг другу на цветовом круге, поэтому они точно являются комплементарными. Ура! Но использовать их все еще нельзя: два оранжевых оттенка слишком похожи. И все выглядит таким… ярким.
Поэтому теперь нам нужно отрегулировать насыщенность и яркость:

Используйте насыщенность и яркость, чтобы ваши оттенки сочетались

Насыщенность и яркость так же важны, как и сам оттенок. Фактически, вы можете создавать новые цвета, просто изменяя насыщенность и яркость. Вот две пары цветов одного оттенка, но разной насыщенности и яркости: / . Если вы немного измените оттенок, то добьетесь лучших результатов: / .
Вернемся к нашему цветовому сочетанию: . Поиграв с параметрами, получается следующее:

Улучшенные комплементарные цвета из Adobe Color

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

Используйте теплые оттенки и голубой цвет

Существует комплементарная цветовая комбинация, которая особенно нравится дизайнерам визуализации данных: желтый/оранжевый/красный и синий. Просмотрите графические портфолио, например, из South China Morning Post или The Economist (PDF), и вы заметите, что они используют эти цвета гораздо чаще, чем фиолетовый или зеленый.

Три страницы из The South China Morning Post

Все дело в том, что эти теплые цвета в комбинации с синим — универсальны для категорий. Желтый, оранжевый и красный очень хорошо смотрятся вместе, но люди все равно будут воспринимать их как разные цвета:  — это именно то, что нам нужно для цветов разных категорий. А синий цвет более гибкий, чем любой другой оттенок: много синего (независимо от того, темный или светлый , насыщенный или ненасыщенный ) выглядят приятно, успокаивающе и даже профессионально.
Важно заметить, что эти цвета доступны всем: дальтоники легко смогут отличить друг от друга синий и оранжевый/красный.
Так что, если сомневаетесь, то используйте оранжевый/красный с синим.

Если используете зеленый, сделайте его желтоватым или синеватым

Оттенок, который можно назвать “лесной зеленый”, занимает одну шестую часть цветового круга, примерно от 90° до 150° , с пиком в 120° . Однако вы едва ли сможете найти много хорошо продуманных визуализаций, в которых он используется. Почему так?
Во-первых, лесной зеленый — очень темный. А если сделать его светлее, то он станет похож на странноватый неон . Таким образом, вам нужно сильно изменить яркость и насыщенность зеленого цвета (больше, чем у других цветов) чтобы получить приятный оттенок. Именно это и делает Washington Post с зеленым цветом в примере ниже:

How Trump is rolling back Obama’s legacy от The Washington Post

Это зеленый цвет на 142° цветового круга, но в нем только 14% насыщенности. Вот как тот же оттенок с такой же яркостью будет выглядеть на 100% насыщенным: . Ой!
И не забывайте про людей-дальтоников: чистый зеленый цвет в сочетании с красным, оранжевым или даже красновато-коричневым им трудно различить.
Поэтому, используя зеленый цвет, добавьте в него немного желтого или синего. Вы можете увидеть это в примерах вверху этой статьи: все зеленые (кроме одного в примере FiveThirtyEight ) имеют оттенок более 160°, то есть более синий , или менее 60°, то есть более желтый . Надие использует и желто-зеленый, и сине-зеленый в этом проекте, который мы уже видели выше:

Похоже, вы даже можете использовать оттенки зеленого в своей визуализации как два разных цвета, как это делает Надие: вариант абсолютно беспроигрышный!

Избегайте чистых цветов

«Чистые» оттенки — это те, которые расположены точно под 60°, 120°, 180°, 240°, 300° и 360°/0° на цветовом круге:

Давайте разберем пример, чтобы научиться определять “чистый” цвет. В HSV/HSB значение оттенка (hue) этого ярко-синего . равно 180°, значение насыщенности (saturation) — 67%, а значение яркости — 91%. Вы также можете проверить значения RGB: если хотя бы два значения совпадают, цвет — «чистый». Например, наш . — это rgb (77, 232, 232).
Чтобы ваши цвета выглядели более естественными и приятными для глаз читателей, вы можете либо уменьшить насыщенность чистых цветов, либо сделать их темнее. Если вы хотите получить яркие, насыщенные цвета, полагайтесь на смешанные цвета на расстоянии не менее 5-10° от чистых цветов.
На изображении выше красный, оранжевый, синий и зеленый имеют одинаковую насыщенность и яркость. Единственная разница — оттенок! Красный (0°), синий (240°) и зеленый (120°) выглядят более красочно, чем оранжевый (40°), светло-синий (211°) и ярко-сине-зеленый (170°). Почему бы тоже не выбрать их, ведь всем хочется яркой, красочной визуализации? Давайте разбираться.

Лучше избегать ярких, насыщенных цветов

Неоновые цвета обязательно привлекут внимание читателей. Но эти читатели не будут вам благодарны. Большинство из нас немного нервничают, когда видят их: «Сильно насыщенные светлые цвета НЕ подходят [для передачи] Серьезности, Доверия или Спокойствия», — объясняют Бартрам, Патра и Стоун в своей статье «Аффективный цвет в визуализации» 2017 года.
Если ваши цвета близки к 100% насыщенности и 100% яркости, скорее всего, ваши цвета чересчур красочные. Это определенно относится к чистым цветам, таким как .
«Но я ведь видел такие сумасшедшие цвета раньше, и они хорошо смотрятся», — скажете вы, имея в виду такие проекты:

Статья из New York Times про музыкальные плейлисты

Статья Bloomberg о банкротстве

Статья The Pudding про звезд

Но если вы сравните цвета из этих примеров с такими цветами, как , вы увидите, что все первые менее насыщенные или более темные. 100% насыщенный и 100% ярко-зеленый . становится менее насыщенным в New York Times , менее насыщенным и темным как в статье Bloomberg ., так и в статье Pudding выглядят в оттенках серого вот так: .
Чтобы эта ошибка не резала глаза читателю, у вас есть два варианта:

  • “Сделайте все правильно в черно-белом”: измените яркость каждой области, сделав некоторые светлее, а некоторые темнее, например: . Так они выглядят в оттенках серого: .
  • Разделите области, к примеру, белой каймой.

Я настоятельно рекомендую первый вариант (однако, вы все равно можете обвести его белой рамкой, если вам нравится стиль): цвета будут выглядеть более динамичными, и люди с дальтонизмом смогут понять диаграмму ничуть не хуже остальных. На самом деле, все будут вам благодарны, независимо от способности видеть цвета!
Фактически, правильный способ подобрать палитру для категориальных данных — это выбрать цвета из градиентов, подобных этим:

Цветовые схемы Viridis

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

Сделайте свои цвета одинаково «красочными»

Зачастую, задача визуализатора заключается в том, чтобы некоторые цвета выделялись на фоне остальных. Есть разные способы добиться этого. Цвета становятся заметнее по разным причинам, например:

  • они намного темнее
  • они намного светлее
  • они более насыщенные
  • они более «чистые»

Обычно вам нужно выделить один или два цвета. Предполагается, что большинство цветов в диаграмме более или менее одинаково привлекают внимание.
Если вы используете цвета с разной яркостью («Сделайте это правильно в черно-белом»), вам нужно будет сбалансировать их. Попробуйте обесцветить яркие цвета. Увеличьте насыщенность темных цветов.
Еще вы можете выбрать менее чистый оттенок: на изображении выше зеленый и синий очень чистые, поэтому я затемнила их (вот как они выглядят при 100% яркости: ).
Затем я хотела добавить красный… но ярко-красный был бы слишком интенсивным, так как это чистый оттенок: . Так что у меня было два варианта:

  • просто затемнить его: .
  • переместить оттенок (и только оттенок) на 30°, чтобы сделать его более оранжевым .

Я выбрала второй вариант, чтобы он выглядел немного более спокойно, но оба этих варианта хороши.

Избегайте слишком слабого контраста с фоном

Удивительное количество диаграмм на ярком фоне выполнено в пастельных тонах. Они часто бывают недостаточно насыщенными и даже ужасно светлыми.
Это влечет за собой некоторые проблемы: если вы работаете с небольшими областями, такими как линии и точки, читателям может быть трудно различить светлые и ненасыщенные цвета на светлом фоне. Однако, даже если видимость объекта — не проблема (например, для больших объектов), ваши визуализации должны заметно контрастировать с фоном, чтобы можно было уверенно говорить: «Эй, я здесь, и мне есть что сказать!».
Вот что делать, если ваши цвета слишком ненасыщенные и светлые :

  • Увеличьте насыщенность:
  • Сделаем их темнее:
  • Или сделайте и то, и другое для наилучшего результата:

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

Избегайте слишком большого контраста с фоном

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

Выбирайте достаточно ненасыщенный фон

Когда вы немного разберетесь в цветах и оттенках, наверняка цветной фон покажется вам хорошей идеей. Но у цветного фона есть два больших недостатка: во-первых, он легко отвлекает внимание от диаграммы. Во-вторых, он ограничивает вашу потенциальную цветовую палитру, и поэтому с ним сложно работать. Фактически, чем насыщеннее ваш фон, тем сложнее он становится. Так что ненасыщенные цвета — ваш лучший выбор. Вот несколько правил для цветового пространства HSB / HSV:

  • Если вам нужен светлый фон, держитесь подальше от цветов с менее чем 95% яркости и более чем 7% насыщенности.
  • Если вам нужен темный фон, не используйте больше 20% насыщенности. Кроме того, не используйте полностью черный цвет — оставьте яркость между 10% и 25%.

Копируйте цвета или разбирайтесь в них

Выбрать хорошие цвета действительно сложно. Совершенно нормально плохо разбираться в цветах, продолжать в них не разбираться и просто копировать цвета. Серьезно, в том чтобы воровать нет ничего постыдного. Я написала отдельную статью о том, где черпать вдохновение: фильмы (примечание переводчика: фильмов Уэса Андерсона, например!), художники, цветовые палитры, созданные другими, и т. д. И позвольте мне добавить: чужие визуализации данных — тоже отличный источник. Если вы хотите лучше понять, какие цвета подходят друг другу: проанализируйте их. Вот несколько способов сделать это:

Фотография niko photos с сайта Unsplash

  • Выберите изображение в цветах, которые считаете красивыми, например картину или фотографию природы. Затем выберите из них цвета с помощью пипетки, например с помощью Photoshop или image-color.com и попробуйте использовать именно их в следующем графике.
  • Установите Adobe Capture, это то же самое, но для «живых изображений»: этот инструмент позволяет брать цвета из вашего окружения. (Приятно видеть, как много цветов вокруг нас ненасыщены!)
  • Играйте в «ручную подборку цветов»: посмотрите на свой экран. Какие цвета вы видите? Насколько темные и насколько они насыщенные? Какие оттенки рядом; какие из них находятся на противоположной стороне цветового круга?
  • Выбирайте цвета из красивых визуализаций данных. Измените несколько цветов. Они по-прежнему хорошо работают вместе?

Кроме того, в следующий раз, когда вы будете создавать визуализацию данных и не будете довольны цветами, проанализируйте их в цветовом пространстве HSV/HSB, например с colorizer.org:

  • Насколько они насыщены — и выглядят ли они лучше, если вы увеличите или уменьшите насыщенность на несколько (или много) процентных пунктов?
  • Какое у них значение оттенка? Что произойдет, если вы измените оттенок всего на несколько градусов?
  • Ваши цвета по-разному яркие?

Со временем ваше понимание изменится от «это красиво, но я не знаю почему» к «это красиво, потому что…». И вы обнаружите, что можете нарушать все больше и больше правил, которые я объяснила здесь, и при этом создавать отличные цветовые комбинации!

 Нет комментариев    659   1 мес  

How to: Google App Script

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

Сегодняшний пост прольет свет на то, как можно еще более эффективно использовать один из очень удобных инструментов для базовой аналитики — Google Sheets.
Зачастую, аналитикам нужно часто и много работать с таблицами и создавать выборки по различным условиям. В работе с таблицами проблема часто заключается в том, что приходится обрабатывать и систематизировать большие объемы данных, хранящихся на разных листах. К примеру, вы ведете учет клиентов на одном листе таблицы, выгружая данные из сторонних сервисов, и вам хотелось бы, при обновлении ячеек менять данные на другом листе, используя возможности скриптов Google Sheets. Давайте посмотрим как легко и просто решить эту задачу.

Редактор скриптов

Если у вас есть Google аккаунт и таблицы с данными, загруженные в Google Sheets, то можно создавать скрипт для этой таблицы. Выберите таблицу, в которой нужно автоматизировать перенос информации с одного листа на другой, откройте её и выберете в меню «Инструменты» пункт «Редактор скриптов». Браузер переадресует вас на страницу Apps Script, где вы можете создавать и редактировать скрипты для таблицы.

Автоматизация переноса строк на другой лист

Итак, наш скрипт должен автоматически выполнять задачу переноса строки на другой лист. Для этого, мы создаем еще одну колонку в таблице, в которой можно будет поставить галочку для переноса строки и убрать её для отмены этого действия. Давайте разберемся, как именно это делается.
При написании скрипта можно использовать функцию-триггер, которая срабатывает при выполнении определенного условия. Подробнее о функциях-триггерах вы можете прочитатьhttps://developers.google.com/apps-script/guides/triggers?hl=ru#onedite в документации. В нашем скрипте мы используем функцию OnEdit(e), которая запускается при редактировании ячейки таблицы (включение и выключение галочки). Эту функцию мы вызовем в конце скрипта, а пока что, пропишем все, что в функции должно происходить.

function myScript(e) {   
  // Задаем следующее условие для функции: нужно реагировать только на нажатие галочки в восьмой колонке на листе "Лиды-воронка". 
  if (e.source.getActiveSheet().getName() == "Лиды- воронка" && e.range.getColumn() == 8)
  {
    // Сохраняем объекты исходного листа и листа назначения
    destSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');
    sourceSheet = e.source.getActiveSheet();
    // Очищаем лист назначения. При очистке, начинаем со второй строки, так как у нас в таблице есть заголовок.
    destSheet.getRange(2, 1, destSheet.getLastRow(), destSheet.getLastColumn()).clearContent();
    //Перебираем все ячейки с галочками, ищем те ячейки, в которых галочки проставлены.
    range = sourceSheet.getRange(1, 1, sourceSheet.getLastRow(), sourceSheet.getLastColumn());       
    for (i = 2; i <= range.getNumRows(); i++) 
    {      
      //Получаем все проставленные галочки.
      if (range.getCell(i,8).getValue())
      {        
        // Если галочка проставлена, то текущая строка переносится на новый лист.
        currentRow = sourceSheet.getRange(i, 1, i, sourceSheet.getLastColumn());           
        destSheet.appendRow(currentRow.getValues()[0]);
      }      
    }    
  }

// Затем, вызываем функцию-триггер, которая будет вызывать наш скрипт при каждом редактировании ячейки.
function onEdit(e) {
  myScript(e)
}

В итоге, если галочка в столбце выделена, то соответствующая строка будет перенесена на другой лист. Также, важно помнить, что, при удалении галочки, строка убирается с другого листа.
В нашем примере мы получили следующие две таблицы:

Выводы

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

Ранее Ctrl + ↓