python bokeh多系列柱狀圖(分頁 标簽)

實戰

import MySQLdb as mdb #加載MySQLdb
import pandas as pd #資料庫
from bokeh.models.widgets import Panel, Tabs #分頁展示
from bokeh.transform import dodge #多個直方圖拼接
from bokeh.core.properties import value #多個直方圖
from bokeh.models import ColumnDataSource #生成資料 字典 DataFrame
from bokeh.plotting import figure, show, output_file #bokeh基本插件
from bokeh.models import HoverTool #建立懸浮對象

server1=[]
s1901=[]
s1902=[]
s1903=[]
s1904=[]
s1905=[]
s1801=[]
s1802=[]
s1803=[]
s1804=[]
s1805=[]

conn = mdb.connect(host='1xxxxxxxxx8', port=xxxxxx, user='xxxxxx', passwd='xxxxxxxxx',db='superking_payment') #連結位址 庫名
cursor1 = conn.cursor() #建立遊标cursor()
sql1="select " \
"server_id,"\
"round(SUM(case when FROM_UNIXTIME(create_time/1000,'%y-%m')='19-01' then amount else 0 end),2)as '19-01', "\
"round(SUM(case when FROM_UNIXTIME(create_time/1000,'%y-%m')='19-02' then amount else 0 end),2)as '19-02', "\
"round(SUM(case when FROM_UNIXTIME(create_time/1000,'%y-%m')='19-03' then amount else 0 end),2)as '19-03', "\
"round(SUM(case when FROM_UNIXTIME(create_time/1000,'%y-%m')='19-04' then amount else 0 end),2)as '19-04', "\
"round(SUM(case when FROM_UNIXTIME(create_time/1000,'%y-%m')='19-05' then amount else 0 end),2)as '19-05', "\
"round(SUM(case when FROM_UNIXTIME(create_time/1000,'%y-%m')='18-01' then amount else 0 end),2)as '18-01', "\
"round(SUM(case when FROM_UNIXTIME(create_time/1000,'%y-%m')='18-02' then amount else 0 end),2)as '18-02', "\
"round(SUM(case when FROM_UNIXTIME(create_time/1000,'%y-%m')='18-03' then amount else 0 end),2)as '18-03', "\
"round(SUM(case when FROM_UNIXTIME(create_time/1000,'%y-%m')='18-04' then amount else 0 end),2)as '18-04', "\
"round(SUM(case when FROM_UNIXTIME(create_time/1000,'%y-%m')='18-05' then amount else 0 end),2)as '18-05' "\
"from superking_payment.success_record  "\
"where server_id != 0 "\
"GROUP BY server_id "\
#sql語句
cursor1.execute(sql1) #查詢資料 execute()用于執行一個資料庫的查詢指令
res = cursor1.fetchall() #擷取結果fetchall()擷取結果集中剩下的所有行

for i4 in range(len(res)):
    server1.append(str(res[i4][0])+'服')
for i3 in range(len(res)):
    s1902.append(res[i3][2])
for i in range(len(res)):
    s1901.append(res[i][1])
for i in range(len(res)):
    s1903.append(res[i][3])
for i in range(len(res)):
    s1904.append(res[i][4])
for i in range(len(res)):
    s1905.append(res[i][5])
for i in range(len(res)):
    s1801.append(res[i][6])
for i in range(len(res)):
    s1802.append(res[i][7])
for i in range(len(res)):
    s1803.append(res[i][8])
for i in range(len(res)):
    s1804.append(res[i][9])
for i in range(len(res)):
    s1805.append(res[i][10])

cursor1.close()     #關閉遊标close()
conn.close() #關閉連接配接close()'''

output_file("line.html")

a=pd.DataFrame({'1901':s1901,'1902':s1902,'1903':s1903,'1904':s1904,
                '1905':s1905,'1801':s1801,'1802':s1802,'1803':s1803,'1804':s1804,'1805':s1805},index=server1)
source = ColumnDataSource(a)
#生成資料，資料格式為dict; 專門把dataframe轉換為字典，然後再轉換為ColumnDataSource對象

serv=a.index.tolist()
tooltips_p = [ ("pay", "$y{0,0}")]
#建立懸浮對象 {0,0}取整數
p = figure(x_range=serv,y_range=(0,2500),plot_width=1200,plot_height=600,title='WAO_test_payment',tooltips=tooltips_p)
#X軸取range種類的伺服器id号 添加tooltips懸浮标簽
p.vbar(x=dodge('index',-0.50,range=p.x_range), top='1901',width=0.2,
       source=source,color="#9ACD32", legend=value('1901'))
p.vbar(x=dodge('index',-0.25,  range=p.x_range), top='1902', width=0.2, source=source,
       color="#9400D3", legend=value('1902'))
p.vbar(x=dodge('index', 0.00, range=p.x_range), top='1903', width=0.2, source=source,
       color="#DAA520", legend=value('1903'))
p.vbar(x=dodge('index', 0.25, range=p.x_range), top='1904', width=0.2, source=source,
       color="#FF6347", legend=value('1904'))
p.vbar(x=dodge('index', 0.50,range=p.x_range), top='1905', width=0.2, source=source,
       color="#778899", legend=value('1905'))
tab2 = Panel(child=p, title="19year")
#用dodge的方法把3個柱狀圖拼到了一起
#定義X軸的分類
#top條形圖高度：Y軸取值 / 0.25和width=0.2是柱狀圖之間的空隙間隔，都是0.2了就沒有空隙了

tooltips_p2 = [ ("pay", "$y{0,0}")]
p2 = figure(x_range=serv,y_range=(0,2500),plot_width=1200,plot_height=600,title='WAO_test_payment',tooltips=tooltips_p2)
p2.vbar(x=dodge('index',-0.50,range=p.x_range), top='1801',width=0.2,
       source=source,color="#9ACD32", legend=value('1801'))
p2.vbar(x=dodge('index',-0.25,  range=p.x_range), top='1802', width=0.2, source=source,
       color="#9400D3", legend=value('1802'))
p2.vbar(x=dodge('index', 0.00, range=p.x_range), top='1803', width=0.2, source=source,
       color="#DAA520", legend=value('1803'))
p2.vbar(x=dodge('index', 0.25, range=p.x_range), top='1804', width=0.2, source=source,
       color="#FF6347", legend=value('1804'))
p2.vbar(x=dodge('index', 0.50,range=p.x_range), top='1805', width=0.2, source=source,
       color="#778899", legend=value('1805'))
tab1 = Panel(child=p2, title="18year")

tabs = Tabs(tabs=[ tab1, tab2 ])
show(tabs)
           

解析：

for i4 in range(len(res)):
    server1.append(str(res[i4][0])+'服')
   
    摘出res資料中的每一列

tooltips_p = [ ("pay", "$y{0,0}")]
增加标簽 pay的值 以Y軸坐标數為準

serv=a.index.tolist()
把資料a中(DateFrame)的索引(index)打成一個清單 指派給serv

p = figure(x_range=serv,y_range=(0,2500),plot_width=1200,plot_height=600,title='WAO_test_payment',tooltips=tooltips_p)
定義圖像範圍/值 
x/y_range x/y軸的範圍值 
tooltips=tooltips_p 加載懸浮層坐标

#重點
    p.vbar(x=dodge('index',-0.50,range=p.x_range), top='1901',width=0.2,
           source=source,color="#9ACD32", legend=value('1901'))
    x=dodge中：
    'index'索引名稱
    -0.50 多個分類在X軸位置
    range X軸的範圍值
     0.25和width=0.2是柱狀圖之間的空隙間隔，都是0.2了就沒有空隙了
     source=source (  source = ColumnDataSource(a))ColumnDataSourcede化後的資料源
     color="#9400D3" 顔色
     legend=value('1901') 伺服器對應的付費金額 作為長度展示