reshape2 - melt - id.vars merged into 1 column

Question

I have this data.frame organized like this:

   Time.A         Z.A        Y.A    Time.B        Z.B         Y.B
1       1 -0.10612452  0.6359504  1.414806 -0.4304691 -1.71700868
2       2  1.51152200 -0.2842529  2.437075 -0.2572694 -0.78445901
3       3 -0.09465904 -2.6564554  2.786140 -1.7631631 -0.85090759
4       4  2.01842371 -2.4404669  4.330448  0.4600974 -2.41420765
5       5 -0.06271410  1.3201133  5.141746 -0.6399949  0.03612261
6       6  1.30486965 -0.3066386  6.019096  0.4554501  0.20599860
7       7  2.28664539 -1.7813084  7.236588  0.7048373 -0.36105730
8       8 -1.38886070 -0.1719174  7.634667  1.0351035  0.75816324
9       9 -0.27878877  1.2146747  9.156992 -0.6089264 -0.72670483
10     10 -0.13332134  1.8951935 10.205065  0.5049551 -1.36828104

Where the variable names have the name of the group they belong to. I want to melt this data.frame in a way that I have 1 column with time and then the respective values for each Group (A and B in this example) and for each variable (Z and Y). My desired output should look something like this:

Time   Group   variable value
1        A        Z      -0.10612452
1        A        Y       0.6359504
1.41     B        Z      -0.4304691
1.41     B        Y      -1.71700868

I have managed to partially melt it, but i am stuck at making the time column unique. As you see, the time values are not exactly the same. Is there a straightforward way of solving this?

My code so far:

library(plyr)
library(reshape2)

# generating the data.frame
set.seed(42)

Time.A <- 1:10
Time.B <- 1:10+runif(10, -0.5, 0.5)

Z.A <- rnorm(10)
Y.A <- rnorm(10)
Z.B <- rnorm(10)
Y.B <- rnorm(10)

MyData <- data.frame(Time.A, Z.A, Y.A, Time.B, Z.B, Y.B)


#Tried so far
Time.indexes <- grep(pattern="Time", x=names(MyData))
MeltedData <- melt(MyData, id.vars=Time.indexes)
New.Vars <- ldply(strsplit(as.character(MeltedData$variable), split="[.]"))
names(New.Vars) <- c("variable", "Group")

MeltedData <- cbind(MeltedData[-3], New.Vars)

The result is

   Time.A    Time.B       value variable Group
1       1  1.414806 -0.10612452        Z     A
2       2  2.437075  1.51152200        Z     A
3       3  2.786140 -0.09465904        Z     A
4       4  4.330448  2.01842371        Z     A
5       5  5.141746 -0.06271410        Z     A
6       6  6.019096  1.30486965        Z     A
...

Answer 1

You can bring together base R's reshape and melt from "reshape2" and have them work together instead of competing for attention as they normally do. To do so, you'll need to first add an "ID" variable (that is, if I understood your desired output correctly).

With your sample data, here's the approach I took:

MyData$ID <- sequence(nrow(MyData))
out <- melt(reshape(
  MyData, direction = "long", idvar="ID", timevar = "Group",
  varying = setdiff(names(MyData), "ID"), sep = "."),
            id.vars = c("ID", "Group", "Time"))

And here's what it looks like. You'll have to reorder the result if you are looking for the specific order you described.

head(out)
#   ID Group Time variable       value
# 1  1     A    1        Z -0.10612452
# 2  2     A    2        Z  1.51152200
# 3  3     A    3        Z -0.09465904
# 4  4     A    4        Z  2.01842371
# 5  5     A    5        Z -0.06271410
# 6  6     A    6        Z  1.30486965
tail(out)
#    ID Group      Time variable       value
# 35  5     B  5.141746        Y  0.03612261
# 36  6     B  6.019096        Y  0.20599860
# 37  7     B  7.236588        Y -0.36105730
# 38  8     B  7.634667        Y  0.75816324
# 39  9     B  9.156992        Y -0.72670483
# 40 10     B 10.205065        Y -1.36828104