count the number of days between two dates per year

user_7220895 0
Viewed 1095

I have a dataframe with a start and end date for each row. I would like to calculate the number of days between the two dates and split it by year. So going from this:

id <- c(1,2,3)
start <- as.Date(c('01/01/2015','01/01/2016','07/01/2015'), format = '%m/%d/%Y')
end <- as.Date(c('12/31/2016','12/31/2016','12/31/2016'), format = '%m/%d/%Y')
df <- data.frame(id, start, end)
id Start End
1 01/01/2015 12/31/2016
2 01/01/2016 12/31/2016
3 01/07/2015 12/31/2016

To this:

id Start End days_no. year_2015 year_2016
1 01/01/2015 12/31/2016 730 365 365
2 01/01/2016 12/31/2016 365 0 365
3 07/01/2015 12/31/2016 548 183 365

Any help is appreciated, please note i would like to compute the yearly stats dynamically, i may end up with many year columns in my practical case... I'm guessing lubridate may help but i'm unsure where to start.

lubridate r date
2 Answers

Here is a base R option

transform(
  df,
  days_no = end - start,
  year_2015 = pmax(as.Date("2015-12-31") - start, 0),
  year_2016 = pmax(end - as.Date("2016-1-1"), 0)
)

which gives

  id      start        end  days_no year_2015 year_2016
1  1 2015-01-01 2016-12-31 730 days  364 days  365 days
2  2 2016-01-01 2016-12-31 365 days    0 days  365 days
3  3 2015-07-01 2016-12-31 549 days  183 days  365 days
user_121587570

Here's one approach using tidyverse and lubridate.

First, separate the rows by calendar year, to use to measure the number of days for each year. Each row will include dates to be counted in each calendar year, starting with January 1st and ending with December 31st if overlapping multiple years. Then, it is easy to calculate the number of days in a given year.

The results from this example are slightly different than what I have. Year 2016 is a leap year and has 366 days. If the number of days are not inclusive of either start or end dates, you would get a different answer.

library(tidyverse)
library(lubridate)

df %>%
  mutate(date_int = interval(start, end),
         year = map2(year(start), year(end), seq)) %>%
  unnest(year) %>%
  mutate(year_int = interval(as.Date(paste0(year, '-01-01')), as.Date(paste0(year, '-12-31'))),
         year_sect = intersect(date_int, year_int),
         start_new = as.Date(int_start(year_sect)),
         end_new = as.Date(int_end(year_sect))) %>%
  select(id, start_new, end_new) %>%
  mutate(year = year(start_new),
         days = as.numeric(end_new - start_new)) %>%
  right_join(df) %>%
  pivot_wider(id_cols = c(id, start, end), names_from = year, values_from = days, names_prefix = "year_", values_fill = list(days = 0)) %>%
  mutate(days_number = reduce(select(., starts_with("year_")), `+`))

Output

     id start      end        year_2015 year_2016 days_number
  <dbl> <date>     <date>         <dbl>     <dbl>       <dbl>
1     1 2015-01-01 2016-12-31       364       365         729
2     2 2016-01-01 2016-12-31         0       365         365
3     3 2015-07-01 2016-12-31       183       365         548
user_34606700
Related