【python資料探勘課程】十一.Pandas、Matplotlib結合SQL語句視覺化分析

Eastmount發表於2017-03-17
PythonSQL視覺化
這是非常好的一篇文章，可以認為是我做資料分析的轉折點，為什麼呢？
因為這是我做資料分析第一次引入SQL語句，然後愛不釋手；結合SQL語句返回結果進行資料分析的效果真的很好，很多大神看到可能會笑話晚輩，但是如果你是資料分析的新人，那我強烈推薦，尤其是結合網路爬蟲進行資料分析的。希望這篇文章對你有所幫助，如果文章中存在錯誤或不足之處，還請高抬貴手~


1.MySQL資料庫知識

首先在"[python爬蟲] Selenium爬取內容並儲存至MySQL資料庫"這篇文章中我講述了爬蟲爬取資料並儲存在MySQL中，如下圖所示，我的所有部落格文章。


其中建立的資料庫表csdn內容如下所示：

CREATE TABLE `csdn` (  
  `ID` int(11) NOT NULL AUTO_INCREMENT,  
  `URL` varchar(100) COLLATE utf8_bin DEFAULT NULL,  
  `Author` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT '作者',  
  `Artitle` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT '標題',  
  `Description` varchar(400) COLLATE utf8_bin DEFAULT NULL COMMENT '摘要',  
  `Manage` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT '資訊',  
  `FBTime` datetime DEFAULT NULL COMMENT '釋出日期',  
  `YDNum` int(11) DEFAULT NULL COMMENT '閱讀數',  
  `PLNum` int(11) DEFAULT NULL COMMENT '評論數',  
  `DZNum` int(11) DEFAULT NULL COMMENT '點贊數',  
  PRIMARY KEY (`ID`)  
) ENGINE=InnoDB AUTO_INCREMENT=9371 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
執行結果如下圖所示：



        如果再安裝過程中，報錯：Fatal error in launcher: Unable to create process using “”C:\Program Files (x86)\Python33\python.exe“ ”C:\Program Files (x86)\Python33\pip.exe“使用下面的命令安裝。




2.繪製24小時部落格對比

SQL語句如下：

select HOUR(FBTime) as hh, count(*) as cnt from csdn group by hh;

分析博主24小時寫部落格的個時間段的部落格數量：

程式碼如下所示：

# coding=utf-8
'''
' 這篇程式碼主要講述獲取MySQL中資料，再進行簡單的統計
' 統計採用SQL語句進行 By:Eastmount CSDN
'''

import matplotlib.pyplot as plt
import matplotlib
import pandas as pd
import numpy as np
import pylab
import MySQLdb
from pylab import *


# 根據SQL語句輸出24小時的柱狀圖
try:
    conn = MySQLdb.connect(host='localhost',user='root',
                         passwd='123456',port=3306, db='test01')
    cur = conn.cursor() #資料庫遊標

    #防止報錯:UnicodeEncodeError: 'latin-1' codec can't encode character
    conn.set_character_set('utf8')
    cur.execute('SET NAMES utf8;')
    cur.execute('SET CHARACTER SET utf8;')
    cur.execute('SET character_set_connection=utf8;')
    sql = "select HOUR(FBTime) as hh, count(*) as cnt from csdn group by hh;"
    cur.execute(sql)
    result = cur.fetchall()        #獲取結果複合紙給result
    hour1 = [n[0] for n in result]
    print hour1
    num1 = [n[1] for n in result]
    print num1
    
    N = 23  
    ind = np.arange(N)  #賦值0-23  
    width=0.35  
    plt.bar(ind, num1, width, color='r', label='sum num')   
    #設定底部名稱    
    plt.xticks(ind+width/2, hour1, rotation=40) #旋轉40度
    for i in range(23):   #中心底部翻轉90度
        plt.text(i, num1[i], str(num1[i]),
                 ha='center', va='bottom', rotation=45) 
    plt.title('Number-24Hour')    
    plt.xlabel('hours')
    plt.ylabel('The number of blog')
    plt.legend()
    plt.savefig('08csdn.png',dpi=400)    
    plt.show()


#異常處理
except MySQLdb.Error,e:
    print "Mysql Error %d: %s" % (e.args[0], e.args[1])
finally:
    cur.close()
    conn.commit()  
    conn.close()
執行結果如下圖所示，突然發現我10點鐘沒有寫過部落格，哈哈！所以引數np.arange(23)設定成23，從圖中看出午夜寫部落格很平凡。



3.每年每月部落格對比

SQL語句如下：

select DATE_FORMAT(FBTime,'%Y%m') as 年份, count(*) as 數量 
from csdn_blog
group by DATE_FORMAT(FBTime,'%Y%m');

分析博主從2013年開始，每個月份寫部落格的數量：


程式碼如下所示：

# coding=utf-8
'''
' 這篇程式碼主要講述獲取MySQL中資料，再進行簡單的統計
' 統計採用SQL語句進行 By:Eastmount
'''

import matplotlib.pyplot as plt
import matplotlib
import pandas as pd
import numpy as np
import pylab
import MySQLdb
from pylab import *
import matplotlib.pyplot as plt

#根據SQL語句輸出散點
try:
    conn = MySQLdb.connect(host='localhost',user='root',
                         passwd='123456',port=3306, db='test01')
    cur = conn.cursor() #資料庫遊標

    #防止報錯:UnicodeEncodeError: 'latin-1' codec can't encode character
    conn.set_character_set('utf8')
    cur.execute('SET NAMES utf8;')
    cur.execute('SET CHARACTER SET utf8;')
    cur.execute('SET character_set_connection=utf8;')
    sql = '''select DATE_FORMAT(FBTime,'%Y%m'), count(*) from csdn
            group by DATE_FORMAT(FBTime,'%Y%m');'''
    cur.execute(sql)
    result = cur.fetchall()        #獲取結果複合紙給result
    date1 = [n[0] for n in result]
    print date1
    num1 = [n[1] for n in result]
    print num1
    print type(date1)
    plt.scatter(date1,num1,25,color='white',marker='o',
                edgecolors='#0D8ECF',linewidth=3,alpha=0.8)
    plt.title('Number-12Month')    
    plt.xlabel('Time')
    plt.ylabel('The number of blog')    
    plt.savefig('02csdn.png',dpi=400) 
    plt.show()


#異常處理
except MySQLdb.Error,e:
    print "Mysql Error %d: %s" % (e.args[0], e.args[1])
finally:
    cur.close()
    conn.commit()  
    conn.close()
執行結果如下圖所示：

然後發現改圖執行效果不好，下面進行改進。


4.通過DataFrame每年每月部落格對比

SQL語句查詢每年發表部落格資料：
select DATE_FORMAT(FBTime,'%Y'), Count(*) from csdn
group by DATE_FORMAT(FBTime,'%Y');

核心程式碼如下所示：

# coding=utf-8
'''
' 這篇程式碼主要講述獲取MySQL中資料，再進行簡單的統計
' 統計採用SQL語句進行 By：Eastmount
'''

import matplotlib.pyplot as plt
import matplotlib
import pandas as pd
import numpy as np
import pylab
import MySQLdb
from pylab import *
from pandas import *


# 根據SQL語句輸出24小時的柱狀圖
try:
    conn = MySQLdb.connect(host='localhost',user='root',
                         passwd='123456',port=3306, db='test01')
    cur = conn.cursor() #資料庫遊標

    #防止報錯:UnicodeEncodeError: 'latin-1' codec can't encode character
    conn.set_character_set('utf8')
    cur.execute('SET NAMES utf8;')
    cur.execute('SET CHARACTER SET utf8;')
    cur.execute('SET character_set_connection=utf8;')
    sql = '''select DATE_FORMAT(FBTime,'%Y'), Count(*) from csdn
                group by DATE_FORMAT(FBTime,'%Y');'''
    cur.execute(sql)
    result = cur.fetchall()        #獲取結果複合紙給result
    day1 = [n[0] for n in result]
    print len(day1)
    num1 = [n[1] for n in result]
    print len(num1),type(num1)
    matplotlib.style.use('ggplot')
    df=DataFrame(num1, index=day1,columns=['Nums'])
    plt.show(df.plot(kind='bar'))
    plt.savefig('05csdn.png',dpi=400)


#異常處理
except MySQLdb.Error,e:
    print "Mysql Error %d: %s" % (e.args[0], e.args[1])
finally:
    cur.close()
    conn.commit()  
    conn.close()
執行結果如下圖所示，同時設定SQL語句"%Y-%m-%d"可以設定年月日。



發現2015年我寫部落格最多，下面是繪製月份的對比，原理一樣。



同時如果想對比多個使用者，參考下面程式碼：
df=DataFrame(np.random.rand(6,4),index=['one','two','three','four','five','six'],
        columns=['A','B','C','D'])
df.columns.name='Genus'
參考文章：http://www.cnblogs.com/splended/p/5229699.html


5.時間序列圖

核心程式碼如下所示：

# coding=utf-8
'''
' 這篇程式碼主要講述獲取MySQL中資料，再進行簡單的統計
' 統計採用SQL語句進行 By:Eastmount CSDN
'''

import matplotlib.pyplot as plt
import matplotlib
import pandas as pd
import numpy as np
import pylab
import MySQLdb
from pylab import *


# 根據SQL語句輸出24小時的柱狀圖
try:
    conn = MySQLdb.connect(host='localhost',user='root',
                         passwd='123456',port=3306, db='test01')
    cur = conn.cursor() #資料庫遊標

    #防止報錯:UnicodeEncodeError: 'latin-1' codec can't encode character
    conn.set_character_set('utf8')
    cur.execute('SET NAMES utf8;')
    cur.execute('SET CHARACTER SET utf8;')
    cur.execute('SET character_set_connection=utf8;')
    sql = '''select DATE_FORMAT(FBTime,'%Y-%m-%d'), Count(*) from csdn
                group by DATE_FORMAT(FBTime,'%Y-%m-%d');'''
    cur.execute(sql)
    result = cur.fetchall()        #獲取結果複合紙給result
    day1 = [n[0] for n in result]
    print len(day1)
    num1 = [n[1] for n in result]
    print len(num1),type(num1)
    matplotlib.style.use('ggplot')
    #獲取第一天
    start = min(day1)
    print start
    #np.random.randn(len(num1)) 生成正確圖形 正態分佈隨機數
    ts = pd.Series(np.random.randn(len(num1)),
                   index=pd.date_range(start, periods=len(num1)))
    ts = ts.cumsum()
    ts.plot()
    plt.title('Number-365Day')    
    plt.xlabel('Time')
    plt.ylabel('The number of blog')
    plt.savefig('04csdn.png',dpi=400)    
    plt.show()


#異常處理
except MySQLdb.Error,e:
    print "Mysql Error %d: %s" % (e.args[0], e.args[1])
finally:
    cur.close()
    conn.commit()  
    conn.close()
執行結果如下所示：

同時如何設定具體的部落格數量呢？設定num1引數總數遞增的曲線，更多知識明天上班來解決及學習啦。

最後希望這篇文章對你有所幫助，尤其是我的學生和接觸資料探勘、資料分析的博友。這篇文字主要是記錄一些程式碼片段，作為線上筆記，也希望對你有所幫助。
一醉一輕舞，一夢一輪迴。
一曲一人生，一世一心願。
最近真的天天辦公室搞到12點多，有點對不起某些人了，而且今天心情不太好，對不住女神啦！
哎，歸去，也無風雨也無晴。
哎，綠么，亦有雪月亦有情。
老師，真是個神奇的職業，今天給學生輔導論文，從下午2點到晚上10點，醉了。
(By:Eastmount 2017-03-17 半夜12點  http://blog.csdn.net/eastmount/ )




相關文章