How can one convert multiple DVs from long to wide? [closed]

Question

Consider the following example long data.frame with two dependent measures, "Score" and "NewVariable", 1 between subjects variable "Prep" (3 levels), 2 within subjects variables "Day" (3 levels) and "Experiment" (2 levels), and a subject identifier "SID".

example <- structure(list(SID = structure(c(1L, 8L, 12L, 13L, 5L, 6L, 1L, 
8L, 12L, 13L, 5L, 6L, 1L, 8L, 12L, 13L, 5L, 6L, 1L, 8L, 12L, 
13L, 5L, 6L, 1L, 8L, 12L, 13L, 5L, 6L, 1L, 8L, 12L, 13L, 5L, 
6L), .Label = c("S1", "S10", "S11", "S12", "S13", "S14", "S15", 
"S2", "S3", "S4", "S5", "S6", "S7", "S8", "S9"), class = "factor"), 
    Prep = structure(c(2L, 2L, 1L, 1L, 3L, 3L, 2L, 2L, 1L, 1L, 
    3L, 3L, 2L, 2L, 1L, 1L, 3L, 3L, 2L, 2L, 1L, 1L, 3L, 3L, 2L, 
    2L, 1L, 1L, 3L, 3L, 2L, 2L, 1L, 1L, 3L, 3L), .Label = c("Group work only", 
    "Lecture only", "No instruction"), class = "factor"), Day = structure(c(1L, 
    1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 
    3L, 3L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 3L, 
    3L, 3L, 3L, 3L, 3L), .Label = c("Day1", "Day2", "Day3"), class = "factor"), 
    Score = c(14, 14, 16, 18, 11, 12, 13, 15, 17, 15, 12, 11, 
    18, 17, 18, 17, 10, 12, 15, 15, 17, 19, 12, 13, 14, 16, 18, 
    16, 13, 12, 19, 18, 19, 18, 11, 13), NewVariable = c(-0.887056411864653, 
    -0.480360621343027, -0.490415963314823, 1.3654758915317, 
    -1.90913204292831, 0.0300532242614742, -1.84822348735206, 
    -0.3813757992351, -1.70572162896999, 1.00321046322335, -0.758813794873949, 
    -0.966033445643038, 0.11876111343571, -1.2312333727132, -0.836123526615442, 
    0.137868615951057, -1.05143917652043, -0.556162009526374, 
    0.112943588135347, 0.519639378656973, 0.509584036685177, 
    2.3654758915317, -0.909132042928306, 1.03005322426147, -0.848223487352064, 
    0.6186242007649, -0.705721628969986, 2.00321046322335, 0.241186205126052, 
    0.0339665543569619, 1.11876111343571, -0.231233372713198, 
    0.163876473384558, 1.13786861595106, -0.0514391765204339, 
    0.443837990473626), Experiment = c(1, 1, 1, 1, 1, 1, 1, 1, 
    1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 
    2, 2, 2, 2, 2, 2, 2, 2, 2)), .Names = c("SID", "Prep", "Day", 
"Score", "NewVariable", "Experiment"), row.names = c("1", "2", 
"6", "7", "13", "14", "16", "17", "21", "22", "28", "29", "31", 
"32", "36", "37", "43", "44", "11", "23", "61", "71", "131", 
"141", "161", "171", "211", "221", "281", "291", "311", "321", 
"361", "371", "431", "441"), class = "data.frame")

My best attempt so far would involve creating the two corresponding wide datasets, e.g.

library(reshape2)
dcast(example,SID+Prep~Day+Experiment,value.var="Score")
dcast(example,SID+Prep~Day+Experiment,value.var="NewVariable")

... then manually gluing them back together.

Is there a better way?

Answer 1

When using reshape(), you can reshape multiple dependent variables at once, thus keeping all the data in one data frame. However, you have to work in two steps for two within factors: One transformation for each within variable.

While transforming for factor Day in step 1, Experiment has to be listed as a between factor for argument idvar. This is because, just like the SID variable and the between factor Prep, Experiment values vary within one level of Day in the resulting data frame.

# data frame "example" as defined in the question
# long -> wide for factor "Day"
> dfW1 <- reshape(example, v.names=c("Score", "NewVariable"), timevar="Day",
                  idvar=c("SID", "Prep", "Experiment"), direction="wide"))

# show part of the created data frame
> dfW1[5:8 , c("SID", "Prep", "Experiment", "Score.Day1", "NewVariable.Day1")]
   SID            Prep Experiment Score.Day1 NewVariable.Day1
13 S13  No instruction          1         11      -1.90913204
14 S14  No instruction          1         12       0.03005322
11  S1    Lecture only          2         15       0.11294359
23  S2    Lecture only          2         15       0.51963938

The second step transforms for factor Experiment. The variables created in step 1 (Score for each day and NewVariable for each day) have to be listed for v.names. Variables SID and Prep remain for argument idvar as their values are not constant for one level of Experiment.

# long -> wide for factor "Experiment"
> dfW2 <- reshape(dfW1, v.names=c("Score.Day1", "Score.Day2", "Score.Day3",
                        "NewVariable.Day1", "NewVariable.Day2", "NewVariable.Day3"),
                  timevar="Experiment", idvar=c("SID", "Prep"), direction="wide")

# show part of the created data frame
> dfW2[1:4, c("SID", "Prep", "Score.Day1.2", "NewVariable.Day1.2")]
   SID            Prep Score.Day1.2 NewVariable.Day1.2
1   S1    Lecture only           15          0.1129436
2   S2    Lecture only           15          0.5196394
6   S6 Group work only           17          0.5095840
7   S7 Group work only           19          2.3654759