How can I use the reshape () function more than once successfully in R?
This is my data file:
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 some wide format re-measures that I would like to reformat into long format. I was not sure how to change all three (x, y, z) repeating variables at once, so I decided to try one by one. So I could successfully change the x variable:
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 change method on the new changed dataframe to melt the next variable, it no longer works. So I'm trying 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 am getting 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 any other way to do this efficiently?
+3
source to share
2 answers
You can try data.table::melt
, which can melt three groups of dimensions at the same time:
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
# ...
+1
source to share
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
+1
source to share