CODEWITHSUNDEEP
rstringuniquesubstr
Leo
Leo

Reputation: 136

extract only letters after _ with numbers inbetween

So I have two columns that looks like this:

    V1                                V2
    ENSP00000222573_N559D             ENSG00000105855
    ENSP00000222573_N559D             ENSG00000105855
    ENSP00000267853_E337*             ENSG00000108239
    ENSP00000299441_R1672P,R1672G     ENSG00000127415
    ENSP00000334642_K277N.            ENSG00000134324
    ENSP00000342952_N585R             ENSG00000134324

First, I need the first column to extract all letters/signs after _ So the results would need to look like this:

V1      V2
ND      ENSG00000105855
ND      ENSG00000105855
E*      ENSG00000108239
RP,RG   ENSG00000127415
KN      ENSG00000134324
NR      ENSG00000134324

Then I would like to filter such that only when both V1 & V2 together are double, they are filtered out. So the final result would be:

  V1      V2
    ND      ENSG00000105855
    E*      ENSG00000108239
    RP,RG   ENSG00000127415
    KN      ENSG00000134324
    NR      ENSG00000134324

Upvotes: 1

Views: 36

Answers (2)

MKR
MKR

Reputation: 20095

An option could be using sapply and strsplit as:

sapply(df, function(x){
 sapply(strsplit(x, split = "_"), function(y){
   if(length(y)<2){
     y
   } else {
     gsub("[0-9]+","",y[2])
   }
 })
  }) %>% as.data.frame() %>% distinct()


#      V1              V2
# 1    ND ENSG00000105855
# 2    E* ENSG00000108239
# 3 RP,RG ENSG00000127415
# 4   KN. ENSG00000134324
# 5    NR ENSG00000134324

Data:

df <- read.table(text = 
 "V1                                V2
ENSP00000222573_N559D             ENSG00000105855
ENSP00000222573_N559D             ENSG00000105855
ENSP00000267853_E337*             ENSG00000108239
ENSP00000299441_R1672P,R1672G     ENSG00000127415
ENSP00000334642_K277N.            ENSG00000134324
ENSP00000342952_N585R             ENSG00000134324",
stringsAsFactors = FALSE, header = TRUE)

Upvotes: 2

www
www

Reputation: 39154

A solution using . dat2 is the final output.

library(tidyverse)

dat2 <- dat %>%
  separate(V1, into = c("Re", "V1"), sep = "_") %>%
  select(-Re) %>%
  mutate(V1 = str_replace_all(V1, "[0-9]*", "")) %>%
  distinct(V1, V2, .keep_all = TRUE)
dat2
#      V1              V2
# 1    ND ENSG00000105855
# 2    E* ENSG00000108239
# 3 RP,RG ENSG00000127415
# 4   KN. ENSG00000134324
# 5    NR ENSG00000134324

DATA

dat <- read.table(text = "V1                                V2
    ENSP00000222573_N559D             ENSG00000105855
                  ENSP00000222573_N559D             ENSG00000105855
                  'ENSP00000267853_E337*'             ENSG00000108239
                  'ENSP00000299441_R1672P,R1672G'     ENSG00000127415
                  'ENSP00000334642_K277N.'            ENSG00000134324
                  ENSP00000342952_N585R             ENSG00000134324",
                  header = TRUE, stringsAsFactors = FALSE)

Upvotes: 2

Related Questions