zen_dash

#!/usr/bin/python
# -*- coding: utf-8 -*-

import dash
import dash_core_components as dcc
import dash_html_components as html
from dash.dependencies import Input, Output
from datetime import datetime as dt

import plotly.graph_objs as go

import pandas as pd

from sqlalchemy import create_engine

engine = create_engine('sqlite:////db/zen.db', echo = False)

db_config = {'user': 'my_user',
            'pwd': 'my_user_password',
            'host': 'localhost',
            'port': 5432,
            'db': 'zen'}
engine = create_engine('postgresql://{}:{}@{}:{}/{}'.format(db_config['user'],
                                                           db_config['pwd'],
                                                           db_config['host'],
                                                           db_config['port'],
                                                           db_config['db']))

# получаем сырые данные
query = '''
            SELECT * FROM dash_visits
        '''
dash_visits = pd.io.sql.read_sql(query, con = engine)
dash_visits['dt'] = pd.to_datetime(dash_visits['dt'])

query = '''
            SELECT * FROM dash_engagement
        '''
dash_engagement = pd.io.sql.read_sql(query, con = engine)
dash_engagement['dt'] = pd.to_datetime(dash_engagement['dt'])

note = '''
            Этот дашборд показывает анализ взаимодествия пользователей с карточками
            статей в Яндекс.Дзене.
            Используйте выбор интервала даты события, тем карточек и возрастной категории пользователей для управления дашбордом.
       '''

# задаём лейаут
external_stylesheets = ['https://codepen.io/chriddyp/pen/bWLwgP.css']
app = dash.Dash(__name__, external_stylesheets = external_stylesheets, compress=False)
app.layout = html.Div(children=[

    # заголовок
    html.H1(children = 'Анализ взаимодействия пользователей в Яндекс.Дзене'),

    html.Br(),
    html.Label(note),

    html.Br(),

    html.Div([

        html.Div([
            # выбор временного периода
            html.Label('Временной период:'),
            dcc.DatePickerRange(
                start_date = dt(2019,9,24).strftime('%Y-%m-%d'),
                end_date = dt(2019,9,25).strftime('%Y-%m-%d'),
                display_format = 'YYYY-MM-DD',
                id = 'dt_selector',
            ),
        ], className = 'six columns'),

        html.Div([
            # выбор возрастной категории
            html.Label('Возрастные категории:'),
            dcc.Dropdown(
                options = [{'label': x, 'value': x} for x in dash_visits['age_segment'].unique()],
                value = dash_visits['age_segment'].unique().tolist(),
                multi = True,
                id = 'age_dropdown'
            ),
            # выбор темы карточек
            html.Label('Темы карточек:'),
            dcc.Dropdown(
                options = [{'label': x, 'value': x} for x in dash_visits['item_topic'].unique()],
                value = dash_visits['item_topic'].unique().tolist(),
                multi = True,
                id = 'item_topic_dropdown'
            ),
        ], className = 'six columns'),

    ], className = 'row'),

    html.Div([
        html.Div([
            html.Label('История событий по темам карточек'),
            dcc.Graph(
                id = 'history_absolute_visits',
                style = {'height': '50vw'},
                ),
            ], className = 'six columns'),

        html.Div([
            html.Label('События по темам источников'),
            dcc.Graph(
                id = 'pie_visits',
                style = {'height': '25vw'},
            ),
            html.Label('Средняя глубина взаимодействия'),
            dcc.Graph(
                id = 'engagement_graph',
                style = {'height': '25vw'},
            ),
        ], className = 'six columns')
    ], className = 'row')
])

# описываем логику дашборда
@app.callback(
    [
        Output('history_absolute_visits', 'figure'),
        Output('pie_visits', 'figure'),
        Output('engagement_graph', 'figure')],
    [
        Input('dt_selector', 'start_date'),
        Input('dt_selector', 'end_date'),
        Input('age_dropdown', 'value'),
        Input('item_topic_dropdown', 'value'),
    ])

def update_figures(start_date, end_date, selected_ages, selected_item_topics):

    start_date = dt.strptime(start_date, '%Y-%m-%d')
    end_date = dt.strptime(end_date, '%Y-%m-%d')

    # применяем фильтрацию
    visits_filtered = dash_visits.query('item_topic in @selected_item_topics')
    visits_filtered = visits_filtered.query('dt >= @start_date and dt <= @end_date')
    visits_filtered = visits_filtered.query('age_segment in @selected_ages')


    engagement_filtered = dash_engagement.query('item_topic in @selected_item_topics')
    engagement_filtered = engagement_filtered.query('dt >= @start_date and dt <= @end_date')
    engagement_filtered = engagement_filtered.query('age_segment in @selected_ages')

    # данные для графиков
    visits_by_item_topic = (visits_filtered.groupby(['item_topic','dt'])
                                           .agg({'visits' : 'sum'})
                                           .sort_values(by = 'visits', ascending = False)
                                           .reset_index())

    visits_by_source_topic = (visits_filtered.groupby('source_topic')
                                             .agg({'visits' : 'sum'})
                                             .reset_index())

    engagement = (engagement_filtered.groupby('event')
                                     .agg({'unique_users' : 'mean'})
                                     .rename(columns = {'unique_users' : 'avg_unique_users'})
                                     .sort_values(by = 'avg_unique_users', ascending = False)
                                     .reset_index())

    data_item_topic_dropdown = []

    # график истории событий по темам карточек
    for item in visits_by_item_topic['item_topic'].unique():
        current = visits_by_item_topic[visits_by_item_topic['item_topic'] == item]
        data_item_topic_dropdown += [go.Scatter(x = current['dt'],
                                   y = current['visits'],
                                   mode = 'lines',
                                   stackgroup = 'one',
                                   name = item)
                        ]

    # график разбивки событий по темам источников
    data_pie_source_topic = [go.Pie(labels = visits_by_source_topic['source_topic'],
                                    values = visits_by_source_topic['visits'])

                        ]

    # график средней глубины взаимодействия
    data_engagement_graph = [go.Bar(x = engagement['event'],
                                   y = engagement['avg_unique_users'],
                                   name = 'Среднее количество пользователей')
                        ]



    return (
            #график item-topic
            {
                'data': data_item_topic_dropdown,
                'layout': go.Layout(xaxis = {'title': 'Время'},
                                    yaxis = {'title': 'Количество событий'})
            },

            #график pie-visits
            {
                'data': data_pie_source_topic,
                'layout': go.Layout()
            },

            #график engagement-graph
            {
                'data': data_engagement_graph,
                'layout': go.Layout(xaxis = {'title': 'Тип cобытия'},
                                    yaxis = {'title': 'Среднее число пользователей'},
                                    hovermode = 'closest')
             },
         )

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