Search code examples
rdata.tablereshape2melt

How to stack 6 specific columns in 2 columns with melt or gather function?

Here is an example of my question :

I would like to go from

A   B   C   D   E   F   G   H
x1  x2  x3  x4  x5  x6  x7  x8
y1  y2  y3  y4  y5  y6  y7  y8
z1  z2  z3  z4  z5  z6  z7  z8

to

A   B   CDE FGH  
x1  x2  x3  x6  
x1  x2  x4  x7  
x1  x2  x5  x8  
y1  y2  y3  y7  
y1  y2  y4  y6  
y1  y2  y5  y8

I can manage to only stack 3 columns into one with this code

NewData= melt(setDT(Data),measure = list(c(6,7,8)), value.name = "FGH ")
Solution

  • We can use patterns

    library(data.table)
melt(setDT(Data), measure = patterns("^[CDE]", "^[FGH]"), 
        value.name = c("CDE", "FGH"))[, variable := NULL][]

    Or another option with unite

    library(dplyr)
library(tidyr)
Data %>% 
    unite(CDE, C, D, E) %>%
    unite(FGH, F, G, H) %>% 
    separate_rows(CDE, FGH)

    data

    Data <- structure(list(A = c("x1", "y1", "z1"), B = c("x2", "y2", "z2"
), C = c("x3", "y3", "z3"), D = c("x4", "y4", "z4"), E = c("x5", 
"y5", "z5"), F = c("x6", "y6", "z6"), G = c("x7", "y7", "z7"), 
    H = c("x8", "y8", "z8")), class = "data.frame", row.names = c(NA, 
-3L))