Bokeh：无法显示具有月份轴的数据

huangapple
go评论94阅读模式
英文:

Bokeh : unable to display data with month axis

问题

我使用SQL和Bokeh进行数据可视化。以下是代码的翻译部分：

  1. # 工作正常的情况
  3. import psycopg2
  4. from bokeh.plotting import figure, show
  5. import numpy as np
  6. from bokeh.palettes import Category10_10 as palette
  7. import itertools
  9. # 数据库连接
  11. conn = psycopg2.connect(database="data", user="postgres", password="privatepassword", host="localhost", port="5432")
  12. cursor = conn.cursor()
  13. cursor.execute("SELECT date_of_search, job_search, COUNT(*) FROM occurrences GROUP BY date_of_search, job_search ORDER BY date_of_search DESC")
  14. results = cursor.fetchall()
  15. cursor.close()
  16. conn.close()
  18. # 数据准备
  20. dates = [np.datetime64(row[0]) for row in results]
  21. job_searches = [row[1] for row in results]
  22. counts = [row[2] for row in results]
  24. # 图表选项
  26. plot = figure(x_axis_type="datetime", title="occurrences evolution over time",
  27.               x_axis_label="date", y_axis_label="occurrences",
  28.               sizing_mode="stretch_width",
  29.               height=700)
  31. # 尝试颜色调色板
  32. colors = itertools.cycle(palette) 
  34. for job_search in list(set(job_searches)):
  35.     job_dates = [date for date, job, count in zip(dates, job_searches, counts) if job == job_search]
  36.     job_counts = [count for date, job, count in zip(dates, job_searches, counts) if job == job_search]
  37.     plot.line(job_dates, job_counts, line_width=3, legend_label=job_search, color=next(colors))

这是工作正常的情况下的代码部分。

以下是不工作情况下的代码翻译：

  1. # 不工作的情况
  3. import psycopg2
  4. from bokeh.plotting import figure, show
  5. from bokeh.palettes import Category10_10 as palette
  6. import numpy as np
  7. import itertools
  9. # 数据库连接
  11. conn = psycopg2.connect(database="data", user="postgres", password="privatepassword", host="localhost", port="5432")
  12. cursor = conn.cursor()
  13. cursor.execute("""
  14.                SELECT DATE(DATE_TRUNC('month', date_of_search)), job_search, COUNT(*) 
  15.                FROM occurrences
  16.                WHERE DATE_TRUNC('month', date_of_search) != DATE_TRUNC('month', current_date)
  17.                GROUP BY DATE_TRUNC('month', date_of_search), job_search 
  18.                ORDER BY DATE_TRUNC('month', date_of_search) DESC
  19.                """)
  20. results = cursor.fetchall()
  21. cursor.close()
  22. conn.close()
  24. # 数据准备
  26. dates = [np.datetime64(row[0]) for row in results]
  27. job_searches = [row[1] for row in results]
  28. counts = [row[2] for row in results]
  30. # 图表选项
  32. plot = figure(x_axis_type="datetime", title="occurrences evolution over time",
  33.               x_axis_label="date", y_axis_label="occurrences",
  34.               sizing_mode="stretch_width",
  35.               height=700)
  37. # 尝试颜色调色板
  38. colors = itertools.cycle(palette) 
  40. for job_search in list(set(job_searches)):
  41.     job_dates = [date for date, job, count in zip(dates, job_searches, counts) if job == job_search]
  42.     job_counts = [count for date, job, count in zip(dates, job_searches, counts) if job == job_search]
  43.     plot.line(job_dates, job_counts, line_width=3, legend_label=job_search, color=next(colors))

这是不工作的情况下的代码翻译部分。

英文:

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:

  1. # Working case
  3. import psycopg2
  4. from bokeh.plotting import figure, show
  5. import numpy as np
  6. from bokeh.palettes import Category10_10 as palette
  7. import itertools
  9. # Database connexion
  11. conn = psycopg2.connect(database="data", user="postgres", password="privatepassword", host="localhost", port="5432")
  12. cursor = conn.cursor()
  13. cursor.execute("SELECT date_of_search, job_search, COUNT(*) FROM occurrences GROUP BY date_of_search, job_search ORDER BY date_of_search DESC")
  14. results = cursor.fetchall()
  15. cursor.close()
  16. conn.close()
  18. # Data preparation
  20. dates = [np.datetime64(row[0]) for row in results]
  21. job_searches = [row[1] for row in results]
  22. counts = [row[2] for row in results]
  24. # Chart options
  26. plot = figure(x_axis_type="datetime", title="occurrences evolution over time",
  27.               x_axis_label="date", y_axis_label="occurrences",
  28.               sizing_mode="stretch_width",
  29.               height=700)
  31. # Trying color palette
  32. colors = itertools.cycle(palette) 
  34. for job_search in list(set(job_searches)):
  35.     job_dates = [date for date, job, count in zip(dates, job_searches, counts) if job == job_search]
  36.     job_counts = [count for date, job, count in zip(dates, job_searches, counts) if job == job_search]
  37.     plot.line(job_dates, job_counts, line_width=3, legend_label=job_search, color=next(colors))

The result is perfectly working :

Bokeh：无法显示具有月份轴的数据

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]') :

  1. 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:

  1. # Not working case
  3. import psycopg2
  4. from bokeh.plotting import figure, show
  5. from bokeh.palettes import Category10_10 as palette
  6. import numpy as np
  7. import itertools
  9. # Database connexion
  11. conn = psycopg2.connect(database="data", user="postgres", password="privatepassword", host="localhost", port="5432")
  12. cursor = conn.cursor()
  13. cursor.execute("""
  14.                SELECT DATE(DATE_TRUNC('month', date_of_search)), job_search, COUNT(*) 
  15.                FROM occurrences
  16.                WHERE DATE_TRUNC('month', date_of_search) != DATE_TRUNC('month', current_date)
  17.                GROUP BY DATE_TRUNC('month', date_of_search), job_search 
  18.                ORDER BY DATE_TRUNC('month', date_of_search) DESC
  19.                 """)
  20. results = cursor.fetchall()
  21. cursor.close()
  22. conn.close()
  24. # Data preparation
  26. dates = [np.datetime64(row[0]) for row in results]
  27. job_searches = [row[1] for row in results]
  28. counts = [row[2] for row in results]
  29.    
  30. # Chart options
  33. plot = figure(x_axis_type="datetime", title="occurrences evolution over time",
  34.               x_axis_label="date", y_axis_label="occurrences",
  35.               sizing_mode="stretch_width",
  36.               height=700)
  38. # Trying color palette
  39. colors = itertools.cycle(palette) 
  41. for job_search in list(set(job_searches)):
  42.     job_dates = [date for date, job, count in zip(dates, job_searches, counts) if job == job_search]
  43.     job_counts = [count for date, job, count in zip(dates, job_searches, counts) if job == job_search]
  44.     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 :

Bokeh：无法显示具有月份轴的数据

Do you have any idea? I'm stuck Bokeh：无法显示具有月份轴的数据

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

答案1

得分: 0

OK, 我明白了。

只是因为只有一个月的数据... 我尝试添加一些七月的数据来检查，像这样：

  1. (SELECT DATE(DATE_TRUNC('month', date_of_search)), job_search, COUNT(*) 
  2.  FROM occurrences
  3.  GROUP BY DATE_TRUNC('month', date_of_search), job_search 
  4.  ORDER BY DATE_TRUNC('month', date_of_search) DESC)
  5. UNION
  6. (SELECT DATE('2023-07-01') AS date, '数据分析师' AS job_search, '487' AS count)
  7. UNION
  8. (SELECT DATE('2023-07-01') AS date, '数据工程师' AS job_search, '1202' AS count)

而且它正常工作了...

Bokeh：无法显示具有月份轴的数据

我的错，也许这个主题将来某天会帮助到别人。

英文:

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 :

  1. (SELECT DATE(DATE_TRUNC('month', date_of_search)), job_search, COUNT(*) 
  2.  FROM occurrences
  3.  GROUP BY DATE_TRUNC('month', date_of_search), job_search 
  4.  ORDER BY DATE_TRUNC('month', date_of_search) DESC)
  5. UNION
  6. (SELECT DATE('2023-07-01') AS date, 'Data Analyst' AS job_search, '487' AS count)
  7. UNION
  8. (SELECT DATE('2023-07-01') AS date, 'Data Engineer' AS job_search, '1202' AS count)

And it's working...

Bokeh：无法显示具有月份轴的数据

My bad, maybe the topic will help someone someday.

huangapple
  • 本文由 发表于 2023年7月11日 01:13:45
  • 转载请务必保留本文链接：https://go.coder-hub.com/76655937.html
  • bokeh
  • python
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定