I need help figuring out how to split strings in a column of a data frame based on the last delimiter when I have varying numbers of the same delimiter in R. For example,
col1 <- c('a', 'b', 'c')
col2 <- c('a_b', 'a_b_c', 'a_b_c_d')
df <- data.frame(cbind(col1, col2))
And I would like to split df$col2 to have a data frame that looks like:
col1 <- c('a', 'b', 'c')
col2 <- c('a', 'a_b', 'a_b_c')
col3 <- c('b', 'c', 'd')
These use no packages. They assume that each element of col2 has at least one underscore. (See note if lifting this restriction is needed.)
1) The first regular expression (.*)_ matches everything up to the last underscore followed by everything remaining .* and the first sub replaces the entire match with the matched part within parens. This works because such matches are greedy so the first .* will take everything it can leaving the rest for the second .* . The second regular expression matches everything up to the last underscore and the second sub replaces that with the empty string.
transform(df, col2 = sub("(.*)_.*", "\\1", col2), col3 = sub(".*_", "", col2))
2) Here is a variation that is a bit more symmetric. It uses the same regular expression for both sub calls.
pat <- "(.*)_(.*)"
transform(df, col2 = sub(pat, "\\1", col2), col3 = sub(pat, "\\2", col2))
Note: If we did want to handle strings with no underscore at all such that "xyz" is split into "xyz" and "" then use this for the second sub. It tries to match the left hand side of the | first and if that fails (which will occur if there are no underscores) then the entire string will match the right hand side and sub will replace that with the empty string.
sub(".*_|^[^_]*$", "", col2)