Untitled

#!/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
import plotly.graph_objs as go
from datetime import datetime
import pandas as pd
# задаём данные для отрисовки
from sqlalchemy import create_engine
# пример подключения к базе данных для Postresql
# db_config = {'user': 'my_user',
#             'pwd': 'my_user_password',
#             'host': 'localhost',
#             'port': 5432,
#             'db': 'games'}
# engine = create_engine('postgresql://{}:{}@{}:{}/{}'.format(db_config['user'],
#                                                            db_config['pwd'],
#                                                            db_config['host'],
#                                                            db_config['port'],
#                                                            db_config['db']))
# пример подключения к базе данных для Sqlite
engine = create_engine('sqlite:////db/games.db', echo=False)

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

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.Label('Года выпуска:'),
        dcc.DatePickerRange(
            start_date=agg_games_year_genre_platform['year_of_release'].dt.date.min(),
            end_date=datetime(2016, 1, 1).strftime('%Y-%m-%d'),
            display_format='YYYY-MM-DD',
            id='dt_selector',
        ),
        html.Div(
            [
                html.Div(
                    [
                        html.Br(),
                        html.Label('Выпуск игр по жанрам:'),
                        dcc.Graph(
                            style={'height': '50vw'},
                            id='launches_by_genre',
                        ),
                    ],
                    className='six columns',
                ),
                html.Div(
                    [
                        html.Br(),
                        html.Label('Продажи по жанрам:'),
                        dcc.Graph(
                            style={'height': '50vw'},
                            id='sales_by_genre',
                        ),
                    ],
                    className='six columns',
                ),
            ],
            className='row',
        ),
    ]
)

@app.callback(
    [
        Output('launches_by_genre', 'figure'),
        Output('sales_by_genre', 'figure'),
    ],
    [
        Input('dt_selector', 'start_date'),
        Input('dt_selector', 'end_date'),
    ],
)
def update_figures(start_date, end_date):

    games_by_genre = (
        agg_games_year_genre_platform.groupby(['year_of_release', 'genre'])
        .agg({'games': 'sum'})
        .reset_index()
    )

    sales_by_genre = (
        agg_games_year_genre_platform.groupby(['genre'])
        .agg({'total_copies_sold': 'sum'})
        .reset_index()
    )

    sales_by_genre['percent'] = (
        sales_by_genre['total_copies_sold'] / sales_by_genre['total_copies_sold'].sum()
    )
    sales_by_genre.loc[sales_by_genre['percent'] < 0.05, 'genre'] = 'Другие'
    sales_by_genre = (
        sales_by_genre.groupby(['genre'])
        .agg({'total_copies_sold': 'sum'})
        .reset_index()
    )

    y_label = 'Выпущенные игры'

    launches_by_genre = []
    for genre in games_by_genre['genre'].unique():
        launches_by_genre += [
            go.Scatter(
                x=games_by_genre.query('genre == @genre')['year_of_release'],
                y=games_by_genre.query('genre == @genre')['games'],
                mode='lines',
                stackgroup='one',
                name=genre,
            )
        ]

    sales_by_genre = [
        go.Pie(
            labels=sales_by_genre['genre'],
            values=sales_by_genre['total_copies_sold'],
            name='platfroms',
        )
    ]

    return (
        {
            'data': launches_by_genre,
            'layout': go.Layout(
                xaxis={'title': 'Год'}, yaxis={'title': y_label}
            ),
        },
        {'data': sales_by_genre,
         'layout': go.Layout()},
    )


if __name__ == '__main__':
    app.run_server(host='0.0.0.0', port=3000)