Split a string with varying length in a data table

Question

I want to create a column based on part of a string within another column.

The reference column follows the general format of: GB / Ling 31st Dec

I want to extract the word "Ling" in this case and is of varying lengths.

My approach so far has been:

library(data.table)
d1 <- data.table(MENU_HINT = 
                 c("GB / Ling 31st Dec", "GB / Taun 30th Dec", 
                   "GB / Ayr 19th Dec", "GB / Ayr 9th Nov", 
                   "GB / ChelmC 29th Sep"), 
             Track = c("Ling", "Taun", "Ayr", "Ayr", "ChelmC"))

#remove all the spaces
d1[, Track2 := gsub("[[:space:]]", "", MENU_HINT)]

# get the position of the first digit
d1[, x := as.numeric(regexpr("[[:digit:]]", Track2)[[1]])]

# get the position of the '/'
d1[, y := as.numeric(regexpr("/", Track2))[[1]]]

# use above to extract the Track
d1[, Track2 := substr(Track2, y + 1, x - 1)]

Track is what I expect to get and Track2 is what I get from my code above.

This seems long winded and also doesn't seem to work because the x and y values are the same throughout the entire column.

Answer 1

I wouldn't use regex for this- it won't be efficient for a big data set. It seems like the word you looking for is always located after the second space. A very simple and efficient solution could be

d1[, Track2 := tstrsplit(MENU_HINT, " ", fixed = TRUE)[[3]]] 

Benchmark

bigDT <- data.table(MENU_HINT = sample(d1$MENU_HINT, 1e6, replace = TRUE))
microbenchmark::microbenchmark("sub: " = sub("\\S+[[:punct:] ]+(\\S+).*", "\\1", bigDT$MENU_HINT),
                               "gsub: " = gsub("^[^/]+/\\s*|\\s+.*$", "", bigDT$MENU_HINT),
                               "tstrsplit: " = tstrsplit(bigDT$MENU_HINT, " ", fixed = TRUE)[[3]])
# Unit: milliseconds
#        expr       min        lq      mean    median        uq      max neval
#       sub:   982.1185  998.6264 1058.1576 1025.8775 1083.1613 1405.051   100
#      gsub:  1236.9453 1262.6014 1320.4436 1305.6711 1339.2879 1766.027   100
# tstrsplit:   385.4785  452.6476  498.8681  470.8281  537.5499 1044.691   100