1

I have data like this.

structure(list(structureId = c("1JDN", "1DP4", "1XS5", "1SW1", 
"1P99", "1IXH"), structureTitle = c("Crystal Structure of Hormone Receptor", 
"DIMERIZED HORMONE BINDING DOMAIN OF THE ATRIAL NATRIURETIC PEPTIDE RECEPTOR", 
"The Crystal Structure of Lipoprotein Tp32 from Treponema pallidum", 
"Crystal structure of ProX from Archeoglobus fulgidus in complex with proline betaine", 
"1.7A crystal structure of protein PG110 from Staphylococcus aureus", 
"PHOSPHATE-BINDING PROTEIN (PBP) COMPLEXED WITH PHOSPHATE"), 
    chainId = c("A", "A", "A", "A", "A", "A"), ligandId = c("BMA,CL,FUC,MAN,NAG,NDG", 
    "CL,NAG,SO4", "MET", "MSE,PBE,ZN", "GLY,MET", "PO4"), ligandName = c("BETA-D-MANNOSE,CHLORIDE ION,ALPHA-L-FUCOSE,ALPHA-D-MANNOSE,N-ACETYL-D-GLUCOSAMINE,2-(ACETYLAMINO)-2-DEOXY-A-D-GLUCOPYRANOSE", 
    "CHLORIDE ION,N-ACETYL-D-GLUCOSAMINE,SULFATE ION", "METHIONINE", 
    "SELENOMETHIONINE,1,1-DIMETHYL-PROLINIUM,ZINC ION", "GLYCINE,METHIONINE", 
    "PHOSPHATE ION")), row.names = c(NA, -6L), class = c("tbl_df", 
"tbl", "data.frame"))

I'd like to split the values of ligandId and ligandName in different rows. I mean, just 1 ligandId and ligandName per row.

I've tried using separate_rows but it didn't handle well with my two columns. 

df %>% separate_rows(ligandId, ligandName, sep = ",")

But I'm getting this error:

> df %>% separate_rows(ligandId, ligandName, sep = ",")
Error: All nested columns must have the same number of elements.
Call `rlang::last_error()` to see a backtrace
> rlang::last_error()
<error>
message: All nested columns must have the same number of elements.
class:   `rlang_error`
backtrace:
  1. tidyr::separate_rows(., ligandId, ligandName, sep = ",")
 10. tidyr:::unnest.data.frame(data, !!!syms(vars), .drop = FALSE)
 12. tidyr::separate_rows(., ligandId, ligandName, sep = ",")
Call `rlang::last_trace()` to see the full backtrace

Also, I tried this: Split comma-separated strings in a column into separate rows but was unsuccessful.

In the end I'd like to have something like this:

1JDN   A   BMA   BETA-D-MANNOSE
1JDN   A   CL    CHLORIDE ION
1JDN   A   FUC   ALPHA-L-FUCOSE
1JDN   A   MAN   ALPHA-D-MANNOSE
1JDN   A   NAG   N-ACETYL-D-GLUCOSAMINE
1JDN   A   NDG   2-(ACETYLAMINO)-2-DEOXY-A-D-GLUCOPYRANOSE
...
Aureliano Guedes
  • 767
  • 6
  • 22
  • Please check `separate` and `gather` from `tidyverse` – Sonny May 01 '19 at 19:46
  • @Sonny I tried `separated_rows` but didn't handle with multiples columns. @camille sorry, I saw this but I'm got error anyway. – Aureliano Guedes May 01 '19 at 19:52
  • 1
    If you've tried all 4 answers at that post and none worked, you should update your question with information to show why another approach is needed – camille May 01 '19 at 19:54
  • 1
    What is the `str` of your data? It seems you have lists in there. – NelsonGon May 01 '19 at 20:04
  • In the 4th row, you have 2 commas in `ligandId` and 3 in `ligandName`. Maybe it's because of chemical notation—in that case, you'd need to find some different separator for splitting rows. – camille May 01 '19 at 20:07

1 Answers1

3

We can use separate_rows

library(tidyverse)
df1 %>% 
    separate_rows(ligandId, sep=",")

As the number of words for each row of 'ligandId', 'ligandName' are not the same, one option is to gather into 'long' format, then do the separate_rows on the 'val' column, and finally spread it back to 'wide'

df1 %>% 
  gather(key, val, ligandId, ligandName) %>%
  separate_rows(val, sep=",") %>% 
  group_by(structureId, key) %>% 
  mutate(rn = row_number()) %>% 
  spread(key, val) %>%
  select(-rn)
# A tibble: 17 x 5
# Groups:   structureId [6]
#   structureId structureTitle                                        chainId ligandId ligandName                
#   <chr>       <chr>                                                 <chr>   <chr>    <chr>                     
# 1 1DP4        DIMERIZED HORMONE BINDING DOMAIN OF THE ATRIAL NATRI… A       CL       CHLORIDE ION              
# 2 1DP4        DIMERIZED HORMONE BINDING DOMAIN OF THE ATRIAL NATRI… A       NAG      N-ACETYL-D-GLUCOSAMINE    
# 3 1DP4        DIMERIZED HORMONE BINDING DOMAIN OF THE ATRIAL NATRI… A       SO4      SULFATE ION               
# 4 1IXH        PHOSPHATE-BINDING PROTEIN (PBP) COMPLEXED WITH PHOSP… A       PO4      PHOSPHATE ION             
# 5 1JDN        Crystal Structure of Hormone Receptor                 A       BMA      BETA-D-MANNOSE            
# 6 1JDN        Crystal Structure of Hormone Receptor                 A       CL       CHLORIDE ION              
# 7 1JDN        Crystal Structure of Hormone Receptor                 A       FUC      ALPHA-L-FUCOSE            
# 8 1JDN        Crystal Structure of Hormone Receptor                 A       MAN      ALPHA-D-MANNOSE           
# 9 1JDN        Crystal Structure of Hormone Receptor                 A       NAG      N-ACETYL-D-GLUCOSAMINE    
#10 1JDN        Crystal Structure of Hormone Receptor                 A       NDG      2-(ACETYLAMINO)-2-DEOXY-A…
#11 1P99        1.7A crystal structure of protein PG110 from Staphyl… A       GLY      GLYCINE                   
#12 1P99        1.7A crystal structure of protein PG110 from Staphyl… A       MET      METHIONINE                
#13 1SW1        Crystal structure of ProX from Archeoglobus fulgidus… A       MSE      SELENOMETHIONINE          
#14 1SW1        Crystal structure of ProX from Archeoglobus fulgidus… A       PBE      1                         
#15 1SW1        Crystal structure of ProX from Archeoglobus fulgidus… A       ZN       1-DIMETHYL-PROLINIUM      
#16 1SW1        Crystal structure of ProX from Archeoglobus fulgidus… A       <NA>     ZINC ION                  
#17 1XS5        The Crystal Structure of Lipoprotein Tp32 from Trepo… A       MET      METHIONINE

For multiple columns with difference in the number of words, use cSplit

library(splitstackshape)
na.omit(cSplit(df1, c("ligandId", "ligandName"), sep=",", "long"))
akrun
  • 874,273
  • 37
  • 540
  • 662