基于时间范围统计另一数据框中记录的出现次数

以下是几种针对需求的可行方法，均能保留df2的所有行（包括重复ID）：

方法1：使用dplyr逐行处理（直观易读） #

该方法通过rowwise()实现逐行判断，逻辑清晰，适合理解和调试：

library(dplyr)

# 定义原始数据框
df1 <- data.frame(
  ID = c("a", "b", "a", "c", "a", "b"), 
  date = as.Date(c("2020-1-1", "2020-1-2", "2020-1-5","2020-1-10", "2020-1-10", "2020-1-15"))
)

df2 <- data.frame(
  ID = c("a", "b", "a", "c"), 
  start = as.Date(c("2019-12-30", "2020-1-1", "2020-1-5","2020-1-10")), 
  end = as.Date(c("2020-1-3", "2020-1-2", "2020-1-12","2020-1-14"))
)

# 逐行统计符合条件的记录数
df3 <- df2 %>%
  rowwise() %>%
  mutate(count = nrow(filter(df1, ID == !!ID, date >= start, date <= end))) %>%
  ungroup()

# 转换count为字符型（匹配预期结果格式）
df3$count <- as.character(df3$count)

方法2：使用data.table非等连接（高效适合大数据） #

如果数据量较大，data.table的非等连接性能更优，且能精准保留df2的所有行：

library(data.table)

# 转换为data.table格式
df1 <- data.table(
  ID = c("a", "b", "a", "c", "a", "b"), 
  date = as.Date(c("2020-1-1", "2020-1-2", "2020-1-5","2020-1-10", "2020-1-10", "2020-1-15"))
)

df2 <- data.table(
  ID = c("a", "b", "a", "c"), 
  start = as.Date(c("2019-12-30", "2020-1-1", "2020-1-5","2020-1-10")), 
  end = as.Date(c("2020-1-3", "2020-1-2", "2020-1-12","2020-1-14"))
)

# 非等连接并按df2每行统计数量
count_df <- df2[df1, on = .(ID, start <= date, end >= date), 
                .(count = .N), by = .EACHI]

# 合并回原df2的所有列并调整格式
df3 <- merge(df2, count_df, by = c("ID", "start", "end")) %>%
  setcolorder(c("ID", "start", "end", "count")) %>%
  .[, count := as.character(count)]

方法3：使用Base R的apply函数（无需第三方包） #

若不想加载额外包，可直接用base R的apply函数实现需求：

# 定义原始数据框
df1 <- data.frame(
  ID = c("a", "b", "a", "c", "a", "b"), 
  date = as.Date(c("2020-1-1", "2020-1-2", "2020-1-5","2020-1-10", "2020-1-10", "2020-1-15"))
)

df2 <- data.frame(
  ID = c("a", "b", "a", "c"), 
  start = as.Date(c("2019-12-30", "2020-1-1", "2020-1-5","2020-1-10")), 
  end = as.Date(c("2020-1-3", "2020-1-2", "2020-1-12","2020-1-14"))
)

# 逐行计算符合条件的记录数
df2$count <- apply(df2, 1, function(row) {
  sum(df1$ID == row["ID"] & df1$date >= as.Date(row["start"]) & df1$date <= as.Date(row["end"]))
})

# 转换为字符型并命名为df3
df2$count <- as.character(df2$count)
df3 <- df2

以上三种方法最终都能得到你预期的df3结果，可根据数据量和个人习惯选择。

内容的提问来源于stack exchange，提问作者Tan Sing Chee