How to spread mutliple columns in R?

Question

Here is an example

df<-data.frame(
    x=c("a1", "a2", "b1", "b2", "c1", "c2"),
    y=c("p1", "p1", "p2", "p2", "p3", "p3"),
    z=c("aa1", "aa2", "bb1", "bb2", "cc1","cc2"),
    g=c("1", "2", "1", "2", "1", "2")
)
df
   x  y   z g
1 a1 p1 aa1 1
2 a2 p1 aa2 2
3 b1 p2 bb1 1
4 b2 p2 bb2 2
5 c1 p3 cc1 1
6 c2 p3 cc2 2

How to spread it as an output like

   y x1 x2  z1  z2
1 p1 a1 a2 aa1 aa2
2 p2 b1 b2 bb1 bb2
3 p3 c1 c2 cc1 cc2

I tried

 df%>%spread(g, x)
   y   z    1    2
1 p1 aa1   a1 <NA>
2 p1 aa2 <NA>   a2
3 p2 bb1   b1 <NA>
4 p2 bb2 <NA>   b2
5 p3 cc1   c1 <NA>
6 p3 cc2 <NA>   c2

score 3 · Accepted Answer · answered Jul 14 '20 at 15:26

3

Try this:

library(tidyr)
pivot_wider(df,id_cols = y,names_from = g,values_from = c(x,z))

# A tibble: 3 x 5
  y     x_1   x_2   z_1   z_2  
  <chr> <chr> <chr> <chr> <chr>
1 p1    a1    a2    aa1   aa2  
2 p2    b1    b2    bb1   bb2  
3 p3    c1    c2    cc1   cc2

answered Jul 14 '20 at 15:26

Duck

39,058
13
42
84

To match the column names in the question (without the underscore), you can add `names_sep = ''`. – divibisan Jul 14 '20 at 15:29

How to spread mutliple columns in R?

1 Answers1