在R中用dplyr或sqldf移除日期重叠行并保留x最大值

步骤说明 #

1. 将字符类型的日期列转换为Date格式，确保能正确判断日期区间的重叠关系；
2. 识别所有存在重叠的日期区间，将它们划分为同一组；
3. 在每个重叠组内，保留x值最大的行。

解决方案代码（基于tidyverse+IRanges） #

# 加载所需包
library(dplyr)
library(IRanges)

# 原始数据框
data.frame(time_left = c("2011-08-05",
                         "2011-07-25",
                         "2017-08-20",
                         "2017-08-20", 
                         "2017-10-09", 
                         "2019-06-01"), 
           time_right= c("2011-09-14",
                         "2011-09-01",
                         "2017-09-12",
                         "2017-09-26",
                         "2017-10-15",
                         "2019-11-05"),
           x = c(114,20,10,1,5,100) ) -> df

# 1. 转换日期列为Date类型
df$time_left <- as.Date(df$time_left)
df$time_right <- as.Date(df$time_right)

# 2. 识别重叠区间组
ir <- IRanges(start = as.integer(df$time_left), end = as.integer(df$time_right))
groups <- findOverlaps(ir, ir, type = "any") %>% 
  as.data.frame() %>% 
  select(queryHits, subjectHits) %>% 
  graph_from_data_frame(directed = FALSE) %>% 
  components() %>% 
  .$membership
df <- df %>% mutate(group = groups)

# 3. 保留每个组内x值最大的行
result <- df %>% 
  group_by(group) %>% 
  filter(x == max(x)) %>% 
  ungroup() %>% 
  select(-group)

# 输出结果
print(result)

替代方案（仅用dplyr，无需额外包） #

如果不想加载IRanges包，可以用循环标记重叠组：

library(dplyr)

# 原始数据框（同上，略）
# 转换日期列（同上，略）

# 按起始日期排序
df <- df %>% arrange(time_left)

# 标记重叠组
group <- integer(nrow(df))
group[1] <- 1
current_group <- 1
current_end <- df$time_right[1]

for (i in 2:nrow(df)) {
  if (df$time_left[i] <= current_end) {
    group[i] <- current_group
    current_end <- max(current_end, df$time_right[i])
  } else {
    current_group <- current_group + 1
    group[i] <- current_group
    current_end <- df$time_right[i]
  }
}

df$group <- group

# 筛选x最大值行
result <- df %>% 
  group_by(group) %>% 
  filter(x == max(x)) %>% 
  ungroup() %>% 
  select(-group)

print(result)

结果说明 #

运行代码后，会自动移除重叠区间中x值较小的行，保留每个重叠组内x最大的记录。针对你的输入数据，最终输出会保留第1、2、3、5、6行（注：你提供的期望输出中第4行的x值应为笔误，原数据中第3行x=10是该重叠组的最大值）。

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