Home > Blockchain >  Transpose every n columns into new rows in R
Transpose every n columns into new rows in R

Time:05-03

I have a data frame that looks like this

     Frame RightEye_x RightEye_y RightEye_z LeftEye_x LeftEye_y LeftEye_z
     0       773        490        0         778       322          0
     1       780        490        0         789       334          0
     2       781        490        0         792       334          0
     3       783        337        0         797       334          1

And I would like to transform it into

BodyPart Frame  x         y         z 
RightEye   0   773       490        0 
RightEye   1   780       490        0
RightEye   2   781       490        0 
RightEye   3   783       337        0 
LeftEye    0   778       322        0
LeftEye    1   789       334        0
LeftEye    2   792       334        0   
LeftEye    3   797       334        1    
             

CodePudding user response:

Using the melt(...) method in data.table:

library(data.table)
setDT(df)
result <- melt(df, measure.vars = patterns(c('_x', '_y', '_z')), value.name = c('x', 'y', 'z'))
result[, variable:=c('RightEye', 'LeftEye')[variable]]
result
##    Frame variable   x   y z
## 1:     0 RightEye 773 490 0
## 2:     1 RightEye 780 490 0
## 3:     2 RightEye 781 490 0
## 4:     3 RightEye 783 337 0
## 5:     0  LeftEye 778 322 0
## 6:     1  LeftEye 789 334 0
## 7:     2  LeftEye 792 334 0
## 8:     3  LeftEye 797 334 1

CodePudding user response:

We can use base R reshape like below

reshape(
    setNames(df, gsub("(.*)_(.*)", "\\2_\\1", names(df))),
    direction = "long",
    idvar = "Frame",
    varying = -1,
    timevar = "BodyPart",
    sep = "_"
)

which gives

           Frame BodyPart   x   y z
0.RightEye     0 RightEye 773 490 0
1.RightEye     1 RightEye 780 490 0
2.RightEye     2 RightEye 781 490 0
3.RightEye     3 RightEye 783 337 0
0.LeftEye      0  LeftEye 778 322 0
1.LeftEye      1  LeftEye 789 334 0
2.LeftEye      2  LeftEye 792 334 0
3.LeftEye      3  LeftEye 797 334 1
  • Related