LC-datascientist LC-datascientist - 3 months ago 16
R Question

How do I transform this matrix format to a matrix for repeated measures analysis in R?

Sorry "this matrix format" is very vague in my question (suggestions to improve my question?). I have a matrix that's like this

x <- data.frame(ID = c('A','B','C','D'), SCORE_YR1 = c(2,2,1,0),
SCORE_YR2 = c(2,3,3,1), SCORE_YR3 = c(0,2,2,5))

x
ID SCORE_YR1 SCORE_YR2 SCORE_YR3
1 A 2 2 0
2 B 2 3 2
3 C 1 3 2
4 D 0 1 5


I would like to transform the matrix format to look like this

y <- data.frame(ID = rep(c('A','B','C','D'),3), YEAR = rep(1:3,each=4),
SCORE = c(x$SCORE_YR1,x$SCORE_YR2,x$SCORE_YR3))

y
ID YEAR SCORE
1 A 1 2
2 B 1 2
3 C 1 1
4 D 1 0
5 A 2 2
6 B 2 3
7 C 2 3
8 D 2 1
9 A 3 0
10 B 3 2
11 C 3 2
12 D 3 5


Is there a function that can easily transform the dataframe like this?

Thanks

Answer

With dplyr and tidyr, you can do:

library(dplyr); library(tidyr)
x %>% 
      gather(YEAR, SCORE, -ID) %>% 
      mutate(YEAR = extract_numeric(YEAR))

#   ID YEAR SCORE
#1   A    1     2
#2   B    1     2
#3   C    1     1
#4   D    1     0
#5   A    2     2
#6   B    2     3
#7   C    2     3
#8   D    2     1
#9   A    3     0
#10  B    3     2
#11  C    3     2
#12  D    3     5

Or use reshape function from base R:

reshape(x, varying = 2:4, sep = "_YR", dir = "long", timevar = "YEAR")[1:3]

#    ID YEAR SCORE
#1.1  A    1     2
#2.1  B    1     2
#3.1  C    1     1
#4.1  D    1     0
#1.2  A    2     2
#2.2  B    2     3
#3.2  C    2     3
#4.2  D    2     1
#1.3  A    3     0
#2.3  B    3     2
#3.3  C    3     2
#4.3  D    3     5
Comments