2 week sum ending on every second Tuesday

user_8742237 0
Viewed 257

I have some data in a format like the reproducible example below (code for data input below the question, at the end). Two things:

  1. Not all dates have a value (i.e. many dates are missing).
  2. Some dates have multiple values, eg 16 June 2020.
#>        date value
#> 1 30-Jun-20    20
#> 2 29-Jun-20  -100
#> 3 26-Jun-20    -4
#> 4 16-Jun-20   -13
#> 5 16-Jun-20    40
#> 6  9-Jun-20    -6

For two week periods, ending on Tuesdays, I would like to take a sum of the value column.

So in the example data above, I want to sum ending on:

  • two weeks ending on Tuesday 16 June 2020 (i.e. from 3 June 2020 - 16 June 2020, inclusive)
  • two weeks ending on Tuesday 30 June 2020 (17 June 2020 - 30 June 2020 inclusive)
  • I'd ultimately like the code to continue summing all two week periods ending on every second Tuesday for when there's more data.

So my desired output is:

#2_weeks_end     total
#30-Jun-20    -84
#16-Jun-20     21

Tidyverse and lubridate solutions would be my first preference.

Code for data input below:

df <- data.frame(
  stringsAsFactors = FALSE,
                date = c("30-Jun-20","29-Jun-20",
                       "26-Jun-20","16-Jun-20","16-Jun-20","9-Jun-20"),
                value = c(20L, -100L, -4L, -13L, 40L, -6L)
)
df
dplyr tidyverse lubridate r
2 Answers

Solution using findInterval().

df$date <- dmy(df$date)
df_intervals <- seq(as.Date("2020-06-03"), as.Date("2020-06-03")+14*3, 14)

df %>%
  mutate(interval = findInterval(date, df_intervals)) %>%
  mutate(`2_weeks_end` = df_intervals[interval+1]-1) %>%
  group_by(`2_weeks_end`) %>%
  summarise(total= sum(value))

Returns:

# A tibble: 2 x 2
  2_weeks_end   total
  <date>     <int>
1 2020-06-16    21
2 2020-06-30   -84
user_139067180

Here is an option if you like weekly or any other unit that is in lubridate by default:

library(dplyr)
library(lubridate)
df%>%
    mutate(date = as.Date(date, format = "%d-%b-%y"))%>%
    group_by(week_ceil = ceiling_date(date - 1L, unit = "week", week_start = 2L))%>%
    summarize(sums = sum(value))

Here is a data.table approach that creates a reference table followed by a non-equi join:

library(data.table)
setDT(df)
df[, date := as.Date(date, format = "%d-%b-%y")]

ref_dt = df[,  .(beg_date = seq.Date(from = floor_date(min(date), unit = "week", week_start = 3L),
               to = max(date), 
               by =  "2 weeks"))]
ref_dt[, end_date := beg_date +13L]

df[ref_dt, 
   on = .(date > beg_date,
          date <= end_date),
   sum(value),
   by = .EACHI]

##         date       date  V1
##1: 2020-06-03 2020-06-16  21
##2: 2020-06-17 2020-06-30 -84
user_113550660
Related