R code - restructure data, three columns of stacked data to rows

Question

In this example data, three people sorted 10 items into a variable number of groups and provided a text label for each group. Person and label are text fields. Item var when downloaded and read into R is read as an integer. Item variable is actually categorical data and defines text for the item; an item number for a test is a good analogy. Labels, items and persons can be in any order, I typically sort the data as you see here to make visual inspection possible. Each person has 10 items in this example, in the real world there are usually 100 items. Each person has a variable number of labels. Each label has a variable number of items. All items are associated with one and only one label and all items (1-10 in this example) appear once for each person, there is no missing data.

person group item

person_1 label_A 1

person_1 label_A 2

person_1 label_A 3

person_1 label_A 4

person_1 label_B 5

person_1 label_B 6

person_1 label_C 7

person_1 label_C 8

person_1 label_C 9

person_1 label_C 10

person_2 label_D 1

person_2 label_D 2

person_2 label_D 3

person_2 label_D 4

... remaining lines omitted for brevity

I need the data restructured into the format that follows. Each line is a label variable with the associated items, labels are on one and only one line. Each person is repeated for as many times as they have unique labels. I have searched stack overflow and have made multiple attempts with reshape and tidyr, the best I can produce is a rectangular binary matrix where there are ones or zeros in the data frame with a column for person and label and then 10 columns labeled 1:10 for each item value in this example. I can post-process to get what i want in excel but would rather get it all done in R, I need the actual item value in the column as shown here. Ideally, the max ncol would be one each for peson & label and as many as needed to represent the sorting. Person3,label_H needed 7 col for items so there could be NA or 0 in those column or other rows. Any help would be most appreciated, I can normally find the answer I need on StackOverflow, this time I am stumped.

person group items

person_1 label_A 1 2 3 4
person_1 label_B 5 6
person_1 label_C 7 8 9 10
person_2 label_D 1 2 3 4
person_2 label_E 5 6 7
person_2 label_F 8 9 10
person_3 label_G 1 2 3
person_3 label_H 4 5 6 7 8 9 10

Have you tried anything? – Lev Kuznetsov Apr 28 '16 at 16:36 — Lev Kuznetsov, Apr 28 '16 at 16:36
Please show what you've tried. – Heroka Apr 28 '16 at 16:47 — Heroka, Apr 28 '16 at 16:47

score 0 · Answer 1 · answered Apr 28 '16 at 17:48

I am not sure if I completly understood the question. Eventually you are looking for something like this:

df <- read.table(header=TRUE, text=
"person group item
person_1 label_A 1
person_1 label_A 2
person_1 label_A 3
person_1 label_A 4
person_1 label_B 5
person_1 label_B 6
person_1 label_C 7
person_1 label_C 8
person_1 label_C 9
person_1 label_C 10
person_2 label_D 1
person_2 label_D 2
person_2 label_D 3
person_2 label_D 4")
df2 <- unique(df[-3])
split(df$item, list(df$person, df$group), drop=TRUE)
df2$L <- split(df$item, list(df$person, df$group), drop=TRUE)
df2

result:

#> df2
#     person   group           L
#1  person_1 label_A  1, 2, 3, 4
#5  person_1 label_B        5, 6
#7  person_1 label_C 7, 8, 9, 10
#11 person_2 label_D  1, 2, 3, 4

R code - restructure data, three columns of stacked data to rows

1 Answers1