Блог об аналитике, визуализации данных, data science и BI

    Valiotti Analytics — построение аналитики для мобильных и digital-стартапов

Строим Motion chart по индексу Биг Мака на Python

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

Одной из самых знаменитых визуализаций, конечно же, является работа 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

Анимируем теннисные мячики в Tableau

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

В прошлом видео мы научились визуализировать теннисные мячики из Swing Vision на графике в Tableau с кастомным фоном и кастомными фигурами. Сегодня будем анимировать дашборд, чтобы посмотреть, как менялись удары и получим видео с результатами игры, которое можно экспортировать и кому-нибудь показать.

Краткое резюме:

  1. Создаём элемент Pages. Он позволяет управлять движением анимации: нажимая на кнопку Play, Title страницы будет меняться.
  2. Добавим историю: ставим галочку на Show History ниже и выберем длину в 7 ударов.
  3. Вернёмся на дашборд. Переходим во вкладку Worksheet — Show Cards и выбираем Current Page.
  4. Для захвата видео с экрана добавим новый контейнер и перенесём в него панель с ударами.
  5. Утилитой записи экрана выбираем область с дашбордом и жмём на Play. Для macOS можно воспользоваться встроенной: достаточно нажать комбинацию клавиш ⌘ + Shift + 5.

Теннисные мячики из статистики Swing Vision на графике в Tableau

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

Я увлекаюсь теннисом и недавно обнаружил относительно свежее приложение, помогающее теннисистам понять качество своей игры — Swing Vision. Приложение чудесное: в реальном времени распознает удары ракеткой по мячу и отображает координаты каждого удара. Автор приложения — профессор UC Berkeley, Swupnil Sahai, недавно я написал ему на почту и поблагодарил за такую полезную вещь.

Так выглядит статистика в приложении

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

Так выглядит xls-файл

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

Краткое резюме:

  1. Подготавливаем изображение теннисного корта
  2. Импортируем данные из xls-файла в Tableau.
  3. Создаём новые Calculated Fields, в которых преобразуем вертикально и горизонтально координаты удара.
  4. Для всех мячей, попавших в сетку, ставим конкретную координату по Y = -11,89.
  5. Фильтруем, добавляем цвета и размечаем данные на графике:
    a. Работаем с фоновым изображением
    b. Считаем ratio корта на изображении к корту в метрах. К примеру, в моём случае ширина 913px, а ширина корта — 10,97 метров. Значит, соотношение — 83,2270
    c. Считаем отступы, которые нужно сделать от 0 коориднат
    d. Аналогичные действия проделываем с Y-координатами
    e. Добавляем фоновое изображение и устанавливаем его свойства
  6. Находим векторную иконку тенисного мяча.
  7. Кладём иконку в папку Shapes репозитория Tableau.
  8. Меняем иконку в меню Shapes.

В результате получаем:

Разница между Retention на основе 24-часовых окон и календарных дней

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

Вчера в Telegram мне написал читатель блога:

Допустим, сегодня понедельник, я сделал 187 установок, и хочу посмотреть Retention первого дня, в какой день недели это можно сделать?

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

К примеру, если вы начали продвижение в понедельник 5 октября в 23:59, то все установки этой минуты через пару минут будут иметь Retention первого дня. Это проблема календарного исчисления. Для решения этой проблемы некоторые аналитики измеряют Retention не только по календарю, но и по 24-часовым окнам.

Приложим эту идею к вышеописанному случаю:

  • Retention нулевых суток в таком случае — все инсталлы с 5 октября 23:59 по 6 октября 23:59
  • Retention первого дня: с 6 октября 23:59 по 7 октября 23:59
  • И так далее со сдвигом 24-часового окна.

Как рассчитать Retention на основе 24-часовых окон с использованием SQL?

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


SELECT from_unixtime(user.installed_at, "yyyy-MM-dd") AS reg_date,
   floor((cast(cs.created_at as int)-cast(installed_at as int))/(24*3600)) as date_diff,
   ndv(user.id) AS ret_base
   FROM USER
   LEFT JOIN client_session cs ON cs.user_id=user.id
   WHERE 1=1
     AND floor((cast(cs.created_at as int)-cast(installed_at as int))/(24*3600)) between 0 and 30
     AND from_unixtime(user.installed_at)>=date_add(now(), -60)
     AND from_unixtime(user.installed_at)<=date_add(now(), -31)
   GROUP BY 1,2

Обновленный запрос:

SELECT 
       cohort.date_diff AS day_difference,
       avg(reg.users) AS cohort_size,
       avg(cohort.ret_base) AS retention_base,
       avg(cohort.ret_base)/avg(reg.users)*100 AS retention_rate
FROM
  (SELECT from_unixtime(user.installed_at, "yyyy-MM-dd") AS reg_date,
          ndv(user.id) AS users
   FROM USER
   WHERE from_unixtime(user.installed_at)>=date_add(now(), -60)
     AND from_unixtime(user.installed_at)<=date_add(now(), -31)
   GROUP BY 1) reg
LEFT JOIN
  (SELECT from_unixtime(user.installed_at, "yyyy-MM-dd") AS reg_date,
    floor((cast(cs.created_at as int)-cast(installed_at as int))/(24*3600)) as date_diff,
          ndv(user.id) AS ret_base
   FROM USER
   LEFT JOIN client_session cs ON cs.user_id=user.id
    WHERE 1=1
     AND floor((cast(cs.created_at as int)-cast(installed_at as int))/(24*3600)) between 0 and 30
     AND from_unixtime(user.installed_at)>=date_add(now(), -60)
     AND from_unixtime(user.installed_at)<=date_add(now(), -31)
   GROUP BY 1,2
  ) cohort ON reg.reg_date=cohort.reg_date
    GROUP BY 1        
    ORDER BY 1

Результат:

Сравним с результатам, полученным предыдущим скриптом:

Заметно, что в первые дни Retention, посчитанный методом 24-часового окна, несколько ниже.

Постановка задачи для дашборда

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

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

Постановка задачи

Наша задача — спроектировать и реализовать дашборд на тестовом датасете SuperStore Sales (он, кстати, весьма приближен к реальности), который ответит на следующие вопросы:

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

О датасете

В данных содержится информация о покупках (лист Orders) и возвратах (лист Returns) клиентов. Данные о покупках содержат всю доступную информацию о заказах: идентификаторы записи, даты заказов, приоритетность заказов, количество позиций, объём продаж и размер прибыли, размеры скидкок, типы и цены доставки, данные о клиентах и прочую полезную информацию. Мы будем работать только с листом данных Orders.

Фрагмент листа Orders

Макет дашборда

В верхней панели расположен заголовок дашборда с его кратким описанием. На том же уровне фильтр временного отрезка (конкретный месяц отчета). На уровне ниже — подзаголовок «KPI».

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

  • Прибыль (Profit) и прирост YoY
  • Продажи (Sales) и прирост YoY
  • Количество заказов (Orders count) и прирост YoY
  • Средняя скидка (Avg Discount) и прирост YoY
  • Число клиентов (Customers) и прирост YoY
  • Продажи на клиента (Sales per Customer) и прирост YoY

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

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

Следующий блок анализа — продукты и клиентские сегменты. На левой горизонтальной столбчатой диаграмме типа «Градусник» расположим объём продаж и прибыль по категориям и подкатегориям. По возможности BI-системы к диаграмме добавим фильтр топа товарных наименований по прибыли.

О том, как строить диаграмму Градусник в Python можно почитать в материале о красивой визуализации

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

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

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

Предполагаемый макет дашборда в BI-инструменте
Ранее Ctrl + ↓