sqlite 可视化_如何利用Python批量爬取人民币外汇数据并整理储存可视化

一、需求

由于该网站(http://www.safe.gov.cn/safe/rmbhlzjj/index.html)日期选择最长间隔只能90天，因此如果手动点击下载表格，需要多次选择日期，并且将多个表格手动合并后存入数据库。为了解决这些问题，本篇使用爬虫爬取，pandas整理数据后存入数据库。

二、实现

STEP1 爬取第一批数据

打开网站，输入在允许范围内的日期，点击查询，就发送了一个POST请求

F12打开开发者工具刷新后，可以看到这个请求的url和请求体信息，仿照该请求体，设置发送请求的data参数

import requestspayload = {'startDate':'2020-01-01','endDate':'2020-03-30','queryYN':'true'}res = requests.post('http://www.safe.gov.cn/AppStructured/hlw/RMBQuery.do', data = payload)1234567

STEP2 解析数据

用查看器查看表格位置(id=“InfoTable”)，使用beautifulsoup解析数据

from bs4 import BeautifulSoupsoup = BeautifulSoup(res.text, 'html.parser')type(soup.select('#InfoTable')[0])>>>bs4.element.Tag1234

pd.read_html()需要传入的io：str or file-like，需要用prettify()方法格式化一下，顺便设置下编码

import pandas as pddfs = pd.read_html(soup.select('#InfoTable')[0].prettify('utf-8'), header=0)df_rates=dfs[0]df_rates.head()1234

STEP3 数据转换

得到的数据是一个二维表，需要存储到数据库，为了以后更好地使用，需要降低维度
使用pd.melt()方法逆透视

df_rates=pd.melt(df_rates,col_level=0,  id_vars=['日期'])df_rates.columns = ['date', 'currency', 'exchange']df_rates.head()123

或者一步步转

se=df_rates.set_index('日期').stack()se.index.names=['date', 'currency']df_rates=se.reset_index(name='exchange')df_rates.head()1234

STEP4 存储数据到数据库

import sqlite3with sqlite3.connect('currency.sqlite') as db:    df_rates.to_sql('currency_data', con=db, if_exists='replace', index = None)123

STEP5 整合成自定义方法

上述4个步骤已经完成了一次完整的抓取-处理-存储过程，之后只要改变request请求的日期就能完成长时间距离的数据获取，因此可以将上述步骤整合成可调用的自定义方法，只需要传入时间参数

import requestsimport pandas as pdfrom bs4 import BeautifulSoupimport sqllite3def getCurrency(start, end):#STEP 1payload = {'startDate':start,'endDate':end,'queryYN':'true'}res = requests.post('http://www.safe.gov.cn/AppStructured/hlw/RMBQuery.do', data = payload)#STEP2soup = BeautifulSoup(res.text, 'html.parser')dfs = pd.read_html(soup.select('#InfoTable')[0].prettify(), header=0)df_rates=dfs[0]#STEP3df_rates=pd.melt(df_rates,col_level=0,  id_vars=['日期'])df_rates.columns = ['date', 'currency', 'exchange']#STEP4with sqlite3.connect('currency.sqlite') as db:    df_rates.to_sql('currency_data', con=db, if_exists='append', index = None)1234567891011121314151617181920212223242526

STEP6 使用循环

上述4个步骤已经完成了一次完整的抓取-处理-存储过程，如果需要抓取365天至今的数据，至少需要math.ceil(365/90)=5次，每次请求体中日期之间都是等距的90天

from datetime import datetime, timedeltacurrent_time = datetime.now()for i in range(-1,365-1,90):    start_date = (current_time - timedelta(days = i + 90)).strftime('%Y-%m-%d')    end_date   = (current_time - timedelta(days = i +1)).strftime('%Y-%m-%d')    #print(start_date, end_date)    getCurrency(start_date, end_date)1234567

STEP7 取数可视化

上述步骤已经将所需数据存入数据库，可以在python中使用pandas执行SQL语句查询到所需数据进行分析
1.查询“美元“的汇率中间价

with lite.connect('currency.sqlite') as db:    df = pandas.read_sql("SELECT * FROM currency_data WHERE currency = '美元'", con=db)12

2.修改数据类型

#df.info()df['date'] = pandas.to_datetime(df['date'], format= '%Y-%m-%d')12

3.将日期列转为索引

df=df.set_index('date')1

4.可视化数据

% pylab inline#线图df.plot(kind = 'line')#30天移动平均线df['mvg30']  = df['exchange'].rolling(window=30).mean()#同时展示df[['exchange', 'mvg30']].plot(kind = 'line')1234567

如果同时取”美元”和“英镑“，多个系列在同一张图上，需要将项透视成列

with sqlite3.connect('currency.sqlite') as db:    df = pd.read_sql("SELECT * FROM currency_data WHERE currency IN ('美元', '英镑') ", con=db)df['date'] = pandas.to_datetime(df['date'], format= '%Y-%m-%d')#建立数据透视表df2 = df.pivot_table(index= 'date', columns= 'currency')#绘图plt.rcParams['font.sans-serif']=['SimHei']df2.plot(kind= 'line', rot= 30)

源码获取加私信小编01