extracting values from column using tidyr

Question

I have data.frame annot defined as:

annot <- structure(list(Name = c("dd_1", "dd_2", "dd_3","dd_4", "dd_5", "dd_6","dd_7"), GOs = 
c("C:extracellular space; C:cell body; P:cell migration process; P:NF/ß pathway", 
   "C:Signal transduction; C:nucleus; F:positive regulation; P:single organism; P:positive(+) regulation",
   "C:cardiomyceltes; C:intracellular pace; F:putative; F:magnesium ion binding; F:calcium ion binding; P:visual perception; P:blood coagulation",
   "F:poly(A) RNA binding; P:DNA-templated transcription, initiation",
    "C:ULK1-ATG13-FIP200 complex; F:histone-arginine N-methyltransferase activity; P:single-organism cellular process",
    "F:3'-5' DNA helicase activity; P:acetate-CoA ligase activity",
    "F:UDP-N-acetylmuramoylalanyl-D-glutamyl-2,6-diaminopimelate-D-alanyl-D-alanine ligase activity; P:oxidoreductase activity, acting on the aldehyde or oxo group of donors, NAD or NADP as acceptor"
)), .Names = c("Name", "GOs"), class = "data.frame", row.names = c(NA, 
-7L))

The data.frame looks like this:

Name     GOs
dd_1     C:extracellular space; C:cell body; P:cell migration process; P:NF/ß pathway 
dd_2     C:Signal transduction; C:nucleus; F:positive regulation; P:single organism; P:positive(+) regulation
dd_3     C:cardiomyceltes; C:intracellular pace; F:putative; F:magnesium ion binding; F:calcium ion binding; P:visual perception; P:blood coagulation
dd_4     F:poly(A) RNA binding; P:DNA-templated transcription, initiation
dd_5     C:ULK1-ATG13-FIP200 complex; F:histone-arginine N-methyltransferase activity; P:single-organism cellular process
dd_6     F:3'-5' DNA helicase activity; P:acetate-CoA ligase activity
dd_7     F:UDP-N-acetylmuramoylalanyl-D-glutamyl-2,6-diaminopimelate-D-alanyl-D-alanine ligase activity; P:oxidoreductase activity, acting on the aldehyde or oxo group of donors, NAD or NADP as acceptor

Every entry contain words, special character, alpha numeric characters in C, F, P. I would like split the all the values that corresponds to C:xxx;F:yyy:P:zzz into separate columns with their corresponding values like the following:

Name   Component                             Function                  P
dd_1   C:extracellular space;C:cell body     F:transport carrier       P:cell migration process;P:NF/ß pathway  
dd_2   C:Signal transduction;C:nucleus       F:positive regulation     P:single organism;P:positive regulation 
dd_3   C:cardiomyceltes;C:intracellular pace F:magnesium ion           P:visual perception;P:blood coagulationbinding;F:calcium ion binding; 
dd_4                                         F:poly(A) RNA binding;    P:DNA-templated transcription, initiation
dd_5   C:ULK1-ATG13-FIP200 complex           F:histone-arginine N-methyltransferase activity               P:single-organism cellular process
dd_6                                         F:3'-5' DNA helicase activity; P:acetate-CoA ligase activity
dd_7                                         F:UDP-N-acetylmuramoylalanyl-D-glutamyl-2,6-diaminopimelate-D-alanyl-D-alanine ligase activity P:oxidoreductase activity, acting on the aldehyde or oxo group of donors, NAD or NADP as acceptor

I tried following command in R using tidyr

separate(annot, GOs, into = c("P", "F", "C"), sep = "[a-z]+=")

but it returned the following error:

Error: Values not split into 3 pieces at 1, 2, 3,4

@akrun your previous of tidyr worked fine for the the example data which I provided. But in my original file, there are lot of specail character like (),/,0-9, ,, and also in may row, there will only P or F or C so the tidyr throwed me error cannot regex for many entries but worked fine for the rows which were similar liket the data I gave previously. now I updated the datset with as much possible types I could. I will also share the file with youu shortly — Dinesh, Feb 16 '15 at 10:18
Your new dataset works fine with the updated strsplit solution. — akrun, Feb 16 '15 at 10:20

score 7 · Answer 1 · answered Feb 18 '15 at 03:39

I think you'd be better off with a tidy format like this:

library(tidyr)
library(dplyr)
annot %>%
  tbl_df() %>%
  mutate(GOs = strsplit(GOs, "; ")) %>% # split each GO into a vector
  unnest(GOs) %>%  # unnest the vectors into multiple rows
  separate(GOs, c("type", "value"), ":") 
#> Source: local data frame [25 x 3]
#> 
#>    Name type                  value
#> 1  dd_1    C    extracellular space
#> 2  dd_1    C              cell body
#> 3  dd_1    P cell migration process
#> 4  dd_1    P           NF/ß pathway
#> 5  dd_2    C    Signal transduction
#> 6  dd_2    C                nucleus
#> 7  dd_2    F    positive regulation
#> 8  dd_2    P        single organism
#> 9  dd_2    P positive(+) regulation
#> 10 dd_3    C         cardiomyceltes
#> ..  ...  ...                    ...

akrun · Accepted Answer · 2015-02-16T10:05:54.313

You could try strsplit

res <- do.call(rbind.data.frame,lapply(strsplit(annot$GOs, ";"), 
      function(x) tapply(x, sub(':.*', '', x), FUN=paste, collapse=";")))

res1 <-  data.frame(Name=annot[,1], setNames(res, c('Component',
     'Function', 'P')), stringsAsFactors=FALSE)

res1
#   Name                             Component
#1 dd_1     C:extracellular space;C:cell body
#2 dd_2       C:Signal transduction;C:nucleus
#3 dd_3 C:cardiomyceltes;C:intracellular pace
#                                                 Function
#1                                      F:transport carrier
#2                                    F:positive regulation
#3 F:putative;F:magnesium ion binding;F:calcium ion binding
#                                       P
#1 P:cell migration process;P:NF/ß pathway
#2 P:single organism;P:positive regulation
#3 P:visual perception;P:blood coagulation

Or you could try extract from tidyr

library(tidyr)
extract(annot, GOs, c('C', 'F', 'P'), '(C:[^F]+);(F:[^P]+);(P:.*)')
# Name                                      C
#1 dd_1     C:extracellular space;C:cell body
#2 dd_2       C:Signal transduction;C:nucleus
#3 dd_3 C:cardiomyceltes;C:intracellular pace
#                                                        F
#1                                      F:transport carrier
#2                                    F:positive regulation
#3 F:putative;F:magnesium ion binding;F:calcium ion binding
#                                       P
#1 P:cell migration process;P:NF/ß pathway
#2 P:single organism;P:positive regulation
#3 P:visual perception;P:blood coagulation

Update

The new dataset have some elements absent (i.e. "C" , "F" etc) for each row. You could modify the first solution

res <- do.call(rbind.data.frame,lapply(strsplit(annot$GOs, "; "),function(x){
      x1 <- tapply(x, sub(':.*', '', x), FUN=paste, collapse=";")
      x1[match(c('C', 'F', 'P'),  names(x1))]}))
 res1 <-  data.frame(Name=annot[,1], setNames(res, c('Component',
          'Function', 'P')), stringsAsFactors=FALSE)
 head(res1,2)
 #  Name                         Component              Function
 #1 dd_1 C:extracellular space;C:cell body                  <NA>
 #2 dd_2   C:Signal transduction;C:nucleus F:positive regulation
 #                                          P
 #1    P:cell migration process;P:NF/ß pathway
 #2 P:single organism;P:positive(+) regulation

I tried ur strsplit function. But when I ran the command, it gave me error stating that "Error in strsplit(annot$GOs, ";") : non-character argument" — Dinesh, Feb 15 '15 at 12:59
Sorry for the confusion - I added the dput to the question and thought the column would be character.. — talat, Feb 15 '15 at 19:17
@docendodiscimus I tried the tidyr code on my actual dataset. It gave me error "Error in UseMethod("extract_") : no applicable method for 'extract_' applied to an object of class "factor" . How can I rectify it — Dinesh, Feb 15 '15 at 20:03
@akrun I tried it but it gave the following error Error in UseMethod("extract_") :no applicable method for 'extract_' applied to an object of class "character" — Dinesh, Feb 15 '15 at 20:09
@Thileepan My code was based on the example you showed. It worked on that. Have you tried the `strsplit` — akrun, Feb 15 '15 at 20:10
@akrun I tried your strsplit but it gave me the following error "Error in setNames(res, c("Component", "Function", "P")) : 'names' attribute [3] must be the same length as the vector [1]"..I dont know whats wrong...please help me — Dinesh, Feb 15 '15 at 20:20
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/70972/discussion-between-thileepan-and-akrun). — Dinesh, Feb 15 '15 at 20:25
@akrun My Rstudio is using tidyr 0.2.0. Is this causing error? My original file is in same file format.. I can share the file..But how can I share the file...Please guide me — Dinesh, Feb 15 '15 at 20:32

extracting values from column using tidyr

2 Answers2

Update

Linked