# 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?

• 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. Commented Jan 15, 2013 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. Commented Jan 15, 2013 at 7:51
• This is off topic here and I'm pretty sure it has been duplicated on SO approximately 100 times.
– whuber
Commented Jan 15, 2013 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
Commented Jan 15, 2013 at 19:15
• @whuber: I've moved the discussion to meta. It seems more appropriate there. Commented Jan 21, 2013 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) Commented Jan 15, 2013 at 22:40