Unexpected behavior with idx as dates and at = "1 months"

[tjohnson250]

I expected the following code to give me the distinct count of category for all rows with a date in January and another count for all rows with a date in February. Using 1 month for both k and at just demonstrates the issue. In my use case I need k = 12.

library(runner)
library(tidyverse)

df <- read.table(text = "  user_id       date category
       27 2016-01-01    apple
       27 2016-01-03    apple
       27 2016-01-05     pear
       27 2016-01-07     plum
       27 2016-01-10    apple
       27 2016-01-14     pear
       27 2016-01-16     plum
       27 2016-02-05   orange
       11 2016-01-01    apple
       11 2016-01-03     pear
       11 2016-01-05     pear
       11 2016-01-07     pear
       11 2016-01-10    apple
       11 2016-01-14    apple
       11 2016-01-16    apple
       27 2016-02-10   orange", header = TRUE)

df <- arrange(df, date)

runner(df, k = "1 months", idx = as.Date(df$date), f = function(x) length(unique(x$category)), at = "1 months")

Here, runner returns: 1 3, instead of the expected 3 1

The problem seems to stem in part from how the at argument is used to create the reporting periods:

seq(min(as.Date(df$date)), max(as.Date(df$date)), by = "1 month")

returns "2016-01-01" "2016-02-01"

From the docs I'm not sure how 1 month is taken off of those dates to look back, but I assume it uses Date functions, so the windows above would each start at the first of the prior month.

From the docs, the results are "correct", but not what I would expect, or what most people mean when they say something like a rolling 12 month count.

Passing a list of end of month dates as the value of at also doesn't work, because subtracting 1 month from some months will include days from the end of prior months, for instance

seq(as.Date("2016-02-28"), length = 2, by = "-1 month")

returns "2016-02-28" "2016-01-28" so data for the second month would include anything near the end of the first month.

So far I cannot find a solution using runner and dates as the index. I tried using Clock at the year month precision, but runner flags Clock's data type as an error when used for idx. Allowing Clock would work as expected when using Clock's year_month_date constructor with just a year and a month:

library(clock)
seq(year_month_day(2016, 2), length = 2, by  = -1)

returns "2016-02" "2016-01"

Thus far, the only way I can see to do this is by converting the dates to Clock's year month type, then ranking and using the rank as index. So this works:

df <- df %>% mutate(ymRank = dense_rank(date_group(as.Date(date), "month")))

runner(df, k = 1, idx = df$ymRank, f = function(x) length(unique(x$category)), at = seq(min(df$ymRank), max(df$ymRank), by = 1))

producing the desired: 3 1

And this also works with a k > 1 window size, such as:

runner(df, k = 2, idx = df$ymRank, f = function(x) length(unique(x$category)), at = seq(min(df$ymRank), max(df$ymRank), by = 1))

producing: 3 4

At a minimum this use case should be mentioned in the docs or in the set of examples. Allowing Clock dates would be better though.

[gogonzo]

Hi @tjohnson250 sorry if I didn't document this explicitly. I'll submit update to the documentation. At this moment I'll try to answer your questions and solve this problem.

The problem seems to stem in part from how the at argument is used to create the reporting periods:
seq(min(as.Date(df$date)), max(as.Date(df$date)), by = "1 month")
...
From the docs I'm not sure how 1 month is taken off of those dates to look back, but I assume it uses Date functions, so the windows above would each start at the first of the prior month.

Yes, the code seq(min(as.Date(df$date)), max(as.Date(df$date)), by = "1 month") is exactly how at is calculated when using at = "1 month". I think in this case you could provide at by yourself as a first day of each month in date format.

If you check which date-ranges are taken to windows you will see that k = "1 month" or k = "2 month" gives expected results. seq creates the sequences by the month-day not by the 30 days period, so when you specify at you can expect always n-month window length.

for 1-month window

df <- data.frame(
    date = as.Date("2021-01-01") + 0:365,
    id = 1:366
)

runner(
    df, 
    k = "month", 
    lag = "1 days", # lag by 1 day to end window just before month starts
    idx = as.Date(df$date), 
    f = function(x) range(x$date),
    at = seq(as.Date("2021-01-01"), by = "1 months", length.out = 12),
    simplify = FALSE
)
# [[1]]
# [1] NA
# [[2]]
# [1] "2021-01-01" "2021-01-31"
# [[3]]
# [1] "2021-02-01" "2021-02-28"
# [[4]]
# [1] "2021-03-01" "2021-03-31"

for 2-months window

runner(
    df, 
    k = "2 months", 
    lag = "1 days",
    idx = as.Date(df$date), 
    f = function(x) range(x$date),
    at = seq(as.Date("2021-01-01"), by = "1 months", length.out = 12),
    simplify = FALSE
)

# [[1]]
# [1] NA
# [[2]]
# [1] "2021-01-01" "2021-01-31"
# [[3]]
# [1] "2021-01-01" "2021-02-28"
# [[4]]
# [1] "2021-02-01" "2021-03-31"
# [[5]]
# [1] "2021-03-01" "2021-04-30"

Your example might also be calculated with runner

df <- read.table(text = "  user_id       date category
       27 2016-01-01    apple
       27 2016-01-03    apple
       27 2016-01-05     pear
       27 2016-01-07     plum
       27 2016-01-10    apple
       27 2016-01-14     pear
       27 2016-01-16     plum
       27 2016-02-05   orange
       11 2016-01-01    apple
       11 2016-01-03     pear
       11 2016-01-05     pear
       11 2016-01-07     pear
       11 2016-01-10    apple
       11 2016-01-14    apple
       11 2016-01-16    apple
       27 2016-02-10   orange", header = TRUE)

df <- arrange(df, date)
runner(
    df, 
    k = "month", 
    lag = "1 days",
    idx = as.Date(df$date), 
    f = function(x) length(unique(x$category)),
    at = c(as.Date("2016-02-01"), as.Date("2016-03-01"))
)

runner(
    df, 
    k = "month", 
    lag = "1 days",
    idx = as.Date(df$date), 
    f = function(x) length(unique(x$category)),
    at = c(as.Date("2016-02-01"), as.Date("2016-03-01"))
)
# [1] 3 1
runner(
    df, 
    k = "2 months", 
    lag = "1 days",
    idx = as.Date(df$date), 
    f = function(x) length(unique(x$category)),
    at = c(as.Date("2016-02-01"), as.Date("2016-03-01"))
)
# [1] 3 4

So I suggest to specify at instead of relying on at = "1 month" output. You might find better solution to find the right at values - below solution using base

seq(
    trunc(min(df$date), "months"), 
    seq(max(df$date), length.out = 2, by = "1 month")[2], 
    by = "1 months"
)[-1]

Hope it helps,
Dawid