如何保留genus列，将latbin与timebin转为行并统计属的区间出现次数？

你可以通过先构建完整的区间组合网格，再补全计数，最后转宽表的方式实现需求，核心是确保所有19个纬度区间和54个时间区间的组合都被覆盖，包括原始数据中未出现的组合（补0）。以下是两种常用实现方案：

方案一：使用tidyverse包 #

library(tidyverse)

# 1. 生成所有纬度、时间区间的中点
lat_bins <- seq(-85, 85, by = 10)
time_bins <- seq(5, 535, by = 10)

# 2. 创建包含所有区间组合的完整网格
full_grid <- expand_grid(latbin = lat_bins, timebin = time_bins)

# 3. 统计原始数据中每个(属, 纬度区间, 时间区间)的出现次数
# 假设你的原始数据框名为original_data
counted_data <- original_data %>%
  count(genus, latbin, timebin, name = "occurrences")

# 4. 补全所有属的区间组合，缺失计数填0
full_data <- full_grid %>%
  crossing(genus = unique(original_data$genus)) %>%
  left_join(counted_data, by = c("genus", "latbin", "timebin")) %>%
  mutate(occurrences = replace_na(occurrences, 0))

# 5. 转换为宽表：属+纬度区间为行，时间区间为列
wide_table <- full_data %>%
  pivot_wider(names_from = timebin, values_from = occurrences) %>%
  arrange(genus, latbin)

# 6. 输出无表头的结果（可替换为你需要的输出路径）
write.table(wide_table, "output.txt", sep = "\t", col.names = FALSE, row.names = FALSE)

方案二：使用data.table包（适合大数据量） #

library(data.table)

setDT(original_data)

# 1. 生成所有区间中点
lat_bins <- seq(-85, 85, by = 10)
time_bins <- seq(5, 535, by = 10)

# 2. 创建完整的区间组合网格
full_grid <- CJ(latbin = lat_bins, timebin = time_bins)

# 3. 统计原始数据的出现次数
counted_data <- original_data[, .N, by = .(genus, latbin, timebin)]

# 4. 补全所有属的区间组合，缺失计数填0
full_data <- full_grid[CJ(genus = unique(original_data$genus), latbin = lat_bins, timebin = time_bins), on = .(latbin, timebin)]
full_data <- counted_data[full_data, on = .(genus, latbin, timebin)]
full_data[is.na(N), N := 0]

# 5. 转换为宽表
wide_table <- dcast(full_data, genus + latbin ~ timebin, value.var = "N")

# 6. 输出无表头结果
write.table(wide_table, "output.txt", sep = "\t", col.names = FALSE, row.names = FALSE)

关键说明 #

之前你觉得dcast无法实现，是因为直接使用它只会保留原始数据中存在的区间组合。通过先构建全区间组合网格，再与统计结果左连接补0，就能确保所有要求的区间都被覆盖，最终转宽表即可得到目标结构。

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