Extract a pattern from column and make a new one in R data frame

user_8791858 0
Viewed 142

I have the following R DataFrame

  x                                                                                          y
1 1               "gene_biotype "lincRNA"; gene_id "ENSG00000224818"; gene_name "RP11-134G8.8"
2 2 "gene_biotype "processed_transcript"; gene_id "ENSG00000261067"; gene_name "RP11-264B17.3"
3 3 "gene_biotype "processed_transcript"; gene_id "ENSG00000261067"; gene_name "RP11-264B17.3"

I want to extract the gene_id from every row and make new column like below,

  x               y
1 1 ENSG00000224818
2 2 ENSG00000261067
3 3 ENSG00000261067

I have tried sapply(strsplit(dat$y, 'gene_id \"'), "[", 2), but this only removed the preceding pattern. How should I remove the succeeding pattern and extract the gene_id

The dataframe can be reproduced using the following

x <- c(1:3)
y <- c('"gene_biotype \"lincRNA\"; gene_id \"ENSG00000224818\"; gene_name \"RP11-134G8.8\"',
       '"gene_biotype \"processed_transcript\"; gene_id \"ENSG00000261067\"; gene_name \"RP11-264B17.3\"',
       '"gene_biotype \"processed_transcript\"; gene_id \"ENSG00000261067\"; gene_name \"RP11-264B17.3\"')
dat <- data.frame(x,y)
dat
r regex
3 Answers

You can extract the word after "gene_id" :

dat$y <- sub('.*gene_id\\s"(\\w+)";.*', '\\1', dat$y)
dat

#  x               y
#1 1 ENSG00000224818
#2 2 ENSG00000261067
#3 3 ENSG00000261067
user_39629140

You can use

library(stringr)
dat$x <- str_extract(dat$y, '(?<=gene_id ")[^"]+')

The regex pattern (see its online demo) matches

  • (?<=gene_id ") - a location immediately preceded with gene_id " substring
  • [^"]+ - one or more chars other than ".

See an R demo online:

x <- c(1:3)
y <- c('"gene_biotype \"lincRNA\"; gene_id \"ENSG00000224818\"; gene_name \"RP11-134G8.8\"',
       '"gene_biotype \"processed_transcript\"; gene_id \"ENSG00000261067\"; gene_name \"RP11-264B17.3\"',
       '"gene_biotype \"processed_transcript\"; gene_id \"ENSG00000261067\"; gene_name \"RP11-264B17.3\"')
dat <- data.frame(x,y)
library(stringr)
dat$x <- str_extract(dat$y, '(?<=gene_id ")[^"]+')
dat

Output:

                x
1 ENSG00000224818
2 ENSG00000261067
3 ENSG00000261067
                                                                                           y
1               "gene_biotype "lincRNA"; gene_id "ENSG00000224818"; gene_name "RP11-134G8.8"
2 "gene_biotype "processed_transcript"; gene_id "ENSG00000261067"; gene_name "RP11-264B17.3"
3 "gene_biotype "processed_transcript"; gene_id "ENSG00000261067"; gene_name "RP11-264B17.3"
user_38329700

Another way you can try

library(dplyr)
library(tidyr)
dat %>% 
  extract(y, c("y"), "(ENSG\\d+)")
# x               y
# 1 1 ENSG00000224818
# 2 2 ENSG00000261067
# 3 3 ENSG00000261067
user_123363050
Related