Все статьи

4 минут чтения

10 июля 2020 г.

Строим scatter plot по пивоварням Untappd

Сегодня построим scatter plot, который отобразит отношение количества отзывов российских пивоварен к их средней оценке за последние 30 дней. В качестве данных будем использовать чекины социальной сети Untappd, которые пользователи оставляют для оценки пива. Маркеры на графике будут иметь две характеристики: цвет и размер. Цвет будет зависеть от даты регистрации пивоварни на сервисе (то есть показывать сколько лет пивоварне в Untappd), а размер — от количества сортов пива в её ассортименте. Этот материал – первая часть цикла материалов, посвященных построению дашборда с библиотекой dash от plotly.

Пишем запрос к Clickhouse

Данные, по которым мы хотим построить дашборд для начала нужно обработать. Мы использовали открытые данные, собранные с сайта Untappd в материалах «Обрабатываем нажатие кнопки в Selenium» и «Использование словарей в Clickhouse на примере данных Untappd».


from datetime import datetime, timedelta
from clickhouse_driver import Client
import plotly.graph_objects as go
import pandas as pd
import numpy as np
client = Client(host=’ec1-2-34-567-89.us-east-2.compute.amazonaws.com’, user=’default’, password=», port=’9000′, database=’default’)

График будет строиться в функции get_scatter_plot(n_days, top_n). Первый аргумент будет отвечать за временной период, который нужно обработать. Второй — какое количество пивоварен из топа отобразить на графике. Для начала напишем SQL-запрос, который возьмёт данные из таблицы Clickhouse и посчитает Brewery Pure Average. Для каждой пивоварни на сервисе он считается так: умножаем рейтинг сорта пива на количество оценок этого сорта и делим на общее число оценок пивоварни. Ещё запрос возьмёт наименование пивоварни и количество сортов пива у пивоварни из словаря, с которым мы работали ранее: при помощи функции dictGet обратимся к нему прямо в запросе и возьмём нужные колонки. Зададим ограничение: нас интересуют только те пивоварни, у которых Brewery Pure Average отличен от нуля, а количество отзывов более 100.


brewery_pure_average = client.execute(f»»»
SELECT
       t1.brewery_id,
       sum(t1.beer_pure_average_mult_count / t2.count_for_that_brewery) AS brewery_pure_average,
       t2.count_for_that_brewery,
       dictGet(‘breweries’, ‘brewery_name’, toUInt64(t1.brewery_id)),
       dictGet(‘breweries’, ‘beer_count’, toUInt64(t1.brewery_id)),
       t3.stats_age_on_service / 365
   FROM
   (
       SELECT
           beer_id,
           brewery_id,
           sum(rating_score) AS beer_pure_average_mult_count
       FROM beer_reviews
       WHERE created_at >= today()-{n_days}
       GROUP BY
           beer_id,
           brewery_id
   ) AS t1
   ANY LEFT JOIN
   (
       SELECT
           brewery_id,
           count(rating_score) AS count_for_that_brewery
       FROM beer_reviews
       WHERE created_at >= today()-{n_days}
       GROUP BY brewery_id
   ) AS t2 ON t1.brewery_id = t2.brewery_id
   ANY LEFT JOIN
   (
       SELECT
           brewery_id,
           stats_age_on_service
       FROM brewery_info
   ) AS t3 ON t1.brewery_id = t3.brewery_id
   GROUP BY
       t1.brewery_id,
       t2.count_for_that_brewery,
       t3.stats_age_on_service
   HAVING t2.count_for_that_brewery >= 150
   ORDER BY brewery_pure_average
   LIMIT {top_n}
    «»»)

scatter_plot_df_with_age = pd.DataFrame(brewery_pure_average, columns=[‘brewery_id’, ‘brewery_pure_average’, ‘rating_count’, ‘brewery_name’, ‘beer_count’])

Обрабатываем значения из DataFrame

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


dict_list = []
dict_list.append(dict(type=»line»,
                     line=dict(
                         color=»#666666″,
                         dash=»dot»),
                     x0=0,
                     y0=np.median(scatter_plot_df_with_age.brewery_pure_average),
                     x1=7000,
                     y1=np.median(scatter_plot_df_with_age.brewery_pure_average),
                     line_width=1,
                     layer=»below»))
dict_list.append(dict(type=»line»,
                     line=dict(
                         color=»#666666″,
                         dash=»dot»),
                     x0=np.median(scatter_plot_df_with_age.rating_count),
                     y0=0,
                     x1=np.median(scatter_plot_df_with_age.rating_count),
                     y1=5,
                     line_width=1,
                     layer=»below»))

Добавим аннотации: они будут сообщать пользователю медианные значения.


annotations_list = []
annotations_list.append(
    dict(
        x=8000,
        y=np.median(scatter_plot_df_with_age.brewery_pure_average) — 0.1,
        xref=»x»,
        yref=»y»,
        text=f»Медианное значение: {round(np.median(scatter_plot_df_with_age.brewery_pure_average), 2)}»,
        showarrow=False,
        font={
            ‘family’:’Roboto, light’,
            ‘color’:’#666666′,
            ‘size’:12
        }
    )
)
annotations_list.append(
    dict(
        x=np.median(scatter_plot_df_with_age.rating_count) + 180,
        y=0.8,
        xref=»x»,
        yref=»y»,
        text=f»Медианное значение: {round(np.median(scatter_plot_df_with_age.rating_count), 2)}»,
        showarrow=False,
        font={
            ‘family’:’Roboto, light’,
            ‘color’:’#666666′,
            ‘size’:12
        },
        textangle=-90
    )
)

Прибавим графику информативности: поделим пивоварни на 4 группы по сортам пива. В первой группе будут пивоварни, у которых менее 10 сортов пива, во второй 10 — 30 сортов, в третьей 30 — 50 и в четвертой те, у кого сортов более 50. Значения списка bucket_beer_count — размеры маркеров.


bucket_beer_count = []
for beer_count in scatter_plot_df_with_age.beer_count:
   if beer_count < 10:
       bucket_beer_count.append(7)
   elif 10 <= beer_count <= 30:
       bucket_beer_count.append(9)
   elif 31 <= beer_count <= 50:
       bucket_beer_count.append(11)
   else:
       bucket_beer_count.append(13)
scatter_plot_df_with_age['bucket_beer_count'] = bucket_beer_count
[/code_snippet]



<p>Следом поделим график ещё на четыре группы: теперь уже по возрасту.</p>



[code_snippet]
bucket_age = []
for age in scatter_plot_df_with_age.age_on_service:
   if age < 4:
       bucket_age.append(0)
   elif 4 <= age <= 6:
       bucket_age.append(1)
   elif 6 < age < 8:
       bucket_age.append(2)
   else:
       bucket_age.append(3)
scatter_plot_df_with_age['bucket_age'] = bucket_age
[/code_snippet]



<p>Разделим один DataFrame на четыре, чтобы по каждому построить отдельный scatter plot со своим цветом и размером.</p>



[code_snippet]
scatter_plot_df_0 = scatter_plot_df[scatter_plot_df.bucket == 0]
scatter_plot_df_1 = scatter_plot_df[scatter_plot_df.bucket == 1]
scatter_plot_df_2 = scatter_plot_df[scatter_plot_df.bucket == 2]
scatter_plot_df_3 = scatter_plot_df[scatter_plot_df.bucket == 3]

Описываем график

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


fig = go.Figure()
fig.add_trace(go.Scatter(
    x=scatter_plot_df_0.rating_count,
    y=scatter_plot_df_0.brewery_pure_average,
    name=’< 4',
    mode='markers',
    opacity=0.85,
    text=scatter_plot_df_0.name_count,
    marker_color='rgb(114, 183, 178)',
    marker_size=scatter_plot_df_0.bucket_beer_count,
    textfont={"family":"Roboto, light",
              "color":"black"
             }
))

fig.add_trace(go.Scatter(
    x=scatter_plot_df_1.rating_count,
    y=scatter_plot_df_1.brewery_pure_average,
    name='4 – 6',
    mode='markers',
    opacity=0.85,
    marker_color='rgb(76, 120, 168)',
    text=scatter_plot_df_1.name_count,
    marker_size=scatter_plot_df_1.bucket_beer_count,
    textfont={"family":"Roboto, light",
              "color":"black"
             }
))

fig.add_trace(go.Scatter(
    x=scatter_plot_df_2.rating_count,
    y=scatter_plot_df_2.brewery_pure_average,
    name='6 – 8',
    mode='markers',
    opacity=0.85,
    marker_color='rgb(245, 133, 23)',
    text=scatter_plot_df_2.name_count,
    marker_size=scatter_plot_df_2.bucket_beer_count,
    textfont={"family":"Roboto, light",
              "color":"black"
             }
))

fig.add_trace(go.Scatter(
    x=scatter_plot_df_3.rating_count,
    y=scatter_plot_df_3.brewery_pure_average,
    name='8+',
    mode='markers',
    opacity=0.85,
    marker_color='rgb(228, 87, 86)',
    text=scatter_plot_df_3.name_count,
    marker_size=scatter_plot_df_3.bucket_beer_count,
    textfont={"family":"Roboto, light",
              "color":"black"
             }
))

fig.update_layout(
    title=f"Отношение количества отзывов к средней оценке пивоварни<br>за последние {n_days} дней, топ-{top_n} пивоварен»,
    font={
            ‘family’:’Roboto, light’,
            ‘color’:’black’,
            ‘size’:14
        },
    plot_bgcolor=’rgba(0,0,0,0)’,
    yaxis_title=»Средняя оценка»,
    xaxis_title=»Количество отзывов»,
    legend_title_text=’Возраст пивоварни<br>на Untappd, лет:’,
    height=750,
    shapes=dict_list,
    annotations=annotations_list
)

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

Код функции get_scatter_plot:


def get_scatter_plot(n_days, top_n):
    brewery_pure_average = client.execute(f»’
    SELECT 
        t1.brewery_id, 
        sum(t1.beer_pure_average_mult_count / t2.count_for_that_brewery) AS brewery_pure_average, 
        t2.count_for_that_brewery, 
        dictGet(‘breweries’, ‘brewery_name’, toUInt64(t1.brewery_id)), 
        dictGet(‘breweries’, ‘beer_count’, toUInt64(t1.brewery_id)),
        t3.stats_age_on_service / 365

    FROM 
    (
        SELECT 
            beer_id, 
            brewery_id, 
            sum(rating_score) AS beer_pure_average_mult_count
        FROM beer_reviews
        WHERE created_at >= today()-{n_days}
        GROUP BY 
            beer_id, 
            brewery_id
    ) AS t1
    ANY LEFT JOIN 
    (
        SELECT 
            brewery_id, 
            count(rating_score) AS count_for_that_brewery
        FROM beer_reviews
        WHERE created_at >= today()-{n_days}
        GROUP BY brewery_id
    ) AS t2 ON t1.brewery_id = t2.brewery_id
    ANY LEFT JOIN
    (
        SELECT
            brewery_id,
            stats_age_on_service
        FROM brewery_info_new
    ) AS t3 ON t1.brewery_id = t3.brewery_id
    GROUP BY 
        t1.brewery_id, 
        t2.count_for_that_brewery,
        t3.stats_age_on_service
    HAVING t2.count_for_that_brewery >= 150
    ORDER BY brewery_pure_average
    LIMIT {top_n}
    »’)

    scatter_plot_df_with_age = pd.DataFrame(brewery_pure_average, columns=[‘brewery_id’, ‘brewery_pure_average’, ‘rating_count’, ‘brewery_name’, ‘beer_count’, ‘age_on_service’])
    brewery_name_and_beer_count = []
    for name, beer_count in zip(scatter_plot_df_with_age.brewery_name, scatter_plot_df_with_age.beer_count):
        brewery_name_and_beer_count.append(f'{name},<br>количество сортов пива: {beer_count}’)
    scatter_plot_df_with_age[‘name_count’] = brewery_name_and_beer_count
    dict_list = []
    dict_list.append(dict(type=»line»,
        line=dict(
             color=»#666666″,
             dash=»dot»),
        x0=0,
        y0=np.median(scatter_plot_df_with_age.brewery_pure_average),
        x1=9000,
        y1=np.median(scatter_plot_df_with_age.brewery_pure_average),
        line_width=1,
        layer=»below»))
    dict_list.append(dict(type=»line»,
        line=dict(
             color=»#666666″,
             dash=»dot»),
        x0=np.median(scatter_plot_df_with_age.rating_count),
        y0=0,
        x1=np.median(scatter_plot_df_with_age.rating_count),
        y1=5,
        line_width=1,
        layer=»below»))
    annotations_list = []
    annotations_list.append(
        dict(
            x=8000,
            y=np.median(scatter_plot_df_with_age.brewery_pure_average) — 0.1,
            xref=»x»,
            yref=»y»,
            text=f»Медианное значение: {round(np.median(scatter_plot_df_with_age.brewery_pure_average), 2)}»,
            showarrow=False,
            font={
                ‘family’:’Roboto, light’,
                ‘color’:’#666666′,
                ‘size’:12
            }
        )
    )
    annotations_list.append(
        dict(
            x=np.median(scatter_plot_df_with_age.rating_count) + 180,
            y=0.8,
            xref=»x»,
            yref=»y»,
            text=f»Медианное значение: {round(np.median(scatter_plot_df_with_age.rating_count), 2)}»,
            showarrow=False,
            font={
                ‘family’:’Roboto, light’,
                ‘color’:’#666666′,
                ‘size’:12
            },
            textangle=-90
        )
    )
    bucket = []
    for beer_count in scatter_plot_df_with_age.beer_count:
        if beer_count < 10:
            bucket.append(7)
        elif 10 <= beer_count <= 30:
            bucket.append(9)
        elif 31 <= beer_count <= 50:
            bucket.append(11)
        else:
            bucket.append(13)
    scatter_plot_df_with_age['bucket_beer_count'] = bucket
    bucket_age = []
    for age in scatter_plot_df_with_age.age_on_service:
        if age < 4:
            bucket_age.append(0)
        elif 4 <= age <= 6:
            bucket_age.append(1)
        elif 6 < age < 8:
            bucket_age.append(2)
        else:
            bucket_age.append(3)
    scatter_plot_df_with_age['bucket_age'] = bucket_age
    scatter_plot_df_0 = scatter_plot_df_with_age[(scatter_plot_df_with_age.bucket_age == 0)]
    scatter_plot_df_1 = scatter_plot_df_with_age[scatter_plot_df_with_age.bucket_age == 1]
    scatter_plot_df_2 = scatter_plot_df_with_age[scatter_plot_df_with_age.bucket_age == 2]
    scatter_plot_df_3 = scatter_plot_df_with_age[scatter_plot_df_with_age.bucket_age == 3]
    
    fig = go.Figure()
    fig.add_trace(go.Scatter(
        x=scatter_plot_df_0.rating_count,
        y=scatter_plot_df_0.brewery_pure_average,
        name='< 4',
        mode='markers',
        opacity=0.85,
        text=scatter_plot_df_0.name_count,
        marker_color='rgb(114, 183, 178)',
        marker_size=scatter_plot_df_0.bucket_beer_count,
        textfont={"family":"Roboto, light",
                  "color":"black"
                 }
    ))

    fig.add_trace(go.Scatter(
        x=scatter_plot_df_1.rating_count,
        y=scatter_plot_df_1.brewery_pure_average,
        name='4 – 6',
        mode='markers',
        opacity=0.85,
        marker_color='rgb(76, 120, 168)',
        text=scatter_plot_df_1.name_count,
        marker_size=scatter_plot_df_1.bucket_beer_count,
        textfont={"family":"Roboto, light",
                  "color":"black"
                 }
    ))

    fig.add_trace(go.Scatter(
        x=scatter_plot_df_2.rating_count,
        y=scatter_plot_df_2.brewery_pure_average,
        name='6 – 8',
        mode='markers',
        opacity=0.85,
        marker_color='rgb(245, 133, 23)',
        text=scatter_plot_df_2.name_count,
        marker_size=scatter_plot_df_2.bucket_beer_count,
        textfont={"family":"Roboto, light",
                  "color":"black"
                 }
    ))

    fig.add_trace(go.Scatter(
        x=scatter_plot_df_3.rating_count,
        y=scatter_plot_df_3.brewery_pure_average,
        name='8+',
        mode='markers',
        opacity=0.85,
        marker_color='rgb(228, 87, 86)',
        text=scatter_plot_df_3.name_count,
        marker_size=scatter_plot_df_3.bucket_beer_count,
        textfont={"family":"Roboto, light",
                  "color":"black"
                 }
    ))

    fig.update_layout(
        title=f"Отношение количества отзывов к средней оценке пивоварни<br>за последние {n_days} дней, топ-{top_n} пивоварен»,
        font={
                ‘family’:’Roboto, light’,
                ‘color’:’black’,
                ‘size’:14
            },
        plot_bgcolor=’rgba(0,0,0,0)’,
        yaxis_title=»Средняя оценка»,
        xaxis_title=»Количество отзывов»,
        legend_title_text=’Возраст пивоварни<br>на Untappd, лет:’,
        height=750,
        shapes=dict_list,
        annotations=annotations_list
    )
    fig.show()
    return fig

239 просмотров

Добавить комментарий

Имя

Сообщение

[ Рекомендации ]

[ Связаться ]

Давайте раскроем потенциал вашего бизнеса вместе

Заполните форму на бесплатную консультацию

Ваше имя

Название компании

Бюджет

10-20 млн

20-50 млн

от 50 млн

Откуда вы узнали о нас?

Рекомендации

СМИ

Соцсети

Другое

Опишите задачу

Строим scatter plot по пивоварням Untappd

Пишем запрос к Clickhouse

Обрабатываем значения из DataFrame

Описываем график

Комментарии

Добавить комментарий

[ Рекомендации ]

[ Связаться ]

[ Дальше ]

Строим scatter plot по пивоварням Untappd

Пишем запрос к Clickhouse

Обрабатываем значения из DataFrame

Описываем график

Комментарии

Добавить комментарий

Подтвердите, что вы не робот

[ Рекомендации ]

[ Связаться ]

[ Дальше ]