# Python 資料視覺化開發
打包 pip install pyinstaller

指令 pyinstaller -F .\檔名.py

## Matplotlib

import numpy as np
import matplotlib.pyplot as plt
import pandas as pd
#100個學生的分數
data = [73, 71, 34, 85, 80, 100, 57, 78, 88, 98, 
        46, 34, 74, 86, 41, 54, 80, 40, 71, 46, 
        43, 51, 58, 61, 71, 70, 65, 39, 28, 62, 
        49, 89, 73, 38, 41, 51, 45, 64, 51, 34, 
        42, 58, 67, 56, 71, 45, 32, 42, 59, 98, 
        33, 64, 55, 67, 50, 45, 64, 63, 85, 39, 
        48, 62, 34, 67, 100, 58, 68, 34, 38, 70, 
        64, 74, 62, 73, 45, 17, 68, 26, 69, 56, 
        76, 59, 45, 95, 78, 77, 70, 59, 91, 79, 
        53, 78, 61, 84, 56, 96, 68, 64, 46, 70]
data=np.array(data)#將分數轉為np陣列
bins=np.linspace(np.ceil(min(data)),np.floor(max(data)),20)#等差級數
#hist也可以匯出直方圖
plt.hist(data,bins,color='b',alpha=0.5)
plt.xlim([0,100])
plt.xlabel('Score',size=14)
plt.ylabel('Quantity',size=14)
plt.title('Student Scores Distribution',size=20)
plt.show()


data=[5,36,6,14,28]
plt.bar(range(len(data)),data,color='rgb',ec='#000000',ls='--',lw=2)
plt.show()


https://colorhunt.co/
色碼的網站

### hatch→設置填充樣式
/,\\,|,-,+,x,o,.

data=[5,36,6,14,28]
plt.bar(range(len(data)),data,color='rgb',ec='#000000',ls='--',lw=2,hatch='.')
plt.show()


data=[5,20,15,25,10]
labels=['Tom','Dick','Harry','Slim','Jim']
plt.bar(range(len(data)),data,tick_label=labels)
plt.show()


tick_label是xlabel設置為str(更改xlabel的標示)

#bottom參數,可以繪製堆疊bar
size=5
x=np.arange(size)
a=np.random.random(size)
b=np.random.random(size)
plt.bar(x,a,label='a')
plt.bar(x,b,bottom=a,label='b')
plt.legend()
plt.show()


#3層
A=np.random.randint(2,15,5)
B=np.random.randint(2,15,5)
C=np.random.randint(2,15,5)
plt.bar(x,A,fc='r',ec='none')
plt.bar(x,B,fc='g',ec='none',bottom=A)
plt.bar(x,C,fc='b',ec='none',bottom=A+B)
plt.show()


data=[5,20,15,25,10]
plt.barh(range(len(data)),data,fc='#e43f5a',ec='#1f4068',lw=3,ls='--',hatch='\\')
plt.show()


a=np.array([5,20,15,25,10])
b=np.array([10,15,20,15,5])
plt.barh(range(len(a)),a)
plt.barh(range(len(b)),-b)
plt.show()


import numpy as np
import matplotlib.pyplot as plt
import pandas as pd
sale= np.array([22,22,17,21,22,19,21,19,20,15,24,19])
profit=np.array([12,15,11,17,11,12,11,10,16,16,13,11])
plt.title('Sales and Profit in one year',size=20)
plt.xlabel('Month',size=14)
plt.ylabel('Million Dollars',size=14)
plt.plot(range(len(sale)),sale,marker='o')
plt.plot(range(len(profit)),profit,marker='o')
plt.show()

## 佈景主題

import matplotlib.pyplot as plt

style_available=plt.style.available
print('可以使用{}個佈景主題。'.format(len(style_available)))
print(style_available)

可以使用26個佈景主題。
['Solarize_Light2', '_classic_test_patch', 'bmh', 'classic', 'dark_background', 'fast', 'fivethirtyeight', 'ggplot', 'grayscale', 'seaborn', 'seaborn-bright', 'seaborn-colorblind', 'seaborn-dark', 'seaborn-dark-palette', 'seaborn-darkgrid', 'seaborn-deep', 'seaborn-muted', 'seaborn-notebook', 'seaborn-paper', 'seaborn-pastel', 'seaborn-poster', 'seaborn-talk', 'seaborn-ticks', 'seaborn-white', 'seaborn-whitegrid', 'tableau-colorblind10']

import matplotlib.pyplot as plt
import pandas as pd

csv_url='https://storage.googleapis.com/ds_data_import/chicago_bulls_1995_1996.csv'
df=pd.read_csv(csv_url)
grouped=df.groupby('Pos')
pos=grouped['Pos'].count()
plt_themes=['Solarize_Light2','_classic_test_patch','bmh','dark_background','fast']
for i in range(5):
    plt.style.use(plt_themes[i])
    plt.bar(range(1,6),pos)
    plt.xticks(range(1,6),pos.index)
    plt.title(plt_themes[i])
    plt.show()

自己跑看看囉~

import matplotlib.pyplot as plt
import pandas as pd

csv_url='https://storage.googleapis.com/ds_data_import/chicago_bulls_1995_1996.csv'
df=pd.read_csv(csv_url)
grouped=df.groupby('Pos')
pos=grouped['Pos'].count()
plt_themes=['Solarize_Light2','_classic_test_patch','bmh','dark_background','fast']

plt.style.use(plt_themes[i])
plt.bar(range(1,6),pos)
plt.xticks(range(1,6),pos.index)
plt.suptitle(plt_themes[i])#置中標題
plt.title(plt_themes[i])
plt.show()


from matplotlib.font_manager import FontProperties
import pandas as pd
import matplotlib.pyplot as plt

csv_url='https://storage.googleapis.com/ds_data_import/chicago_bulls_1995_1996.csv'
df=pd.read_csv(csv_url)
grouped=df.groupby('Pos')
pos=grouped['Pos'].count()
#可以顯示中文
myfont=FontProperties(fname='C:\Windows\Fonts\msjhbd.ttf')
plt.bar(range(1,6),pos)
plt.xticks(range(1,6),['中鋒','大前鋒','小前鋒','控球後衛','得分後衛'],fontproperties=myfont,c='r')
plt.title('反映什為了抗衡其他具有主宰力的中前鋒的隊伍之現象',fontproperties=myfont,c='r')
plt.xlabel('鋒衛位置',fontproperties=myfont,c='r')
plt.ylabel('球員人數',fontproperties=myfont,c='r')
plt.show()


from matplotlib.font_manager import FontProperties
import pandas as pd
import matplotlib.pyplot as plt

per_game_url = "https://storage.googleapis.com/ds_data_import/stats_per_game_chicago_bulls_1995_1996.csv"
player_info_url = "https://storage.googleapis.com/ds_data_import/chicago_bulls_1995_1996.csv"
per_game=pd.read_csv(per_game_url)
player_info=pd.read_csv(player_info_url)
df=pd.merge(player_info,per_game[['Name','PTS/G']],left_on='Player',right_on='Name')
grouped=df.groupby('Pos')
point_per_game=grouped['PTS/G'].mean()
plt.bar([1,2,3,4,5],point_per_game)
plt.xticks([1,2,3,4,5],point_per_game.index)
plt.ylim(0,point_per_game.max()+3)
plt.title('Point per game by postions',c='r')
plt.xlabel('Positions',c='r')
plt.ylabel('PPG',c='r')
for i,v in enumerate(point_per_game):
    plt.text(i+0.9,v+0.5,"{:,.1f}".format(v))
plt.show()


import pandas as pd
import matplotlib.pyplot as plt
import numpy as np

fig,ax=plt.subplots()#繪圖方法
#繪製一條餘弦曲線
t=np.arange(0.0,5.0,0.01)
s=np.cos(2*np.pi*t)
line,=ax.plot(t,s,lw=2)
ax.annotate('lacal max',xy=(2,1),xytext=(3,1.5),xycoords='data',arrowprops=dict(facecolor='black',shrink=0.05))
ax.set_ylim(-2,2)
plt.show()


## 散佈圖scatter

mport numpy as np
import matplotlib.pyplot as plt

x=np.random.rand(50)*10
y=np.random.randint(1,101,50)
plt.xlabel('X',size=16)
plt.ylabel('Y',size=16)
plt.xticks(range(0,11))
plt.yticks(range(0,101,20))
plt.title('123')
plt.scatter(x,y,s=80,marker='^',c=np.random.rand(50),alpha=0.7)


import numpy as np 
import pandas as pd
import matplotlib.pyplot as plt
import csv

x=[]
y=[]

with open('csvfile2.csv') as file:
    data=list(csv.reader(file))
    for d in data[1:]:
        x.append(eval(d[0]))
        y.append(eval(d[1]))

plt.xlabel('x_value',size=14)
plt.ylabel('y_value',size=14)
plt.xticks(np.arange(-80,81,10))
plt.yticks(np.arange(-300,301,40))
plt.title('Scatter_csv',size=20)
plt.scatter(x,y,s=50,c=np.random.rand(100),marker='o',alpha=0.7)
plt.show() 

自己試 我試起來怪怪的

import matplotlib.pyplot as plt
import numpy as np

plt.style.use('bmh')
x=np.arange(0,100)
#圖一
plt.subplot(221)
plt.plot(x,x)
#圖二
plt.subplot(222)
plt.plot(x,x**2)
#圖三
plt.subplot(223)
plt.plot(x,x**3)
#圖四
plt.subplot(224)
plt.plot(x,np.log(x))


import matplotlib.pyplot as plt
import numpy as np

ax1=plt.subplot2grid((3,2),(0,0),rowspan=1,colspan=2)
ax2=plt.subplot2grid((3,2),(1,0),rowspan=2,colspan=1)
ax3=plt.subplot2grid((3,2),(1,1),rowspan=2,colspan=1)
ax1.plot([1,2,3],[4,5,6])
ax2.plot([1,2,3],[8,3,2])
ax3.plot([1,2,3],[4,4,4])


#雙軸
import matplotlib.pyplot as plt
import numpy as np

x1=[1,2,3,4,5]
y1=[60,70,80,60,50]
y2=[i *2.2 for i in y1]
ax1=plt.gca()
ax1.plot(x1,y1)
ax2=ax1.twinx()
ax2.plot(x1,y2,'go')
plt.title('Double Axes')
ax1.set_xlabel('People')
ax2.set_ylabel('KG')
ax1.set_ylabel('LBS')
plt.show()


scatter_mapbox()
data_frame
Lon 經度
Lat 緯度
size
color
title
text 圖中顯示的標籤
hover_name 滑鼠移過顯示的名稱
hover_data 滑鼠移過顯示的數據
animation_frame 動畫標記
color_continuous_sale CSS顏色列表
size_max 最大尺寸 預設為20
zoom 地圖縮放的級別0~20,預設為8

安裝conda install plotly

地圖樣式指令
"white-bg" yields an empty white canvas which results in no external HTTP requests
"open-street-map", "carto-positron", "carto-darkmatter", "stamen-terrain", "stamen-toner" or "stamen-watercolor" yeild maps
composed of raster tiles from various public tile servers which do not require signups or access tokens
"basic", "streets", "outdoors", "light", "dark", "satellite", or "satellite-streets"

import pandas as pd
us_cities=pd.read_csv('https://raw.githubusercontent.com/plotly/datasets/master/us-cities-top-1k.csv')
import plotly.express as px
fig=px.scatter_mapbox(us_cities,lat='lat',lon='lon',hover_name='City',hover_data=['State','Population'],color_discrete_sequence=['fuchsia'],zoom=3,height=300)

fig.update_layout(mapbox_style='open-street-map')
fig.update_layout(margin={'r':0,'t':0,'l':0,'b':0})
fig.show()


import pandas as pd
us_cities=pd.read_csv('https://raw.githubusercontent.com/plotly/datasets/master/us-cities-top-1k.csv')
import plotly.express as px
fig=px.scatter_mapbox(us_cities,lat='lat',lon='lon',hover_name='City',hover_data=['State','Population'],color_discrete_sequence=['fuchsia'],zoom=3,height=300)

fig.update_layout(mapbox_style='white-bg',mapbox_layers=[{'below':'traces','sourcetype':'raster','source':['https://basemap.nationalmap.gov/arcgis/rest/services/USGSImageryOnly/MapServer/tile/{z}/{y}/{x}']}])
fig.update_layout(margin={'r':0,'t':0,'l':0,'b':0})
fig.show()


import plotly.graph_objects as go
import pandas as pd
df= pd.read_csv('https://raw.githubusercontent.com/plotly/datasets/master/finance-charts-apple.csv')

fig=go.Figure(go.Scatter(x=df['Date'],y=df['AAPL.High']))
fig.update_layout(title='Time Series with Custom Data-Time Format',xaxis_tickformat='%d %B (%a)<br>%Y')
fig.show()


import plotly.graph_objects as go

fig = go.Figure(go.Scatter(x=[1,2,3,4,5,6,7,8,9,10,11,12],y=[28.8,28.5,37,56.8,69.7,79.7,78.5,77.8,74.1,62.6,45.3,39.9]))

fig.update_layout(yaxis_tickformat='%')

fig.show()


import plotly.express as px
df = px.data.tips()
df['size']=df['size'].astype(str)
df['size']=df['size'].astype(float)
fig = px.scatter(df,x='total_bill',y='tip',color='size',title="Numberic 'size' values mean continous color")
fig.show()


import plotly.express as px
print(px.data.tips.__doc__)
px.data.tips().head()


print(px.data.iris.__doc__)
px.data.iris().head()


import plotly.express as px
iris=px.data.iris()
fig = px.scatter(iris,x='sepal_width',y='sepal_length',color='species')
fig.show()


from plotly.subplots import make_subplots
fig = make_subplots(rows=1,cols=2)
fig.add_trace(go.Scatter(y=[4,2,1],mode='lines'),row=1,col=1)
fig.add_trace(go.Bar(y=[2,1,3]),row=1,col=2)
fig.show()


import plotly.express as px
df=px.data.iris()
fig = px.parallel_coordinates(df,color='species_id',labels={'species_id':'Species','sepal_width':'Sepal_width','sepal_length':'Sepal_length','petal_width':'Petal_width','petal_length':'Petal_length'},color_continuous_scale=px.colors.diverging.Tealrose,color_continuous_midpo)

import plotly.figure_factory as ff
data_matrix=[['國家','Year','Population'],
             ['United States',2000,202200000],
             ['Canada',2000,27790000],
             ['United States',2005,295500000],
             ['Canada',2005,32310000],
             ['United States',2010,309000000],
             ['Canada',2010,34000000]]
fig=ff.create_table(data_matrix)
fig.show()


import plotly.figure_factory as ff
data_matrix = [['User','Language','Chart Type','of Views'],
               ['<a href="https://plotly.com/~empet/folder/home">empet</a>',
                '<a href="https://plotly.com/python/">Python</a>',
                '<a href="https://plotly.com/~empet/8614/">Network Graph</a>',
                289],
                ['<a href="https://plotly.com/~Grondo/folder/home">Grondo</a>',
                 '<a href="https://plotly.com/matlab/">Matlab</a>',
                 '<a href="https://plotly.com/~Grondo/42/">Subplots</a>',
                 356],
                ['<a href="https://plotly.com/~Dreamshot/folder/home">Dreamshot</a>',
                 '<a href="https://help.plotly.ly/tutorials/">Web App</a>',
                 '<a href="https://plotly.com/~Dreamshot/6575/_2014=us=city=populations">Bubble Map</a>',
                 262],
                ['<a href="https://plotly.com/~FiveThirtyEight/folder/home">FiveThirtyEight</a>',
                 '<a href="https://help.plotly.ly/tutorials/">Web App</a>',
                 '<a href="https://plotly.com/~FiveThirtyEight/30/">Scatter</a>',
                 692],
                ['<a href="https://plotly.com/~cpsievert/folder/home">cpsievert</a>',
                 '<a href="https://plotly.com/r/">R</a>',
                 '<a href="https://plotly.com/~cpsievert/1130/">Surface</a>',
                 302]]
fig = ff.create_table(data_matrix)
fig.show()


import pandas as pd
df = pd.read_csv('https://raw.githubusercontent.com/plotly/datasets/master/gapminderDataFiveYear.csv')
df_sample=df[400:410]
colorscale=[[0,'#4d004c'],[.5,'#f2e5ff'],[1,'#ffffff']]
fig=ff.create_table(df_sample,colorscale=colorscale)
fig.show()


import plotly.figure_factory as ff
text=[['Team','Rank'],['A',1],['B',2],['C',3],['D',4],['E',5],['F',6]]
colorscale=[[0,'#272D31'],[.5,'#ffffff'],[1,'#ffffff']]
font=['#FCFCFC','#00EE00','#008B00','#004F00','#660000','#CD0000','#FF3030']
fig=ff.create_table(text,colorscale=colorscale,font_colors=font)
fig.layout.width=250
fig.show()


add_trace()->繪製子圖
text->設置圖表標題

from plotly.subplots import make_subplots
import plotly.graph_objects as go
fig = make_subplots(rows=1,cols=2)
fig.add_trace(go.Scatter(x=[1,2,3],y=[4,5,6]),row=1,col=1)
fig.add_trace(go.Scatter(x=[20,30,40],y=[50,60,70]),row=1,col=2)
fig.update_layout(height=600,width=800,title_text='Side By Side Subplots')
fig.show()


from plotly.subplots import make_subplots
import plotly.graph_objects as go
fig = make_subplots(rows=2,cols=2,subplot_titles=('Plot 1','Plot 2','Plot 3','Plot 4'))
fig.add_trace(go.Scatter(x=[1,2,3],y=[4,5,6]),row=1,col=1)
fig.add_trace(go.Scatter(x=[20,30,40],y=[50,60,70]),row=1,col=2)
fig.add_trace(go.Scatter(x=[300,400,500],y=[600,700,800]),row=2,col=1)
fig.add_trace(go.Scatter(x=[4000,5000,6000],y=[7000,8000,9000]),row=2,col=2)
fig.update_layout(height=600,width=800,title_text='Multiple Subpolts with Titles')
fig.show()


from plotly.subplots import make_subplots
import plotly.graph_objects as go
fig = make_subplots(rows=1,cols=2,shared_yaxes=True)
fig.add_trace(go.Bar(x=[1,2,3],y=[4,5,6],marker=dict(color=[4,5,6],coloraxis='coloraxis')),1,1)
fig.add_trace(go.Bar(x=[1,2,3],y=[2,3,5],marker=dict(color=[2,3,5],coloraxis='coloraxis')),1,2)
fig.update_layout(coloraxis=dict(colorscale='Bluered_r'),showlegend=False)
fig.show()


go.Bar()->直條圖
go.Barpolar()->雷達圖
go.Pie()->圓餅圖
go.Scatter3d()->3D視圖

from plotly.subplots import make_subplots
import plotly.graph_objects as go 
fig=make_subplots(rows=2,cols=2,specs=[[{'type':'xy'},{'type':'polar'}],[{'type':'domain'},{'type':'scene'}]])
fig.add_trace(go.Bar(y=[2,3,1]),row=1,col=1)
fig.add_trace(go.Barpolar(theta=[0,45,90],r=[2,3,1]),row=1,col=2)
fig.add_trace(go.Pie(values=[2,3,1]),row=2,col=1)
fig.add_trace(go.Scatter3d(x=[2,3,1],y=[0,0,0],z=[0.5,1,2],mode='lines'),row=2,col=2)
fig.update_layout(height=700,showlegend=False)
fig.show()


import plotly.graph_objs as go 
import plotly
import numpy as np

x,y,z=np.random.multivariate_normal(np.array([0,0,0]),np.eye(3),20).transpose()
trace1=go.Scatter3d(x=x,y=y,z=z,mode='markers',marker=dict(size=12,line=dict(color='rgba(217,217,217,0.14)',width=0.5),opacity=0.8))
x2,y2,z2=np.random.multivariate_normal(np.array([0,0,0]),np.eye(3),20).transpose()
trace2=go.Scatter3d(x=x2,y=y2,z=z2,mode='markers',marker=dict(color='rgba(255,0,0,0.5)',size=12,symbol='circle',line=dict(color='rgb(204,204,204)',width=1),opacity=0.9))
data=[trace1,trace2]
layout=go.Layout(margin=dict(l=0,r=0,b=0,t=0))
fig=go.Figure(data=data,layout=layout)
plotly.offline.plot(fig,filename='plotly3d_simple.html',auto_open=True)


import plotly.graph_objs as go 
import plotly
import numpy as np
df=pd.read_csv('https://raw.githubusercontent.com/plotly/datasets/master/api_docs/mt_bruno_elevation.csv',dtype='str')
#利用FOR迴圈把資料撈出來
for row in df.values:
    for col in row:
        print('%s, '%col,end='')
    print()
    
trace=go.Surface(z=df.values)
data=[trace]
layout=go.Layout(title='Mt Broun Elevation',autosize=True,margin=dict(l=50,r=50,b=50,t=50))
fig=go.Figure(data=data,layout=layout)
plotly.offline.plot(fig,filename='loltlyid_Topographical.html',auto_open=True)


import matplotlib.pyplot as plt
import numpy as np
#from mpl_toolkits.mplot3d import Axes3D
fig=plt.figure()
ax=fig.gca(projection='3d')
X,Y=np.mgrid[0:6*np.pi:0.25,0:4*np.pi:0.25]
Z=np.sqrt(np.abs(np.cos(X)+np.cos(Y)))
ax.plot_surface(X+1e5,Y+1e5,Z,cmap='autumn',cstride=2,rstride=2)
ax.set_xlabel('X label')
ax.set_ylabel('Y label')
ax.set_zlabel('Z label')
ax.set_zlim(0,2)
plt.show()


import matplotlib.pyplot as plt
from mpl_toolkits.mplot3d import Axes3D
from sklearn import datasets
from sklearn.decomposition import PCA
iris=datasets.load_iris()
X=iris.data[:,:2]
Y=iris.target
X_reduced=PCA(n_components=3).fit_transform(iris.data)
#透過PCA演算法將資料為度降至3
fig=plt.figure(1,figsize=(8,6))
ax=Axes3D(fig,elev=-150,azim=110)
ax.scatter(X_reduced[:,0],X_reduced[:,1],X_reduced[:,2],c=Y,cmap=plt.cm.Paired)


## SQLite 輕量級資料庫
python 2.5版後內建SQLite
import sqlite3

SQLite->MySQL->SQL->MongoDB(大數據)
(中階的資料庫)
(可用python,Java,php,C,C#)
名稱.db
資料庫->資料表->欄位 (這機制叫做關聯式資料庫)

import sqlite3
conn=sqlite3.connect('test.db')
#conn=sqlite3.connet(':menory:')#在記憶體中建立資料庫


commit() 提交
rollback() 回朔
close() 關閉連線
cursor() 建立一個游標
### cursor()還有以下物件可以操作
execute() 執行SQL語法
executemany() 執行多行SQL語法
fetchone() 從結果中取一條紀錄,並把游標指向下一條紀錄
fetchmany() 從結果中取多條紀錄,並把游標指向下一條紀錄
fetchall() 從結果中取所有紀錄,並把游標指向下一條紀錄
scroll() 滾動游標

import sqlite3
conn=sqlite3.connect('test.db')
#conn=sqlite3.connet(':menory:')#在記憶體中建立資料庫
cu=conn.cursor()
cu.execute("create table user (id integer primary key,name verchar(20) UNIQUE,age integer,comment text NULL)")
#建立一張user表,表中有id(主鏈),名字(唯一),年齡,備註(預設為空)
for user in [(0,'aaa',111,'aaaa'),(1,'bbb',222,'bbbb')]:
    conn.execute("insert into user values (?,?,?,?)",user)
    conn.commit()

cu.execute("select * from user")#*是代表全部的意思
cu.fetchone()

(0, 'aaa', 111, 'aaaa')
找到第一條

cu.execute("select * from user")
cu.fetchall()

[(0, 'aaa', 111, 'aaaa'), (1, 'bbb', 222, 'bbbb')]
找到全部

cu.execute("update user set name='ccc' where id=0")
conn.commit()

更新資料把aaa變成ccc

cu.execute("select * from user")
cu.fetchone()

(0, 'ccc', 111, 'aaaa')
成功變成ccc

cu.execute("delete from user where id=1")
conn.commit()

刪除id=1的資料

cu.execute("select * from user")
cu.fetchone()

(0, 'ccc', 111, 'aaaa')
id=1的被刪除了

import pandas as pd
import matplotlib.pyplot as plt
pd.set_option("display.max_rows",1000)
pd.set_option("display.max_columns",1000)
from pylab import mpl
mpl.rcParams['font.sans-serif']=['Microsoft YaHei']
mpl.rcParams['axes.unicode_minus']=False

#讀取檔案
df=pd.read_csv(r'AQI.csv')
df.pivot_table
#畫圖
df.plot(x='SiteName',y=['AQI'])
df.plot(kind='bar')
#存檔資料
df.to_csv('New_Data.csv',encoding='utf8')#存成csv
df.to_excel('New_Data.xlsx',encoding='utf8')#存成xlsx
df.to_html('New_Data.html',encoding='utf8')#存成html
#存成資料庫型態
con=sqlite3.connect('mydatabase.db')
df.to_sql('users',con)


import matplotlib as mpl
mpl.use('Agg')

import matplotlib.pyplot as plt

# 四個月份
labels = ["Jun","Jul","Aug","Sep"]
sizes = [20, 30, 40, 10]

# 圓餅圖顏色
colors = ['yellowgreen', 'gold', 'lightskyblue', 'lightcoral']

# 長條圖 位置
plt.subplot(1, 2, 1)
xticks = range(0, len(labels) ) # 範例寫法   xticks = range(1, len(labels) + 1 )
# 長條圖以labels為X軸，sizes為Y軸，各長條顏色為藍色（blue）
plt.xticks(xticks, labels)
plt.bar(labels, sizes, color="blue")

# 圓餅圖 位置
plt.subplot(1, 2,2)
# 圓餅圖以labels為圖標，sizes為各項所占百分比
# 圓餅圖colors為各項顏色，突顯「Aug」
# 圓餅圖顯示各項百分比到小數點第1位
explode = (0, 0, 0.1, 0)
plt.pie(sizes , explode= explode , labels = labels ,
        colors= colors , autopct='%1.1f%%')
# 長寬比為1:1
plt.axis('equal')

plt.savefig('chart.png')
plt.close()


#建立資料表的查詢指令
import sqlite3
conn=sqlite3.connect('456.db')
cu=conn.cursor()
createStr = 'CREATE TABLE Employee\
       (ID INT PRIMARY KEY     NOT NULL,\
       NAME           TEXT    NOT NULL,\
       BIRTHYEAR      INT     NOT NULL,\
       ADDRESS        CHAR(50),\
       SALARY         INT);'
cu.execute(createStr)
for Employee in [(1,'小陳',1997,'新北市',58000),(2,'小苑',2000,'台北市',50000),(3,'小施',1999,'高雄市',47000),(4,'小吳',1998,'台中市',52000)]:
    conn.execute("insert into Employee values (?,?,?,?,?)",Employee)
    conn.commit()
cursor=cu.execute('select * from Employee')
for i in cursor:
    print(i)
cu.close()

(1, '小陳', 1997, '新北市', 58000)
(2, '小苑', 2000, '台北市', 50000)
(3, '小施', 1999, '高雄市', 47000)
(4, '小吳', 1998, '台中市', 52000)

#堆疊條圖形
import numpy as np
import matplotlib.pyplot as plt
labels=['G1','G2','G3','G4','G5']
men_means=[20,35,30,35,27]
women_means=[25,32,34,20,25]
men_std=[2,3,4,1,2]
women_std=[3,5,2,3,3]
width=0.35
fig,ax=plt.subplots()
ax.bar(labels,men_means,width,yerr=men_std,label='Men')
ax.bar(labels,women_means,width,yerr=women_std,bottom=men_means,label='Women')
ax.set_ylabel('Scores')
ax.set_title('Scores by group and gender')
ax.legend()
plt.show()


import numpy as np
import matplotlib.pyplot as plt
x=np.arange(0,10,0.005)
y=np.exp(-x/2)*np.sin(2*np.pi*x)
fig,ax=plt.subplots()
ax.plot(x,y)
ax.set_xlim(0,10)
ax.set_ylim(-1,1)
xdata,ydata=5,0
xdisplay,ydisplay=ax.transData.transform((xdata,ydata))
bbox=dict(boxstyle="round",fc="0.8")
arrowprops=dict(arrowstyle="->",connectionstyle="angle,angleA=0,angleB=90,rad=10")
offset=72
ax.annotate('data = (%.1f, %.1f)'%(xdata, ydata),
            (xdata, ydata), xytext=(-2*offset, offset), textcoords='offset points',
            bbox=bbox, arrowprops=arrowprops)
plt.show()


from mpl_toolkits.mplot3d import axes3d
fig=plt.figure()
ax=fig.gca(projection='3d')
x=np.linspace(0,1,100)
y=np.sin(x*2*np.pi)/2+0.5
ax.plot(x,y,zs=0,zdir='z',label='curve in (x,y)')
colors=('r','g','b','k')
x=np.random.sample(20*len(colors))
y=np.random.sample(20*len(colors))
c_list=[]
for c in colors:
    c_list.extend([c]*20)
ax.scatter(x,y,zs=0,zdir='y',c=c_list,label='points in (x,z)')
ax.legend()
ax.set_xlim(0,1)
ax.set_ylim(0,1)
ax.set_zlim(0,1)
ax.set_xlabel('X')
ax.set_ylabel('Y')
ax.set_zlabel('Z')
ax.view_init(elev=20,azim=-35)
plt.show()