如何按分组计算数据框中重叠日期区间的数量？

我有如下DataFrame df：

> df
   group       from         to
1      A 2023-03-01 2023-03-02
2      A 2023-03-01 2023-03-03
3      A 2023-03-03 2023-03-07
4      A 2023-03-05 2023-03-08
5      A 2023-03-09 2023-03-10
6      A 2023-03-11 2023-03-11
7      B 2023-03-01 2023-03-02
8      B 2023-03-04 2023-03-06
9      B 2023-03-07 2023-03-07
10     B 2023-03-08 2023-03-11
11     B 2023-03-10 2023-03-12
12     B 2023-03-15 2023-03-16

我需要基于from和to列，按group分组计算重叠日期区间的数量：

• A组中，第1行与第2行重叠，第3行与第2行、第4行重叠，共3个重叠区间
• B组仅第10行与第11行重叠，共1个重叠区间

期望输出：

group overlaying_intervals
1     A                    3
2     B                    1

dput格式的df：

df <- structure(list(group = c("A", "A", "A", "A", "A", "A", "B", "B", 
"B", "B", "B", "B"), from = c("2023-03-01", "2023-03-01", "2023-03-03", 
"2023-03-05", "2023-03-09", "2023-03-11", "2023-03-01", "2023-03-04", 
"2023-03-07", "2023-03-08", "2023-03-10", "2023-03-15"), to = c("2023-03-02", 
"2023-03-03", "2023-03-07", "2023-03-08", "2023-03-10", "2023-03-11", 
"2023-03-02", "2023-03-06", "2023-03-07", "2023-03-11", "2023-03-12", 
"2023-03-16")), class = "data.frame", row.names = c("1", "2", 
"3", "4", "5", "6", "7", "8", "9", "10", "11", "12"))

解决方案 #

首先要将from和to列转换为日期类型，再按分组统计不重复的重叠区间对数量。

方法1：使用tidyverse工具链 #

library(tidyverse)

# 转换日期格式
df <- df %>%
  mutate(across(c(from, to), as.Date))

# 分组计算重叠区间数
result <- df %>%
  group_by(group) %>%
  mutate(row_id = row_number()) %>%
  # 生成组内所有区间配对
  expand_grid(., .) %>%
  # 只保留i<j的配对，避免重复计数
  filter(row_id.x < row_id.y) %>%
  # 判断区间是否重叠：[a1,b1]与[a2,b2]重叠的条件为a1<=b2且b1>=a2
  mutate(is_overlap = from.x <= to.y & to.x >= from.y) %>%
  # 汇总每组重叠数
  summarise(overlaying_intervals = sum(is_overlap)) %>%
  ungroup()

print(result)

方法2：基础R实现（无需额外包） #

# 转换日期格式
df$from <- as.Date(df$from)
df$to <- as.Date(df$to)

# 分组统计重叠数
result <- aggregate(. ~ group, data = df, FUN = function(x) {
  n <- length(x)
  count <- 0
  # 遍历所有i<j的区间对
  for(i in 1:(n-1)) {
    for(j in (i+1):n) {
      if(df$from[i] <= df$to[j] && df$to[i] >= df$from[j]) {
        count <- count + 1
      }
    }
  }
  count
})

# 重命名结果列
colnames(result)[2] <- "overlaying_intervals"

print(result)

两种方法均能输出符合预期的结果。

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