postgresql - Facing multiple issues with bokeh plots regarding x-axis and y-axis(labels & range)

Question

I am trying to achieve something like bokeh plots movies example, fetching data from postgresql database. I have almost achieve everything, except one thing like my plot is not getting rendered.

Here in my scenario, when I try to render plot, it fetches records successfully from database and in log it shows data, but it is not rendering my plot. Find the below screenshot for that:

Also if you notice, it says 2 incident selected and it is also not rendering x-axis & y-axis labels and it's range.

This is my code:

from os.path import dirname, join
import sys
import numpy as np
import pandas.io.sql as psql

import psycopg2
from datetime import datetime
from bokeh.plotting import figure
from bokeh.layouts import layout, widgetbox
from bokeh.models import ColumnDataSource, HoverTool, Div
from bokeh.models.widgets import Slider, Select, TextInput, DatePicker, DateRangeSlider
from bokeh.io import curdoc

try:

    #Create a database session
    conn = psycopg2.connect(database='movies', user='postgres', password='postgres')

    #Create a client cursor to execute commands
    cursor = conn.cursor()

    #The variables placeholder must always be a %s, psycop2 will automatically convert the values to SQL literal
    query = "SELECT * FROM atrocities_details"
    movies = psql.read_sql(query, conn)

    movies["color"] = np.where(movies["gender"] == 'Male', "orange", "grey")
    movies["alpha"] = np.where(movies["gender"] == 'Male', 0.9, 0.25)
    movies.fillna(0, inplace=True)  # just replace missing values with zero

    axis_map = {
        "Date": "date_published",
        "State": "state",
        "Gender": "gender",
        "Religion": "religion",
        "Caste": "caste"
    }

    desc = Div(text=open(join(dirname(__file__), "description.html")).read(), width=800)

    # Create Input controls
    min_date = Slider(title="Date From", start=2000, end=2017, value=2010, step=1)
    max_date = Slider(title="Date To", start=2001, end=2018, value=2018, step=1)
    religion = Select(title="Religion", value="All",
                   options=open(join(dirname(__file__), 'religion.txt')).read().split())
    state = Select(title="State", value="All",
                   options=open(join(dirname(__file__), 'states.txt')).read().split())
    incident_name = TextInput(title="Incident name contains")
    x_axis = Select(title="X Axis", options=sorted(axis_map.keys()), value="Caste")
    y_axis = Select(title="Y Axis", options=sorted(axis_map.keys()), value="Date")

    # Create Column Data Source that will be used by the plot
    source = ColumnDataSource(data=dict(x=[], y=[], color=[], title=[], alpha=[]))

    hover = HoverTool(tooltips=[
        ("Title", "@title")
    ])

    p = figure(plot_height=500, plot_width=500, title="", toolbar_location=None, tools=[hover])
    p.circle(x="x", y="y", source=source, size=7, color="color", line_color=None, fill_alpha="alpha")

    def select_movies():
        religion_val = religion.value
        state_val = state.value
        incident_name_val = incident_name.value.strip()

        # Compare form details with the database object that we created above and return the result
        selected = movies[
            (movies.date_published >= str(min_date.value) + "-01-01 00:00:00") &
            (movies.date_published <= str(max_date.value) + "-01-01 00:00:00")
        ]

        if (religion_val != "All"):
            selected = selected[selected.religion.str.contains(religion_val)==True]
        if (state_val != "All"):
            selected = selected[selected.state.str.contains(state_val)==True]
        if (incident_name_val != ""):
            selected = selected[selected.title.str.contains(incident_name_val)==True]
        return selected

    def update():
        df = select_movies()
        x_name = axis_map[x_axis.value]
        y_name = axis_map[y_axis.value]
        p.xaxis.axis_label = x_axis.value
        p.yaxis.axis_label = y_axis.value
        p.title.text = "%d incident selected" % len(df)
        source.data = dict(
            x=df[x_name],
            y=df[y_name],
            color=df["color"],
            title=df["title"],
            alpha=df["alpha"]
        )

    controls = [religion, min_date, max_date, state, incident_name, x_axis, y_axis]
    for control in controls:
        control.on_change('value', lambda attr, old, new: update())

    sizing_mode = 'fixed'  # 'scale_width' also looks nice with this example

    inputs = widgetbox(*controls, sizing_mode=sizing_mode)
    l = layout([
        [desc],
        [inputs, p],
    ], sizing_mode=sizing_mode)

    update()  # initial load of the data

    curdoc().add_root(l)
    curdoc().title = "Movies"

    #print(cursor.fetchone())
except psycopg2.DatabaseError as e:
    print ('Error %s' % e)
    sys.exit(1)
finally:
    if conn:
        conn.close()

I am trying to debug this issue on my own since yesterday but couldn't achieve the success, so kindly help me on it. Thanks.

Answer 1

Key issue is that for categorical data, you need to explicitly supply the x_range and y_range as the categorical factors. Your case is slightly more complex, since you need to dynamically set the categorical factors, and additionally you have dates as a possible x/y axis.

• If you do not set the x_range, y_range at all, nothing appears ( see for example, Nominal/categorical axis on a scatter plot)
• If you set the y_range, x_range arguments on a figure instantiation, it will automatically configure the correct x/y labels.
• If you set the y_range, x_range attrs after instantiation, you also need to construct the x and y axis tick marks.

I have put in a sample set of data, but I broke the filtering due to the dates I put in. Note that you cant have colons in a categorical axis , so you will have to come up with a solution there (maybe create date time axis when date is selected or different formatting for the axis compared to database values)

from os.path import dirname, join
import sys
import time
import numpy as np
import pandas.io.sql as psql
import pandas as pd
import psycopg2
from datetime import datetime
from bokeh.plotting import figure
from bokeh.layouts import layout, widgetbox
from bokeh.models import ColumnDataSource, HoverTool, Div, FactorRange, FuncTickFormatter, FixedTicker
from bokeh.models.widgets import Slider, Select, TextInput, DatePicker, DateRangeSlider
from bokeh.io import curdoc
gender=["Male","Male","Female","Female"]
religion=['r1','r2','r1','r3']
state = ['s1','s2','s1','s3']
date=['d1','d2','d3','d4']
caste = ['c1','c2','c3','c4']
title=['t1','t2','t3','t4']
data = {'gender':gender,'religion':religion,'state':state,'date_published':date,'caste':caste,'title':title}
movies = pd.DataFrame(data)

movies["color"] = np.where(movies["gender"] == 'Male', "orange", "grey")
movies["alpha"] = np.where(movies["gender"] == 'Male', 0.9, 0.25)
movies.fillna(0, inplace=True)  # just replace missing values with zero

axis_map = {
    "Date": "date_published",
    "State": "state",
    "Gender": "gender",
    "Religion": "religion",
    "Caste": "caste"
}

desc = Div(text="blah blah", width=800)

# Create Input controls
min_date = Slider(title="Date From", start=2000, end=2017, value=2010, step=1)
max_date = Slider(title="Date To", start=2001, end=2018, value=2018, step=1)
religion = Select(title="Religion", value="All",
               options=['r1','r2','r2'])
state = Select(title="State", value="All",
               options=['s1','s2','s3'])
incident_name = TextInput(title="Incident name contains")
x_axis = Select(title="X Axis", options=sorted(axis_map.keys()), value="Caste")
y_axis = Select(title="Y Axis", options=sorted(axis_map.keys()), value="Date")

# Create Column Data Source that will be used by the plot
source = ColumnDataSource(data=dict(x=[], y=[], color=[], title=[], alpha=[]))

hover = HoverTool(tooltips=[
    ("Title", "@title")
])

p = figure(plot_height=500, plot_width=500, title="", toolbar_location=None, tools=[hover])
p.circle(x="x", y="y", source=source, size=7, color="color", line_color=None, fill_alpha="alpha")


def select_movies():
    religion_val = religion.value
    state_val = state.value
    incident_name_val = incident_name.value.strip()

    # Compare form details with the database object that we created above and return the result
    selected = movies[
        (movies.date_published >= str(min_date.value) + "-01-01 00:00:00") &
        (movies.date_published <= str(max_date.value) + "-01-01 00:00:00")
    ]

    if (religion_val != "All"):
        selected = selected[selected.religion.str.contains(religion_val)==True]
    if (state_val != "All"):
        selected = selected[selected.state.str.contains(state_val)==True]
    if (incident_name_val != ""):
        selected = selected[selected.title.str.contains(incident_name_val)==True]
    # ignore filtering for example plotting.
    selected = movies
    return selected

def update():
    df = select_movies()
    x_name = axis_map[x_axis.value]
    y_name = axis_map[y_axis.value]
    p.xaxis.axis_label = x_axis.value
    p.yaxis.axis_label = y_axis.value
    p.title.text = "%d incident selected" % len(df)
    source.data = dict(
        x=df[x_name],
        y=df[y_name],
        color=df["color"],
        title=df["title"],
        alpha=df["alpha"]
    )
    xticker = FixedTicker(ticks=list(range(1,len(df[x_name])+1)))
    yticker = FixedTicker(ticks=list(range(1,len(df[y_name])+1)))
    # func tick formatter maps each tick defined by the x/y ticker, 
    # then maps this to the returned value which is displayed on the 
    #  plot
    yformatter = FuncTickFormatter(args={'source':source},code="""                        
          return source.data['y'][tick-1];
          """)
    xformatter = FuncTickFormatter(args={'source':source},code="""
          return source.data['x'][tick-1];
          """)
    p.x_range = FactorRange(factors=list(df[x_name].values),bounds='auto')
    p.y_range = FactorRange(factors=list(df[y_name].values),bounds='auto')
    p.renderers[0].ticker = xticker
    p.renderers[2].ticker = yticker
    p.renderers[0].formatter = xformatter
    p.renderers[2].formatter = yformatter

controls = [religion, min_date, max_date, state, incident_name, x_axis, y_axis]
for control in controls:
    control.on_change('value', lambda attr, old, new: update())

sizing_mode = 'fixed'  # 'scale_width' also looks nice with this example

inputs = widgetbox(*controls, sizing_mode=sizing_mode)
l = layout([
    [desc],
    [inputs, p],
], sizing_mode=sizing_mode)

update()  # initial load of the data

curdoc().add_root(l)
curdoc().title = "Movies"