R split daily hour ranges into 7 separate columns Monday - Sunday with hours

user_10853784 0
Viewed 34

Hi I have a task of sorting daily open hours into a usable format more similar to google's opening hours, e.g:

Monday 06:00 - 18:00
Tuesday 08:00 - 18:00
...
Sunday Closed

There is a column opening hours 100K+ but a sample shown below of the irregularly formats. I thought I could use regular expressions to use the group matches and assign where found but I can't find a way of not using the group match when they are NA.

Times <-  data.frame(opening_hours = c("Su 09:00-17:00,M-S 08:00-20:00",
"Su-S 06:00-23:59",
"Su-S 08:00-22:00",
"Su 08:00-22:00,M-F 08:30-22:00,S 08:00-22:00",
"Su-S 00:00-23:59",
"M-F 08:00-22:00",
"M-W 08:00-22:00, Th-F 08:00-22:00"))

df <- as.data.frame(str_match(Times$opening_hours,"(?i)Su-S (\\d\\d:\\d\\d)-(\\d\\d:\\d\\d)"))

Times$Monday <- paste(df$V2, df$V3,sep = "-")
Times$Tuesday <- paste(df$V2, df$V3,sep = "-")
Times$Wednesday <- paste(df$V2, df$V3,sep = "-")
Times$Thursday <- paste(df$V2, df$V3,sep = "-")
Times$Friday <- paste(df$V2, df$V3,sep = "-")
Times$Satuarday <- paste(df$V2, df$V3,sep = "-")
Times$Sunday <- paste(df$V2, df$V3,sep = "-")


df <- as.data.frame(str_match(Times$opening_hours,"(?i)Su (\\d\\d:\\d\\d)-(\\d\\d:\\d\\d)"))
Times$Sunday <- paste(df$V2, df$V3,sep = "-")

Continued for each range format I can find in the data which isn't elegant but if I could conditionally check for V2 and V3 being NA because no date was found and not paste the values in it would work but if there is a better approach I am all ears.

Many thanks, Leo.

regex-group r
1 Answers

Here's what I have so far, which gets you at least partly there. There's a data row for each specification, with a "row" specifying the original source location.

library(tidyverse)
Times %>%
  mutate(row = row_number()) %>%
  separate_rows(opening_hours, sep = ",") %>%
  mutate(opening_hours = str_trim(opening_hours)) %>%
  separate(opening_hours, c("day_range", "hours"), sep = " ")


# A tibble: 11 x 3
   day_range hours         row
   <chr>     <chr>       <int>
 1 Su        09:00-17:00     1
 2 M-S       08:00-20:00     1
 3 Su-S      06:00-23:59     2
 4 Su-S      08:00-22:00     3
 5 Su        08:00-22:00     4
 6 M-F       08:30-22:00     4
 7 S         08:00-22:00     4
 8 Su-S      00:00-23:59     5
 9 M-F       08:00-22:00     6
10 M-W       08:00-22:00     7
11 Th-F      08:00-22:00     7

I think parsing the day ranges is tricky, especially if there might be any inconsistency around S/Sa/Su and T/Tu/Th that might be evident to a reader based on context but is hard to encode in logic. One approach might be to take the various day ranges that exist in your data (maybe 20 or 30 of those?) and create a lookup table, e.g. like this but for all the existing day ranges, and join that to the data above to help apply the row's hours to the right days.

tribble(~day_range, ~Su, ~Mo, ~Tu, ~Wed, ~Th, ~Fr, ~Sa,
            "Th-F",   0,   0,   0,    0,   1,   1,   0)
user_68518250
Related