In [1]:

    

import pandas
from vincent.colors import brews


    

In [2]:

    

from gasp.frompsql.pnd import sql_to_pandas
from gasp.pnd.joins import combine_dataframes


    

In [3]:

    

conParam = {
    "HOST" : "localhost", "PORT" : "5432", "PASSWORD" : "admin",
    "USER" : "postgres", "DATABASE" : "valcarris_v2"
}

DIAS = [
    '2018-01-08', '2018-01-09', '2018-01-10', '2018-01-11', '2018-01-12',
    '2018-01-15', '2018-01-16', '2018-01-17', '2018-01-18', '2018-01-19',
    '2018-01-22', '2018-01-23', '2018-01-24', '2018-01-25', '2018-01-26'
]

ROUTE = '727|3|DESC'

OUT_XLS = r'D:\TRENMO_JASP\CARRIS\validacoes\porlinha\distri_727_3_DESC.xlsx'


    

In [4]:

    

q = (
    "SELECT routes.*, CASE WHEN busentrance.nstops "
    "IS NULL THEN 0 ELSE busentrance.nstops END AS nstops "
    "FROM ("
        "SELECT fid_percurso, paragem, ordem FROM "
        "percursos_geom_v2 "
        "WHERE fid_percurso='{rou}'"
    ") AS routes LEFT JOIN ("
        "SELECT route_id, paragem, COUNT(route_id) AS nstops "
        "FROM validacoes_v9 WHERE dia='{day}' "
        "AND route_id='{rou}' "
        "GROUP BY route_id, paragem"
    ") AS busentrance "
    "ON routes.paragem = busentrance.paragem AND "
    "routes.fid_percurso = busentrance.route_id "
    "ORDER BY ordem"
)


    

In [5]:

    

dias_data = []
for d in DIAS:
    data = sql_to_pandas(conParam, q.format(rou=ROUTE, day=d))
    data.rename(columns={'nstops' : d}, inplace=True)
    
    dias_data.append(data)


    

In [6]:

    

main_df = dias_data[0]
main_df = combine_dataframes(main_df, dias_data[1:], 'paragem')


    

In [7]:

    

cols = list(main_df.columns.values)
graphCols = ['paragem'] + DIAS
colsPos = {cols[i] : i for i in range(len(cols)) if cols[i] in graphCols}


    

In [8]:

    

writer = pandas.ExcelWriter(OUT_XLS, engine='xlsxwriter')

main_df.to_excel(writer, sheet_name='sheet1')

workbook = writer.book

worksheet = writer.sheets['sheet1']


    

In [9]:

    

chart = workbook.add_chart({"type" : "column"})


    

In [10]:

    

df_rows = main_df.shape[0]
c = 1
colorRamp = "Spectral"
for d in DIAS:
    if c not in range(len(brews[colorRamp])):
        c = 1
        colorRamp = "Set3"
    
    chart.add_series({
        "name"       : ['sheet1', 0, colsPos[d] + 1],
        "categories" : ['sheet1', 1, colsPos['paragem'] +1, df_rows, colsPos['paragem']+1],
        "values"     : ['sheet1', 1, colsPos[d] + 1, df_rows, colsPos[d] + 1],
        'fill'       : {"color" : brews[colorRamp][c -1]},
        "gap"        : 300
    })
    c+=1


    

In [11]:

    

chart.set_y_axis({'major_gridlines': {'visible': False}})
worksheet.insert_chart('K2', chart)
writer.save()


    

In [12]:

    

"""
import plotly
import plotly.plotly as py
import plotly.graph_objs as go
plotly.tools.set_credentials_file(username='jasp', api_key='kPK6hyWDe46Gvq4T9tAg')"""


    

    
Out[12]:





"\nimport plotly\nimport plotly.plotly as py\nimport plotly.graph_objs as go\nplotly.tools.set_credentials_file(username='jasp', api_key='kPK6hyWDe46Gvq4T9tAg')"

In [13]:

    

"""def plot_bar_chart(TITLE, xData, yData, maxY=None):
    data = [go.Bar(
        x=xData, y=yData,
        marker=dict(color='rgb(158,202,225)', line=dict(
            color='rgb(8,48,107)', width=1.5
        )), opacity=0.6
    )]

    layout = go.Layout(title=TITLE, yaxis=dict(range=[0, maxY if maxY else max(yData)]))
    fig = go.Figure(data=data, layout=layout)
    
    return fig"""


    

    
Out[13]:





"def plot_bar_chart(TITLE, xData, yData, maxY=None):\n    data = [go.Bar(\n        x=xData, y=yData,\n        marker=dict(color='rgb(158,202,225)', line=dict(\n            color='rgb(8,48,107)', width=1.5\n        )), opacity=0.6\n    )]\n\n    layout = go.Layout(title=TITLE, yaxis=dict(range=[0, maxY if maxY else max(yData)]))\n    fig = go.Figure(data=data, layout=layout)\n    \n    return fig"