时间戳

首页 > 解决方案 > Reshape from wide to long in R where id and value of id are in the same row

r - Reshape from wide to long in R where id and value of id are in the same row

问题描述

I am having trouble to reshape my data set to a panel data set. My df looks as follows

id   s1  s2  s3  s4  ct1 ct2  ret1 ret2 ret3 ret4

1    a    b   c   d  0.5 0.5   0.6  0.7  0.8   0.5
2    c    b   a   d  0.6 0.6   0.7  0.6  0.5   0.4
3    a    c   d   b  0.7 0.7   0.7  0.8  0.2   0.1

I would like to reshape so it looks as follows

id   s  ct1 ct2  ret

1    a   0.5 0.5 0.6
1    b   0.5 0.5 0.7 
1    c   0.5 0.5 0.8 
1    d   0.5 0.5 0.5 
2    a   0.6 0.6 0.5
2    b   0.6 0.6 0.6 
2    c   0.6 0.6 0.7 
2    d   0.6 0.6 0.4 
3    a   0.7 0.7 0.7
3    b   0.7 0.7 0.1 
3    c   0.7 0.7 0.8 
3    d   0.7 0.7 0.2

I regularly reshape from wide to long but somehow my head cannot get around this problem.

标签： rpanelreshape

解决方案

1) base R

An option using reshape

out <- reshape(
    dat,
    idvar = c("id", "ct1", "ct2"),
    varying = c(outer(c("s", "ret"), 1:4, paste0)),
    sep = "",
    direction = "long"
  )

Remove rownames and column time

rownames(out) <- out$time <- NULL

Result

out[order(out$id), ]
#   id ct1 ct2 s ret
#1   1 0.5 0.5 a 0.6
#4   1 0.5 0.5 b 0.7
#7   1 0.5 0.5 c 0.8
#10  1 0.5 0.5 d 0.5
#2   2 0.6 0.6 c 0.7
#5   2 0.6 0.6 b 0.6
#8   2 0.6 0.6 a 0.5
#11  2 0.6 0.6 d 0.4
#3   3 0.7 0.7 a 0.7
#6   3 0.7 0.7 c 0.8
#9   3 0.7 0.7 d 0.2
#12  3 0.7 0.7 b 0.1

2) data.table

Using melt from data.table

library(data.table)
out <- melt(
    setDT(dat),
    id.vars = c("id", "ct1", "ct2"),
    measure.vars = patterns(c("^s\\d", "^ret\\d")),
    value.name = c("s", "ret")
  )[, variable := NULL]
out

data

dat <- structure(list(id = 1:3, s1 = structure(c(1L, 2L, 1L), .Label = c("a", 
"c"), class = "factor"), s2 = structure(c(1L, 1L, 2L), .Label = c("b", 
"c"), class = "factor"), s3 = structure(c(2L, 1L, 3L), .Label = c("a", 
"c", "d"), class = "factor"), s4 = structure(c(2L, 2L, 1L), .Label = c("b", 
"d"), class = "factor"), ct1 = c(0.5, 0.6, 0.7), ct2 = c(0.5, 
0.6, 0.7), ret1 = c(0.6, 0.7, 0.7), ret2 = c(0.7, 0.6, 0.8), 
    ret3 = c(0.8, 0.5, 0.2), ret4 = c(0.5, 0.4, 0.1)), .Names = c("id", 
"s1", "s2", "s3", "s4", "ct1", "ct2", "ret1", "ret2", "ret3", 
"ret4"), class = "data.frame", row.names = c(NA, -3L))

推荐阅读