Split space- and equal-separated string into different columns

Question

I have a dataset.

test
                                                                                                                                              Intervar
1 Uncertain significance PVS1=0 PS=[0, 1, 0, 0, 0] PM=[0, 1, 0, 0, 0, 0, 0] PP=[0, 0, 0, 0, 0, 0] BA1=0 BS=[0, 0, 0, 0, 0] BP=[0, 0, 0, 0, 0, 0, 0, 0]
2             Pathogenic PVS1=1 PS=[0, 0, 0, 0, 0] PM=[0, 1, 0, 0, 0, 0, 0] PP=[0, 0, 1, 0, 0, 0] BA1=0 BS=[0, 0, 0, 0, 0] BP=[0, 0, 0, 0, 0, 0, 0, 0]
3             Pathogenic PVS1=1 PS=[0, 1, 0, 0, 0] PM=[0, 1, 0, 0, 0, 0, 0] PP=[0, 0, 1, 0, 0, 0] BA1=0 BS=[0, 0, 0, 0, 0] BP=[0, 0, 0, 0, 0, 0, 0, 0]
4             Pathogenic PVS1=1 PS=[0, 0, 0, 0, 0] PM=[0, 1, 0, 0, 0, 0, 0] PP=[0, 0, 1, 0, 0, 0] BA1=0 BS=[0, 0, 0, 0, 0] BP=[0, 0, 0, 0, 0, 0, 0, 0]
5             Pathogenic PVS1=1 PS=[0, 0, 0, 0, 0] PM=[0, 1, 0, 0, 0, 0, 0] PP=[0, 0, 1, 0, 0, 0] BA1=0 BS=[0, 0, 0, 0, 0] BP=[0, 0, 0, 0, 0, 0, 0, 0]
6          Likely benign PVS1=0 PS=[0, 0, 0, 0, 0] PM=[0, 1, 0, 0, 0, 0, 0] PP=[0, 0, 0, 0, 0, 0] BA1=0 BS=[0, 0, 0, 0, 0] BP=[0, 0, 0, 1, 0, 0, 1, 0]
7             Pathogenic PVS1=1 PS=[0, 0, 0, 0, 0] PM=[0, 1, 0, 0, 0, 0, 0] PP=[0, 0, 1, 0, 0, 0] BA1=0 BS=[0, 0, 0, 0, 0] BP=[0, 0, 0, 0, 0, 0, 0, 0]
8             Pathogenic PVS1=1 PS=[0, 0, 0, 0, 0] PM=[0, 1, 0, 0, 0, 0, 0] PP=[0, 0, 1, 0, 0, 0] BA1=0 BS=[0, 0, 0, 0, 0] BP=[0, 0, 0, 0, 0, 0, 0, 0]

Each row follows the same pattern:

Pathogenic/Likely pathogenic/Uncertain significance/Likely benign/Benign PVS1=0 PS=[0,0,0,0,0] PM=[0,0,0,0,0,0,0] PP=[0,0,0,0,0,0] BA1=0 BS=[0,0,0,0,0] BP=[0,0,0,0,0,0,0,0]

I want to split it into multiple columns and name the column based on the character before the equal sign.

  PVS1 PS1 PS2 PS3 PS4 PS5 PM1 PM2 PM3 PM4 PM5 PM6 PM7 PP1 PP2 PP3 PP4 PP5 PP6 ... BP1 BP2 BP3 BP4 BP5 BP6 BP7 BP8
1    0   0   1   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   0 ...   0   0   0   0   0   0   0   0
2    1   0   0   0   0   0   0   1   0   0   0   0   0   0   0   1   0   0   0 ...   0   0   0   0   0   0   0   0
3    1   0   1   0   0   0   0   1   0   0   0   0   0   0   0   1   0   0   0 ...   0   0   0   0   0   0   0   0
4    1   0   0   0   0   0   0   1   0   0   0   0   0   0   0   1   0   0   0 ...   0   0   0   0   0   0   0   0
5    1   0   0   0   0   0   0   1   0   0   0   0   0   0   0   1   0   0   0 ...   0   0   0   0   0   0   0   0
6    0   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   0 ...   0   0   0   1   0   0   1   0
7    1   0   0   0   0   0   0   1   0   0   0   0   0   0   0   1   0   0   0 ...   0   0   0   0   0   0   0   0
8    1   0   0   0   0   0   0   1   0   0   0   0   0   0   0   1   0   0   0 ...   0   0   0   0   0   0   0   0

data

structure(list(Intervar = c("Uncertain significance PVS1=0 PS=[0, 1, 0, 0, 0] PM=[0, 1, 0, 0, 0, 0, 0] PP=[0, 0, 0, 0, 0, 0] BA1=0 BS=[0, 0, 0, 0, 0] BP=[0, 0, 0, 0, 0, 0, 0, 0]", 
"Pathogenic PVS1=1 PS=[0, 0, 0, 0, 0] PM=[0, 1, 0, 0, 0, 0, 0] PP=[0, 0, 1, 0, 0, 0] BA1=0 BS=[0, 0, 0, 0, 0] BP=[0, 0, 0, 0, 0, 0, 0, 0]", 
"Pathogenic PVS1=1 PS=[0, 1, 0, 0, 0] PM=[0, 1, 0, 0, 0, 0, 0] PP=[0, 0, 1, 0, 0, 0] BA1=0 BS=[0, 0, 0, 0, 0] BP=[0, 0, 0, 0, 0, 0, 0, 0]", 
"Pathogenic PVS1=1 PS=[0, 0, 0, 0, 0] PM=[0, 1, 0, 0, 0, 0, 0] PP=[0, 0, 1, 0, 0, 0] BA1=0 BS=[0, 0, 0, 0, 0] BP=[0, 0, 0, 0, 0, 0, 0, 0]", 
"Pathogenic PVS1=1 PS=[0, 0, 0, 0, 0] PM=[0, 1, 0, 0, 0, 0, 0] PP=[0, 0, 1, 0, 0, 0] BA1=0 BS=[0, 0, 0, 0, 0] BP=[0, 0, 0, 0, 0, 0, 0, 0]", 
"Likely benign PVS1=0 PS=[0, 0, 0, 0, 0] PM=[0, 1, 0, 0, 0, 0, 0] PP=[0, 0, 0, 0, 0, 0] BA1=0 BS=[0, 0, 0, 0, 0] BP=[0, 0, 0, 1, 0, 0, 1, 0]", 
"Pathogenic PVS1=1 PS=[0, 0, 0, 0, 0] PM=[0, 1, 0, 0, 0, 0, 0] PP=[0, 0, 1, 0, 0, 0] BA1=0 BS=[0, 0, 0, 0, 0] BP=[0, 0, 0, 0, 0, 0, 0, 0]", 
"Pathogenic PVS1=1 PS=[0, 0, 0, 0, 0] PM=[0, 1, 0, 0, 0, 0, 0] PP=[0, 0, 1, 0, 0, 0] BA1=0 BS=[0, 0, 0, 0, 0] BP=[0, 0, 0, 0, 0, 0, 0, 0]"
)), class = "data.frame", row.names = c(NA, -8L))

I've tried the following code to separate it and sum the number in the same bracket as a previous answer of another question suggested.

test$Intervar %>%
  gsub(" (\w+=)", "\n\1", .) %>%
  paste0("\nDescription=", .) %>%
  gsubfn("$$(.*?)$$", ~ sum(scan(text = txt, sep = ",", quiet = TRUE)), .) %>%
  gsub("=", ": ", .) %>%
  textConnection %>%
  read.dcf %>%
  as.data.frame %>%
  type.convert

The output was like

             Description PVS1 PS PM PP BA1 BS BP
1 Uncertain significance    0  1  1  0   0  0  0
2             Pathogenic    1  0  1  1   0  0  0
3             Pathogenic    1  1  1  1   0  0  0
4             Pathogenic    1  0  1  1   0  0  0
5             Pathogenic    1  0  1  1   0  0  0
6          Likely benign    0  0  1  0   0  0  2
7             Pathogenic    1  0  1  1   0  0  0
8             Pathogenic    1  0  1  1   0  0  0

But this time, I want all numbers in the sampe bracket being separated.

user_516548 · Answer

Extract the digits that begin at a word boundary converting them to numeric giving the matrix m.

The remainder of the code computes the column names.

First define a regular expression that matches a word followed by an equal sign capturing the word. Extract those into vars. That is vars equals c("PVS1", "PS", "PM", "PP", "BA1", "BS", "BP").
Then split the first row by the same regular expression. That produces a list of length 1 containing a character vector so extract the character vector using [[1]] and remove the first element [-1] which we don't need. Now count the number of comma separated fields left. That will give a numeric vector, lens, with the lengths of each of the 0/1 vectors. lens is c(1, 5, 7, 6, 1, 5, 8) .
Using vars and lens we can compute the column names.

The result is the numeric matrix m. (Optionally use data.frame(m) if you prefer a data frame although given that it is entirely numeric a matrix is actually more appropriate.)

library(gsubfn)

m <- do.call("rbind", strapply(dat[, 1], "\b(\d)", as.numeric, perl = TRUE))

pat <- "(\w+)="
vars <- strapplyc(dat[1,1], pat, simplify = unlist)    
lens <- count.fields(textConnection(strsplit(dat[1,1], pat)[[1]][-1]), ",")

colnames(m) <- unlist(Map(function(v, n) paste0(v, 1:n), vars, lens))

giving (continued after output):

> m
     PVS11 PS1 PS2 PS3 PS4 PS5 PM1 PM2 PM3 PM4 PM5 PM6 PM7 PP1 PP2 PP3 PP4 PP5
[1,]     0   0   1   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0
[2,]     1   0   0   0   0   0   0   1   0   0   0   0   0   0   0   1   0   0
[3,]     1   0   1   0   0   0   0   1   0   0   0   0   0   0   0   1   0   0
[4,]     1   0   0   0   0   0   0   1   0   0   0   0   0   0   0   1   0   0
[5,]     1   0   0   0   0   0   0   1   0   0   0   0   0   0   0   1   0   0
[6,]     0   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0
[7,]     1   0   0   0   0   0   0   1   0   0   0   0   0   0   0   1   0   0
[8,]     1   0   0   0   0   0   0   1   0   0   0   0   0   0   0   1   0   0
     PP6 BA11 BS1 BS2 BS3 BS4 BS5 BP1 BP2 BP3 BP4 BP5 BP6 BP7 BP8
[1,]   0    0   0   0   0   0   0   0   0   0   0   0   0   0   0
[2,]   0    0   0   0   0   0   0   0   0   0   0   0   0   0   0
[3,]   0    0   0   0   0   0   0   0   0   0   0   0   0   0   0
[4,]   0    0   0   0   0   0   0   0   0   0   0   0   0   0   0
[5,]   0    0   0   0   0   0   0   0   0   0   0   0   0   0   0
[6,]   0    0   0   0   0   0   0   0   0   0   1   0   0   1   0
[7,]   0    0   0   0   0   0   0   0   0   0   0   0   0   0   0
[8,]   0    0   0   0   0   0   0   0   0   0   0   0   0   0   0
>

Alternately replace the last 4 lines of code that calculate and set the column names with

vars <- c("PVS1", "PS", "PM", "PP", "BA1", "BS", "BP")
lens <- c(1, 5, 7, 6, 1, 5, 8)
colnames(m) <- unlist(Map(function(v, n) paste0(v, 1:n), vars, lens))

or with

colnames(m) <-
  c("PVS11", "PS1", "PS2", "PS3", "PS4", "PS5", "PM1", "PM2", "PM3", 
  "PM4", "PM5", "PM6", "PM7", "PP1", "PP2", "PP3", "PP4", "PP5", 
  "PP6", "BA11", "BS1", "BS2", "BS3", "BS4", "BS5", "BP1", "BP2", 
  "BP3", "BP4", "BP5", "BP6", "BP7", "BP8")

user_8825261 · Answer

Perhaps you could try some thing like this, with dplyr, tidyr and stringr :

# split on spaces excepted those in bracketed lists
df1 <- x %>% mutate(id=row_number(), 
             a=intervar %>% str_replace_all("(?<=,) ","") %>% str_split(" ")) %>%
  unnest(a) %>% 
  select(id,a)
# Variable names = bracketed list
df2 <- df1 %>%
  filter(str_detect(a,"=$$")) %>%
  separate(a,into=c("v","l"),sep='=') %>%
  mutate(l=l %>% str_replace_all("[\[$$]","") %>% str_split(",")) %>%
  unnest(l) %>%
  group_by(id,v) %>%
  mutate(v = paste0(v,row_number()))
# Variable names without bracketed list
df3 <- df1 %>% filter(str_detect(a,"=(?!\[)")) %>%
  separate(a,into=c("v","l"),sep='=')
# Description
df4 <- x %>% mutate(id=row_number(),decription=str_extract(intervar,"^.*(?= PVS)"))

# Final result
inner_join(df4,bind_rows(df2,df3) %>% arrange(id,v) %>% spread(v,l) ,by="id")

user_5635580 · Answer

Not sure how efficient this is

library(dplyr)
library(tidyr)
library(data.table) #for 'rleid()'

df %>% 
  mutate(Intervar = trimws(gsub('Pathogenic|Likely pathogenic|Uncertain significance|Likely benign|Benign', '', Intervar)), 
         Intervar = gsub(', ', ',', Intervar) ) %>% 
  separate_rows(Intervar, sep = ' ') %>%
  separate(Intervar, into = c('nms', 'vals'), sep = '=') %>%
  mutate(vals = trimws(gsub('\D+', ' ', vals))) %>%
  separate_rows(vals, sep = ' ') %>% 
  mutate(new = rleid(nms)) %>%
  group_by(new) %>%
  mutate(nms = make.unique(nms)) %>%
  ungroup() %>%
  select(-new) %>% 
  pivot_wider(names_from = nms, values_from = vals)%>% 
  unnest()

PVS1  PS    PS.1  PS.2  PS.3  PS.4  PM    PM.1  PM.2  PM.3  PM.4  PM.5  PM.6  PP    PP.1  PP.2  PP.3  PP.4  PP.5  BA1   BS    BS.1  BS.2  BS.3  BS.4  BP    BP.1  BP.2  BP.3  BP.4  BP.5  BP.6  BP.7 
                                  
1 0     0     1     0     0     0     0     1     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0    
2 1     0     0     0     0     0     0     1     0     0     0     0     0     0     0     1     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0    
3 1     0     1     0     0     0     0     1     0     0     0     0     0     0     0     1     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0    
4 1     0     0     0     0     0     0     1     0     0     0     0     0     0     0     1     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0    
5 1     0     0     0     0     0     0     1     0     0     0     0     0     0     0     1     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0    
6 0     0     0     0     0     0     0     1     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     1     0     0     1     0    
7 1     0     0     0     0     0     0     1     0     0     0     0     0     0     0     1     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0    
8 1     0     0     0     0     0     0     1     0     0     0     0     0     0     0     1     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0

Split space- and equal-separated string into different columns

3 Answers