数据预处理|4 数据库

数据库是有效和高效地记录和检索数据的技术解决方案。在本章中，我们将首先讨论数据库在有效分析和预处理中的技术作用。然后，我们将列举并了解不同类型的数据库。最后，我们将介绍连接到数据库并从数据库中提取数据的五种不同方法。本章将涉及以下主题。

• • 什么是数据库？

• • 数据库的类型

• • 连接到数据库并从数据库中提取数据

image.png

1 什么是数据库？

数据库可能有少数不同的定义，这些定义可能都是正确的，但有一个定义最能满足数据分析的目的。数据库是一种技术解决方案，用于有效和高效地存储和检索数据。虽然数据库确实是数据分析的技术基础，但有效的分析并不发生在数据库内，这是件好事。我们希望数据库能够擅长于它们所要做的事情：有效和高效地存储和检索数据。我们希望数据库是快速、准确和安全的。我们还希望数据库能够满足我们在快速共享和同步方面的需求。当我们想从数据库中获取一些数据用于分析时，我们很容易忘记数据库并不是为我们的分析目的而设计的。所以，数据库中的数据被组织起来为其功能服务--有效和高效地存储和检索数据--而不是为我们的分析目的而组织起来，这不应该是一个惊喜。数据分析的最初步骤之一是定位和收集来自不同数据库和来源的数据，并将其重组为一个有可能回答我们决策环境问题的数据集。下图说明了数据分析的这一重要步骤。

image.png

有时，数据可能来自一个数据库，但同样，数据需要被重组为一个数据集，以满足我们的分析需求。当我们将数据重组为一个数据集时，我们需要密切关注数据集的数据对象的定义。我们定义数据集的数据对象，使数据集服务于我们分析的需要。

1.1 了解数据库和数据集之间的区别

数据库和数据集不是同一个概念，但经常被错误地互换使用。我们确实把数据库定义为一个有效和高效地存储和检索数据的技术解决方案。然而，数据集是为了特定的原因而对一些数据进行的特定组织和呈现。对于数据分析来说，虽然数据来自于数据库，但它最终会被重新组织成一个数据集。这种数据集的 "具体原因 "是分析目标，而该数据集的 "具体组织和呈现 "是为了支持这些目标。例如，我们想用温度、湿度和风速等天气数据来预测Redlands市每小时的耗电量。对于这样的分析，我们需要一个数据集，其数据对象的定义是Redlands市的一个小时。属性将是平均温度、平均湿度、平均风速和耗电量。请注意，所有这些属性都描述了数据对象--雷德兰兹市的一个小时。这就是数据集的设计，支持根据天气数据预测雷德兰兹市每小时耗电量的分析目标。在雷德兰兹市，天气数据和电力数据来自不同的数据库。天气数据来自5个数据库，这些数据库收集了全市5个地点的数据，每个数据库每15分钟记录一次其周围的天气数据。电力数据来自该市唯一的电力供应商，其数据库每5分钟记录一次该市的用电量。这六个数据库中的数据需要被整理并重新组织成所述的数据集，这样才有可能根据天气预测每小时的用电量。

2 数据库的类型

主要有四种类型的数据库。

1. 1. 关系型数据库（或SQL数据库）

2. 2. 非结构化数据库（NoSQL）

3. 3. 分布式数据库

4. 4. 区块链

这些数据库之间的区别在技术上和实践中都不是一刀切的。例如，分布式数据库本质上是多个地点的不同类型数据库的组合。在这里，我们将讨论这些类型的数据库，以便对数据库根据情况的需要组织数据的方式有更好的了解。我们还将简要地谈一谈这些类型的数据库的异同和优劣。

为什么我们需要知道数据预处理的数据库类型？四种类型的数据库各自以不同的方式组织和存储数据。由于我们的数据分析之旅总是涉及到从各种数据库中定位和收集数据，了解不同种类的数据库有两个重要目的。首先，通过了解什么是可能的，我们可能会在寻找数据的时候设想到可能有什么。第二，更重要的是，当我们想把相关数据重新组织到我们设计的数据集中时，我们需要先了解其来源的组织和结构。

2.1 数据库的差异化要素

在讨论这四种类型的数据库之前，我们先谈谈可能需要使用各种数据库的要素是什么。这些要素是结构水平、存储位置和权限。

2.1.1 数据结构的水平

没有结构的数据是一堆没有用途或意义的符号。因此，不要让 "非结构化数据库 "这个词迷惑你，因为每一个可用的数据至少需要一些结构。数据的结构化程度越高，使用时需要的处理就越少。然而，结构化数据是昂贵的，而且并不总是明智的。当数据被结构化时，不仅有可能占用更多的空间，而且在数据被记录之前，还需要资源来预处理和处理数据。另一方面，当数据在一个场合被充分结构化后，它就可以被反复使用。所以，确定数据需要多少结构的方法是考虑到结构化的成本和收益。例如，虽然结构化作为许多企业核心资产的基本客户数据的好处很容易超过其成本，但在许多情况下，结构化客户电子邮件、语音和社交媒体数据的成本对中小型企业来说可能显得过于庞大。下图显示了结构化数据的成本和收益之间的互动。随着数据的结构化，自然，结构化的成本也会上升。但作为回报，每天处理非结构化数据的成本也会下降，直到数据结构化的收益达到顶峰。通过考虑成本和收益，我们可以找到合适的数据结构水平。

image.png

结构化数据的最佳水平会因情况不同而不同，也会因数据不同而不同。例如，有些数据，如视频、声音和社交媒体数据，每次用于不同的目的时，都可能需要进行特定的预处理。这意味着每次使用时，无论如何都需要经过数据重组，因此对数据进行结构化处理不会带来任何好处，也不具有经济意义。此外，这些类型的数据往往很大，而且只有其中一个独特的部分需要不时地被结构化，而我们事先并不知道是哪一部分。在这种情况下，提前对整个数据进行结构化并不经济，因为我们不知道将来需要对哪一部分数据进行结构化。下图显示了这种特殊情况。

image.png

2.1.2 存储位置

数据库所处的地理位置也很重要，原因有很多，包括数据安全、数据可用性、数据可及性，当然还有运营成本。

2.1.3 权限

在选择什么类型的数据库时，有两个关键的权力问题是非常重要的，需要考虑。1. 该数据属于谁？2. 谁应该有权力更新它？

2.2 关系型数据库（SQL数据库）

关系型数据库，或称结构化数据库，是一个数据收集和管理的生态系统，其中收集的数据和传入的数据都必须符合一套预先定义的数据之间的关系。对于关系型数据库来说，如果传入的数据在关系型数据库中不被期待，那么这些数据就不能被存储。在数据库生态系统以这种方式更新之前，这些类型的传入数据在新的生态系统中是被期待的。有些类型的数据是如此的不同，以至于更新数据库的生态系统使其被预期，只会阻碍数据库的目标。此外，对于某些类型的数据，我们可能不确定是否要对它们进行足够的投资来改变它们的生态系统。对于视频、语音、文本和社交媒体数据来说，往往是这种情况，这些数据往往具有很大的规模。对于这些类型的数据，我们放弃改变关系型数据库来适应它们，而把它们存储在不需要那么多结构化的数据库类型中。

2.3 非结构化数据库(NoSQL数据库)

NoSQL，或者说非结构化数据库，正是为了解决想要存储我们无法结构化的数据，或者说是矛盾的问题。此外，非结构化数据库可以作为我们现在没有资源来结构化的数据的一个临时住所。当然，"非结构化数据库 "这个词并不是字面意思。完全非结构化的数据是一堆没有价值的符号和象征。术语 "非结构化数据库 "是为了与关系型数据库形成区别而出现的。下面的例子展示了结构化和非结构化数据之间的实际区别，以及它们在一家律师事务所的不同应用。

2.3.1 一个需要结合结构化和非结构化数据库的实际例子

塞夫及合伙人律师事务所自1956年以来一直活跃在民事和刑事法律领域。在过去，该事务所对每一份法律文件、每一份备忘录、每一次上诉、每一张发票等都保留了一份纸质副本。1998年，该公司经历了一次重大的IT检修，并创建了一个关系数据库，记录所有的法律和商业活动。支持该事务所的关系数据库是高度结构化的，并允许该事务所提供四种不同类型的报告，只有这样一个高度结构化的数据库才允许。例如，该数据库可以报告每个律师助理每月分配的法律任务。所有寄给法院的文件和寄给客户的发票都不是数据库中的数据对象，而是根据数据库的需求产生的。例如，一张发票每次都是通过检查数据库中的发票号码读取与发票相关的项目和价格而产生的。一旦在数据库中找到所有这些数据，一个软件就会把它们放在一起，每次都会打印出一张发票。由于1998年的重大IT检修本身就是一项重要的工作，该公司从未有机会将1956年至1998年的纸质发票数字化。然而，一年前，该公司决定解除自己的负担，不必携带所有这些物理副本。现在，该公司将这些文件的扫描版保存在一个非结构化的数据库中。尽管数据都在非结构化数据库中，但无法从这个数据库中获得详细的报告。一家人工智能公司最近与该公司接触，并建议他们拥有技术来翻阅1956年至1998年的文件的数字副本，并将它们纳入结构化数据库。该公司的结论是，结构化这些数据的成本（人工智能公司的报价）并不符合或超过结构化数据可能带来的好处。因此，该公司决定为这些记录建立一个非结构化数据库就足够了，因为这些记录只是为了法律目的而记录，如果需要这些文件，非结构化数据库有足够的索引，所以5到10分钟就能找到这些文件。

2.4 分布式数据库

当我们想到结构化或非结构化数据库时，我们通常假设每个数据库都位于一个站点或一台计算机的物理位置。然而，这很容易成为一个不正确的假设。一个数据库有多个地点/站点/计算机的原因有很多，比如更高的数据可用性，更低的运营成本，以及更高的数据安全性。简单地说，分布式数据库是一个数据库的集合（结构化的、非结构化的，或两者的组合），其数据被物理地存储在多个地点。然而，对终端用户来说，它感觉只是一个数据库。云计算的基础是分布式数据库。例如，亚马逊网络服务（AWS）是一个巧妙地连接世界各地的分布式数据库的网络，提供具有高可用性和安全性的数据库空间，并根据客户的实际使用情况收费。

2.5 区块链

我们通常假设一个数据库由一个人或一个组织拥有。虽然在许多情况下这是一个正确的假设，但当中央所有权和权威性不具优势时，区块链是一种解决方案。例如，这就是比特币成为数字货币的一个竞争性选择的众多原因之一。虽然银行的中央数据库权威为数据安全提供了一些保证，但银行也将拥有技术权威，如果他们认为有必要，可以切断客户的资金。然而，区块链是一个没有中央权威的数据库替代品，同时提供数据安全。区块链的缺点是，其所有数据都储存在区块中，每个区块只能容纳少量信息。此外，关系型数据库容易产生的复杂而详细的报告，区块链也无法创建。

3 连接到数据库，并从数据库中提取数据

对于数据分析和数据预处理，我们需要拥有连接到数据库并从数据库中提取我们想要的数据的技能。有几种方法可以做到这一点。在本节中，我们将介绍这些方法，分享它们的优点和缺点，并在例子的帮助下，我们将看到这是如何做到的。我们将介绍连接数据库的五种方法：直接连接、网页连接、API连接、请求连接和公开共享。

3.1 直接连接

当你被允许直接访问一个数据库时，这意味着你可以从数据库中提取任何你想要的数据。这是一种从数据库中提取数据的好方法，但有两个主要缺点。首先，你很少被允许直接访问数据库，除非你被数据库的所有者完全信任。第二，你需要具备与数据库互动的技能，以便从数据库中提取数据。你需要知道的连接到关系型数据库的脚本被称为结构化查询语言（SQL）。在SQL中，每次你想从数据库中提取数据时，你都要用SQL语言写一个查询。在W3Schools.com可以免费获得学习SQL的一个很好的资源：https://www.w3schools.com/sql/。

对初学者学习SQL的建议：如果你不熟悉SQL，至少要确定知道以下概念。SQL表、主键和外键，以及以下运算符。SELECT, DISTINCT, WHERE, AND, OR, ORDER BY, LIKE, JOIN, GROUP BY, COUNT(), MIN(), MAX(), AVE(), SUM(), HAVING, 和 CASE。https://www.w3schools.com/sql/ 可以帮助你学习上述主题。

当你写了一个正确的查询，你需要以某种方式将其发送到数据库，并能够得到结果，为此，你需要一个与数据库的连接。没有一种方法可以创建与数据库的连接。有一种具有交互式用户界面（UI）的软件可以为你做到这一点。这类软件的例子有Microsoft Access、SQL Server Management Studio（SSMS）和SQLite。好消息是，我们也可以使用Python模块sqlite3创建一个与数据库的连接。我们将使用Chinook样本数据库来练习使用Python和sqlite3模块连接到数据库。下图显示了使用统一建模语言(UML)的Chinook数据库。这个样本数据库有11个表，通过它们的主键相互连接，创建一个数据库，旨在支持一个销售音乐曲目的小型/中型企业。数据库的UML有助于理解表之间的连接，并设计查询，以便从数据库中提取数据。

image.png

下面的截图显示了结合使用pandas和sqlite3模块来创建一个与数据库的连接，并从数据库中读取数据到pandas DataFrame。该代码为此采用了函数pd.read_sql_query()。这个函数需要两个输入：一个字符串形式的查询和一个连接。代码使用sqlite3.connect()函数来创建一个连接，然后将Connection和query_txt传给pd.read_sql_query()，以便在DataFrame中获得请求的数据。

image.png

image.png

import sqlite3 
import pandas as pd 

Connection = sqlite3.connect('chinook.db')
query_text = 'SELECT * FROM customers;'

df_customers = pd.read_sql_query(query_text,Connection)
df_customers.head()

image.png

3.2 网页连接

有时，数据库的所有者只希望让你对他们的数据库进行有控制的访问。由于这些类型的访问是受控的，所以数据共享是按照所有者的条件进行的。例如，所有者可能希望让你访问他们数据库的某一部分。此外，所有者可能不希望你能够一次性提取所有你需要的数据，而是分时段提取。网页连接是数据库所有者在提供对其数据库的控制访问时可以使用的方法之一。在londonair.org.uk/london/asp/datadownload.asp可以看到一个很好的网页连接的例子，并与之互动。在你打开这个页面后，你可以选择一个特定的地点或一个特定的测量。无论你的选择是什么，这个网页都会带你到另一个页面，在向你展示图表和提供CSV数据集之前，需要你提供更多的输入。在继续阅读之前，去这个网页尝试不同的输入并下载一些数据集。

image.png

image.png

3.3 API连接

给出对数据库的受控访问的第二种方法是提供API连接。然而，与网页连接方法不同的是，网页会浏览并响应你的请求，而通过API连接，网络服务器会处理你的数据请求。通过API连接共享数据的一个很好的例子是股票市场的数据。不同的网络服务为用户提供免费和或基于订阅的API，以获得实时的股票市场数据。

3.3.1 使用API连接和提取数据的例子

Finnhub股票API（finnhub.io）是这种网络服务的一个好例子。Finnhub提供对其数据库的免费和基于订阅的访问。你可以访问和使用他们的基本股市数据，如美国股票价格的每日、每小时和每分钟的数据。通过他们的免费版访问，你可以请求他们的基本数据，如股票价格，你每分钟最多可以发送60个请求。如果你需要每分钟处理超过60个请求，或者你想要免费访问中不包括的数据，你将不得不订阅。Finnhub的免费版本足以让我们练习通过API访问数据。首先，在finnhub.io的第一页，点击获取免费API密钥，为自己获取一个API密钥。第二，在你的Jupyter笔记本中输入以下代码，将API_Key从任意的'abcdefghijklmnopq'改为你从finnhub.io得到的免费API密钥。如果你每一步都做得正确，你会得到<Response [200]>打印出来，这意味着一切都很顺利。通过这段代码，你连接到Finnhub的网络服务器，并收集了一些数据。现在，让我们一起剖析这段代码。每个API请求都需要用一个网络地址来表达。这是放之四海而皆准的；对于不同的网络服务器，你应该把你的请求翻译成网络地址的方式可能有些不同，但它们是非常相似的。如果你已经运行了接下来的代码，当你执行print(API_address)时，就像下面的截图中实现的那样，你会看到声称拥有ccc4rcaad3i8urq8nqj0的API密钥并要求提供2020年1月1日至12月30日的亚马逊每周股票价格的网址。在继续阅读之前，请研究该网址，并找出该地址的每一段。下面的要点列出并解释了网站地址的不同部分。

• • symbol=AMZN指定你要的是带有股票代码AMZN的价格，这表示亚马逊。

• • resolution=W指定你想要每周的价格。你可以要求每分钟、每5分钟、每15分钟、每半小时、每小时、每天、每周和每月的价格，分别使用1、5、15、30、60、D、W和M。

• • from=1577865600 指定你想要数据的时间。这个看起来奇怪的数字是2020年1月1日的时间戳。

• • to=1609315200 指定你想要的数据的时间。这个看起来奇怪的数字是2020年12月30日的时间戳。

• • token=ccc4rcaad3i8urq8nqj0指定这个地址的API密钥。

import requests

stk_ticker = 'AMZN'
data_resolution = 'W'
timestamp_from = 1577865600
timestamp_to = 1609315200
API_Key = 'ccc4rcaad3i8urq8nqj0'
Address_template = 'https://finnhub.io/api/v1/stock/candle?symbol={}&resolution={}&from={}&to={}&token={}';

API_address = Address_template.format(stk_ticker,data_resolution,
                                      timestamp_from,timestamp_to,API_Key)
r = requests.get(API_address)
print(r) #<Response [200]>

print(API_address)
# https://finnhub.io/api/v1/stock/candle?symbol=AMZN&resolution=W&from=1577865600&to=1609315200&token=ccc4rcaad3i8urq8nqj0

print(r.json())

image.png

输出结果基本上显示了亚马逊股票价格的51周数据。下面的列表显示了每个字母所代表的含义。

• • c'：该期间的收盘价

• • 'h'：该期间的最高价

• • 'l'：该期间的最低价

• • 'o'：该期间的开盘价

• • 's'：股票的状态

• • 't'：显示该期间结束的时间戳

• • 'v'：该期间的交易量 当以这种格式呈现时，处理股票数据并不容易。然而，将其转换为你所习惯的格式是很容易的。运行以下代码并研究其输出。

from datetime import datetime
AMZN_df = pd.DataFrame(r.json())
AMZN_df.head()

image.png

Unix的时间戳是指某一特定日期与1970年1月1日UTC之间的秒数。

AMZN_df.drop(columns=['s'],inplace=True)
# Python 的 date.fromtimestamp(~) 方法将 Unix 的时间戳转换为日期对象。
AMZN_df.t = AMZN_df.t.apply(datetime.fromtimestamp)
AMZN_df.t = AMZN_df.t.apply(lambda v:v.date())
AMZN_df.set_index('t',drop=True,inplace=True)
AMZN_df.columns = ['Closing','High','Low','Opening','Volume']
df.head()

image.png

3.4 请求连接

这种类型的数据库连接发生在你没有前述三种方法中的任何一种对感兴趣的数据库的访问权，但你知道有人有访问权并被授权与你分享某些部分的数据。在这种方法中，你需要清楚地说明你需要从数据库中获得什么数据。

3.5 公开共享

这种连接数据库的方法是最不灵活的。在公开共享的方法下，数据库的所有者从他们拥有的数据库中提取了一个数据集，并提供了对这一个数据集的访问。例如，你在kaggle.com上找到的几乎所有数据集都属于这种连接到数据库的方法。此外，data.gov下提供的大部分数据访问也属于这种不灵活的数据库访问。

3.6 数据库连接方法的排名

|
| 直接连接 | 网页连接 | API连接 | 请求连接 | 公开共享 |
| --- | --- | --- | --- | --- | --- |
| 访问的灵活性 | 1 | 2 | 2 | 4 | 5 |
| 容易出现人际沟通不畅的情况 | 5 | 5 | 5 | 1 | 5 |
| 需要高水平的技术技能组合 | 1 | 3 | 2 | 4 | 5 |
| 需要了解数据库表的情况 | 1 | 5 | 5 | 5 | 5 |
| 最快获取所需数据 | 1 | 2 | 2 | 5 | 4 |
| 更加便于编写代码 | 1 | 5 | 2 | 5 | 5 |
| 对数据库的可能性的认识 | 1 | 3 | 3 | 2 | 5 |
| 最不费时的数据提取 | 1 | 3 | 2 | 5 | 4 |
| 最高级别的数据库安全性 | 4 | 2 | 2 | 3 | 1 |

4 练习

以奇努克数据库为例，我们想调查并找到以下问题的答案。使用积极词汇作为标题的曲目是否比使用消极词汇作为标题的曲目平均销量更好。我们希望在调查中只关注以下词语。负面词汇的清单。['Evil', 'Night', 'Problem', 'Sorrow', 'Dead', 'Curse', 'Venom', 'Pain', 'Lonely', 'Beast']积极词汇列表。['Amazing', 'Angel', 'Perfect', 'Sunshine', 'Home', 'Live', 'Friends'] a. 使用Sqlite3连接到Chinook数据库并执行以下查询。SELECT * FROM tracks join invoice_items on tracks.TrackId = invoice_items.TrackId b. 使用你在前几章学到的技能（应用函数、按函数分组等），得出一个表格，列出含有好词的曲目的平均总销售额，含有坏词的曲目也是如此。

import sqlite3 
import pandas as pd 

Connection = sqlite3.connect('chinook.db')
query_text = 'SELECT * FROM tracks join invoice_items on tracks.TrackId = invoice_items.TrackId'

df_customers = pd.read_sql_query(query_text,Connection)
df_customers.head()

image.png

df_customers.Name = df_customers.Name.astype(str).apply(lambda x:x.lower())
df_customers['name'] = df_customers.Name.apply(lambda x:x.strip().split(' '))
def judge(x):
    negative_words = ['Evil', 'Night', 'Problem', 'Sorrow', 'Dead', 'Curse', 'Venom', 'Pain', 'Lonely', 'Beast']
    positive_words = ['Amazing', 'Angel', 'Perfect', 'Sunshine', 'Home', 'Live', 'Friends']
    positive_words = [s.lower() for s in positive_words]
    negative_words = [s.lower() for s in negative_words]
    for i in x:
        if i in negative_words:
            return 'Negative'
        elif i in positive_words:
            return 'Positive'
        else:
            return 'Neither'
df_customers.name = df_customers.name.apply(judge)
df_customers.head()

image.png

df_summary = df_customers.groupby('name',as_index=False)['Quantity'].mean()
df_summary.columns = ['MusicTitleType','TotalSale']

df_summary

image.png