Creating new row for the item in a data frame if seperated by special character such as "+" sign in R

Question

I have a data in text file which contain several column, I would like to process data in such a way that I should not loose any information, some coulmn include two or more information seperated with special character such as "+" plus sign, I would like to put this combined information in differnt row within same column, for example I pasted data below here
My dataframe look like following

df <- data.frame(G1=c("GH13_22+CBM4",  "GH109+PL7+GH9","GT57", "AA3","",""),
                 G2=c("GH13_22","","GT57+GH15","AA3", "GT41","PL+PL2"),
                 G3=c("GH13", "GH1O9","", "CBM34+GH13+CBM48", "GT41","GH16+CBM4+CBM54+CBM32"))

             G1        G2                    G3
1  GH13_22+CBM4   GH13_22                  GH13
2 GH109+PL7+GH9                           GH1O9
3          GT57 GT57+GH15
4           AA3       AA3      CBM34+GH13+CBM48
5                    GT41                  GT41
6                  PL+PL2 GH16+CBM4+CBM54+CBM32

Expected Results should look like

df2 <- data.frame(G1=c("GH13_22","CBM4",  "GH109","PL7","GH9","GT57", "AA3","","","",""),
                  G2=c("GH13_22","","GT57","GH15","AA3", "GT41","PL","PL2","","",""),
                  G3=c("GH13", "GH1O9","", "CBM34","GH13","CBM48", "GT41","GH16","CBM4","CBM54","CBM32"))

        G1      G2    G3
1  GH13_22 GH13_22  GH13
2     CBM4         GH1O9
3    GH109    GT57
4      PL7    GH15 CBM34
5      GH9     AA3  GH13
6     GT57    GT41 CBM48
7      AA3      PL  GT41
8              PL2  GH16
9                   CBM4
10                 CBM54
11                 CBM32

Appreciation for any help Thanks

`tidyr::separate_rows()` should help here. Where do the new Gene names come from? — Jon Spring, Mar 08 '23 at 07:41
@JonSpring, Thank you for the reply, Now I have removed gene list, now could you provide me the code for tidy, Also If I want to do with for loop how can I do it, thanks and regards — Umar, Mar 08 '23 at 07:51
if you have significant changes to your question after it recieved an accepted answer, it is usually considered best practice to write a new question — D.J, Mar 08 '23 at 09:12

score 2 · Accepted Answer · answered Mar 08 '23 at 09:04

2

A base solution:

split <- lapply(df, \(x) unlist(strsplit(replace(x, x == '', NA_character_), '\\+')))
as.data.frame(lapply(split, `[`, 1:max(lengths(split))))

        G1      G2    G3
1  GH13_22 GH13_22  GH13
2     CBM4    <NA> GH1O9
3    GH109    GT57  <NA>
4      PL7    GH15 CBM34
5      GH9     AA3  GH13
6     GT57    GT41 CBM48
7      AA3      PL  GT41
8     <NA>     PL2  GH16
9     <NA>    <NA>  CBM4
10    <NA>    <NA> CBM54
11    <NA>    <NA> CBM32

answered Mar 08 '23 at 09:04

Darren Tsai

32,117
5
21
51

@Umar the two scenarios have different spliting logic. You cannot find a solution which works for both. Please post another question. – Darren Tsai Mar 08 '23 at 09:22
Sur, Thank you for figuring it out, greets – Umar Mar 08 '23 at 09:23

TarJae · Answer 2 · 2023-03-08T08:15:00.487

separate_rows() has been superseded in favour of separate_longer_delim() because it has a more consistent API with other separate functions. Superseded functions will not go away, but will only receive critical bug fixes. https://tidyr.tidyverse.org/reference/separate_rows.html

We bring data in long format
replace blank with NA using na_if from dplyr
With this line of code summarise(cur_data()[seq(max(id)), ]) we expandd each group to the max of id.
Finally we pivot back the prepared data frame:

library(dplyr)
library(tidyr)

df %>% 
  pivot_longer(everything()) %>% 
  separate_longer_delim(value, "+") %>% 
  mutate(value = na_if(value, "")) %>% 
  group_by(name) %>% 
  mutate(id = row_number()) %>% 
  summarise(cur_data()[seq(max(id)), ]) %>% 
  pivot_wider(names_from = name, values_from = value) 

      id G1      G2      G3   
   <int> <chr>   <chr>   <chr>
 1     1 GH13_22 GH13_22 GH13 
 2     2 CBM4    NA      GH1O9
 3     3 GH109   GT57    NA   
 4     4 PL7     GH15    CBM34
 5     5 GH9     AA3     GH13 
 6     6 GT57    GT41    CBM48
 7     7 AA3     PL      GT41 
 8     8 NA      PL2     GH16 
 9     9 NA      NA      CBM4 
10    10 NA      NA      CBM54
11    11 NA      NA      CBM32

D.J · Answer 3 · 2023-03-08T09:07:18.630

Another option, inspired by @Peter M in this post

library(tidyverse)
library(stringr)

# finds which vector is the longest and pads the other vectors accordingly
makePaddedDataFrame <- function(l){
  maxlen <- max(sapply(l,length))
  data.frame(lapply(l,\(x) x[1:maxlen])) # pads vectors with na
}

df %>% 
  mutate(across(.fns = function(x) str_split(x, pattern="\\+"))) %>% 
  lapply(function(x) do.call(c, x)) %>% 
  makePaddedDataFrame %>% 
  replace(is.na(.), " ") # if you want empty strings instead of na

        G1      G2    G3
1  GH13_22 GH13_22  GH13
2     CBM4         GH1O9
3    GH109    GT57      
4      PL7    GH15 CBM34
5      GH9     AA3  GH13
6     GT57    GT41 CBM48
7      AA3      PL  GT41
8              PL2  GH16
9                   CBM4
10                 CBM54
11                 CBM32

Creating new row for the item in a data frame if seperated by special character such as "+" sign in R

3 Answers3

Linked