数据操作语法对比教程

DuckDB vs R (Tidyverse & data.table) vs Python (Pandas)

1 包（库）的安装

在打开本页面的同时，会自动开始安装dplyr、tidyr与data.table，安装速度取决于你的网络情况，请耐心等待，不要刷新，否则将重新安装。对于其他包，你可以使用webr::install进行安装，安装完成后使用library加载包

在打开本页面的同时，会自动安装常用的数据分析库，如pandas、matplotlib等,对于没有预安装的库，你可以使用micropip进行安装，安装完成后使用import进行加载库。比如如果需要安装pandas,你可以使用以下代码

2 数据集

企鹅数据集（penguins）是一个包含南极洲三种企鹅物种的生物测量数据的公开数据集。所有示例基于本数据集,请执行下方代码块先加载本数据集至环境。

  • species: 企鹅物种 (Adelie, Chinstrap, Gentoo)
  • island: 栖息岛屿 (Biscoe, Dream, Torgersen)
  • sex: 性别 (male, female)
  • bill_length_mm: 喙长 (毫米)
  • bill_depth_mm: 喙深 (毫米)
  • flipper_length_mm: 鳍长 (毫米)
  • body_mass_g: 体重 (克)

首先，我们需要将CSV数据加载到DuckDB的内存数据库中，创建一个名为 penguins 的表。后续所有SQL操作都将基于此表。

3 链式法则

SQL 主要通过嵌套查询或通用表表达式（CTE, Common Table Expressions）来实现类似链式操作的逻辑。CTE (WITH 子句) 提高了可读性。

你可以使用%>% 或者|>进行链式处理，快捷键Ctrl+Shift+M。

使用中括号进行链式处理，形如DT[ ... ][ ... ][ ... ]，为了美观也可以纵向链式。

Python 使用.进行链式处理，为了美观可以使用反斜杠将一行代码拆分成多行或是将整个链式放在括号中。

4 筛选行

使用 WHERE 子句进行条件筛选。

  • 使用slice进行行索引筛选,slice_head和slice_tail可以快速筛选
  • 使用filter进行条件数据筛选
  • 使用slice_max 或者slice_min 快速筛选某列最大值对应行

data.table的一般数据清洗形式形如DT[i, j, by],其中i的位置可以用来筛选行和排序。

Python 使用loc方法或直接[]进行条件选择，使用iloc进行索引筛选。query方法也十分便捷。

4.1 练习

筛选出所有 species 为 ‘Gentoo’ 且 body_mass_g 大于 5000 克的企鹅数据。

5 选择列

在 SELECT 语句后直接列出你需要的列名。使用 * 选择所有列。DuckDB 还提供了 COLUMNS 函数，可以使用正则表达式方便地进行列匹配。

  • 使用select进行列选择。
  • select内部提供了强大的辅助函数来匹配列，例如starts_with()、ends_with()、contains()以及支持正则表达式的matches()。

在j位置选择列，使用 .() 或 list() 可以确保返回的是一个 data.table。.SD 和 .SDcols 参数组合提供了更强大的列选择能力，尤其适合进行模式匹配。

使用 [] 并传入一个列名列表来选择多列。.filter() 方法则提供了更灵活的选择方式，例如通过正则表达式。

5.1 练习

从企鹅数据集中选择 species, island, sex 和所有以 flipper_ 开头的列。

6 添加/修改列

在 SELECT 语句中，你可以直接进行计算，并使用 AS 关键字为新列赋予名称。

使用dplyr包中的mutate函数来创建或修改列。它非常适合在链式操作中使用。

data.table使用独特的 := 操作符，它可以非常高效地”原地”添加或修改列，无需复制整个数据集。这使得它在处理大型数据集时速度极快。

你可以直接用 df['new_column'] = ... 的方式创建一个新列。在链式操作中，.assign() 方法更为方便，它会返回一个包含新列的新 DataFrame，而不修改原始数据。

6.1 练习

计算体重（单位：千克）和鳍长（单位：厘米），分别命名为 body_mass_kg 和 flipper_length_cm。 (提示: 1 kg = 1000 g, 1 cm = 10 mm)

7 分组与聚合

使用 GROUP BY 子句进行分组，然后在 SELECT 中使用聚合函数（如 COUNT, SUM, AVG 等）进行计算。

使用 group_by() 进行分组，然后使用 summarise()（或 summarize()）进行聚合计算。

在 j 位置使用 .() 或 list() 进行聚合计算，并通过 by 参数指定分组变量。

使用 groupby() 方法进行分组，然后使用聚合方法（如 size(), mean(), max() 等）。

7.1 练习

按 species 和 sex 分组，计算每组的平均体重（body_mass_g）和平均鳍长（flipper_length_mm）。

8 排序

使用 ORDER BY 子句进行排序，ASC 为升序（默认），DESC 为降序。

使用 arrange() 函数进行排序，默认升序，使用 desc() 包裹变量名表示降序。

使用 order() 函数进行排序，默认升序，在变量前加 - 号表示降序。

使用 sort_values() 方法进行排序，ascending 参数控制升序或降序。

8.1 练习

按 species 升序排序，然后按 bill_length_mm 降序排序，显示前 8 行。

9 处理缺失值

使用 WHERE 子句配合 IS NULL 或 IS NOT NULL 来筛选缺失值或非缺失值。

使用 is.na() 检测缺失值，complete.cases() 检测完整行，drop_na() 快速删除含缺失值的行。

使用 is.na() 在 i 位置进行筛选，.N 可以快速统计数量。

使用 isnull() 或 isna() 检测缺失值，dropna() 删除含缺失值的行。

9.1 练习

  1. 找出 sex 列有缺失值的所有行
  2. 删除 body_mass_g 和 flipper_length_mm 列有缺失值的行

10 数据连接/合并

使用标准的 SQL JOIN 语法进行数据连接，支持 INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN 等。

使用 dplyr 包中的连接函数：inner_join(), left_join(), right_join(), full_join() 等。

使用 merge() 函数进行连接，通过修改表结构避免笛卡尔积。

使用 merge() 函数或 join() 方法进行数据连接，支持各种连接类型。

10.1 练习

创建两个表： 1. penguin_species: 包含 species 列的唯一值 2. penguin_measure: 包含 species, bill_length_mm, body_mass_g 的前4行数据

使用左连接将这两个表连接起来，保留 penguin_species 中的所有记录。

11 数据重塑

使用 UNPIVOT 进行宽表转长表，PIVOT 进行长表转宽表。

使用 pivot_longer() 进行宽表转长表，pivot_wider() 进行长表转宽表。

使用 melt() 进行宽表转长表，dcast() 进行长表转宽表。

使用 melt() 进行宽表转长表，pivot_table() 或 pivot() 进行长表转宽表。

11.1 练习

将企鹅数据集中的测量列（bill_length_mm, bill_depth_mm, flipper_length_mm）转换为长格式，然后转换回宽格式，计算每个物种的平均值。

12 窗口函数

使用窗口函数配合 OVER() 子句，支持 PARTITION BY 和 ORDER BY。

使用 dplyr 的窗口函数如 row_number(), rank(), lead(), lag() 等，配合 group_by()。

使用特殊的 .N, .I, .SD 等符号，配合 by 参数实现窗口函数功能。

使用 groupby() 配合 transform() 方法，或使用 rank() 等方法实现窗口函数。

12.1 练习

对于每个岛屿上的企鹅，计算： 1. 按喙长排名的序号 2. 该岛屿上的平均喙长 3. 当前企鹅喙长与岛屿平均喙长的差值

13 字符串处理

使用内置字符串函数如 LENGTH, UPPER, LOWER, SUBSTRING, REPLACE 等。

使用 stringr 包提供的字符串处理函数，如 str_to_upper, str_to_lower, str_length, str_sub, str_replace 等。

使用基础R的字符串函数或 stringr 包函数进行字符串处理。

使用字符串方法如 .upper(), .lower(), len(), 切片操作, .replace() 等，或使用 str 访问器。

13.1 练习

对 species 列进行以下字符串操作： 1. 转换为全大写 2. 提取前4个字符 3. 将所有的 ‘a’ 替换为 ‘@’ 4. 计算字符串长度 5. 在字符串末尾添加 ” - Bird”

14 条件处理与逻辑判断

使用 CASE WHEN 语句进行条件处理，支持多条件判断。

使用 case_when() 函数进行多条件判断，或 if_else() 进行简单条件判断。

使用 fcase() 函数（data.table专用）进行多条件判断，或基础的 ifelse() 函数。

使用 np.where() 进行简单条件判断，或使用字典映射和 apply() 进行复杂条件处理。

14.1 练习

根据企鹅的喙长（bill_length_mm）创建分类： - 短喙：bill_length_mm < 40 - 中喙：40 <= bill_length_mm < 50
- 长喙：bill_length_mm >= 50

同时，如果企鹅是雌性（sex = ‘female’）且体重（body_mass_g）大于4500，标记为 “Heavy Female”，否则标记为 “Normal”。

15 数据采样与随机化

使用 USING SAMPLE 或 ORDER BY RANDOM() 进行随机抽样。

使用 sample_n(), sample_frac(), 和 slice_sample() 进行随机抽样。

使用 .N 和 sample() 函数进行随机抽样。

使用 sample() 方法进行随机抽样。

15.1 练习

  1. 从数据中随机抽取15只企鹅
  2. 对每个岛屿进行分层抽样，每个岛屿抽取3只企鹅
  3. 抽取20%的数据作为样本

16 数据描述性统计

使用聚合函数和 GROUP BY 进行描述性统计。

使用 summarise() 和统计函数进行描述性统计。

在 j 位置使用统计函数进行描述性统计。

使用 describe() 方法和聚合函数进行描述性统计。

16.1 练习

  1. 计算每个物种的鳍长（flipper_length_mm）的描述性统计（数量、均值、标准差、最小值、最大值）
  2. 创建岛屿和性别的交叉频数表
  3. 计算喙长和喙深的相关系数
回到顶部