0

I would like to transforme this dataset :

  ID  v1  v2  v3  c1  c2 c3
1  1  -3 -11  -2  -6  -1 -1
2  2 -10  -4 -12 -11   4  6
3  3   4  -4  15   5   1 -3
4  4  -6   0  -6   5  -1  8
5  5  -7  12   6 -12 -11 11

input<-structure(list(ID = 1:5, v1 = c(-3, -10, 4, -6, -7), v2 = c(-11, 
-4, -4, 0, 12), v3 = c(-2, -12, 15, -6, 6), c1 = c(-6, -11, 5, 
5, -12), c2 = c(-1, 4, 1, -1, -11), c3 = c(-1, 6, -3, 8, 11)), .Names = c("ID", 
"v1", "v2", "v3", "c1", "c2", "c3"), row.names = c(NA, -5L), class = "data.frame")

To this one :

   ID   v   c T
1   1  -3  -6 1
2   2 -10 -11 1
3   3   4   5 1
4   4  -6   5 1
5   5  -7 -12 1
6   1 -11  -1 2
7   2  -4   4 2
8   3  -4   1 2
9   4   0  -1 2
10  5  12 -11 2
11  1  -2  -1 3
12  2 -12   6 3
13  3  15  -3 3
14  4  -6   8 3
15  5   6  11 3

    output<-structure(list(ID = c(1, 2, 3, 4, 5, 1, 2, 3, 4, 5, 1, 2, 3, 
4, 5), v = c(-3, -10, 4, -6, -7, -11, -4, -4, 0, 12, -2, -12, 
15, -6, 6), c = c(-6, -11, 5, 5, -12, -1, 4, 1, -1, -11, -1, 
6, -3, 8, 11), T = c(1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 
3L, 3L, 3L, 3L, 3L)), .Names = c("ID", "v", "c", "T"), row.names = c(NA, 
-15L), class = "data.frame")

I think I can do this with reshape2 OR with tidyr. Do you know how how I can do this effectively ?

thanks

Vincent Guyader
  • 2,927
  • 1
  • 26
  • 43

3 Answers3

4

Another one:

reshape(input, idvar = "ID", varying = list(2:4, 5:7), direction = "long", timevar = "T", v.names = c("v", "c"))
#     ID T   v   c
# 1.1  1 1  -3  -6
# 2.1  2 1 -10 -11
# 3.1  3 1   4   5
# 4.1  4 1  -6   5
# 5.1  5 1  -7 -12
# 1.2  1 2 -11  -1
# 2.2  2 2  -4   4
# 3.2  3 2  -4   1
# 4.2  4 2   0  -1
# 5.2  5 2  12 -11
# 1.3  1 3  -2  -1
# 2.3  2 3 -12   6
# 3.3  3 3  15  -3
# 4.3  4 3  -6   8
lukeA
  • 53,097
  • 5
  • 97
  • 100
3

With the data.table package you can reshape to multiple columns by using patterns:

library(data.table)
melt(setDT(input), 
     measure.vars = patterns('^v','^c'), 
     value.name = c('v','c'), 
     variable.name = 'T')

which gives:

    ID T   v   c
 1:  1 1  -3  -6
 2:  2 1 -10 -11
 3:  3 1   4   5
 4:  4 1  -6   5
 5:  5 1  -7 -12
 6:  1 2 -11  -1
 7:  2 2  -4   4
 8:  3 2  -4   1
 9:  4 2   0  -1
10:  5 2  12 -11
11:  1 3  -2  -1
12:  2 3 -12   6
13:  3 3  15  -3
14:  4 3  -6   8
15:  5 3   6  11
Jaap
  • 81,064
  • 34
  • 182
  • 193
2

Using tidyr:

library(tidyr)
input %>%
  gather(var, val, v1:c3) %>%
  separate(var, c("var", "T"), sep = 1) %>%
  spread(var, val) %>%
  arrange(T)
#   ID T   c   v
#1   1 1  -6  -3
#2   2 1 -11 -10
#3   3 1   5   4
#4   4 1   5  -6
#5   5 1 -12  -7
#6   1 2  -1 -11
#7   2 2   4  -4
#8   3 2   1  -4
#9   4 2  -1   0
#10  5 2 -11  12
#11  1 3  -1  -2
#12  2 3   6 -12
#13  3 3  -3  15
#14  4 3   8  -6
#15  5 3  11   6
mtoto
  • 23,919
  • 4
  • 58
  • 71