Create a sequence of unique observations by group with dplyr and create a difference in months column

Question

Lets suppose I have a data frame:

    User    Date
    aaaa    2015-11-26
    aaaa    2015-12-26
    aaaa    2016-01-26
    bbbb    2014-10-15
    bbbb    2014-11-15
    bbbb    2015-05-16

And I want to generate a new column variable(s) that:

1. Count a the unique user in sequence
2. If there is a gap in the month to month, count how many months the gap is.

Desired ouput:

    User    Date        Count  Gap
    aaaa    2015-11-26    1     0
    aaaa    2015-12-26    2     0
    aaaa    2016-01-26    3     0
    bbbb    2014-10-15    1     0
    bbbb    2014-11-15    2     0
    bbbb    2015-05-16    3     6

Answer 1

Using zoo::as.yearmon(), however, I had to round because otherwise 2015-11-26 to 2015-12-26 is considered longer than one month. Perhaps someone can comment/edit/explain how to make that particular calculation more "intuitive".

library(dplyr)
library(zoo)

df %>%
  group_by(User) %>%
  mutate(Count = 1:n(),
         Gap_In_Months = round(12 * as.numeric(as.yearmon(Date) - as.yearmon(lag(Date))), 1),
         Gap = ifelse(Gap_In_Months <= 1 | is.na(Gap_In_Months), 0, Gap_In_Months))

#     User       Date Count Gap_In_Months   Gap
#   (fctr)     (fctr) (int)         (dbl) (dbl)
# 1   aaaa 2015-11-26     1            NA     0
# 2   aaaa 2015-12-26     2             1     0
# 3   aaaa 2016-01-26     3             1     0
# 4   bbbb 2014-10-15     1            NA     0
# 5   bbbb 2014-11-15     2             1     0
# 6   bbbb 2015-05-16     3             6     6

Perhaps you want to be more specific as to "what is a month"? 30 days? 31 days? 28 days?

If that's the case, we can utilize lubridate:

library(lubridate)

df %>%
  group_by(User) %>%
  mutate(Count = 1:n(),
         Diff_Time = ymd(Date) - ymd(lag(Date)),
         Gap = ifelse(Diff_Time <= ddays(31) | is.na(Diff_Time), 0, as.numeric(Diff_Time, units = "days")))

#     User       Date Count Diff_Time   Gap
#   (fctr)     (fctr) (int)    (dfft) (dbl)
# 1   aaaa 2015-11-26     1   NA days     0
# 2   aaaa 2015-12-26     2   30 days     0
# 3   aaaa 2016-01-26     3   31 days     0
# 4   bbbb 2014-10-15     1   NA days     0
# 5   bbbb 2014-11-15     2   31 days     0
# 6   bbbb 2015-05-16     3  182 days   182