Bokeh : unable to display data with month axis

Question

I use SQL and Bokeh for data visualization. Here's the context: I need to represent a linechart according to occurrences of words related to job names. The aim is a monthly granularity to follow an evolution.

I first tested a daily granularity with the following code:

# Working case

import psycopg2
from bokeh.plotting import figure, show
import numpy as np
from bokeh.palettes import Category10_10 as palette
import itertools

# Database connexion

conn = psycopg2.connect(database="data", user="postgres", password="privatepassword", host="localhost", port="5432")
cursor = conn.cursor()
cursor.execute("SELECT date_of_search, job_search, COUNT(*) FROM occurrences GROUP BY date_of_search, job_search ORDER BY date_of_search DESC")
results = cursor.fetchall()
cursor.close()
conn.close()

# Data preparation

dates = [np.datetime64(row[0]) for row in results]
job_searches = [row[1] for row in results]
counts = [row[2] for row in results]

# Chart options

plot = figure(x_axis_type="datetime", title="occurrences evolution over time",
              x_axis_label="date", y_axis_label="occurrences",
              sizing_mode="stretch_width",
              height=700)

# Trying color palette
colors = itertools.cycle(palette) 

for job_search in list(set(job_searches)):
    job_dates = [date for date, job, count in zip(dates, job_searches, counts) if job == job_search]
    job_counts = [count for date, job, count in zip(dates, job_searches, counts) if job == job_search]
    plot.line(job_dates, job_counts, line_width=3, legend_label=job_search, color=next(colors))

The result is perfectly working :

So I tried changing the granularity in my SQL query, but the X-axis is unusable. I tried to change the date extraction in the SQL query in various ways, I tried to convert the month of the X axis via .astype('datetime64[M]') :

job_months = np.unique([date.astype('datetime64[M]') for date, job, count in zip(dates, job_searches, counts) if job == job_search])

Unfortunately, nothing works. Here's the (almost identical) non-functional code:

# Not working case

import psycopg2
from bokeh.plotting import figure, show
from bokeh.palettes import Category10_10 as palette
import numpy as np
import itertools

# Database connexion

conn = psycopg2.connect(database="data", user="postgres", password="privatepassword", host="localhost", port="5432")
cursor = conn.cursor()
cursor.execute("""
               SELECT DATE(DATE_TRUNC('month', date_of_search)), job_search, COUNT(*) 
               FROM occurrences
               WHERE DATE_TRUNC('month', date_of_search) != DATE_TRUNC('month', current_date)
               GROUP BY DATE_TRUNC('month', date_of_search), job_search 
               ORDER BY DATE_TRUNC('month', date_of_search) DESC
                """)
results = cursor.fetchall()
cursor.close()
conn.close()

# Data preparation

dates = [np.datetime64(row[0]) for row in results]
job_searches = [row[1] for row in results]
counts = [row[2] for row in results]
   
# Chart options


plot = figure(x_axis_type="datetime", title="occurrences evolution over time",
              x_axis_label="date", y_axis_label="occurrences",
              sizing_mode="stretch_width",
              height=700)

# Trying color palette
colors = itertools.cycle(palette) 

for job_search in list(set(job_searches)):
    job_dates = [date for date, job, count in zip(dates, job_searches, counts) if job == job_search]
    job_counts = [count for date, job, count in zip(dates, job_searches, counts) if job == job_search]
    plot.line(job_dates, job_counts, line_width=3, legend_label=job_search, color=next(colors))

This is the only result i'm able to have :

Do you have any idea? I'm stuck :(

The WHERE DATE_TRUNC('month', date_of_search) != DATE_TRUNC('month', current_date) in SQL statement is because I don't want the data of unfinished month (allways the current one).

Answer 1

OK, i figured out.

It's just because there is data for one month... I tried to add some data for July to check like this :

(SELECT DATE(DATE_TRUNC('month', date_of_search)), job_search, COUNT(*) 
 FROM occurrences
 GROUP BY DATE_TRUNC('month', date_of_search), job_search 
 ORDER BY DATE_TRUNC('month', date_of_search) DESC)
UNION
(SELECT DATE('2023-07-01') AS date, 'Data Analyst' AS job_search, '487' AS count)
UNION
(SELECT DATE('2023-07-01') AS date, 'Data Engineer' AS job_search, '1202' AS count)

And it's working...

My bad, maybe the topic will help someone someday.