CODEWITHSUNDEEP
rdataframereshape
Fidel Alencar
Fidel Alencar

Reputation: 71

Reshaping data.frame from wide to long creating more than one column from groups of variables

I have tried to adapt my knowledge about Reshape() to my necessities, but I cannot. My data.frame has two sets of columns (a and b), which I want to reshape to the long format separatly. It also has variables I want to keep unmodified. Like this:

id   2010a  2011a  2012a  char  2010b  2011b  2012b
 1     1      2      3      x     5      6      7
 2     1      2      3      y     5      6      7
 3     1      2      3      z     5      6      7
 4     1      2      3      x     5      6      7

To this long format

id    year     a   b  char
 1    2010     1   5   x 
 2    2010     1   5   y  
 3    2010     1   5   z  
 4    2010     1   5   x  
 1    2011     2   6   x 
 2    2011     2   6   y
 3    2011     2   6   z
 4    2011     2   6   x
 1    2012     3   7   x
 2    2012     3   7   y
 3    2012     3   7   z
 4    2012     3   7   x

Thank you!

Upvotes: 2

Views: 92

Answers (2)

NRLP
NRLP

Reputation: 578

A solution with tidyr:

library(tidyr)
library(dplyr)

dt_final <- gather(dt_initial, key = year, value = value, -id) %>%
  separate(col=year, into=c("year", "name"), sep=-1) %>%
  spread(key = name, value = value) %>%
  arrange(id, year)

Upvotes: 2

s_baldur
s_baldur

Reputation: 33613

What about this?

library(data.table)
data2 <- melt(setDT(data), id.vars = "id", variable.name = "year")
data2[, l := substr(year, 6,6)][, year := gsub("[a-zA-Z]", "", year)]
dcast(data2, id + year ~ l, value.var = "value")[order(year, id)]
    id year a b
 1:  1 2010 1 5
 2:  2 2010 1 5
 3:  3 2010 1 5
 4:  4 2010 1 5
 5:  1 2011 2 6
 6:  2 2011 2 6
 7:  3 2011 2 6
 8:  4 2011 2 6
 9:  1 2012 3 7
10:  2 2012 3 7
11:  3 2012 3 7
12:  4 2012 3 7

Data:

data <- data.frame(
  id = 1:4,
  `2010a` = c(1L, 1L, 1L, 1L),
  `2011a` = c(2L, 2L, 2L, 2L),
  `2012a` = c(3L, 3L, 3L, 3L),
  `2010b` = c(5L, 5L, 5L, 5L),
  `2011b` = c(6L, 6L, 6L, 6L),
  `2012b` = c(7L, 7L, 7L, 7L)
)

Upvotes: 1

Related Questions