如何基于夏令时日期范围拆分DataFrame并新增行？

我有一个包含id、id_month_year、start_date、end_date、daylight_begin和daylight_end的DataFrame，其中id为记录ID，id_month_year是记录ID、月份和年份的字符串，start_date和end_date为记录的起止日期，daylight_begin和daylight_end是夏令时的起止日期。

初始数据构建代码 #

df <- data.frame(
  id = c(rep("A01", 5), rep("B01", 4)),
  id_month_year = c("A01 Jan 2023", "A01 March 2023", "A01 November 2022", "A01 June 2022",
                    "A01 March 2022", "B02 March 2022", "B02 November 2022", "B02 March 2023", "B02 May 2022"),
  start_date = c("2023-01-04", "2023-03-01", "2022-11-01", "2022-06-05",
                 "2022-03-02", "2022-03-04", "2022-11-05", "2023-03-02", "2022-05-03"),
  end_date = c("2023-01-25", "2023-03-24", "2022-11-27", "2022-06-24",
               "2022-03-29", "2022-03-16", "2022-11-28", "2023-03-30", "2022-05-30")
) %>%
  mutate(across(c(start_date, end_date), ~as.Date(.x)),
         daylight_begin = if_else(year(start_date) == "2022", as_date("2022-03-13"), as_date("2023-03-12")),
         daylight_end = if_else(year(end_date) == "2022", as_date("2022-11-06"), as_date("2023-11-05"))
  )

需求说明 #

若记录的start_date和end_date区间包含daylight_begin或daylight_end日期，需以这些夏令时日期为分界点拆分原日期区间，并复制对应行的id和id_month_year字段。

不同年份的夏令时起止日期 #

• 2022年：daylight_begin = "2022-03-13"；daylight_end = "2022-11-06"
• 2023年：daylight_begin = "2023-03-12"；daylight_end = "2023-11-05"

拆分示例 #

以id_month_year为"A01 March 2023"的记录为例，原起止日期为"2023-03-01"至"2023-03-24"，因包含daylight_begin（2023-03-12），需拆分为两个区间：

• 区间1：start_date = "2023-03-01" 至 end_date = "2023-03-11"
• 区间2：start_date = "2023-03-12" 至 end_date = "2023-03-24"

处理后期望的DataFrame #

new_df <- data.frame(
  id = c(rep("A01", 8), rep("B01", 7)),
  id_month_year = c("A01 Jan 2023", rep("A01 March 2023", 2), rep("A01 November 2022", 2), "A01 June 2022",
                    rep("A01 March 2022",2), rep("B02 March 2022",2), rep("B02 November 2022",2), rep("B02 March 2023",2), "B02 May 2022"),
  start_date = c("2023-01-04", "2023-03-01", "2023-03-12", "2022-11-01",  "2022-11-06", "2022-06-05",
                 "2022-03-02", "2022-03-13", "2022-03-04", "2022-03-13", "2022-11-05", "2022-11-06", "2023-03-02", "2023-03-12","2022-05-03"),
  end_date = c("2023-01-25","2023-03-11", "2023-03-24", "2022-11-05", "2022-11-27", "2022-06-24",
               "2022-03-12", "2022-03-29", "2022-03-12", "2022-03-16", "2022-11-05", "2022-11-28", "2023-03-11", "2023-03-30", "2022-05-30")
) %>%
  mutate(across(c(start_date, end_date), ~as.Date(.x)),
    daylight_begin = if_else(year(start_date) == "2022", as_date("2022-03-13"), as_date("2023-03-12")),
         daylight_end = if_else(year(end_date) == "2022", as_date("2022-11-06"), as_date("2023-11-05"))
    )

处理后的输出结果 #

> new_df
    id     id_month_year start_date   end_date daylight_begin daylight_end
1  A01      A01 Jan 2023 2023-01-04 2023-01-25     2023-03-12   2023-11-05
2  A01    A01 March 2023 2023-03-01 2023-03-11     2023-03-12   2023-11-05
3  A01    A01 March 2023 2023-03-12 2023-03-24     2023-03-12   2023-11-05
4  A01 A01 November 2022 2022-11-01 2022-11-05     2022-03-13   2022-11-06
5  A01 A01 November 2022 2022-11-06 2022-11-27     2022-03-13   2022-11-06
6  A01     A01 June 2022 2022-06-05 2022-06-24     2022-03-13   2022-11-06
7  A01    A01 March 2022 2022-03-02 2022-03-12     2022-03-13   2022-11-06
8  A01    A01 March 2022 2022-03-13 2022-03-29     2022-03-13   2022-11-06
9  B01    B02 March 2022 2022-03-04 2022-03-12     2022-03-13   2022-11-06
10 B01    B02 March 2022 2022-03-13 2022-03-16     2022-03-13   2022-11-06
11 B01 B02 November 2022 2022-11-05 2022-11-05     2022-03-13   2022-11-06
12 B01 B02 November 2022 2022-11-06 2022-11-28     2022-03-13   2022-11-06
13 B01    B02 March 2023 2023-03-02 2023-03-11     2023-03-12   2023-11-05
14 B01    B02 March 2023 2023-03-12 2023-03-30     2023-03-12   2023-11-05
15 B01      B02 May 2022 2022-05-03 2022-05-30     2022-03-13   2022-11-06

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