splitting filename text by underscores using R

In R I'd like to take a collection of file names in the format below and return the number to the right of the second underscore (this will always be a number) and the text string to the right of the third underscore (this will be combinations of letters and numbers).

I have file names in this format:

HELP_PLEASE_4_ME

I want to extract the number 4 and the text ME

I'd then like to create a new field within my data frame where these two types of data can be stored. Any suggestions?

Solution

Here is an option using regexec and regmatches to pull out the patterns:

matches <- regmatches(df$a, regexec("^.*?_.*?_([0-9]+)_([[:alnum:]]+)$", df$a))
df[c("match.1", "match.2")] <- t(sapply(matches, `[`, -1)) # first result for each match is full regular expression so need to drop that.

Produces:

                 a match.1 match.2
1 HELP_PLEASE_4_ME       4      ME
2  SOS_WOW_3_Y34OU       3   Y34OU

This will break if any rows don't have the expected structure, but I think that is what you want to happen (i.e. be alerted that your data is not what you think it is). strsplit based approaches will require additional checking to ensure that your data is what you think it is.

And the data:

df <- data.frame(a=c("HELP_PLEASE_4_ME", "SOS_WOW_3_Y34OU"), stringsAsFactors=F)