Disaggregate data over different years with dplyr

user_11167812 0
Viewed 54

I have a data frame and I need to dis-aggregate it over different years. The data frame is:

df = data.frame(x = 1000, time_L = '2018-12-30', time_R = "2020-10-30")

and I need the output to be like this:

  time_L     time_R    diff xprime
 2018-12-30 2018-12-31  2      3
 2019-01-01 2019-12-31 365    544
 2020-01-01 2020-10-30 304    453

xprime is dis-aggregated over each time interval based on the number of days in it.

Thanks in advance.

dplyr r
1 Answers
library(tidyverse)
library(lubridate)

df %>%
  mutate(across(contains('time'),ymd),
         start = list(seq(floor_date(time_L, "year"), time_R, "1 year")),
         end = list(lead(start[[1]] - 1, default = last(time_R)))) %>%
  unnest(c(start, end)) %>%
  mutate(start = pmax(start, time_L),
         diff = as.numeric(end-start) + 1,
         xprime = round(prop.table(diff)*x))
  
# A tibble: 3 x 7
      x time_L     time_R     start      end         diff xprime
  <dbl> <date>     <date>     <date>     <date>     <dbl>  <dbl>
1  1000 2018-12-30 2020-10-30 2018-12-30 2018-12-31     2      3
2  1000 2018-12-30 2020-10-30 2019-01-01 2019-12-31   365    544
3  1000 2018-12-30 2020-10-30 2020-01-01 2020-10-30   304    453
user_83802720
Related