python数据挖掘课程 十一.Pandas、Matplotlib结合SQL语句可视化分析

这是非常好的一篇文章，可以认为是我做数据分析的转折点，为什么呢？
因为这是我做数据分析第一次引入SQL语句，然后爱不释手；结合SQL语句返回结果进行数据分析的效果真的很好，很多大神看到可能会笑话晚辈，但是如果你是数据分析的新人，那我强烈推荐，尤其是结合网络爬虫进行数据分析的。希望这篇文章对你有所帮助，如果文章中存在错误或不足之处，还请高抬贵手~

1.MySQL数据库知识

首先在"[python爬虫] Selenium爬取内容并存储至MySQL数据库"这篇文章中我讲述了爬虫爬取数据并存储在MySQL中，如下图所示，我的所有博客文章。

其中创建的数据库表csdn内容如下所示：

[sql]  view plain  copy

 

1. CREATE TABLE `csdn` (    
2.   `ID` int(11) NOT NULL AUTO_INCREMENT,    
3.   `URL` varchar(100) COLLATE utf8_bin DEFAULT NULL,    
4.   `Author` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT '作者',    
5.   `Artitle` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT '标题',    
6.   `Description` varchar(400) COLLATE utf8_bin DEFAULT NULL COMMENT '摘要',    
7.   `Manage` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT '信息',    
8.   `FBTime` datetime DEFAULT NULL COMMENT '发布日期',    
9.   `YDNum` int(11) DEFAULT NULL COMMENT '阅读数',    
10.   `PLNum` int(11) DEFAULT NULL COMMENT '评论数',    
11.   `DZNum` int(11) DEFAULT NULL COMMENT '点赞数',    
12.   PRIMARY KEY (`ID`)    
13. ) 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小时写博客的个时间段的博客数量：

代码如下所示：

[python]  view plain  copy

 

1. # coding=utf-8  
2. ''''' 
3. ' 这篇代码主要讲述获取MySQL中数据，再进行简单的统计 
4. ' 统计采用SQL语句进行 By:Eastmount CSDN 
5. '''  
6.   
7. import matplotlib.pyplot as plt  
8. import matplotlib  
9. import pandas as pd  
10. import numpy as np  
11. import pylab  
12. import MySQLdb  
13. from pylab import *  
14.   
15.   
16. # 根据SQL语句输出24小时的柱状图  
17. try:  
18.     conn = MySQLdb.connect(host='localhost',user='root',  
19.                          passwd='123456',port=3306, db='test01')  
20.     cur = conn.cursor() #数据库游标  
21.   
22.     #防止报错:UnicodeEncodeError: 'latin-1' codec can't encode character  
23.     conn.set_character_set('utf8')  
24.     cur.execute('SET NAMES utf8;')  
25.     cur.execute('SET CHARACTER SET utf8;')  
26.     cur.execute('SET character_set_connection=utf8;')  
27.     sql = "select HOUR(FBTime) as hh, count(*) as cnt from csdn group by hh;"  
28.     cur.execute(sql)  
29.     result = cur.fetchall()        #获取结果复合纸给result  
30.     hour1 = [n[0] for n in result]  
31.     print hour1  
32.     num1 = [n[1] for n in result]  
33.     print num1  
34.       
35.     N = 23    
36.     ind = np.arange(N)  #赋值0-23    
37.     width=0.35    
38.     plt.bar(ind, num1, width, color='r', label='sum num')     
39.     #设置底部名称      
40.     plt.xticks(ind+width/2, hour1, rotation=40) #旋转40度  
41.     for i in range(23):   #中心底部翻转90度  
42.         plt.text(i, num1[i], str(num1[i]),  
43.                  ha='center', va='bottom', rotation=45)   
44.     plt.title('Number-24Hour')      
45.     plt.xlabel('hours')  
46.     plt.ylabel('The number of blog')  
47.     plt.legend()  
48.     plt.savefig('08csdn.png',dpi=400)      
49.     plt.show()  
50.   
51.   
52. #异常处理  
53. except MySQLdb.Error,e:  
54.     print "Mysql Error %d: %s" % (e.args[0], e.args[1])  
55. finally:  
56.     cur.close()  
57.     conn.commit()    
58.     conn.close()  
59.   
60.          

运行结果如下图所示，突然发现我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年开始，每个月份写博客的数量：

代码如下所示：

[python]  view plain  copy

 

1. # coding=utf-8  
2. ''''' 
3. ' 这篇代码主要讲述获取MySQL中数据，再进行简单的统计 
4. ' 统计采用SQL语句进行 By:Eastmount 
5. '''  
6.   
7. import matplotlib.pyplot as plt  
8. import matplotlib  
9. import pandas as pd  
10. import numpy as np  
11. import pylab  
12. import MySQLdb  
13. from pylab import *  
14. import matplotlib.pyplot as plt  
15.   
16. #根据SQL语句输出散点  
17. try:  
18.     conn = MySQLdb.connect(host='localhost',user='root',  
19.                          passwd='123456',port=3306, db='test01')  
20.     cur = conn.cursor() #数据库游标  
21.   
22.     #防止报错:UnicodeEncodeError: 'latin-1' codec can't encode character  
23.     conn.set_character_set('utf8')  
24.     cur.execute('SET NAMES utf8;')  
25.     cur.execute('SET CHARACTER SET utf8;')  
26.     cur.execute('SET character_set_connection=utf8;')  
27.     sql = '''''select DATE_FORMAT(FBTime,'%Y%m'), count(*) from csdn 
28.             group by DATE_FORMAT(FBTime,'%Y%m');'''  
29.     cur.execute(sql)  
30.     result = cur.fetchall()        #获取结果复合纸给result  
31.     date1 = [n[0] for n in result]  
32.     print date1  
33.     num1 = [n[1] for n in result]  
34.     print num1  
35.     print type(date1)  
36.     plt.scatter(date1,num1,25,color='white',marker='o',  
37.                 edgecolors='#0D8ECF',linewidth=3,alpha=0.8)  
38.     plt.title('Number-12Month')      
39.     plt.xlabel('Time')  
40.     plt.ylabel('The number of blog')      
41.     plt.savefig('02csdn.png',dpi=400)   
42.     plt.show()  
43.   
44.   
45. #异常处理  
46. except MySQLdb.Error,e:  
47.     print "Mysql Error %d: %s" % (e.args[0], e.args[1])  
48. finally:  
49.     cur.close()  
50.     conn.commit()    
51.     conn.close()  
52.               

运行结果如下图所示：

然后发现改图运行效果不好，下面进行改进。

4.通过DataFrame每年每月博客对比

SQL语句查询每年发表博客数据：
select DATE_FORMAT(FBTime,'%Y'), Count(*) from csdn
group by DATE_FORMAT(FBTime,'%Y');

核心代码如下所示：

[python]  view plain  copy

 

1. # coding=utf-8  
2. ''''' 
3. ' 这篇代码主要讲述获取MySQL中数据，再进行简单的统计 
4. ' 统计采用SQL语句进行 By：Eastmount 
5. '''  
6.   
7. import matplotlib.pyplot as plt  
8. import matplotlib  
9. import pandas as pd  
10. import numpy as np  
11. import pylab  
12. import MySQLdb  
13. from pylab import *  
14. from pandas import *  
15.   
16.   
17. # 根据SQL语句输出24小时的柱状图  
18. try:  
19.     conn = MySQLdb.connect(host='localhost',user='root',  
20.                          passwd='123456',port=3306, db='test01')  
21.     cur = conn.cursor() #数据库游标  
22.   
23.     #防止报错:UnicodeEncodeError: 'latin-1' codec can't encode character  
24.     conn.set_character_set('utf8')  
25.     cur.execute('SET NAMES utf8;')  
26.     cur.execute('SET CHARACTER SET utf8;')  
27.     cur.execute('SET character_set_connection=utf8;')  
28.     sql = '''''select DATE_FORMAT(FBTime,'%Y'), Count(*) from csdn 
29.                 group by DATE_FORMAT(FBTime,'%Y');'''  
30.     cur.execute(sql)  
31.     result = cur.fetchall()        #获取结果复合纸给result  
32.     day1 = [n[0] for n in result]  
33.     print len(day1)  
34.     num1 = [n[1] for n in result]  
35.     print len(num1),type(num1)  
36.     matplotlib.style.use('ggplot')  
37.     df=DataFrame(num1, index=day1,columns=['Nums'])  
38.     plt.show(df.plot(kind='bar'))  
39.     plt.savefig('05csdn.png',dpi=400)  
40.   
41.   
42. #异常处理  
43. except MySQLdb.Error,e:  
44.     print "Mysql Error %d: %s" % (e.args[0], e.args[1])  
45. finally:  
46.     cur.close()  
47.     conn.commit()    
48.     conn.close()  
49.   
50.       

运行结果如下图所示，同时设置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.时间序列图

核心代码如下所示：

[python]  view plain  copy

 

1. # coding=utf-8  
2. ''''' 
3. ' 这篇代码主要讲述获取MySQL中数据，再进行简单的统计 
4. ' 统计采用SQL语句进行 By:Eastmount CSDN 
5. '''  
6.   
7. import matplotlib.pyplot as plt  
8. import matplotlib  
9. import pandas as pd  
10. import numpy as np  
11. import pylab  
12. import MySQLdb  
13. from pylab import *  
14.   
15.   
16. # 根据SQL语句输出24小时的柱状图  
17. try:  
18.     conn = MySQLdb.connect(host='localhost',user='root',  
19.                          passwd='123456',port=3306, db='test01')  
20.     cur = conn.cursor() #数据库游标  
21.   
22.     #防止报错:UnicodeEncodeError: 'latin-1' codec can't encode character  
23.     conn.set_character_set('utf8')  
24.     cur.execute('SET NAMES utf8;')  
25.     cur.execute('SET CHARACTER SET utf8;')  
26.     cur.execute('SET character_set_connection=utf8;')  
27.     sql = '''''select DATE_FORMAT(FBTime,'%Y-%m-%d'), Count(*) from csdn 
28.                 group by DATE_FORMAT(FBTime,'%Y-%m-%d');'''  
29.     cur.execute(sql)  
30.     result = cur.fetchall()        #获取结果复合纸给result  
31.     day1 = [n[0] for n in result]  
32.     print len(day1)  
33.     num1 = [n[1] for n in result]  
34.     print len(num1),type(num1)  
35.     matplotlib.style.use('ggplot')  
36.     #获取第一天  
37.     start = min(day1)  
38.     print start  
39.     #np.random.randn(len(num1)) 生成正确图形 正态分布随机数  
40.     ts = pd.Series(np.random.randn(len(num1)),  
41.                    index=pd.date_range(start, periods=len(num1)))  
42.     ts = ts.cumsum()  
43.     ts.plot()  
44.     plt.title('Number-365Day')      
45.     plt.xlabel('Time')  
46.     plt.ylabel('The number of blog')  
47.     plt.savefig('04csdn.png',dpi=400)      
48.     plt.show()  
49.   
50.   
51. #异常处理  
52. except MySQLdb.Error,e:  
53.     print "Mysql Error %d: %s" % (e.args[0], e.args[1])  
54. finally:  
55.     cur.close()  
56.     conn.commit()    
57.     conn.close()  
58.               

运行结果如下所示：

同时如何设置具体的博客数量呢？设置num1参数总数递增的曲线，更多知识明天上班来解决及学习啦。

最后希望这篇文章对你有所帮助，尤其是我的学生和接触数据挖掘、数据分析的博友。这篇文字主要是记录一些代码片段，作为在线笔记，也希望对你有所帮助。
(By:Eastmount 2017-03-17 半夜12点  http://blog.csdn.net/eastmount/ )