postgresql - 面对关于 x 轴和 y 轴(标签和范围)的 Bokeh 图的多个问题

Question

我正在尝试实现类似 Bokeh 图的效果 movies例如，从 postgresql 数据库中获取数据。我几乎完成了所有事情，除了一件事，比如我的情节没有被渲染。

在我的场景中，当我尝试渲染绘图时，它成功地从数据库中获取记录并在日志中显示数据，但它没有渲染我的绘图。找到下面的截图:

此外，如果您注意到，它会显示已选择 2 个事件，并且它也没有渲染 x 轴和 y 轴标签及其范围。

这是我的代码:

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()

我从昨天开始尝试自己调试这个问题，但没有成功，所以请帮助我。谢谢。

Answer 1

关键问题是对于分类数据，您需要明确提供 x_range 和 y_range 作为分类因子。你的情况稍微复杂一些，因为你需要动态设置分类因素，另外你还有日期作为可能的 x/y 轴。

• 如果您根本不设置 x_range、y_range，则不会出现任何内容(参见例如 Nominal/categorical axis on a scatter plot)
• 如果您在图形实例化上设置 y_range、x_range 参数，它会自动配置正确的 x/y 标签。
• 如果你设置实例化后的 y_range, x_range 属性，还需要构造 x 和 y 轴刻度线。

我已经输入了一组样本数据，但由于我输入的日期，我破坏了过滤。请注意，您不能在分类轴中使用冒号，因此您必须在那里提出解决方案(也许选择日期或与数据库值相比轴的格式不同时创建日期时间轴)

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"