Date roll-up in R

Question

I have a dataset that looks as the following:

    ID  FromDate    ToDate  SiteID  Cost
    1   8/12/2014   8/31/2014   12  245.98
    1   9/1/2014    9/7/2014    12  269.35
    1   10/10/2014  10/17/2014  12  209.98
    1   11/22/2014  11/30/2014  12  309.12
    1   12/1/2014   12/11/2014  12  202.14
    2   8/16/2014   8/21/2014   12  109.35
    2   8/22/2014   8/24/2014   14  44.12
    2   9/25/2014   9/29/2014   12  98.75
    3   9/15/2014   9/30/2014   23  536.27
    3   10/1/2014   10/31/2014  12  529.87
    3   11/1/2014   11/30/2014  12  969.55
    3   12/1/2014   12/12/2014  12  607.35

What I would like this to look like is:

    ID  FromDate    ToDate  SiteID  Cost
    1   8/12/2014   9/7/2014    12  515.33
    1   10/10/2014  10/17/2014  12  209.98
    1   11/22/2014  12/11/2014  12  511.26
    2   8/16/2014   8/21/2014   12  109.35
    2   8/22/2014   8/24/2014   14  44.12
    2   9/25/2014   9/29/2014   12  98.75
    3   9/15/2014   9/30/2014   23  536.27
    3   10/1/2014   12/12/2014  12  2106.77

As one can see, the dates are rolled up if there is a continuation and the costs are summed up by ID and SiteID. To help someone understand the complexity, if there is a continuation in date interval, but the SiteID changes, then it is a separate row. If there is no continuation in date interval, it is a separate row. How do I do this in R? Also, I have over 100,000 individual IDs. So what is the most efficient way/package to use for this?

Khashaa · Accepted Answer · 2016-05-28T16:24:31.067

This might do

df %>% 
  mutate(gr = cumsum(FromDate-lag(ToDate, default=1) != 1)) %>% 
  group_by(gr, ID, SiteID) %>% 
  summarise(FromDate = min(FromDate), 
            ToDate   = max(ToDate), 
            cost     = sum(Cost))


     gr    ID SiteID   FromDate     ToDate    cost
  (int) (int)  (int)     (date)     (date)   (dbl)
1     1     1     12 2014-08-12 2014-09-07  515.33
2     2     1     12 2014-10-10 2014-10-17  209.98
3     3     1     12 2014-11-22 2014-12-11  511.26
4     4     2     12 2014-08-16 2014-08-21  109.35
5     4     2     14 2014-08-22 2014-08-24   44.12
6     5     2     12 2014-09-25 2014-09-29   98.75
7     6     3     23 2014-09-15 2014-09-30  536.27
8     6     3     12 2014-10-01 2014-12-12 2106.77

with data.table

library(data.table)
setDT(df)
df[, gr := cumsum(FromDate - shift(ToDate, fill=1) != 1),
   ][, list(FromDate=min(FromDate), ToDate=max(ToDate), cost=sum(Cost)), by=.(gr, ID, SiteID)]



   gr ID SiteID   FromDate     ToDate    cost
1:  1  1     12 2014-08-12 2014-09-07  515.33
2:  2  1     12 2014-10-10 2014-10-17  209.98
3:  3  1     12 2014-11-22 2014-12-11  511.26
4:  4  2     12 2014-08-16 2014-08-21  109.35
5:  4  2     14 2014-08-22 2014-08-24   44.12
6:  5  2     12 2014-09-25 2014-09-29   98.75
7:  6  3     23 2014-09-15 2014-09-30  536.27
8:  6  3     12 2014-10-01 2014-12-12 2106.77

I like this approach a lot better - how about simplifying to: `df %>% mutate(crit = FromDate-lag(ToDate, default=1)==1, gr = cumsum(crit==FALSE)) %>% group_by(gr, ID, SiteID) %>% summarise(cost = sum(Cost), FromDate = min(FromDate), ToDate = max(ToDate))` — JasonAizkalns, May 27 '16 at 16:37
@akash87 `ID` column is retained if you group by `ID`. Check the updated post. — Khashaa, May 28 '16 at 06:13

score 2 · Answer 2 · answered May 27 '16 at 15:23

Here's one way with dplyr and tidyr -- probably a few opportunities to clean this up, but the premise is to create a new group indicator. Somebody with some better data.table skills can probably come up with something pretty slick for this one.

library(dplyr)
library(tidyr)

df$FromDate <- lubridate::mdy(df$FromDate)
df$ToDate <- lubridate::mdy(df$ToDate)

gather(df, Date, Val, -c(ID, SiteID, Cost)) %>%
  arrange(ID, SiteID, Val, Date) %>%
  group_by(ID, SiteID) %>%
  mutate(lagDateDiff = as.integer(Val - lag(Val)),
         indicator = ifelse(Date == "ToDate" | is.na(lagDateDiff), 0, 
                            ifelse((Date == "FromDate" & lagDateDiff == 1), 0, 1)),
         newGroup = cumsum(indicator)) %>% # Run to here to see intermediate result
  select(-lagDateDiff, -indicator) %>%
  spread(Date, Val) %>%
  group_by(ID, SiteID, newGroup) %>%
  summarise(Min_From_Date = min(FromDate),
            Max_To_Date = max(ToDate),
            Sum_Cost = sum(Cost))

#     ID SiteID newGroup Min_From_Date Max_To_Date Sum_Cost
#   (int)  (int)    (dbl)        (date)      (date)    (dbl)
# 1     1     12        0    2014-08-12  2014-09-07   515.33
# 2     1     12        1    2014-10-10  2014-10-17   209.98
# 3     1     12        2    2014-11-22  2014-12-11   511.26
# 4     2     12        0    2014-08-16  2014-08-21   109.35
# 5     2     12        1    2014-09-25  2014-09-29    98.75
# 6     2     14        0    2014-08-22  2014-08-24    44.12
# 7     3     12        0    2014-10-01  2014-12-12  2106.77
# 8     3     23        0    2014-09-15  2014-09-30   536.27

I am not familiar with the %>% notation. Could you provide a link or some documentation regarding this? — akash87, May 27 '16 at 16:04
`%>%` comes from the [`magrittr` package](https://cran.r-project.org/web/packages/magrittr/vignettes/magrittr.html). In short, it is known as a "pipe"-like operator, with which you pipe a value forward into an expression or call. Instead of `f(x)` we can write `x %>% f` which makes certain chains of code easier to read and maintain. — JasonAizkalns, May 27 '16 at 16:30

Date roll-up in R

2 Answers2

Linked