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
- WK (Weeks by Months) and FT (Fortnight by Months) are not as expected.
- Want to label Weeks as January 01-07, January 08-14, January 15-21, January 22-28, January 29-31, etc.
- Want to label Fortnights as January 01-14, January 15-28, January 29-31, etc.
>Solution :
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