string split values in two columns, and then concatenate them into a new column

user_12388621 0
Viewed 309

I am trying to call the str_split function for both columns (Proteins and Positions.within.proteins), and then concatenate the corresponding values in a new column called ID.

df <- data.frame(Proteins = c("Q99755;A2A3N6", "O00329", "O00444", 
"O14965", "O14976", "Q6A1A2;O15530", "O43318", "O43526", "O43930;P51817", 
"O60331"), Positions.within.proteins = c("276;223", "708", "41", 
"162", "175", "84;111", "63", "628", "78;78", "270"))

Here are my codes.

my.function <- function(x, y){
  protein.names <- str_split(x, ";")[[1]]
  position.names <- str_split(y, ";")[[1]]
  ID <- list()
    for (i in 1:length(protein.names)){
      ID[i] <- paste(protein.names[i], position.names[i], sep ="_")
    }
  ID.2 <- unlist(ID)
  return(ID.2)
}

It works to some extent when I call the function on a single row.

row1 <- my.function(df$Proteins[1], df$Positions.within.proteins[1])

"Q99755_276" "A2A3N6_223"

But my questions are:

  1. How to apply this function to the whole data frame?
  2. How to convert "Q99755_276" "A2A3N6_223" to what I want "Q99755_276;A2A3N6_223"

I'd like to use the apply function, but am not sure if the apply function can take two arguments.

Here shows what it should look like.

df.final <- data.frame(Proteins = c("Q99755;A2A3N6", "O00329", "O00444", 
"O14965", "O14976", "Q6A1A2;O15530", "O43318", "O43526", "O43930;P51817", 
"O60331"), Positions.within.proteins = c("276;223", "708", "41", 
"162", "175", "84;111", "63", "628", "78;78", "270"), ID = c("Q99755_276;A2A3N6_223", 
"O00329_708", "O00444_41", "O14965_162", "O14976_175", "Q6A1A2_84;O15530_111", 
"O43318_63", "O43526_628", "O43930_78;P51817_78", "O60331_270"
))

Does anyone know how to achieve these? Thanks so much for any help!

strsplit apply dataframe r string
4 Answers

What you are looking for is tidyr::unite():

tidyr::unite(data = iris, col = "new_column", Species, Sepal.Length, sep = ";")

Try that out. It takes a data frame (iris in this case), the name of your new column (new_column), the columns you would like to concatenate together (Species and Sepal.Length), and a value you would like to separate them by (a semicolon). tidyr::separate() is the opposite of unite()--it makes two new columns based on a delimiter found in the original.

Edit

Alright, you need to get a bit more creative...Try breaking each protein into its own column with tidyr::separate(), do the same for the protein position, then unite each protein with its position. Then, unite both proteins together with a semicolon as the delimiter. Finally, remove missing values for the cases where only one protein was used (which will always have the same form, at the end in ;NA_NA format). Viola:

library(tidyr)
library(dplyr)
library(stringr)

df %>% 
  separate(col = Proteins, c("protein1", "protein2"), ";", remove = FALSE) %>% 
  separate(col = Positions.within.proteins, into = c("position_p1", "position_p2"), ";", remove = FALSE) %>% 
  unite(col = "id_part1", sep = "_", protein1, position_p1) %>% 
  unite(col = "id_part2", sep = "_", protein2, position_p2) %>% 
  unite(col = "id", sep = ";", id_part1, id_part2) %>% 
  mutate(id = str_remove_all(id, ";NA_NA"))

Another Edit

I did some benchmarking, and my implementation is a bit faster, too:

rbenchmark::benchmark(
  mine = df %>% 
    separate(col = Proteins, c("protein1", "protein2"), ";", remove = FALSE) %>% 
    separate(col = Positions.within.proteins, into = c("position_p1", "position_p2"), ";", remove = FALSE) %>% 
    unite(col = "id_part1", sep = "_", protein1, position_p1) %>% 
    unite(col = "id_part2", sep = "_", protein2, position_p2) %>% 
    unite(col = "id", sep = ";", id_part1, id_part2) %>% 
    mutate(id = str_remove_all(id, ";NA_NA")),
  
alt_implementation = df %>% 
    rowwise() %>%
    mutate(ID = map2(Proteins, Positions.within.proteins, my.function)) %>%
    unnest_wider(ID, names_sep = '.') %>%
    unite(contains('ID'), col = 'ID', remove = TRUE, sep = ";", na.rm = TRUE),

replications = 1000
)
#                  test replications elapsed relative user.self sys.self user.child sys.child
# 1                mine         1000    9.06    1.000      8.97     0.05         NA        NA
# 2  alt_implementation         1000   11.77    1.299     11.73     0.00         NA        NA
user_164840470

You can use your function and the tidyverse

Use mutate() with map2(.f = my.function) to create a nested ID column containing a list column with all IDs per row(some have 1 ID, some have two in the example data). Then you can unnest_wider() to create several different ID columns, which you can latter collapse using tidyr::unite()

library(tidyr)
library(dplyr)
library(stringr)
library(purrr)

df %>% mutate(ID=map2(Proteins, Positions.within.proteins, my.function))%>%
        unnest_wider(ID, names_sep = '.')%>%
        unite(contains('ID'), col='ID', remove = TRUE, sep=";", na.rm=TRUE)

# A tibble: 10 x 3
   Proteins      Positions.within.proteins ID                   
   <chr>         <chr>                     <chr>                
 1 Q99755;A2A3N6 276;223                   Q99755_276;A2A3N6_223
 2 O00329        708                       O00329_708           
 3 O00444        41                        O00444_41            
 4 O14965        162                       O14965_162           
 5 O14976        175                       O14976_175           
 6 Q6A1A2;O15530 84;111                    Q6A1A2_84;O15530_111 
 7 O43318        63                        O43318_63            
 8 O43526        628                       O43526_628           
 9 O43930;P51817 78;78                     O43930_78;P51817_78  
10 O60331        270                       O60331_270
user_139723330

A brief base R solution.

df$ID <- apply(df, 1, \(x) paste(do.call(\(y, z) paste0(y, "_", z), 
                                         unname(strsplit(x, ';'))), collapse=';'))
df
#         Proteins Positions.within.proteins                    ID
# 1  Q99755;A2A3N6                   276;223 Q99755_276;A2A3N6_223
# 2         O00329                       708            O00329_708
# 3         O00444                        41             O00444_41
# 4         O14965                       162            O14965_162
# 5         O14976                       175            O14976_175
# 6  Q6A1A2;O15530                    84;111  Q6A1A2_84;O15530_111
# 7         O43318                        63             O43318_63
# 8         O43526                       628            O43526_628
# 9  O43930;P51817                     78;78   O43930_78;P51817_78
# 10        O60331                       270            O60331_270
user_65740380

Here is base R way using strsplit and mapply -

df$ID <- mapply(function(x, y) paste(x, y, collapse = ';', sep = '_'), 
        strsplit(df$Proteins, ';'), strsplit(df$Positions.within.proteins, ';'))
df

#        Proteins Positions.within.proteins                    ID
#1  Q99755;A2A3N6                   276;223 Q99755_276;A2A3N6_223
#2         O00329                       708            O00329_708
#3         O00444                        41             O00444_41
#4         O14965                       162            O14965_162
#5         O14976                       175            O14976_175
#6  Q6A1A2;O15530                    84;111  Q6A1A2_84;O15530_111
#7         O43318                        63             O43318_63
#8         O43526                       628            O43526_628
#9  O43930;P51817                     78;78   O43930_78;P51817_78
#10        O60331                       270            O60331_270
user_39629140
Related