Bdtjtk

I have a following sample dataset, and I am trying to reshape to long form using data.table package (no dplyr solution please).

test_data<-structure(list(id = c("a", "b", "c", "d"), demo_d1_alt = c(2, 

3, 4, 5), demo_d1_pre = c(5, 4, 5, 5), demo_d2_alt = c(4, 5, 

6, 7), demo_d3_alt = c(8, 9, 10, 11), demo_d3_pre = c(5, 5, 5, 

5)), .Names = c("id", "demo_d1_alt", "demo_d1_pre", "demo_d2_alt", 

"demo_d3_alt", "demo_d3_pre"), class = c("tbl_df", "tbl", "data.frame"

), row.names = c(NA, -4L))



id  demo_d1_alt demo_d1_pre demo_d2_alt demo_d3_alt demo_d3_pre

a   2   5   4   8   5

b   3   4   5   9   5

c   4   5   6   10  5

d   5   5   7   11  5

Following is my code and the output:

library(data.table)

data_long <- melt.data.table(

     setDT(test_data), id.vars = "id", 

     measure.vars = patterns(alt = "alt",

                             prec= "pre"))

data_long

    id variable alt prec

 1:  a        1   2    5

 2:  b        1   3    4

 3:  c        1   4    5

 4:  d        1   5    5

 5:  a        2   4    5

 6:  b        2   5    5

 7:  c        2   6    5

 8:  d        2   7    5

 9:  a        3   8   NA

10:  b        3   9   NA

11:  c        3  10   NA

12:  d        3  11   NA

My expected output is (since only for demo_d2 there is no prec)

data_long

    id variable alt prec

 1:  a        1   2    5

 2:  b        1   3    4

 3:  c        1   4    5

 4:  d        1   5    5

 5:  a        2   4    NA

 6:  b        2   5    NA

 7:  c        2   6    NA

 8:  d        2   7    NA

 9:  a        3   8   5

10:  b        3   9   5

11:  c        3  10   5

12:  d        3  11   5

Please suggest.

One option would be to do a melt/dcast

dcast(melt(setDT(test_data), measure = patterns("d\d+"))[,

    c("var1", "var2") := tstrsplit(variable, "_")[-1]], 

       id + var1 ~ var2, value.var = 'value')[order(var1)]

#    id var1 alt pre

# 1:  a   d1   2   5

# 2:  b   d1   3   4

# 3:  c   d1   4   5

# 4:  d   d1   5   5

# 5:  a   d2   4  NA

# 6:  b   d2   5  NA

# 7:  c   d2   6  NA

# 8:  d   d2   7  NA

# 9:  a   d3   8   5

#10:  b   d3   9   5

#11:  c   d3  10   5

#12:  d   d3  11   5

Currently, most R approaches don't handle these types of unbalanced datasets very neatly, but instead require you to first convert the data into a "long" format, do some transformation, and then go back into a "wide" format. For this, I had written the merged.stack function as part of my "splitstackshape" package, so named because it would first stack related columns of data into a list and then merge them together.

The approach would be something like:

library(splitstackshape)

merged.stack(test_data, var.stubs = c("_alt", "_pre"), sep = "var.stubs", atStart = FALSE)

#     id .time_1 _alt _pre

#  1:  a demo_d1    2    5

#  2:  a demo_d2    4   NA

#  3:  a demo_d3    8    5

#  4:  b demo_d1    3    4

#  5:  b demo_d2    5   NA

#  6:  b demo_d3    9    5

#  7:  c demo_d1    4    5

#  8:  c demo_d2    6   NA

#  9:  c demo_d3   10    5

# 10:  d demo_d1    5    5

# 11:  d demo_d2    7   NA

# 12:  d demo_d3   11    5

However, with the improvements that "data.table" has brought to melt, it made sense to revisit the approach.

The working draft can be found here, and you can load the relevant functions with:

source("https://gist.githubusercontent.com/mrdwab/ceded54b616bdc61cb0f/raw/c5a583267f8682a5afea1b551347eafdab618471/MELT.R")

Usage is straightforward: specify the dataset, the stubs, and whether the stubs are at the start or at the end of the variable names.

library(data.table)

ReshapeLong_(test_data, stubs = c(alt = "_alt", pre = "_pre"), end_stub = TRUE)

#     id variable alt pre

#  1:  a  demo_d1   2   5

#  2:  b  demo_d1   3   4

#  3:  c  demo_d1   4   5

#  4:  d  demo_d1   5   5

#  5:  a  demo_d2   4  NA

#  6:  b  demo_d2   5  NA

#  7:  c  demo_d2   6  NA

#  8:  d  demo_d2   7  NA

#  9:  a  demo_d3   8   5

# 10:  b  demo_d3   9   5

# 11:  c  demo_d3  10   5

# 12:  d  demo_d3  11   5

Simple hack would be

test_data <- as.data.table(test_data)

test_data[,.NA:=NA]

melt(test_data, id='id', 

     measure = list(c('demo_d1_alt', 'demo_d2_alt', 'demo_d3_alt'),

                    c('demo_d1_pre', '.NA', 'demo_d3_pre')))

align the variables by hand and insert '.NA' where no observation.

If there are mulitple missing variables,

test_data <- as.data.table(test_data)

test_data[,.NA:=NA]

melt(test_data, id='id', 

     measure = list(c('demo_d1_alt', 'demo_d2_alt', '.NA'),

                    c('demo_d1_pre', '.NA', 'demo_d3_pre')))