How to insert a part of a value of a column into a new column

user_18098777 0
Viewed 80

I have not been programming for that long and have now encountered a problem to which I have not yet been able to find a solution.

In my dataframe there is a column that contains several pieces of information. For example, one row looks like this:

sp|O94910|AGRL1_HUMAN

or like this

sp|Q13554|KCC2B_HUMAN;sp|Q13555|KCC2G_HUMAN

Now I want to create a new column with the combination of digits between the two vertical bars. For the upper example it would be O94910, for the lower Q13554; Q13555

I have already tried functions like str_extract_all, str_match or gsub. But nothing worked.

The "id" is the column I look at. It includes different combinations of digits. I need the one between the two |

> dput(head(anaDiff_PD_vs_CTRL$id, 10))
c("sp|O94910|AGRL1_HUMAN", "sp|P02763|A1AG1_HUMAN", "sp|P19652|A1AG2_HUMAN", 
"sp|P25311|ZA2G_HUMAN", "sp|Q8NFZ8|CADM4_HUMAN", "sp|P08174|DAF_HUMAN", 
"sp|Q15262|PTPRK_HUMAN", "sp|P78324|SHPS1_HUMAN;sp|Q5TFQ8|SIRBL_HUMAN;sp|Q9P1W8|SIRPG_HUMAN", 
"sp|Q8N3J6|CADM2_HUMAN", "sp|P19021|AMD_HUMAN")>
extract r
3 Answers

With dplyr and stringr you can try...


library(dplyr)
library(stringr)

dat %>% 
  rowwise() %>%
  mutate(dig = str_extract_all(col, "(?<=sp\\|)[A-Z0-9]+(?=\\|)"),
         dig = paste0(dig, collapse = "; "))
#> # A tibble: 4 x 2
#> # Rowwise: 
#>   col                                         dig           
#>   <chr>                                       <chr>         
#> 1 sp|Q8NFZ8|CADM4_HUMAN                       Q8NFZ8        
#> 2 sp|94910|AGRL1_HUMAN                        94910         
#> 3 sp|O94910|AGRL1_HUMAN                       O94910        
#> 4 sp|Q13554|KCC2B_HUMAN;sp|Q13555|KCC2G_HUMAN Q13554; Q13555

data

dat <- data.frame(col = c("sp|Q8NFZ8|CADM4_HUMAN", "sp|94910|AGRL1_HUMAN", "sp|O94910|AGRL1_HUMAN", "sp|Q13554|KCC2B_HUMAN;sp|Q13555|KCC2G_HUMAN"))

Created on 2022-02-02 by the reprex package (v2.0.1)

user_69365450

Here is a solution without tidyverse:

dat <- read.table(text = "
sp|Q8NFZ8|CADM4_HUMAN
sp|94910|AGRL1_HUMAN
sp|O94910|AGRL1_HUMAN
sp|Q13554|KCC2B_HUMAN;sp|Q13555|KCC2G_HUMAN")


ids <- strsplit(dat$V1, ";")
ids <- lapply(ids, function(x) gsub("sp\\|([[:alnum:]]*)\\|.*", "\\1", x))
ids <- lapply(ids, function(x) paste(x, collapse="; "))
dat$newcol <- unlist(ids)

Even with tidyverse, I would define a helper function for more clarity:

extract_ids <- function(x) {
    ids <- strsplit(x, ";")
    ids <- map(ids, ~ gsub("sp\\|([[:alnum:]]*)\\|.*", "\\1", .))
    ids <- map(ids, ~ paste(., collapse="; "))
    unlist(ids)
}

dat <- dat %>% mutate(ids = extract_ids(V1))
user_16868140

This solution should help if you want to change your column names in a similar fashion:

library(tidyverse)

# create test data frame with column names "sp|O94910|AGRL1_HUMAN" and "sp|Q13554|KCC2B_HUMAN;sp|Q13555|KCC2G_HUMAN"

    col1 <- c(1,2,3,4,5)
    col2 <- c(6,7,8,9,10)
    df <- data.frame(col1, col2)
    names(df)[1] <- "sp|O94910|AGRL1_HUMAN"
    names(df)[2] <- "sp|Q13554|KCC2B_HUMAN;sp|Q13555|KCC2G_HUMAN"


    names <- as.data.frame((str_split(colnames(df), "\\|", simplify = TRUE)))  # split the strings representing the column names seperated by "|" into a list

# remove all strings that contain less digits than letters or special characters
for(i in 1:nrow(names)) {       
  for(j in 1:ncol(names)){
    if ( (str_count(as.vector(str_split(names[i,j], "\\|", simplify = TRUE)), "[0-9]") > 
          str_count(as.vector(str_split(names[i,j], "\\|", simplify = TRUE)), "[:alpha:]|[:punct:]") )){
      names[i,j] <- names[i,j]
    } else {
      names[i,j] <- ""
    }
  }
}

# combine the list columns into a single column calles "colnames"
names <- names %>% unite("colnames", 1:5, na.rm = TRUE, remove = TRUE, sep = ";")

# remove all ";" separators at the start of the strings, the end of the strings, and series of ";" into a single ";"
for (i in 1:nrow(names)){
  names[i,] <- str_replace(names[i,],"\\;+$", "") %>% 
    str_replace("^\\;+", "") %>%
    str_replace("\\;{2}", ";")
}
# convert column with new names into a vector
new_names <- as.vector(names$colnames)

# replace old names with new names
names(df) <- new_names
user_180989650
Related