表嵌在圖裏面。
驗証之前匯入sqlite3的data是否正確,並發換葉問一次打10個的精神,這次畫10家的今年一到八月的股價。
複習之前的指令。
光電業挑十家知名公司,
import sqlite3
conn = sqlite3.connect('revenue.db')
conn.text_factory = str
c = conn.cursor()
for row in c.execute("SELECT distinct * FROM m_prc_new WHERE cpy in ('3149','2499','3008','2448','2475','3019','3356','3406','3584','6209')"):
print(row)
output:
('2448', '102', '1', 56.7, 49.2, 53.74, 111266.0, 13480459739.0, 250808419.0, 26.91)
('2448', '102', '2', 59.0, 53.5, 56.64, 44109.0, 5479853713.0, 96743000.0, 10.37)
------------------------------------------------------
('6209', '102', '7', 32.8, 29.85, 31.25, 11319.0, 654723498.0, 20948654.0, 12.88)
('6209', '102', '8', 31.2, 28.15, 29.8, 8228.0, 442854241.0, 14858041.0, 9.13)
第六個欄位是月平均單價
把資料換成適合畫圖的格式,
import datetime
import matplotlib.dates as md
x=[];y1=[];y2=[];y3=[];y4=[];y5=[];y6=[];y7=[];y8=[];y9=[];yA=[];
for row in c.execute("SELECT distinct * FROM m_prc_new WHERE cpy in ('3149','2499','3008','2448','2475','3019','3356','3406','3584','6209')"):
if row[0]=='3149':
y=int(row[1])+1911
m=int(row[2])
dt=datetime.datetime(y,m,1)
x.append(md.date2num(dt))
y1.append(float(row[5]))
if row[0]=='2499':
y2.append(float(row[5]))
if row[0]=='3008':
y3.append(float(row[5]))
if row[0]=='2448':
y4.append(float(row[5]))
if row[0]=='2475':
y5.append(float(row[5]))
if row[0]=='3019':
y6.append(float(row[5]))
if row[0]=='3356':
y7.append(float(row[5]))
if row[0]=='3406':
y8.append(float(row[5]))
if row[0]=='3584':
y9.append(float(row[5]))
if row[0]=='6209':
yA.append(float(row[5]))
x,y1,y2,y3,y4,y5,y6,y7,y8,y9,yA
output:
([734869.0,
734900.0,
734928.0,
734959.0,
734989.0,
735020.0,
735050.0,
735081.0],
[69.54, 71.11, 71.73, 66.26, 65.04, 55.44, 57.14, 56.63],
[27.68, 28.01, 29.3, 28.53, 28.88, 27.79, 25.49, 23.19],
[755.13, 807.42, 770.57, 749.72, 908.11, 967.25, 953.93, 1054.1],
[53.74, 56.64, 56.9, 51.02, 56.37, 56.33, 53.04, 49.96],
[0.96, 0.94, 1.11, 1.65, 2.0, 1.87, 1.66, 1.58],
[29.23, 30.22, 31.18, 28.64, 33.0, 33.22, 32.8, 30.66],
[120.81, 125.23, 127.58, 134.54, 133.89, 134.09, 147.42, 158.77],
[203.95, 207.04, 190.68, 171.67, 187.26, 184.37, 172.77, 169.05],
[23.59, 25.41, 29.55, 29.89, 34.22, 34.8, 30.68, 26.58],
[35.08, 36.97, 38.32, 35.57, 36.48, 34.19, 31.25, 29.8])
因為資料是排序的,筆者用這種比較直覺的寫法。把SQL的select result轉成List.
畫上圖與表
import matplotlib.pylab as plt
plt.figure()
ax=plt.gca()
ax.set_title('10 Companies')
ax.plot_date(x, y1, '-', label='3149 ')
ax.plot_date(x, y2, '-', label='2499 ')
ax.plot_date(x, y3, '-', label='3008 ')
ax.plot_date(x, y4, '-', label='2448 ')
ax.plot_date(x, y5, '-', label='2475 ')
ax.plot_date(x, y6, '-', label='3019 ')
ax.plot_date(x, y7, '-', label='3356 ')
ax.plot_date(x, y8, '-', label='3406 ')
ax.plot_date(x, y9, '-', label='3584 ')
ax.plot_date(x, yA, '-', label='6209 ')
#plt.autofmt_xdate()
legend = ax.legend(loc='upper left', shadow=True)
col_labels=['2013/01','2013/02','2013/03','2013/04','2013/05','2013/06','2013/07','2013/08']
row_labels=['3149','2499','3008','2448','2475','3019','3356','3406','3584','6209']
table_vals=[y1, y2, y3, y4, y5, y6, y7, y8, y9, yA]
# the rectangle is where I want to place the table
the_table = plt.table(cellText=table_vals,
colWidths = [0.15]*8,
rowLabels=row_labels,
colLabels=col_labels,
loc='bottom')
plt.subplots_adjust(left=0.1, bottom=-0.5)
plt.show()
花了不少時間,把表往下挪一點,就是挪不動,還得多了解其繪圖的原理。
加上中文
ax.set_title('10 家光電業公司本年一至八月均價',fontproperties=zhfont1)
plt.ylabel('單位(新台幣:元)',fontproperties=zhfont1)
效果呈現:
Title和縱軸中文說明。