match values to the rowname in R-CodePudding

I have a dataframe looks like this

path:hsa00010   cpd:C00022
path:hsa00010   cpd:C00024
path:hsa00010   cpd:C00031
path:hsa00010   cpd:C00033
path:hsa00010   cpd:C00036
path:hsa00010   cpd:C00068
path:hsa00010   cpd:C00074
path:hsa00010   cpd:C00084
path:hsa00010   cpd:C00103
path:hsa00010   cpd:C00111
path:hsa00020   cpd:C00022
path:hsa00020   cpd:C00024
path:hsa00020   cpd:C00031
path:hsa00020   cpd:C00033
path:hsa00020   cpd:C00036
path:hsa00020   cpd:C00068
path:hsa00020   cpd:C00074
path:hsa00020   cpd:C00084
path:hsa00020   cpd:C00103
path:hsa00020   cpd:C00111

I would like to use second column as the rowname and obtain dataframe like this

cpd:C00022 path:hsa00010 path:hsa00020
cpd:C00024 path:hsa00010 path:hsa00020
...

Can somebody give any ideas? Thanks!

CodePudding user response：

Do you want something like this, using dplyr::group_by() and dplyr::summarize()? Once you have this, you can of course turn the cpd... column into rownames if you really need it as rownames.

library(dplyr)
library(tidyr)

df <- tibble::tribble(
  ~x,              ~y,
  "path:hsa00010", "cpd:C00022",
  "path:hsa00010", "cpd:C00024",
  "path:hsa00010", "cpd:C00031",
  "path:hsa00010", "cpd:C00033",
  "path:hsa00010", "cpd:C00036",
  "path:hsa00010", "cpd:C00068",
  "path:hsa00010", "cpd:C00074",
  "path:hsa00010", "cpd:C00084",
  "path:hsa00010", "cpd:C00103",
  "path:hsa00010", "cpd:C00111",
  "path:hsa00020", "cpd:C00022",
  "path:hsa00020", "cpd:C00024",
  "path:hsa00020", "cpd:C00031",
  "path:hsa00020", "cpd:C00033",
  "path:hsa00020", "cpd:C00036",
  "path:hsa00020", "cpd:C00068",
  "path:hsa00020", "cpd:C00074",
  "path:hsa00020", "cpd:C00084",
  "path:hsa00020", "cpd:C00103",
  "path:hsa00020", "cpd:C00111"
)

df %>% 
  group_by(y) %>% 
  summarise(x = list(x)) %>% 
  ungroup() %>% 
  unnest_wider(x, names_sep = "_")
#> # A tibble: 10 x 3
#>    y          x_1           x_2          
#>    <chr>      <chr>         <chr>        
#>  1 cpd:C00022 path:hsa00010 path:hsa00020
#>  2 cpd:C00024 path:hsa00010 path:hsa00020
#>  3 cpd:C00031 path:hsa00010 path:hsa00020
#>  4 cpd:C00033 path:hsa00010 path:hsa00020
#>  5 cpd:C00036 path:hsa00010 path:hsa00020
#>  6 cpd:C00068 path:hsa00010 path:hsa00020
#>  7 cpd:C00074 path:hsa00010 path:hsa00020
#>  8 cpd:C00084 path:hsa00010 path:hsa00020
#>  9 cpd:C00103 path:hsa00010 path:hsa00020
#> 10 cpd:C00111 path:hsa00010 path:hsa00020

^{Created on 2021-12-21 by the reprex package (v2.0.0)}

CodePudding user response：

We are probably looking for pivot_wider here.

library(tidyr)
library(dplyr)
library(stringr)

df %>% pivot_wider(values_from = path,
                   values_fn = \(x) str_remove_all(x, 'path:'),
                   names_from = path,
                   names_glue = 'path_{1:length(unique(path))}'
                   )%>%
    mutate(cpd = str_remove_all(cpd, "^cpd:"))

# A tibble: 10 × 3
   cpd    path_1   path_2  
   <chr>  <chr>    <chr>   
 1 C00022 hsa00010 hsa00020
 2 C00024 hsa00010 hsa00020
 3 C00031 hsa00010 hsa00020
 4 C00033 hsa00010 hsa00020
 5 C00036 hsa00010 hsa00020
 6 C00068 hsa00010 hsa00020
 7 C00074 hsa00010 hsa00020
 8 C00084 hsa00010 hsa00020
 9 C00103 hsa00010 hsa00020
10 C00111 hsa00010 hsa00020