R: Reshaping from wide to long - results messed up

Question

I want to reshape my data from wide to long, but I'm messing something up:

data <- as.data.frame(matrix(c(1:5,0,0,0,5,1,0,0,0,5,0,2,6,2,1,7,6,8,2,4,5),5,5))
colnames(data) <- c("id", "x1.a", "x3.a", "y1.a", "y3.a")
print(data)

#   id x1.a x3.a y1.a y3.a
# 1  1    0    0    2    6
# 2  2    0    0    6    8
# 3  3    0    0    2    2
# 4  4    5    5    1    4
# 5  5    1    0    7    5

reshaped <- reshape(data,
                    varying = 2:5,
                    v.names = c("x.a","y.a"),
                    times = c(1,3),
                    timevar = "time",
                    idvar = "id",
                    direction = "long")
reshaped <- reshaped[with(reshaped,order(id,time)),]

# Result:
#     id time x.a y.a
# 1.1  1    1   0   0
# 1.3  1    3   2   6
# 2.1  2    1   0   0
# 2.3  2    3   6   8
# 3.1  3    1   0   0
# 3.3  3    3   2   2
# 4.1  4    1   5   5
# 4.3  4    3   1   4
# 5.1  5    1   1   0
# 5.3  5    3   7   5

As you can see above, after the reshaping, x1.a and y1.a are grouped together (in x.a), and x3.a and y3.a are grouped together (in y.a). What I want is for x1.a and x3.a to be grouped together (and the same for y1.a and y3.a), like so:

#     id time x.a y.a
# 1.1  1    1   0   2
# 1.3  1    3   0   6
# 2.1  2    1   0   6
# 2.3  2    3   0   8
# 3.1  3    1   0   2
# 3.3  3    3   0   2
# 4.1  4    1   5   1
# 4.3  4    3   5   4
# 5.1  5    1   1   7
# 5.3  5    3   0   5

What am I doing wrong? Thanks.

Does using `varying = list(c('x1.a','x3.a'),c('y1.a','y3.a'))` give you what you want? — joran, May 02 '16 at 21:00

lmo · Answer 1 · 2016-05-02T21:16:32.833

1

The help file says

Notice that the order of variables in varying is like x.1,y.1,x.2,y.2.

So this will work:

reshape(data, varying = c(2,4,3,5), 
     v.names = c("x.a","y.a"),
     times = c(1,3),
     timevar = "time",
     idvar = "id",
     direction = "long")

You have to reorder the columns so that variables from each time period are adjacent.

In this example, R does a pretty good job guessing without the v.names and times arguments, so that

reshape(data,
    varying = c(2,4,3,5),
    timevar = "time",
    idvar = "id",
    direction = "long")

Produces nearly identical results, the only difference being that the variable names x.a and y.a become xa and ya.

edited May 02 '16 at 21:16

answered May 02 '16 at 21:00

lmo

37,904
9
56
69

Thanks. This works but I'm not sure I understand why the reordering jumbles everything up. I used joran's solution eventually. – David Spivak May 03 '16 at 00:12
@DavidSpivak When the varying argument is fed an atomic vector, that vector must be ordered as x.1, y.1, x.2, y.2 as noted in the help file. When you feed varying a list, you have greater ability to indicate the structure. – lmo May 03 '16 at 11:44

score 0 · Answer 2 · answered May 03 '16 at 02:13

Here is an alternative with melt from data.table which can take multiple measure patterns.

library(data.table)
dM <- melt(setDT(data), measure = patterns("^x", "^y"), 
        value.name = c("x.a", "y.a"), variable.name = "time")[order(id)]
dM
#    id time x.a y.a
# 1:  1    1   0   2
# 2:  1    2   0   6
# 3:  2    1   0   6
# 4:  2    2   0   8
# 5:  3    1   0   2
# 6:  3    2   0   2
# 7:  4    1   5   1
# 8:  4    2   5   4
# 9:  5    1   1   7
#10:  5    2   0   5

If we want the 'time' column to be 1, 3

dM[,  time :=  unique(gsub("\\D+", "", names(data)[-1]))[time]][]

R: Reshaping from wide to long - results messed up

2 Answers2