I have a dataframe df which contains a single column GO. Each row in df contains either one term or multiple terms (separated by ;) and each term has a specific format - it starts with either P, C or F and is followed by a : and then the actual term.

df <- data.frame(
  GO = c("C:mitochondrion; C:kinetoplast", "", "F:calmodulin binding; C:cytoplasm; C:axoneme",
     "", "P:cilium movement; P:inner dynein arm assembly; C:axoneme", "", "F:calcium ion binding"))


                                                         GO
1                            C:mitochondrion; C:kinetoplast
2                                                          
3              F:calmodulin binding; C:cytoplasm; C:axoneme
4                                                          
5 P:cilium movement; P:inner dynein arm assembly; C:axoneme
6                                                          
7                                     F:calcium ion binding

I want to split this column into three columns BP, CC, MF based on whether the terms start with a P, C or an F respectively. Also I want the three columns to have only the terms and not the other identifiers (P, C, F and :).

This is what I want my new dataframe to look like:

                                          BP                         CC                  MF
1                                            mitochondrion; kinetoplast                    
2                                                                                          
3                                                    cytoplasm; axoneme  calmodulin binding
4                                                                                          
5 cilium movement; inner dynein arm assembly                    axoneme                    
6                                                                                          
7                                                                       calcium ion binding

CodePudding user response：

A tidyverse approach to achieve your desired result may look like so:

library(tidyr)
library(dplyr)

df %>%
  mutate(id = seq(nrow(.))) %>%
  separate_rows(GO, sep = ";\\s") %>%
  separate(GO, into = c("category", "item"), sep = ":") %>%
  mutate(category = recode(category, C = "CC", P = "BP", F = "MF", .default = "foo")) %>%
  replace_na(list(item = "")) %>%
  group_by(id, category) %>%
  summarise(items = paste(item, collapse = "; "), .groups = "drop") %>%
  pivot_wider(names_from = category, values_from = items, values_fill = "") %>%
  select(BP, CC, MF)
#> Warning: Expected 2 pieces. Missing pieces filled with `NA` in 3 rows [3, 7,
#> 11].
#> # A tibble: 7 × 3
#>   BP                                           CC                          MF   
#>   <chr>                                        <chr>                       <chr>
#> 1 ""                                           "mitochondrion; kinetoplas… ""   
#> 2 ""                                           ""                          ""   
#> 3 ""                                           "cytoplasm; axoneme"        "cal…
#> 4 ""                                           ""                          ""   
#> 5 "cilium movement; inner dynein arm assembly" "axoneme"                   ""   
#> 6 ""                                           ""                          ""   
#> 7 ""                                           ""                          "cal…

CodePudding user response：

Here is one more:

1. Create an identifier with row_number
2. Use separate_rows to place each item in a single row
3. use str_detect in case_when to prepare the column names
4. remove the beginnings of the items e.g 'C:' 'F:' and 'P:'
5. group and collapse to one row
6. get distinct values and remove NA
7. apply pivot_wider and select the columns

library(tidyverse)

df %>%
  mutate(row = row_number()) %>%
  separate_rows(GO, sep = '; ') %>% 
  mutate(names = case_when(str_detect(GO, 'C:')~"CC",
                           str_detect(GO, 'F:')~"MF",
                           str_detect(GO, 'P:')~"BP",
                           TRUE ~ NA_character_)) %>% 
  mutate(GO = str_replace_all(GO, '.\\:', '')) %>% 
  group_by(row, names) %>% 
  mutate(b_x = paste(GO, collapse = "; ")) %>% 
  distinct(b_x) %>% 
  na.omit() %>% 
  pivot_wider(
    names_from = names, 
    values_from = b_x
  ) %>% 
  ungroup() %>% 
  select(BP, CC, MF)

  BP                                         CC                         MF                 
  <chr>                                      <chr>                      <chr>              
1 NA                                         mitochondrion; kinetoplast NA                 
2 NA                                         cytoplasm; axoneme         calmodulin binding 
3 cilium movement; inner dynein arm assembly axoneme                    NA                 
4 NA                                         NA                         calcium ion binding