summing within a day via an interval R

Question

I want to count the total number of people who were present on a given day. They have an interval (start.date) and (end.date). How can I count all those people who's interval fell within a given date? I thought about using lubridate and interval(), but I am still stuck as how to iterate this for each day? Please help.

first_date = as.Date(min("2011-01-01"))
last_date  = as.Date(max("2011-12-31"))
all_dates = seq(first_date, by=1, to=last_date)

CY_2015 <- data.frame(DATE = all_dates)

df <- data.frame(ID = c(1,2,3,4),
                 START.DATE = as.Date(c("2011-07-24", "2011-07-19", "2011-07-08", "2011-07-23")),
                 END.DATE = as.Date(c("2011-07-26", "2011-07-21", "2011-07-10", "2011-07-25")))

# I tried something like this to no avail:

for (i in 1:365) {
  DATE <- CY_2015$DATE[i]
  df <- df %>% mutate(new_var = in.interval.lo(DATE, START.DATE, END.DATE))
  names(df)[length(df)] <- paste0(DATE)
  }

[roll join with start/end window](http://stackoverflow.com/questions/24480031) — zx8754, Apr 06 '16 at 19:54

Dave2e · Answer 1 · 2016-04-08T19:25:09.993

This should do it. I used to apply to iterate through all of the days and "which" command on the data frame to determine the counts per day.

 #number of people present per day
countperday<-sapply(all_dates, function(x) {length(which(df$START.DATE<=x &df$END.DATE>=x))})
#results<-data.frame(counts=countperday, date=all_dates) 
  #only the days where someone was present
days<-all_dates[countperday>0]   
  #reduced results
results<-data.frame(counts=countperday[countperday>0], date=days)

May not be an optimal approach but should answer your question.

summing within a day via an interval R

1 Answers1