This question already has an answer here:
I need to reshape my wide table into long format but keeping multiple fields for each record, for example:
dw <- read.table(header=T, text='
sbj f1.avg f1.sd f2.avg f2.sd blabla
A 10 6 50 10 bA
B 12 5 70 11 bB
C 20 7 20 8 bC
D 22 8 22 9 bD
')
# Now I want to melt this table, keeping both AVG and SD as separate fields for each measurement, to get something like this:
# sbj var avg sd blabla
# A f1 10 6 bA
# A f2 50 10 bA
# B f1 12 5 bB
# B f2 70 11 bB
# C f1 20 7 bC
# C f2 20 8 bC
# D f1 22 8 bD
# D f2 22 9 bD
I have basic knowledge of using melt
and reshape
, but it is not obvious for me how to apply such reshaping in my case.
I would be grateful for any hints or point to another SO post if something similar have been asked already.
This seems to do what you want except that the
f
is removed from elements intime
.To add to the options available here, you can also consider
merged.stack
from my "splitstackshape" package:You can also do a little more cleanup on the
".time_1"
variable, like this.You would note the use of the
atStart = FALSE
argument. This is because your names are in a little bit of a different order than reshape-related functions seem to like. In general, the "stub" is expected to come first, and then the "times", like this:If the names were in that format, then both base R's
reshape
andmerged.stack
benefit from more direct syntax:Another option using Hadley's new
tidyr
package.melt
from the >=1.9.6 version ofdata.table
, does this by specifying the column index inmeasure.vars
as alist
.Or you could use the new
patterns
function:reshape
does this with the appropriate arguments.varying
lists the columns which exist in the wide format, but are split into multiple rows in the long format.v.names
is the long format equivalents. Between the two, a mapping is created.From
?reshape
:Given these
varying
andv.names
arguments,reshape
is smart enough to see that I've specified that the index is before the dot here (i.e., order 1.x, 1.y, 2.x, 2.y). Note that the original data has the columns in this order, so we can specifyvarying=2:5
for this example data, but that is not safe in general.Given the values of
times
andv.names
,reshape
splits thevarying
columns on a.
character (the defaultsep
argument) to create the columns in the output.times
specifies values that are to be used in the createdvar
column, andv.names
are pasted onto these values to get column names in the wide format for mapping to the result.Finally,
idvar
is specified to be thesbj
column, which identifies individual records in the wide format (thanks @thelatemail).