reshape / restructure the data frame in R

Question

I'm cleaning a dataset, but the frame is not ideal, I have to reshape it, but I don't know how. The following are the original data frame:

Rater   Rater ID    Ratee1  Ratee2  Ratee3  Ratee1.item1    Ratee1.item2    Ratee2.item1    Ratee2.item2    Ratee3.item1    Ratee3.item2
A       12          701     702     800     1               2               3               4               5               6
B       23          45      46      49      3               3               3               3               3               3
C       24          80      81      28      2               3               4               5               6               9

Then I am wondering how to reshape it as the below:

Rater   Rater ID    Ratee   item1   item2
A       12          701     1       2
A       12          702     3       4
A       12          800     5       6
B       23          45      3       3
B       23          46      3       3
B       23          49      3       3
C       24          80      2       3
C       24          81      4       5
C       24          28      6       9

This reshaping is a little bit different from this one (Reshaping data.frame from wide to long format). As I have three parts in the original data.

First part is about the rater's ID (Rater and Rater ID).
The second is about retee's ID (Ratee1, Ratee2, Ratee3).
The Third part is about Rater's rating on each retee (retee*.item1(or2)).

To make it more clear, let me brief the data collecting process.

First, a rater types in his own name and ID,
then nominates three persons (Ratee1 to Ratee3),
and then rates the questions regarding each retee (for each retee, there are two questions).

Does anyone know how to reshape this? Thanks!

@Jaap It is not an exact dupe if you read the question and the expected output — akrun, Feb 06 '17 at 05:59
both questions are about rehaping multiple sets of columns according to a pattern — Jaap, Feb 06 '17 at 07:11

score -1 · Answer 1 · answered Feb 06 '17 at 05:53

We can use melt from data.table

library(data.table)
melt(setDT(df1), measure = patterns("^Ratee\\d+$", "^Ratee\\d+\\.item1", 
    "^Ratee\\d+\\.item2"), value.name = c("Ratee", "item1", "item2"))[, 
       variable := NULL][order(Rater)]
#    Rater RaterID Ratee item1 item2
#1:     A      12   701     1     2
#2:     A      12   702     3     4
#3:     A      12   800     5     6
#4:     B      23    45     3     3
#5:     B      23    46     3     3
#6:     B      23    49     3     3
#7:     C      24    80     2     3
#8:     C      24    81     4     5
#9:     C      24    28     6     9

reshape / restructure the data frame in R

1 Answers1