教程 > 使用 sqlite-utils 和 Datasette 清理数据

使用 sqlite-utils 和 Datasette 清理数据

本教程将向您展示如何使用 sqlite-utils 命令行工具将数据导入 SQLite，对其进行清理，然后使用 Datasette 探索它。

本教程中的概念也在我于 2022 年 4 月在 HYTRADBOI 会议上的一次演讲视频中进行了演示。

从一些 CSV 数据开始

佛罗里达鱼类和野生动物保护委员会维护着一个海牛尸体回收地点数据库，详细信息可追溯到 1974 年 4 月。

您可以从他们的网站请求最新的 CSV 文件副本 - 或下载这份 CSV 副本：Manatee_Carcass_Recovery_Locations_in_Florida.csv，该文件生成于 2022 年 7 月 31 日。

安装 sqlite-utils

这里是安装说明。如果您在安装了 Homebrew 的 Mac 上，可以运行 brew install sqlite-utils。Python 用户可以运行 pip install sqlite-utils。

将 CSV 导入 SQLite 数据库

sqlite-utils 是一个命令行工具，因此您需要在计算机上打开终端，并切换到保存 CSV 文件的目录。

您可以像这样创建一个新的 manatees.db SQLite 数据库

sqlite-utils insert manatees.db locations \
  Manatee_Carcass_Recovery_Locations_in_Florida.csv --csv -d

此 sqlite-utils insert 命令将数据插入数据库表，并在必要时创建表。

  • manatees.db 是我们正在使用的 SQLite 数据库文件 - 如果它尚不存在，将创建它。
  • locations 是我们正在创建的表的名称
  • Manatee_Carcass_Recovery_Locations_in_Florida.csv 是我们正在导入的 CSV 文件
  • --csv 告诉工具将输入数据视为 CSV 数据
  • -d 选项告诉工具自动检测数据类型。如果没有此选项，每列将被视为文本。使用此选项，包含数字数据的列将用于填充数字列。

创建数据库后，我们可以使用 schema 命令查看其模式

sqlite-utils schema manatees.db

该命令的输出如下所示

CREATE TABLE "locations" (
   [X] FLOAT,
   [Y] FLOAT,
   [OBJECTID] INTEGER,
   [FIELDID] TEXT,
   [REPDATE] TEXT,
   [REPYEAR] INTEGER,
   [REPMONTH] INTEGER,
   [REPDAY] INTEGER,
   [SEX] TEXT,
   [TLENGTH] FLOAT,
   [STATE] TEXT,
   [COUNTY] TEXT,
   [LAT] FLOAT,
   [LONG_] FLOAT,
   [DCODE] INTEGER,
   [MORTALITY] TEXT,
   [created_user] TEXT,
   [created_date] TEXT,
   [last_edited_user] TEXT,
   [last_edited_date] TEXT
);

此处的 locations 表已创建，其列与 CSV 文件中存在的列匹配。由于我们传递给 sqlite-utils insert 的 -d 选项，这些列具有适当的类型。

我们可以使用 sqlite-utils tables 命令查看这些表

sqlite-utils tables manatees.db --counts

输出为

[{"table": "locations", "count": 13568}]

--counts 选项会添加每个表中的行数计数。那是超过 13,000 只已故的海牛！

默认情况下，tables 命令输出 JSON。您可以使用 --table 或 -t 选项将其更改为表格格式

sqlite-utils tables manatees.db --counts -t
table        count
---------  -------
locations    13568

查询数据

让我们对您的数据运行 SQL 查询。用于执行此操作的 sqlite-utils 命令如下所示

sqlite-utils manatees.db "select * from locations limit 2"

这将输出 JSON，显示前两行和所有列（感谢 select *）

[{"X": -80.1176098026034, "Y": 26.1004059191504, "OBJECTID": 149433, "FIELDID": "M7401", "REPDATE": "1974/04/03 00:00:00+00", "REPYEAR": 1974, "REPMONTH": 4, "REPDAY": 3, "SEX": "F", "TLENGTH": 260.0, "STATE": "FL", "COUNTY": "Broward", "LAT": 26.100401, "LONG_": -80.117607, "DCODE": 9, "MORTALITY": "Undetermined: Other", "created_user": "FWC", "created_date": "2021/10/15 11:30:55+00", "last_edited_user": "FWC", "last_edited_date": "2021/10/15 11:30:55+00"},
 {"X": -80.1664354041335, "Y": 25.8337161801256, "OBJECTID": 149434, "FIELDID": "M7402", "REPDATE": "1974/06/27 00:00:00+00", "REPYEAR": 1974, "REPMONTH": 6, "REPDAY": 27, "SEX": "M", "TLENGTH": 290.0, "STATE": "FL", "COUNTY": "Miami-Dade", "LAT": 25.833711, "LONG_": -80.166433, "DCODE": 1, "MORTALITY": "Human Related: Watercraft Collision", "created_user": "FWC", "created_date": "2021/10/15 11:30:55+00", "last_edited_user": "FWC", "last_edited_date": "2021/10/15 11:30:55+00"}]

让我们指定一些列，限制为 10 行，并添加 -t 将结果输出为表格。

sqlite-utils manatees.db -t \
  "select REPDATE, MORTALITY, COUNTY from locations limit 10"

输出为

REPDATE                 MORTALITY                            COUNTY
----------------------  -----------------------------------  ------------
1974/04/03 00:00:00+00  Undetermined: Other                  Broward
1974/06/27 00:00:00+00  Human Related: Watercraft Collision  Miami-Dade
1974/08/20 00:00:00+00  Human Related: Watercraft Collision  Miami-Dade
1974/10/23 00:00:00+00  Human Related: Other                 Volusia
1974/11/11 00:00:00+00  Human Related: Other                 Brevard
1974/12/25 00:00:00+00  Human Related: Watercraft Collision  Citrus
1974/12/27 00:00:00+00  Undetermined: Too Decomposed         Indian River
1975/01/01 00:00:00+00  Verified: Not Necropsied             Brevard
1975/01/19 00:00:00+00  Human Related: Watercraft Collision  Miami-Dade
1975/01/31 00:00:00+00  Verified: Not Necropsied             Brevard

在 Datasette 中打开它

Datasette 提供了一个基于浏览器的界面，用于探索 SQLite 数据库。安装它（Homebrew 用户可以使用 brew install datasette），然后针对您的数据库运行它

datasette manatees.db

然后导航到 http://localhost:8001/ 开始探索您的数据。

转换列

探索数据有助于确定我们可以对数据结构进行的改进。

LAT 和 LONG_ 列看起来像是纬度和经度。将这些列重命名为 latitude 和 longitude 将使其更明显，并且稍后还可以让我们使用 Datasette 插件可视化数据。

created_user、last_edited_user 和 STATE 列没有意义：它们总是存储相同的值。

X 和 Y 列是纬度和经度的重复。

最后，FIELDID 列似乎是数据库中每一行的唯一标识符。这将是一个很好的表主键。

sqlite-utils transform 命令可用于对表应用转换 - 重命名列、删除列、分配主键等。

以下命令将一次对表结构进行大量更改

sqlite-utils transform manatees.db locations \
  --rename LAT latitude \
  --rename LONG_ longitude \
  --drop created_user \
  --drop last_edited_user \
  --drop X \
  --drop Y \
  --drop STATE \
  --drop OBJECTID \
  --pk FIELDID

现在运行 sqlite-utils schema manatees.db 查看这些更改的结果

CREATE TABLE "locations" (
   [FIELDID] TEXT PRIMARY KEY,
   [REPDATE] TEXT,
   [REPYEAR] INTEGER,
   [REPMONTH] INTEGER,
   [REPDAY] INTEGER,
   [SEX] TEXT,
   [TLENGTH] FLOAT,
   [COUNTY] TEXT,
   [latitude] FLOAT,
   [longitude] FLOAT,
   [DCODE] INTEGER,
   [MORTALITY] TEXT,
   [created_date] TEXT,
   [last_edited_date] TEXT
);

在地图上可视化位置

Datasette 支持插件，这些插件可以启用额外功能，例如数据可视化。

datasette-cluster-map 插件对任何包含 latitude 和 longitude 列的表运行，将其显示为地图上的点。这就是我们之前重命名 LAT 和 LONG_ 列的原因！

要安装插件，请退出 Datasette 服务器（在终端窗口中使用 Ctrl+C），然后运行以下命令

datasette install datasette-cluster-map

然后再次启动 Datasette

datasette manatees.db

如果您访问 http://localhost:8001/manatees/locations，您现在将看到前 1,000 行作为地图上的点

Screenshot of Datasette showing a map of locations

点击“load all”（加载全部）按钮加载并显示所有 13,000 多个位置。

修正日期列

日期列当前看起来像这样

2021/10/15 11:30:55+00

这使用的是 YYYY/MM/DD 格式（至少没那么令人困惑 - 对欧洲人来说 - 美国人的 MM/DD/YYYY 格式）。我更喜欢 ISO 标准格式 YYYY-MM-DD - 所以我们来转换它。

sqlite-utils transform 命令可用于对一个或多个列中的值应用转换函数。

以下是使用它来解析各种日期和时间格式并将其转换为标准 ISO 格式的方法

sqlite-utils convert manatees.db locations \
  REPDATE created_date last_edited_date \
  'r.parsedatetime(value)'

这将针对 REPDATE、created_date 和 last_edited_date 列运行。它将使用内置的 r.parsedatetime() 方案（此处有文档记录）来转换这些值并将其保存回同一列。

运行该命令，然后在 Datasette 中再次查看数据。这些列现在已经转换好了！

将列提取到单独的表中

我们将对数据进行另一项改进。

在 Datasette 中打开表，然后使用建议的分面列表或列顶部的齿轮菜单按 DCODE 和 MORTALITY 进行分面分析。

Screenshot showing the data faceted by DCODE and MORTALITY - the counts of results shown for the two different facets are the same

两个分面显示的结果计数相同。这表明这两列代表相同的数据 - DCODE 为 1 对应于 MORTALITY 的“Human Related: Watercraft Collision”（人类相关：水上交通工具碰撞），2 对应于“Human Related: Flood Gate/Canal Lock”（人类相关：水闸/运河闸门）等等。

我们可以使用 sqlite-utils extract 命令将这些列提取到单独的表中。

sqlite-utils extract manatees.db locations DCODE MORTALITY \
  --rename MORTALITY name \
  --table mortality

运行此命令将创建一个名为 mortality 的新表，并为数据库中的每个 DCODE 和 MORTALITY 对填充一行。它会从 locations 表中移除这些列，并将其替换为一个指向新表的 mortality_id 外键列。

这是 sqlite-utils schema manatees.db 的输出，显示了这些更改对模式的影响

CREATE TABLE [mortality] (
  [id] INTEGER PRIMARY KEY,
  [DCODE] INTEGER,
  [name] TEXT
);
CREATE TABLE "locations" (
  [FIELDID] TEXT PRIMARY KEY,
  [REPDATE] TEXT,
  [REPYEAR] INTEGER,
  [REPMONTH] INTEGER,
  [REPDAY] INTEGER,
  [SEX] TEXT,
  [TLENGTH] FLOAT,
  [COUNTY] TEXT,
  [latitude] FLOAT,
  [longitude] FLOAT,
  [mortality_id] INTEGER,
  [created_date] TEXT,
  [last_edited_date] TEXT,
  FOREIGN KEY([mortality_id]) REFERENCES [mortality]([id])
);
CREATE UNIQUE INDEX [idx_mortality_DCODE_name]
   ON [mortality] ([DCODE], [name]);

延伸阅读

sqlite-utils 提供了更多用于清理数据的工具。请查阅此文档和这些博文了解更多详情。

更多教程