Pivoting/combining a table in R to get rows based on key ID column (tidyverse?)

Question

I have a table (Table 1) that has matched pairs as rows, like this:

ID	Neighbour
Cell_A	Cell_B
Cell_A	Cell_C
Cell_D	Cell_C
Cell_D	Cell_E

and what I want is a table like this:

ID	Neighbour	Neighbour
Cell_A	Cell_B	Cell_C
Cell_D	Cell_C	Cell_E

I have tried using dcast with ID ~ neigbour but end up with an ID column, and then every Cell_* listed as their own column, and NA where there's no match (ie. Cell_A/Cell_D = NA).

I also tried pivot_wider(data = table1, id_cols = ID, values_from = neighbour) and got an error (Error 1: column 1 must be named).

What's the obvious step that I'm missing here? Thanks in advance!!

@AnilGoyal it can have up to 8 in the dataset, the example is for ease. — AlbertQuackstein, Jul 21 '21 at 11:43

score 0 · Answer 1 · answered Jul 21 '21 at 11:39

Does this work:

library(dplyr)
library(tidyr)

df %>% group_by(ID) %>% mutate(N = row_number()) %>% 
       pivot_wider(ID, N, 'Neighbour', values_from = Neighbour)
# A tibble: 2 x 3
# Groups:   ID [2]
  ID     Neighbour1 Neighbour2
  <chr>  <chr>      <chr>     
1 Cell_A Cell_B     Cell_C    
2 Cell_D Cell_C     Cell_E

Pivoting/combining a table in R to get rows based on key ID column (tidyverse?)

1 Answers1