数据操作语法对比教程

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

1 包（库）的安装

R

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

Python

在打开本页面的同时，会自动安装常用的数据分析库，如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: 体重 (克)

DuckDB-SQL

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

R-tidy

R-data.table

Python

3 链式法则

DuckDB-SQL

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

R-tidy

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

R-data.table

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

Python

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

4 筛选行

DuckDB-SQL

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

R-tidy

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

R-data.table

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

Python

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

4.1 练习

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

DuckDB-SQL

你需要使用 WHERE 子句，并用 AND 连接两个条件。

SELECT *
FROM penguins
WHERE species = 'Gentoo' AND body_mass_g > 5000;

R-tidy

在 filter() 函数中，你需要写两个条件，并用 , 或 & 分隔。

tb_penguins %>%
  filter(species == 'Gentoo', body_mass_g > 5000)

R-data.table

在 [ 的 i 位置写入两个条件，并用 & 连接。

dt_penguins[species == 'Gentoo' & body_mass_g > 5000]

Python

你需要构建两个布尔 Series，并使用 & 运算符将它们组合。记住每个条件都要用括号括起来。

df_penguins[(df_penguins['species'] == 'Gentoo') & (df_penguins['body_mass_g'] > 5000)]

5 选择列

DuckDB-SQL

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

R-tidy

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

R-data.table

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

Python

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

5.1 练习

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

DuckDB-SQL

你可以使用 COLUMNS() 函数和正则表达式来匹配以 flipper_ 开头的列。

SELECT species, island, sex, COLUMNS('flipper_.*')
FROM penguins
LIMIT 5;

R-tidy

在 select() 中，除了直接写列名，你还可以使用 starts_with() 辅助函数。

tb_penguins %>% 
  select(species, island, sex, starts_with('flipper_'))

R-data.table

你可以将要选择的列名直接放在 .() 中。对于模式匹配，可以使用 .SDcols 和 patterns()。

# 方法一：分别指定
dt_penguins[,.(species, island, sex, flipper_length_mm)]

# 方法二：使用模式匹配 (更通用)
dt_penguins[, .SD, .SDcols = patterns('^species$|^island$|^sex$|^flipper_')]

Python

.filter() 方法中的 items 参数可以接受一个完整的列名列表。你可以先构建好这个列表。或者，你可以用 regex 参数来匹配 flipper_ 列。

# 使用 filter 和 regex 组合
df_penguins.filter(regex='species|island|sex|^flipper_')

6 添加/修改列

DuckDB-SQL

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

R-tidy

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

R-data.table

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

Python

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

6.1 练习

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

DuckDB-SQL

你需要将原始列除以相应的换算系数。

SELECT
    *,
    body_mass_g / 1000 AS body_mass_kg,
    flipper_length_mm / 10 AS flipper_length_cm
FROM penguins
LIMIT 5;

R-tidy

在 mutate() 函数中，像 body_mass_kg = body_mass_g / 1000 这样写即可。

tb_penguins %>%
  mutate(
    body_mass_kg = body_mass_g / 1000,
    flipper_length_cm = flipper_length_mm / 10
  ) %>%
  head(5)

R-data.table

在 . 或 list() 中放入两个计算表达式，与新列名一一对应。

dt_penguins[, c('body_mass_kg', 'flipper_length_cm') := .(body_mass_g / 1000, flipper_length_mm / 10)]
head(dt_penguins, 5)

Python

在 .assign() 中，你可以使用 lambda 函数来引用 DataFrame 本身，例如 lambda df: df['body_mass_g'] / 1000。

df_penguins.assign(
    body_mass_kg = lambda df: df['body_mass_g'] / 1000,
    flipper_length_cm = lambda df: df['flipper_length_mm'] / 10
).head(5)

7 分组与聚合

DuckDB-SQL

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

R-tidy

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

R-data.table

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

Python

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

7.1 练习

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

DuckDB-SQL

你需要使用 AVG() 聚合函数，并按两个列进行分组。

SELECT
    species,
    sex,
    AVG(body_mass_g) AS avg_body_mass,
    AVG(flipper_length_mm) AS avg_flipper_length
FROM penguins
GROUP BY species, sex;

R-tidy

在 group_by() 中放入两个分组变量，在 summarise() 中使用 mean() 函数计算平均值。

tb_penguins %>%
  group_by(species, sex) %>%
  summarise(
    avg_body_mass = mean(body_mass_g, na.rm = TRUE),
    avg_flipper_length = mean(flipper_length_mm, na.rm = TRUE)
  )

R-data.table

在 by 参数中使用 .() 指定两个分组变量，在 j 位置使用 mean() 函数计算平均值。

dt_penguins[,
  .(avg_body_mass = mean(body_mass_g, na.rm = TRUE),
    avg_flipper_length = mean(flipper_length_mm, na.rm = TRUE)),
  by = .(species, sex)
]

Python

在 groupby() 中传入一个列名列表，然后在 agg() 中指定要计算的聚合函数。

df_penguins.groupby(['species', 'sex']).agg(
    avg_body_mass=('body_mass_g', 'mean'),
    avg_flipper_length=('flipper_length_mm', 'mean')
).reset_index()

8 排序

DuckDB-SQL

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

R-tidy

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

R-data.table

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

Python

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

8.1 练习

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

DuckDB-SQL

第一个排序字段使用 ASC（可省略），第二个使用 DESC。

SELECT *
FROM penguins
ORDER BY species ASC, bill_length_mm DESC
LIMIT 8;

R-tidy

在 arrange() 中，第一个变量直接写，第二个变量用 desc() 包裹。

tb_penguins %>%
  arrange(species, desc(bill_length_mm)) %>%
  head(8)

R-data.table

在 order() 中，第一个变量直接写，第二个变量前加 - 号。

dt_penguins[order(species, -bill_length_mm)] %>%
  head(8)

Python

by 参数接受列名列表，ascending 参数接受布尔值列表，与 by 中的列一一对应。

df_penguins.sort_values(
    by=['species', 'bill_length_mm'],
    ascending=[True, False]
).head(8)

9 处理缺失值

DuckDB-SQL

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

R-tidy

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

R-data.table

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

Python

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

9.1 练习

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

DuckDB-SQL

使用 IS NULL 检测缺失值，IS NOT NULL 检测非缺失值。

-- 1. 找出 sex 列有缺失值的行
SELECT *
FROM penguins
WHERE sex IS NULL;

-- 2. 删除 body_mass_g 和 flipper_length_mm 列有缺失值的行
SELECT *
FROM penguins
WHERE body_mass_g IS NOT NULL AND flipper_length_mm IS NOT NULL;

R-tidy

使用 is.na() 检测缺失值，drop_na() 可以直接传入列名。

# 1. 找出 sex 列有缺失值的行
tb_penguins %>%
  filter(is.na(sex))

# 2. 删除 body_mass_g 和 flipper_length_mm 列有缺失值的行
tb_penguins %>%
  drop_na(body_mass_g, flipper_length_mm)

R-data.table

在 i 位置使用 is.na() 或 !is.na() 进行筛选。

# 1. 找出 sex 列有缺失值的行
dt_penguins[is.na(sex)]

# 2. 删除 body_mass_g 和 flipper_length_mm 列有缺失值的行
dt_penguins[!is.na(body_mass_g) & !is.na(flipper_length_mm)]

Python

使用 isnull() 检测缺失值，dropna() 删除缺失值。

# 1. 找出 sex 列有缺失值的行
df_penguins[df_penguins['sex'].isnull()]

# 2. 删除 body_mass_g 和 flipper_length_mm 列有缺失值的行
df_penguins.dropna(subset=['body_mass_g', 'flipper_length_mm'])

10 数据连接/合并

DuckDB-SQL

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

R-tidy

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

R-data.table

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

Python

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

10.1 练习

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

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

DuckDB-SQL

使用 DISTINCT 获取唯一组合，使用 LEFT JOIN 进行左连接。

-- 创建第一个表
CREATE TEMPORARY TABLE penguin_species AS
SELECT DISTINCT species
FROM penguins;

-- 创建第二个表  
CREATE TEMPORARY TABLE penguin_measure AS
SELECT species, bill_length_mm, body_mass_g
FROM penguins
LIMIT 4;

-- 左连接
SELECT *
FROM penguin_species ps
LEFT JOIN penguin_measure pm ON ps.species = pm.species;

R-tidy

使用 distinct() 获取唯一组合，使用 left_join() 进行左连接。

# 创建第一个表
penguin_species <- tb_penguins %>%
  distinct(species)

# 创建第二个表
penguin_measure <- tb_penguins %>%
  select(species, bill_length_mm, body_mass_g) %>%
  head(4)

# 左连接
left_join(penguin_species, penguin_measure, by = "species")

R-data.table

使用 unique() 获取唯一组合，在 merge() 中使用 all.x = TRUE 进行左连接。

# 创建第一个表 - 只包含物种列避免笛卡尔积
penguin_species_dt <- unique(dt_penguins[, .(species)])

# 创建第二个表
penguin_measure_dt <- dt_penguins[1:4, .(species, bill_length_mm, body_mass_g)]

# 左连接 - 使用 merge() 函数
merge(penguin_species_dt, penguin_measure_dt, by = "species", all.x = TRUE)

Python

使用 drop_duplicates() 获取唯一组合，在 merge() 中使用 how='left' 进行左连接。

# 创建第一个表
penguin_species_df = df_penguins[['species']].drop_duplicates()

# 创建第二个表
penguin_measure_df = df_penguins[['species', 'bill_length_mm', 'body_mass_g']].head(4)

# 左连接
pd.merge(penguin_species_df, penguin_measure_df, on='species', how='left')

11 数据重塑

DuckDB-SQL

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

R-tidy

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

R-data.table

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

Python

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

11.1 练习

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

DuckDB-SQL

在 UNPIVOT 中列出要转换的列名，在 PIVOT 中使用 AVG 聚合函数。

-- 1. 宽表转长表
CREATE TEMPORARY TABLE long_measurements AS
SELECT 
    species,
    measurement_type,
    measurement_value
FROM penguins
UNPIVOT (measurement_value FOR measurement_type IN 
    (bill_length_mm, bill_depth_mm, flipper_length_mm));

-- 2. 长表转宽表，计算每个物种的平均值
PIVOT long_measurements
ON measurement_type
USING AVG(measurement_value)
GROUP BY species;

R-tidy

在 pivot_longer() 的 cols 参数中指定要转换的列，在 pivot_wider() 中使用 values_fn = mean。

# 1. 宽表转长表
long_data <- tb_penguins %>%
  pivot_longer(
    cols = c(bill_length_mm, bill_depth_mm, flipper_length_mm),
    names_to = "measurement_type",
    values_to = "measurement_value"
  )

# 2. 长表转宽表，计算每个物种的平均值
long_data %>%
  pivot_wider(
    names_from = measurement_type,
    values_from = measurement_value,
    values_fn = mean
  )

R-data.table

在 melt() 的 measure.vars 中指定要转换的列，在 dcast() 中使用 fun.aggregate = mean。

# 1. 宽表转长表
long_dt <- melt(dt_penguins,
                measure.vars = c("bill_length_mm", "bill_depth_mm", "flipper_length_mm"),
                variable.name = "measurement_type",
                value.name = "measurement_value")

# 2. 长表转宽表，计算每个物种的平均值
dcast(long_dt,
      species ~ measurement_type,
      fun.aggregate = mean,
      value.var = "measurement_value")

Python

在 melt() 的 value_vars 中指定要转换的列，在 pivot_table() 中使用 aggfunc='mean'。

# 1. 宽表转长表
long_df = df_penguins.melt(
    id_vars=['species'],
    value_vars=['bill_length_mm', 'bill_depth_mm', 'flipper_length_mm'],
    var_name='measurement_type',
    value_name='measurement_value'
)

# 2. 长表转宽表，计算每个物种的平均值
long_df.pivot_table(
    index='species',
    columns='measurement_type',
    values='measurement_value',
    aggfunc='mean'
).reset_index()

12 窗口函数

DuckDB-SQL

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

R-tidy

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

R-data.table

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

Python

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

12.1 练习

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

DuckDB-SQL

使用 ROW_NUMBER() 计算排名，AVG() 计算平均值，按 island 分区。

SELECT 
    species,
    island,
    bill_length_mm,
    ROW_NUMBER() OVER (PARTITION BY island ORDER BY bill_length_mm DESC) as beak_rank,
    AVG(bill_length_mm) OVER (PARTITION BY island) as avg_island_beak,
    bill_length_mm - AVG(bill_length_mm) OVER (PARTITION BY island) as beak_diff_from_avg
FROM penguins
ORDER BY island, beak_rank
LIMIT 10;

R-tidy

使用 row_number(desc()) 计算降序排名，按 island 分组。

tb_penguins %>%
  group_by(island) %>%
  mutate(
    beak_rank = row_number(desc(bill_length_mm)),
    avg_island_beak = mean(bill_length_mm, na.rm = TRUE),
    beak_diff_from_avg = bill_length_mm - avg_island_beak
  ) %>%
  ungroup() %>%
  select(species, island, bill_length_mm, beak_rank, avg_island_beak, beak_diff_from_avg) %>%
  arrange(island, beak_rank) %>%
  head(10)

R-data.table

使用 frank(-bill_length_mm) 计算降序排名，按 island 分组。

dt_penguins[,
  `:=`(
    beak_rank = frank(-bill_length_mm, ties.method = "first"),
    avg_island_beak = mean(bill_length_mm, na.rm = TRUE)
  ), 
  by = island
][,
  beak_diff_from_avg := bill_length_mm - avg_island_beak
][order(island, beak_rank),
  .(species, island, bill_length_mm, beak_rank, avg_island_beak, beak_diff_from_avg)
][1:10]

Python

使用 ascending=False 进行降序排名，按 island 分组。

df_result = df_penguins.copy()
df_result['beak_rank'] = df_result.groupby('island')['bill_length_mm'] \
    .rank(method='first', ascending=False)
df_result['avg_island_beak'] = df_result.groupby('island')['bill_length_mm'] \
    .transform('mean')
df_result['beak_diff_from_avg'] = df_result['bill_length_mm'] - df_result['avg_island_beak']

df_result[['species', 'island', 'bill_length_mm', 'beak_rank', 
           'avg_island_beak', 'beak_diff_from_avg']] \
    .sort_values(['island', 'beak_rank']) \
    .head(10)

13 字符串处理

DuckDB-SQL

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

R-tidy

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

R-data.table

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

Python

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

13.1 练习

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

DuckDB-SQL

使用 UPPER, SUBSTRING, REPLACE, LENGTH 函数和 || 连接符。

SELECT 
    species,
    UPPER(species) as species_upper,
    SUBSTRING(species, 1, 4) as species_prefix,
    REPLACE(species, 'a', '@') as species_replace,
    LENGTH(species) as species_length,
    species || ' - Bird' as species_bird
FROM penguins
GROUP BY species;

R-tidy

使用 str_to_upper, str_sub, str_replace_all, str_length, paste 函数。

tb_penguins %>%
  distinct(species) %>%
  mutate(
    species_upper = str_to_upper(species),
    species_prefix = str_sub(species, 1, 4),
    species_replace = str_replace_all(species, "a", "@"),
    species_length = str_length(species),
    species_bird = paste(species, "- Bird")
  )

R-data.table

使用 toupper, substr, gsub, nchar, paste 函数。

dt_penguins[, .(species = unique(species))][,
  `:=`(
    species_upper = toupper(species),
    species_prefix = substr(species, 1, 4),
    species_replace = gsub("a", "@", species),
    species_length = nchar(species),
    species_bird = paste(species, "- Bird")
  )
]

Python

使用 .str.upper(), 切片操作 [:4], .str.replace(), .str.len() 方法。

df_penguins[['species']].drop_duplicates().assign(
    species_upper = lambda df: df['species'].str.upper(),
    species_prefix = lambda df: df['species'].str[:4],
    species_replace = lambda df: df['species'].str.replace('a', '@'),
    species_length = lambda df: df['species'].str.len(),
    species_bird = lambda df: df['species'] + ' - Bird'
)

14 条件处理与逻辑判断

DuckDB-SQL

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

R-tidy

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

R-data.table

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

Python

使用 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”。

DuckDB-SQL

注意边界条件的处理，使用 AND 连接多个条件。

SELECT 
    species,
    sex,
    bill_length_mm,
    body_mass_g,
    CASE 
        WHEN bill_length_mm < 40 THEN '短喙'
        WHEN bill_length_mm < 50 THEN '中喙'
        ELSE '长喙'
    END as beak_category,
    CASE 
        WHEN sex = 'female' AND body_mass_g > 4500 THEN 'Heavy Female'
        ELSE 'Normal'
    END as special_category
FROM penguins
LIMIT 10;

R-tidy

使用 & 连接多个条件，注意边界值的处理。

tb_penguins %>%
  head(10) %>%
  mutate(
    beak_category = case_when(
      bill_length_mm < 40 ~ '短喙',
      bill_length_mm < 50 ~ '中喙',
      TRUE ~ '长喙'
    ),
    special_category = case_when(
      sex == 'female' & body_mass_g > 4500 ~ 'Heavy Female',
      TRUE ~ 'Normal'
    )
  ) %>%
  select(species, sex, bill_length_mm, body_mass_g, beak_category, special_category)

R-data.table

在 fcase() 中直接写条件表达式，使用 & 连接多个条件。

dt_penguins[1:10, .(
  species,
  sex,
  bill_length_mm,
  body_mass_g,
  beak_category = fcase(
    bill_length_mm < 40, '短喙',
    bill_length_mm < 50, '中喙',
    default = '长喙'
  ),
  special_category = fcase(
    sex == 'female' & body_mass_g > 4500, 'Heavy Female',
    default = 'Normal'
  )
)]

Python

使用 & 连接多个条件，注意每个条件都要用括号括起来。

import numpy as np

df_penguins.head(10).assign(
    beak_category = lambda df: np.where(df['bill_length_mm'] < 40, '短喙',
                       np.where(df['bill_length_mm'] < 50, '中喙', '长喙')),
    special_category = lambda df: np.where((df['sex'] == 'female') & (df['body_mass_g'] > 4500), 'Heavy Female', 'Normal')
)[['species', 'sex', 'bill_length_mm', 'body_mass_g', 'beak_category', 'special_category']]

15 数据采样与随机化

DuckDB-SQL

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

R-tidy

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

R-data.table

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

Python

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

15.1 练习

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

DuckDB-SQL

使用 RANDOM() 进行随机排序，PARTITION BY 进行分组，bernoulli 采样方法。

-- 1. 随机抽取15只企鹅
SELECT *
FROM penguins
ORDER BY RANDOM()
LIMIT 15;

-- 2. 每个岛屿抽取3只企鹅
WITH ranked_penguins AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY island ORDER BY RANDOM()) as rn
    FROM penguins
)
SELECT *
FROM ranked_penguins
WHERE rn <= 3;

-- 3. 抽取20%的数据
SELECT *
FROM penguins
USING SAMPLE 20 PERCENT (bernoulli);

R-tidy

使用 slice_sample() 进行抽样，sample_frac() 按比例抽样。

# 1. 随机抽取15只企鹅
result1 <- tb_penguins %>%
  slice_sample(n = 15)

# 2. 每个岛屿抽取3只企鹅
result2 <- tb_penguins %>%
  group_by(island) %>%
  slice_sample(n = 3) %>%
  ungroup()

# 3. 抽取20%的数据
result3 <- tb_penguins %>%
  sample_frac(0.2)

R-data.table

使用 sample() 函数进行抽样，.N 获取总行数。

# 1. 随机抽取15只企鹅
result1 <- dt_penguins[sample(.N, 15)]

# 2. 每个岛屿抽取3只企鹅
result2 <- dt_penguins[, .SD[sample(.N, min(3, .N))], by = island]

# 3. 抽取20%的数据
result3 <- dt_penguins[sample(.N, round(.N * 0.2))]

Python

使用 sample() 方法，frac 参数按比例抽样。

# 1. 随机抽取15只企鹅
result1 = df_penguins.sample(n=15)

# 2. 每个岛屿抽取3只企鹅
result2 = df_penguins.groupby('island').apply(
    lambda x: x.sample(n=min(3, len(x)))
).reset_index(drop=True)

# 3. 抽取20%的数据
result3 = df_penguins.sample(frac=0.2)

16 数据描述性统计

DuckDB-SQL

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

R-tidy

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

R-data.table

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

Python

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

16.1 练习

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

DuckDB-SQL

使用 COUNT, AVG, STDDEV, MIN, MAX 函数，CORR 计算相关系数。

-- 1. 每个物种的鳍长统计
SELECT 
    species,
    COUNT(*) as count,
    AVG(flipper_length_mm) as mean_flipper,
    STDDEV(flipper_length_mm) as std_flipper,
    MIN(flipper_length_mm) as min_flipper,
    MAX(flipper_length_mm) as max_flipper
FROM penguins
GROUP BY species;

-- 2. 岛屿和性别的交叉频数表
SELECT 
    island,
    sex,
    COUNT(*) as count
FROM penguins
WHERE sex IS NOT NULL
GROUP BY island, sex
ORDER BY island, sex;

-- 3. 喙长和喙深的相关系数
SELECT 
    CORR(bill_length_mm, bill_depth_mm) as correlation
FROM penguins;

R-tidy

使用 n(), mean(), sd(), min(), max(), count(), cor() 函数。

# 1. 每个物种的鳍长统计
stats1 <- tb_penguins %>%
  group_by(species) %>%
  summarise(
    count = n(),
    mean_flipper = mean(flipper_length_mm, na.rm = TRUE),
    std_flipper = sd(flipper_length_mm, na.rm = TRUE),
    min_flipper = min(flipper_length_mm, na.rm = TRUE),
    max_flipper = max(flipper_length_mm, na.rm = TRUE)
  )

# 2. 岛屿和性别的交叉频数表
stats2 <- tb_penguins %>%
  filter(!is.na(sex)) %>%
  count(island, sex)

# 3. 喙长和喙深的相关系数
correlation <- tb_penguins %>%
  summarise(corr = cor(bill_length_mm, bill_depth_mm, use = "complete.obs"))

R-data.table

使用 mean(), sd(), min(), max(), cor() 函数。

# 1. 每个物种的鳍长统计
stats1 <- dt_penguins[, .(
  count = .N,
  mean_flipper = mean(flipper_length_mm, na.rm = TRUE),
  std_flipper = sd(flipper_length_mm, na.rm = TRUE),
  min_flipper = min(flipper_length_mm, na.rm = TRUE),
  max_flipper = max(flipper_length_mm, na.rm = TRUE)
), by = species]

# 2. 岛屿和性别的交叉频数表
stats2 <- dt_penguins[!is.na(sex), .(count = .N), by = .(island, sex)]

# 3. 喙长和喙深的相关系数
correlation <- dt_penguins[, .(corr = cor(bill_length_mm, bill_depth_mm, use = "complete.obs"))]

Python

使用 agg() 方法，corr() 计算相关系数。

# 1. 每个物种的鳍长统计
stats1 = df_penguins.groupby('species')['flipper_length_mm'].agg([
    'count', 'mean', 'std', 'min', 'max'
]).round(2)

# 2. 岛屿和性别的交叉频数表
stats2 = df_penguins[df_penguins['sex'].notna()].groupby(['island', 'sex']).size().reset_index(name='count')

# 3. 喙长和喙深的相关系数
correlation = df_penguins[['bill_length_mm', 'bill_depth_mm']].corr().iloc[0, 1]