Extracting the times using Regular Expression

In this example, I want to extract the opening times from the text using regular expression. The time format is iregular, such as “Monday: 5:30 PM – 7:15 PM, 5:30 – 7:15 PM”, “Closed”, “Opening 24 hours”, or “” (i.e., nothing). The target output is a tidy data frame, in the format of “id,open,close”. If any id has multiple opening-closing times in the day, multiple lines will be generated for that id.

This is the input data:

id <- letters[1:6]
openHour <- c("Monday: 5:30 PM – 7:15 PM, 5:30 PM – 7:15 PM", "Monday: 5:30 PM – 7:15 PM", "Monday: 5:30 PM – 7:15 PM, 5:30 – 7:15 PM, 9:00 PM - 12:00 AM", "", "Monday: Closed", "Monday: Open 24 hours")
dfInput <- data.frame(id = id, Monday = openHour)
dfInput

This is the targeted result:

dfResult <- data.frame(id = c("a","b","c", "e", "f", "a", "c", "c"), open = c("17:30","17:30","17:30","0:00","0:00","17:30","17:30","21:00"), close = c("19:15","19:15","19:15","0:00","23:59","19:15","19:15","0:00"), stringsAsFactors = F)
dfResult

These are the packages that are loaded and used here.

require(dplyr)
require(stringr)
require(data.table)

The first step is to tidy up the data, which includes the following functions: replacing non-ASCII characters (“–”) removing “Monday:” replacing irregular formats of “Closed” and “Open 24 hours”.

We are going to replace “-” with “T”, “Closed” with “12:00 AM T 12:00 AM”, and “Open 24 hours” with “12:00 AM T 11:59 PM”. Note that “-” is replaced by “T”, as “T” is never used in the time format here, and “T” means “TO”.

After the processing, we are going to extract the time formats. There are two types of time formats, namely “5:30 PM T 7:15 PM” and “5:30 T 7:15 PM”. Regular expressions are very powerful to express the patterns.

listExtract <- dfInput$Monday %>% str_replace_all("[^[:alnum:] :]", replacement = "T") %>%
    str_replace_all("Monday: ", replacement = "") %>%
    str_replace_all("Closed", replacement = "12:00 AM T 12:00 AM") %>%
    str_replace_all("Open 24 hours", replacement = "12:00 AM T 11:59 PM") %>%
    str_extract_all("[:digit:]+:[:digit:]+ [:alpha:]* *T [:digit:]+:[:digit:]+ [:alpha:]*")
listExtract

[[1]]
[1] "5:30 PM T 7:15 PMT" "5:30 PM T 7:15 PM" 

[[2]]
[1] "5:30 PM T 7:15 PM"

[[3]]
[1] "5:30 PM T 7:15 PMT" "5:30 T 7:15 PMT"    "9:00 PM T 12:00 AM"

[[4]]
character(0)

[[5]]
[1] "12:00 AM T 12:00 AM"

[[6]]
[1] "12:00 AM T 11:59 PM"

After extracting the times, the result is a list of character vectors that have different lengths (from 0 to 3). The list will be converted into a data frame.

dfExtract <- plyr::ldply(listExtract, rbind) %>% 
    cbind(dfInput$id) %>%
    rename(id = `dfInput$id`, time1=`1`, time2=`2`, time3 =`3`)
dfExtract

The data frame dfExtract consists of many NA values. Moreover, the format is different from the objective format. Using melt function to adjust the format.

dfExtract <- dfExtract %>% 
    data.table::melt(id="id") %>% 
    select(id, value) %>%
    filter(!is.na(value))

attributes are not identical across measure variables; they will be dropped

dfExtract

The final step is to extract the opening and closing times from the value column. The new functions are used here. Function of addAMPM is to transform the format of “5:30 – 7:15 PM” into “5:30 PM – 7:15 PM”. The other function, called reformatTime, is to transform the time format into 24-hour. For example, “7:00 AM” is transformed into “7:00”, and “7:00 PM” into “19:00”.

Note the format of hour:minute. We want to use the uniform 2-digit minute format, so the C-style sprintf function is used here.

# a function to reformat time: 7:00 AM as 7:00
# 7:00 PM as 19:00
reformatTime <- function(time){
    newTime <- strptime(time,format="%I:%M %p")
    paste0(hour(newTime), ":", sprintf("%02d",minute(newTime)))
}
# Turn "5:30 – 7:15 PM" into "5:30 PM – 7:15 PM"
# If the input is "5:30 PM – 7:15 PM", the output is the same as input
addAMPM <- function(time){
    # if it is in the format of 5:30 – 7:15 PM
    if(!grepl(x = time, pattern = ".*[AP]M.*[AP]M")){
        apm <- str_extract_all(time, pattern = "[AP]M")[[1]]
        # apm: "PM"
        splitVec <- str_split(time, pattern = "T")[[1]]
        # splitVec: "5:30 ", "7:15 PM"
        newTime <- paste0(splitVec[1], apm, " T", splitVec[2])
        newTime
    }
    else{
        return(time)
    }
}
# the vectorized version of addAMPM
vecAddAMPM <- Vectorize(addAMPM, SIMPLIFY = T)
# Extracting the open and close times.
dfExtract <- dfExtract %>%
    mutate(value = vecAddAMPM(value)) %>%
    mutate(open = reformatTime(str_extract(value, pattern = "^[:digit:]+:[:digit:]+ [:alpha:]*"))) %>%
    mutate(close = reformatTime(str_extract(value, pattern = "[:digit:]+:[:digit:]+ [:alpha:]*$"))) %>%
    select(-value)
dfExtract