Combining different column values in data.table from a table to form row values of another table

Question

I have the following table DT-1. The columns represents different states

 id    col1   col2  col3  col4  col5 col6
qw-1   ABC   XYZ    QRT   RWQ   OIP  KIJ
qw-2   WET   ERT    YUP   TIP   IUR  ETY
qw-3   QRT   ERT    RWQ   YUP   0     0
qw-4   XYZ   QRT    RWQ    0    0     0
qw-5   YUP    0     0     0     0     0

I would like to form another table DT-2 where the rows in the table look as mentioned below:

 1   ABC   XYZ
 2   XYZ   QRT
 3   QRT   RWQ
 4   RWQ   OIP
 5   OIP   KIJ
 6   WET   ERT
 7   ERT   YUP
 8   YUP   TIP
 9   TIP   IUR
 10  IUR   ETY
 11  QRT   ERT
 12  RWQ   YUP
 13  XYZ   QRT
 14  QRT    RWQ
 15  YUP

The rows 1-5 of 2nd table is formed from the 1st row of 1st table. Similarly the rows 6-10, 11-12, 13-14, & 15 of 2nd table are formed from 2nd ,3rd ,4th & 5th row of table 1.

Is there any data.table method or any other R package to do it?

akrun · Answer 1 · 2017-03-11T21:50:36.190

We can transpose the dataset, remove the first and last element, convert to vector and then create two columns in a data.frame

library(data.table)
m1 <- t(setDF(DT1)[-1])
d1 <- data.frame(col1= c(m1[-length(m1)]), col2 = c(m1[-1]), stringsAsFactors=FALSE)

Or we can melt and then do a group by operation

dN <- setDT(melt(t(DT1[-1])))[, .(col1 = value[-.N], 
               col2 = value[-1]), Var2][, Var2 := NULL][]

Combining different column values in data.table from a table to form row values of another table

1 Answers1