Re-organizing a dataframe

Question

I am trying to re-organize my data frame using reshape2 library. Even though convincing, I am not able to get the expected result.

Here is my data.frame on hand.

   > mydata                
       Destination S1 S2 S3 S4
    1           D1 94 87 84 75
    2           D2 94 87 84 75
    3           D3 94 87 84 75
    4           D4 10 95 92 83

Here is what I am expecting.

Source  Destination Value
    S1      D1      94
    S2      D1      87
    S3      D1      84
    S4      D1      75
    S1      D2      94
    S2      D2      87
    S3      D2      84
    S4      D2      75
    S1      D3      94
    S2      D3      87
    S3      D3      84
    S4      D3      75
    S1      D4      10
    S2      D4      95
    S3      D4      92
    S4      D4      83

I tried to transpose the data.frame, using the below:

> mydata.T <- t(mydata[,2:ncol(mydata)])
> 
> mydata.T
S1   94   94   94   10
S2   87   87   87   95
S3   84   84   84   92
S4   75   75   75   83

I tried with melt, but I am not able to get the exact result.

Can someone please help me here?

A5C1D2H2I1M1N2O1R2T1 · Accepted Answer · 2014-03-28T04:24:59.553

What's wrong with using transpose on your data.frame?

If you want to use "reshape2", you can try the following:

mydataLong <- melt(mydata, id.vars="Destination")
mydataLong
#    Destination variable value
# 1           D1       S1    94
# 2           D2       S1    94
# 3           D3       S1    94
# 4           D4       S1    10
# 5           D1       S2    87
# 6           D2       S2    87
# 7           D3       S2    87
# 8           D4       S2    95
# 9           D1       S3    84
# 10          D2       S3    84
# 11          D3       S3    84
# 12          D4       S3    92
# 13          D1       S4    75
# 14          D2       S4    75
# 15          D3       S4    75
# 16          D4       S4    83

In base R, you can try the following for the long output:

cbind(mydata[1], stack(mydata[-1]), row.names = NULL)

If you wanted that compact transposed output, you would do this:

dcast(mydataLong, variable ~ Destination)
#   variable D1 D2 D3 D4
# 1       S1 94 94 94 10
# 2       S2 87 87 87 95
# 3       S3 84 84 84 92
# 4       S4 75 75 75 83

I tried this @Ananda Mahto. But, I want to split the 4*4 matrix data into 16 rows — Raghu, Mar 28 '14 at 04:24
Yes @Ananda Mahto. You are right. After using Melt, I am not checking that and using the variable somewhere. I missed there. Thanks a lot. That worked well for me. — Raghu, Mar 28 '14 at 04:29

IRTFM · Answer 2 · 2014-03-28T04:40:42.433

0

data.frame(Destination=mydata$Destination, Value=as.vector(data.matrix(mydata[-1]) ))

My gues is that this came from a table operation ans should have been:

 as.data.frame(table( something ) )

edited Mar 28 '14 at 04:40

answered Mar 28 '14 at 04:37

IRTFM

258,963
21
364
487

Won't this result in just two columns, not the desired three columns? – A5C1D2H2I1M1N2O1R2T1 Mar 28 '14 at 04:40
Y'ere right. If it had been asymmetric I might have seen it better. `cbind(df[1], stack(...) )` is better. – IRTFM Mar 28 '14 at 04:41
... as I had mentioned :-) – A5C1D2H2I1M1N2O1R2T1 Mar 28 '14 at 04:45

Re-organizing a dataframe

2 Answers2