CODEWITHSUNDEEP
rdataframe
jontieez
jontieez

Reputation: 79

Converting monthly intervals into weeks in R

I've been wrapping my head around this question. So my task is to convert the data from a monthly basis to a weekly basis (Sunday to Sat would be each week). The data looks like this:

Name Meal Food date from date to
Albert lunch apple 30/05/2021 26/06/2021
Lucas dinner banana 30/05/2021 26/06/2021
John lunch grape 30/05/2021 26/06/2021

The Weekly buckets would look like this:

Name Meal Food date from date to
Albert lunch apple 1/06/2021 5/06/2021
Albert lunch apple 6/06/2021 12/06/2021
Albert lunch apple 13/06/2021 19/06/2021
Albert lunch apple 20/06/2021 26/06/2021
Lucas dinner banana 1/06/2021 5/06/2021
Lucas dinner banana 6/06/2021 12/06/2021
Lucas dinner banana 13/06/2021 19/06/2021
Lucas dinner banana 20/06/2021 26/06/2021
John lunch grape 1/06/2021 5/06/2021
John lunch grape 6/06/2021 12/06/2021
John lunch grape 13/06/2021 19/06/2021
John lunch grape 20/06/2021 26/06/2021

I'm lost to start, any help would be appreciated.

Upvotes: 0

Views: 270

Answers (1)

Anoushiravan R
Anoushiravan R

Reputation: 21908

I decided to write a custom function for this purpose:

library(dplyr)
library(lubridate)
library(tidyr)

fn <- function(x, y) {
  stopifnot(is.Date(x))
  stopifnot(is.Date(y))
  
  intv <- floor(interval(x, y) / weeks(1))
  a <- x + (0:intv) * 7
  b <- a + days(6)
  a[1] <- round_date(a[1], unit = "month")
  
  
  dts <- tibble(start = a, end = b)
  dts
}
# Then we test our function with a pair of dates
fn(ymd("2021-05-30"), ymd("2021-06-26"))

# A tibble: 4 x 2
  start      end       
  <date>     <date>    
1 2021-06-01 2021-06-05
2 2021-06-06 2021-06-12
3 2021-06-13 2021-06-19
4 2021-06-20 2021-06-26

Then we apply our function to our original data set:

DF %>%
  mutate(across(starts_with("date"), ~ dmy(.x))) %>%
  rowwise() %>%
  mutate(output = list(fn(date_from, date_to))) %>%
  unnest(output) %>%
  select(!starts_with("date"))

# A tibble: 12 x 5
   Name   Meal   Food   start      end       
   <chr>  <chr>  <chr>  <date>     <date>    
 1 Albert lunch  apple  2021-06-01 2021-06-05
 2 Albert lunch  apple  2021-06-06 2021-06-12
 3 Albert lunch  apple  2021-06-13 2021-06-19
 4 Albert lunch  apple  2021-06-20 2021-06-26
 5 Lucas  dinner banana 2021-06-01 2021-06-05
 6 Lucas  dinner banana 2021-06-06 2021-06-12
 7 Lucas  dinner banana 2021-06-13 2021-06-19
 8 Lucas  dinner banana 2021-06-20 2021-06-26
 9 John   lunch  grape  2021-06-01 2021-06-05
10 John   lunch  grape  2021-06-06 2021-06-12
11 John   lunch  grape  2021-06-13 2021-06-19
12 John   lunch  grape  2021-06-20 2021-06-26

Upvotes: 2

Related Questions