sqlite 可视化_数据分析实战-热点新闻可视化-0

最近有一个朋友问我一般你们做分析都什么流程啊？我零散学了SQL, Python, Power BI 等但是不知道怎么用啊。这一问题启发我写了这一个小项目，标题图片是项目成果的一部分。

在这篇文章中我就分享下数据分析的简单流程，具体分析流程如下图。

这篇文章是这个小项目的第一篇。接下来我会用3篇文章展开讲一下操作步骤。

1. 获取，整理数据并存储：使用Python的pandas库 和网页API 获取数据, 并存储到开源数据库SQLite
2. 自动定时更新ETL：创建bat文件和Windows中自带的任务计划定时运行ETL
3. 数据分析：使用Jupyter Notebook 和Python 常用绘图库进行数据可视化等分析

项目文件下载地址（制作中）

在开始阅读前，需要对以下内容有一定了解，可以帮助你阅读。

• Python 基础操作和常用库的使用。
• Anaconda 管理Python编程环境。这里推荐一个猴子的回答。
• 数据库存储和查询数据（SQL的使用）。这里了解SQL相关知识。

---

获取网页数据API

分析的第一步便是获取数据， 俗话说巧妇难为无米之炊。 如果你都没有可靠的数据，谈何数据分析呢？一般常用数据源分为，1）用SQL等数据库查询语言从数据库中调取数据， 2）从数据文件导入，比如EXCEL， CSV等，3）从网页爬取或者API直接获取，4）又或者是通过传感器获得实时数据。

我们这里使用网页的API来获取数据。我们使用的网页是：https://www.enlightent.cn/research/

1. 使用谷歌Chrome浏览器打开网页并点击热搜榜

2. 按键盘上F12打开浏览器开发者工具，你会看到如下页面。鼠标点击右边的Network，然后点击下方的XHR。

3. 依次点击左侧抖音，头条，豆瓣，微博可以看到右边出现对应的地址

4. 双击右边出现的地址，打开新的网页可以看到如下图

5. 注意到上面的网页地址https://www.enlightent.cn/research/top/getWeiboRank.do?type=douyin&t=1789229393 ，我们注意到是这里又两个参数。type = 和 t = 。

这里的type = 就是抖音的拼音。由此我们推断出改变这里的type就可以获取对应的平台数据。而这里的t 参数不重要，可以直接去掉。我们来试试头条的地址: https://www.enlightent.cn/research/top/getWeiboRank.do?type=toutiao。 我们成功获得了头条的实时数据。这里需要注意的是微博的type是realTimeHotSearchList。

这个可以返回如图所示的数据就是我们说的API。我们使用Python 的pandas库就可以直接读取到网页数据。

用Python读取网页API 数据

# 可以使用jupyter notebook 运行以下代码，需要安装pandas
# 首先载入需要的包
import pandas as pd # Data manipulation

# 设置API的URL
lst_type = ['realTimeHotSearchList', 'douyin', 'toutiao', 'douban']
url = 'https://www.enlightent.cn/research/top/getWeiboRank.do?type='

# 我们先尝试下获取douyin的数据
df = pd.read_json(url+lst_type[1])
df.head()

可以看到我们成功通过API 把网页数据载入到了pandas 的DataFrame中。

这里我们注意到firstRankingTime和updateTime不是时间格式而是一串数字。我们需要把他转换成时间格式。

#转换成时间格式
df['firstRankingTime'] = pd.to_datetime(df.loc[:,'firstRankingTime'], unit = 's')
df['updateTime'] = pd.to_datetime(df.loc[:,'updateTime'], unit = 'ms')
#添加数据获取时间
df['scrapingTime'] = pd.Timestamp.today()
df.head()

通过对数据的转换，我们把这2列转换成了时间。

接下来我们就要想办法把不同平台的数据都存到一起。

存储数据到数据库SQLite

把数据存入数据库pandas提供了非常方便的方式。只要1.建立数据库链接,然后通过， 2. dataframe.to_sql()的函数方式就能直接存入数据库。

#简单演示
import sqlite3
#建立数据库地址.替换如下地址到你自己指定的文件夹
d_path = r'youraddressdatabasehot_news.db'
# 建立数据库链接，如果数据库不存在，则会自动创建一个
conn = sqlite3.connect(d_path)
#存入数据库, 这里的df就是你之间创建的dataframe
df.to_sql('hot_news', con = conn, if_exists = 'append')
#检验下是否成功存进去了。
cur = conn.cursor()
#用SQL语句进行查询
cur.execute('SELECT * FROM hot_news')
rows = cur.fetchall()
for row in rows:
    print(row)

我们成功调取了存在SQLite中的数据

接下来我把上面的汇总在一个pyhon代码文件里。这个汇总的文件就是我们所说的ETL过程，对数据进行获取，预处理，并存入到数据库中，方便我们未来对数据进行分析。

# 代码汇总
"""
Created on Tue Mar 10 08:30:54 2020
This is an ETL to load the data into a sqlite database

@author: UpcaseM
"""

# =============================================================================
# Import packages needed
# =============================================================================

import pandas as pd # Data manipulation
import sqlite3
import os
# Path of the working folder
w_path = os.path.dirname(os.path.abspath(__file__))[:-3]
# Path of the database
d_path = w_path + 'databasehot_news.db'

# =============================================================================
# Load json data into dataframe through API
# =============================================================================

lst_type = ['realTimeHotSearchList', 'douyin', 'toutiao', 'douban']
url = 'https://www.enlightent.cn/research/top/getWeiboRank.do?type='

#这里你可以选择把所有平台数据汇总后再存入数据库或者每次获取后存入数据库。
def hot_news_etl(url, type_list, db):
    
    for n_type in type_list:
        print('Loading data for {}'.format(n_type))
        df = pd.read_json(url+n_type)
        
        # Convert float to date type for Time columns
        df['firstRankingTime'] = pd.to_datetime(df.loc[:,'firstRankingTime'], unit = 's')
        df['updateTime'] = pd.to_datetime(df.loc[:,'updateTime'], unit = 'ms')
        df['scrapingTime'] = pd.Timestamp.today()
        
        # Connect to sqlite database and create one if it doesn't exit
        with sqlite3.connect(d_path) as conn:
            df.to_sql('hot_news', con = conn, if_exists = 'append')
        
        print('{} is saved successfully!'.format(n_type))

if __name__ == '__main__':
    print('Start updating...')
    hot_news_etl(url, lst_type, d_path)
    print('Completed')

在下一篇文章中我们要做一个可以自动运行的程序，每天在特定时间运行，然后把每天网页上的热点新闻存到我们的数据库中，我们这样我们就可以对不同时间段的新闻进行分析了。

这个过程和我们实际工作的流程是一样的，都是通过ETL整合不同数据源并更新数据库中的数据。然后利用这个数据库中的数据进行数据分析，可视化，或者建模之类的操作。

第一篇文章就到这里，大家有兴趣赶紧来练练手吧！