How do I use the reshape() function more than once successfully in R?

Question

This is my dataframe:

   ID Group x1  x2  x3  y1  y2  y3  z1  z2  z3
    144 1   566 613 597 563 549 562 599 82  469
    167 2   697 638 756 682 695 693 718 82  439.5
    247 4   643 698 730 669 656 669 698 82  514.5
    317 4   633 646 641 520 543 586 559 82  405.5
    344 3   651 678 708 589 608 615 667 82  514
    352 2   578 702 671 536 594 579 591 82  467.5
    382 1   678 690 693 555 565 534 521 82  457.5
    447 3   668 672 718 663 689 751 784 82  506.5
    464 2   760 704 763 514 554 520 564 82  486
    628 1   762 789 783 618 610 645 625 82  536

I have several repeated measures in wide format which I would like to reshape into long format. I wasn't sure how to reshape all the three (x,y,z) respeated variables at once, so I opted for trying one after the other. So I could successfully reshape variable x:

reshaped.df <- reshape(df, 
                       idvar="ID", 
                       varying= c("x.1", "x.2", "x.3"),
                       timevar="Timex",
                       v.names= "X", 
                       times=c("Part1", "Part2", "Part3"),
                       direction="long")

When I then try to use the same reshape method on the new reshaped dataframe to melt the next variable, it doesn't work anymore. So I try to run this:

reshaped.df <- reshape(reshaped.df, 
                       idvar="ID", 
                       varying= list( c("y.1", "y.2", "y.3")),
                       timevar="Timey",
                       v.names= "Y", 
                       times=c("P1", "P2", "P3"),
                       direction="long")

And I get the following error and warning message:

Error in `row.names<-.data.frame`(`*tmp*`, value = paste(d[, idvar], times[1L],  : 
  duplicate 'row.names' are not allowed
In addition: Warning message:
non-unique values when setting 'row.names': ‘144.Part1’, ‘167.Part1’, ‘247.Part1’, ‘317.Part1’, ‘344.Part1’, ‘352.Part1’, ‘382.Part1’,  ... <truncated>

Is there another way to do this efficiently?

Is there a specific reason you didn't try something like `melt`? I.e.: `melt(df, id.vars = c("ID","Group"))` — Mike H., May 08 '17 at 14:21
Hi Mike, if I try to simply `melt`, all my x,y and z values are all in one huge column. I would at least like to keep one column for x, one for y and one for z... How would you specify this with the simple `melt` function? — Inkling, May 08 '17 at 15:18

score 1 · Answer 1 · answered May 08 '17 at 14:26

You can try data.table::melt, which can melt the three measurement groups simultaneously:

library(data.table)
df <- fread('ID Group x1  x2  x3  y1  y2  y3  z1  z2  z3
    144 1   566 613 597 563 549 562 599 82  469
    167 2   697 638 756 682 695 693 718 82  439.5
    247 4   643 698 730 669 656 669 698 82  514.5
    317 4   633 646 641 520 543 586 559 82  405.5
    344 3   651 678 708 589 608 615 667 82  514
    352 2   578 702 671 536 594 579 591 82  467.5
    382 1   678 690 693 555 565 534 521 82  457.5
    447 3   668 672 718 663 689 751 784 82  506.5
    464 2   760 704 763 514 554 520 564 82  486
    628 1   762 789 783 618 610 645 625 82  536')

melt(df, id = 1:2, measure.vars = patterns('^x', '^y', '^z'),
     variable.name = 'repeat', value.name = c('x', 'y', 'z'))
#      ID Group repeat   x   y     z
#  1: 144     1      1 566 563 599.0
#  2: 167     2      1 697 682 718.0
#  3: 247     4      1 643 669 698.0
#  4: 317     4      1 633 520 559.0
#  5: 344     3      1 651 589 667.0
#  6: 352     2      1 578 536 591.0
#  7: 382     1      1 678 555 521.0
#  8: 447     3      1 668 663 784.0
#  9: 464     2      1 760 514 564.0
# 10: 628     1      1 762 618 625.0
# 11: 144     1      2 613 549  82.0
# 12: 167     2      2 638 695  82.0
# ...

Hi there! Thanks for the suggestion. I however don't quite understand the `measure.vars = patterns('^x', '^y', '^z')` part. My actual variable names are a bit more complicated than x,y and z. They are more like: MedianRTPosTrialsPart1, MedianRTPosTrialsPart2 and MedianRTPosTrialsPart3. I tried using this: `measure.vars = patterns('^MedianRTPosTrialsPart')` but that doesn't work: `Error: measure variables not found in data: ^MedianRTPostrialsPart`. Any more tips? — Inkling, May 08 '17 at 15:28
I am not sure how you data looks like. But You can check `?data.table::melt` to see how to specify `measure.var` with `patterns` or a list of colnames and the examples below. If you have still have problem, you can show me a short sample of you real data by editing the original POST and let me help you or you can ask a new question. — mt1022, May 08 '17 at 15:46

score 1 · Answer 2 · answered May 08 '17 at 15:28

I would do something like this using reshape:

vars <- names(df)[grepl("(x|y|z)",names(df))]

res <- reshape(df, varying=vars, v.names = c("x","y","z"), direction = "long")

head(res)
#     ID Group time   x   y   z id
#1.1 144     1    1 566 613 597  1
#2.1 167     2    1 697 638 756  2
#3.1 247     4    1 643 698 730  3
#4.1 317     4    1 633 646 641  4
#5.1 344     3    1 651 678 708  5
#6.1 352     2    1 578 702 671  6

How do I use the reshape() function more than once successfully in R?

2 Answers2

Linked