R: How to melt data in R where row values become the column labels?

Question

I have this data

   RES  RANK1   RANK2   RANK3   RANK4   RANK5
    1   3   3   5   16  1
    2   3   1   2   5   16
    3   3   5   15  10  1

how do i melt the data such that values of RANK1 to RANK5 becomes the variable and RANK1 to RANK5 becomes the value.

The data were gathered by: among the list of (22) items, select the top 5 preferences.

The new data would be analyze as if the answers are multi response.

expected output would be (eg.RES=1)

RES 1      2      3        5       10       15     16
1   RANK5         RANK2    RANK3                   RANK4
2   RANK2  RANK3  RANK1    RANK4                   RANK5
3   RANK5         RANK1    RANK2   RANK4    RANK3

Thanks!

Not sure I understand the question... could you provide an example of the output that you would like to have? — nico, Jan 06 '14 at 09:44
Hi Nico, I editted the question for you to see my expected output. thanks! — dixi, Jan 06 '14 at 09:54

Simon O'Hanlon · Accepted Answer · 2014-01-06T10:28:01.797

3

You will neeed to melt your data and then dcast it. Because you don't have unique values for each combination you need some way to keep all values when you have more than one (as happens here for RES == 1 and VALUE == 3). In this case we just concatenate the values using paste with a collapse argument:

dcast( melt( df , measure = 2:6 ) , RES ~ value , value.var = "variable" , fun = function(x) paste(x,collapse=";") , fill = ""   )
#  RES     1     2           3     5    10    15    16
#1   1 RANK5       RANK1;RANK2 RANK3             RANK4
#2   2 RANK2 RANK3       RANK1 RANK4             RANK5
#3   3 RANK5             RANK1 RANK2 RANK4 RANK3

The effect of melt using the RANK* columns as measure variables is to give us a long dataset with two columns of id variables like so

head( melt( df , measure = 2:6 ) )
#  RES variable value
#1   1    RANK1     3
#2   2    RANK1     3
#3   3    RANK1     3
#4   1    RANK2     3
#5   2    RANK2     1
#6   3    RANK2     5

edited Jan 06 '14 at 10:28

answered Jan 06 '14 at 09:53

Simon O'Hanlon

58,647
14
142
184

Hi Simon, yes, but its not what i need. I editted my questions for you to see my expected output, Thanks for your help! – dixi Jan 06 '14 at 09:54
@dixi yes it is... hold on. – Simon O'Hanlon Jan 06 '14 at 10:07
@dixi for value=3 and res=1 you have both rank1 and rank2. How do you decide between them? e.g. you put rank2 in your example. – Simon O'Hanlon Jan 06 '14 at 10:21
i see. probably its just a mistake with the (data) coding, so either rank1 or rank2 is ok.. if its possible to have both rank1,rank2 as the value, it is better. – dixi Jan 06 '14 at 10:25
Thank you Simon! This is what Im looking for. :-D – dixi Jan 06 '14 at 10:35

R: How to melt data in R where row values become the column labels?

1 Answers1