r - 按财政季度计算 R 中两个日期之间事件的记录

Question

头(df，6)

       PositionName       Hire.Date     Termination.Date      
1      CBM                4/22/2002         9/14/2007  
2      CBM                10/5/2005         5/5/2008   
3      SBM                10/31/2005        6/25/2007  
4      CBM                12/1/2005         5/5/2008   
5      SBM                7/6/2006          6/20/2008  
6      CBM                10/6/2006         6/30/2008  



df$Hire.Date <- format(as.Date(sm.SL$Hire.Date, format = "%m/%d/%Y"), "%m/%Y")
df$Termination.Date <- format(as.Date(sm.SL$Termination.Date, format = "%m/%d/%Y"), "%m/%Y")

    #Stuckhere
      WorkedDuring <- df %>% 
      filter(PositionName == "CBM")%>%
      mutate(quarter = quarter(Hire.Date >, with_year = TRUE))%>% 
      group_by(quarter) %>%
      summarise(total = n())

试图获得 df:

Year.Quarter     n
2005.1           1
2005.2           1
2005.3           1
2005.4           4
2006.1           4
2006.2           4
2006.3           5
2006.4           6

不是一个很好的例子，因为它刚刚上升，但我想在他们被终止时带走他们。

Answer 1

尝试这个:

# quick helper function
date2qtr <- function(d) 1 + (as.integer(format(d, "%m")) - 1) %/% 3L

library(dplyr)
# library(tidyr)
# library(purrr)
tbl_df(df) %>%
  mutate(
    alldates = purrr::map2(Hire.Date, Termination.Date,
                           ~ seq.Date(..1, ..2, by = "3 months"))
  ) %>%
  tidyr::unnest() %>%
  mutate(
    Year = as.integer(format(alldates, "%Y")),
    Quarter = date2qtr(alldates)
  ) %>%
  group_by(Year, Quarter) %>%
  tally()
# # A tibble: 25 x 3
# # Groups:   Year [?]
#     Year Quarter     n
#    <int>   <dbl> <int>
#  1  2002       2     1
#  2  2002       3     1
#  3  2002       4     1
#  4  2003       1     1
#  5  2003       2     1
#  6  2003       3     1
#  7  2003       4     1
#  8  2004       1     1
#  9  2004       2     1
# 10  2004       3     1
# # ... with 15 more rows

数据:

df <- structure(list(PositionName = c("CBM", "CBM", "SBM", "CBM", "SBM", 
"CBM"), Hire.Date = c("4/22/2002", "10/5/2005", "10/31/2005", 
"12/1/2005", "7/6/2006", "10/6/2006"), Termination.Date = c("9/14/2007", 
"5/5/2008", "6/25/2007", "5/5/2008", "6/20/2008", "6/30/2008"
)), class = "data.frame", row.names = c("1", "2", "3", "4", "5", 
"6"))

df[c("Hire.Date","Termination.Date")] <-
  lapply(df[c("Hire.Date","Termination.Date")], as.Date, format = "%m/%d/%Y")