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

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

Кроме того, что предобработка данных — база для аналитика, без правильного объединения персональных данных в принципе сложно отследить клиентский путь. Значит, нужно настраивать процессы объединения неоднородных персональных данных.

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

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

Кейс Refocus: данные и путь клиента

Мы разрабатывали кастомную систему сквозной аналитики для эдтех-стартапа Refocus. Данные каждого студента в системы Refocus попадали из нескольких источников и были записаны несколько раз — как минимум при регистрации на курс, при первом входе на образовательную платформу и при входе в чат сопровождения.

В нашем случае мэтчинг был важнее всего по трем источникам из тринадцати:

• amoCRM, где фиксируется весь клиентский путь студента;
• Discord, где проходило сопровождение студентов;
• Thinkific, сама образовательная платформа с курсами.

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

В Discord и Thinkific данные попадали напрямую, от студентов при регистрации в системах, а затем подтягивались в amoCRM. Основные причины несовпадения клиентских данных как у Refocus, так и в похожих случаях — человеческий фактор (опечатки), наличие у людей более чем одного телефона или адреса почты и ограничения самих платформ, с которых приходят данные: разный заданный формат полей и их количество.

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

Задача и поиск решения

Данные в Refocus мы подгружали в хранилище в BigQuery напрямую из интересующих нас источников (рекламных кабинетов, LMS и т. д.), используя Python. В дальнейшем на этих данных строились дашборды в Tableau.

Обнаружить проблему несложно — при создании хранилища и дальнейшей выгрузке данных из него мы в любом случае чистим датасет от дубликатов и несовпадений.

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

• имя — да, люди иногда вводят разные вариации ФИО (Юлия, Юля и Бля — на деле один человек!);
• телефон — с кодом страны или без, с пробелами, дефисами или слитно;
• электронная почта — длинные строки сложного формата, в которых легко опечататься.

Поначалу, пока количество студентов Refocus было относительно небольшим, достаточно было скриптов, которые объединяли данные по одному из этих полей. В полученных таблицах в Tableau проводился поиск строк с пустым значением в соответствующем поле — и вот видно всех студентов, чьи данные не сошлись.

Количество таких строк было в пределах пары десятков, и трекать и объединять их было несложно вручную. Это делалось прямо в первоисточниках сотрудниками Refocus, которые могли поправить опечатки и ошибки у себя в системах. После этого наш код выгрузки в хранилище перезапускался и тянул уже чистые данные. Если после этого что-то не сходилось, то наши аналитики правили информацию на уровне базы данных.

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

Вообще, в такой ситуации возможны несколько вариантов. Можно бесконечно править скрипты мэтчинга, учитывая новые и новые случаи и создавая костыли. А можно, например, настроить алерты в оркестраторе процессов (в нашем случае  — Airflow), которые позволят моментально узнавать о появившемся несовпадении и объединять “потерянные” клиентские сущности по паре за раз. Но это все еще неполная автоматизация, и она только ускоряет, а не упрощает процесс.

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

Реализация решения и рабочий процесс

Чтобы понять масштаб проблемы, мы начали с того, что создали таблицы несовпадающих персональных данных. Для этого мы использовали скрипты на Python. Эти скрипты объединяли данные из разных источников и создавали из них большую сводную таблицу. Для того, чтобы свести данные о студенте в одну сущность, использовался мэтчинг по адресу электронной почты. Мы попробовали мэтчить по имени, фамилии, телефону (который сначала надо было привести к одному формату!) и почте, и именно последний вариант показал самую высокую точность. Возможно, дело в том, что из всех данных почта имеет самый однородный формат, поэтому остается учитывать только опечатки.

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

WITH snapshot_ AS (
      SELECT DISTINCT s.*,
        IFNULL(ae.name, ap.name) as contact_name,
        ap.phone, ae.email,
        split(replace(trim(lower(ae.email)),' ',''),'@')[OFFSET(0)] as email_first_part,
        ai.thinkific_id, ai.intercom_id, ac.student_id
      FROM (
        SELECT *,
          ROW_NUMBER() OVER(PARTITION BY lead_id ORDER BY updated_at DESC) as num_,
        FROM `Differture.amocrm_leads_snapshot`
      ) s
      LEFT JOIN (
        SELECT DISTINCT lead_id, contact_id, name, email,
          ROW_NUMBER() OVER(PARTITION BY lead_id ORDER BY contact_id) as num1_
        FROM `Differture.amo_emails`
      ) ae using(lead_id)
      LEFT JOIN (
        SELECT DISTINCT lead_id, contact_id, name, phone,
          ROW_NUMBER() OVER(PARTITION BY lead_id ORDER BY contact_id) as num2_
        FROM `Differture.amo_phones`
      ) ap using(lead_id)
      LEFT JOIN `Differture.amo_contact_thinkific_intercom_match` ai using(lead_id)
      LEFT JOIN `Differture.AmoContacts` ac on cast(ae.contact_id as string)=ac.amo_id
      WHERE (num_=1 or num_ is null) and (num1_=1 or num1_ is null) and (num2_=1 or num2_ is null)
        and s.pipeline_id in (4920421,5245535) and s.status_id=142 and lower(s.lead_name) not like '%test%'
    )

Как можно заметить, идея сквозного идентификатора здесь уже присутствует — фигурирует student_id. На самом деле, в этой версии скрипта это графа из AmoContacts — таблицы, в которой хранятся только данные из amoCRM. Никаких джойнов по student_id пока не происходит. А происходят по email_first_part, адресу почты до символа @:

select distinct * from th_amo_ds_rf
    left join calendly ce using(email_first_part)
    left join typeform_live tfl on email_first_part=tf_email_first_part
    left join typeform tf using(email_first_part)
    left join csat using(email_first_part)

Первым шагом по практическому введению идентификатора была таблица students_main_info, созданная в BigQuery in-house специалистом Refocus. К сожалению, у нас нет доступа к коду, который использовался для присвоения идентификатора. Зато мы можем показать вид этой таблицы:

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

При этом поле student_id использовалось пока не везде; также использовались другие поля этой таблицы — например, thinkific_student_id или discord_user_id.

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

Что получилось в итоге

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

Вот так выглядела схема соотношения этих таблиц:

А вот так выглядела основная таблица Students:

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

Остальные таблицы выглядели похоже: всегда было поле с идентификатором и информация о какой-то характеристике студента — когорта, курс, роль в дискорде и так далее.

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

Сначала он отбирал собранные нами данные из amoCRM (amocrm_leads_snapshot) и объединял их с контактной информацией клиентов. Затем в таблицу добавлялось поле student_id и отбирались данные, которые понадобятся нам дальше.

WITH snapshot_ AS (
      SELECT DISTINCT s.*,
        ac.name as contact_name, ac.phone, ac.email,
        split(replace(trim(lower(ac.email)),' ',''),'@')[OFFSET(0)] as email_first_part,
        ac.intercom_id, ac.student_id
      FROM (
        SELECT *,
          ROW_NUMBER() OVER(PARTITION BY lead_id ORDER BY updated_at DESC) as num_,
        FROM `Differture.amocrm_leads_snapshot`
      ) s
      LEFT JOIN (
        select cast(al.amo_id as INT64) as lead_id, cast(ac.amo_id as INT64) as contact_id,
          ac.name, emails as email, phone, student_id, ic.intercom_id,
          ROW_NUMBER() OVER(PARTITION BY al.amo_id ORDER BY ac.amo_id) as num1_
        from `Differture.AmoContacts` ac
        left join `Differture.AmoLeads` al on al.amo_contact_id=ac.id
        left join `Differture.IntercomContacts` ic using(student_id)
        , unnest(ac.emails) emails
      ) ac using(lead_id)
      WHERE (num_=1 or num_ is null) and (num1_=1 or num1_ is null)
        and s.pipeline_id in (4920421,5245535) and s.status_id=142 and lower(s.lead_name) not like '%test%'
    )

Теперь при создании общей таблицы о возвратах с данными из amo, Thinkific и Discord объединение проходило через student_id:

th_amo_ds_rf as (
      select distinct * except (channel_id, channel),
        ifnull(channel_id, 'Not in discord') as channel_id,
        ifnull(channel, 'Not in discord') as channel
      from thinkific_amo_refunds
      full outer join discord using(student_id)
    )

Когда объединенные таблицы данных студентов были созданы, получить таблицы несовпадений можно было простой строкой кода в Tableau:

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

Ниже можно увидеть таблицу, где данные из Thinkific не совпадали с amoCRM после перехода на Student ID. В этом случае студент есть в LMS, значит, на курсе учится — но его либо нет в системе учета, либо данные в ней разнятся с LMS.

А вот таблица, где данные из Discord не совпадали с amoCRM. Все так же, как выше — студент есть в чатах сопровождения, но не ищется по своим данным в amoCRM.

Оба скриншота показывают количество несовпадений примерно за месяц. Как видно по этим таблицам, количество несовпадений уменьшилось с 80-90 до пары десятков — примерно на 75%. Это позволило сократить количество перезапусков кода выгрузки вручную и уменьшить затраты времени и технических ресурсов на поддержание системы.

Выводы

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

Выход практически каждого ролика на канале «вДудь» считается событием, а некоторые из этих релизов даже сопровождаются скандалами из-за неосторожных высказываний его гостей.
Сегодня при помощи статистических подходов и алгоритмов ML мы будем анализировать прямую речь. В качестве данных используем интервью, которые журналист Юрий Дудь (признан иностранным агентом на территории РФ) берет для своего YouTube-канала. Посмотрим с помощью Python, о чем таком интересном говорили в интервью на канале «вДудь».

Сбор данных

C помощью YouTube API мы получили список всех видео с канала Юрия Дудя, а также их метаинформацию. О том, как это сделать, вы можете узнать, например, из статьи нашего блога.
Если вы уже слышали знаменитое “Юрий будет дуть, дуть будет Юрий”, то наверняка знаете, что на этом канале есть документальные фильмы, а также интервью, в которых участвуют сразу несколько гостей. Нас заинтересовали только те выпуски, в которых преимущественно говорит только один гость. Поэтому нам пришлось провести фильтрацию всех видео вручную.
Для дальнейшего анализа нам необходимо было получить длительности роликов. Это мы сделали с помощью GET-запросов к YouTube API. Результаты приходили в специфическом формате (для примера: “PT1H49M35S”), поэтому их нам пришлось распарсить и перевести в секунды.
Итак, мы получили датафрейм, состоящий из 122 записей:

На основе метаинформации по лайкам, комментариям и просмотрам мы построили следующий Bubble Chart:

Так как наша цель — проанализировать речь в интервью, нам необходимо было получить текстовые составляющие роликов. В этом нам помог API-интерфейс youtube_transcript_api, который скачивает субтитры из видео на YouTube. Для каких-то роликов субтитры были прописаны вручную, но для большинства они были сгенерированы автоматически. К сожалению, для 10 видео субтитров не оказалось: беседы с L’one, Шнуром, Ресторатором, Амираном, Ильичом, Ильей Найшуллером, Соболевым, Иваном Дорном, Навальным, Noize MC. Причину их отсутствия мы, к сожалению, понять не смогли.

А гости кто?

Спектр рода деятельности гостей канала «вДудь» достаточно обширен, поэтому было решено пополнить исходные данные информацией о том, чем же в основном занимается приглашенный участник каждого интервью. К сожалению, ролики не сопровождаются четкими метками профессиональной принадлежности гостя, поэтому мы прописали эту информацию сами. На момент выгрузки данных последним видео на канале был разговор с комиком Дмитрием Романовым.
Если с идентификацией профессии каждого гостя мы не ошиблись, то вот такое распределение в итоге получается:

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

Обработка текста

Анализ текстовой информации сложен в той степени, в какой сложен язык, на котором написан текст. Подробно о подготовке текста к анализу мы рассказывали в материале «Python и тексты нового альбома Земфиры». Тут была проведена идентичная работа.
Как и раньше, для решения аналитической задачи мы решили использовать такой подход как лемматизация, т. е. приведение слова к его словарной форме. Проведя лемматизацию текстовых данных по правилам русского языка, мы получим существительные в именительном падеже единственного числа (кошками — кошка), прилагательные в именительном падеже мужского рода (пушистая — пушистый), а глаголы в инфинитиве (бежит — бежать). В этом проекте мы опять воспользовались библиотекой Pymorphy, представляющую собой морфологический анализатор.
Помимо приведения к словарной форме нам потребовалось убрать из текстов часто встречающиеся слова, которые не несут ценности для анализа. Это было необходимо, потому что так называемые стоп-слова могут повлиять на работу используемой модели машинного обучения. Список таких слов мы взяли из пакета ntlk.corpus, а после расширили его, изучив тексты интервью. Конечно, мы также убрали все знаки пунктуации.

Анализ словарного запаса

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

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

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

Что касается количества уникальных слов, то тут ситуация аналогичная. И рэперы опять в аутсайдерах…

Если говорить об отношении уникальных слов к общему количеству, то тут можно увидеть совершенно иную картину. Теперь впереди оказываются, рэперы, музыканты и бизнесмены. Предыдущие же лидеры, наоборот, становятся самыми последними.
Конечно, стоит отметить, что такие сравнения могут быть несправедливыми, так как длительность интервью у каждого гостя Дудя разная, а потому кто-то просто мог успеть наговорить больше слов, чем остальные. Наглядно в этом можно убедиться, взглянув на распределение длительности интервью по роду деятельности (для построения использовался тот же пул гостей, что и для боксплотов выше):

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

Первое место по упоминаниям очевидно занимает Россия. Что касается Запада, то про США гости говорили в 2,5 раза меньше. Что касается лидера РФ, то про него речь заходила достаточно часто. Его оппонент, Алексей Навальный, в этой словесной “баталии” потерпел поражение. Интересно, что политики далеко не в топе по упоминаниям Путина. Впереди оказался экономист Сергей Гуриев, после него ведущий Александр Гордон, а тройку замкнули журналисты.
Глагол “любить” чаще использовали люди, имеющие отношение к искусству, творчеству и гуманитарным наукам — кинокритик Антон Долин, мультипликатор Олег Куваев, историк Тамара Эйдельман, актеры, рэперы, художник Федор Павлов-Андреевич, комики, музыканты, режиссеры. Про страхи (если судить по глаголу “бояться”) гости говорили реже, чем о любви. В топ вошли историк Эйдельман, дизайнер Артемий Лебедев, кинокритик Долин и политики. Может быть в этом кроется ответ на вопрос, почему же политики не так охотно произносили имя президента России.
Что касается денег, то о них говорили все. Ну, за исключением человека науки, астрофизика Константина Батыгина. С церковью же имеем совершенно обратную ситуацию. О ней по большей части говорили только писатели и художник Павлов-Андреевич.

Анализ мата

Далее мы решили проанализировать то, как часто гости Юрия Дудя ругались матом. С помощью регулярных выражений мы составили словарь матерных слов со всех интервью. После этого, для каждого ролика было подсчитано суммарное количество вхождений элементов составленного словаря.
Мы построили диаграммы, отражающие топ-10 любителей нецензурно выражаться по количеству “запрещенных” слов в минуту.

Как видим, рэперы и музыканты почти полностью захватили топ. Помимо них очень часто ругались такие гости как блогер Данила Поперечный и комики Иван Усович и Алексей Щербаков. Первое место в рейтинге с большим отрывом от остальных держит Morgenstern (признан иностранным агентом на территории РФ), а вот Олег Тиньков в своем последнем интервью матерился не так много, чтобы попасть в Топ-10.
Зато, как искрометно!

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

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

Ограничения анализа

Стоит отметить, что в нашем небольшом исследовании есть два недостатка:

1. Как уже говорилось ранее, мы не смогли отделить слова гостей Дудя от речи Юрия, который и сам зачастую не брезгует использовать нецензурные выражения. Однако, задача интервьюера — подстроиться под стиль речи гостя, поэтому, скорее всего, результаты бы не сильно изменились.
2. В автосгенерированных субтитрах нам встретилось некое подобие цензуры — некоторые слова были заменены на ‘[ __ ]’. Тут можно выделить несколько интересных моментов:
   • действительно некоторые матерные слова были зацензурены (по большей части слово “бл**ь”);
   • остальные матерные слова остались нетронутыми;
   • под чистку попали некоторые другие грубые слова, при этом не являющиеся матерными (“мудак”, “гавно”).

Продемонстрируем наглядно на примере следующего диалога:
Дудь: Почему твои треки такое гавно?
Гнойный: Мои треки ох**тельные, Юра, просто ты любишь гавно.

Такие замены встречались в субтитрах роликов с людьми, которые не употребляли нецензурные выражения в своей речи (по крайней мере на протяжении интервью). Однозначное решение, что же делать с ‘[ __ ]’, мы не смогли принять, поэтому для некоторых гостей какая-то часть матерных слов была, увы, не подсчитана.

Работа с Word2vec

После статистического анализа интервью мы перешли к определению их контекста. Для этого мы, как и раньше, воспользовались моделью Word2vec. Она основана на нейронной сети и позволяет представлять слова в виде векторов с учетом семантической составляющей. Косинусная мера семантически схожих слов будет стремиться к 1, а у двух слов, не имеющих ничего общего по смыслу, она близка к 0. Модель можно обучать самостоятельно на подготовленном корпусе текстов, но мы решили взять готовую — от RusVectores. Для ее использования нам понадобилась библиотека gensim.
Мы рассчитали векторы-представления для каждой профессиональной группы. Наверное, можно ожидать, что режиссёры обсуждали кино и все, что с ним связано, а музыканты — музыку. Поэтому для каждого рода деятельности мы получили список слов, описывающих тематику текстов соответствующих роликов. Также мы раскрасили ячейки в зависимости от того, насколько каждое полученное слово было близко к текстам соответствующей категории гостей.

Можно сказать, что в целом каждая профессиональная категория описывается вполне соответствующими терминами. Конечно, некоторые слова могут показаться спорными. К примеру, на первом месте для рэперов стоит слово “джазовый”, хотя ни с 1 представителем хип-хоп течения речь о джазе не заходила. Тем не менее модель посчитала, что это слово достаточно близко к общему смыслу интервью людей, относящихся к этой категории (видимо, за счет непосредственного отношения рэперов к музыке).

P.S. Мистическое число 25.000000

Как мы уже говорили, среди скачанных субтитров некоторые были написаны вручную. Интересно, что все они начинаются с числа 25.000000, причем оно нигде не озвучивается.

Что же это за мистическое число? Если уйти в конспирологию, то можно вспомнить про 25-й кадр. К сожалению, нам об этом ничего неизвестно, мы просто оставим это как пищу для размышлений…

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

В рамках текущего курса моей учебы, я изучаю много современных подходов к анализу и визуализации данных. В самом начале курса было разминочное упражнение: объектно-ориентированное программирование на Python для сбора и итеративного построения графа с TMDB API. В задаче этот метод применяется для построения графа связи актеров, где связь — игра в одном и том же фильме. Но я решил, что можно применить его и к другой задаче: построению графа связей аналитического сообщества.

Поскольку последнее время мой временной ресурс особенно ограничен, а аналогичную задачу для курса я уже выполнил, то я решил передать эти знания кому-то еще, кто интересуется аналитикой. К счастью, в этот момент, ко мне в личку постучался кандидат на вакансию младшего аналитика данных — Андрей. Он сейчас находится в процессе постижения всех тонкостей аналитики, поэтому мы договорились на стажировку, в рамках которой Андрей спарсил данные с telegram-каналов.

Основной задачей Андрея был сбор всех текстов с телеграм-канала Интернет-аналитика, выделение каналов, на которые ссылался Алексей Никушин, сбор текстов из этих телеграм-каналов и ссылок на этих каналах. Под “ссылкой” подразумевается любое упоминание канала: через @, через ссылку или репостом. В результате парсинга, у Андрея получилось два файла: nodes и edges.
Теперь я представлю вам граф, который получился у меня на основе этих данных и прокомментирую результаты.

Пользуясь случаем, хочу выразить мое почтение команде karpov.courses, поскольку у Андрея отличное знание языка Python!

В результате топ-10 каналов по показателю degree (количество связей) выглядит так:

1. Интернет-аналитика
2. Reveal The Data
3. Инжиниринг Данных
4. Data Events
5. Datalytics
6. Чартомойка
7. LEFT JOIN
8. Epic Growth
9. RTD: ссылки и репосты
10. Дашбордец

По-моему, получилось супер-круто и визуально интересно, а Андрей — большой молодец! Кстати, он тоже начал свой канал ”Это разве аналитика?”, где публикуются новости аналитики.

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

Такой навык как визуализация данных применяется в любой отрасли, где присутствуют данные, ведь таблицы хороши лишь для хранения информации. Когда есть необходимость презентовать данные, точнее определенные выводы, полученные на их основе — данные необходимо представить на графиках подходящего типа. И тут перед вами встает две задачи: первая — правильно подобрать тип графика, вторая — правдоподобно отразить результаты на диаграмме. Сегодня мы расскажем вам об одной ошибке, которую иногда допускают дизайнеры при визуализации данных на bubble-charts и о том, как эту ошибку можно избежать.

Суть построения bubble-чарта

Немного скучной теории перед тем, как мы приступим к анализу данных. Bubble-chart — удобный способ показать три параметра наблюдения без построения трехмерной модели. По привычным осям X и Y указываются значения двух параметров, а третий показан размером круга, который соответствует каждому наблюдению. Именно это позволяет избежать необходимости построения сложного 3D графика, то есть любой, кто видит bubble-chart, гораздо быстрее сможет сделать выводы о данных изображенных на одной плоскости.

Ошибка, которую может допустить дизайнер, но не аналитик данных

С метриками, которые отображены на осях графика не возникает никаких вопросов, это привычный способ их визуализации, а вот с размерами возникает некоторая трудность: как грамотно и точно отобразить изменения в значениях переменной, если управление идет не точкой на оси, а размером этой точки?
Дело в том, что при построении такого графика без использования аналитических средств, например, в графическом редакторе, автор может нарисовать круги, принимая радиус круга за его размер. На первый взгляд, все кажется абсолютно корректным — чем больше значение переменной, тем больше радиус круга. Однако, в таком случае, площадь круга будет увеличиваться не как линейная, а как степенная функция, ведь S = π × r2. Например, на рисунке ниже показано, что, если увеличить радиус круга в два раза, то площадь увеличится в 4 раза.

fig = plt.figure(figsize=(10, 10))
ax = fig.add_subplot(1, 1, 1)
s = 4*10e3


ax.scatter(100, 100, s=s, c='r')
ax.scatter(100, 100, s=s/4 ,c='b')
ax.scatter(100, 100, s=10, c='g')
plt.axvline(99, c='black')
plt.axvline(101, c='black')
plt.axvline(98, c='black')
plt.axvline(102, c='black')


ax.set_xticks(np.arange(95, 106, 1))
ax.grid(alpha=1)

plt.show()

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

Как построить такой график правильно?

К счастью, если строить bubble-charts с помощью библиотек Python (Matplotlib и Seaborn), то размер круга будет определяться именно площадью, что абсолютно корректно и грамотно с точки зрения визуализации.
Сейчас на примере реальных данных, найденных на Kaggle, покажем, как построить bubble-chart правильно. В данных присутствуют следующие переменные: страна, численность населения, процент грамотного населения. Для того чтобы диаграмма была читаемой, возьмем подвыборку из 10 первых стран после сортировки всех данных по возрастанию ВВП.

Для начала, загрузим все нужные библиотеки:

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

Затем, загрузим данные, очистим и от всех строк с пропущенными значениями и приведем данные по численности населения стран в миллионы:

data = pd.read_csv('countries of the world.csv', sep = ',')
data = data.dropna()
data = data.sort_values(by = 'Population', ascending = False)
data = data.head(10)
data['Population'] = data['Population'].apply(lambda x: x/1000000)

Теперь, когда все подготовка завершена, можно построить bubble-chart:

sns.set(style="darkgrid")    
fig, ax = plt.subplots(figsize=(10, 10))    
g = sns.scatterplot(data=data, x="Literacy (%)", y="GDP ($ per capita)", size = "Population", sizes=(10,1500), alpha=0.5)
plt.xlabel("Literacy (Percentage of literate citizens)")
plt.ylabel("GDP per Capita")
plt.title('Chart with bubbles as area', fontdict= {'fontsize': 'x-large'})

def label_point(x, y, val, ax):
    a = pd.concat({'x': x, 'y': y, 'val': val}, axis=1)
    for i, point in a.iterrows():
        ax.text(point['x'], point['y']+500, str(point['val']))

label_point(data['Literacy (%)'], data['GDP ($ per capita)'], data['Country'], plt.gca()) 

ax.legend(loc='upper left', fontsize = 'medium', title = 'Population (in mln)', title_fontsize = 'large', labelspacing = 1)

plt.show()

На этом графике получилось понятным образом отобразить три метрики: уровень ВВП на душу населения по оси Y, процент грамотного населения по оси X и численность населения — площадью круга.

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

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

Для начала стоит вспомнить определения двух метрик, о которых мы сегодня поговорим.
Среднее  — самый популярный статистический показатель, который используется для измерения центра данных. А что же такое медиана? Медиана — значение, которое разбивает данные, отсортированные по порядку увеличения значений, на две равные части. Это значит, что медиана показывает центральное значение в выборке, если наблюдений нечетное количество и среднее арифметическое двух значений, если количество наблюдений в выборке четно.

Исследовательские задачи

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

import pandas as pd
city = pd.read_csv('city.csv')

Затем, нужно посчитать среднее и медиану выборки.

mean_pop = round(city.population_2020.mean(), 0)
median_pop = round(city.population_2020.median(), 0)

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

import matplotlib.pyplot as plt
import seaborn as sns

sns.set_palette('rainbow')
fig = plt.figure(figsize = (20, 15))
ax = fig.add_subplot(1, 1, 1)
g = sns.histplot(data = city, x= 'population_2020', alpha=0.6, bins = 100, ax=ax)

g.axvline(mean_pop, linewidth=2, color='r', alpha=0.9, linestyle='--', label = 'Среднее = {:,.0f}'.format(mean_pop).replace(',', ' '))
g.axvline(median_pop, linewidth=2, color='darkgreen', alpha=0.9, linestyle='--', label = 'Медиана = {:,.0f}'.format(median_pop).replace(',', ' '))

plt.ticklabel_format(axis='x', style='plain')
plt.xlabel("Численность населения", fontsize=25)
plt.ylabel("Количество городов", fontsize=25)
plt.title("Распределение численности населения российских городов", fontsize=25)
plt.legend(fontsize="xx-large")
plt.show()

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

fig = plt.figure(figsize = (10, 10))
sns.set_theme(style="whitegrid")
sns.set_palette(palette="pastel")

sns.boxplot(y = city['population_2020'], showfliers = False)

plt.scatter(0, 550100, marker='*', s=100, color = 'black', label = 'Выбросы')
plt.scatter(0, 560200, marker='*', s=100, color = 'black')
plt.scatter(0, 570300, marker='*', s=100, color = 'black')
plt.scatter(0, mean_pop, marker='o', s=100, color = 'red', edgecolors = 'black', label = 'Среднее')
plt.legend()

plt.ylabel("Численность населения", fontsize=15)
plt.ticklabel_format(axis='y', style='plain')
plt.title("Боксплот численности населения", fontsize=15)
plt.show()

Из графиков следует, что медиана существенно меньше среднего, а также, ясно, что это следствие наличия больших выбросов — Москвы и Санкт-Петербурга. Поскольку среднее арифметическое — метрика крайне чувствительная к выбросам — при их наличии в выборке опираться на выводы относительно среднего не стоит. Рост или снижение численности населения Москвы может сильно смещать среднюю численность по России, однако это не будет влиять на настоящий общерегиональный тренд.
Используя среднее арифметическое мы скажем, что численность типичного (среднего) города в РФ — 268 тысяч человек. Однако, это вводит нас в заблуждение, так как среднее значительно превышает медиану исключительно из-за численности населения Москвы и Санкт-Петербурга. На самом деле, численность типичного российского города существенно меньше (аж в 2 раза!) и составляет 104 тысячи жителей.

Маркетинговые задачи

В контексте бизнеса разница между средним арифметическим и медианой также важна, так как использование неверной метрики может серьезно сказаться на результатах проведения акции или затруднить достижение цели. Давайте посмотрим на реальном примере, с какими трудностями может столкнуться предприниматель в ритейле, если неверно выберет целевую метрику.
Для начала, как и в предыдущем примере, загрузим датасет о покупках в супермаркете. Выберем необходимые для анализа столбцы датасета и переименуем их, для упрощения кода в дальнейшем. Поскольку эти данные не так хорошо подготовлены, как предыдущие, необходимо сгруппировать все купленные товары по чекам. В этом случае необходима группировка по двум переменным: по id покупателя и по дате покупки (дата и время определяется моментом закрытия чека, поэтому все покупки в рамках одного чека совпадают по дате). Затем, назовем полученный столбец «total_bill», то есть сумма чека и посчитаем среднее и медиану.

df = pd.read_excel('invoice_data.xlsx')
df_nes = df[['Номер КПП', 'Сумма', 'Дата продажи']]
df_nes.columns = ['user','total_price', 'date']
groupped_df = pd.DataFrame(df_nes.groupby(['user', 'date']).total_price.sum())
groupped_df.columns = ['total_bill']
mean_bill = groupped_df.total_bill.mean()
median_bill = groupped_df.total_bill.median()

Теперь, как и в предыдущем примере нужно построить график распределения чеков покупателей и боксплот, а также отметить медиану и среднее арифметическое на каждом из них.

sns.set_palette('rainbow')
fig = plt.figure(figsize = (20, 15))
ax = fig.add_subplot(1, 1, 1)
sns.histplot(groupped_df, x = 'total_bill', binwidth=200, alpha=0.6, ax=ax)
plt.xlabel("Покупки", fontsize=25)
plt.ylabel("Суммы чеков", fontsize=25)
plt.title("Распределение суммы чеков", fontsize=25)
plt.axvline(mean_bill, linewidth=2, color='r', alpha=1, linestyle='--', label = 'Среднее = {:.0f}'.format(mean_bill))
plt.axvline(median_bill, linewidth=2, color='darkgreen', alpha=1, linestyle='--', label = 'Медиана = {:.0f}'.format(median_bill))
plt.legend(fontsize="xx-large")
plt.show()

fig = plt.figure(figsize = (10, 10))
sns.set_theme(style="whitegrid")
sns.set_palette(palette="pastel")

sns.boxplot(y = groupped_df['total_bill'], showfliers = False)

plt.scatter(0, 1800, marker='*', s=100, color = 'black', label = 'Выбросы')
plt.scatter(0, 1850, marker='*', s=100, color = 'black')
plt.scatter(0, 1900, marker='*', s=100, color = 'black')
plt.scatter(0, mean_bill, marker='o', s=100, color = 'red', edgecolors = 'black', label = 'Среднее')
plt.legend()

plt.ticklabel_format(axis='y', style='plain')
plt.ylabel("Сумма чека", fontsize=15)
plt.title("Боксплот суммы чеков", fontsize=15)
plt.show()

Из графиков следует, что распределение смещено к началу координат (отличное от нормального), а значит медиана и среднее не равны. Медианное значение меньше среднего примерно на 220 рублей.
Теперь представим, что у маркетологов есть задача повысить средний чек покупателя. Маркетолог может решить, что поскольку средний чек равен 601 рублю, то можно предложить следующую акцию: «Всем покупателям, кто совершит покупку на 600 рублей, мы предоставляем скидку 20% на товар за 100 рублей». В целом, резонное предложение, однако, в реальности, средний чек ниже — 378 рублей. То есть большая часть покупателей не заинтересуется в предложении, поскольку их покупка обычно не достигает предложенного порога. Это значит. что они не воспользуются предложением и не получат скидку, а компания не сможет достичь поставленной цели и увеличить прибыль супермаркета. Все дело в том, что исходные предпосылки были ошибочны.

Выводы

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

У большинства игровых и мобильных компаний имеется кривая Retention, ранее мы писали о том, что такое Retention и как его посчитать. Вкратце — это метрика, которая позволяет понять насколько хорошо продукт вовлекает пользователей в ежедневное использование. А ещё при помощи Retention и ARPDAU можно посчитать LTV (Lifetime Value), пожизненный доход с одного пользователя. Зная средний доход с пользователя за день и кривую Retention мы можем смоделировать ее и спрогнозировать LTV.

Для материала были взяты данные из одного реального игрового проекта. Нулевой день не отображен для того, чтобы видеть динамику в деталях

В сегодняшнем материале мы подробно разберём, как смоделировать LTV для 180 дней при помощи SQL и просто линейной регрессии.

Как посчитать LTV?

В общем случае формула LTV выглядит как ARPDAU умноженное на Lifetime — время жизни пользователя в проекте.

Посмотрим на классический график Retention:

Lifetime — это площадь фигуры под Retention:

Откуда взялись интегралы и площади можно подробнее узнать в этом материале

Значит, чтобы посчитать Lifetime, нужно взять интеграл от функции удержания по времени. Формула приобретает следующий вид:

Для описания кривой Retention лучше всего подходит степенная функция a*x^b. Вот как она выглядит в сравнении с кривой Retention:

При этом x — номер дня, a и b — параметры функции, которую мы построим при помощи линейной регрессии. Регрессия появилась неслучайно — эту степенную функцию можно привести к виду линейной функции, логарифмируя:

ln(a) — intercept, b — slope. Остаётся найти эти параметры — в линейной регрессии для этого используют метод наименьших квадратов. Lifetime — кумулятивная сумма прогноза за 180 дней. Посчитав её, остаётся умножить Lifetime на ARPDAU и получим LTV за 180 дней.

Строим LTV

Перейдём к практике. Для всех расчётов мы использовали данные одной игровой компании и СУБД PostgreSQL — в ней уже реализованы функции поиска параметров для линейной регрессии. Начнём с построения Retention: соберём общее количество пользователей в период с 1 марта по 1 апреля 2021 года — мы изучаем активность за один месяц:

--общее количество юзеров в когорте
with cohort as (
    select count(distinct id) as total_users_of_cohort
    from users
    where date(registration) between date '2021-03-01' and date '2021-03-30'
),

Теперь посмотрим, как ведут себя эти пользователи в последующие 90 дней:

--количество активных юзеров на 1ый день, 2ой, 3ий и тд. из когорты
active_users as (
    select date_part('day', activity.date - users.registration) as activity_day, 
               count(distinct users.id) as active_users_of_day
    from activity
    join users on activity.user_id = users.id
    where date(registration) between date '2021-03-01' and date '2021-03-30' 
    group by 1
    having date_part('day', activity.date - users.registration) between 1 and 90 --берем только первые 90 дней, остальные дни предсказываем.
),

Кривая Retention — отношение количества активных пользователей к размеру когорты текущего дня. В нашем случае она выглядит так:

По данным кривой посчитаем параметры для линейной регрессии. regr_slope(x, y) — функция для вычисления наклона регрессии, regr_intercept(x, y) — функция для вычисления перехвата по оси Y. Эти функции являются стандартными агрегатными функциями в PostgreSQL и для известных X и Y по методу наименьших квадратов.

Вернёмся к нашей формуле — мы получили линейное уравнение, и хотим найти коэффициенты линейной регрессии. Перехват по оси Y и коэффициент наклона можем найти по дефолтным для PostgreSQL функциям. Получается:

Подробнее о том, как работают функции intercept(x, y) и slope(x, y) можно почитать в этом мануале

Из свойства натурального логарифма следует, что:

Наклон считаем аналогичным образом:

Эти же вычисления запишем в подзапрос для расчёта коэффициентов регрессии:

--рассчитываем коэффициенты регрессии
coef as (
    select exp(regr_intercept(ln(activity), ln(activity_day))) as a, 
                regr_slope(ln(activity), ln(activity_day)) as b
    from(
                select activity_day,
                            active_users_of_day::real / total_users_of_cohort as activity
                from active_users 
                cross join cohort order by activity_day 
            )
),

И получим прогноз на 180 дней, подставив параметры в степенную функцию, описанную ранее. Заодно посчитаем Lifetime — кумулятивную сумму спрогнозированных данных. В подзапросе coef мы получим только два числа — параметр наклона и перехвата. Чтобы эти параметры были доступны каждой строке подзапроса lt, делаем cross join к coef:

lt as(
    select generate_series as activity_day,
               active_users_of_day::real/total_users_of_cohort as real_data,
               a*power(generate_series,b) as pred_data, 	 
               sum(a*power(generate_series,b)) over(order by generate_series) as cumulative_lt
    from generate_series(1,180,1)
    cross join coef
    join active_users on generate_series = activity_day::int
),

Сравним прогноз на 180 дней с Retention:

Наконец, считаем сам LTV — Lifetime, умноженный на ARPDAU. В нашем случае ARPDAU равняется $83.7:

select cumulative_lt as LT,
           cumulative_lt * 83.7 as LTV
from lt

Наконец, построим график LTV на 180 дней:

Весь запрос:

--общее количество юзеров в когорте
with cohort as (
    select count(*) as total_users_of_cohort
    from users
    where date(registration) between date '2021-03-01' and date '2021-03-30'
),
--количество активных юзеров на 1ый день, 2ой, 3ий и тд. из когорты
active_users as (
    select date_part('day', activity.date - users.registration) as activity_day, 
               count(distinct users.id) as active_users_of_day
    from activity
    join users on activity.user_id = users.id
    where date(registration) between date '2021-03-01' and date '2021-03-30' 
    group by 1
    having date_part('day', activity.date - users.registration) between 1 and 90 --берем только первые 90 дней, остальные дни предсказываем.
),
--рассчитываем коэффициенты регрессии
coef as (
    select exp(regr_intercept(ln(activity), ln(activity_day))) as a, 
                regr_slope(ln(activity), ln(activity_day)) as b
    from(
                select activity_day,
                            active_users_of_day::real / total_users_of_cohort as activity
                from active_users 
                cross join cohort order by activity_day 
            )
),
lt as(
    select generate_series as activity_day,
               active_users_of_day::real/total_users_of_cohort as real_data,
               a*power(generate_series,b) as pred_data, 	 
               sum(a*power(generate_series,b)) over(order by generate_series) as cumulative_lt
    from generate_series(1,180,1)
    cross join coef
    join active_users on generate_series = activity_day::int
),
select cumulative_lt as LT,
            cumulative_lt * 83.7 as LTV
from lt

В марте мы опубликовали исследование «Python и тексты нового альбома Земфиры: анализируем суть песен», в котором при помощи Word2Vec-модели проанализировали близость песен альбома «бордерлайн» и получили самые близкие слова по духу альбома — ими оказались «пламень», «гореть», «тоска», «печаль», «сердце», «солнце» и другие.

Мы продолжили работу над альбомами Земфиры и проанализировали семь из них, а затем результаты собрали в один дашборд и опубликовали его в Tableau Public. Посмотрите, что получилось.

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

В этом материале мы расскажем, как парсить вакансии с сайта Indeed. Indeed — это крупнейший в мире поисковик вакансий. Этим текстом мы начинаем большой проект по анализу и визуализации показателей оплаты труда в области Data Science в разных странах.
Подобный анализ рынка вакансий, но только в России, мы проводили в материале Анализ рынка вакансий аналитики и BI: дашборд в Tableau, когда парсили данные с сайта HeadHunter.

А еще у нас можно почитать материал Парсим данные каталога сайта, используя Beautiful Soup и Selenium

Импорт библиотек
Библиотека fake_useragent имитирует реальный User-Agent, чтобы преодолеть защиту сайта от парсинга. Таким образом мы сможем пройти проверку HTTP заголовка User-Agent.
Модуль urllib.parse разбирает URL-адрес на компоненты и записывает его как кортеж. Он пригодится для перехода на карточки вакансий. BeautifulSoup поможет разобраться в структуре html-страницы и добыть нужную нам информацию.

import requests
from datetime import timedelta, datetime
import urllib.parse
from fake_useragent import UserAgent
from bs4 import BeautifulSoup
import pandas as pd
import time
from lxml.html import fromstring
from clickhouse_driver import Client
from clickhouse_driver import errors
import numpy as np
from funcs import check_title, get_skills_row, parse_salary, get_sheetname, create_table

Создадим таблицу в Clickhouse
Данные, которые мы собираемся собрать, будем хранить в базе Clickhouse.

create_table = '''CREATE TABLE if not exists indeed.vacancies (
    row_idx UInt16,
    query_string String,
    country String,
    title String,
    company String,
    city String,
    job_added Date,
    easy_apply UInt8,
    company_rating Nullable(Float32),
    remote UInt8,
    job_id String,
    job_link String,
    sheet String,
    skills String,
    added_date Date,
    month_salary_from_USD Float64,
    month_salary_to_USD Float64,
    year_salary_from_USD Float64,
    year_salary_to_USD Float64,
)
ENGINE = ReplacingMergeTree
SETTINGS index_granularity = 8192'''

Обход блокировок
Нам нужно обойти защиту Indeed и избежать блокировки по IP. Для этого используем анонимные прокси адреса на сайте free-proxy-list.net. Как собрать свежие прокси, мы писали в нашем предыдущем тексте «Пишем парсер свежих прокси на Python для Selenium». Прокси адреса мы запишем в массив, который понадобится в момент обращения к Indeed, когда запрос будет проверять User-Agent.

Данный метод удаляет IP из списка с прокси в том случае, если ответ от Indeed через него так и не пришел.

def remove_proxy_from_list_and_update_if_required(proxy):
    global _proxies
    _proxies.remove(proxy)
    if len(_proxies) == 0:
        update_proxy_list()

Функция, используя прокси, возвращает нам страницу Indeed, из которой мы впоследствии спарсим данные.

def get_page(updated_url, session):
    proxy = get_proxy()
    proxy_dict = {"http": proxy, "https": proxy}
    logger.info(f'try with proxy: {proxy}')
    try:
        session.proxies = proxy_dict
        return session.get(updated_url, timeout=15)
    except (requests.exceptions.RequestException, requests.exceptions.ProxyError, requests.exceptions.ConnectTimeout,
            requests.exceptions.ReadTimeout, requests.exceptions.SSLError,
            requests.exceptions.ConnectionError, url_ex.MaxRetryError, ConnectionResetError,
            socket.timeout, url_ex.ReadTimeoutError):
        remove_proxy_from_list_and_update_if_required(proxy)
        logger.info(f'try with proxy {proxy}')
        return get_page(updated_url, session)

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

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

def raw_date_to_str(raw_date):
    raw_date = raw_date.lower()
    if '+' in raw_date or "более" in raw_date:
        delta = timedelta(days=32)
        return (datetime.now() - delta).strftime("%Y-%m-%d")
    else:
        parts = raw_date.split()
        for part in parts:
            if part.isdigit():
                delta = timedelta(days=part.isdigit())
                return (datetime.now() - delta).strftime("%Y-%m-%d")
    return ""

Сохраним id вакансии в системе Indeed. Подставляя id в URL страницы, мы сможем получить доступ к полному описанию вакансий.

def get_job_id_from_card(card):
    try:
        return card['id'].split('_')[1]
    except:
        return ""

Данный метод соберет названия вакансий.

def get_title_from_card(card):
    try:
        job_title = card.find('a', {'class': 'jobtitle'}).text
        return job_title.replace('\n', '')
    except:
        return ''

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

URL сайта Indeed пишется для разных стран по-разному. Для США это будет просто indeed.com, а локализации для других стран получают префиксом xx.indeed.com. Список с префиксами мы собрали в массив заранее из https://opensource.indeedeng.io/api-documentation/docs/supported-countries/ списка Indeed.

def get_link_from_card(card, card_country):
    try:
        if card_country == 'us':
            return f"https://indeed.com{card.find('a', {'class': 'jobtitle'})['href']}"
        else:
            return f"https://{card_country}.indeed.com{card.find('a', {'class': 'jobtitle'})['href']}"
    except:
        return ""

Спарсим описание вакансии, которое можно найти по тегу ’summary’. Именно там содержатся требования, которые предъявляют к кандидату.

def get_summary_from_card_and_transform_to_skills(card):
    try:
        smr = card.find('div', {'class': 'summary'}).text
        return get_skills_row(smr)
    except:
        return ""
Необходимые hard-skills из описания вакансий будем сверять со списком 'skills'. 
skills = ["python", "tableau", "etl", "power bi", "d3.js", "qlik", "qlikview", "qliksense",
          "redash", "metabase", "numpy", "pandas", "congos", "superset", "matplotlib", "plotly",
          "airflow", "spark", "luigi", "machine learning", "amplitude", "sql", "nosql", "clickhouse",
          'sas', "hadoop", "pytorch", "tensorflow", "bash", "scala", "git", "aws", "docker",
          "linux", "kafka", "nifi", "ozzie", "ssas", "ssis", "redis", 'olap', ' r ', 'bigquery', 'api', 'excel']

Эта функция разобьет ’summary’ на слова пробелом и проверит их на соответствие нашему списку. В датасет будут возвращаться совпадения с нашим списком hard-skills.

def get_skills_row(summary):
    summary = summary.lower()
    row = []
    for sk in skills:
        if sk in summary:
            row.append(sk)
    return ','.join(row)

На выходе мы получим таблицу с примерно 30 тысячами строк.

Полный код проекта можно посмотреть в нашем репозитории на GitHub.

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

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

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

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

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 рост самого высокого.

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

Вариант 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.

Нормализация с помощью 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;

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

Неделю назад вышёл первый за 8 лет студийный альбом Земфиры «Бордерлайн». К работе помимо рок-певицы приложили руку разные люди, в том числе и её родственники — рифф для песни «таблетки» написал её племянник из Лондона. Альбом получился разнообразным: например, песня «остин» посвящена главному персонажу игры Homescapes российской студии Playrix (кстати, посмотрите свежие Бизнес-секреты с братьями Бухманами, там они тоже про это рассказывают) — Земфире нравится игра, и для трека она связалась со студией. А сингл «крым» был написан в качестве саундтрека к новой картине соратницы Земфиры — Ренаты Литвиновой.

Послушать альбом в Apple Music / Яндекс.Музыке / Spotify

Тем не менее, дух всего альбома довольно мрачен — в песнях часто повторяются слова «боль», «ад», «бесишь» и прочие по смыслу. Мы решили провести разведочный анализ нового альбома, а затем при помощи модели Word2Vec и косинусной меры посмотреть на семантическую близость песен между собой и вычислить общее настроение альбома.

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

Подготовка данных

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

import pandas as pd
import re
import string
import pymorphy2
from nltk.corpus import stopwords

Создаём морфологический анализатор и расширяем список всего, что нужно отбросить:

morph = pymorphy2.MorphAnalyzer()
stopwords_list = stopwords.words('russian')
stopwords_list.extend(['куплет', 'это', 'я', 'мы', 'ты', 'припев', 'аутро', 'предприпев', 'lyrics', '1', '2', '3', 'то'])
string.punctuation += '—'

Названия песен приведены на английском — создадим словарь для перевода на русский и словарь, из которого позднее сделаем таблицу:

result_dict = dict()

songs_dict = {
    'snow':'снег идёт',
    'crimea':'крым',
    'mother':'мама',
    'ostin':'остин',
    'abuse':'абьюз',
    'wait_for_me':'жди меня',
    'tom':'том',
    'come_on':'камон',
    'coat':'пальто',
    'this_summer':'этим летом',
    'ok':'ок',
    'pills':'таблетки'
}

Опишем несколько функций. Первая читает целиком песню из файла и удаляет переносы строки, вторая очищает текст от ненужных символов и слов, а третья при помощи морфологического анализатора pymorphy2 приводит слова к нормальной форме. Модуль pymorphy2 не всегда хорошо справляется с неоднозначностью — для слов «ад» и «рай» потребуется дополнительная обработка.

def read_song(filename):
    f = open(f'{filename}.txt', 'r').read()
    f = f.replace('\n', ' ')
    return f

def clean_string(text):
    text = re.split(' |:|\.|\(|\)|,|"|;|/|\n|\t|-|\?|\[|\]|!', text)
    text = ' '.join([word for word in text if word not in string.punctuation])
    text = text.lower()
    text = ' '.join([word for word in text.split() if word not in stopwords_list])
    return text

def string_to_normal_form(string):
    string_lst = string.split()
    for i in range(len(string_lst)):
        string_lst[i] = morph.parse(string_lst[i])[0].normal_form
        if (string_lst[i] == 'аду'):
            string_lst[i] = 'ад'
        if (string_lst[i] == 'рая'):
            string_lst[i] = 'рай'
    string = ' '.join(string_lst)
    return string

Проходим по каждой песне и читаем файл с соответствующим названием:

name_list = []
text_list = []
for song, name in songs_dict.items():
    text = string_to_normal_form(clean_string(read_song(song)))
    name_list.append(name)
    text_list.append(text)

Затем объединяем всё в DataFrame и сохраняем в виде csv-файла.

df = pd.DataFrame()
df['name'] = name_list
df['text'] = text_list
df['time'] = [290, 220, 187, 270, 330, 196, 207, 188, 269, 189, 245, 244]
df.to_csv('borderline.csv', index=False)

Результат:

Облако слов по всему альбому

Начнём анализ с построения облака слов — оно отобразит, какие слова чаще всего встречаются в песнях. Импортируем нужные библиотеки, читаем csv-файл и устанавливаем конфигурации:

import nltk
from wordcloud import WordCloud
import pandas as pd
import matplotlib.pyplot as plt
from nltk import word_tokenize, ngrams

%matplotlib inline
nltk.download('punkt')
df = pd.read_csv('borderline.csv')

Теперь создаём новую фигуру, устанавливаем параметры оформления и при помощи библиотеки wordcloud отображаем слова с размером прямо пропорциональным частоте упоминания слова. Над каждым графиком дополнительно указываем название песни.

fig = plt.figure()
fig.patch.set_facecolor('white')
plt.subplots_adjust(wspace=0.3, hspace=0.2)
i = 1
for name, text in zip(df.name, df.text):
    tokens = word_tokenize(text)
    text_raw = " ".join(tokens)
    wordcloud = WordCloud(colormap='PuBu', background_color='white', contour_width=10).generate(text_raw)
    plt.subplot(4, 3, i, label=name,frame_on=True)
    plt.tick_params(labelsize=10)
    plt.imshow(wordcloud)
    plt.axis("off")
    plt.title(name,fontdict={'fontsize':7,'color':'grey'},y=0.93)
    plt.tick_params(labelsize=10)
    i += 1

EDA текстов альбома

Теперь проанализируем тексты песен — импортируем библиотеки для работы с данными и визуализации:

import plotly.graph_objects as go
import plotly.figure_factory as ff
from scipy import spatial
import collections
import pymorphy2
import gensim

morph = pymorphy2.MorphAnalyzer()

Сначала посчитаем число слов в каждой песне, число уникальных слов и процентное соотношение:

songs = []
total = []
uniq = []
percent = []

for song, text in zip(df.name, df.text):
    songs.append(song)
    total.append(len(text.split()))
    uniq.append(len(set(text.split())))
    percent.append(round(len(set(text.split())) / len(text.split()), 2) * 100)

А теперь составим из этого DataFrame и дополнительно посчитаем число слов в минуту для каждой песни:

df_words = pd.DataFrame()
df_words['song'] = songs
df_words['total words'] = total
df_words['uniq words'] = uniq
df_words['percent'] = percent
df_words['time'] = df['time']
df_words['words per minute'] = round(total / (df['time'] // 60))
df_words = df_words[::-1]

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

colors_1 = ['rgba(101,181,205,255)'] * 12
colors_2 = ['rgba(62,142,231,255)'] * 12

fig = go.Figure(data=[
    go.Bar(name='📝 Всего слов',
           text=df_words['total words'],
           textposition='auto',
           x=df_words.song,
           y=df_words['total words'],
           marker_color=colors_1,
           marker=dict(line=dict(width=0)),),
    go.Bar(name='🌀 Уникальных слов',
           text=df_words['uniq words'].astype(str) + '<br>'+ df_words.percent.astype(int).astype(str) + '%' ,
           textposition='inside',
           x=df_words.song,
           y=df_words['uniq words'],
           textfont_color='white',
           marker_color=colors_2,
           marker=dict(line=dict(width=0)),),
])

fig.update_layout(barmode='group')

fig.update_layout(
    title = 
        {'text':'<b>Соотношение числа уникальных слов к общему количеству</b><br><span style="color:#666666"></span>'},
    showlegend = True,
    height=650,
    font={
        'family':'Open Sans, light',
        'color':'black',
        'size':14
    },
    plot_bgcolor='rgba(0,0,0,0)',
)
fig.update_layout(legend=dict(
    yanchor="top",
    xanchor="right",
))

fig.show()

colors_1 = ['rgba(101,181,205,255)'] * 12
colors_2 = ['rgba(238,85,59,255)'] * 12

fig = go.Figure(data=[
    go.Bar(name='⏱️ Длина трека, мин.',
           text=round(df_words['time'] / 60, 1),
           textposition='auto',
           x=df_words.song,
           y=-df_words['time'] // 60,
           marker_color=colors_1,
           marker=dict(line=dict(width=0)),
          ),
    go.Bar(name='🔄 Слов в минуту',
           text=df_words['words per minute'],
           textposition='auto',
           x=df_words.song,
           y=df_words['words per minute'],
           marker_color=colors_2,
           textfont_color='white',
           marker=dict(line=dict(width=0)),
          ),
])

fig.update_layout(barmode='overlay')

fig.update_layout(
    title = 
        {'text':'<b>Длина трека и число слов в минуту</b><br><span style="color:#666666"></span>'},
    showlegend = True,
    height=650,
    font={
        'family':'Open Sans, light',
        'color':'black',
        'size':14
    },
    plot_bgcolor='rgba(0,0,0,0)'
)


fig.show()

Работа с Word2Vec моделью

При помощи модуля gensim загружаем модель, указывая на бинарный файл:

model = gensim.models.KeyedVectors.load_word2vec_format('model.bin', binary=True)

Для материала мы использовали готовую обученную на Национальном Корпусе Русского Языка модель от сообщества RusVectōrēs

Модель Word2Vec основана на нейронных сетях и позволяет представлять слова в виде векторов, учитывая семантическую составляющую. Это означает, что если мы возьмём два слова — например, «мама» и «папа», представим их в виде двух векторов и посчитаем косинус, значения будет близко к 1. Аналогично, у двух слов, не имеющих ничего общего по смыслу косинусная мера близка к 0.

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

def get_vector(word_list):
    vector = 0
    for word in word_list:
        pos = morph.parse(word)[0].tag.POS
        if pos == 'INFN':
            pos = 'VERB'
        if pos in ['ADJF', 'PRCL', 'ADVB', 'NPRO']:
            pos = 'NOUN'
        if word and pos:
            try:
                word_pos = word + '_' + pos
                this_vector = model.word_vec(word_pos)
                vector += this_vector
            except KeyError:
                continue
    return vector

Для каждой песни находим вектор и собираем соответствующий столбец в DataFrame:

vec_list = []
for word in df['text']:
    vec_list.append(get_vector(word.split()))
df['vector'] = vec_list

Теперь сравним вектора между собой, посчитав их косинусную близость. Те песни, у которых косинусная метрика выше 0,5 запомним отдельно — так мы получим самые близкие пары песен. Данные о сравнении векторов запишем в двумерный список result.

similar = dict()
result = []
for song_1, vector_1 in zip(df.name, df.vector):
    sub_list = []
    for song_2, vector_2 in zip(df.name.iloc[::-1], df.vector.iloc[::-1]):
        res = 1 - spatial.distance.cosine(vector_1, vector_2)
        if res > 0.5 and song_1 != song_2 and (song_1 + ' / ' + song_2 not in similar.keys() and song_2 + ' / ' + song_1 not in similar.keys()):
            similar[song_1 + ' / ' + song_2] = round(res, 2)
        sub_list.append(round(res, 2))
    result.append(sub_list)

Самые похожие треки соберём в отдельный DataFrame:

df_top_sim = pd.DataFrame()
df_top_sim['name'] = list(similar.keys())
df_top_sim['value'] = list(similar.values())
df_top_sim.sort_values(by='value', ascending=False)

И построим такой же bar chart:

colors = ['rgba(101,181,205,255)'] * 5

fig = go.Figure([go.Bar(x=df_top_sim['name'],
                        y=df_top_sim['value'],
                        marker_color=colors,
                        width=[0.4,0.4,0.4,0.4,0.4],
                        text=df_top_sim['value'],
                        textfont_color='white',
                        textposition='auto')])

fig.update_layout(
    title = 
        {'text':'<b>Топ-5 схожих песен</b><br><span style="color:#666666"></span>'},
    showlegend = False,
    height=650,
    font={
        'family':'Open Sans, light',
        'color':'black',
        'size':14
    },
    plot_bgcolor='rgba(0,0,0,0)',
    xaxis={'categoryorder':'total descending'}
)

fig.show()

Имея вектор каждой песни, давайте посчитаем вектор всего альбома — сложим вектора песен. Затем для такого вектора при помощи модели получим самые близкие по духу и смыслу слова.

def get_word_from_tlist(lst):
    for word in lst:
        word = word[0].split('_')[0]
        print(word, end=' ')

vec_sum = 0
for vec in df.vector:
    vec_sum += vec
sim_word = model.similar_by_vector(vec_sum)
get_word_from_tlist(sim_word)

небо тоска тьма пламень плакать горе печаль сердце солнце мрак

Наверное, это ключевой результат и описание альбома Земфиры всего лишь в 10 словах.

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

colorscale=[[0.0, "rgba(255,255,255,255)"],
            [0.1, "rgba(229,232,237,255)"],
            [0.2, "rgba(216,222,232,255)"],
            [0.3, "rgba(205,214,228,255)"],
            [0.4, "rgba(182,195,218,255)"],
            [0.5, "rgba(159,178,209,255)"],
            [0.6, "rgba(137,161,200,255)"],
            [0.7, "rgba(107,137,188,255)"],
            [0.8, "rgba(96,129,184,255)"],
            [1.0, "rgba(76,114,176,255)"]]

font_colors = ['black']
x = list(df.name.iloc[::-1])
y = list(df.name)
fig = ff.create_annotated_heatmap(result, x=x, y=y, colorscale=colorscale, font_colors=font_colors)
fig.show()

Результаты анализа и интерпретация данных

Давайте ещё раз посмотрим на всё, что у нас получилось — начнём с облака слов. Нетрудно заметить, что у слов «боль», «невозможно», «сорваться», «растерзаны», «сложно», «терпеть», «любить» размер весьма приличный — всё потому, что такие слова встречаются часто на протяжении всего текста песен:

Одной из самых «разнообразных» песен оказался сингл «крым» — в нём 74% уникальных слов. А в песне «снег идёт» слов совсем мало, поэтому большинство — 82% уникальны. Самой большой песней в альбоме получился трек «таблетки» — суммарно там около 150 слов.

Как было выяснено на прошлом графике, самый «динамичный» трек — «таблетки», целых 37 слов в минуту — практически по слову на каждые две секунды. А самый длинный трек — «абъюз», в нём же и согласно предыдущему графику практически самый низкий процент уникальных слов — 46%.

Топ-5 самых семантически похожих пар текстов:

Ещё мы получили вектор всего альбома и подобрали самые близкие слова. Только посмотрите на них — «тьма», «тоска», «плакать», «горе», «печаль», «сердце» — это же ведь и есть тот перечень слов, который характеризует лирику Земфиры!

небо тоска тьма пламень плакать горе печаль сердце солнце мрак

Финал — тепловая карта. По визуализации заметно, что практически все песни достаточно схожи между собой — косинусная мера у многих пар превышает значение в 0.4.

Выводы

В материале мы провели EDA всего текста нового альбома и при помощи предобученной модели Word2Vec доказали гипотезу — большинство песен «бордерлайна» пронизывают довольно мрачные и тексты. И это нормально, ведь Земфиру мы любим именно за искренность и прямолинейность.

Для устройств на базе iOS и watchOS существует приложение Health, которое ежедневно записывает все данные о здоровье носителя и синхронизирует их со сторонними приложениями. Все эти данные в любой момент можно получить прямо из приложения в виде XML-документа. Сегодня мы выгрузим исторические данные о здоровье из приложения Apple Health, обработаем их и отправим в Google Sheets для анализа и визуализации в будущем.

Экспорт архива из приложения

Зайдите в приложение Health на iPhone. Нажмите на аватарку своего профиля в верхнем правом углу — откроется меню приложения.

Внизу нажмите на кнопку «Экспортировать медданные». Через некоторое время откроется меню экспорта — отправьте архив себе на компьютер любым способом, можно по AirDrop или даже по почте в письме самому себе. Из архива нужен только один файл — «экспорт.xml». Достаньте его и положите в папку с ноутбуком jupyter.

Парсер XML в DataFrame

При помощи библиотеки XML составляем дерево на основе документа из Health. Собирать в словарь будем следующие атрибуты: тип, единица измерения, дата создания, дата начала, дата конца, значение. Проходим по всему дереву и отправляем полученные значения атрибутов в records_dict.

from xml.etree import ElementTree
import pandas as pd
import datetime

tree = ElementTree.parse('экспорт.xml')
root = tree.getroot()
records = root.findall('Record')

records_dict = {
    'type':[],
    'unit':[],
    'creationDate':[],
    'startDate':[],
    'endDate':[],
    'value':[]
}

for record in records:
    for attribute in records_dict.keys():
        attribute_value = record.get(attribute)
        records_dict[attribute].append(attribute_value)

События записаны в нечитабельном виде — для перевода составим специальный словарь с нужными типами, где ключ — старое название, а значение — новое. Мы возьмём только 11 событий: минуты осознанности, дистанция на велосипеде, дистанция заплыва, дистанция ходьбы и бега, пройдено пролётов, пульс, пульс в покое, шаги, активная энергия, энергия покоя и средний пульс при ходьбе.

types_dict = {
    'HKCategoryTypeIdentifierMindfulSession': 'Mindful Session',
    'HKQuantityTypeIdentifierDistanceCycling': 'Cycling Distance',
    'HKQuantityTypeIdentifierDistanceSwimming': 'Swimming Distance',
    'HKQuantityTypeIdentifierDistanceWalkingRunning': 'Walking + Running Distance',
    'HKQuantityTypeIdentifierFlightsClimbed': 'Flights Climbed',
    'HKQuantityTypeIdentifierHeartRate': 'Heart Rate',
    'HKQuantityTypeIdentifierRestingHeartRate': 'Resting Heart Rate',
    'HKQuantityTypeIdentifierStepCount': 'Steps',
    'HKQuantityTypeIdentifierActiveEnergyBurned': 'Active Calories',
    'HKQuantityTypeIdentifierBasalEnergyBurned': 'Resting Calories',
    'HKQuantityTypeIdentifierWalkingHeartRateAverage': 'Walking Heart Rate Average'
}

Для минут осознанности в поле значения записей нет — мы сами посчитаем позже это поле как разницу даты окончания и начала события. Разница будет представлена как timedelta, поэтому напишем функцию перевода timedelta в минуты:

def td_to_m(td):
    seconds = td.seconds + td.days * 24 * 60 * 60
    return seconds // 60

Из словаря создаём DataFrame и задаём названия колонок. Оставляем только те 11 событий, которые есть в словаре types_dict и приводим все колонки к нужным типам данных:

df = pd.DataFrame(records_dict)
df.columns = ['type', 'unit', 'date', 'start', 'end', 'value']
df = df[df['type'].isin(types_dict.keys())]
df['value'] = df['value'].astype(float)
df['date'] = df['date'].astype('datetime64')
df['date'] = df['date'].dt.date
df['start'] = df['start'].astype('datetime64')
df['end'] = df['end'].astype('datetime64')
df['unit'] = df['unit'].astype(str)

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

df_1 = df[df['unit'] == 'count/min']
df_1 = df_1.groupby(by=['date', 'type', 'unit'], as_index=False).agg({'start':'min',
                                                                      'end':'max',
                                                                      'value':'mean'})

df_2 = df[df['type'] == 'HKCategoryTypeIdentifierMindfulSession']
df_2['value'] = df_2['end'] - df_2['start']
df_2['value'] = df_2['value'].map(td_to_m)
df_2 = df_2.groupby(by=['date', 'type', 'unit'], as_index=False).agg({'start':'min',
                                                                     'end':'max',
                                                                     'value':'sum'})
df_3 = df[(df['unit'] != 'count/min') & (df['type'] != 'HKCategoryTypeIdentifierMindfulSession')]
df_3 = df_3.groupby(by=['date', 'type', 'unit'], as_index=False).agg({'start':'min',
                                                                      'end':'max',
                                                                      'value':'sum'})
df = pd.concat([df_1, df_2, df_3])

Дату создания записи переводим в строковый тип. Все наименования типов событий заменяем согласно словарю types_dict. В переменную dates записываем все уникальные даты.

df['date'] = df['date'].astype(str)
df['type'] = df['type'].apply(lambda x: types_dict[x])
dates = df['date'].unique()

В результате нужен словарь с колонкой даты и отдельной колонкой под каждое из 11 событий:

result = {
    'date': [],
    'Steps': [],
    'Walking + Running Distance': [],
    'Swimming Distance': [],
    'Cycling Distance': [],
    'Resting Calories': [],
    'Active Calories': [],
    'Flights Climbed': [],
    'Heart Rate': [],
    'Resting Heart Rate': [],
    'Walking Heart Rate Average': [],
    'Mindful Session': []
}

Проходим по каждой дате и получаем кусок DataFrame за эту дату. Добавляем её в словарь и проходим по каждому ключу, пробуя добавить значение:

for date in dates:
    part = df[df['date'] == date]
    result['date'].append(date)
    for key in result.keys():
        if key == 'date':
            continue
        else:
            field = 'value'
        try:
            result[key].append(part[part['type'] == key][field].values[0])
        except IndexError:
            result[key].append(None)

Из полученного словаря создаём DataFrame, округляем всё до двух знаков после запятой и сортируем по дате:

result_df = pd.DataFrame(result)
result_df = result_df.round(2)
result_df = result_df.sort_values(by='date')

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

Экспорт DataFrame в Google Sheets

Для экспорта в Google Docs необходим сервисный аккаунт и json-файл с ключом. О том, как его получить, мы писали в материале «Собираем данные по рекламным кампаниям ВКонтакте»

Создайте новый документ в Google Sheets. Весь DataFrame можно вставить одним действием при помощи методов библиотеки gspread. Импортируйте её, а также укажите идентификатор документа и json-файл с ключом. В методе get_worksheet указывается порядковый номер листа в файле начиная с нуля.

import pandas as pd
import gspread
from gspread_dataframe import set_with_dataframe
gc = gspread.service_account(filename='serviceAccount.json')
sh = gc.open_by_key('1osKA63LQkUC0FC0eIZ63jEJwn1TeIkUvqCV6ur')
worksheet = sh.get_worksheet(0)

В итоге в Google Spreadsheets появится такая таблица:

А в следующем материале посмотрим, как наладить ежедневный экспорт данных Здоровья в эту таблицу при помощи шорткатов и Google AppScript!

С 9 по 13 ноября в онлайн-формате прошёл Матемаркетинг — крупнейшая конференция по маркетинговой аналитике в России, и в этом году мне посчастливилось стать одним из спикеров. Я выступил с двумя докладами, в этом материале обсудим первый — о современном облачном Data Stack.

Внутри объясняю подход к проектированию аналитической инфраструктуры, обосновываю использование Clickhouse при построении облачной аналитики и рассказываю о его же нюансах и говорю про Redash с точки зрения инструмента для визуализации.

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

Таблица из примера доступна в нашем репозитории на GitHub

Данные должны быть представлены в следующем виде, и если вы получаете их при помощи Custom SQL Query, вам может быть полезен наш последний материал: «UNPIVOT данных с использованием CROSS JOIN».

В таблице из примера мы получим 6 разных этапов: Identify, Pursue, Contact, Proposal, Negotiation и Won. Для каждого нужно задать соответствующее вычисляемое поле: например, ниже описана формула для вычисляемого поля статуса Identify.

if ATTR([Status]) = 'Identify' or LOOKUP(ATTR([Status]), -1)="Identify" then SUM([Value]) END

Итого должно получиться 6 новых мер:

Перетяните Measure Values в верхнюю часть графика: должен получиться такой bar chart:

Над графиком в выпадающем меню отображения графиков поменяйте Standart на Entrie View — график должен в ответ расшириться:

Из Measure Values удалите меры CNT(Sheet) и SUM(Value), которые не относятся к воронке:

Измерение Status перенесите в поле Rows. Получится несколько столбиков — это и есть будущие этапы воронки:

Убедитесь, что все вычисляемые поля вычислены при помощи Table (down):

А Stack marks установлен на off:

Смените тип графика на Area:

Чтобы каждый этап был окрашен в собственный цвет, перенесите измерение Status в поле Color:

В фильтре Status справа отсортируйте этапы по убыванию, перетягивая левой кнопкой мыши в нужное место:

Зажав клавишу CMD на MacOS или Ctrl на Windows, зажмите левой клавишей мыши Measures Values в поле Columns и протяните в область рядом: должно появиться такое же, а на графике с воронкой рядом должен появиться идентичный график.

Нажмите на ось X у левого графика и перейдите в меню Edit Axis. В разделе Scale поставьте галочку на поле Reversed, чтобы «отзеркалить» левый график.

Получится такая диаграмма:

Поработаем над оформлением. Нажмите правой кнопкой мыши на область с наименованием этапов слева и поставьте галочку напротив «Show Header». Проделайте то же самое с осью внизу:

Скройте также индикатор внизу:

Нажмите правой кнопкой мыши по графику и перейдите в раздел Format. Перейдите в меню Format Lines и смените значение для каждого типа линий на None. В соседнем разделе Format Borders также везде установите None:

Затем перенесите измерение Status и меру Value в поле Label. Нажмите на SUM(VALUE) и перейдите в Add Table Calculation, чтобы добавить ещё процент от первого этапа. В поле Calculation Type выберите «Percent From», а в поле Relative to — «First». Чтобы отобразить на каждом этапе процент от предыдущего: нажмите правой кнопкой мыши по мере SUM(Value) и нажмите на Add Table Calculation. В поле Calculation Type выберите «Percent From», а в поле Relative to — «Previous».

После нажмите на Label, перейдите в Edit Label и расположите текст с процентным соотношением под статусом:

Выравнивание установите, как на скриншоте:

Ещё перенесите в Tooltip меру Value, чтобы отображать в нём абсолютные значения. Затем нажмите на Tooltip и поменяйте форматирование:

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

На написание этой статьи нас вдохновил анлоязычный видеорецепт.

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

Лента по ценной бумаге в Пульсе выглядит вот так:

У каждого инвестора есть личный профиль. Внутри отображается объём портфеля, статистика прироста за год и число сделок за последний месяц.

Схема в Clickhouse

Для биграмм и частотного словаря достаточно собрать только тексты постов, логины пользователей и объёмы портфеля, но ради спортивного интереса будем хранить ещё и рост портфеля, число сделок человека за месяц и количество оценок под его постом. Для хранения данных получится две таблицы posts и users:

CREATE TABLE tinkoff.posts
(
    `login` String,
    `post` String,
    `likes` Int16
)
ENGINE = MergeTree
ORDER BY login

 CREATE TABLE tinkoff.users
(
    `login` String,
    `volume_prefix` String,
    `volume` String,
    `year_stats_prefix` String,
    `year_stats` String
)
ENGINE = MergeTree()
ORDER BY login

В таблице с пользователями volume_prefix — это префикс «до» или «от», стоящий в объёме портфеля, а volume — сам объём портфеля. Соответственно years_stats_prefix обозначает, портфель за год упал или вырос, а year_stats — на сколько он упал или вырос. Такая схема из двух таблиц с ключом сортировки таблиц по полю login позволит их соединить позднее.

Пишем парсер постов

У Пульса нет своего API, поэтому для парсинга постов будем использовать Selenium.

Мы уже писали про то, как парсить сайты с прокруткой при помощи Selenium

Нам понадобятся следующие библиотеки:

from selenium import webdriver
import time
from webdriver_manager.chrome import ChromeDriverManager
from clickhouse_driver import Client
from bs4 import BeautifulSoup as bs
import pandas as pd
import requests
import os
from lxml import html
import re

Сразу составим список интересующих ценных бумаг: это акции Сбербанка, Газпрома, Яндекса, Лукойла, MailRu, Аэрофлота, Киви, ВТБ, Детского Мира и Ленты. Для каждой бумаги будем переходить на страницу с постами, в которых она упоминается и проматывать страницу, пока длина страницы не станет более 300000: это около одной недели.

driver = webdriver.Chrome(ChromeDriverManager().install())

securities = ['SBER', 'GAZP', 'YNDX', 'LKOH', 'MAIL', 'AFLT', 'QIWI', 'VTBR', 'DSKY', 'LNTA']
        
for security in securities:
    try:
        print(security)
        driver.get(f'https://www.tinkoff.ru/invest/stocks/{security}/pulse/')
        
        page_length = driver.execute_script("return document.body.scrollHeight")
        while page_length < 300000:
            driver.execute_script(f"window.scrollTo(0, {page_length - 1000});")
            page_length = driver.execute_script("return document.body.scrollHeight")

После забираем себе весь код полученной страницы и извлекаем нужную информацию: логины, текст постов и число лайков. Формируем из них DataFrame и записываем его в папку data.

source_data = driver.page_source
        soup = bs(source_data, 'lxml')
        
        posts = soup.find_all('div', {'class':'PulsePostCollapsed__text_1ypMP'})
        logins = soup.find_all('div', {'class':'PulsePostAuthor__nicknameLink_19Aca'})
        likes = soup.find_all('div', {'class':'PulsePostBody__likes_3qcu0'})
        
        logins = [login.text for login in logins]
        posts = [post.text for post in posts]
        likes = [like.text.split()[0] for like in likes]
        
        df_posts = pd.DataFrame()
        df_posts['login'] = logins
        df_posts['post'] = posts
        df_posts['likes'] = likes
        
        df_posts.to_csv(f'data/{security}.csv', index=False)
        
        print(f'SAVED {security}')
    except Exception as E:
        print(E)

После того, как нужные посты собраны, отправим их в таблицу posts в Clickhouse. При помощи модуля os переходим в директорию data и собираем в список all_files названия всех файлов в ней — это все csv-таблицы, которые мы спарсили. Затем по очереди читаем файл в DataFrame и вставляем в posts.

client = Client(host='', user='', password='', port='9000', database='tinkoff')

os.chdir('data')
all_files = os.listdir()

for file in all_files:
    df = pd.read_csv(file)
    client.execute("INSERT INTO posts VALUES", df.to_dict('records'))

Собираем информацию о профилях

Чтобы собрать все профили, получим уникальный список логинов из базы:

flatten = lambda t: [item for sublist in t for item in sublist]
logins = flatten(client.execute("SELECT DISTINCT login FROM posts"))

Получать посты будем request-запросом, без Selenium, ведь ничего листать уже не нужно. Но иконка падения или роста портфеля за год не является текстом и получить её нельзя, зато внутри CSS-стилей можно увидеть её цвет — его мы и будем сохранять себе.

Поэтому опишем такую функцию: она примет объект soup и извлечёт цвет иконки.

headers = {'accept': '*/*',
           'user-agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/75.0.3770.142 Safari/537.36'}

def up_or_down_color(soup):
    string = str(soup.find('div', {'class':'Icon__container_3u7WK'}))
    start_index = string.find('style')
    color = string[start_index + 13:start_index + 20]
    return color

А теперь напишем парсер, который проходит по списку логинов, для каждого отправляет запрос и собирает всю статистику профиля. Причём если цвет иконки роста зеленый, то в поле year_stats_prefix добавим «+», иначе «-». В конце сделаем паузу на 0.2 секунды на всякий случай, чтобы не напороться на неявные ограничения.

session = requests.Session()

login_list = []
volume_list = []
volume_prefix_list = []
year_stats_list = []
year_stats_prefix_list = []

count = 0
for login in logins:
    print(count, '/', len(logins))
    
    try:
        count += 1
        if login == 'blocked_user':
            continue

        url = f'https://www.tinkoff.ru/invest/social/profile/{login}'
        request = session.get(url, headers=headers)
        soup = bs(request.content, 'lxml')

        try:
            login = soup.find('div', {'class':'ProfileHeader__nickname_1oynx'}).text
            volume = soup.find('span', {'class':'Money__money_3_Tn4'}).text
            _to = soup.find('div', {'class':'ProfileHeader__statistics_11-DO'}).text.find('До')
            _from = soup.find('div', {'class':'ProfileHeader__statistics_11-DO'}).text.find('От')
            year_stats = soup.find('div', {'class':'ProfileHeader__statisticsItem_1HPLt'}).text
            color = up_or_down_color(soup)
        except AttributeError as E:
            print(login, E)
            continue
        volume_list.append(volume)
        login_list.append(login)

        if _to == -1:
            volume_prefix_list.append('до')
        else:
            volume_prefix_list.append('от')

        year_stats_list.append(re.findall(r'\d+.+', year_stats)[0])

        if color == '#22a053':
            year_stats_prefix_list.append('-')
        elif color == '#dd5656':
            year_stats_prefix_list.append('+')
        else:
            year_stats_prefix_list.append('')
    except Exception as E:
        print(E)
        continue
    time.sleep(0.2)

Собираем все аккаунты и статистику по ним в DataFrame. Их тоже сохраним себе в базу.

df_users = pd.DataFrame()
df_users['login'] = login_list
df_users['volume_prefix'] = volume_prefix_list
df_users['volume'] = volume_list
df_users['year_stats_prefix'] = year_stats_prefix_list
df_users['year_stats'] = year_stats_list

client.execute("INSERT INTO users VALUES", df_users.to_dict('records'))

А теперь сделаем LEFT JOIN таблицы с постами к таблице с пользователями, чтобы у каждой строки с постом была ещё статистика по аккаунту автора. Запишем результат в DataFrame.

posts_with_users = client.execute('''
    SELECT login, post, likes, volume_prefix, volume, year_stats_prefix, year_stats FROM posts
    LEFT JOIN users
    ON posts.login = users.login
''')
posts_with_users_df = pd.DataFrame(posts_with_users, columns=['login', 'post', 'likes', 'volume_prefix', 'volume', 'year_stats_prefix', 'year_stats'])

Полученный результат будет выглядеть так:

Частотный словарь и биграммы

Для начала составим частотный словарь по постам без разделений на группы.

posts_with_users_df.post.str.split(expand=True).stack().value_counts()

Получим, что предлоги и союзы превалируют над остальными словами:

Значит, их нужно убрать из сета данных. Но даже в том случае, если данные будут очищены, получим что-то подобное. Такие данные ни о чём не говорят, и никаких выводов сделать не удастся.

В таком случае попробуем построить биграммы. Одна биграмма — последовательность из двух элементов, то есть два слова, стоящие рядом друг с другом. Существует много алгоритмов построения n-грамм разной степени оптимизации, мы воспользуемся встроенной функцией в nltk и разберём пример построения биграмм для одной группы. Первым делом импортируем дополнительные библиотеки, загружаем stopwords для русского языка и чистим данные. В список стоп-слов вносим дополнительные: среди них будут и тикеры акций, которые встречаются в каждом посте.

import nltk
from nltk.corpus import stopwords
from pymystem3 import Mystem
from string import punctuation
import unicodedata
import collections

nltk.download("stopwords")
nltk.download('punkt')
russian_stopwords = stopwords.words("russian")
append_stopword = ['это', 'sber', 'акция', 'компания', 'aflt', 'gazp', 'yndx', 'lkoh', 'mail', 'год', 'рынок', 'https', 'млрд', 'руб', 'www', 'кв']
russian_stopwords.extend(append_stopword)

Опишем функцию для подготовки текста, которая переведёт все слова в нижний регистр, приведёт к нормальной форме, удалит стоп-слова и пунктуацию:

mystem = Mystem() 

def preprocess_text(text):
    tokens = mystem.lemmatize(text.lower())
    tokens = [token for token in tokens if token not in russian_stopwords\
              and token != " " \
              and token.strip() not in punctuation]
    
    text = " ".join(tokens)
    
    return text

posts_with_users_df.post = posts_with_users_df.post.apply(preprocess_text)

Для примера возьмём посты группы инвесторов с объёмом портфеля до 10 тысяч рублей и построим биграммы, а затем выведем самые частые:

up_to_10k_df = posts_with_users_df[(posts_with_users_df['volume_prefix'] == 'от') & (posts_with_users_df['volume'] == '10 000 ₽')]

up_to_10k_counts = collections.Counter()
for sent in up_to_10k_df["post"]:
    words = nltk.word_tokenize(sent)
    up_to_10k_counts.update(nltk.bigrams(words))
up_to_10k_counts.most_common()

Получаем такой список:

Результаты исследования биграмм

В группе с объёмом портфеля до 10 и 100 тысяч руб. инвесторы чаще пишут о личном опыте и полученной прибыли: на это указывают биграммы «чистая прибыль» и «финансовый результат».

До 10 000 руб:

1. добрый утро, 44
2. цена нефть, 36
3. неквалифицированный инвестор, 36
4. чистый прибыль, 32
5. шапка профиль, 30
6. московский биржа, 30
7. совет директор, 28

До 100 000 руб:

1. чистый прибыль, 80
2. финансовый результат, 67
3. добрый утро, 66
4. индекс мосбиржа, 63
5. цена нефть, 58
6. квартал 2020, 42
7. мочь становиться 41

В группе до 500 тысяч руб. впервые появляются биграммы со словами «подписываться», «выкладывать», «новость» — инвесторы с такими характеристиками портфеля часто заводят собственные блоги об инвестировании и продвигают их через посты в Пульсе.

До 500 000 руб:

1. чистый прибыль, 169
2. квартал 2020, 154
3. отчетность 3, 113
4. выкладывать новость, 113
5. 🤝подписываться, 80
6. подписываться выкладывать, 80
7. публиковать отчёт, 80
8. цена нефть, 76
9. колво бумага, 69
10. вес портфель, 68

В биграммах группы с объёмом портфеля до и от 1 миллиона руб. появляется «фьючерс», что логично — это сложный инструмент, который обычно не рекомендуется новичкам. Кроме того, в постах группы проходит больше обсуждений отчётностей компаний — это биграммы «финансовый отчетность», «опубликовать финансовый», «отчетность мсфо».

До 1 000 000 руб:

1. 3 квартал, 183
2. квартал 2020, 157
3. фьчерс утро, 110
4. финансовый отчетность, 107
5. опубликовать финансовый, 104
6. чистый прибыль, 75
7. наш биржа, 72
8. отчетность мсфо, 69
9. цена нефть, 67
10. операционный результат, 61
11. ноябрьский фьючерс, 54
12. азиатский площадка, 51

От 1 000 000 руб:

1. октябрь опубликовывать, 186
2. 3 квартал, 168
3. квартал 2020, 168
4. финансовый отчетность, 159,
5. опубликовывать финансовый, 95
6. чистый прибыль, 94
7. операционный результат, 86
8. целевой цена, 74
9. опубликовывать операционный, 63
10. цена повышать, 60

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

Иногда у экономистов возникает желание сравнить уровень жизни в разных странах. Одной из таких опций считается индекс Биг Мака, учёт которого журнал «The Economist» ведёт с 1986 года. Основная мысль — изучить паритет покупательской способности в разных странах, максимально учитывая стоимость внутреннего производства. В производстве Биг Мака участвует стандартный набор ингредиентов, одинаковый во всех странах: сыр, мясо, хлеб и овощи. Считается, что все эти ингредиенты произведены локально, а, значит, цена на Биг Мак позволяет сравнивать покупательскую способность в разных странах на данный товар. Помимо этого, McDonalds — глобальный бренд и его рестораны есть в огромном количестве стран, что обеспечивает широкий охват Биг Маком.

Сегодня при помощи библиотеки Plotly построим Motion Chart для индекса Биг Мака. Мы, следуя за Hann Rosling, хотим получить Motion Chart, где по оси X будет численность населения, по Y — ВВП на душу населения в долларах, а размер точек будет обозначать индекс Биг Мака в данной стране. Кроме того, цвет точки будет обозначать континент, на котором расположилась страна.

Подготовка данных

Хотя «The Economist» ведёт учёт уже более 30 лет и делится своими наблюдениями в интернете, датасет содержит множество пропусков по разным странам. В то же время в датасете журнала не представлены названия континентов, к которым принадлежат страны и численность населения. Поэтому мы дополним данные журнала тремя другими датасетами, представленными в нашем репозитории.

Начнём с импорта библиотек:

import pandas as pd
from pandas.errors import ParserError
import plotly.graph_objects as go
import numpy as np
import requests
import io

Прочитаем все 4 датасета прямо из GitHub. Для этого опишем функцию, которая отправляет GET-запрос к csv-файлу и формирует из него DataFrame. По двум датасетам может возникнуть ошибка ParseError из-за наличия подписи в заглавии: пропустим несколько строк, если это произошло.

def read_raw_file(link):
    raw_csv = requests.get(link).content
    try:
        df = pd.read_csv(io.StringIO(raw_csv.decode('utf-8')))
    except ParserError:
        df = pd.read_csv(io.StringIO(raw_csv.decode('utf-8')), skiprows=3)
    return df

bigmac_df = read_raw_file('https://github.com/valiotti/leftjoin/raw/master/motion-chart-big-mac/big-mac.csv')
population_df = read_raw_file('https://github.com/valiotti/leftjoin/raw/master/motion-chart-big-mac/population.csv')
dgp_df = read_raw_file('https://github.com/valiotti/leftjoin/raw/master/motion-chart-big-mac/gdp.csv')
continents_df = read_raw_file('https://github.com/valiotti/leftjoin/raw/master/motion-chart-big-mac/continents.csv')

От датасета «The Economist» оставим только название страны, местную цену, курс доллара, код страны и дату записи. После оставим строки, записанные между 2005 и 2020 годом: данные за этот период наиболее полные. Последним действием посчитаем цену на Биг Мак в долларах: для этого цену в местной валюте поделим на валютный курс.

bigmac_df = bigmac_df[['name', 'local_price', 'dollar_ex', 'iso_a3', 'date']]
bigmac_df = bigmac_df[bigmac_df['date'] >= '2005-01-01']
bigmac_df = bigmac_df[bigmac_df['date'] < '2020-01-01']
bigmac_df['date'] = pd.DatetimeIndex(bigmac_df['date']).year
bigmac_df = bigmac_df.drop_duplicates(['date', 'name'])
bigmac_df = bigmac_df.reset_index(drop=True)
bigmac_df['dollar_price'] = bigmac_df['local_price'] / bigmac_df['dollar_ex']

Взглянем на наш DataFrame:

У нас есть датасет с континентами и странами, и нужно к bigmac_df добавить колонку «continents». Для удобства оставим от continents_df только колонки с названием континента и трёхбуквенным кодом страны, а затем для каждой страны в bigmac_df найдём континент. В случае, например, с Россией или с Турцией может произойти ошибка, ведь нельзя однозначно сказать, Европа это или Азия, так что такие страны будем определять как европейские.

continents_df = continents_df[['Continent_Name', 'Three_Letter_Country_Code']]
continents_list = []
for country in bigmac_df['iso_a3']:
    try:
        continents_list.append(continents_df.loc[continents_df['Three_Letter_Country_Code'] == country]['Continent_Name'].item())
    except ValueError:
        continents_list.append('Europe')
bigmac_df['continent'] = continents_list

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

bigmac_df = bigmac_df.drop(['local_price', 'iso_a3', 'dollar_ex'], axis=1)
bigmac_df = bigmac_df.sort_values(by=['name', 'date'])
bigmac_df['date'] = bigmac_df['date'].astype(int)

Заполним пробелы: по тем годам, где нет данных и установим цену в 0 долларов. Ещё придётся удалить Китайскую Республику — Тайвань: это частично признанное государство отсутствует в датасетах World Bank. А Арабские Эмираты повторяются дважды, с этим тоже могут возникнуть проблемы.

countries_list = list(bigmac_df['name'].unique())
years_set = {i for i in range(2005, 2020)}
for country in countries_list:
    if len(bigmac_df[bigmac_df['name'] == country]) < 15:
        this_continent = bigmac_df[bigmac_df['name'] == country].continent.iloc[0]
        years_of_country = set(bigmac_df[bigmac_df['name'] == country]['date'])
        diff = years_set - years_of_country
        dict_to_df = pd.DataFrame({
                      'name':[country] * len(diff),
                      'date':list(diff),
                      'dollar_price':[0] * len(diff),
                      'continent': [this_continent] * len(diff)
                     })
        bigmac_df = bigmac_df.append(dict_to_df)
bigmac_df = bigmac_df[bigmac_df['name'] != 'Taiwan']
bigmac_df = bigmac_df[bigmac_df['name'] != 'United Arab Emirates']

Осталось добавить ВВП на душу населения и численность населения из других датасетов. В обоих датасетах многие страны записаны иначе, поэтому пропишем словарь и переименуем все страны в обоих датасетах методом replace().

years = [str(i) for i in range(2005, 2020)]

countries_replace_dict = {
    'Russian Federation': 'Russia',
    'Egypt, Arab Rep.': 'Egypt',
    'Hong Kong SAR, China': 'Hong Kong',
    'United Kingdom': 'Britain',
    'Korea, Rep.': 'South Korea',
    'United Arab Emirates': 'UAE',
    'Venezuela, RB': 'Venezuela'
}
for key, value in countries_replace_dict.items():
    population_df['Country Name'] = population_df['Country Name'].replace(key, value)
    gdp_df['Country Name'] = gdp_df['Country Name'].replace(key, value)

Наконец, соберём данные по численности и ВВП за нужные года и добавим в основной DataFrame:

countries_list = list(bigmac_df['name'].unique())

population_list = []
gdp_list = []
for country in countries_list:
    population_for_country_df = population_df[population_df['Country Name'] == country][years]
    population_list.extend(list(population_for_country_df.values[0]))
    gdp_for_country_df = gdp_df[gdp_df['Country Name'] == country][years]
    gdp_list.extend(list(gdp_for_country_df.values[0]))
    
bigmac_df['population'] = population_list
bigmac_df['gdp'] = gdp_list
bigmac_df['gdp_per_capita'] = bigmac_df['gdp'] / bigmac_df['population']

В итоге получили такой датасет:

Формируем график в plotly

Логарифмируем значения по оси X. В Китае и Индии, например, население в 10 раз больше, чем в среднем в других странах: из-за этого получим сложно интерпретируемую визуализацию, в которой у нас будет много наблюдений около оси и несколько наблюдений справа. Логарифмирование — часто используемый экономистами прием для учета эффекта масштаба в данных.

fig_dict = {
    "data": [],
    "layout": {},
    "frames": []
}

fig_dict["layout"]["xaxis"] = {"title": "Численность населения", "type": "log"}
fig_dict["layout"]["yaxis"] = {"title": "ВВП на душу населения (в $)", "range":[-10000, 120000]}
fig_dict["layout"]["hovermode"] = "closest"
fig_dict["layout"]["updatemenus"] = [
    {
        "buttons": [
            {
                "args": [None, {"frame": {"duration": 500, "redraw": False},
                                "fromcurrent": True, "transition": {"duration": 300,
                                                                    "easing": "quadratic-in-out"}}],
                "label": "Play",
                "method": "animate"
            },
            {
                "args": [[None], {"frame": {"duration": 0, "redraw": False},
                                  "mode": "immediate",
                                  "transition": {"duration": 0}}],
                "label": "Pause",
                "method": "animate"
            }
        ],
        "direction": "left",
        "pad": {"r": 10, "t": 87},
        "showactive": False,
        "type": "buttons",
        "x": 0.1,
        "xanchor": "right",
        "y": 0,
        "yanchor": "top"
    }
]

Помимо кнопок у нас будет Slider, позволяющий получать данные за определённый год:

sliders_dict = {
    "active": 0,
    "yanchor": "top",
    "xanchor": "left",
    "currentvalue": {
        "font": {"size": 20},
        "prefix": "Год: ",
        "visible": True,
        "xanchor": "right"
    },
    "transition": {"duration": 300, "easing": "cubic-in-out"},
    "pad": {"b": 10, "t": 50},
    "len": 0.9,
    "x": 0.1,
    "y": 0,
    "steps": []
}

Для статичного графика до нажатия на кнопку «Start» возьмём данные за 2005 год и заполним ими поле data фигуры.

continents_list_from_df = list(bigmac_df['continent'].unique())
year = 2005
for continent in continents_list_from_df:
    dataset_by_year = bigmac_df[bigmac_df["date"] == year]
    dataset_by_year_and_cont = dataset_by_year[dataset_by_year["continent"] == continent]
    
    data_dict = {
        "x": dataset_by_year_and_cont["population"],
        "y": dataset_by_year_and_cont["gdp_per_capita"],
        "mode": "markers",
        "text": dataset_by_year_and_cont["name"],
        "marker": {
            "sizemode": "area",
            "sizeref": 200000,
            "size":  np.array(dataset_by_year_and_cont["dollar_price"]) * 20000000
        },
        "name": continent,
        "customdata": np.array(dataset_by_year_and_cont["dollar_price"]).round(1),
        "hovertemplate": '<b>%{text}</b>' + '<br>' +
                         'ВВП на душу населения: %{y}' + '<br>' +
                         'Численность населения: %{x}' + '<br>' +
                         'Стоимость Биг Мака: %{customdata}$' +
                         '<extra></extra>'
    }
    fig_dict["data"].append(data_dict)

А для анимации заполним поле frames. Каждый frame — данные за год с 2005 по 2019.

for year in years:
    frame = {"data": [], "name": str(year)}
    for continent in continents_list_from_df:
        dataset_by_year = bigmac_df[bigmac_df["date"] == int(year)]
        dataset_by_year_and_cont = dataset_by_year[dataset_by_year["continent"] == continent]

        data_dict = {
            "x": list(dataset_by_year_and_cont["population"]),
            "y": list(dataset_by_year_and_cont["gdp_per_capita"]),
            "mode": "markers",
            "text": list(dataset_by_year_and_cont["name"]),
            "marker": {
                "sizemode": "area",
                "sizeref": 200000,
                "size": np.array(dataset_by_year_and_cont["dollar_price"]) * 20000000
            },
            "name": continent,
            "customdata": np.array(dataset_by_year_and_cont["dollar_price"]).round(1),
            "hovertemplate": '<b>%{text}</b>' + '<br>' +
                             'ВВП на душу населения: %{y}' + '<br>' +
                             'Численность населения: %{x}' + '<br>' +
                             'Стоимость Биг Мака: %{customdata}$' +
                             '<extra></extra>'
        }
        frame["data"].append(data_dict)

    fig_dict["frames"].append(frame)
    slider_step = {"args": [
        [year],
        {"frame": {"duration": 300, "redraw": False},
         "mode": "immediate",
         "transition": {"duration": 300}}
    ],
        "label": year,
        "method": "animate"}
    sliders_dict["steps"].append(slider_step)

Наконец, создадим объект графика, поправим цвета, шрифты и добавим описание.

fig_dict["layout"]["sliders"] = [sliders_dict]

fig = go.Figure(fig_dict)

fig.update_layout(
    title = 
        {'text':'<b>Motion chart</b><br><span style="color:#666666"> Биг Мака для стран мира с 2005 по 2019 год </span>'},
    font={
        'family':'Open Sans, light',
        'color':'black',
        'size':14
    },
    plot_bgcolor='rgba(0,0,0,0)'
)
fig.update_yaxes(nticks=4)
fig.update_xaxes(tickfont=dict(family='Open Sans, light', color='black', size=12), nticks=4, gridcolor='lightgray', gridwidth=0.5)
fig.update_yaxes(tickfont=dict(family='Open Sans, light', color='black', size=12), nticks=4, gridcolor='lightgray', gridwidth=0.5)

fig.show()

В итоге получаем такой Motion Chart:

Полный код проекта доступен на GitHub

В некоторых телеграм-каналах (раз, два) уже говорилось про другие интересные паблики в телеграме, однако по Instagram такого топа пока не было. Вероятно, это не самая популярная сеть для контента в нашей индустрии, тем не менее, можно проверить эту гипотезу, используя Python и данные. В этом материале рассказываем, как собрать данные по аккаунтам Instagram без API.

Метод сбора данных
Instagram API не позволит вам просто так собирать данные о других пользователях, но есть и другой метод. Можно отправить такой request-запрос:

https://instagram.com/leftjoin/?__a=1

И получить в ответе JSON-объект со всей информацией о пользователе, которую можно посмотреть самому: имя аккаунта, количество постов, подписок и подписчиков, а также первые десять постов с информацией про них: количество лайков, комментарии и прочее. Именно на таких request-запросах устроена библиотека pyInstagram.

Схема данных
Будем собирать данные в три таблицы Clickhouse: пользователи, посты и комментарии. В таблицу пользователей собираем всю информацию о них: идентификатор, наименование аккаунта, имя и фамилия человека, описание профиля, количество подписок и подписчиков, количество постов, суммарное количество комментариев и лайков, наличие верификации, география пользователя и ссылки на аватарку и Facebook.

CREATE TABLE instagram.users
(
    `added_at` DateTime,
    `user_id` UInt64,
    `user_name` String,
    `full_name` String,
    `base_url` String,
    `biography` String,
    `followers_count` UInt64,
    `follows_count` UInt64,
    `media_count` UInt64,
    `total_comments` UInt64,
    `total_likes` UInt64,
    `is_verified` UInt8,
    `country_block` UInt8,
    `profile_pic_url` Nullable(String),
    `profile_pic_url_hd` Nullable(String),
    `fb_page` Nullable(String)
)
ENGINE = ReplacingMergeTree
ORDER BY added_at

В таблицу с постами сохраняем автора поста, идентификатор записи, текст, количество комментариев и прочее. is_ad, is_album и is_video — поля, проверяющие, является ли запись рекламной, «каруселью» изображений или видеозаписью.

CREATE TABLE instagram.posts
(
    `added_at` DateTime,
    `owner` String,
    `post_id` UInt64,
    `caption` Nullable(String),
    `code` String,
    `comments_count` UInt64,
    `comments_disabled` UInt8,
    `created_at` DateTime,
    `display_url` String,
    `is_ad` UInt8,
    `is_album` UInt8,
    `is_video` UInt8,
    `likes_count` UInt64,
    `location` Nullable(String),
    `recources` Array(String),
    `video_url` Nullable(String)
)
ENGINE = ReplacingMergeTree
ORDER BY added_at

В таблице с комментариями храним отдельно каждый комментарий к записи с автором и текстом.

CREATE TABLE instagram.comments
(
    `added_at` DateTime,
    `comment_id` UInt64,
    `post_id` UInt64,
    `comment_owner` String,
    `comment_text` String
)
ENGINE = ReplacingMergeTree
ORDER BY added_at

Скрипт
Из библиотеки pyInstagram нам понадобятся классы Account, Media, WebAgent и Comment.

from instagram import Account, Media, WebAgent, Comment
from datetime import datetime
from clickhouse_driver import Client
import requests
import pandas as pd

Создаем экземпляр класса WebAgent — он необходим для вызова некоторых методов и обновления аккаунтов. В начале нам нужно иметь хотя бы названия профилей пользователей, информацию о которых мы хотим собрать, поэтому отправим другой request-запрос для поиска пользователей по ключевым словам, их список ниже в фрагменте кода. В выдаче будут аккаунты, у которых название или описание профиля совпало с ключевым словом.

agent = WebAgent()
queries_list = ['machine learning', 'data science', 'data analytics', 'analytics', 'business intelligence',
                'data engineering', 'computer science', 'big data', 'artificial intelligence',
                'deep learning', 'data scientist','machine learning engineer', 'data engineer']
client = Client(host='12.34.56.789', user='default', password='', port='9000', database='instagram')
url = 'https://www.instagram.com/web/search/topsearch/?context=user&count=0'

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

response_list = []
for query in queries_list:
    response = requests.get(url, params={
        'query': query
    }).json()
    response_list.extend(response['users'])
instagram_pages_list = []
for item in response_list:
    instagram_pages_list.append(item['user']['username'])
instagram_pages_list = list(set(instagram_pages_list))

Теперь проходим по списку аккаунтов, и если аккаунта с таким наименованием ещё не было в базе, то получаем расширенную информацию о нём. Для этого пробуем создать экземпляр класса Account, передав username параметром. После при помощи объекта agent обновляем информацию об аккаунте. Будем собирать только первые 100 постов, чтобы сбор не задерживался. Создадим список media_list — он при помощи метода get_media будет хранить код каждого поста, который затем можно будет получить при помощи класса Media.

all_posts_list = []
username_count = 0
for username in instagram_pages_list:
    if client.execute(f"SELECT count(1) FROM users WHERE user_name='{username}'")[0][0] == 0:
        print('username:', username_count, '/', len(instagram_pages_list))
        username_count += 1
        account_total_likes = 0
        account_total_comments = 0
        try:
            account = Account(username)
        except Exception as E:
            print(E)
            continue
        try:
            agent.update(account)
        except Exception as E:
            print(E)
            continue
        if account.media_count < 100:
            post_count = account.media_count
        else:
            post_count = 100
        print(account, post_count)
        media_list, _ = agent.get_media(account, count=post_count, delay=1)
        count = 0

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

for media_code in media_list:
            if client.execute(f"SELECT count(1) FROM posts WHERE code='{media_code}'")[0][0] == 0:
                print('posts:', count, '/', len(media_list))
                count += 1

                post_insert_list = []
                post = Media(media_code)
                agent.update(post)
                post_insert_list.append(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
                post_insert_list.append(str(post.owner))
                post_insert_list.append(post.id)
                if post.caption is not None:
                    post_insert_list.append(post.caption.replace("'","").replace('"', ''))
                else:
                    post_insert_list.append("")
                post_insert_list.append(post.code)
                post_insert_list.append(post.comments_count)
                post_insert_list.append(int(post.comments_disabled))
                post_insert_list.append(datetime.fromtimestamp(post.date).strftime('%Y-%m-%d %H:%M:%S'))
                post_insert_list.append(post.display_url)
                try:
                    post_insert_list.append(int(post.is_ad))
                except TypeError:
                    post_insert_list.append('cast(Null as Nullable(UInt8))')
                post_insert_list.append(int(post.is_album))
                post_insert_list.append(int(post.is_video))
                post_insert_list.append(post.likes_count)
                if post.location is not None:
                    post_insert_list.append(post.location)
                else:
                    post_insert_list.append('')
                post_insert_list.append(post.resources)
                if post.video_url is not None:
                    post_insert_list.append(post.video_url)
                else:
                    post_insert_list.append('')
                account_total_likes += post.likes_count
                account_total_comments += post.comments_count
                try:
                    client.execute(f'''
                        INSERT INTO posts VALUES {tuple(post_insert_list)}
                    ''')
                except Exception as E:
                    print('posts:')
                    print(E)
                    print(post_insert_list)

Чтобы собрать комментарии необходимо вызвать метод get_comments и передать параметром экземпляр класса Media.

comments = agent.get_comments(media=post)
                for comment_id in comments[0]:
                    comment_insert_list = []
                    comment = Comment(comment_id)
                    comment_insert_list.append(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
                    comment_insert_list.append(comment.id)
                    comment_insert_list.append(post.id)
                    comment_insert_list.append(str(comment.owner))
                    comment_insert_list.append(comment.text.replace("'","").replace('"', ''))
                    try:
                        client.execute(f'''
                            INSERT INTO comments VALUES {tuple(comment_insert_list)}
                        ''')
                    except Exception as E:
                        print('comments:')
                        print(E)
                        print(comment_insert_list)

user_insert_list = []
        user_insert_list.append(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
        user_insert_list.append(account.id)
        user_insert_list.append(account.username)
        user_insert_list.append(account.full_name)
        user_insert_list.append(account.base_url)
        user_insert_list.append(account.biography)
        user_insert_list.append(account.followers_count)
        user_insert_list.append(account.follows_count)
        user_insert_list.append(account.media_count)
        user_insert_list.append(account_total_comments)
        user_insert_list.append(account_total_likes)
        user_insert_list.append(int(account.is_verified))
        user_insert_list.append(int(account.country_block))
        user_insert_list.append(account.profile_pic_url)
        user_insert_list.append(account.profile_pic_url_hd)
        if account.fb_page is not None:
            user_insert_list.append(account.fb_page)
        else:
            user_insert_list.append('')
        try:
            client.execute(f'''
                INSERT INTO users VALUES {tuple(user_insert_list)}
            ''')
        except Exception as E:
            print('users:')
            print(E)
            print(user_insert_list)

Результаты
Таким методом нам удалось собрать 500 пользователей, 20 тысяч постов и 40 тысяч комментариев. Теперь можем написать простой запрос к базе и получить топ-10 Instagram-аккаунтов по теме аналитики и машинного обучения за последнее время:

SELECT *
FROM users
ORDER BY followers_count DESC
LIMIT 10

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

1. @ai_machine_learning
2. @neuralnine
3. @datascienceinfo
4. @compscistuff
5. @computersciencelife
6. @welcome.ai
7. @papa_programmer
8. @data_science_learn
9. @neuralnet.ai
10. @techno_thinkers

Полный код проекта доступен на GitHub

C 8 по 9 сентября состоялась онлайн-конференция FutureData, в которой я принял участие. Вчера организаторы опубликовали записи докладов. Хочу поделиться своими наблюдениями и докладами, которые заинтересовали меня. Постарался собрать максимально релевантные скриншоты, но сразу извиняюсь за их качество, выдирал прямо из видео.

Featured Keynote: Automating Analysis
Спикер: Pat Hanrahan
В докладе профессор Стэнфордского университета и сооснователь Tableau рассуждает об использовании AI в аналитике. Доклад получился монотонным, и большую часть времени Pat обсуждает где мы сейчас, как мы используем AI, однако секция вопросов и ответов получилась интересная.

The Modern Data Stack: Past, Present, and Future
Спикер: Tristan Handy
Автор знаменитой публикации о руководстве по аналитике для основателя стартапа и создатель dbt рассуждает о том, как менялся современный data-stack с 2012 по 2020 год. Для меня доклад оказался наиболее интересным, особенно учитывая, что Tristan делает предсказания о том, что будет расти и развиваться в data-stack в ближайшее время.

Making Enterprise Data Timelier and More Reliable with Lakehouse Technology
Спикер: Matei Zaharia
Доклад главного технолога DataBricks. К сожалению, в докладе большие проблемы с аудио, но Matei рассматривает проблемы современного Data Lake, а дальше продвигает технологию DataBricks — DeltaLake. Как по мне, доклад получился рекламным, но послушать интересно.

How to Close the Analytic Divide
Спикер: Alan Jacobson
Chief Data Officer из Alteryx рассуждает о профессии Data Scientist и приводит статистику по зарплатам, в которой средняя зарплата специалиста по данным существенно выше, чем у остальных аналитиков. К слову, наше недавнее исследование с Ромой Буниным это подтверждает. Далее Alan обсуждает выручку компаний, находящихся на разных стадиях аналитического развития. Более развитые — (сюрприз!) растут быстрее. Отдельная часть доклада посвящена изменениям в трансформации к подходу к работе с данными, а в конце небольшое рекламное интро Alteryx. Доклад смотрится легко.

Hot Analytics — Handle with Care
Спикер: Gian Merlino
Co-Founder и CTO Imply приводит сравнение hot & cold data (намек на Snowflake?). Затем — демонстрация некоторой BI от Imply с простеньким интерфейсом и реализованным drag-n-drop. Далее Gian рассказывает о возможных аналитических архитектурах и затрагивает тему Druid, на которой построен Imply.

По данным рейтинга SimilarWeb, hh.ru — третий по популярности сайт о трудоустройстве в мире. В одном из разговоров с Ромой Буниным у нас появилась идея сделать совместный проект: собрать данные из открытого HeadHunter API и визуализировать их при помощи Tableau Public. Нам захотелось понять, как меняется зарплата в зависимости от указанных в вакансии навыков, наименования позиции и сравнить, как обстоят дела в Москве, Санкт-Петербурге и регионах.

Как мы собирали данные?

Схема данных основана на коротком представлении вакансии, которую возвращает метод GET /vacancies. Из представления собираются следующие поля: тип вакансии, идентификатор, премиальность вакансии, необходимость прохождения тестирования, адрес компании, информация о зарплате, график работы и другие. Соответствующий CREATE-запрос для таблицы:

CREATE TABLE headhunter.vacancies_short
(
    `added_at` DateTime,
    `query_string` String,
    `type` String,
    `level` String,
    `direction` String,
    `vacancy_id` UInt64,
    `premium` UInt8,
    `has_test` UInt8,
    `response_url` String,
    `address_city` String,
    `address_street` String,
    `address_building` String,
    `address_description` String,
    `address_lat` String,
    `address_lng` String,
    `address_raw` String,
    `address_metro_stations` String,
    `alternate_url` String,
    `apply_alternate_url` String,
    `department_id` String,
    `department_name` String,
    `salary_from` Nullable(Float64),
    `salary_to` Nullable(Float64),
    `salary_currency` String,
    `salary_gross` Nullable(UInt8),
    `name` String,
    `insider_interview_id` Nullable(UInt64),
    `insider_interview_url` String,
    `area_url` String,
    `area_id` UInt64,
    `area_name` String,
    `url` String,
    `published_at` DateTime,
    `employer_url` String,
    `employer_alternate_url` String,
    `employer_logo_urls_90` String,
    `employer_logo_urls_240` String,
    `employer_logo_urls_original` String,
    `employer_name` String,
    `employer_id` UInt64,
    `response_letter_required` UInt8,
    `type_id` String,
    `type_name` String,
    `archived` UInt8,
    `schedule_id` Nullable(String)
)
ENGINE = ReplacingMergeTree
ORDER BY vacancy_id

Первый скрипт собирает данные с HeadHunter по API и отправляет их в Clickhouse. Он использует следующие библиотеки:

import requests
from clickhouse_driver import Client
from datetime import datetime
import pandas as pd
import re

Далее загружаем таблицу с запросами и подключаемся к CH:

queries = pd.read_csv('hh_data.csv')
client = Client(host='1.234.567.890', user='default', password='', port='9000', database='headhunter')

Таблица queries хранит список поисковых запросов. Она содержит следующие колонки: тип запроса, уровень вакансии для поиска, направление вакансии и саму поисковую фразу. В строку с запросом можно помещать логические операторы: например, чтобы найти вакансии, в которых должны присутствовать ключевые слова «Python», «data» и «анализ» между ними можно указать логическое «И».

Не всегда вакансии в выдаче соответствуют ожиданиям: случайно в базу могут попасть повара, маркетологи и администраторы магазина. Чтобы этого не произошло, опишем функцию check_name(name) — она будет принимать наименование вакансии и возвращать True в случае, если вакансия не подошла по названию.

def check_name(name):
    bad_names = [r'курьер', r'грузчик', r'врач', r'менеджер по закупу',
           r'менеджер по продажам', r'оператор', r'повар', r'продавец',
          r'директор магазина', r'директор по продажам', r'директор по маркетингу',
          r'кабельщик', r'начальник отдела продаж', r'заместитель', r'администратор магазина', 
          r'категорийный', r'аудитор', r'юрист', r'контент', r'супервайзер', r'стажер-ученик', 
          r'су-шеф', r'маркетолог$', r'региональный', r'ревизор', r'экономист', r'ветеринар', 
          r'торговый', r'клиентский', r'начальник цеха', r'территориальный', r'переводчик', 
          r'маркетолог /', r'маркетолог по']
    for item in bad_names:
        if re.match(item, name):
            return True

Затем объявляем бесконечный цикл — мы собираем данные без перерыва. Идём по DataFrame queries и сразу забираем оттуда тип вакансии, уровень, направление и поисковый запрос в отдельные переменные. Сначала по ключевому слову отправляем один запрос к методу /GET vacancies и получаем количество страниц. После идём от нулевой до последней страницы, отправляем те же запросы и заполняем список vacancies_from_response с полученными в выдаче короткими представлениями всех вакансий. В параметрах указываем 10 вакансий на страницу — больше ограничения HH API получить не позволяют. Так как мы не указали параметр area, API возвращает вакансии по всему миру.

while True:
   for query_type, level, direction, query_string in zip(queries['Тип'], queries['Уровень'], queries['Направление'], queries['Ключевое слово']):
           print(f'ключевое слово: {query_string}')
           url = 'https://api.hh.ru/vacancies'
           par = {'text': query_string, 'per_page':'10', 'page':0}
           r = requests.get(url, params=par).json()
           added_at = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
           pages = r['pages']
           found = r['found']
           vacancies_from_response = []

           for i in range(0, pages + 1):
               par = {'text': query_string, 'per_page':'10', 'page':i}
               r = requests.get(url, params=par).json()
               try:
                   vacancies_from_response.append(r['items'])
               except Exception as E:
                   continue

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

for item in vacancies_from_response:
               for vacancy in item:
                   if client.execute(f"SELECT count(1) FROM vacancies_short WHERE vacancy_id={vacancy['id']} AND query_string='{query_string}'")[0][0] == 0:
                       name = vacancy['name'].replace("'","").replace('"','')
                       if check_name(name):
                           continue

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

vacancy_id = vacancy['id']
                       is_premium = int(vacancy['premium'])
                       has_test = int(vacancy['has_test'])
                       response_url = vacancy['response_url']
                       try:
                           address_city = vacancy['address']['city']
                           address_street = vacancy['address']['street']
                           address_building = vacancy['address']['building']
                           address_description = vacancy['address']['description']
                           address_lat = vacancy['address']['lat']
                           address_lng = vacancy['address']['lng']
                           address_raw = vacancy['address']['raw']
                           address_metro_stations = str(vacancy['address']['metro_stations']).replace("'",'"')
                       except TypeError:
                           address_city = ""
                           address_street = ""
                           address_building = ""
                           address_description = ""
                           address_lat = ""
                           address_lng = ""
                           address_raw = ""
                           address_metro_stations = ""
                       alternate_url = vacancy['alternate_url']
                       apply_alternate_url = vacancy['apply_alternate_url']
                       try:
                           department_id = vacancy['department']['id']
                       except TypeError as E:
                           department_id = ""
                       try:
                           department_name = vacancy['department']['name']
                       except TypeError as E:
                           department_name = ""
                       try:
                           salary_from = vacancy['salary']['from']
                       except TypeError as E:
                           salary_from = "cast(Null as Nullable(UInt64))"
                       try:
                           salary_to = vacancy['salary']['to']
                       except TypeError as E:
                           salary_to = "cast(Null as Nullable(UInt64))"
                       try:
                           salary_currency = vacancy['salary']['currency']
                       except TypeError as E:
                           salary_currency = ""
                       try:
                           salary_gross = int(vacancy['salary']['gross'])
                       except TypeError as E:
                           salary_gross = "cast(Null as Nullable(UInt8))"
                       try:
                           insider_interview_id = vacancy['insider_interview']['id']
                       except TypeError:
                           insider_interview_id = "cast(Null as Nullable(UInt64))"
                       try:
                           insider_interview_url = vacancy['insider_interview']['url']
                       except TypeError:
                           insider_interview_url = ""
                       area_url = vacancy['area']['url']
                       area_id = vacancy['area']['id']
                       area_name = vacancy['area']['name']
                       url = vacancy['url']
                       published_at = vacancy['published_at']
                       published_at = datetime.strptime(published_at,'%Y-%m-%dT%H:%M:%S%z').strftime('%Y-%m-%d %H:%M:%S')
                       try:
                           employer_url = vacancy['employer']['url']
                       except Exception as E:
                           print(E)
                           employer_url = ""
                       try:
                           employer_alternate_url = vacancy['employer']['alternate_url']
                       except Exception as E:
                           print(E)
                           employer_alternate_url = ""
                       try:
                           employer_logo_urls_90 = vacancy['employer']['logo_urls']['90']
                           employer_logo_urls_240 = vacancy['employer']['logo_urls']['240']
                           employer_logo_urls_original = vacancy['employer']['logo_urls']['original']
                       except Exception as E:
                           print(E)
                           employer_logo_urls_90 = ""
                           employer_logo_urls_240 = ""
                           employer_logo_urls_original = ""
                       employer_name = vacancy['employer']['name'].replace("'","").replace('"','')
                       try:
                           employer_id = vacancy['employer']['id']
                       except Exception as E:
                           print(E)
                       response_letter_required = int(vacancy['response_letter_required'])
                       type_id = vacancy['type']['id']
                       type_name = vacancy['type']['name']
                       is_archived = int(vacancy['archived'])

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

try:
    schedule = vacancy['schedule']['id']
except Exception as E:
    print(E)
    schedule = ''
if schedule == 'flyInFlyOut':
    continue

Теперь формируем список из полученных переменных, заменяем в нём None-значения на пустые строки во избежании конфликтов с Clickhouse и вставляем строку в таблицу.

vacancies_short_list = [added_at, query_string, query_type, level, direction, vacancy_id, is_premium, has_test, response_url, address_city, address_street, address_building, address_description, address_lat, address_lng, address_raw, address_metro_stations, alternate_url, apply_alternate_url, department_id, department_name,
salary_from, salary_to, salary_currency, salary_gross, insider_interview_id, insider_interview_url, area_url, area_name, url, published_at, employer_url, employer_logo_urls_90, employer_logo_urls_240,  employer_name, employer_id, response_letter_required, type_id, type_name, is_archived, schedule]
for index, item in enumerate(vacancies_short_list):
    if item is None:
        vacancies_short_list[index] = ""
tuple_to_insert = tuple(vacancies_short_list)
print(tuple_to_insert)
client.execute(f'INSERT INTO vacancies_short VALUES {tuple_to_insert}')

Как подключили Tableau к данным?

Tableau Public не умеет работать с базами данных, поэтому мы написали коннектор Clickhouse к Google Sheets. Он использует библиотеки gspread и oauth2client для авторизации в Google Spreadsheets API и библиотеку schedule для ежедневной работы по графику.

Работа с Google Spreadseets API подробно разобрана в материале «Собираем данные по рекламным кампаниям ВКонтакте»

import schedule
from clickhouse_driver import Client
import gspread
import pandas as pd
from oauth2client.service_account import ServiceAccountCredentials
from datetime import datetime

scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
client = Client(host='54.227.137.142', user='default', password='', port='9000', database='headhunter')
creds = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scope)
gc = gspread.authorize(creds)

Опишем функцию update_sheet() — она будет брать все данные из Clickhouse и вставлять их в таблицу Google Docs.

def update_sheet():
   print('Updating cell at', datetime.now())
   columns = []
   for item in client.execute('describe table headhunter.vacancies_short'):
       columns.append(item[0])
   vacancies = client.execute('SELECT * FROM headhunter.vacancies_short')
   df_vacancies = pd.DataFrame(vacancies, columns=columns)
   df_vacancies.to_csv('vacancies_short.csv', index=False)
   content = open('vacancies_short.csv', 'r').read()
   gc.import_csv('1ZWS2kqraPa4i72hzp0noU02SrYVo0teD7KZ0c3hl-UI', content.encode('utf-8'))

Чтобы скрипт запускался в 16:00 по МСК каждый день используем библиотеку schedule:

schedule.every().day.at("13:00").do(update_sheet)
while True:
   schedule.run_pending()

А что в результате?

Рома построил на полученных данных дашборд.

И в youtube-ролике рассказывает о том, как эффективно использовать дашборд

Инсайты, которые можно извлечь из дашборда

1. Аналитики с навыком бизнес-аналитики востребованы на рынке больше всего: по такому запросу нашлось больше всего вакансий. Тем не менее, средняя зарплата выше у продуктовых аналитиков и аналитиков BI.
2. В Москве средние зарплаты выше на 10-30 тысяч рублей, чем в Санкт-Петербурге и на 30-40 тысячи рублей, чем в регионах. Там же работы нашлось больше всего в России.
3. Самые высокооплачиваемые должности: руководитель отдела аналитики (в среднем, 110 тыс. руб. в месяц), инженер баз данных (138 тыс. руб. в месяц) и директор по машинному обучению (250 тыс. руб. в месяц).
4. Самые полезные навыки на рынке — владение Python c библиотеками pandas и numpy, Tableau, Power BI, Etl и Spark. Вакансий с такими требованиями больше и зарплаты в них указаны выше прочих. Для Python-программистов знание matplotlib ценится на рынке выше, чем владение plotly.

Полный код проекта доступен на GitHub

Перевод статьи «Dashboards are Dead»

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

Hello Dashboard, my old friend

На старте карьеры я работала в крупной технологической компании. Компания только-только приобрела свой первый инструмент для создания дашбордов, и наша команда отвечала за захватывающий переход от устаревших spreadsheets и отчетов SSRS к новым ярким дашбордам.

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

Не совсем. Вскоре мы обнаружили, что дашборды приносят с собой ряд новых проблем:

1. Как? У вас ещё нет дашборда?! Неожиданно повсюду появились дашборды. Инженеру нужны данные для специального анализа? Вот дашборд. У вице-президента будет презентация на следующей неделе и ему нужны диаграммы? Она получает дашборд. А что происходит дальше? О нём просто забывают. Такой шаблонный подход истощал время, ресурсы и мотивацию нашей команды. Это уникальное деморализующее чувство — наблюдать, как ещё один из ваших дашбордов забросили быстрее, чем профиль MySpace в 2008 году.
2. Смерть от 1000 фильтров. После того, как новый дашборд заработал, нас сразу же заваливали запросами на новые представления, фильтры, поля, страницы (напомните мне рассказать вам о том, как я увидела 67-страничный дашборд). Было ясно: дашборды не отвечали на все вопросы, что было либо неудачей на этапе разработки, либо неспособностью инструментов дать ответы, в которых нуждались люди. Что ещё хуже, мы выяснили, что люди использовали все эти фильтры, чтобы экспортировать данные в Excel и уже там работать с ними 🤦‍♀️
3. Не мой дашборд. Постепенно шумиха вокруг дашбордов начала сходить на нет, люди начали пренебрегать ими и откровенно игнорировать их. Многие видели в них угрозу для своей работы, и если они встречали неожиданные цифры, то списывали всё на «плохие данные». У нас на работе были серьёзные проблемы с доверием между людьми, и дашборды только усугубляли положение. В конце концов, мы ведь не могли отправлять другим наши SQL-запросы для получения данных: люди бы просто не смогли не только прочитать их, но даже понять ту сложную схему, по которой они работают. И тем более мы не могли отправлять другим командам необработанные данные. Итак, у нас была просто огромная, наболевшая, серьезная проблема с доверием.

Реальный пример: что это за странная красная точка на карте?

Для примера давайте рассмотрим дашборд, который стал широко популярен во время пандемии — панель мониторинга коронавируса университета Джона Хопкинса.

Дашборд привлекателен визуально. Красный и чёрный вызывают чувство строгости и важности с первого взгляда. По мере того, как взгляд останавливается на странице, мы сталкиваемся с числами, точками разного размера и графиками, которые почти всегда направлены вправо-вверх. У нас осталось ощущение, что всё плохо, и, кажется, становится ещё хуже. Этот дашборд был создан с целью получения данных доступным и интересным способом. Возможно, он даже был разработан, чтобы ответить на несколько ключевых вопросов: «Сколько новых случаев было сегодня в моей стране? А в моём регионе?». Безусловно, это намного лучше, чем если бы они просто разместили таблицу или ссылку для скачивания.

Но кроме этих поверхностных выводов мы не можем сделать с данными ничего. Если бы мы хотели использовать данные для определенной цели, у нас не было бы необходимого контекста вокруг этих цифр, чтобы сделать их полезными и доверять как своим собственным. Например, «Когда в моей стране или в моём регионе начали действовать меры социального дистанцирования? Насколько доступны тесты в моей стране?». И даже если бы нам каким-то образом удалось получить этот контекст, чтобы доверять этим числам самому дашборду не хватает гибкости для проведения самостоятельного анализа.

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

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

Данные в портретном режиме

Блокноты с данными, такие как Jupyter, стали очень популярными за последние несколько лет в области Data Science. Их технологическая направленность оказалась лучше традиционных скриптовых инструментов для Data Analysis и Data Science. Это не только полезно для аналитика, выполняющего работу, но также помогает начальнику, коллеге, другу, который вынужден этим пользоваться.

По сути, блокноты обеспечивают:

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

Я, конечно, не первая, кто хочет применить мощь и гибкость блокнотов в области анализа данных или бизнес-аналитики, и мы поговорили с рядом компаний, которые используют их вместо дашбордов. Некоторые используют только Jupyter для своих отчётов, другие вырезают и вставляют диаграммы оттуда в текстовый редактор для аналогичного эффекта. Это не совершенные решения, но это признак того, что компании готовы отказаться от тщательно продуманных дашбордов, чтобы попробовать преимущества блокнотов.

Нам просто нужен способ вынести эту идею за пределы Data Science и сделать блокнот таким же доступным, как и дашборды.

Блокноты в массы

В Count мы настолько верим в преимущества блокнотов, что создали платформу для анализа данных на их основе. Народ, больше никаких дашбордов!

Чтобы использовать их за пределами Data Science, нам пришлось создать собственную версию, но фундаментальные принципы всё ещё применимы с некоторыми дополнительными преимуществами...

Создан для любого уровня опыта

1. Нет необходимости учить всех в вашей команде Python или SQL, поскольку запросы можно создавать по принципу drag-and-drop, используя «составной запрос» SQL или написания запроса с нуля.
2. Стройте графики и диаграммы одним щелчком мыши, без сложных пакетов визуализации или программного обеспечения
3. Автоматическое объединение таблиц и результатов запроса, нет необходимости писать сложные объединения или пытаться объяснить схему

Collaboration-enabled

1. Делитесь блокнотами с товарищем по команде, всей командой или тем, у кого есть ссылка
2. Добавляйте комментарии и выноски, чтобы сделать документ действительно общим

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

1. Аналитики используют блокноты вместо SQL-скриптов для создания нескольких базовых таблиц, которые используют другие команды. Эти блокноты доступны для просмотра всем, что решает проблему доверия в команде
2. Команда по работе с данными создаёт несколько базовых отчётов. Эти отчёты полны комментариев, которые помогут читателю лучше понять, как интерпретировать числа и какие соображения следует принять
3. Затем пользователи делают fork этих дата-блокнотов или создают свои собственные. Они делятся этими блокнотами с Data Team, чтобы они могли помочь им, а затем и с другими подразделениями компании

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

UPD 29-09-2021: Мы обновили клиент. Теперь проходить аутентификацию можно по номеру телефона и подтверждению по SMS. Репозиторий на GitHub

Ранее в блоге мы рассказывали, как благодаря открытому API можно собирать данные от ФНС по нашим чекам из магазинов, обращаясь к приложению налоговой. С прошлой недели способ стал нерабочим: ФНС обновили методы приложения. Сегодня напишем свой клиент для nalog.ru, который проходит авторизацию и отправляет чеки на проверку.

Авторизация

Прежде чем начать пользоваться приложением, наш профиль необходимо авторизовать. В отличии от предыдущей версии, текущая требует прохождение капчи для авторизации по мобильному телефону — такой способ нам не подходит. Проще всего будет входить в профиль по ИНН и паролю от личного кабинета налогоплательщика. Для хранения этих данных создадим файл credentials.env. Переменную CLIENT_SECRET зададим согласно коду: она отвечает за авторизацию приложения. А ИНН и пароль подставляем свои.

INN = 
PASSWORD = 
CLIENT_SECRET=IyvrAbKt9h/8p6a7QPh8gpkXYQ4=

Теперь создадим файл nalog_python.py, в котором будет описан клиент. Библиотека dotenv используется для загрузки нашего логина и пароля из .env файла.

import os
import json
import requests
from dotenv import load_dotenv

Опишем класс NalogRuPython, и начнём с глобальных переменных класса. Здесь перечисляем headers, необходимые для отправки запроса.

class NalogRuPython:
    HOST = 'irkkt-mobile.nalog.ru:8888'
    DEVICE_OS = 'iOS'
    CLIENT_VERSION = '2.9.0'
    DEVICE_ID = '7C82010F-16CC-446B-8F66-FC4080C66521'
    ACCEPT = '*/*'
    USER_AGENT = 'billchecker/2.9.0 (iPhone; iOS 13.6; Scale/2.00)'
    ACCEPT_LANGUAGE = 'ru-RU;q=1, en-US;q=0.9'

В конструкторе класса прочитаем данные из нашего окружения методом load_dotenv и вызовем метод set_session_id для получения __session_id, который сейчас опишем. Идентификатор сессии необходим для отправки прочих запросов к серверу налоговой, поэтому его получаем первым делом.

def __init__(self):
        load_dotenv()
        self.__session_id = None
        self.set_session_id()

Метод set_session_id проводит авторизацию пользователя по ИНН и паролю от личного кабинета налогоплательщика и ничего не возвращает, только задаёт значение переменной __session_id. Отправляем по указанному в глобальных переменных HOST запрос с нашими данными от аккаунта и получаем идентификатор сессии в ответе.

def set_session_id(self) -> None:
        if os.getenv('CLIENT_SECRET') is None:
            raise ValueError('OS environments not content "CLIENT_SECRET"')
        if os.getenv('INN') is None:
            raise ValueError('OS environments not content "INN"')
        if os.getenv('PASSWORD') is None:
            raise ValueError('OS environments not content "PASSWORD"')

        url = f'https://{self.HOST}/v2/mobile/users/lkfl/auth'
        payload = {
            'inn': os.getenv('INN'),
            'client_secret': os.getenv('CLIENT_SECRET'),
            'password': os.getenv('PASSWORD')
        }
        headers = {
            'Host': self.HOST,
            'Accept': self.ACCEPT,
            'Device-OS': self.DEVICE_OS,
            'Device-Id': self.DEVICE_ID,
            'clientVersion': self.CLIENT_VERSION,
            'Accept-Language': self.ACCEPT_LANGUAGE,
            'User-Agent': self.USER_AGENT,
        }

        resp = requests.post(url, json=payload, headers=headers)
        self.__session_id = resp.json()['sessionId']

Получение идентификатора чека

Следующий шаг — получение ticket_id. Прежде чем отправить сам чек, необходимо получить его идентификатор для проверки. Опишем метод _get_ticket_id, который принимает строку с расшифрованным QR-кодом чека, отправляет соответствующий запрос на сервер и возвращает идентификатор для этой строки. В headers помимо указания глобальных переменных появился также __session_id, который требует метод /v2/ticket.

def _get_ticket_id(self, qr: str) -> str:
        url = f'https://{self.HOST}/v2/ticket'
        payload = {'qr': qr}
        headers = {
            'Host': self.HOST,
            'Accept': self.ACCEPT,
            'Device-OS': self.DEVICE_OS,
            'Device-Id': self.DEVICE_ID,
            'clientVersion': self.CLIENT_VERSION,
            'Accept-Language': self.ACCEPT_LANGUAGE,
            'sessionId': self.__session_id,
            'User-Agent': self.USER_AGENT,
        }
        resp = requests.post(url, json=payload, headers=headers)
        return resp.json()["id"]

Расшифровка чека

Последний шаг — проверка чека. Формируем по ticket_id запрос к серверу и получаем подробную расшифровку чека в ответе. На этом клиент полностью описан и готов к работе.

def get_ticket(self, qr: str) -> dict:
        ticket_id = self._get_ticket_id(qr)
        url = f'https://{self.HOST}/v2/tickets/{ticket_id}'
        headers = {
            'Host': self.HOST,
            'sessionId': self.__session_id,
            'Device-OS': self.DEVICE_OS,
            'clientVersion': self.CLIENT_VERSION,
            'Device-Id': self.DEVICE_ID,
            'Accept': self.ACCEPT,
            'User-Agent': self.USER_AGENT,
            'Accept-Language': self.ACCEPT_LANGUAGE,
        }
        resp = requests.get(url, headers=headers)
        return resp.json()

Наконец, для удобства опишем пример работы клиента. Создадим экземпляр класса NalogRuPython, зададим для примера строку QR-кода и получим расшифрованный ticket, который затем напечатаем на экране.

if __name__ == '__main__':
    client = NalogRuPython()
    qr_code = "t=20200727T1117&s=4850.00&fn=9287440300634471&i=13571&fp=3730902192&n=1"
    ticket = client.get_ticket(qr_code)
    print(json.dumps(ticket, indent=4))

Клиент можно использовать и в своих скриптах: для этого нужно импортировать класс, создать экземпляр и, как и в примере, вызвать метод get_ticket.

from nalog_python import NalogRuPython

client = NalogRuPython()
qr_code = "t=20200727T1117&s=4850.00&fn=9287440300634471&i=13571&fp=3730902192&n=1"
ticket = client.get_ticket(qr_code)

Полный код проекта на GitHub

В прошлых материалах мы познакомились с фреймворком Plotly Dash, научились строить scatter plots и визуализировать данные на карте. Сегодня мы попробуем объединить имеющиеся части в одном веб-приложении и расскажем как можно создать полноценный дашборд используя сетчатую структуру Bootstrap.
В этом нам поможет dash-bootstrap-components, эта библиотека позволяет строить дашборды по принципу “plug-and-play”, добавлять любые компоненты Bootstrap и стилизовать их используя грид-дизайн.

Подготовка макета

Перед созданием любого приложения, не только дашборда нам просто необходим план, черновой макет, следуя которому, мы бы видели общую картину и могли быстро внести корректировки в структуру. Для создания макета дашборда мы использовали приложение draw.io, этот инструмент позволяет быстро создавать диаграммы, графики, блок-схемы, формы и т. д. Наш дашборд будет построен по следующему макету:

Как и сам дашборд, шапка будет оформлена в главных цветах Untappd — белом и золотом. Ниже расположится раздел с пивоварнями, состоящий из scatter plot и панели с настройками. А в самом низу будет карта, показывающая средний рейтинг напитка по регионам России.

Итак, приступим, для начала создадим файл application.py, в нем будут храниться все фронтенд элементы дашборда, и папку assets в той же директории, структура должна быть такой:

- application.py
- assets/
    |-- typography.css
    |-- header.css
    |-- custom-script.js
    |-- image.png

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

import dash
import dash_bootstrap_components as dbc
import dash_html_components as html
import dash_core_components as dcc
import pandas as pd
from get_ratio_scatter_plot import get_plot
from get_russian_map import get_map
from clickhouse_driver import Client
from dash.dependencies import Input, Output

standard_BS = dbc.themes.BOOTSTRAP
app = dash.Dash(__name__, external_stylesheets=[standard_BS])

Главные аргументы для app:
__name__ — для активации cтатических элементов из папки assets (картинки, CSS и JS файлы)
external_stylesheets — внешний CSS для стилизации дашборда, здесь мы используем стандартный BootstrapCDN, однако вы можете создать свой или воспользоваться одним из уже готовых стилей.

Включаем ещё несколько параметров для работы с локальными файлами и подключаемся к ClickHouse:

app.scripts.config.serve_locally = True
app.css.config.serve_locally = True

client = Client(host='',
                user='default',
                password='',
                port='9000',
                database='default')

Добавим палитру цветов:

colors = ['#ffcc00', 
          '#f5f2e8', 
          '#f8f3e3',
          '#ffffff', 
          ]

Верстка макета

Все элементы дашборда будут помещены в Bootstrap Контейнер, который находится в блоке <div>:

- app 
    |-- div
     |-- container
      |-- logo&header
     |-- container
      |-- div
       |-- controls&scatter
       |-- map

app.layout = html.Div(
                    [
                        dbc.Container(

                                         < шапка >
                         
                        dbc.Container(       
                            html.Div(
                                [
                        
                                    < основной код >
                        
                                ],
                            ),
                            fluid=False, style={'max-width': '1300px'},
                        ),
                    ],
                    style={'background-color': colors[1], 'font-family': 'Proxima Nova Bold'},
                )

Здесь мы сразу задаем фиксированную ширину контейнера, цвет фона и стиль шрифта на странице, который берется из typography.css в папке assets. Стоит подробней остановится на первом элементе блока div, это и есть заголовок страницы, он включает в себя логотип Untappd:

logo = html.Img(src=app.get_asset_url('logo.png'),
                        style={'width': "128px", 'height': "128px",
                        }, className='inline-image')

и главный заголовок:

header = html.H3("Статистика российских пивоварен в Untappd", style={'text-transform': "uppercase"})

Чтобы расположить эти два элемента на одной строке мы воспользовались Bootstrap Формами:

logo_and_header = dbc.FormGroup(
        [
            logo,
            html.Div(
                [
                    header
                ],
                className="p-5"
            )
        ],
        className='form-row',
)

В блоке html.Div параметр ’p-5’ позволяет добавить отступы и вертикально выровнять заголовок, а ’form-row’ поставить logo и header в один ряд. На данном этапе шапка дашборда вылгядит следующим образом:

Нам осталось выровнять их по центру и добавить красок. Для этого создаем отдельный контейнер, который будет состоять из одного ряда. В параметре className указываем ’d-flex justify-content-center’, чтобы выровнять элементы контейнера по центру.

dbc.Container(
                    dbc.Row(
                        [
                            dbc.Col(
                                html.Div(
                                    logo_and_header,
                                ),
                            ),
                        ],
                        style={'max-height': '128px',
                               'color': 'white',
                       }

                    ),
                    className='d-flex justify-content-center',
                    style={'max-width': '100%',
                           'background-color': colors[0]},
                ),

На данном этапе шапка дашборда готова:

Далее в следующий Bootstrap Контейнер добавим первый подзаголовок:

dbc.Container(
                    html.Div(
                        [
                            html.Br(),
                            html.H5("Пивоварни", style={'text-align':'center', 'text-transform': 'uppercase'}),
                            html.Hr(), # разделительная линия

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

slider_day_values = [1, 10, 20, 30, 40, 50, 60, 70, 80, 90, 100]
slider_top_breweries_values = [5, 25, 50, 75, 100, 125, 150, 175, 200]

controls = dbc.Card(
    [
       dbc.CardBody(
           [
               dbc.FormGroup(
                    [
                        dbc.Label("Временной период", style={'text-align': 'center', 'font-size': '100%', 'text-transform': 'uppercase'}),
                        dcc.Slider(
                            id='slider-day',
                            min=1,
                            max=100,
                            step=10,
                            value=100,
                            marks={i: i for i in slider_day_values}
                        ),
                    ], style={'text-align': 'center'}
               ),
               dbc.FormGroup(
                    [
                        dbc.Label("Количество пивоварен", style={'text-align': 'center', 'font-size': '100%', 'text-transform': 'uppercase'}),
                        dcc.Slider(
                            id='slider-top-breweries',
                            min=5,
                            max=200,
                            step=5,
                            value=200,
                            marks={i: i for i in slider_top_breweries_values}
                        ),
                    ], style={'text-align': 'center'}
               ),
           ],
       )
    ],
    style={'height': '32.7rem', 'background-color': colors[3]}
)

Панель включает два слайдера для управления scatter plot, они располагаются друг под другом в Bootstrap формах. Мы добавили слайдеры в один блок — dbc.CardBody. остальные эдементы будут добавлены по такому же принципу, это позволяет поставить одинаковые отступы со всех сторон . По умолчанию слайдеры оформлены в голубом цвете, для того чтобы изменить их стиль, воспользуйтесь файлом sliders.css, находящемся в папке assets.
Добавляем панель управления и scatter plot следующим блоком:

dbc.Row(
                [
                    dbc.Col(controls, width={"size": 4,
                                     "order": 'first',
                                             "offset": 0},
                     ),
                     dbc.Col(dbc.Card(
                                [
                                    dbc.CardBody(
                                        [
                                            html.H6("Отношение количества отзывов к средней оценке пивоварни",
                                                    className="card-title",
                                                    style={'text-transform': 'uppercase'}), 
                                            dcc.Graph(id='ratio-scatter-plot'),
                                        ],
                                    ),
                                ],
                                style={'background-color': colors[2], 'text-align':'center'}
                             ),
                     md=8),
                ],
                align="start",
                justify='center',
            ),
html.Br(),

И в конце страницы расположим карту:

html.H5("Заведения и регионы", style={'text-align':'center', 'text-transform': 'uppercase',}),
                            html.Hr(), #разделительная линия
                            dbc.Row(
                                [
                                    dbc.Col(
                                        dbc.Card(
                                            [
                                                dbc.CardBody(
                                                    [
                                                        html.H6("Средний рейтинг пива по регионам",
                                                                className="card-title",
                                                                style={'text-transform': 'uppercase'},
                                                        ),  
                                                        dcc.Graph(figure=get_map())
                                                    ],
                                                ),
                                            ],
                                        style={'background-color': colors[2], 'text-align': 'center'}
                                        ),
                                md=12),
                                ]
                            ),
                            html.Br(),

Callback-функции в Dash

Callback-функции позволяют сделать элементы дашборда интерактивными, если меняется входной элемент (Input), то и выход (Output) тоже изменится.

@app.callback(
    Output('ratio-scatter-plot', 'figure'),
    [Input('slider-day', 'value'),
     Input('slider-top-breweries', 'value'),
     ]
)
def get_scatter_plots(n_days=100, top_n=200):
    if n_days == 100 and top_n == 200:
        df = pd.read_csv('data/ratio_scatter_plot.csv')
        return get_plot(n_days, top_n, df)
    else:
        return get_plot(n_days, top_n)

Входные/ выходные (Input/Output) значения это, проще говоря, параметр value элемента с определенным id. Например, входное значение верхнего слайдера с id=’slider-day’, показывающего временной период по умолчанию 100. При изменении этого значения функция, обернутая в декоратор будет вызвана автоматически и output на графике обновится. Больше примеров представлено сайте plotly.
Важный момент, чтобы scatter plot при загрузке страницы отображал данные нам нужно сперва считать их из сохраненного датафрейма в папке data (указать начальное состояние), иначе scatter plot будет пустым. В дальнейшем при изменении параметров слайдера данные будут загружаться из ClickHouse таблиц.

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

application = app.server

if __name__ == '__main__':
    application.run(debug=True, port=8000)

Теперь осталось только загрузить его на AWS с помощью BeansTalk и наш дашборд на Bootstrap готов:

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

Полный код проекта на GitHub

Часто для визуализации данных подходит карта: например, когда нужно показать, как статистика ведёт себя в определённых городах, областях, регионах. В таких случаях каждый регион или другая административная единица кодируется: ее границы преобразуют в полигоны и мультиполигоны с координатами широты и долготы относительно карты мира. Для Америки и Европы не составит труда найти встроенное в библиотеку Plotly решение, но в случае с картой России такого реализованного решения сходу найти не удалось. Сегодня мы разметим готовый geojson файл с административными границами регионов России, напишем парсер последних данных по коронавирусу и визуализируем статистику на карте при помощи библиотеки Plotly.

from urllib.request import urlopen
import json
import requests
import pandas as pd
from selenium import webdriver
from bs4 import BeautifulSoup as bs
import plotly.graph_objects as go

Правим geojson

Скачаем открытый geojson с границами российских регионов, найденный по одной из первых ссылок в Google по запросу «russia geojson». В нём уже есть кое-какие данные: например, наименования регионов. Но этот geojson-файл пока ещё не подходит под требуемый формат Plotly: в нём не размечены идентификаторы регионов.

with urlopen('https://raw.githubusercontent.com/codeforamerica/click_that_hood/master/public/data/russia.geojson') as response:
    counties = json.load(response)

Кроме разметки идентификаторов есть различия в наименовании регионов. Например, на сайте стопкоронавирус.рф, откуда мы будем брать свежие данные о заболевших, республика Башкортостан занесена как «Республика Башкортостан», а в нашем geojson-файле — просто «Башкортостан». Все эти различия необходимо устранить во избежание конфликтов. Кроме того, все первые буквы в названиях регионов должны начинаться с верхнего регистра.

regions_republic_1 = ['Бурятия', 'Тыва', 'Адыгея', 'Татарстан', 'Марий Эл',
                      'Чувашия', 'Северная Осетия – Алания', 'Алтай',
                      'Дагестан', 'Ингушетия', 'Башкортостан']
regions_republic_2 = ['Удмуртская республика', 'Кабардино-Балкарская республика',
                      'Карачаево-Черкесская республика', 'Чеченская республика']
for k in range(len(counties['features'])):
    counties['features'][k]['id'] = k
    if counties['features'][k]['properties']['name'] in regions_republic_1:
        counties['features'][k]['properties']['name'] = 'Республика ' + counties['features'][k]['properties']['name']
    elif counties['features'][k]['properties']['name'] == 'Ханты-Мансийский автономный округ - Югра':
        counties['features'][k]['properties']['name'] = 'Ханты-Мансийский АО'
    elif counties['features'][k]['properties']['name'] in regions_republic_2:
        counties['features'][k]['properties']['name'] = counties['features'][k]['properties']['name'].title()

Из получившегося geojson-файла сформируем DataFrame с регионами России: возьмём идентификаторы и наименования.

region_id_list = []
regions_list = []
for k in range(len(counties['features'])):
    region_id_list.append(counties['features'][k]['id'])
    regions_list.append(counties['features'][k]['properties']['name'])
df_regions = pd.DataFrame()
df_regions['region_id'] = region_id_list
df_regions['region_name'] = regions_list

Если сделаем всё правильно, получим такой DataFrame:

Собираем данные

Будем парсить эту таблицу:

Воспользуемся библиотекой Selenium. Перейдём на сайт и получим всю страницу, а затем преобразуем её в Soup для парсинга.

driver = webdriver.Chrome()
driver.get('https://стопкоронавирус.рф/information/')
source_data = driver.page_source
soup = bs(source_data, 'lxml')

На сайте наименования регионов находятся под тегом <th>, а свежие данные по регионам под тегом <td>. Для начала получим данные.

divs_data = soup.find_all('td')

Данные в divs_data выглядят следующим образом:

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

count = 1
for td in divs_data:
    if count == 1:
        sick_list.append(int(td.text))
    elif count == 2:
        new_list.append(int(td.text))
    elif count == 3:
        cases_list.append(int(td.text))
    elif count == 4:
        healed_list.append(int(td.text))
    elif count == 5:
        died_list.append(int(td.text))
        count = 0
    count += 1

Следующим шагом соберём названия регионов из таблицы — они лежат под классом col-region. Из названий нужно убрать лишние двойные пробелы и символы переноса строки.

divs_region_names = soup.find_all('th', {'class':'col-region'})
region_names_list = []
for i in range(1, len(divs_region_names)):
    region_name = divs_region_names[i].text
    region_name = region_name.replace('\n', '').replace('  ', '')
    region_names_list.append(region_name)

Соберём DataFrame:

df = pd.DataFrame()
df['region_name'] = region_names_list
df['sick'] = sick_list
df['new'] = new_list
df['cases'] = cases_list
df['healed'] = healed_list
df['died'] = died_list

И посмотрим на Челябинскую область под десятым индексом — в конце наименования остался пробел! Этот пробел в конце строки может причинить много бед, ведь тогда название не будет соответствовать названию региона в geojson-файле. Уберём его — благо, все остальные наименования на сайте в порядке.

df.loc[10, 'region_name'] = df[df.region_name == 'Челябинская область '].region_name.item().strip(' ')

Наконец, сделаем merge двух DataFrame по колонке названия региона, чтобы в получившейся таблице с данными по коронавирусу появилась колонка с идентификатором региона, необходимая для генерации карты.

df = df.merge(df_regions, on='region_name')

Визуализация данных на карте Plotly

Создадим новую фигуру — она будет являться объектом Choroplethmapbox. В параметр geojson передаём переменную counties с geojson-файлом, в параметр locations вставляем идентификаторы регионов. Параметр z — значения, которые мы хотим визуализировать. Для примера возьмём количество новых случаев в каждом регионе — они лежат в колонке new таблицы. В text передаём названия регионов. Другой параметр — colorscale — нужен для цветового сопровождения данных. Он принимает списки со значениями от 0 до 1, которые являются позициями цветов в градиенте. Чем меньше заболевших, тем зеленее будет регион. С увеличением числа заболевших цвет переходит от желтого к красному. Параметр hovertemplate — шаблон панели, появляющейся при наведении на регион. С тултипом связан ещё один аргумент — customdata. Он принимает объединенные вдоль оси объекты, которые затем можно использовать в hovertemplate для отображения новых данных.

fig = go.Figure(go.Choroplethmapbox(geojson=counties,
                           locations=df['region_id'],
                           z=df['new'],
                           text=df['region_name'],
                           colorscale=[[0, 'rgb(34, 150, 79)'],
                                       [0.2, 'rgb(249, 247, 174)'],
                                       [0.8, 'rgb(253, 172, 99)'],
                                       [1, 'rgb(212, 50, 44)']],
                           colorbar_thickness=20,
                           customdata=np.stack([df['cases'], df['died'], df['sick'], df['healed']], axis=-1),
                           hovertemplate='<b>%{text}</b>'+ '<br>' +
                                         'Новых случаев: %{z}' + '<br>' +
                                         'Активных: %{customdata[0]}' + '<br>' +
                                         'Умерло: %{customdata[1]}' + '<br>' +
                                         'Всего случаев: %{customdata[2]}' + '<br>' +
                                         'Выздоровело: %{customdata[3]}' +
                                         '<extra></extra>',
                           hoverinfo='text, z'))

Теперь зададим стиль карты — возьмём готовую carto-positron, нейтральный и минималистичный шаблон, который не отвлекает от основных данных. Аргумент mapbox_zoom отвечает за приближение карты, а mapbox_center принимает координаты начального центра карты. Зададим marker_line_width равный нулю, чтобы убрать границы между регионами. После зададим всем отступам в margin значение 0, чтобы карта была визуально шире. Сразу после выведем фигуру методом show().

fig.update_layout(mapbox_style="carto-positron",
                  mapbox_zoom=1, mapbox_center = {"lat": 66, "lon": 94})
fig.update_traces(marker_line_width=0)
fig.update_layout(margin={"r":0,"t":0,"l":0,"b":0})
fig.show()

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

Полный код проекта на GitHub

Если под рукой имеется машина на Amazon Web Services и стоит задача развернуть веб-приложение, можно воспользоваться сервисом Elastic Beanstalk от AWS: он позволяет развертывать приложения под другими сервисами от Amazon, включая EC2.

Готовим приложение

В материале «Делаем дашборд с параметром на Python» мы создали проект с двумя файлами: application.py — скрипт с генерацией локального дашборда и get_plots.py — скрипт, возвращающий scatter plot с пивоварнями Untappd из материала «Строим scatter plot по пивоварням Untappd». Немного подкорректируем файл application.py: чтобы приложение запускалось на Elastic Beanstalk, app.server в конце файла присвоим переменной application. Должно получиться вот так:

application = app.server

if __name__ == '__main__':
   application.run(debug=True, port=8080)

Перед тем, как развернуть приложение, нужно собрать его в архив. В архиве должны присутствовать все необходимые файлы, включая requirements.txt — перечень зависимостей приложения. В нём перечислены пакеты и версии, необходимые для запуска приложения. Чтобы его создать, достаточно в директории с проектом и окружением ввести команду pip freeze и отправить вывод в файл:

pip freeze > requirements.txt

Теперь соберём архив. В unix для архивации и сжатия предусмотрена встроенная утилита zip.

zip deploy_v0 application.py get_plots.py requirements.txt

Создаём приложение и окружение

Переходим на Elastic Beanstalk в раздел «Applications». Жмём на «Create a new application».

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

Сразу после нам покажут список окружений для приложения: изначально он пустой, поэтому нажимаем на «Create a new environment».

Так как мы работаем с веб-приложением, выбираем окружение веб-сервера:

После предлагают ввести информацию о приложении, включая домен. Можно ввести свой домен, если таковой будет свободен:

Следом выбираем платформу веб-приложения. Наше написано на Python.

Теперь загружаем само приложение: так как код мы уже написали, выбираем «Upload your code» и прикрепляем файл с архивом. После жмём «Create environment».

Следом откроется окно с логами создания окружения. Пару минут придётся подождать.

Если все сделали правильно, увидим экран с галочкой и подписью «OK»: это означает, что наше приложение успешно загружено и доступно. Если захотим загрузить новую версию, достаточно пересобрать архив с файлами и загрузить его по кнопке «Upload and deploy».

По ссылке, представленной выше можем пройти на сайт, где лежит дашборд. При помощи тега <iframe> этот дашборд можно также встроить на другой сайт:

<iframe id="igraph" scrolling="no" style="border:none;"seamless="seamless" src="http://dashboardleftjoin-env.eba-qxzgfj64.us-east-2.elasticbeanstalk.com" height="1100" width="800"></iframe>

В итоге получим такой дашборд на сайте:

Полный код проекта на GitHub

В прошлом материале мы подготовили scatter plot, используя библиотеку plotly: он отображал отношение количества отзывов пивоварни к её рейтингу в социальной сети Untappd. Ещё мы добавили каждому маркеру характеристики: дату регистрации пивоварни и количество сортов пива в её ассортименте. Сегодня воспользуемся другим инструментом plotly — Dash, и построим дашборд с двумя параметрами для этого графика. Создадим новый файл — application.py, который будет импортировать функцию get_scatter_plot(n_days, top_n) из последнего материала.

import dash
import dash_core_components as dcc
import dash_html_components as html
from get_plots import get_scatter_plot

После импорта библиотек загружаем css-стили и инициируем веб-приложение:

external_stylesheets = ['https://codepen.io/chriddyp/pen/bWLwgP.css']
app = dash.Dash(__name__, external_stylesheets=external_stylesheets)

Опишем структуру дашборда:

app.layout = html.Div(children=[
       html.Div([
           dcc.Graph(id='fig1'),
       ]) ,
       html.Div([
           html.H6('Временной период, дней'),
           dcc.Slider(
               id='slider-day1',
               min=0,
               max=100,
               step=1,
               value=30,
               marks={i: str(i) for i in range(0, 100, 10)}
           ),
           html.H6('Количество пивоварен в топе'),
           dcc.Slider(
               id='slider-top1',
               min=0,
               max=500,
               step=50,
               value=500,
               marks={i: str(i) for i in range(0, 500, 50)})
       ])
])

Мы обозначили на панели график и два слайдера. У каждого слайдера есть свой идентификатор и параметры: минимальное значение, максимальное, шаг изменения, начальное значение. Так как данные из слайдеров будут передаваться в график, опишем callback для них: первый аргумент — Output — график, который будет изменяться, это наш вывод. Следующие два — Input — параметры, от которых график зависит.

@app.callback(
   dash.dependencies.Output('fig1', 'figure'),
   [dash.dependencies.Input('slider-day1', 'value'),
    dash.dependencies.Input('slider-top1', 'value')])
def output_fig(n_days, top_n):
    fig = get_scatter_plot(n_days, top_n)
    return fig

В конце файла добавим вызов локального сервера:

if __name__ == '__main__':
   app.run_server(debug=True)

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

Сегодня поработаем с открытыми данными из ВКонтакте и получим семантическую оценку на популярное и актуальное событие — поправки к Конституции Российской Федерации.

Обзор методов API

Воспользуемся методом newsfeed.search: он позволяет получить до тысячи последних постов из новостной ленты по ключевому слову. В результате приходит много полей: среди них идентификаторы записи и пользователя или сообщества, текст поста, количество лайков, комментарии, приложения, геопозиция и прочее. Нас интересуют только идентификаторы и текст.
Для аналитики пригодится расширенная информация об авторе поста: его город, пол и возраст можно получить методом users.get, причём в запросе будем отправлять сразу до тысячи пользователей.

Создаём таблицы в Clickhouse

Данные нужно будет где-то хранить, в качестве СУБД подойдёт Clickhouse. Создадим две таблицы: для постов и для пользователей. В первой будем хранить идентификаторы и текст поста, во второй — данные о пользователе: его id, пол, возраст и город. Движок ReplacingMergeTree() будет удалять дубликаты.

Мы уже писали о том, как установить Clickhouse на бесплатную машину AWS, создавать в нём внешние словари и материализованные представления

CREATE TABLE vk_posts(
   post_id UInt64,
   post_date DateTime,
   owner_id UInt64,
   from_id UInt64,
   text String
) ENGINE ReplacingMergeTree()
ORDER BY post_date

CREATE TABLE vk_users(
   user_id UInt64,
   user_sex Nullable(UInt8),
   user_city String,
   user_age Nullable(UInt16)
) ENGINE ReplacingMergeTree()
ORDER BY user_id

Сбор постов через API ВКонтакте

Перейдём к написанию скрипта. Импортируем библиотеки и задаём несколько константных значений:

В материале «Собираем данные по рекламным кампаниям ВКонтакте» подробно описан процесс получения токена пользователя для VK API

from clickhouse_driver import Client
from datetime import datetime
import requests
import pandas as pd
import time

token = 'your_token'
version = 5.103
client = Client(host='ec1-23-456-789-1011.us-east-2.compute.amazonaws.com', user='default', password='', port='9000', database='default')      
data_list = []
start_from = 0
query_string = 'конституция'

Опишем функцию get_and_insert_info_by_user — она будет принимать список идентификаторов пользователей, получать расширенную информацию о них и отправлять в таблицу vk_users. Так как параметр user_ids метода принимает список как строку, переводим структуру в тип str и отсекаем квадратные скобки. Многие пользователи скрывают пол, возраст или город — в таком случае вставляет Nullable значения. Для получения возраста берём текущий год и вычитаем год из даты рождения, если он представлен — проверку делаем регулярным выражением по четырём цифрам.

def get_and_insert_info_by_user(users):
    try:
        r = requests.get('https://api.vk.com/method/users.get', params={
            'access_token':token,
            'v':version,
            'user_ids':str(users)[1:-2],
            'fields':'sex, city, bdate'
        }).json()['response']
        for user in r:
            user_list = []
            user_list.append(user['id'])
            if client.execute(f"SELECT count(1) FROM vk_users where user_id={user['id']}")[0][0] == 0:
                print(user['id'])
                try:
                    user_list.append(user['sex'])
                except Exception:
                    user_list.append('cast(Null as Nullable(UInt8))')
                try:
                    user_list.append(user['city']['title'])
                except Exception:
                    user_list.append('')
                try:
                    now = datetime.now()
    			    year = item.split('.')[-1]
    			    if re.match(r'\d\d\d\d', year):
        		        age = now.year - int(year)
			    	   user_list.append(age)
                except Exception:
                    user_list.append('cast(Null as Nullable(UInt16))')
                user_insert_tuple = tuple(user_list)
                client.execute(f'INSERT INTO vk_users VALUES {user_insert_tuple}')
    except KeyError:
        pass

while True:
    for i in range(5):
        r = requests.get('https://api.vk.com/method/newsfeed.search', params={
            'access_token':token,
            'v':version,
            'q':query_string,
            'count':200,
            'start_from': start_from
        })
        data_list.append(r.json()['response'])
        try:
            start_from = r.json()['response']['next_from']
        except KeyError:
            pass

Полученные в ответе данные можно распарсить. В ВКонтакте у пользователей id всегда положительный, а у сообществ идёт со знаком минус. Чтобы получить данные только от пользователей, будем собирать только те, где from_id больше нуля. Следующая проверка — на отсутствие текста в посте, такие нам тоже не нужны. Наконец, будем собирать данные только если таких ещё нет — для этого обращаемся к таблице vk_posts по текущему id. В конце приостановим скрипт на 180 секунд, чтобы дождаться новых постов и не столкнуться с ограничениями по запросам VK API.

user_ids = []
    for data in data_list:
        for data_item in data['items']:
            if data_item['from_id'] > 0:
                post_list = []
                if not data_item['text']:
                    continue
                if client.execute(f"SELECT count(1) FROM vk_posts WHERE post_id={data_item['id']} AND from_id={data_item['from_id']}")[0][0] == 0:
                    user_ids.append(data_item['from_id'])
                    date = datetime.fromtimestamp(data_item['date'])
                    date = datetime.strftime(date, '%Y-%m-%d %H:%M:%S')
                    post_list.append(date)
                    post_list.append(data_item['id'])
                    post_list.append(data_item['owner_id'])
                    post_list.append(data_item['from_id'])
post_list.append(data_item['text'].replace("'","").replace('"','').replace("\n",""))
                    post_list.append(query_string)
                    post_tuple = tuple(post_list)
                    print(post_list)
                    try:
                        client.execute(f'INSERT INTO vk_posts VALUES {post_tuple}')
                    except Exception as E:
                        print('!!!!! try to insert into vk_post but got', E)
    try:
        get_and_insert_info_by_user(user_ids)
    except Exception as E:
        print("Try to insert user list:", user_ids, "but got:", E)
    time.sleep(180)

Анализ постов через Dostoevsky

Этот скрипт мы оставили работать на неделю: за это время он набрал почти 20000 постов из ВКонтакте, в которых упоминается ключевое слово «конституция». Напишем второй скрипт — для аналитики и визуализации данных. Для начала соберём данные из таблицы, сформируем DataFrame и для каждого поста получим значения тональности: насколько он положителен, отрицателен и нейтрален. Для оценки тональности текста будем использовать библиотеку Dostoevsky.

from dostoevsky.tokenization import RegexTokenizer
from dostoevsky.models import FastTextSocialNetworkModel
from clickhouse_driver import Client
import pandas as pd
client = Client(host='ec1-23-456-789-1011.us-east-2.compute.amazonaws.com', user='default', password='', port='9000', database='default')

Простым запросом содержимое всей таблицы с постами занесём в переменную vk_posts. Пройдём все посты, выберем те посты, где есть текст помимо пробелов и положим их в DataFrame.

vk_posts = client.execute('SELECT * FROM vk_posts')
list_of_posts = []
list_of_ids = []
for post in vk_posts:
    if str(post[-2]).replace(" ", ""):
        list_of_posts.append(str(post[-2]).replace("\n",""))
        list_of_ids.append(int(post[2]))
df_posts = pd.DataFrame()
df_posts['post'] = list_of_posts
df_posts['id'] = list_of_ids

Обходим моделью весь список постов с текстом и получаем к оценку тональности для каждой записи.

tokenizer = RegexTokenizer()
model = FastTextSocialNetworkModel(tokenizer=tokenizer)
sentiment_list = []
results = model.predict(list_of_posts, k=2)
for sentiment in results:
    sentiment_list.append(sentiment)

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

neutral_list = []
negative_list = []
positive_list = []
speech_list = []
skip_list = []
for sentiment in sentiment_list:
    neutral = sentiment.get('neutral')
    negative = sentiment.get('negative')
    positive = sentiment.get('positive')
    if neutral is None:
        neutral_list.append(0)
    else:
        neutral_list.append(sentiment.get('neutral'))
    if negative is None:
        negative_list.append(0)
    else:
        negative_list.append(sentiment.get('negative'))
    if positive is None:
        positive_list.append(0)
    else:
        positive_list.append(sentiment.get('positive'))
df_posts['neutral'] = neutral_list
df_posts['negative'] = negative_list
df_posts['positive'] = positive_list

Посмотрим, как выглядит наш DataFrame теперь:

Можем посмотреть примеры самых негативных постов:

df_posts[df_posts.negative > 0.9]

Нашей таблице не хватает данных об авторах постов. Возьмём их из таблицы vk_users и сольём обе таблицы по полю «id».

vk_users = client.execute('SELECT * FROM vk_users')
vk_user_ids_list = []
vk_user_sex_list = []
vk_user_city_list = []
vk_user_age_list = []
for user in vk_users:
    vk_user_ids_list.append(user[0])
    vk_user_sex_list.append(user[1])
    vk_user_city_list.append(user[2])
    vk_user_age_list.append(user[3])
df_users = pd.DataFrame()
df_users['id'] = vk_user_ids_list
df_users['sex'] = vk_user_sex_list
df_users['city'] = vk_user_city_list
df_users['age'] = vk_user_age_list
df = df_posts.merge(df_users, on='id')

Теперь таблица выглядит так:

Анализируем графики от plotly

В материале «Как построить красивый waterfall chart в Python?» мы уже строили графики библиотекой plotly

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

Из графика следует, что 46% постов по запросу «конституция» за последнюю неделю имеют негативный окрас. Другие 52% высказываются нейтрально. Чуть позже узнаем, насколько мнения в интернете совпадают с официальными результатами голосования.

Заметно, что доля положительных постов среди мужской аудитории составляет 2%, среди женской — вдвое больше, 4%. Впрочем, негативных постов в обоих группах практически поровну: 47% среди мужской и 44% среди женской.

Наконец, оценка постов по возрастным группам: больше всего доля позитивного текста наблюдается в группе 18 — 25 лет, это 3%. Меньше всего позитивных постов в группе до 18 лет, но это может происходить и в связи с тем, что многие пользователи моложе 18 лет предпочитают скрывать возраст, и точные данные по такой группе получить не удастся. Негативных постов во всех группах кроме 18 — 25 поровну: 46%.
Заметно, что на всех трёх графиках данные распределены приблизительно одинаково. Это говорит о том, что за последнюю неделю практически половина всех постов по ключевому слову «конституция» в новостной ленте ВКонтакте имела негативный окрас.

Когда-то давно в 2014ом году для одной из презентаций о рынке e-commerce в Юлмарте мы строили широко известную во всем мире консалтинга диаграмму Waterfall, средствами Excel. В этом материале построим Waterfall chart средствами Python — она наглядно демонстрирует изменения с появлением нового положительного или отрицательного фактора. Для построения диаграммы будем использовать библиотеку plotly.
Для тех, кто пропустил: в цикле материалов о визуализации данных на Python мы уже пробовали строить диаграмму Градусник — она полезна, когда мы хотим сравнить, как соотносятся ожидаемые и реальные данные.

В качестве данных используем сведенную в Юлмарте информацию об изменении объёма рынка e-commerce с 2013 по 2014 год. Данные по оси X — подписи к каждому столбцу, по Y — начальные, итоговые значения и их изменения. Функцией sum() посчитаем итог и добавим его в конец списка. Тег <br> в списке x_list означает перенос строки.

import plotly.graph_objects as go

x_list = ['2013','Макроэкономика РФ','Сокращение численности<br>трудоспобного населения','Проникновение интернета','Развитие трансграничной<br>торговли', 'Федеральные компании', '2014']
y_list = [738.5, 48.7, -7.4, 68.7, 99.7, 48.0]
total = round(sum(y_list))
y_list.append(total)

Создадим список text_list — это те самые значения столбцов. Они берутся из списка y_list, но сперва их нужно немного обработать: переведём все числа в строки и если это столбец с изменением, то есть любой столбец, кроме первого и последнего, добавим к строке знак «плюс» для наглядности. А ещё в случае положительного изменения поменяем цвет на зелёный и на красный в обратном случае. Первому и последнему значению прибавим жирности к шрифту тегом <b>.

text_list = []
for index, item in enumerate(y_list):
    if item > 0 and index != 0 and index != len(y_list) - 1:
        text_list.append(f'+{str(y_list[index])}')
    else:
        text_list.append(str(y_list[index]))
for index, item in enumerate(text_list):
    if item[0] == '+' and index != 0 and index != len(text_list) - 1:
        text_list[index] = '<span style="color:#2ca02c">' + text_list[index] + '</span>'
    elif item[0] == '-' and index != 0 and index != len(text_list) - 1:
        text_list[index] = '<span style="color:#d62728">' + text_list[index] + '</span>'
    if index == 0 or index == len(text_list) - 1:
        text_list[index] = '<b>' + text_list[index] + '</b>'

Для того, чтобы поместить на фон пунктирные линии, необходимо задать их параметры. Сделаем список словарей и положим в него пунктирные линии светло-серого цвета с положением по Y от 0 до 1000 с шагом в 200.

dict_list = []
for i in range(0, 1200, 200):
    dict_list.append(dict(
            type="line",
            line=dict(
                 color="#666666",
                 dash="dot"
            ),
            x0=-0.5,
            y0=i,
            x1=6,
            y1=i,
            line_width=1,
            layer="below"))

Теперь зададим диаграмму — она лежит в методе Waterfall(). У каждого столбца есть тип — total, absolute или relative. Колонки с итоговыми значениями получают тип total или absolute, с промежуточными — relative. Кроме того, задаём цвета: делаем соединяющую линию прозрачной, положительные изменения — зелёными, отрицательные — красными, а итоговые колонки — фиолетовыми. Для текста выберем шрифт Open Sans.

О том, как подобрать хорошие шрифты для своей визуализации данных, можно узнать в материале «Choosing Fonts for Your Data Visualization»

fig = go.Figure(go.Waterfall(
    name = "e-commerce", orientation = "v",
    measure = ["absolute", "relative", "relative", "relative", "relative", "relative", "total"],
    x = x_list,
    y = y_list,
    text = text_list,
    textposition = "outside",
    connector = {"line":{"color":'rgba(0,0,0,0)'}},
    increasing = {"marker":{"color":"#2ca02c"}},
    decreasing = {"marker":{"color":"#d62728"}},
    totals={'marker':{"color":"#9467bd"}},
    textfont={"family":"Open Sans, light",
              "color":"black"
             }
))

Наконец, добавим заголовок и описание графика, уберём легенду, подпишем ось Y и внесём пунктирные линии на график.

fig.update_layout(
    title = 
        {'text':'<b>Waterfall chart</b><br><span style="color:#666666">Изменение объема рынка e-commerce с 2013 по 2014 год</span>'},
    showlegend = False,
    height=650,
    font={
        'family':'Open Sans, light',
        'color':'black',
        'size':14
    },
    plot_bgcolor='rgba(0,0,0,0)',
    yaxis_title="млрд руб.",
    shapes=dict_list
)
fig.update_xaxes(tickangle=-45, tickfont=dict(family='Open Sans, light', color='black', size=14))
fig.update_yaxes(tickangle=0, tickfont=dict(family='Open Sans, light', color='black', size=14))

fig.show()

Получим такую диаграмму:

В Clickhouse реализована возможность использования внутренних и внешних словарей, которые могут быть альтернативой JOIN (которые, к сожалению, не всегда здорово работают). Словари хранят информацию в памяти и к ним можно обратиться командой dictGet. Рассмотрим как создать словарь в Clickhouse и как его можно использовать в запросах.

Будем изучать функционал на примере данных из API Untappd. Untappd — социальная сеть любителей крафтового пива. Мы сфокусируемся на чекинах российких крафтовых пивоварен, начнем собирать информацию о них, чтобы в следующих постах проанализировать данные и сделать некоторые выводы. В рамках этого поста разберем получение мета-информации о российских пивоварнях на Untappd, а полученные данные сохраним в словаре Clickhouse.

Собираем данные с Untappd

Для обращений к API нужны client_id и  client_secret_key — их можно получить, создав приложение. Для этого переходим в раздел создания приложения в документации и указываем некоторые данные:

После отправления заявки нужно будет подождать некоторое время: от 1 до 3 недель.

import requests
import pandas as pd
import time

Отправлять запросы к API будем через requests, а в pandas посмотрим на результаты и выгрузим в csv, чтобы отправить в словарь Clickhouse. У Untappd строгие ограничения на количество запросов: всего в час можно отправить 100 запросов, поэтому будем библиотекой time ставить скрипт в ожидание на 38 секунд, чтобы число запросов в час не превосходило 100.

client_id = 'ваш_client_id'
client_secret = 'ваш_client_secret'
all_brewery_of_russia = []

Мы хотим собрать всю тысячу российских пивоварен. Один запрос к методу Brewery Search позволяет получить до 50 пивоварен. При поиске вручную на сайте Untappd по слову «Russia» сайт выдаст 3369 пивоварен:

Проверим это: пролистаем страницу до самого низа и откроем код страницы.

Каждая полученная пивоварня в поиске находится в классе beer-item. Значит, можем в поиске посчитать количество упоминаний beer-item:

И выясняем, что на самом деле их здесь ровно 1000, а не 3369. По запросу Russia в выборку попадают и американские пивоварни, а некоторые были удалены. Значит, придётся отправить 20 запросов, будем получать по 50 пивоварен за раз:

for offset in range(0, 1000, 50):
    try:
        print('offset = ', offset)
        print('осталось:', 1000 - offset, '\n')
        response = requests.get(f'https://api.untappd.com/v4/search/brewery?client_id={client_id}&client_secret={client_secret}',
                               params={
                                   'q':'Russia',
                                   'offset':offset,
                                   'limit':50
                               })
        item = response.json()
        print(item, '\n')
        all_brewery_of_russia.append(item)
        time.sleep(37)
    except Exception:
        print(Exception)
        continue

В параметрах метод Brewery Search принимает q — строку, по которой будем осуществлять поиск на сервисе. Укажем в ней «Russia», чтобы получить все пивоварни, связанные с Россией. Другой параметр — offset — отвечает за смещение. Получив первые 50 пивоварен мы смещаемся на 50 строк в поиске, чтобы получить следующие 50 пивоварен. limit отвечает за количество получаемых пивоварен и не может быть больше 50.
Преобразовываем ответ в формат json и добавляем полученные данные в список all_brewery_of_russia. Объект item будет содержать такие данные:

Но в полученных данных могли затесаться и пивоварни других стран. Отфильтруем их: пройдём итератором по всему списку all_brewery_of_russia и добавим в итоговый только те пивоварни, у которых параметр country_name принимает значение Russia.

brew_list = []
for element in all_brewery_of_russia:
    brew = element['response']['brewery']
    for i in range(brew['count']):
        if brew['items'][i]['brewery']['country_name'] == 'Russia':
            brew_list.append(brew['items'][i])

Посмотрим на первый элемент списка brew_list:

print(brew_list[0])

Соберём из списка DataFrame с колонками brewery_id, beer_count, brewery_name, brewery_slug, brewery_page_url, brewery_city, lat и  lng. Получим в отдельные списки данные из  brewery_list:

df = pd.DataFrame()
brewery_id_list = []
beer_count_list = []
brewery_name_list = []
brewery_slug_list = []
brewery_page_url_list = []
brewery_location_city = []
brewery_location_lat = []
brewery_location_lng = []
for brewery in brew_list:
    brewery_id_list.append(brewery['brewery']['brewery_id'])
    beer_count_list.append(brewery['brewery']['beer_count'])
    brewery_name_list.append(brewery['brewery']['brewery_name'])
    brewery_slug_list.append(brewery['brewery']['brewery_slug'])
    brewery_page_url_list.append(brewery['brewery']['brewery_page_url'])
 brewery_location_city.append(brewery['brewery']['location']['brewery_city'])
    brewery_location_lat.append(brewery['brewery']['location']['lat'])
    brewery_location_lng.append(brewery['brewery']['location']['lng'])

И отправим их в DataFrame:

df['brewery_id'] = brewery_id_list
df['beer_count'] = beer_count_list
df['brewery_name'] = brewery_name_list
df['brewery_slug'] = brewery_slug_list
df['brewery_page_url'] = brewery_page_url_list
df['brewery_city'] = brewery_location_city
df['brewery_lat'] = brewery_location_lat
df['brewery_lng'] = brewery_location_lng

Посмотрим, как выглядит наша таблица:

df.head()

Отсортируем значения по  brewery_id и выгрузим таблицу в формате csv без столбца с индексами и заголовков колонок:

df = df.sort_values(by='brewery_id')
df.to_csv('brewery_data.csv', index=False, header=False)

Создаём словарь Clickhouse

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

Со всеми способами создания словарей можно ознакомиться в документации

<yandex>
<dictionary>
        <name>breweries</name>
        <source>
                <file>
                        <path>/home/ubuntu/brewery_data.csv</path>
                        <format>CSV</format>
                </file>
        </source>
        <layout>
                <flat />
        </layout>
        <structure>
                <id>
                        <name>brewery_id</name>
                </id>
                <attribute>
                        <name>beer_count</name>
                        <type>UInt64</type>
                        <null_value>Null</null_value>
                </attribute>
                <attribute>
                        <name>brewery_name</name>
                        <type>String</type>
                        <null_value>Null</null_value>
                </attribute>
                <attribute>
                        <name>brewery_slug</name>
                        <type>String</type>
                        <null_value>Null</null_value>
                </attribute>
                <attribute>
                        <name>brewery_page_url</name>
                        <type>String</type>
                        <null_value>Null</null_value>
                </attribute>
                <attribute>
                        <name>brewery_city</name>
                        <type>String</type>
                        <null_value>Null</null_value>
                </attribute>
                <attribute>
                        <name>lat</name>
                        <type>String</type>
                        <null_value>Null</null_value>
                </attribute>
                <attribute>
                        <name>lng</name>
                        <type>String</type>
                        <null_value>Null</null_value>
                </attribute>
        </structure>
        <lifetime>300</lifetime>
</dictionary>
</yandex>

Под  идёт имя словаря. В  указываем свойства колонок. Под тегом идёт ключевое поле, а под тегом укажем путь и формат файла. Скоро мы положим его в папку /home/ubuntu, поэтому так и укажем.

Загрузим нашу csv-таблицу и xml-файл на сервер, это можно сделать, например, по ftp через FileZilla. В одном из материалов мы учились ставить Clickhouse на бесплатную машину от Amazon, в этот раз будем работать там же. В FileZilla заходим в настройки SFTP и добавляем файл с ключом:

И подключаемся к серверу по адресу, который указан в консоли EC2 машины на AWS. Укажем протокол SFTP, свой Host и в качестве User — Ubuntu:

В случае перезагрузки машины через консоль Public DNS мог измениться

После подсоединения мы попадём в папку /home/ubuntu сервера. Положим файлы туда же. Теперь подключимся по SSH через Termius. Чтобы Clickhouse увидел файл со структурой словаря, его нужно положить в папку /etc/clickhouse-server:

О том, как подключаться в серверу на AWS через SSH-клиент мы рассказывали в материале «Устанавливаем Clickhouse на AWS»

sudo mv breweries_dictionary.xml /etc/clickhouse server/

Переходим в конфигурационный файл:

cd /etc/clickhouse-server
sudo nano config.xml

Нам нужен тег  — он указывает путь к файлу, который описывает структуру словарей. Укажем путь к нашему xml:

<dictionaries_config>/etc/clickhouse-server/breweries_dictionary.xml</dictionaries_config>

Сохраняем файл и запускаем клиент Clickhouse:

clickhouse client

Проверим, что наш словарь действительно загрузился:

SELECT * FROM system.dictionaries\G

В случае успеха получим подобное:

Напишем запрос к функции dictGet, чтобы получить название пивоварни под ID 999. Указываем первым аргументом наименование словаря, затем поле, значение которого хотим получить и ID.

SELECT dictGet('breweries', 'brewery_name', toUInt64(999))

Если сделаем всё правильно, то выясним, что под ID 999 находится Балтика:

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

Эта статья — продолжение серии материалов «Собираем данные с чеков гипермаркетов на Python» и «Парсим данные каталога сайта». В этот раз построим модель, которая обучится на датасете из собранного каталога и классифицирует товарные позиции чека из гипермаркета на продуктовые категории. Суть проблемы: в чеке мы видим данные о каждом товаре отдельно, а иногда хочется быстро понять сколько сегодня потратили денег на «Сладкое».

Предобработка датасета

Импортируем библиотеку pandas и прочитаем csv-файл с каталогом igoods (мы сформировали его, когда парсили каталог). Заодно посмотрим, как он выглядит:

Подробнее о том, как программе эмулировать поведение человека на сайте и собрать датасет из каталога можно прочитать в материале «Парсим данные каталога сайта»

import pandas as pd
sku = pd.read_csv('SKU_igoods.csv',sep=';')
sku.head()

После парсинга в таблице осталось несколько ненужных колонок: например, нам ни к чему знать цену на продукт и его вес, чтобы построить модель предсказания категории товара. Избавляемся от этих колонок методом drop(), а остальные переименуем через rename() и снова смотрим на таблицу:

sku.drop(columns=['Unnamed: 0', 'Weight','Price'],inplace=True)
sku.rename(columns={"SKU": "SKU", "Category": "Group"},inplace=True)
sku.head()

Сгруппируем товары по их категории и посчитаем количество функциями groupby() и agg():

sku.groupby('Group').agg(['count'])

Наша модель должна обучиться на каталоге и, увидев наименование товара, предсказать его категорию. Но в каталоге многие названия будут непонятны модели. В русском языке, например, много предлогов, союзов и других стоп-слов: мы хотим, чтобы модель понимала, что «Мангал с ребрами жесткости» и «Мангал с 6 шампурами» — продукты одной и той же категории. Для этого почистим все названия: уберём из них союзы, предлоги, междометия, частицы и приведём слова к своим основам при помощи стеммера.

Стеммер — программа, которая находит для заданного слова его основу.

import nltk
from nltk.corpus import stopwords
from pymystem3 import Mystem
from string import punctuation
nltk.download('stopwords')

Для стемминга будем использовать стеммер Яндекса из библиотеки pymystem3. Список стоп-слов необходимо расширить — каталог товаров из магазина немного отличается от бытовых ситуаций, в которых базовый набор актуален.

mystem = Mystem() 
russian_stopwords = stopwords.words("russian")
russian_stopwords.extend(['лента','ассорт','разм','арт','что', 'это', 'так', 'вот', 'быть', 'как', 'в', '—', 'к', 'на'])

Опишем функцию подготовки текста. Она приводит текст стеммером к своей основе, убирает из него знаки пунктуации, цифры и стоп-слова. Этот код был найден в одном из kernel на kaggle.

def preprocess_text(text):
    text = str(text)
    tokens = mystem.lemmatize(text.lower())
    tokens = [token for token in tokens if token not in russian_stopwords\
              and token != " " \
              and len(token)>=3 \
              and token.strip() not in punctuation \
              and token.isdigit()==False]
    text = " ".join(tokens)
    return text

Проверим, как работает функция:

preprocess_text("Мой дядя самых честных правил, Когда не в шутку занемог, Он уважать себя заставил И лучше выдумать не мог.")

Получаем:

'дядя самый честный правило шутка занемогать уважать заставлять выдумывать мочь'

А значит всё работает как надо — все слова в своей морфологической основе и переведены в нижний регистр, отсутствует пунктуация и предлоги. Теперь опробуем функцию на одном из наименований товара из каталога:

print(‘Было:’, sku['SKU'][0])
print(‘Стало:’, preprocess_text(sku['SKU'][0]))

Получаем:

Было: Фисташки соленые жареные ТМ 365 дней
Стало: фисташка соленый жареный день

Функция справляется отлично, теперь можем применить её ко всем наименованиям и вынести обработанные названия в новый столбец processed. Посмотрим, как выглядит датасет теперь:

sku['processed']=sku['SKU'].apply(preprocess_text)
sku.head()

Строим модель предсказания категории

Для предсказания категории товара будем использовать CountVectorizer и наивный байесовский классификатор. Первый разобьёт текст на токены и посчитает их количество, а второй — простейший мультикатегорийный классификатор, позволит обучить модель предсказывать категорию товара. Также нам потребуются TfidfTransformer для подсчета весов вхождения каждого токена. Поскольку мы хотим запустить все функции одну за другой, обратимся к библиотеке Pipeline.

import pandas as pd
from sklearn.model_selection import train_test_split
from sklearn.feature_extraction.text import CountVectorizer
from sklearn.feature_extraction.text import TfidfTransformer
from sklearn.naive_bayes import MultinomialNB
from imblearn.pipeline import Pipeline

Поделим наш датасет на X — обработанные наименования товаров и на Y — их категории. Разделим на обучающую и тестовую выборку, отдав под тесты 33% от общего числа данных.

x = sku.processed
y = sku.Group
X_train, X_test, y_train, y_test = train_test_split(x, y, test_size=0.33)

Пройдём пайплайном следующие команды:

• CountVectorizer() — вернет матрицу с количеством вхождений каждого токена
• TfidfTransformer() — преобразует эту матрицу в нормализованное представление tf-idf
• MultinomialNB() — наивный байесовский классификатор для предсказания категории товара

text_clf = Pipeline([('vect', CountVectorizer(ngram_range=(1,2))),
                     ('tfidf', TfidfTransformer()), 
                    ('clf', MultinomialNB())])

На выходе получим модель в text_clf, которую затем обучим по обучающей выборке и посчитаем предсказания по тестовой выборке:

text_clf = text_clf.fit(X_train, y_train)
y_pred = text_clf.predict(X_test)

А теперь оценим модель:

print('Score:', text_clf.score(X_test, y_test))

Получим такую точность:

Score: 0.923949864498645

Верификация на реальных данных

Можем проверить, как работает модель на реальных данных из свежего чека. В материале о том, как получить продукты из чека гипермаркета, на выходе мы получали DataFrame с продуктами — возьмём его и применим к названиям товаров функцию preprocess_text.

my_products['processed']=my_products['name'].apply(preprocess_text)
my_products.head()

Заполним новый столбец prediction — он будет предсказывать категорию товара по его названию. Передаем ему колонку с обработанными названиями и создаём новую колонку с предсказаниями.

prediction = text_clf.predict(my_products['processed'])
my_products['prediction']=prediction
my_products[['name', 'prediction']]

DataFrame станет таким:

И посчитаем сумму по каждой категории:

my_products.groupby('prediction').sum()

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

Очень часто диаграммы, построенные стандартными средствами Matplotlib, выглядят некрасиво и неинформативно. В 2011ом году для целей одного из отчетов телеком-компании в Excel мы построили полезную симпатичную диаграмму «Градусник», рецепт которой стал известен из популярного в тот момент блога Chandoo про приемы визуализации в Excel.
Вот как она выглядела в Excel:

Времена меняются, и мы попробуем восстановить знание о построении этой полезной диаграммы, используя штатные средства библиотеки matplotlib в Python.

Для каких случаев подойдет диаграмма «Градусник»?
Лучше всего использовать данный тип для сравнения плановых и фактических значений, таким образом наглядно можно увидеть недовыполнение и перевыполнение показателей. При этом план / факт может быть как в процентах, так и в фактических значениях. Мы рассмотрим пример с фактическими значениями в условных единицах.

В этот раз возьмем данные из excel-файла. Используем типичный состав библиотек для работы с данными (и соответствующие им типичные alias):

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

И считываем в DataFrame таблицу:

df = pd.read_excel('data.xlsx')

Посмотрим, как она выглядит:

Начнем извлекать из таблицы колонки. Первый столбец «Продажи» будет вертикальной подписью к каждому столбцу на графике. «План» — статичный столбец, относительно которого измеряется «Факт». Некоторые данные могут приходить в виде вещественных чисел — такие столбцы будут считаны как тип str, если в данных будет встречена запятая. Чтобы работать с такими числами, будем сначала менять в них запятую на точку, а затем переводить в тип float.

xticks = df.iloc[:,0]
try:
    bars2 = df.iloc[:,1].str.replace(',','.').astype('float')
except AttributeError:
    bars2 = df.iloc[:,1].astype('float')
try:
    bars1 = df.iloc[:,2].str.replace(',','.').astype('float')
except AttributeError:
    bars1 = df.iloc[:,2].astype('float')

Так как мы не знаем наверняка, будут ли в данных такие числа, можем словить AttributeError в случае их отсутствия, ведь будем обращаться к методу str, который есть только у строк. Поэтому напишем обработчик исключений try — except, который будет на всякий случай переводить данные в тип float.

Построим из этого классический barchart — график со столбцами. Зададим массив положения на оси Х для bars1 функцией np.arange и bars2, смещённый на ширину столбца:

barWidth = 0.2
r1 = np.arange(len(bars1))
r2 = [x + barWidth for x in r1]
 
plt.bar(r1, bars1, width=barWidth)
plt.bar(r2, bars2, width=barWidth)

И посмотрим, что получилось:

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

barWidth1 = 0.065
barWidth2 = 0.032
x_range = np.arange(len(bars1) / 8, step=0.125)

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

plt.bar(x_range, bars1, color='#dce6f2', width=barWidth1/2, edgecolor='#c3d5e8', label='План')
plt.bar(x_range, bars2, color='#ffc001', width=barWidth2/2, edgecolor='#c3d5e8', label='Факт')
for i, bar in enumerate(bars2):
    plt.text(i / 8 - 0.015, bar + 1, bar, fontsize=14)

Наконец, сделаем несколько визуальных штрихов — уберём лишние рамки, чёрточки, добавим серую линию под столбцами, поправим размер и шрифт легенде, сделаем диаграмму шире, выведем её на экран и сохраним как plt.png в директории скрипта:

plt.xticks(x_range, xticks)
plt.tick_params(
    bottom=False,
    left=False,
    labelsize=15
)
plt.rcParams['figure.figsize'] = [25, 7]
plt.axhline(y=0, color='gray')
plt.legend(frameon=False, loc='lower center', bbox_to_anchor=(0.25, -0.3, 0.5, 0.5), prop={'size':20})
plt.box(False)
plt.savefig('plt', bbox_inches = "tight")
plt.show()

Получили такую диаграмму:

В пятничном лонгриде проделаем большую работу: возьмём информацию по рекламным кампаниям ВКонтакте и сопоставим их с данными Google Analytics в Redash. Чтобы снова не поднимать сервер, будем передавать данные через Google Docs, используя Spreadsheet API.

Получение access token
Для получение пользовательского ключа ВКонтакте нужно создать приложение. Идём в раздел «Разработчики» по https://vk.com/apps?act=manage, жмём на кнопку «Создать приложение». В поле «Тип приложения» выбираем «Standalone-приложение» и даём любое название. После этого в меню слева идём в настройки и сохраняем себе ID приложения.

Актуальную информацию о ключах можно посмотреть в статье «Получение ключа доступа»

Теперь копируем себе эту ссылку:

https://oauth.vk.com/authorize?client_id=YourClientID&scope=ads&response_type=token

Но вместо YourClientID вставляем ID своего созданного приложения. В scope у этой ссылки только ads, так что с этим ключом можно будет получать только информацию о рекламном кабинете. Вставляем её в браузер и нас скидывает на другую страницу — в адресе этой странице будет указан ваш сгенерированный access token.

Срок жизни токена — 86400 секунд: ровно сутки. Чтобы получить токен без временных ограничений можно добавить в scope параметр offline. Если токен понадобилось отозвать — смените пароль от страницы или в настройках безопасности завершите активные сессии.

Ещё для запросов к API нам пригодится ID рекламного кабинета — проходим по https://vk.com/ads?act=settings и копируем «номер кабинета».

Сбор данных через запросы к API
Напишем скрипт, который обращается к серверу ВКонтакте с нашим access token и номером рекламного кабинета и берёт информацию о всех кампаниях пользователя: количество просмотров на рекламах, кликов и затрат. Затем скрипт будет формировать из него DataFrame и отправлять в Google Docs.

from oauth2client.service_account import ServiceAccountCredentials
from pandas import DataFrame
import requests
import gspread
import time

Зададим несколько константных значений: access token, ID рекламного кабинета и версию API ВКонтакте, которую будем использовать. Актуальной является версия 5.103.

token = 'fa258683fd418fafcab1fb1d41da4ec6cc62f60e152a63140c130a730829b1e0bc'
version = 5.103
id_rk = 123456789

За получение статистики по рекламе отвечает метод ads.getStatistics, но один из обязательных параметров при его вызове — ’ids’, ID рекламного объявления, статистику по которому мы хотим получить. Так как ID у нас пока нет, придётся сначала воспользоваться методов ads.getAds, который возвращает ID объявлений и кампаний.

Подробнее со всеми методами ВКонтакте API можно ознакомиться в документации

Библиотекой requests отправляем запрос к серверу и передаём свои параметры. Полученный ответ сразу переведём в формат json

campaign_ids = []
ads_ids = []
r = requests.get('https://api.vk.com/method/ads.getAds', params={
    'access_token': token,
    'v': version,
    'account_id': id_rk
})
data = r.json()['response']

Вот, как выглядит объект data: нам вернулся обычный список словарей, с которым мы уже имели дело в материале “Передаём и анализируем собранные данные по рекламным капманиям в Redash”.

Заполняем словарь ad_campaign_dict. Ключом будет ID объявления, а значением — ID кампании, к которой принадлежит объявление. Так будет удобнее присваивать к объявлению ID кампании, к которой оно принадлежало.

ad_campaign_dict = {}
for i in range(len(data)):
    ad_campaign_dict[data[i]['id']] = data[i]['campaign_id']

Теперь, имея ID каждого нужного объявления, можно обратиться к методу ads.getStatistics. Мы будем собирать количество просмотров, кликов, затрат и даты начала и конца объявления, поэтому заблаговременно заведём пустые списки.

ads_campaign_list = []
ads_id_list = []
ads_impressions_list = []
ads_clicks_list = []
ads_spent_list = []
ads_day_start_list = []
ads_day_end_list = []

Вызывать getStatistics нужно отдельно для каждого объявления — будем делать это в итераторе по ad_campaign_dict. Отправляем запрос, передавая в ‘period’ значение ‘overall’ — берём данные за всё время. У некоторых объявлений могут отсутствовать данные по полю «Просмотры» или «Клики» если они не были запущены, и, потребовав их, мы словим KeyError — во избежание этого добавим обработчик try — except, который заставит скрипт не обращать внимания на эту ошибку.

for ad_id in ad_campaign_dict:
        r = requests.get('https://api.vk.com/method/ads.getStatistics', params={
            'access_token': token,
            'v': version,
            'account_id': id_rk,
            'ids_type': 'ad',
            'ids': ad_id,
            'period': 'overall',
            'date_from': '0',
            'date_to': '0'
        })
        try:
            data_stats = r.json()['response']
            for i in range(len(data_stats)):
                for j in range(len(data_stats[i]['stats'])):
                    ads_impressions_list.append(data_stats[i]['stats'][j]['impressions'])
                    ads_clicks_list.append(data_stats[i]['stats'][j]['clicks'])
                    ads_spent_list.append(data_stats[i]['stats'][j]['spent'])
                    ads_day_start_list.append(data_stats[i]['stats'][j]['day_from'])
                    ads_day_end_list.append(data_stats[i]['stats'][j]['day_to'])
                    ads_id_list.append(data_stats[i]['id'])
                    ads_campaign_list.append(ad_campaign_dict[ad_id])
        except KeyError:
            continue

Теперь сформируем из списков DataFrame и выведем первые 5 элементов:

df = DataFrame()
df['campaign_id'] = ads_campaign_list
df['ad_id'] = ads_id_list
df['impressions'] = ads_impressions_list
df['clicks'] = ads_clicks_list
df['spent'] = ads_spent_list
df['day_start'] = ads_day_start_list
df['day_end'] = ads_day_end_list
print(df.head())

Экспорт данных в Google Docs
Для экспорта DataFrame в таблицу Google Sheets необходим ключ доступа Google API. Пройдём по https://console.developers.google.com и создадим новый проект. Даём ему любое имя и в Dashboard жмём на кнопку “Подключить API и сервисы”. Нужно включить два API — Google Drive API и Google Sheets API. Ищем первый в поиске, нажимаем на “Включить API”, затем ищем второй и проделываем то же самое.

После включения нас отправят на панель управления API. Жмём на «Создать учётные данные» — по ним будем проводить авторизацию в скрипте. Отмечаем, что используем Google Sheets API из веб-сервера и обращаемся к данным пользователя. Нажимаем на «Выбрать тип учётных данных» и создаем сервисный аккаунт. В поле «Роль» выбираем Проект — Редактор, а тип ключа оставим JSON.

После этого нам отправят файл в формате JSON с нашими учетными данными — назовём его «credentials.json» — и перенаправят на страницу с сервисными аккаунтами. Ниже будет поле с почтой — копируем её себе.

Переходим по https://docs.google.com/spreadsheets и создаем пустой файл с названием data, в который будут отправляться данные из DataFrame. В настройках доступа даём доступ по почте, скопированной ранее из сервисных аккаунтов — от неё будут приходить данные из скрипта.

Закинем файл credentials.json в директорию со скриптом и продолжим писать код. Перечисляем область видимости в виде ссылок:

scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']

И при помощи библиотек oauth2client и gspread проводим авторизацию методами ServiceAccountCredentials.from_json_keyfile_name и gspread.authorize, указывая в параметрах первого наш файл и переменную scope. Через переменную sheet будем обращаться к нашему файлу в Google Docs.

creds = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scope)
client = gspread.authorize(creds)
sheet = client.open('data').sheet1

Для ввода значений в ячейку таблички есть метод update_cell. Важно: нумерация индексов ячеек при обращении начинается не с нуля, а с единицы. Первым циклом пройдём по первой строке и перенесем туда заголовки нашего DataFrame. Во втором будем идти по каждой ячейке и вставлять соответствующие значения DataFrame. По умолчанию стоит ограничение — 100 запросов в 100 секунд. Это ограничение может остановить наш скрипт на полпути: чтобы избежать ошибки пропишем time.sleep, чтобы после каждой вставки скрипт секунду выжидал.

count_of_rows = len(df)
count_of_columns = len(df.columns)
for i in range(count_of_columns):
    sheet.update_cell(1, i + 1, list(df.columns)[i])
for i in range(1, count_of_rows + 1):
    for j in range(count_of_columns):
        sheet.update_cell(i + 1, j + 1, str(df.iloc[i, j]))
        time.sleep(1)

Если всё сделаем правильно — получим таблицу такого вида:

Экспорт данных в Redash

Подключение Google Analytics к Redash описано в статье «Как подключить Google Analytics как Redash?».

Имея в Redash таблицу с Google Analytics и рекламным кампаниям ВКонтакте, можем сопоставить их друг другу. Напишем такой запрос:

SELECT
    query_50.day_start,
    CASE WHEN ga_source LIKE '%vk%' THEN 'vk.com' END AS source,
    query_50.spent,
    query_50.impressions,
    query_50.clicks,
    SUM(query_49.ga_sessions) AS sessions,
    SUM(query_49.ga_newUsers) AS users
FROM query_49
JOIN query_50
ON query_49.ga_date = query_50.day_start
WHERE query_49.ga_source LIKE '%vk%' AND DATE(query_49.ga_date) BETWEEN '2020-05-16' AND '2020-05-20'
GROUP BY query_49.ga_date, source

ga_source — источник, с которого человек пришел на сайт. Всё, что похоже на vk оператором CASE объединяем в столбец «vk.com». Оператором JOIN добавляем таблицу с данными из ВКонтакте, объединяя по полю даты. Отсеиваем данные — возьмём день последней рекламной кампании и посмотрим на несколько дней после него. На выходе получим таблицу такого вида:

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