R语言：统计时间区间内字母出现次数的实现方案

你完全不需要用循环来实现这个需求，R的向量化操作和tidy工具能更高效、简洁地完成任务。下面提供几种常用的实现方式：

方法1：使用tidyverse（dplyr + tidyr） #

这是最直观的方式，适合初学者理解：

library(tidyverse)
library(lubridate)

# 原始数据
df1 <- data.frame (
  timestamp  = as.POSIXct(c("2021-07-11 02:22:22.000","2021-07-11 02:22:22.000", "2021-07-11 02:24:59.000","2021-07-11 02:26:59.000")),
  letter = c("A", "A", "B","C")
)

df2 <- data.frame (
  timestamp_start  = as.POSIXct( c("2021-07-11 02:22:22.000", "2021-07-11 02:24:59.000","2021-07-11 02:26:59.000")),
  timestamp_end = as.POSIXct(c("2021-07-11 02:23:59.000", "2021-07-11 02:25:59.000","2021-07-11 02:27:59.000"))
)

# 给df2添加行标识，方便后续连接
df2 <- df2 %>% mutate(interval_id = row_number())

# 交叉连接筛选+分组统计+转宽表
cross_join(df2, df1) %>%
  filter(timestamp >= timestamp_start & timestamp <= timestamp_end) %>%
  count(interval_id, timestamp_start, timestamp_end, letter, name = "count") %>%
  pivot_wider(
    id_cols = c(timestamp_start, timestamp_end),
    names_from = letter,
    values_from = count,
    names_prefix = "count_",
    values_fill = 0
  ) %>%
  arrange(interval_id) %>%
  select(-interval_id)

运行后会得到和你预期一致的结果，若需要将计数转为字符类型，可在最后添加mutate(across(starts_with("count_"), as.character))。

方法2：使用data.table（适合大数据量） #

如果数据量较大，data.table的运行效率会更高：

library(data.table)
library(lubridate)

setDT(df1)
setDT(df2)

# 交叉连接筛选+分组统计+转宽表
df1[df2, on = .(timestamp >= timestamp_start, timestamp <= timestamp_end), allow.cartesian = TRUE] %>%
  .[, .N, by = .(timestamp_start = timestamp, timestamp_end = i.timestamp_end, letter)] %>%
  dcast(timestamp_start + timestamp_end ~ paste0("count_", letter), value.var = "N", fill = 0)

方法3：基础R向量化操作 #

用sapply实现向量化判断，避免显式循环：

# 获取所有唯一字母类别
letters_unique <- unique(df1$letter)

# 对每个区间统计各字母数量
counts <- t(sapply(1:nrow(df2), function(i) {
  in_interval <- df1$timestamp >= df2$timestamp_start[i] & df1$timestamp <= df2$timestamp_end[i]
  table(factor(df1$letter[in_interval], levels = letters_unique))
}))

# 合并结果
result <- cbind(df2, counts)
colnames(result)[3:ncol(result)] <- paste0("count_", letters_unique)

为什么不用循环？ #

R的显式for循环在处理大数据时效率极低，而上述向量化操作和工具都是底层优化过的，运行速度更快，代码也更简洁易读，符合R的编程范式。

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