CODEWITHSUNDEEP
rtime
Sim
Sim

Reputation: 25

Check if dates are within a time frame r

I have two datasets, one with values at specific time points for different IDs and another one with several time frames for the IDs. Now I want to check if the timepoint in dataframe one is within any of the time frames from dataset 2 matching the ID. For example:

df1:

ID  date           time
1   2020-04-14   11:00:00
1   2020-04-14   18:00:00
1   2020-04-15   10:00:00
1   2020-04-15   20:00:00
1   2020-04-16   11:00:00
1   ...
2   ...

df2:

ID  start                  end
1  2020-04-14  16:00:00  2020-04-14 20:00:00
1  2020-04-15  18:00:00  2020-04-16 13:00:00
2  ...
2

what I want df1_new:

ID  date           time      mark
1   2020-04-14   11:00:00   0
1   2020-04-14   18:00:00   1
1   2020-04-15   10:00:00   0
1   2020-04-15   20:00:00   1
1   2020-04-16   11:00:00   1
1   ...
2   ...

Any help would be appreciated!

Upvotes: 0

Views: 77

Answers (1)

Yuriy Saraykin
Yuriy Saraykin

Reputation: 8880

An option could be:

library(tidyverse)
library(lubridate)
 
#>     date, intersect, setdiff, union
df_1 <- structure(list(ID = c(1L, 1L, 1L, 1L, 1L), date = c("14.04.2020", 
                                                            "14.04.2020", "15.04.2020", "15.04.2020", "16.04.2020"), time = c("11:00:00", 
                                                                                                                              "18:00:00", "10:00:00", "20:00:00", "11:00:00"), date_time = structure(c(1586862000, 
                                                                                                                                                                                                       1586887200, 1586944800, 1586980800, 1587034800), class = c("POSIXct", 
                                                                                                                                                                                                                                                                  "POSIXt"), tzone = "UTC")), class = "data.frame", row.names = c(NA, 
                                                                                                                                                                                                                                                                                                                                  -5L))
df_2 <- structure(list(ID = c(1L, 1L), start = c("14.04.2020 16:00", 
                                                 "15.04.2020 18:00"), end = c("14.04.2020 20:00", "16.04.2020 13:00"
                                                 )), class = "data.frame", row.names = c(NA, -2L))


df_22 <- df_2 %>% 
  mutate(across(c("start", "end"), dmy_hm)) %>% 
  group_nest(ID)  

left_join(x = df_1, y = df_22, by = "ID") %>% 
  as_tibble() %>% 
  mutate(mark = map2_dbl(date_time, data, ~+any(.x %within% interval(.y$start, .y$end)))) %>% 
  select(-data)
#> # A tibble: 5 x 5
#>      ID date       time     date_time            mark
#>   <int> <chr>      <chr>    <dttm>              <dbl>
#> 1     1 14.04.2020 11:00:00 2020-04-14 11:00:00     0
#> 2     1 14.04.2020 18:00:00 2020-04-14 18:00:00     1
#> 3     1 15.04.2020 10:00:00 2020-04-15 10:00:00     0
#> 4     1 15.04.2020 20:00:00 2020-04-15 20:00:00     1
#> 5     1 16.04.2020 11:00:00 2020-04-16 11:00:00     1

Created on 2021-05-25 by the reprex package (v2.0.0)

Upvotes: 1

Related Questions