Check if dates and times overlap for two separate data frames in R

user_15163662 0
Viewed 76

In a data frame containing traffic jams, start date, end date, start time, end time, road, direction (right or left), start road section and end road section are provided. An example looks like this:

start date    end date    start time    end time    road    direction   start road section    end road section
2015-03-02    2015-03-02     07:29        09:45      A1        R               113.1               125.7
2017-05-04    2017-05-05     23:29        01:45      A10       L               12.5                14.3

Another large dataframe (20+ million rows) contains all possible combinations of road sections (divided into pieces of 20 kms) and times (divided into hours) from 2015 until 2020. An example looks like this:

date         time         road      direction      road section
2015-03-02  09:00-10:00   A1          R               100 - 120
2015-03-02  10:00-11:00   A1          R               100 - 120
2015-03-02  09:00-10:00   A1          R               80 - 100

I would like to add a binary variable to the second data frame indicating whether or not there was a traffic jam at that moment on that certain road section. In this example, the first row would take the value 1 as both time and road section range overlap with the traffic jam in the first row of the first data frame. The second and third row, however, will take the value 0, as either time or road section do not overlap.

Any help and/or suggestions is highly appreciated!

r
1 Answers

As it turns out, this was a pretty tall task whose solution involved quite some work. In the end, it was a nice Sunday afternoon exercise. Let's hope it works with your full dataset.

Data

ex <- data.frame(start_date = c("2015-03-02", "2017-05-04"),
                 end_date = c("2015-03-02", "2017-05-05"), 
                 start_time = c("07:29", "23:29"), 
                 end_time = c("09:45", "01:45"), 
                 road = c("A1", "A10"), 
                 direction = c("R", "L"),
                 start_road_section = c(113.1, 12.5), 
                 end_road_section = c(125.7, 14.3)) 
df <- data.frame(date = c("2015-03-02", "2015-03-02", "2015-03-02"),
                 time = c("09:00-10:00", "10:00-11:00", "09:00-10:00"),
                 road = c("A1", "A1", "A1"), 
                 direction = c("R", "R", "R"), 
                 road_section = c("100 - 120", "100 - 120", "80 - 100"))

1st step: Cleaning the data

df %<>% mutate(time_component = strsplit(as.character(time), "-")) %>% 
               tidyr::unnest_wider(time_component, names_sep = "_")   %>%        
                     select(-time) %>% 
                         mutate(road_component = strsplit(as.character(road_section), " - ")) %>% 
                           tidyr::unnest_wider(road_component, names_sep = "_") %>% 
                                   select(-road_section)
> df
# A tibble: 3 x 7
  date       road  direction time_component_1 time_component_2 road_component_1 road_component_2
  <chr>      <chr> <chr>     <chr>            <chr>            <chr>            <chr>           
1 2015-03-02 A1    R         09:00            10:00            100              120             
2 2015-03-02 A1    R         10:00            11:00            100              120             
3 2015-03-02 A1    R         09:00            10:00            80               100

2nd step: Combining the data

comb_df <- dplyr::inner_join(ex,df, by=c("start_date" = "date", "road", "direction"))
comb_df$start_road_section <- as.numeric(comb_df$start_road_section) 
comb_df$end_road_section <- as.numeric(comb_df$end_road_section) 
comb_df$road_component_1 <- as.numeric(comb_df$road_component_1) 
comb_df$road_component_2 <- as.numeric(comb_df$road_component_2)

3rd step: Finding the overlap in time and road section

new_df <- comb_df %>% mutate(ex_start = lubridate::ymd_hm(paste(start_date, start_time, sep = " ")),
                    ex_end = lubridate::ymd_hm(paste(end_date, end_time, sep = " ")), 
                    df_start = lubridate::ymd_hm(paste(start_date, time_component_1, sep = " ")),
                    df_end = lubridate::ymd_hm(paste(start_date, time_component_2, sep = " ")),
                    Intervall_ex = lubridate::interval(start = ex_start, end = ex_end),
                    Intervall_df = lubridate::interval(start = df_start, end = df_end), 
                    Time_Contained = ifelse(int_overlaps(Intervall_ex, Intervall_df), 1, 0), 
                    Road_Contained = ifelse(((start_road_section < road_component_1) & (end_road_section > road_component_1)) | 
                                              ((start_road_section > road_component_1) & (start_road_section < road_component_2)), 
                                            1, 
                                            0), 
                    Final_Result = ifelse(Time_Contained + Road_Contained == 2, 1, 0))

4th and final step: Reducing the dataset

final_df <- new_df %>% select(start_date, end_date, road, 
                              direction, road_component_1, road_component_2, 
                              time_component_1, time_component_2, Final_Result)
> final_df
  start_date   end_date road direction road_component_1 road_component_2 time_component_1 time_component_2 Final_Result
1 2015-03-02 2015-03-02   A1         R              100              120            09:00            10:00            1
2 2015-03-02 2015-03-02   A1         R              100              120            10:00            11:00            0
3 2015-03-02 2015-03-02   A1         R               80              100            09:00            10:00            0
user_75518160
Related