1

I have the following R dataframe (with header):

A   B   C   x       y
a1  b1  c1  0.68    0.43
a1  b1  c2  -0.52   0
a1  b2  c1  -0.58   -0.32
a1  b2  c2  -1.36   -0.73
a2  b1  c1  0.68    0.43
a2  b1  c2  -0.52   0
a2  b2  c1  -0.58   -0.32
a2  b2  c2  -1.36   -0.73

and I would like to obtain the following:

C   x_a1_b1 y_a1_b1 x_a1_b2 y_a1_b2 x_a2_b1 y_a2_b1 x_a2_b2 y_a2_b2
c1  0.68    0.43    -0.58   -0.32   0.68    0.43    -0.58   -0.32
c2  -0.52   0       -1.36   -0.73   -0.52   0       -1.36   -0.73

I have tried to somehow do it with tidyr::spread(), but I do not know how I can make it to spread the original table in the desired way.

Any way to make this work?

Thanks!

Miguel
  • 356
  • 1
  • 15

2 Answers2

2

spread has been replaced with pivot_wider, use that which can handle this.

tidyr::pivot_wider(df, names_from = c(A,B), values_from = c(x, y))

#  C     x_a1_b1 x_a1_b2 x_a2_b1 x_a2_b2 y_a1_b1 y_a1_b2 y_a2_b1 y_a2_b2
#  <chr>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>
#1 c1       0.68  -0.580    0.68  -0.580    0.43   -0.32    0.43   -0.32
#2 c2      -0.52  -1.36    -0.52  -1.36     0      -0.73    0      -0.73

In data.table : 

library(data.table)
dcast(setDT(df), C~A+B, value.var = c('x', 'y'))

data

df <- structure(list(A = c("a1", "a1", "a1", "a1", "a2", "a2", "a2", 
"a2"), B = c("b1", "b1", "b2", "b2", "b1", "b1", "b2", "b2"), 
C = c("c1", "c2", "c1", "c2", "c1", "c2", "c1", "c2"), x = c(0.68, 
-0.52, -0.58, -1.36, 0.68, -0.52, -0.58, -1.36), y = c(0.43, 
0, -0.32, -0.73, 0.43, 0, -0.32, -0.73)), 
class = "data.frame", row.names = c(NA, -8L))
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
1

You can also reshape your data twice:

res1 <- reshape(df, direction = "wide", timevar = "A", idvar = c('B','C'), sep = "_")
res2 <- reshape(res1, direction = "wide", timevar = "B", idvar = "C", sep = "_")
res2
   C x_a1_b1 y_a1_b1 x_a2_b1 y_a2_b1 x_a1_b2 y_a1_b2 x_a2_b2 y_a2_b2
1 c1    0.68    0.43    0.68    0.43   -0.58   -0.32   -0.58   -0.32
2 c2   -0.52    0.00   -0.52    0.00   -1.36   -0.73   -1.36   -0.73
Adamm
  • 2,150
  • 22
  • 30