Extract date after string in R

user_11521889 0
Viewed 139

I'm trying to extract dates from a Notes column using tidyr's extract function. The data I'm working on looks like this:

dates <- data.frame(col1 = c("customer", "customer2", "customer3"),
                    Notes = c("DOB: 12/10/62
START: 09/01/2019
END: 09/01/2020", "
S/DATE: 28/08/19
R/DATE: 27/08/20", "DOB: 13/01/1980
Start:04/12/2018"),
                    End_date = NA,
                    Start_Date = NA )

I tried extracting the date following the string "S/DATE" like this:

extract <- extract(
  dates,
  col = "Notes",
  into = "Start_date",
  regex = "(?<=(S\\/DATE:)).*"  # Using regex lookahead
)

However, this only extracts the string "S/DATE:", not the date after it. When I tried this on regex101.com, it works as expected.

Thanks. Ibrahim

tidyr r regex
4 Answers

You could use sub here for a base R option:

s_date <- ifelse(grepl("S/DATE", dates$Notes),
                 sub("^.*\\bS/DATE: (\\S+).*$", "\\1", dates$Notes), NA)
s_date

[1] NA         "28/08/19" NA

Note that the call to grepl above is needed here, because sub by default will return the entire input string (in this case the full Notes) in the event that S/DATE be not found in the text.

user_18632290

One method can be like this one also. (Assuming that you need either of S/DATE or START as your expected new column name is Start_date). If however all such values aren't required you may easily modify this syntax.

Explanation -

  • In the innermost expr Notes column has been splitted into list by either of these separators : or \n.
  • In this list, blanks are removed then
  • In the modified list item next to Start or S/Date is extracted using sapply which simplifies the list into a vector (if possible)
  • lastly lubridate::dmy is used in outermost expr.
sapply(strsplit(dates$Notes, 
                 "[: | \n]"), 
       function(x) subset(x, x != "")[1 + which(toupper(subset(x, x != "")) %in% c("S/DATE", "START"))])

[1] "09/01/2019" "28/08/19"   "04/12/2018"

If you'll wrap the above in lubridate::dmy dates will be correctly formatted too

dmy(sapply(strsplit(dates$Notes, 
                        "[: | \n]"), 
               function(x) subset(x, x != "")[1 + which(toupper(subset(x, x != "")) %in% c("S/DATE", "START"))]))

[1] "2019-01-09" "2019-08-28" "2018-12-04"

Further, this can be passed into dplyr pipes, so as to simultaneously create a new column in your dates

dates %>% mutate(Start_Date = dmy(sapply(strsplit(Notes, 
                                                  "[: | \n]"), 
                                         function(x) subset(x, x != "")[1 + which(toupper(subset(x, x != "")) %in% c("S/DATE", "START"))])))

       col1                                             Notes End_date Start_Date
1  customer DOB: 12/10/62\nSTART: 09/01/2019\nEND: 09/01/2020       NA 2019-01-09
2 customer2              \nS/DATE: 28/08/19\nR/DATE: 27/08/20       NA 2019-08-28
3 customer3                 DOB: 13/01/1980\nStart:04/12/2018       NA 2018-12-04
user_28848590

Another approach is splitting the text and dealing with smaller chunks.

Step by step illustration, with one row of data

# Split the text on newlines, yielding dates with labels
dates$Notes %>% head(1) %>% strsplit("\n")

[[1]]
[1] "DOB: 12/10/62"     "START: 09/01/2019" "END: 09/01/2020"

Drilling down to the next level

# Split each name/value pair on colons
dates$Notes %>% head(1) %>% strsplit("\n") %>% 
    unlist() %>% strsplit(":\\s*")

[[1]]
[1] "DOB"      "12/10/62"

[[2]]
[1] "START"      "09/01/2019"

[[3]]
[1] "END"        "09/01/2020"

Extract the individual values

# extract a vector of name labels
dates$Notes %>% head(1) %>% strsplit("\n") %>% 
    unlist() %>% strsplit(":\\s*") %>%
    sapply(function(x) x[1])

[1] "DOB"   "START" "END" 


# extract a vector of associated values 
dates$Notes %>% head(1) %>% strsplit("\n") %>% 
    unlist() %>% strsplit(":\\s*") %>%
    sapply(function(x) x[2])

[1] "12/10/62"   "09/01/2019" "09/01/2020"

With some clever dplyr usage, you'll get a data frame

dates %>%
    group_by(col1) %>%
    # summarize can collapse many rows into one or expand one into many
    summarize(
        name = Notes %>% strsplit("\n") %>%
            unlist() %>% strsplit(":\\s*") %>% 
            sapply(function(x) x[1]),
        value = Notes %>% strsplit("\n") %>% 
            unlist() %>% strsplit(":\\s*") %>% 
            sapply(function(x) x[2])
    ) %>%
    ungroup()

Result, all of the values separated and ready for further processing

# A tibble: 8 x 3
  col1      name   value     
  <chr>     <chr>  <chr>     
1 customer  DOB    12/10/62  
2 customer  START  09/01/2019
3 customer  END    09/01/2020
4 customer2 NA     NA        
5 customer2 S/DATE 28/08/19  
6 customer2 R/DATE 27/08/20  
7 customer3 DOB    13/01/1980
8 customer3 Start  04/12/2018
user_59712320

I would combine stringr and lubridate:

dates %>% 
  mutate(
    Start_Date = 
      sub("\ns/date:", "\nstart:", tolower(Notes)) %>% 
      str_remove_all("(.*\nstart:)|(\n.*)") %>% 
      trimws() %>% 
      lubridate::dmy()
  )

#        col1                                             Notes End_date Start_Date
# 1  customer DOB: 12/10/62\nSTART: 09/01/2019\nEND: 09/01/2020       NA 2019-01-09
# 2 customer2              \nS/DATE: 28/08/19\nR/DATE: 27/08/20       NA 2019-08-28
# 3 customer3                 DOB: 13/01/1980\nStart:04/12/2018       NA 2018-12-04

The answer is not as concise, but I find it intuitive and easy to follow the steps.

First I substitute one start-pattern with another (sub), where I use tolower to make all lower caps. Then I remove everything before the start date, and everything after the line change str_remove_all. Finally I trim whitespace (trimws) and turn into a date (lubridate::dmy).

user_90074680
Related