how to create new variables from one variable usin

Question

I would appreciate any help to create new variables from one variable.

Specifically, I need help to simultaneously create one row per each ID and various columns of E, where each of the new columns of E, (that is, E1, E2, E3) contains the values of E for each row of ID. I tried doing this which melt followed by spread but I am getting the error:

Error: Duplicate identifiers for rows (4, 7, 9), (1, 3, 6), (2, 5, 8)

Additionally, I tried the solutions discussed here and here but these did not work for my case because I need to be able to create row identifiers for rows (4, 1, 2), (7, 3, 5), and (9, 6, 8). That is, E for rows (4, 1, 2) should be named E1, E for rows (7, 3, 5) should be named E2, E for rows (9, 6, 8) should be named E3, and so on.

#data

dT<-structure(list(A = c("a1", "a2", "a1", "a1", "a2", "a1", "a1", 
    "a2", "a1"), B = c("b2", "b2", "b2", "b1", "b2", "b2", "b1", 
    "b2", "b1"), ID = c("3", "4", "3", "1", "4", "3", "1", "4", "1"
    ), E = c(0.621142094943352, 0.742109450696123, 0.39439152996948, 
    0.40694392882818, 0.779607277916503, 0.550579323666347, 0.352622183880119, 
    0.690660491345867, 0.23378944873769)), class = c("data.table", 
    "data.frame"), row.names = c(NA, -9L))

#my attempt

    A  B ID         E
1: a1 b2  3 0.6211421
2: a2 b2  4 0.7421095
3: a1 b2  3 0.3943915
4: a1 b1  1 0.4069439
5: a2 b2  4 0.7796073
6: a1 b2  3 0.5505793
7: a1 b1  1 0.3526222
8: a2 b2  4 0.6906605
9: a1 b1  1 0.2337894

aTempDF <- melt(dT, id.vars = c("A", "B", "ID")) )

    A  B  ID variable    value
1: a1 b2  3        E 0.6211421
2: a2 b2  4        E 0.7421095
3: a1 b2  3        E 0.3943915
4: a1 b1  1        E 0.4069439
5: a2 b2  4        E 0.7796073
6: a1 b2  3        E 0.5505793
7: a1 b1  1        E 0.3526222
8: a2 b2  4        E 0.6906605
9: a1 b1  1        E 0.2337894

aTempDF%>%spread(variable, value)

Error: Duplicate identifiers for rows (4, 7, 9), (1, 3, 6), (2, 5, 8)

#expected output

    A  B  ID       E1           E2           E3
1: a1 b2  3        0.6211421    0.3943915    0.5505793
2: a2 b2  4        0.7421095    0.7796073    0.6906605 
3: a1 b1  1        0.4069439    0.3526222    0.2337894

Thanks in advance for any help.

Answer 1

You can use dcast from data.table

library(data.table)
dcast(dT, A + B + ID ~ paste0("E", rowid(ID)))
#   A  B ID        E1        E2        E3
#1 a1 b1  1 0.4069439 0.3526222 0.2337894
#2 a1 b2  3 0.6211421 0.3943915 0.5505793
#3 a2 b2  4 0.7421095 0.7796073 0.6906605

You need to create the correct 'time variable' first which is what rowid(ID) does.