reshape data frame x and y-values on the same row

Question

I have this data frame and I want x and y-values on the same row:

ID     x   x  y   y
A      P1  P2 R1 R2
B      P1  P2 R1 R2
C      P1  P2 R1 R2
D      P1  P2 R1 R2

I want this as data frame :

ID x    y
A  P1   R1
A  P2   R2
B  P1   R1
B  P2   R2
C  P1   R1
C  P2   R2
D  P1   R1
D  P2   R2

akrun · Accepted Answer · 2015-10-19T13:18:02.160

We can use melt from data.table which can take multiple measure columns. We convert the 'data.frame' to 'data.table' (setDT(df1)), melt and specify the patterns of the column names.

library(data.table)#v1.9.6+
DT <- melt(setDT(df1), measure=patterns('^x', 'y'),
               value.name=c('x', 'y'))[, variable:= NULL][]
DT
#   ID  x  y
#1:  A P1 R1
#2:  B P1 R1
#3:  C P1 R1
#4:  D P1 R1
#5:  A P2 R2
#6:  B P2 R2
#7:  C P2 R2
#8:  D P2 R2

We can order the 'ID' column to get the same result as in the OP's post

DT[order(ID)]
#   ID  x  y
#1:  A P1 R1
#2:  A P2 R2
#3:  B P1 R1
#4:  B P2 R2
#5:  C P1 R1
#6:  C P2 R2
#7:  D P1 R1
#8:  D P2 R2

reshape data frame x and y-values on the same row

1 Answers1