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

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?

-

## closed as off topic by Andy W, Macro, whuber♦Jan 15 '13 at 14:35

Questions on Cross Validated are expected to relate to statistics within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

Are you sure the example data is correctly set up? Each SID has 6 entries, not 3. It seems Experiment is currently a within-factor as well. – caracal Jan 15 '13 at 7:11
The issues you mentioned were present. I think I have resolved them in the new sample dataset. Experiment was meant to be within, I misspecified it in the initial posting. – rpierce Jan 15 '13 at 7:51
This is off topic here and I'm pretty sure it has been duplicated on SO approximately 100 times. – whuber Jan 15 '13 at 14:34
In the early days, things were still being figured out. For a more recent thread, please see meta.stats.stackexchange.com/questions/1335/…. It quotes our faq: "if it needs statistical expertise to understand or answer, ask it here; if it's about an algorithm, routine data processing, or details of the language, then please refer to the collection of links to resources we maintain." I would be happy to cast a reopen vote here if you could point out where statistical expertise may be needed. Usually if r is the only tag, it's off topic. – whuber Jan 15 '13 at 19:15
@whuber: I've moved the discussion to meta. It seems more appropriate there. – rpierce Jan 21 '13 at 6:10

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

-
The reshape2 approach seems to be: dcast(melt(example,id.vars=c("SID","Prep","Experiment","Day")),SID+Prep~Experime‌​‌​nt+Day+variable) – rpierce Jan 15 '13 at 22:40