R: Split data frame into several columns by row value

Question

I have a tibble that looks as follows

tibble(
  text = c('text1','text1','text2','text2','text3','text3'), 
  cell_id = rep(1:3, each=2)
)

   text             cell_id
   <chr>              <int>
 1 text1               1
 2 text1               1
 3 text2               2
 4 text2               2
 5 text3               3
 6 text3               3

I want to split the tibble into columns defined by the cell_id, and move the text value into the respective columns

   col1                col2        col3
   <chr>               <chr>       <chr>  
 1 text1               text2       text3
 2 text1               text2       text3

What would be the cleanest way to do this?

`tidyr::pivot_wider` is what you need, search SO for *"reshape from long to wide"* or similar. — r2evans, Mar 21 '21 at 22:31
The question I marked this a dupe of is a little dated, recommending `reshape` and `spread`. The latter has been superseded by `pivot_wider` in the `tidyr` package. There are links within those answers with examples and such. Good luck! — r2evans, Mar 21 '21 at 22:33

score 1 · Accepted Answer · answered Mar 21 '21 at 22:33

Here is a dplyr way.

library(dplyr)
library(tidyr)

tibble(
  text = c('text1','text1','text2','text2','text3','text3'), 
  cell_id = rep(1:3, each=2)
) %>%
  group_by(text) %>%
  mutate(id = row_number()) %>%
  pivot_wider(
    id_cols = id,
    names_from = cell_id,
    names_prefix = "col",
    values_from = text
  ) %>%
  select(-id)
## A tibble: 2 x 3
#  col1  col2  col3 
#  <chr> <chr> <chr>
#1 text1 text2 text3
#2 text1 text2 text3

R: Split data frame into several columns by row value

1 Answers1