在R中按ID合并无市政ID变更的日期区间

可以用dplyr包快速实现这个需求，核心思路是对每个用户（id）的连续相同市政ID（municipality_id）进行分组，然后合并对应的日期区间：

步骤1：加载依赖包 #

library(dplyr)

步骤2：处理数据 #

data_processed <- data %>%
  group_by(id) %>%
  # 为连续相同的municipality_id生成分组标记
  mutate(group_id = cumsum(municipality_id != lag(municipality_id, default = first(municipality_id)))) %>%
  # 按id、市政ID和分组标记聚合
  group_by(id, municipality_id, group_id) %>%
  summarise(
    from = min(from),
    to = max(to),
    .groups = "drop"
  ) %>%
  # 移除临时分组列
  select(-group_id)

验证结果 #

执行以下代码可以确认处理后的数据与目标数据一致：

all.equal(data_processed, data_edit)
# 输出 TRUE 表示结果匹配

原始数据与目标数据定义 #

原始数据 #

data <- data.frame(id = c('A', 'A', 'B', 'B','B', 'B', 'C', 'C', 'C'),
                   municipality_id = c(820, 200, 820, 820, 830, 830, 700, 500, 700),
                   from = as.Date(c("2007-01-01", "2007-03-01", "2007-01-01", "2007-04-01", "2007-06-01", "2008-02-01", "2007-01-01", "2007-06-01", "2009-01-01")),
                   to = as.Date(c("2007-02-28", "2100-01-01", "2007-03-31", "2007-05-31", "2008-01-31", "2100-01-01", "2007-05-31", "2008-12-31", "2100-01-01"))) 

目标数据 #

data_edit <- data.frame(id = c('A', 'A', 'B', 'B', 'C', 'C', 'C'),
                   municipality_id = c(820, 200, 820, 830, 700, 500, 700),
                   from = as.Date(c("2007-01-01", "2007-03-01", "2007-01-01", "2007-06-01", "2007-01-01", "2007-06-01", "2009-01-01")),
                   to = as.Date(c("2007-02-28", "2100-01-01", "2007-05-31", "2100-01-01", "2007-05-31", "2008-12-31", "2100-01-01"))) 

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