RTrain3k RTrain3k - 3 months ago 12
R Question

How to "melt" row wise instead of column wise in R?

I have a data frame

A
that I would like to melt row wise instead of column wise to look like
B
(which also excludes observations with
NA
in them). Can this be accomplished with the "melt" function?

A <- read.table(text=" Id1 Id2 Var1 Var2 Var3
1 1 1 2 NA
1 2 NA 3 4
1 3 5 6 7 ", header=T)


B <- read.table(text=" Id1 Id2 NewVar
1 1 1
1 1 2
1 2 3
1 2 4
1 3 5
1 3 6
1 3 7 ", header=T)


I found an answer to a similar question but the function seems like overkill and is beyond my current R skills.

Answer

We can order it after melting

library(data.table)
melt(setDT(A), id.var = c("Id1", "Id2"), na.rm = TRUE, value.name = "NewVar"
     )[order(Id1, Id2)][, variable := NULL][]
#   Id1 Id2 NewVar
#1:   1   1     1
#2:   1   1     2
#3:   1   2     3
#4:   1   2     4
#5:   1   3     5
#6:   1   3     6
#7:   1   3     7

Or we can do with base R by replicating to expanding the rows of 'A' for the 'Id' columns, and cbind with the transpose of non-Id columns, concatenated.

`row.names<-`(na.omit(data.frame(A[rep(1:nrow(A), each =3), 1:2], 
                    NewVar= c(t(A[-(1:2)])))), NULL)
#  Id1 Id2 NewVar
#1   1   1      1
#2   1   1      2
#3   1   2      3
#4   1   2      4
#5   1   3      5
#6   1   3      6
#7   1   3      7
Comments