R data.table reshape data

Question

I reshape data using data.table.

library(data.table)
market <- data.table(
  stkcd=c(1,2),
  type =c(1,0),
  roa2013=c(2,3),
  roa2014=c(4,5),
  lev2013=c(6,7),
  lev2016=c(8,9))
market
#     stkcd type roa2013 roa2014 lev2013 lev2016
# 1:     1    1       2       4       6       8
# 2:     2    0       3       5       7       9
melt(market,
     measure.vars = patterns("^roa", "^lev"),
     variable.name = "year", 
     value.name = c("roa","lev"))
#     stkcd type year roa lev
# 1:     1    1    1   2   6
# 2:     2    0    1   3   7
# 3:     1    1    2   4   8
# 4:     2    0    2   5   9

This is how the final data should look like.

#     stkcd type year roa lev
# 1     1    1 2013   2   6
# 2     1    1 2014   4  NA
# 3     1    1 2016  NA   8
# 4     2    0 2013   3   7
# 5     2    0 2014   5  NA
# 6     2    0 2016  NA   9

Does anybody have any good ways for it? Thanks.

For the naming of the 'year' values, see [Convert numeric representation of 'variable' column to original string following melt using patterns](https://stackoverflow.com/questions/41883573/convert-numeric-representation-of-variable-column-to-original-string-following). — Henrik, Jun 03 '17 at 13:47

akrun · Answer 1 · 2017-06-03T14:33:10.063

We can do this easily with splitstackshape. Create a delimiter between the numeric and non-numeric part in the columns of interest, then use merged.stack to reshape into 'long' and change the '.time_1` column name to 'year'

library(splitstackshape)
names(market) <- sub("(\\d+)", "_\\1", names(market))
res <- merged.stack(market, var.stubs = c("roa", "lev"), sep="_")
setnames(res, ".time_1", "year")
res
#   stkcd type year roa lev
#1:     1    1 2013   2   6
#2:     1    1 2014   4  NA
#3:     1    1 2016  NA   8
#4:     2    0 2013   3   7
#5:     2    0 2014   5  NA
#6:     2    0 2016  NA   9

score 0 · Answer 2 · answered Jun 03 '17 at 16:15

1.use reshape {stats},

library(data.table)
market <- data.table(
  stkcd=c(1,2),
  type =c(1,0),
  roa2013=c(2,3),
  roa2014=c(4,5),
  lev2013=c(6,7),
  lev2016=c(8,9))

market[,`:=`(roa2016=NA,lev2014=NA)]
long <- reshape(market, 
        idvar = "stkcd", 
        varying = c("roa2013","lev2013",
                    "roa2014","lev2014",
                    "roa2016","lev2016"),
        sep = "",
        timevar = "year",
        direction = "long")
setorder(long,stkcd,year)
long
#     stkcd type year roa lev
# 1:     1    1 2013   2   6
# 2:     1    1 2014   4  NA
# 3:     1    1 2016  NA   8
# 4:     2    0 2013   3   7
# 5:     2    0 2014   5  NA
# 6:     2    0 2016  NA   9

2.str_extract str

library(data.table)
library(stringr)
market <- data.table(
  stkcd=c(1,2),
  type =c(1,0),
  roa2013=c(2,3),
  roa2014=c(4,5),
  lev2013=c(6,7),
  lev2016=c(8,9))
market
long <- melt(market,
             id.vars = c("stkcd","type"))
long[,`:=`(year=str_extract(variable,pattern = "[0-9]{4}"),
           vars=str_extract(variable,pattern = "[a-zA-Z]{1,}"))][,variable:=NULL]
long <- dcast(long, stkcd + type + year ~ vars, value.var = "value")
long
#     stkcd type year lev roa
# 1:     1    1 2013   6   2
# 2:     1    1 2014  NA   4
# 3:     1    1 2016   8  NA
# 4:     2    0 2013   7   3
# 5:     2    0 2014  NA   5
# 6:     2    0 2016   9  NA

...

R data.table reshape data

2 Answers2