Melt data frame row by row

Question

How can I melt a data frame row by row? I found a really similar question on the forum but I still can't solve my problem without a different id variable.

This is my data set:

V1 V2 V3 V4 V5
51 20 29 12 20
51 22 51 NA NA
51 14 NA NA NA
51 75 NA NA NA

And I want to melt it into:

V1 variable value    
51 V2 20
51 V3 29
51 V4 12
51 V5 20
51 V2 22
51 V3 51
51 V2 14
51 V2 75

Currently my approach is melting it row by row with a for loop and then rbind them together.

library(reshape)

df <- read.table(text = "V1 V2 V3 V4 V5 51 20 29 12 20 51 22 51 NA NA 51 
+14 NA NA NA 51 75 NA NA NA", header = TRUE)

dfall<-NULL
for (i in 1:NROW(df))
{
  dfmelt<-melt(df,id="V1",na.rm=TRUE)
  dfall<-rbind(dfall,dfmelt)
}

Just wondering if there is any way to do this faster? Thanks!

Check out tidyr::gather but doesn't melt achieve what you want? — chinsoon12, Apr 06 '16 at 05:59
@chinsoon12 nope, melt will do it column by column i.e., the "value" column will be 20,22,14,75,29,51,12,20 instead of 20,29,12,20,22,51,14,75 — tiffkyn, Apr 06 '16 at 06:05

akrun · Accepted Answer · 2016-04-06T06:58:37.277

We replicate the first column "V1" and the names of the dataset except the first column name to create the first and second column of the expected output, while the 'value' column is created by transposing the dataset without the first column.

na.omit(data.frame(V1=df1[1][col(df1[-1])],
             variable = names(df1)[-1][row(df1[-1])],
              value = c(t(df1[-1]))))
#   V1 variable value
#1  51       V2    20
#2  51       V3    29
#3  51       V4    12
#4  51       V5    20
#5  51       V2    22
#6  51       V3    51
#9  51       V2    14
#13 51       V2    75

NOTE: No additional packages used.

Or we can use gather (from tidyr) to convert the 'wide' to 'long' format after we create a row id column (add_rownames from dplyr) and then arrange the rows.

library(dplyr)
library(tidyr)
add_rownames(df1) %>% 
        gather(variable, value, V2:V5, na.rm=TRUE) %>% 
        arrange(rowname, V1) %>% 
        select(-rowname)
#      V1 variable value
#    (int)    (chr) (int)
#1    51       V2    20
#2    51       V3    29
#3    51       V4    12
#4    51       V5    20
#5    51       V2    22
#6    51       V3    51
#7    51       V2    14
#8    51       V2    75

Or with data.table

library(data.table)
melt(setDT(df1, keep.rownames=TRUE),
      id.var= c("rn", "V1"), na.rm=TRUE)[
      order(rn, V1)][, rn:= NULL][]

Thanks, this is exactly what I'm looking for. Never thought of doing it this way, was stuck in "how to melt a data frame" — tiffkyn, Apr 06 '16 at 06:08

score 2 · Answer 2 · answered Apr 06 '16 at 06:32

You can make a column with a unique ID for each row, so you can sort on it after melting. Using dplyr:

library(reshape2)
library(dplyr)
df %>% mutate(id = seq_len(n())) %>% 
    melt(id.var = c('V1','id'), na.rm = T) %>% 
    arrange(V1, id, variable) %>% 
    select(-id)
#   V1 variable value
# 1 51       V2    20
# 2 51       V3    29
# 3 51       V4    12
# 4 51       V5    20
# 5 51       V2    22
# 6 51       V3    51
# 7 51       V2    14
# 8 51       V2    75

...or base R:

library(reshape2)
df$id <- seq_along(df$V1)
df2 <- melt(df, id.var = c('V1', 'id'), na.rm = TRUE)
df2[order(df2$V1, df2$id, df2$variable),-2]

Melt data frame row by row

2 Answers2