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!
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))