Home > Blockchain >  Tranforming dates into Weeks and Fortnights by Months using lubridate
Tranforming dates into Weeks and Fortnights by Months using lubridate

Time:02-11

I want to transform dates into weeks and fortnights by months using lubridate R package. My working example is below:

library(tidyverse)
library(lubridate)

dt1 <- 
  tibble(
    Date      = seq(from = ymd("2021-01-01"), to = ymd("2021-12-31"), by = '1 day')
  , Week      = week(Date)
  , Fortnight = ceiling(week(Date)/2)
  , Month     =  month(Date)
    )
dt2 <-
  dt1 %>% 
  group_by(Month) %>% 
  mutate(
    WK = week(Date)
  , FN = ceiling(week(Date)/2)
    ) 

head(dt2, 10)
# A tibble: 10 x 6
# Groups:   Month [1]
   Date        Week Fortnight Month    WK    FN
   <date>     <dbl>     <dbl> <dbl> <dbl> <dbl>
 1 2021-01-01     1         1     1     1     1
 2 2021-01-02     1         1     1     1     1
 3 2021-01-03     1         1     1     1     1
 4 2021-01-04     1         1     1     1     1
 5 2021-01-05     1         1     1     1     1
 6 2021-01-06     1         1     1     1     1
 7 2021-01-07     1         1     1     1     1
 8 2021-01-08     2         1     1     2     1
 9 2021-01-09     2         1     1     2     1
10 2021-01-10     2         1     1     2     1

tail(dt2, 10)
# A tibble: 10 x 6
# Groups:   Month [1]
   Date        Week Fortnight Month    WK    FN
   <date>     <dbl>     <dbl> <dbl> <dbl> <dbl>
 1 2021-12-22    51        26    12    51    26
 2 2021-12-23    51        26    12    51    26
 3 2021-12-24    52        26    12    52    26
 4 2021-12-25    52        26    12    52    26
 5 2021-12-26    52        26    12    52    26
 6 2021-12-27    52        26    12    52    26
 7 2021-12-28    52        26    12    52    26
 8 2021-12-29    52        26    12    52    26
 9 2021-12-30    52        26    12    52    26
10 2021-12-31    53        27    12    53    27

Questions

  1. WK (Weeks by Months) and FT (Fortnight by Months) are not as expected.
  2. Want to label Weeks as January 01-07, January 08-14, January 15-21, January 22-28, January 29-31, etc.
  3. Want to label Fortnights as January 01-14, January 15-28, January 29-31, etc.

CodePudding user response:

We may need to group by the 'WK', 'FT' to do this

library(dplyr)
library(lubridate)
library(stringr)
dt1 %>% 
  group_by(Month) %>%
  mutate(WK = week(Date), FN = ceiling(week(Date)/2)) %>% 
  group_by(WK, .add = TRUE) %>% 
  mutate(WKfmt = str_c(format(min(Date), '%B %d-'), 
      format(max(Date), '%d'))) %>% 
  group_by(Month, FN) %>% 
  mutate(FNfmt = str_c(format(min(Date), '%B %d-'), 
      format(max(Date), '%d'))) %>%
  ungroup

-output

# A tibble: 365 × 8
   Date        Week Fortnight Month    WK    FN WKfmt         FNfmt        
   <date>     <dbl>     <dbl> <dbl> <dbl> <dbl> <chr>         <chr>        
 1 2021-01-01     1         1     1     1     1 January 01-07 January 01-14
 2 2021-01-02     1         1     1     1     1 January 01-07 January 01-14
 3 2021-01-03     1         1     1     1     1 January 01-07 January 01-14
 4 2021-01-04     1         1     1     1     1 January 01-07 January 01-14
 5 2021-01-05     1         1     1     1     1 January 01-07 January 01-14
 6 2021-01-06     1         1     1     1     1 January 01-07 January 01-14
 7 2021-01-07     1         1     1     1     1 January 01-07 January 01-14
 8 2021-01-08     2         1     1     2     1 January 08-14 January 01-14
 9 2021-01-09     2         1     1     2     1 January 08-14 January 01-14
10 2021-01-10     2         1     1     2     1 January 08-14 January 01-14
# … with 355 more rows
  • Related