R: getting if a value is between two dates and count

Question

I have the following data frame in R

df1 <- data.frame(id = c(12332, 231231, 123123, 1231231, 123123),
                  date_in = c("04/08/2019 04:00", "04/08/2019 06:00", "05/08/2019 04:00",
                              "08/08/2019 12:00", "12/08/2019 04:00"),
                  date_out = c("20/08/2019 04:00", "14/08/2019 13:00", "11/08/2019 04:00",
                              "30/08/2019 04:00"))

I would like to create another data frame which counts how many id where in in every day of the months

Any ideas?

Thank you!

score 1 · Answer 1 · answered Apr 18 '20 at 14:33

A tidyverse approach could be :

library(dplyr)

df1 %>%
  #Convert to date
  mutate_at(-1, ~as.Date(lubridate::dmy_hm(.))) %>%
  #Create a sequence of date from date_in to date_out
  mutate(date = purrr::map2(date_in, date_out, seq, by  ="1 day")) %>%
  #Get list dates in long format
  tidyr::unnest(date) %>%
  #Count number of id's in each date
  count(date)

# A tibble: 27 x 2
#   date           n
#   <date>     <int>
# 1 2019-08-04     2
# 2 2019-08-05     3
# 3 2019-08-06     3
# 4 2019-08-07     3
# 5 2019-08-08     4
# 6 2019-08-09     4
# 7 2019-08-10     4
# 8 2019-08-11     4
# 9 2019-08-12     4
#10 2019-08-13     4
# … with 17 more rows

data

df1 <- structure(list(id = c(12332, 231231, 123123, 1231231, 123123), 
date_in = structure(1:5, .Label = c("04/08/2019 04:00", "04/08/2019 06:00", 
"05/08/2019 04:00", "08/08/2019 12:00", "12/08/2019 04:00"
), class = "factor"), date_out = structure(c(3L, 2L, 1L, 
4L, 4L), .Label = c("11/08/2019 04:00", "14/08/2019 13:00", 
"20/08/2019 04:00", "30/08/2019 04:00"), class = "factor")), 
class = "data.frame", row.names = c(NA, -5L))

R: getting if a value is between two dates and count

1 Answers1