convert long-format grouped data to wide format

Question

I currently have long format data

# Input
library(dplyr)
library(tidyr)

tibble(
    x = c(1,1,2,2), 
    y = c("A", "B", "C", "D")
)

I want to widen the data to look like this:

# Desired Output
tibble(
    x = c(1,2), 
    x_1 = c("A", "C"), 
    x_2 = c("B", "D")
)

But this isn't a typical tidyr::spread() because my column names are not becoming cell values. So while this seems simple, I'm stumped.

Answers [here](https://stackoverflow.com/q/31125693/5325862) might help — camille, Sep 13 '19 at 17:43

IceCreamToucan · Answer 1 · 2019-09-13T18:12:57.257

3

library(data.table)

dcast(df, x ~ paste0('x_', rowid(x)))
#   x x_1 x_2
# 1 1   A   B
# 2 2   C   D

edited Sep 13 '19 at 18:12

answered Sep 13 '19 at 18:01

IceCreamToucan

28,083
2
22
38

akrun · Answer 2 · 2019-09-14T17:38:01.320

2

An option would be to create a sequence column by group and then do the spread

library(dplyr)
library(tidyr)
library(stringr)
tbl1 %>% 
    group_by(x) %>% 
    group_by(x1 = str_c('x_', row_number())) %>% 
    # or using paste0 from base R (as @d.b commented)
    # group_by(x1 = paste0('x_', row_number())) %>%
    spread(x1, y)
# A tibble: 2 x 3
#      x x_1   x_2  
#  <dbl> <chr> <chr>
#1     1 A     B    
#2     2 C     D

Or in base R

unstack(df, y~ ave(x, x, FUN = seq_along))

edited Sep 14 '19 at 17:38

answered Sep 13 '19 at 17:37

akrun

874,273
37
540
662

I made a mistake in my desired output which has since been edited. Thank you for your initial response. But you'll see now that A & B need to be in the same row. Apologies for my initial error. – Joe Sep 13 '19 at 17:56
There was no issue with your code, and I hope that changing the correct answer status wasn't inappropriate. There was a post this morning using chop(df,y) %>% unnest_wider(y) that solved the problem in fewer lines of code. I am unsure of the SO protocol for selecting first correct response vs. later correct responses. I sincerely hope you didn't take offense at this. You have been extremely helpful to me and many others on SO. – Joe Sep 14 '19 at 17:18
@Joe I do agree the fewer lines part (if you are okay with not formatting the column name) – akrun Sep 14 '19 at 17:40
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/199465/discussion-between-joe-and-akrun). – Joe Sep 14 '19 at 17:44

score 1 · Accepted Answer · answered Sep 14 '19 at 12:32

from tidyr 1.0.0 you can do the following :

library(tidyr)

df <- tibble(
  x = c(1,1,2,2), 
  y = c("A", "B", "C", "D")
) 

chop(df,y) %>% unnest_wider(y)
#> New names:
#> * `` -> ...1
#> * `` -> ...2
#> New names:
#> * `` -> ...1
#> * `` -> ...2
#> # A tibble: 2 x 3
#>       x ...1  ...2 
#>   <dbl> <chr> <chr>
#> 1     1 A     B    
#> 2     2 C     D

^{Created on 2019-09-14 by the reprex package (v0.3.0)}

Add parameter names_repair = ~sub("..." , "x_", ., fixed=TRUE) to the unnest_wider call to get the names you gave in your question.

convert long-format grouped data to wide format

3 Answers3