使用 duckplyr 分析开放政府数据
TL;DR（太长不看）：我们使用 duckplyr R 库来清理和分析新西兰政府发布的一个开放数据集。
有关 duckplyr 文档，请访问
duckplyr.tidyverse.org。
在 R 中进行交互式数据分析时，通过扔出 SQL 字符串来整理数据并不是最符合人体工程学的方式。一段时间以来，我们一直与 Posit（原 RStudio）和 Kirill Müller 的 dplyr 项目团队合作，开发了 duckplyr。duckplyr 是一个高性能的 dplyr 直接替代品，由 DuckDB 提供支持。您可以在发布博文中阅读更多关于 duckplyr 的信息。在这篇文章中，我们将通过一个具有挑战性的真实世界用例来演示 duckplyr。对于希望跟着操作的读者，我们准备了一个 Google Colab 笔记本，其中包含本文中的所有代码片段。下面报告的时间数据也来自 Colab。
与许多政府统计机构一样，新西兰的“Stats NZ Tatauranga Aotearoa”值得称赞地提供了他们维护的一些数据集作为开放数据供下载。该页面上可供下载的最大文件包含“按族裔群体（分组总响应）划分的年龄和性别，针对 2006 年、2013 年和 2018 年人口普查的常住人口统计数据”，CSV 压缩文件。
我们可以下载该文件（从我们的 CDN 镜像，我们不想 DDoS Stats NZ）并像这样解压缩：
download.file("https://blobs.duckdb.org/nzcensus.zip", "nzcensus.zip")
unzip("nzcensus.zip")
让我们探索一下 zip 文件中的 CSV 文件及其大小：
file.info(Sys.glob("*.csv"))["size"]
size
Data8277.csv 857672667
DimenLookupAge8277.csv 2720
DimenLookupArea8277.csv 65400
DimenLookupEthnic8277.csv 272
DimenLookupSex8277.csv 74
DimenLookupYear8277.csv 67
正如我们所看到的，有一个大型的（约 800 MB）Data文件和一堆Dimen...维度文件。这是一种相当常见的数据布局，有时被称为“星型模式”。由此可见，未来我们会有一些连接操作。但首先，我们关注主文件Data8277.csv。读取大型 CSV 文件并非易事，可能非常令人沮丧。但是，正如新西兰人会说的，抱怨够了。
首先，我们快速看看文件长什么样：
cat(paste(readLines("Data8277.csv", n=10), collapse="\n"))
Year,Age,Ethnic,Sex,Area,count
2018,000,1,1,01,795
2018,000,1,1,02,5067
2018,000,1,1,03,2229
2018,000,1,1,04,1356
2018,000,1,1,05,180
2018,000,1,1,06,738
2018,000,1,1,07,630
2018,000,1,1,08,1188
2018,000,1,1,09,2157
到目前为止，这看起来相当简单，似乎有五列。幸运的是，它们有名称。仅凭目测列值，它们似乎都是数值甚至整数值。然而，外表可能具有欺骗性，Age、Area、count这几列在后面某个地方包含字符值。有趣的是：我们必须等到第 431,741 行，Area列才包含非整数值。显然我们需要一个好的 CSV 解析器。R 中不乏 CSV 读取器，例如readr包包含一个灵活的 CSV 解析器。用readr读取这个文件大约需要一分钟（在 Colab 上）。
但现在让我们开始使用 DuckDB 和 duckplyr。首先，我们安装 duckplyr（以及作为其依赖项的 DuckDB）：
install.packages("duckplyr")
duckdb:::sql("SELECT version()")
此命令打印出已安装的 DuckDB 版本，截至本文撰写时，CRAN上的最新版本是 1.1.0。我们现在可以使用 DuckDB 的高级数据整理功能。首先，DuckDB 包含可能是世界上最先进的 CSV 解析器。对于额外好奇的读者，这里有一个关于 DuckDB CSV 解析器的演示。我们使用 DuckDB 的 CSV 读取器仅从 CSV 文件中读取前 10 行：
duckdb:::sql("FROM Data8277.csv LIMIT 10")
Year Age Ethnic Sex Area count
1 2018 000 1 1 01 795
2 2018 000 1 1 02 5067
3 2018 000 1 1 03 2229
4 2018 000 1 1 04 1356
5 2018 000 1 1 05 180
6 2018 000 1 1 06 738
7 2018 000 1 1 07 630
8 2018 000 1 1 08 1188
9 2018 000 1 1 09 2157
10 2018 000 1 1 12 177
这只需几毫秒，因为 DuckDB 的 CSV 读取器以流式方式生成结果，并且由于我们只请求了 10 行，因此很快就完成了。
DuckDB 还可以使用 DESCRIBE 关键字打印它从 CSV 文件中检测到的模式：
duckdb:::sql("DESCRIBE FROM Data8277.csv")
column_name column_type ...
1 Year BIGINT ...
2 Age VARCHAR ...
3 Ethnic BIGINT ...
4 Sex BIGINT ...
5 Area VARCHAR ...
6 count VARCHAR ...
我们可以看到，我们已经正确检测到各列的不同数据类型。我们可以使用 SUMMARIZE 关键字计算文件中所有列的各种摘要统计信息：
duckdb:::sql("SUMMARIZE FROM Data8277.csv")
这将需要更长一点的时间，但结果非常有趣：
# A tibble: 6 × 12
column_name column_type min max approx_unique avg std q25 q50
<chr> <chr> <chr> <chr> <dbl> <chr> <chr> <chr> <chr>
1 Year BIGINT 2006 2018 3 2012.33… 4.92… 2006 2013
2 Age VARCHAR 000 999999 149 NA NA NA NA
3 Ethnic BIGINT 1 9999 11 930.545… 2867… 3 6
4 Sex BIGINT 1 9 3 4.0 3.55… 1 2
5 Area VARCHAR 001 DHB9999 2048 NA NA NA NA
6 count VARCHAR ..C 9999 16825 NA NA NA NA
# ℹ 3 more variables: q75 <chr>, count <dbl>, null_percentage <dbl>
这将再次显示列名及其类型，还会显示最小值和最大值、唯一值的近似计数、平均值、标准差、25、50 和 75 百分位数以及 NULL/NA 值的百分比等摘要统计信息。因此，可以很好地了解数据是什么样的。
但我们来这里不是为了盯着摘要统计数据，我们想对数据进行实际分析。在这个用例中，我们想计算 2018 年人口普查数据中居住在奥克兰地区、年龄在 20 到 40 岁之间的非欧洲裔人口数量，结果应按性别分组。为此，我们需要将维度 CSV 文件与主数据文件连接起来，以便正确过滤维度值。在大型数据分析的通用语言 SQL 中，它看起来是这样的：
我们首先将所有内容连接在一起：
FROM 'Data8277.csv' data
JOIN 'DimenLookupAge8277.csv' age ON data.Age = age.Code
JOIN 'DimenLookupArea8277.csv' area ON data.Area = area.Code
JOIN 'DimenLookupEthnic8277.csv' ethnic ON data.Ethnic = ethnic.Code
JOIN 'DimenLookupSex8277.csv' sex ON data.Sex = sex.Code
JOIN 'DimenLookupYear8277.csv' year ON data.Year = year.Code
接下来，我们使用 SELECT 投影执行一些基本的重命名和数据清理：
SELECT
year.Description AS year_,
area.Description AS area_,
ethnic.Description AS ethnic_,
sex.Description AS sex_,
TRY_CAST(replace(age.Description, ' years', '') AS INTEGER) AS age_,
TRY_CAST(data.count AS INTEGER) AS count_
数据集包含各种总计，因此我们在继续之前将其删除：
WHERE count_ > 0
AND age_ IS NOT NULL
AND area_ NOT LIKE 'Total%'
AND ethnic_ NOT LIKE 'Total%'
AND sex_ NOT LIKE 'Total%'
我们将之前的语句封装为一个公共表表达式 expanded_cleaned_data，然后可以使用 DuckDB 计算实际的聚合：
SELECT sex_, sum(count_) AS group_count
FROM expanded_cleaned_data
WHERE age_ BETWEEN 20 AND 40
AND area_ LIKE 'Auckland%'
AND ethnic_ <> 'European'
AND year_ = 2018
GROUP BY sex_
ORDER BY sex_
这在 Colab 免费层有限的计算资源上大约需要 20 秒。结果是：
sex_ group_count
1 Female 398556
2 Male 397326
到目前为止，一切顺利。然而，编写 SQL 查询并非适合所有人。在像 R 这样的交互式数据分析环境中创建 SQL 字符串的人机工程学至少可以说是有问题的。dplyr 等框架已经展示了如何大大改进数据整理的人机工程学。让我们在首先将数据从 CSV 读入 RAM 后，用 dplyr 表达我们的分析：
library(dplyr)
data <- readr::read_csv("Data8277.csv")
age <- readr::read_csv("DimenLookupAge8277.csv")
area <- readr::read_csv("DimenLookupArea8277.csv")
ethnic <- readr::read_csv("DimenLookupEthnic8277.csv")
sex <- readr::read_csv("DimenLookupSex8277.csv")
year <- readr::read_csv("DimenLookupYear8277.csv")
expanded_cleaned_data <- data |>
filter(grepl("^\\d+$", count)) |>
mutate(count_ = as.integer(count)) |>
filter(count_ > 0) |>
inner_join(
age |>
filter(grepl("^\\d+ years$", Description)) |>
mutate(age_ = as.integer(Code)),
join_by(Age == Code)
) |>
inner_join(area |>
mutate(area_ = Description) |>
filter(!grepl("^Total", area_)), join_by(Area == Code)) |>
inner_join(ethnic |>
mutate(ethnic_ = Description) |>
filter(!grepl("^Total", ethnic_)), join_by(Ethnic == Code)) |>
inner_join(sex |>
mutate(sex_ = Description) |>
filter(!grepl("^Total", sex_)), join_by(Sex == Code)) |>
inner_join(year |> mutate(year_ = Description), join_by(Year == Code))
# create final aggregation, still completely lazily
twenty_till_fourty_non_european_in_auckland_area <-
expanded_cleaned_data |>
filter(
age_ >= 20, age_ <= 40,
grepl("^Auckland", area_),
year_ == "2018",
ethnic_ != "European"
) |>
summarise(group_count = sum(count_), .by = sex_) |> arrange(sex_)
print(twenty_till_fourty_non_european_in_auckland_area)
这看起来更美观，并在大约一分钟内完成，但存在几个隐藏问题。首先，我们将 整个 数据集读入 RAM。虽然对于此数据集来说这可能可行，因为大多数计算机都拥有超过 1 GB 的 RAM，但对于更大的数据集来说，这当然是行不通的。然后，我们执行一系列 dplyr 动词。然而，dplyr 急切地执行这些操作，这意味着它不会整体优化动词序列。例如，它无法看到我们在最后一步中过滤掉了所有非欧洲族裔，而是乐此不疲地为中间结果计算所有这些。同样的情况也发生在非 2018 年的普查年份上，我们只在最后一步才将其过滤掉。我们白白地为所有其他年份计算了一个昂贵的连接。根据数据分布，这可能会极其浪费。是的，可以手动移动过滤器，但这既繁琐又容易出错。至少结果与上面的 SQL 版本完全相同：
# A tibble: 2 × 2
sex_ group_count
<chr> <int>
1 Female 398556
2 Male 397326
现在我们将完全相同的脚本切换到 duckplyr。我们不使用 readr 将 CSV 文件完全读入 RAM，而是使用 duckplyr 中的 duckplyr_df_from_csv 函数：
library("duckplyr")
data <- duckplyr_df_from_csv("Data8277.csv")
age <- duckplyr_df_from_csv("DimenLookupAge8277.csv")
area <- duckplyr_df_from_csv("DimenLookupArea8277.csv")
ethnic <- duckplyr_df_from_csv("DimenLookupEthnic8277.csv")
sex <- duckplyr_df_from_csv("DimenLookupSex8277.csv")
year <- duckplyr_df_from_csv("DimenLookupYear8277.csv")
这精确地花费了 0 秒，因为 duckplyr 实际上没有做太多事情。我们使用我们屡获殊荣的“嗅探器”检测 CSV 文件的模式，并为每个文件创建了六个占位符对象。duckplyr 独特设计的一部分是这些对象是“海森堡式的”，一旦它们被视为正常的 R data.frames，它们就会表现得像那样，但当它们传递到下游分析步骤时，它们也可以作为惰性求值的占位符。这得益于一个鲜为人知的 R 特性，称为ALTREP，它允许 R 向量按需计算，等等。
现在我们重新运行与上面完全相同的 dplyr 管道。只是这次我们在不到一秒钟内“完成”了。这是因为我们所做的只是惰性地构建了一个所谓的关联树，它封装了所有的转换。这使得整体优化成为可能，例如在连接之前将年份和族裔一直下推到 CSV 文件的读取。我们还可以消除对查询中根本未使用的列的读取。
只有当我们最终打印结果时：
print(twenty_till_fourty_non_european_in_auckland_area)
才会触发实际的计算。这与上面手工编写的 SQL 查询在相同的时间内完成，只是这次我们使用了 dplyr 语法，体验更加愉快。而且，幸运的是，结果仍然完全相同。
此用例也作为我今年 posit::conf 大会主题演讲的一部分进行了展示。
最后，我们应该指出 duckplyr 仍在开发中。我们非常注意不破坏任何东西，如果某些功能（尚未）无法在 DuckDB 中运行，我们将退回到现有的 dplyr 实现。但是，如果任何功能未按预期工作，我们很乐意听取您的意见。
