将包含分隔字符串的dataframe列拆分为多个列，并保留拆分字符串的特定部分。

Question

我有一个dataframe df，它包含一个列GO。df中的每一行包含一个术语或多个术语(由;分隔)，每个术语都有特定的格式--以P、C或F开头，后面跟着一个:，然后是实际术语。

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

我希望根据术语分别以BP、CC或F开头，将该列分为三列：P、C或F。另外，我希望这三列只有术语，而不是其他标识符(P、C、F和:)。

这就是我希望我的新数据文件看起来是什么样子：

                                          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

Answer 1

实现所需结果的tidyverse方法可能如下所示：

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…

Answer 2

这里还有一个：

row_number

• Use

• 创建一个标识符，将每一项放置在一行中，

• 在case_when中使用str_detect来准备列名

• ，删除项目的开头，例如'C:‘F:’和‘P：’< separate_rows >H 212H 113/separate_rows>组，然后折叠到一行<<代码>H 214/代码><代码>H 115获取不同的值并删除NA<代码>H 216H 117应用pivot_wider，并选择列H 219>G 220代码>

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

Answer 3

另一种可能的解决办法是：

library(tidyverse)

df %>% 
  rownames_to_column("id") %>% 
  separate_rows(GO, sep = "; ") %>% 
  separate(GO, into = c("name", "value"), sep = ":", fill = "right") %>% 
  filter(complete.cases(.)) %>% 
  pivot_wider(id_cols = id, values_fn = list) %>% rowwise %>% 
  mutate(across(-id, ~ str_c(.x, collapse = "; "))) %>% 
  left_join(data.frame(id = seq(nrow(df)) %>% as.character), .) %>% 
  mutate(across(everything(), replace_na, "")) %>% 
  select(BP = P, CC = C, MF = F)

#> Joining, by = "id"
#>                                           BP                         CC
#> 1                                            mitochondrion; kinetoplast
#> 2                                                                      
#> 3                                                    cytoplasm; axoneme
#> 4                                                                      
#> 5 cilium movement; inner dynein arm assembly                    axoneme
#> 6                                                                      
#> 7                                                                      
#>                    MF
#> 1                    
#> 2                    
#> 3  calmodulin binding
#> 4                    
#> 5                    
#> 6                    
#> 7 calcium ion binding