4

I'm trying to filter a list of dates, to only include dates once a year, which resets on every inclusion date.

In below table, I would only want to filter out the rows where include=1 (for this example I created the include column manually). If you look closely:

  • id=10 is included, because that's more than a year after id=1, where id=9 isn't yet.
  • id=22 in included, because that's more than a year after id=10, where id=21 isn't yet.

Table, sorted ascending on testdate obviously:

| id |  testdate  | include |
|    |            |         |
|    |            | (I want |
|    |            |  this   |
|    |            | column) |
|:--:|:----------:|:-------:|
|  1 | 2008-02-26 |    1*   |
|  2 | 2008-03-07 |    0    |
|  3 | 2008-04-03 |    0    |
|  4 | 2008-04-25 |    0    |
|  5 | 2008-07-23 |    0    |
|  6 | 2008-10-09 |    0    |
|  7 | 2008-10-28 |    0    |
|  8 | 2009-01-14 |    0    |
|  9 | 2009-01-28 |    0    |
| 10 | 2009-05-19 |    1*   |
| 11 | 2009-06-05 |    0    |
| 12 | 2009-06-05 |    0    |
| 13 | 2009-06-26 |    0    |
| 14 | 2009-07-15 |    0    |
| 15 | 2009-07-15 |    0    |
| 16 | 2009-08-18 |    0    |
| 17 | 2009-08-18 |    0    |
| 18 | 2009-09-08 |    0    |
| 19 | 2009-09-25 |    0    |
| 20 | 2010-03-19 |    0    |
| 21 | 2010-04-06 |    0    |
| 22 | 2010-06-30 |    1*   |
| 23 | 2010-10-07 |    0    |
| 24 | 2010-10-21 |    0    |
| 25 | 2010-10-30 |    0    |
| 26 | 2010-12-10 |    0    |
| 27 | 2011-03-04 |    0    |
| 28 | 2011-05-11 |    0    |
| 29 | 2012-03-08 |    1*   |
| 30 | 2012-03-23 |    0    |
| 31 | 2012-09-13 |    0    |
| 32 | 2013-03-21 |    1*   |
| 33 | 2014-10-08 |    1*   |
-----------------------------

What I've tried with the dplyr library:

# calculate interval
mutate(interval = as.double(difftime(testdate,lag(testdate), units = 'days'))) %>%
# accumulate interval in days
mutate(interval_cum = if_else(is.na(interval), -1, interval + lag(interval))) %>%
mutate(interval_cum2 = if_else(lag(interval) > 365, 0, interval_cum)) %>%
# filter out first row and all relevant accumulated intervals
mutate(include = if_else(row_number(testdate) == 1 | interval > 365 | interval_cum == -1 | interval_cum2 > 365, 1, 0, 0))

But this will miss out id's 10, 22 and 32 because I can't iterate over multiple rows. Does anyone know a valid R way to accomplish this?

Raw data input for R:

structure(list(testdate = structure(c(13935, 13945, 13972, 13994, 
14083, 14161, 14180, 14258, 14272, 14383, 14400, 14400, 14421, 
14440, 14440, 14474, 14474, 14495, 14512, 14687, 14705, 14790, 
14889, 14903, 14912, 14953, 15037, 15105, 15407, 15422, 15596, 
15785, 16351), class = "Date"), include = c(1, 0, 0, 0, 0, 0, 
0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 
0, 1, 0, 0, 1, 1)), .Names = c("testdate", "include"), row.names = c(NA, 
-33L), class = c("tbl_df", "tbl", "data.frame"))
MS Berends
  • 4,489
  • 1
  • 40
  • 53
  • Indeed. Like I said, I created the column manually, and I'm looking for a way to SET the include column. – MS Berends Feb 08 '17 at 19:47
  • 2
    I think these Q&As may be relevant here: [Subset time series so that selected rows differs by a certain minimum time](http://stackoverflow.com/questions/41816629/subset-time-series-so-that-selected-rows-differs-by-a-certain-minimum-time?noredirect=1&lq=1) and [How to filter rows based on difference in dates between rows in R?](http://stackoverflow.com/questions/39317354/how-to-filter-rows-based-on-difference-in-dates-between-rows-in-r) – Henrik Feb 08 '17 at 20:39

2 Answers2

4

start_date will contain a vector of the dates to include after the loop:

start_date <- datum$testdate[1]
for (x in datum$testdate) {
  check_new <- (start_date[length(start_date)] + 365)
  if (x > check_new) {
    start_date <- c(start_date, x)
  }
}
AidanGawronski
  • 2,055
  • 1
  • 14
  • 24
  • That's pretty ingenious! But are loops allowed practice in R? I come from MySQL, so I'm rather procedural minded and didn't think R should be treated that way. But this works! – MS Berends Feb 08 '17 at 20:08
3
#Calculate difference in days between rows
difference = df$testdate - df$testdate[1]

#First values >365 signifies start of a new year.
#For other values subtract the first greatest value which is greater than 365
#Repeat until all values are less than 365
while (max(difference) > 365){
difference[which(difference > 365)] = difference[which(difference > 365)] - difference[which(difference > 365)][1]
}

#0 value in difference are the indices you want to extract from df
df[difference == 0,]

OR use a custom function like this

identify_new_year = function(x){
    indices = integer(0)
    start = x[1]
    ind = 1
    indices[ind] = ind
    for (i in 2:length(x)){
        if (as.numeric(x[i] - start >= 365)){
            ind = ind + 1
            indices[ind] = i
            start = x[i]
        }
    }
    return(indices)
}

identify_new_year(df$testdate)
#[1]  1 10 22 29 32 33
d.b
  • 32,245
  • 6
  • 36
  • 77