Extract value between second and third underscore in R

user_6445985 0
Viewed 6456

I have a data below in the dataframe column-

X_ABC_123_DF</n>
A_NJU_678_PP</n>
J_HH_99_LL</n>
II_00_777_PPP</n>

I want to extract the value between second and third underscore for each row in the dataframe, which i am planning to create a new column and store those values.. I found one way on SO mentioned below, but they haven't mentioned how to write this in R. I am not sure how to write its regex function.

^(?:[^_]+_){2}([^_ ]+)<br>

extract word between 2nd underscore and 3rd underscore or space

r
2 Answers

A few solutions:

df$values = sapply(strsplit(df$V1, "_"), function(x) x[3])

df$values = gsub("(.*_){2}(\\d+)_.+", "\\2", df$V1)

library(dplyr)
library(stringr)
df %>%
  mutate(values = str_extract(V1, "\\d+(?=_[a-zA-Z]+.+$)"))

Result:

                 V1 values
1  X_ABC_123_DF</n>    123
2  A_NJU_678_PP</n>    678
3    J_HH_99_LL</n>     99
4 II_00_777_PPP</n>    777

Data:

df = read.table(text = "X_ABC_123_DF</n>
                A_NJU_678_PP</n>
                J_HH_99_LL</n>
                II_00_777_PPP</n>", stringsAsFactors = FALSE)
user_51506290

1) Assume the input is a data frame df with a single column V1. Read it in using read.table with sep="_" and then pick out the third column. No packages or regular expressions are used. If df$V1 is already character (as opposed to factor) then the as.character could be omitted.

read.table(text = as.character(df$V1), sep = "_")$V3
## [1] 123 678  99 777

2) If the third column is the only one that contains digits (which is the case for the sample data in the question) then it would be sufficient to replace each non-digit with the empty string:

as.numeric(gsub("\\D", "", df$V1))
## [1] 123 678  99 777
user_5165480
Related