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

Question

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.

score 3 · Accepted Answer · answered Jul 23 '19 at 22:04

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))

score 0 · Answer 2 · answered Jul 23 '19 at 21:53

You could do it using spread and gather from the tidyr package. You will need to create a temporary id variable in order to be able to pivot the data:

library(dplyr)
library(tidyr)
df %>% 
  gather(key, value , -id, -ct1, -ct2) %>% 
  mutate(key = str_extract(key, "[:alpha:]+")) %>% 
  group_by(key) %>% 
  mutate(tmp_id = row_number()) %>% 
  ungroup() %>% 
  spread(key, value) %>% 
  select(id, s, ct1, ct2, ret)

score 0 · Answer 3 · answered Jul 23 '19 at 22:04

Here is one way that the development version of tidyr (install with devtools::install_github("tidyverse/tidyr")) can make this a lot easier with pivot_longer. We make a spec indicating that the s columns should go into an s variable and similarly for the ret columns. You can remove the final obs column that indicates the number after s or ret if desired.

library(tidyverse)
tbl <- read_table2(
"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"
)

spec <- tibble(
  `.name` = tbl %>% select(matches("^s|ret")) %>% colnames(),
  `.value` = str_remove(`.name`, "\\d$"),
  obs = str_extract(`.name`, "\\d")
)

tbl %>%
  pivot_longer(spec = spec)
#> # A tibble: 12 x 6
#>       id   ct1   ct2 obs   s       ret
#>    <dbl> <dbl> <dbl> <chr> <chr> <dbl>
#>  1     1   0.5   0.5 1     a       0.6
#>  2     1   0.5   0.5 2     b       0.7
#>  3     1   0.5   0.5 3     c       0.8
#>  4     1   0.5   0.5 4     d       0.5
#>  5     2   0.6   0.6 1     c       0.7
#>  6     2   0.6   0.6 2     b       0.6
#>  7     2   0.6   0.6 3     a       0.5
#>  8     2   0.6   0.6 4     d       0.4
#>  9     3   0.7   0.7 1     a       0.7
#> 10     3   0.7   0.7 2     c       0.8
#> 11     3   0.7   0.7 3     d       0.2
#> 12     3   0.7   0.7 4     b       0.1

^{Created on 2019-07-23 by the reprex package (v0.3.0)}

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

3 Answers3