将日期序列拆分为每个月的一个块（包含开始和结束日期）

3 回答

• 1

  data.table解决方案

  我最喜欢的这类问题的首选武器是 data.table 非常快 foverlaps

  df <- data.frame(group = c("a", "a", "b"),
                   start = as.Date(c("2018-01-01", "2018-09-01", "2018-02-01")),
                   end = as.Date(c("2018-02-15", "2018-12-31", "2018-03-30")))
  
  #create data-frame with from-to by month
  df2 <- data.frame( start = seq( as.Date("2018-01-01"), length = 12, by = "1 month" ),
                     end = seq( as.Date( "2018-02-01"), length = 12, by= "1 month" ) - 1,
                     stringsAsFactors = FALSE )
  
  library(data.table)
  
  #setDT on both data.frames... df2 needs to be keyed in order for foverlaps to work.
  dt <- foverlaps( setDT( df ), setDT( df2, key = c("start", "end") ), type = "any", mult = "all" )[]
  #choose keep the right columns (start/end)
  dt[ start < i.start, start := i.start ]
  dt[ end > i.end, end := i.end ]
  #cleaning
  dt[, `:=`(i.start = NULL, i.end = NULL)][]
  
   #         start        end group
  # 1: 2018-01-01 2018-01-31     a
  # 2: 2018-02-01 2018-02-15     a
  # 3: 2018-09-01 2018-09-30     a
  # 4: 2018-10-01 2018-10-31     a
  # 5: 2018-11-01 2018-11-30     a
  # 6: 2018-12-01 2018-12-31     a
  # 7: 2018-02-01 2018-02-28     b
  # 8: 2018-03-01 2018-03-30     b
  

  基准

  与@AntoniosK的tidyverse解决方案相比（效果更好，更具可读性;-)）， foverlaps 在50％的时间内完成工作

  # Unit: milliseconds
  # expr       min       lq      mean    median        uq       max neval
  # tidyverse 10.418585 10.79064 12.531207 11.080309 11.753030 93.110804   100
  # foverlaps  5.320911  5.59506  5.861865  5.846766  6.009146  9.606981   100
  

  回复于 2024-05-06T17:43:50+08:00
• 1

  df <- data.frame(group = c("a", "a", "b"),
                   start = as.Date(c("2018-01-01", "2018-09-01", "2018-02-01")),
                   end = as.Date(c("2018-02-15", "2018-12-31", "2018-03-30")))
  
  library(tidyverse)
  library(lubridate)
  
  df %>%
    group_by(id = row_number()) %>%             # for each row
    mutate(seq = list(seq(start, end, "day")),  # create a sequence of dates with 1 day step
           month = map(seq, month)) %>%         # get the month for each one of those dates in sequence
    unnest() %>%                                # unnest data
    group_by(group, id, month) %>%              # for each group, row and month
    summarise(start = min(seq),                 # get minimum date as start
              end = max(seq)) %>%               # get maximum date as end
    ungroup() %>%                               # ungroup
    select(-id, - month)                        # remove unecessary columns
  
  # # A tibble: 8 x 3
  #   group start      end       
  #  <fct> <date>     <date>    
  # 1 a     2018-01-01 2018-01-31
  # 2 a     2018-02-01 2018-02-15
  # 3 a     2018-09-01 2018-09-30
  # 4 a     2018-10-01 2018-10-31
  # 5 a     2018-11-01 2018-11-30
  # 6 a     2018-12-01 2018-12-31
  # 7 b     2018-02-01 2018-02-28
  # 8 b     2018-03-01 2018-03-30
  

  回复于 2024-05-06T17:43:50+08:00
• 2

  这是另一种可能的 data.table 方法：

  library(data.table)
  setDT(df)
  
  #to create a data.table of monthly periods
  earliest <- as.POSIXlt(df[,min(start)]) 
  earliest$mday <- 1L
  earliest <- as.Date(earliest)
  
  latest <- as.POSIXlt(df[,max(end)])
  latest$mday <- 1L
  latest <- seq(as.Date(latest), by="1 month", length.out=2L)[2L]
  
  startOfMonths <- seq(earliest, latest, by="1 month")
  monthsDT <- data.table(
      som=startOfMonths[-length(startOfMonths)],
      eom=startOfMonths[-1L] - 1L)
  
  #perform non-equi join where som falls within start and end
  ans <- monthsDT[df, .(group, start, som=x.som, end, eom=x.eom), 
      by=.EACHI, on=.(som>=start, som<=end)][, -(1L:2L)]
  
  #get desired output according to OP's requirement
  ans[, .(group, start=max(start, som), end=min(end, eom)), by=seq_len(ans[,.N])][, -1L]
  

  输出：

  group      start        end
  1:     a 2018-01-01 2018-01-31
  2:     a 2018-02-01 2018-02-15
  3:     a 2018-09-01 2018-09-30
  4:     a 2018-10-01 2018-10-31
  5:     a 2018-11-01 2018-11-30
  6:     a 2018-12-01 2018-12-31
  7:     b 2018-02-01 2018-02-28
  8:     b 2018-03-01 2018-03-30
  

  回复于 2024-05-06T17:43:50+08:00