Create new column with dplyr mutate and substring of existing column

r dplyr strsplit
PM. picture PM. · Feb 1, 2017 · Viewed 37.8k times · Source

I have a dataframe with a column of strings and want to extract substrings of those into a new column.

Here is some sample code and data showing I want to take the string after the final underscore character in the id column in order to create a new_id column. The id column entry always has 2 underscore characters and it's always the final substring I would like.

df = data.frame( id = I(c("abcd_123_ABC","abc_5234_NHYK")), x = c(1.0,2.0) )

require(dplyr)

df = df %>% dplyr::mutate(new_id = strsplit(id, split="_")[[1]][3])

I was expecting strsplit to act on each row in turn.

However, the new_id column only contains ABC in each row, whereas I would like ABC in row 1 and NHYK in row 2. Do you know why this fails and how to achieve what I want?

Answer

Sam Firke picture Sam Firke · Feb 1, 2017

You could use stringr::str_extract:

library(stringr)

 df %>%
   dplyr::mutate(new_id = str_extract(id, "[^_]+$"))


#>              id x new_id
#> 1  abcd_123_ABC 1    ABC
#> 2 abc_5234_NHYK 2   NHYK

The regex says, match one or more (+) of the characters that aren't _ (the negating [^ ]), followed by end of string ($).

Related questions

using strsplit and subset in dplyr and mutate

I have a data table with one string column. I'd like to create another column that is a subset of this column using strsplit. dat <- data.table(labels=c('a_1','b_2','c_3','d_4')) The …

Read More
Filter rows which contain a certain string

I have to filter a data frame using as criterion those row in which is contained the string RTB. I'm using dplyr. d.del <- df %>% group_by(TrackingPixel) %>% summarise(MonthDelivery = as.integer(sum(Revenue))) %>% arrange(…

Read More
What does %>% function mean in R?

I have seen the use of %>% (percent greater than percent) function in some packages like dplyr and rvest. What does it mean? Is it a way to write closure blocks in R?

Read More