.. DO NOT EDIT.
.. THIS FILE WAS AUTOMATICALLY GENERATED BY SPHINX-GALLERY.
.. TO MAKE CHANGES, EDIT THE SOURCE PYTHON FILE:
.. "auto_examples/line/plot_line_rick.py"
.. LINE NUMBERS ARE GIVEN BELOW.

.. only:: html

    .. note::
        :class: sphx-glr-download-link-note

        Click :ref:`here <sphx_glr_download_auto_examples_line_plot_line_rick.py>`
        to download the full example code

.. rst-class:: sphx-glr-example-title

.. _sphx_glr_auto_examples_line_plot_line_rick.py:


RICK Line Chart
==================

Example line chart from the RICK package, with an additional baseline plot and custom formatted x axis.

.. GENERATED FROM PYTHON SOURCE LINES 7-35

.. code-block:: default


    from sqlalchemy import create_engine
    import matplotlib.pyplot as plt
    import matplotlib as mpl
    import pandas as pd 
    import configparser
    from psycopg2 import connect
    import psycopg2.sql as pg
    import pandas.io.sql as pandasql
    import numpy as np 
    import datetime
    import math
    import rick
    import geopandas as gpd
    import os
    import shapely
    from shapely.geometry import Point
    os.environ["PROJ_LIB"]=r"C:\Users\rliu4\AppData\Local\Continuum\anaconda3\Library\share"
    import importlib
    import matplotlib.ticker as ticker
    import matplotlib.font_manager as font_manager


    CONFIG = configparser.ConfigParser()
    # CONFIG.read(r'C:\Users\rliu4\Documents\Python\config.cfg')
    CONFIG.read(r'/home/cnangini/db.cfg')
    dbset = CONFIG['DBSETTINGS']
    con = connect(**dbset)







.. GENERATED FROM PYTHON SOURCE LINES 36-40

Data Collection
----------------

This Section grabs and formats the data.

.. GENERATED FROM PYTHON SOURCE LINES 40-64

.. code-block:: default


    query='''
    WITH daily_ave AS (
       SELECT * FROM ptc.daily_trips
    ), total AS  (
    SELECT  extract(month from dt) as mon,
    extract(year from dt) as yr,

    CASE WHEN extract(year from dt) = 2018 AND extract(month from dt) IN (3) THEN 108321
    WHEN extract(year from dt) = 2018 AND extract(month from dt) IN (11) THEN 161733 ELSE
    avg(count)::integer END as count FROM daily_ave
    GROUP BY extract(month from dt), extract(year from dt)
    ORDER BY extract(year from dt), extract(month from dt)
    )

    SELECT 
    CASE WHEN mon = 1 THEN to_char(to_timestamp (mon::text, 'MM'), 'Mon')||' '||yr::text 
    WHEN mon = 9 AND yr = 2016 THEN to_char(to_timestamp (mon::text, 'MM'), 'Mon')||' '||yr::text 
    ELSE to_char(to_timestamp (mon::text, 'MM'), 'Mon')
    END AS period, 
    count FROM total
    '''
    total=pandasql.read_sql(query, con)








.. GENERATED FROM PYTHON SOURCE LINES 65-66

Gets the baseline data (to be graphed in grey)

.. GENERATED FROM PYTHON SOURCE LINES 66-89

.. code-block:: default

    query='''
    WITH daily_ave AS (
    SELECT * FROM ptc.daily_trips
    ), total AS  (
    SELECT  extract(month from dt) as mon,
    EXTRACT(year from dt) as yr,
    CASE WHEN extract(year from dt) = 2018 AND extract(month from dt) IN (3) THEN 108321
    WHEN extract(year from dt) = 2018 AND extract(month from dt) IN (11) THEN 161733 ELSE
    AVG(count)::integer END AS count FROM daily_ave
    GROUP BY EXTRACT(month from dt), EXTRACT(year from dt)
    ORDER BY EXTRACT(year from dt), EXTRACT(month from dt)
    )
    SELECT 
    CASE WHEN mon = 1 THEN to_char(to_timestamp (mon::text, 'MM'), 'Mon')||' '||yr::text 
    WHEN mon = 9 AND yr = 2016 THEN to_char(to_timestamp (mon::text, 'MM'), 'Mon')||' '||yr::text 
    ELSE to_char(to_timestamp (mon::text, 'MM'), 'Mon')
    END AS period, 
    count/2 AS count FROM total
    '''
    total2=pandasql.read_sql(query, con)

    fig, ax, props = rick.charts.line_chart(total['count'], 'Trips', 'Time', baseline = total2['count'])




.. image:: /auto_examples/line/images/sphx_glr_plot_line_rick_001.png
    :alt: plot line rick
    :class: sphx-glr-single-img





.. GENERATED FROM PYTHON SOURCE LINES 90-91

Adds annotations

.. GENERATED FROM PYTHON SOURCE LINES 91-93

.. code-block:: default

    fig.text(0.94, 0.96, '176,000', transform=ax.transAxes, wrap = True, fontsize=9, fontname = 'Libre Franklin',
             verticalalignment='top', ha = 'center', bbox=props, color = '#660159')




.. rst-class:: sphx-glr-script-out

 Out:

 .. code-block:: none


    Text(0.94, 0.96, '176,000')



.. GENERATED FROM PYTHON SOURCE LINES 94-95

Adds custom x axis

.. GENERATED FROM PYTHON SOURCE LINES 95-101

.. code-block:: default

    month_lst2 = ['Sept\n2016',  'Jan\n2017',  'May',  'Sept',
                   'Jan\n2018', 'May',  'Sept', 
                  'Jan\n2019','May',]
    plt.xticks(range(0,35,4), month_lst2, fontsize=9, fontname = 'Libre Franklin')





.. image:: /auto_examples/line/images/sphx_glr_plot_line_rick_002.png
    :alt: plot line rick
    :class: sphx-glr-single-img


.. rst-class:: sphx-glr-script-out

 Out:

 .. code-block:: none


    ([<matplotlib.axis.XTick object at 0x7efdced18e80>, <matplotlib.axis.XTick object at 0x7efdced18710>, <matplotlib.axis.XTick object at 0x7efdced18438>, <matplotlib.axis.XTick object at 0x7efdcecee2b0>, <matplotlib.axis.XTick object at 0x7efdcecee748>, <matplotlib.axis.XTick object at 0x7efdceceeac8>, <matplotlib.axis.XTick object at 0x7efdceceef60>, <matplotlib.axis.XTick object at 0x7efdcece44e0>, <matplotlib.axis.XTick object at 0x7efdcece49e8>], <a list of 9 Text xticklabel objects>)




.. rst-class:: sphx-glr-timing

   **Total running time of the script:** ( 0 minutes  0.255 seconds)


.. _sphx_glr_download_auto_examples_line_plot_line_rick.py:


.. only :: html

 .. container:: sphx-glr-footer
    :class: sphx-glr-footer-example



  .. container:: sphx-glr-download sphx-glr-download-python

     :download:`Download Python source code: plot_line_rick.py <plot_line_rick.py>`



  .. container:: sphx-glr-download sphx-glr-download-jupyter

     :download:`Download Jupyter notebook: plot_line_rick.ipynb <plot_line_rick.ipynb>`


.. only:: html

 .. rst-class:: sphx-glr-signature

    `Gallery generated by Sphinx-Gallery <https://sphinx-gallery.github.io>`_