Skip to main content
 首页 » 编程设计

python之带有来自 postgres DB 的数据标记的 Bokeh 折线图

2025年02月15日9cyq1162

数据行看起来像:

date    name    val 
2017-02-05 a 600.0 
2017-02-05 b 345.0 
2017-02-05 c 679.0 
2017-02-05 d 0.0 
2017-02-05 e 66.0 
2017-02-05 f 0.0 
2017-02-05 g 156.0 
2017-03-05 a 634.0 
2017-03-05 b 0.0 
2017-03-05 c 2679.0 
2017-03-05 d 0.0 
2017-03-05 e 9266.0 
2017-03-05 f 0.0 
2017-03-05 g 56.0 

我想制作一个带有数据标记的折线图。 x 是日期,y 是 val,图例颜色将按“名称”分组

我写了一些代码如下,但它显示了一个空白页面。好像出了什么问题。我也不确定如何添加数据标记。从互联网上搜索,似乎应该合并折线图和圆图才能完成。

有人可以帮助修复错误并告诉我如何做吗?

from bokeh.charts import Line, show, output_file, TimeSeries,Scatter 
from bokeh.plotting import figure 
from bokeh.models import HoverTool 
import sqlalchemy as sa 
import pandas as pd 
 
 
# database credentials 
usr  = 'test' 
pswd = 'test' 
db = 'test' 
 
 
# create the connection to the database 
engine = sa.create_engine( 
    'postgresql://{0}:{2}@localhost:3552/{1}' \ 
    .format(usr,db,pswd) 
) 
 
 
query = "select date::text,name,round(size/1024/1024/1024) as val from test order by 1,2" 
 
# extract the data 
df = pd.read_sql_query(query, engine) 
 
output_file("example_chart.html") 
 
# create a line chart where each column of measures receives a unique color and dash style 
plot = figure(title="Example of a line chart", x_axis_label='Date', y_axis_label='size in GB') 
 
plot.line(x=df['date'], y=df['val'], color=df['name']) 
 
show (plot) 

请您参考如下方法:

由于我无法评论 Pablo 的回答,我将根据他的回答添加我的回答。 它对我来说也是空白的,问题来自时间戳,您需要确保在数据影响中使用 DatetimeIndex。

这是我从我的类(class)中摘录的代码(我不擅长编码,所以欢迎评论),它从 postgresql 中获取数据并绘制它:

def getColumn(self, column): 
    self.logger.info('Getting ' + column +' from DB...') 
    self.cur.execute("SELECT " + column + " FROM history") 
 
    return [i[0] for i in self.cur.fetchall()] 
 
def multiAccountsPlot(self): 
 
    #I sort the blank issue by making sure the dates format was the same as Pablo's sample 
    data = np.array([pd.DatetimeIndex([i.isoformat() for i in self.getColumn("timestamp")]), 
        self.getColumn("account"),  
        self.getColumn("amount")]) 
    df = pd.DataFrame(data=data.T,columns = ["date","account","amount"]) 
 
    p = bokeh.plotting.figure(width=800, height=350, x_axis_type="datetime") 
    dates = [df[df.account.isin([x])].date for x in df.account.unique()] 
    vals = [df[df.account.isin([x])].amount for x in df.account.unique()] 
 
    mypalette=Spectral11[0:len(df.account.unique())] 
    p.multi_line(xs=dates,ys=vals, line_color=mypalette) 
 
    bokeh.io.output_file("example_chart.html") 
    bokeh.io.show(p) 

谢谢Pablo的回答,对我帮助很大