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

作者:Eastmount

这是非常好的一篇文章,可以认为是我做数据分析的转折点,为什么呢?
因为这是我做数据分析第一次引入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/ )




发表评论

0个评论

我要留言×

技术领域:

我要留言×

留言成功,我们将在审核后加至投票列表中!

提示x

知识工程知识库已成功保存至我的图谱现在你可以用它来管理自己的知识内容了

删除图谱提示×

你保存在该图谱下的知识内容也会被删除,建议你先将内容移到其他图谱中。你确定要删除知识图谱及其内容吗?

删除节点提示×

无法删除该知识节点,因该节点下仍保存有相关知识内容!

删除节点提示×

你确定要删除该知识节点吗?