I have the following data:

  df <- data.frame(dt1 = c("2019-05-02", "2019-01-02", "2019-06-02"), 
                   dt2 =   c("2019-08-30", "2019-04-24", "2019-12-06") )
> df
         dt1        dt2
1 2019-05-02 2019-08-30
2 2019-01-02 2019-04-24
3 2019-06-02 2019-12-06

Here is what I want to do:

i) I want create factors by binning, for example, for the first date, the dates binned as 2019-07-31, 2019-06-30, 2019-05-31, so essentially binning by dt2.
ii) I want to count the total number of dates in each bin.

The expected output is:

        dt1        dt2      val_count
1 2019-05-02 2019-08-30         3
2 2019-01-02 2019-04-24         3 
3 2019-06-02 2019-12-06         6

I found this post relevant.

Note: I do not want take difference between months of two dates.

Thank you for suggestions.

CodePudding user response：

It's pretty messy but if you want to count how many last date of the months are in between dt1 and dt2, you may try

library(lubridate)
library(dplyr)

fd <- paste0(lubridate::year(min(df$dt1, df$dt2)), "-02-01") %>% as.Date()

ld <- paste0(lubridate::year(max(df$dt1, df$dt2)) 1, "-01-01") %>% as.Date()
x <- seq.Date(fd, ld, by = "month") - 1
df %>%
  rowwise() %>%
  mutate(val_count = length(x[dt1 < x & x < dt2]))

  dt1        dt2        val_count
  <chr>      <chr>          <int>
1 2019-05-02 2019-08-30         3
2 2019-01-02 2019-04-24         3
3 2019-06-02 2019-12-06         6

Choice of < or <= depends on your purpose.

To get total days between dt1 and dt2,

df %>%
  rowwise() %>%
  mutate(val_count = length(x[dt1 < x & x < dt2])) %>%
  mutate(dd = as.Date(dt2) - as.Date(dt1))

  dt1        dt2        val_count dd      
  <chr>      <chr>          <int> <drtn>  
1 2019-05-02 2019-08-30         3 120 days
2 2019-01-02 2019-04-24         3 112 days
3 2019-06-02 2019-12-06         6 187 days

Add

df %>%
  rowwise() %>%
  mutate(val_count = length(x[dt1 < x & x < dt2]),
         val_count = ifelse(val_count == 0, 1, val_count)) %>%
  mutate(dd = as.Date(dt2) - as.Date(dt1))

  dt1        dt2        val_count dd      
  <chr>      <chr>          <dbl> <drtn>  
1 2019-05-02 2019-08-30         3 120 days
2 2019-01-02 2019-04-24         3 112 days
3 2019-06-02 2019-12-06         6 187 days
4 2019-06-01 2019-06-02         1   1 days

CodePudding user response：

The above solution is indeed kinda messy, it just takes a simple oneliner to do this

df <- data.frame(dt1 = c("2019-05-02", "2019-01-02", "2019-06-02", "2019-06-01"), dt2 =   c("2019-08-30", "2019-04-24", "2019-12-06", "2019-06-02") )

df %>%
  mutate(val_count = as.period(ymd(dt2) - ymd(dt1)) %/% months(1))

#          dt1        dt2 val_count
# 1 2019-05-02 2019-08-30         3
# 2 2019-01-02 2019-04-24         3
# 3 2019-06-02 2019-12-06         6
# 4 2019-06-01 2019-06-02         0