Specify column order when casting long to wide

Question

I want to cast a table to wide format using reshape2. I want the columns to be ordered on the vars ie as follows:

data <- as.data.frame(matrix(c(rep(1,3),rep(2,3),rep(1:3,2),rep(0:2,4)),6,4))
colnames(data) <- c("id", "rater", "x", "y")
print(data)

#   id rater x y
# 1  1     1 0 0
# 2  1     2 1 1
# 3  1     3 2 2
# 4  2     1 0 0
# 5  2     2 1 1
# 6  2     3 2 2

To be cast as such:

# Result:
#   id x.1 y.1 x.2 y.2 x.3 y.3
# 1  1   0   0   1   1   2   2
# 4  2   0   0   1   1   2   2

x followed by y, for each sample.

Right now I'm using dcast and getting the following output:

dcast(as.data.table(data), id~rater, value.var=c("x", "y"), sep=".")
#   id x.1 x.2 x.3 y.1 y.2 y.3
#1:  1   0   1   2   0   1   2
#2:  2   0   1   2   0   1   2

But I want it to be x.1, y.1, x.2, y.2 etc.

I can do this via reshape (original) but it takes way too long on my data (over 500k rows, 15min+ per table, plus 20+GB of memory)

reshape(data, idvar = id, timevar = "rater", direction = "wide")

Thanks!

akrun akrun · Accepted Answer · 2019-06-03T16:19:03

An option is to extract the numeric part of the column names, order

out <- dcast(as.data.table(data), id~rater, value.var=c("x", "y"), sep=".")
setcolorder(out,  c(1, order(as.numeric(gsub("\\D+", "", names(out)[-1])))+1))
out
#   id x.1 y.1 x.2 y.2 x.3 y.3
#1:  1   0   0   1   1   2   2
#2:  2   0   0   1   1   2   2

Specify column order when casting long to wide

1 Answers