实验 - Internet Meter SQL¶

目标¶

第 1 部分：设置数据库

第 2 部分：连接数据库

第 3 部分：使用 Pandas 处理数据

第 4 部分：使用 Pandas 合并表</b >

场景/背景¶

在本实验中，您将学习如何连接关系型数据库，以及通过 Jupyter Notebook 检索和处理数据。为您提供的数据包含英国范围内不同地方管理机构有关 ping 操作、上传和下载速度的各种测量数据。目前，数据存储在 csv 文件中，但您将创建一个数据库并在其中填入测量数据。

您将：

使用关系型数据库
设置 SQLite
创建新数据库
使用 csvsql 创建新表，并从大型 csv 文件中直接检索数据
使用 sqlite3 Python 库连接到数据库
通过 Python 执行基本 SQL 查询
研究 Pandas 和 SQL 数据库之间的交互
合并两个表

所需资源¶

1 台接入互联网的 PC
Raspberry Pi 版本 2 或更高版本
Python 库：sqlite3、pandas、matplotlib
其他库：csvkit
数据文件：LA_wifi_speed_UK.csv、LA_population.csv

SQL 刷新¶

关系型数据库（临时）简介：http://searchsqlserver.techtarget.com/definition/relational-database</li>
与关系型数据库相关的更多视频：https://www.youtube.com/watch?v=jyju2P-7hPA&list=PLAwxTw4SYaPm4R6j_wzVOCV9fJaiQDYx4</li>
SQL 简介http://www.w3schools.com/sql/sql_intro.asp</li>

第 1 部分：设置数据库¶

在本部分中，您将使用 SQLite（一个实现独立的、无服务器的、零配置的事务性 SQL 数据库引擎的进程内库）来设置环境。

第 1 步：建立工作环境。¶

我们需要使用 sqlite3 和 csvkit 完成此笔记本实验。

a) 安装 SQLite。¶

在 Jupyter Notebook 中，可以运行在终端中使用的命令。要实现此操作，请从带有感叹号 (!) 的行开始。

# Code Cell 1
!apt-get update
!apt-get -y install sqlite3

安装过程完成后，请创建数据库：

!sqlite3 <name of your database> ".databases"

数据库名称是您要为数据库命名的名称，例如 InternetSpeed：

$sqlite3 ./Data/InternetSpeed.db ".databases"

此代码可在终端窗口中创建数据库。如果您正在使用终端，请确保导航至本实验所在的目录。

# Code Cell 2
#Create the InternetSpeed database

此时，在本地计算机上，您有一个名为 InternetSpeed 的新的空数据库。通常，如果要创建表，则需要提供一个方案，指定表的名称、列的名称和每列中的数据类型（int、str、decimal 等）。

就本课程而言，使用的是在英国 300 个地点记录的包含 wifi 速度测量数据的 **synthetic** 数据集。该文件的结构方式是每列包含一个位置的测量数据，这些数据是三项统计数据（ping、下载和上传）之一。这意味着我们需要的表要包含 300\*3 列（加上“datetime”和“index”列）。手写这样一张表的方案是不可行的。幸运的是，有一些方法可以解决这个问题，其中的方法之一就是使用 **csvkit** 软件包，它包含可用于创建表并在命令中导入数据的函数 **csvsql**。

b) 安装 csvkit。¶

在终端安装 csvkit：

$pip install csvkit

您也可以像以前那样留在笔记本环境中，在该行开头添加感叹号。

这部分实验中使用的文件名为 ./Data/LA_wifi_speed_UK.csv。

# Code Cell 3
#Installing csvkit
!pip install csvkit

c) 将数据导入数据库。¶

使用 csvsql 在数据库 InternetSpeed 中创建新表，并检索 csv 文件中包含的数据。首先，确保不存在具有相同名称的另一个数据库。如果存在，请删除它：

$test -e /tmp/InternetSpeed.db && rm /tmp/InternetSpeed.db

使用 csvkit 从 csv 文件填充数据库：

$csvsql --db sqlite:////tmp/InternetSpeed.db --insert ./Data/LA_wifi_speed_UK.csv

您可以通过终端执行此操作，也可以留在笔记本环境中。

# Code Cell 4
# Removing database if already existing
!test -e /tmp/InternetSpeed.db && rm /tmp/InternetSpeed.db

# Importing the LA_wifi_speed_UK.csv file into a sql database file /tmp/InternetSpeed.db
 # This command will take two to four minutes to complete ... please wait...
!csvsql --db sqlite:////tmp/InternetSpeed.db --insert ./Data/LA_wifi_speed_UK.csv

第 2 部分：连接数据库¶

Python 用于连接到数据库并运行一些查询来查看这些数据的特征。

导入库：

# Code Cell 5
# this is the library to talk to the database
import sqlite3
import pandas as pd
# this is a library for data vizualization
from matplotlib import pyplot as plt
# this is to say that we want our plots to be part of this page, and not opened in a new window
%matplotlib inline

您可以在此处找到有关适用于 Python 的 sqlite3 的文档：https://docs.python.org/2/library/sqlite3.html

第 1 步：连接并执行查询。¶

需要打开连接和使用光标才能与数据库进行通信。光标是一种控制结构，可让我们浏览表和记录。

要打开连接，请调用 sqlite3 库的 connect 方法。此函数会获取一个包含需要连接的数据库名称的字符串。

在本例中，该命令为：

sqlite3.connect('/tmp/InternetSpeed.db')

此函数返回将存储为变量 conn 的连接。要创建光标，请以下列方式调用方法 cursor()：

conn.cursor()

将其保存在名为 cur 的变量中。

a) 连接到刚刚创建的数据库，然后创建指向数据库的光标。¶

# Code Cell 6
# what is the name of the database you want to connect to?
# conn = sqlite3.connect(??)

cur = conn.cursor()

为了在数据库上执行查询，我们需要使用刚刚创建的光标和方法 execute()。此方法将输入包含我们感兴趣的查询的字符串。我们可以将该字符串存储在一个变量中，我们将它称为 query。请用以下方式调用该方法：

cur.execute(query)

我们要查询的表在可视化方面不是非常理想，所以我们将只选择列“DateTime”，它包含测量数据的日期和时间。我们将把查询检索到的行数限制为 10。

b) 执行查询。¶

从“DateTime”列中选择前 10 行。

提示：如果要查看数据库中的表名称，请返回到交互命令行并使用以下代码：

.tables

# Code Cell 7
# complete the query with the field you want to select, the table you want to select the field from,
# and the field you want your results to be ordered by
#query = 'SELECT ? FROM ? ORDER BY ? LIMIT ?;'


cur.execute(query)

查询结果存储在 cur 中。我们需要迭代它才能使结果可视化。

# Code Cell 8
for row in cur:
    print row

注意：我们需要根据“DateTime”列对包含测量结果的表进行排序。这是因为 SQL 没有固有的行排序，而我们处理无正确排序的时间序列没有任何意义。选择随机行样本将删除时间组件：

# Code Cell 9
query = 'SELECT DateTime FROM LA_wifi_speed_UK ORDER BY RANDOM() LIMIT 10'
cur.execute(query)

for row in cur:
    print row

第 2 步：关注部分数据。¶

900 多个列的名称是什么？可以自动检索它们并将其存储在列表中。这样可以方便在以后处理不同的问题。

首先，我们需要对所有列执行查询。在这种情况下，查询返回的行数并不重要，因此我们将其限制为 1。

# Code Cell 10
# we need to have a cursor pointing to a table for this operation
query = 'SELECT * FROM LA_wifi_speed_UK LIMIT 1'
cur.execute(query)

表中的列名称存储在光标的 description 字段中。

# Code Cell 11
#visualizing the first 10 rows of the field description
cur.description[:10]

我们可以循环遍历该字段的成员，并将列的名称放在列表中（在这种情况下，这是一种方便的数据结构）。在 Python 中，能够以“for”循环输出的形式生成列表。这个概念名为列表解析，它只是较长的“for”循环的另一个名称。在迭代某个集合时，输出列表的每个元素都是您要在“for”循环中执行的操作。

# Code Cell 12
# loop over the header and store them in a list
columns = [member[0] for member in cur.description]

# ignore index column
columns = columns[1:]

列包含每个地理区域中三个速度的测量数据。它们的格式如下：

<area name>_<suffix>，

ping 的后缀是 p，上传的后缀是 u，下载的后缀为 d。

我们需要取消后缀，因为我们感兴趣的是该区域的名称，而目前每个名称重复出现三次。我们将使用这些区域的名称来可视化各种测量数据，并在实验的稍后部分生成一个新表。

我们可以用一个空字符来替换字符串的最后一部分。为此，我们可以使用方法 replace()（参见字符串文档：https://docs.python.org/2/library/string.html）。该方法会将要被替换的部分字符串和替换为的新字符串片段视为输入。例如，要从字符串 'chestnut' 中删除字符 e 和 s，请使用此代码：

'chestnut'.replace('es', '')

a) 获取区域名称。¶

使用列表解析循环遍历包含表中的列名称的列表，并删除后缀“_p”、“_d”和“_u”。

# Code Cell 13
# remove suffix '_p'
columns = [c.replace('_p', '') for c in columns]
# remove suffix '_d'
columns = [c.replace('_d', '') for c in columns]
# remove suffix '_u'
# columns = ??

    
# this operation is to remove duplicates
columns = list(set(columns))

# Code Cell 14
# visualize the first 10 areas
columns[:10]

了解数据库中数据的最佳方法是将其中的一部分可视化。特别是，我们要选择一个区域的三项统计数据。首先，我们选择数据集中的第一个区域：

# Code Cell 15
area = columns[0]
print area

现在我们想要可视化与 E07000101 区域相关的数据。我们需要选择包含该区域的三项测量数据的列。为此，我们需要再次处理字符串。

设置字符串格式的一种方法是：

'We are considering {} area and {} statistics'.format(1, 3)

We are considering 1 area and 3 statistics

我们需要重新添加后缀以获取正确的列名称。首先要做的是，建立一个将测量数据的完整名称与后缀相关联的字典。这也将有助于在图中写入有意义的图例。

# Code Cell 16
suffix = {'_p':'ping', '_d':'download', '_u':'upload'}
# we have the suffixes as the keys of the dictionary
print suffix.keys()

# we have the complete names as the values of the dictionary
print suffix.values()

现在，该过程循环遍历字典的键，写入并执行查询以选择与我们的区域相关的测量数据，并使用以下代码进行绘制：

plt.plot(cur.fetchall(), label=suffix[s])

方法 fetchall() 检索查询结果的所有行。字段 label 将用于生成图的图例。我们也可以使用 title() 方法向图中添加标题。

b) 编写查询以选择相关列。¶

# Code Cell 17
area = columns[0]
plt.figure(figsize=(10, 8))

# Plot each variable in suffix.keys() for each area
for s in suffix.keys():
    # query = ??
    
    cur.execute(query)
    plt.plot(cur.fetchall(), label=suffix[s])
plt.legend()
plt.title(area)

第 3 部分：使用 Pandas 处理数据¶

目前的目标是计算数据集中所有区域的平均 wifi 速度。Pandas 可以帮助轻松存储和可视化数据。

第 1 步：将所有测量数据转换为平均值。¶

对于每个地方当局，我们想知道在收集数据的时间段内的 ping、上传和下载的平均互联网速度。Pandas 数据帧可以非常方便地处理这种操作。

a) 创建一个具有特定列的空数据帧。¶

 ['Area', 'Average_p', 'Average_u', 'Average_d']

# Code Cell 18
#new_columns = ?
#df = pd.DataFrame(columns = ?)

df = pd.DataFrame(columns = new_columns)

有意义的指标是每个区域的 ping、上传和下载的平均速度。要计算它，我们需要循环遍历列，而且对于每个区域、每项统计数据，我们都要计算平均值。然后我们将它插入到我们刚刚创建的空数据帧中。

为此，我们将创建一个临时列表，并使用每个区域的三个指标填充它。这将是我们要在数据帧中插入的行。

刷新命令：

循环遍历包含区域名称的列表。
循环遍历三个后缀（字典 suffix 的键）。
使用字符串格式化程序编写查询以选择某个区域的指标的平均值。
执行查询并获取结果（方法 fetchone()）。
将平均值附加到临时列表（方法 append()）。
在内部循环结束时，在数据帧中插入临时列表（方法 append()，参数 ignore_index=True）。

b) 计算平均值。¶

编写 SQL 查询，计算列的平均值并将其存储在临时列表中。该列表将是附加到数据帧的新行。这在 RaPi 中大约需要 1 分钟。

# Code Cell 19
# this will take a while...
for i in xrange(len(columns)-1):
    tmp_list = []
    tmp_list.append(columns[i])
    for s in suffix.keys():
        # query = ??
        
        cur.execute(query)
        
        mean = cur.fetchone()
        tmp_list.append(mean[0])
    #append the columns to the empty DataFrame
    df = df.append(pd.Series(tmp_list, index=new_columns), ignore_index=True)
# visualize the head of the dataframe here

可以使用 plot() 可视化每个区域的平均值。这将显示不同区域的平均速度是否相同。

# Code Cell 20
plt.figure(figsize=(20,10))
plt.plot(df.index, df[['Average_d','Average_u','Average_p']], 'o')

现在平均速度存储在 Pandas 数据帧中。将它们保存到数据库中的表可能很有用，并且 Pandas 提供了这样做的方法 (to_sql())。我们将首先检查表是否已经存在（这可能会造成问题）。如果表已存在，我们要删除它，然后再创建。否则我们将直接创建它。

构建函数 try... except 将捕获表不存在的情况下引发的例外情况，并继续创建表（有关帮助，请参阅 https://docs.python.org/2.7/tutorial/errors.html 第 8.3 节）。

c) 保存工作。¶

将新数据帧保存到 InternetSpeed 数据库中的表。

# Code Cell 21
# Make sure to drop the table first if it already exists
try:
    cur.execute('DROP TABLE average_speed')
except:
    pass

# what is the name of the table you want to save? To what connection?
# Save the dataframe table into a SQL table
# df.to_sql(?,?)

# Code Cell 22
# just checking everything worked fine: select everything from the table we just created
# and then print the first two rows of the result

query_2 = 'SELECT * FROM average_speed'
cur.execute(query_2)

# print the first fetched record
print(cur.fetchone())
# print the next fetched record
print(cur.fetchone())

第 4 部分：使用 Pandas 合并表¶

要了解区域的平均互联网速度为何变化以及如何变化，我们需要更多关于它们的信息。尤其是，我们希望了解它们的填充方式，以查看拥挤区域是否会有更好或更差的互联网服务。

第 1 步：获取相关信息。¶

此信息可在此文件中找到：

./Data/LA_population.csv

在这里，LA 代表地方当局，而不是洛杉矶。我们希望将包含在此文件中的信息与前面计算的平均速度合并。首先，创建一个新表并将此数据存储在 InternetSpeed 数据库中。

a) 创建填充表。¶

使用 csvsql 将文件 LA_population.csv 导入 InternetSpeed 数据库。重复完成的步骤，在数据库中创建其他表。

# Code Cell 23
#first close the database file, otherwise the external csvsql command cannot use
conn.close()

# Code Cell 24
# use the external csvsql command to add to the DB file data from the CSV file
#!csvsql --db sqlite:///...

# Code Cell 25
#reopen the DB file
#conn = sqlite3.connect(...)

cur = conn.cursor()

b) 测试一切是否正常。¶

为了测试一切是否正常，并且了解这个新表中的内容，请选择前 10 行并打印它们。

# Code Cell 26
#query = ?


#cur.execute(?)


for row in cur:
    # print the first fetched record

我们可以合并这两个表，以获得所需的所有信息。这个想法的前提是两个表有一个共同的字段：区域名称。这是我们合并的键值。这意味着我们需要具有用于每个区域名称的行的新表，其中将包含平均速度信息和地理位置信息。SQL 的做法是逐行比较，并将具有相同键值（即相同的区域名称）的行合并在一起。

c) 执行 JOIN 查询并打印结果的前 10 行。¶

# Code Cell 27
query = 'SELECT * FROM average_speed JOIN LA_population ON LA_population."LA_code"=average_speed.Area'

cur.execute(query)
k = 0
for row in cur:
    if k>10:
        break
    #print ?
    
    k+=1