如何在R中按日期规则合并数据集并匹配机构对应绩效等级

需求背景 #

现有两个数据集：

• dataset1：时间范围为2015-01-31至2021-06-30，包含机构编码、月末日期两个字段
• dataset2：记录各机构不同检查时点的绩效结果，包含机构编码、检查日期、绩效等级三个字段

匹配规则 #

需要遵循3条匹配规则：

1. 每次检查当月及之前的所有月份，赋值为该次检查的绩效等级
2. 最后一次检查之后的所有月份，沿用最后一次检查的绩效等级
3. 若绩效为Inspected but not rated，则直接使用下一次检查的绩效等级

数据集构造代码 #

dataset1构造代码 #

dataset1_dates=c("2015-01-31","2015-02-28","2015-03-31","2015-04-30","2015-05-31","2015-06-30","2015-07-31","2015-08-31","2015-09-30","2015-10-31","2015-11-30","2015-12-31","2016-01-31","2016-02-29","2016-03-31","2016-04-30","2016-05-31","2016-06-30","2016-07-31","2016-08-31","2016-09-30","2016-10-31","2016-11-30","2016-12-31","2017-01-31","2017-02-28","2017-03-31","2017-04-30","2017-05-31","2017-06-30","2017-07-31","2017-08-31","2017-09-30","2017-10-31","2017-11-30","2017-12-31","2018-01-31","2018-02-28","2018-03-31","2018-04-30","2018-05-31","2018-06-30","2018-07-31","2018-08-31","2018-09-30","2018-10-31","2018-11-30","2018-12-31","2019-01-31","2019-02-28","2019-03-31","2019-04-30","2019-05-31","2019-06-30","2019-07-31","2019-08-31","2019-09-30","2019-10-31","2019-11-30","2019-12-31","2020-01-31","2020-02-29","2020-03-31","2020-04-30","2020-05-31","2020-06-30","2020-07-31","2020-08-31","2020-09-30","2020-10-31","2020-11-30","2020-12-31","2021-01-31","2021-02-28","2021-03-31","2021-04-30","2021-05-31","2021-06-30")
# add dates
dataset1 <- expand.grid(Organisation = c("A123","B234","C456"),
                       Date = dataset1_dates)
  
## sort
dataset1 <- dataset1[order(dataset1$Organisation, dataset1$Date),]
## reset id
rownames(dataset1) <- NULL

dataset1$Organisation <- as.character(dataset1$Organisation)
dataset1$Date <- as.Date(dataset1$Date, format="%Y-%m-%d")

dataset2构造代码 #

dataset2 <- read.table(
  text = "
Organisation    Date_inspection     Performance
A123            2015-01-31          Good
A123            2016-01-14          OK
B234            2017-06-14          Inadequate
C456            2015-06-30          OK
C456            2016-02-10          Inspected but not rated
C456            2018-05-18          Good
C456            2020-03-21          OK",
  header = TRUE)

dataset2$Organisation <- as.character(dataset2$Organisation)
dataset2$Date_inspection <- as.Date(dataset2$Date_inspection, format="%Y-%m-%d")
dataset2$Performance <- as.character(dataset2$Performance)

实现代码 #

使用dplyr、lubridate工具包即可完成需求，代码如下：

# 加载依赖包
library(dplyr)
library(lubridate)

# 第一步：处理未评级的绩效记录，用后一次有效绩效替换
dataset2_clean <- dataset2 %>%
  group_by(Organisation) %>%
  arrange(Date_inspection) %>%
  mutate(Performance = ifelse(Performance == "Inspected but not rated", NA, Performance)) %>%
  fill(Performance, .direction = "up") %>%
  ungroup()

# 第二步：生成检查结果的生效起始月末日期
dataset2_clean <- dataset2_clean %>%
  mutate(
    # 取检查日期所在月的最后一天作为生效起始时间
    ins_month_end = ceiling_date(Date_inspection, unit = "month") - days(1)
  ) %>%
  select(Organisation, ins_month_end, Performance)

# 第三步：滚动匹配所有月份的绩效结果
result <- dataset1 %>%
  left_join(dataset2_clean, by = "Organisation") %>%
  filter(Date >= ins_month_end) %>%
  group_by(Organisation, Date) %>%
  # 取离当前月末最近的一次检查结果
  filter(ins_month_end == max(ins_month_end)) %>%
  ungroup() %>%
  select(Date, Organisation, Performance) %>%
  # 补充首次检查之前的月份数据，用首次检查结果填充
  right_join(dataset1, by = c("Date", "Organisation")) %>%
  group_by(Organisation) %>%
  arrange(Date) %>%
  fill(Performance, .direction = "updown") %>%
  ungroup() %>%
  arrange(Organisation, Date)

输出结果示例 #

最终结果包含Date、Organisation、Performance三个字段，格式如下：

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