如何将鱼类长度丰度DataFrame重排为指定格式？

问题背景 #

我有一个包含鱼类体长与丰度的DataFrame，包含Month、Day、Time、Site、Spp（物种）列，以及多个体长区间列（如<5、5-9、10-14等），每列对应该体长区间下各物种的丰度，原始数据如下：

# 原始数据示例
df <- data.frame(
  Month = c(1,1,1,1,1),
  Day = c(22,22,22,22,22),
  Time = c("10:32:00","10:32:00","10:32:00","10:32:00","10:32:00"),
  Site = c("Trees","Trees","Trees","Trees","Trees"),
  Spp = c("Caranx ruber", "Abudefduf saxatilis", "Acanthurus coeruleus", "Acanthurus tractus", "Diodon hystrix"),
  `<5` = c(0,0,0,0,0),
  `5-9` = c(0,0,0,0,0),
  `10-14` = c(0,5,1,3,0),
  `15-20` = c(10,2,2,0,0),
  `20-30` = c(0,0,0,0,2)
)

需要将数据重排为长格式：每个物种的每个体长区间对应一行，包含Month、Day、Time、Site、Spp、length（体长区间）、abundance（丰度）字段，目标格式示例：

# 目标数据格式
target_df <- data.frame(
  Month = c(1,1,1,1,1),
  Day = c(22,22,22,22,22),
  Time = c("10:32:00","10:32:00","10:32:00","10:32:00","10:32:00"),
  Site = c("Trees","Trees","Trees","Trees","Trees"),
  Spp = c("Caranx ruber", "Abudefduf saxatilis", "Abudefduf saxatilis", "Acanthurus coeruleus", "Acanthurus coeruleus"),
  length = c("15-20","10-14","15-20","10-14","15-20"),
  abundance = c(10,5,2,1,2)
)

单独用tapply处理单个体长列可以运行：

# 单列处理可行
as.data.frame(tapply(X = df$`5-9`, INDEX = df$Spp, FUN = sum))

但批量处理多列时报错arguments must have same length：

# 批量处理报错
as.data.frame(tapply(X = list(df$`5-9`, df$`10-14`), INDEX = df$Spp, FUN = sum))

希望用dplyr完成数据重排，或解决tapply的批量问题。

解决方案：用dplyr + tidyr实现宽表转长表 #

这是典型的宽表转长表需求，用tidyr::pivot_longer()可以高效完成，结合dplyr可灵活过滤冗余行：

步骤1：加载依赖包 #

library(dplyr)
library(tidyr)

步骤2：数据重排 #

# 宽表转长表，过滤丰度为0的行（可选）
long_df <- df %>%
  pivot_longer(
    cols = `<5`:`20-30`,  # 指定需要转换的体长区间列，也可用starts_with等匹配规则
    names_to = "length",  # 新列名：存储体长区间
    values_to = "abundance"  # 新列名：存储对应丰度
  ) %>%
  filter(abundance > 0)  # 过滤丰度为0的行，不需要可删除此句

运行后得到的long_df即为目标格式，输出示例：

# A tibble: 7 × 7
  Month   Day Time     Site  Spp                  length abundance
  <dbl> <dbl> <chr>    <chr> <chr>                <chr>      <dbl>
1     1    22 10:32:00 Trees Caranx ruber         15-20        10
2     1    22 10:32:00 Trees Abudefduf saxatilis  10-14         5
3     1    22 10:32:00 Trees Abudefduf saxatilis  15-20         2
4     1    22 10:32:00 Trees Acanthurus coeruleus 10-14         1
5     1    22 10:32:00 Trees Acanthurus coeruleus 15-20         2
6     1    22 10:32:00 Trees Acanthurus tractus   10-14         3
7     1    22 10:32:00 Trees Diodon hystrix       20-30         2

关于tapply批量报错的原因及base R替代方案 #

tapply的X参数仅支持单个向量，你传入多向量组成的列表时，X的长度与INDEX（物种列长度）不匹配，因此报错。如果要用base R实现，可通过lapply循环处理每一列再合并：

# base R 实现方式
length_cols <- c("<5", "5-9", "10-14", "15-20", "20-30")
result_list <- lapply(length_cols, function(col) {
  temp <- tapply(df[[col]], df$Spp, sum)
  temp <- temp[temp > 0]  # 过滤0值
  data.frame(
    Spp = names(temp),
    length = col,
    abundance = as.numeric(temp),
    stringsAsFactors = FALSE
  )
})
# 合并结果并关联原始分组列
base_long_df <- do.call(rbind, result_list) %>%
  left_join(unique(df[, c("Month", "Day", "Time", "Site", "Spp")]), by = "Spp") %>%
  select(Month, Day, Time, Site, Spp, length, abundance)

内容的提问来源于stack exchange，提问作者Cami