r - 如何拆分(分解)R中的日期？

Question

我正在使用登记数据进行病假研究。从登记册上，我只得到了每个人的病假开始日期和结束日期。但日期并没有逐年分割。例如，对于人 A，只有开始日期 (1-may-2016) 和结束日期 (14-feb-2018) 的数据。
所以，我想知道如何在 R 中逐年拆分日期(即 01/05/16 到 14/02/18 将分为 01/5/16-31/12/16、01/01/2017-31/12/17, 01/01/18-14/02/18) 以计算每年的病假总数。
为问题创建的示例数据如下；

sick_leave <- tribble(
      ~id,        ~from,          ~to, 
        1, "01/01/2018", "03/10/2020",
        2, "01/01/2016", "01/01/2021", 
        3, "02/01/2018", "02/06/2018",
        3, "02/07/2018", "31/12/2018",
        4, "02/10/2018", "02/02/2019",
        4, "31/12/2019", "01/01/2021",
        5, "02/10/2017", "20/05/2018",
        6, "02/03/2021", "31/12/2021",
        7, "01/01/2016", "05/06/2016"
    ) %>% mutate(from = dmy(from),to = dmy(to))

所需的输出是:

id  year  from        to          wanted
 1  2018  2018-01-01  2018-12-31  365
 1  2019  2019-01-01  2019-12-31  365
 1  2020  2020-01-01  2020-10-03  277
 2  2016  2016-01-01  2016-12-31  366
 2  2017  2017-01-01  2017-12-31  365
 2  2018  2018-01-01  2018-12-31  365
 2  2019  2019-01-01  2019-12-31  365
 2  2020  2020-01-01  2020-12-31  366
 2  2021  2021-01-01  2021-01-01    1
 3  2018  2018-01-02  2018-06-02  152
 3  2018  2018-07-02  2018-12-31  183
 4  2018  2018-10-02  2018-12-31   91
 4  2019  2019-01-01  2019-02-02   33
 4  2019  2019-12-31  2019-12-31    1
 4  2020  2020-01-01  2020-12-31  366
 4  2021  2021-01-01  2021-01-01    1
 5  2017  2017-10-02  2017-12-31   91
 5  2018  2018-01-01  2018-05-20  140
 6  2021  2021-03-02  2021-12-31  305
 7  2016  2016-01-01  2016-06-05  157

Answer 1

使用此解决方案，您可以通过根据您的请求创建新行来拆分日期。
注意函数split_by_year逐行执行。
在代码中，我会给你一些评论。

# necessary libraries
library(dplyr)
library(lubridate)

split_by_year <- function(from, to){
    
    year_from <- year(from)
    year_to   <- year(to)
    
    # get sequence of years
    years <- seq(year_from, year_to)
    
    # create start and end date for each year
    starts <- make_date(years)  
    ends   <- make_date(years, 12, 31)
    
    # set starts and ends together, replace limits with from and end
    dates <- sort(c(starts, ends))
    dates[c(1, length(dates))] <- c(from, to)
    
    # recreate dataframe with columns from and to
    m <- matrix(dates, ncol = 2, byrow = TRUE)
    colnames(m) <- c("from", "to")
    mutate_all(as_tibble(m), as_date)
    
}

sick_leave %>%
    rowwise() %>% # next line will be performed row by row
    summarise(id = id, split_by_year(from, to)) %>% 
    mutate(sick_days = as.numeric(to - from + 1))

输出:

# A tibble: 20 x 4
      id from       to         sick_days
   <dbl> <date>     <date>         <dbl>
 1     1 2018-01-01 2018-12-31       365
 2     1 2019-01-01 2019-12-31       365
 3     1 2020-01-01 2020-10-03       277
 4     2 2016-01-01 2016-12-31       366
 5     2 2017-01-01 2017-12-31       365
 6     2 2018-01-01 2018-12-31       365
 7     2 2019-01-01 2019-12-31       365
 8     2 2020-01-01 2020-12-31       366
 9     2 2021-01-01 2021-01-01         1
10     3 2018-01-02 2018-06-02       152
11     3 2018-07-02 2018-12-31       183
12     4 2018-10-02 2018-12-31        91
13     4 2019-01-01 2019-02-02        33
14     4 2019-12-31 2019-12-31         1
15     4 2020-01-01 2020-12-31       366
16     4 2021-01-01 2021-01-01         1
17     5 2017-10-02 2017-12-31        91
18     5 2018-01-01 2018-05-20       140
19     6 2021-03-02 2021-12-31       305
20     7 2016-01-01 2016-06-05       157