Parsing complicated date text in R

Viewed 54

I would like to extract all the dates from some text content. The content consists of date text like this:

21, 17, 16, 12, 10, 6, 5, 3 June 2019, 30 and 28, 27 May 2019

I expect to keep all the dates in a list() like this:

c("2019-06-21", "2019-06-17", "2019-06-16", "2019-06-12", "2019-06-10", "2019-06-06", "2019-06-05", "2019-06-03", "2019-05-30", "2019-05-28", "2019-05-27")

Is that possible to do that? Thanks.

2 Answers

As pointed out in the comment, the simple answer is to clean the data into a format which R understands. If the data is imported from another software, it is often (if not always) easier from that software rather than R.

That said, it is always possible to translate, although for these tasks it has to be done manually. Below is an illustration of how this could be achieved in R, using only the base package.

dates <- '21, 17, 16, 12, 10, 6, 5, 3 June 2019, 30 and 28, 27 May 2019'
#split on ', ' and ' and '
split_dates <- strsplit(dates, ", | and ", perl = TRUE)[[1]] 
#Find the dats which contain months and year
long_dates <- which(nchar(split_dates) > 2)
#Function to format dates
make_dates <- function(string){
    string <- unlist(strsplit(string, " "))
    nString <- length(string)
    year <- string[nString]
    month <- string[nString - 1]
    as.Date(paste0(year, month, string[seq(nString - 2)]), format = '%Y%B%d')
}
#Date vector for output
output_Dates <- integer(length(split_dates))
class(output_Dates) <- "Date"
j <- 0
for(i in long_dates){
    output_Dates[j:i] <- make_dates(split_dates[j:i])
    j <- i + 1
}
output_Dates

[1]"2019-06-21" "2019-06-17" "2019-06-16" "2019-06-12" "2019-06-10" "2019-06-06" "2019-06-05" "2019-06-03" "2019-05-30" "2019-05-28" "2019-05-27"

Note that you seem to be lacking 2019-05-30 in your expected output for it to be consistent.

To complement @Oliver answer, there is a solution which uses stringr and lubridate packages and implements quite simple regular expressions.

First of all, find the month-year blocks (like "June 2019"):

mny_loc_list <- str_locate_all(date_string, 
    paste0("\\b(", paste(month.name, collapse = "|"), ")", "\\s*\\d{4}"))
print(mny_loc_list)
> > mny_loc_list
[[1]]
     start end
[1,]    29  38
[2,]    55  62
[3,]    72  81

Note, please, that the built-in month.name vector should correspond to the month names in your original dates string. Inconsistencies may be fixed with proper setting of the locales or by setting a month names vector manually.

Then, create a function to transform the dates corresponding to each month-year block to the calendar dates:

ExtractForMonth <- function(list_entry, string_entry) {

    # define the end of a previous month-year block
    if (string_entry %in% 1) {
        block_begin <- 1
    } else {
        # take the end of a previous entry if it is not the first block
        block_begin <- list_entry[(string_entry - 1), 2] + 1
    }

    n_day <- str_sub(date_string, block_begin, list_entry[string_entry, 1] - 1)
    month_year <- str_sub(date_string, 
        list_entry[string_entry, 1], list_entry[string_entry, 2])
    day_date <- str_extract_all(n_day, "\\b\\d+?\\b")
    date_final <- paste0(unlist(day_date), " ", month_year)
    return(lubridate::dmy(date_final))

}

Finally, apply this function to each pair of the month-year block locations:

dates_list <- lapply(
    function(i) ExtractForMonth(list_entry = mny_loc_list[[1]],
        string_entry = i), 
    X = seq(to = nrow(mny_loc_list[[1]])))

print(dates_list)
[[1]]
[1] "2019-06-21" "2019-06-17" "2019-06-16" "2019-06-12" "2019-06-10"
[6] "2019-06-06" "2019-06-05" "2019-06-03"

[[2]]
[1] "2019-05-30" "2019-05-28" "2019-05-27"
Related