[R] Wide to long form conversion

[David Winsemius]

On Oct 7, 2011, at 7:40 AM, Gang Chen wrote:

> Jim, I really appreciate your help!
>
> I like the power of rep_n_stack, but how can I use rep_n_stack to get
> the following result?
>
>  Subj Group value Ref Var Time
> 1    S1     s     4  Me   F    1
> 2    S1     s     3  Me   F    2
> 3    S1     s     5  Me   J    1
> 4    S1     s     6  Me   J    2
> 5    S1     s     6 She   F    1
> 6    S1     s     6 She   F    2
> 7    S1     s    10 She   J    1
> 8    S1     s     9 She   J    2

I was not able to construct a one step solution with `reshape` that  
will contains all the columns. You can do it in about 4 steps by first  
making the data "long" and then adding annotation columns. Using just  
rows 1 and 26 you might get:

reshape(myData[c(1,26), ], idvar=c("Group","Subj"),
        direction="long",
        varying=2:9,
        v.names=c("value") )
         Group Subj time value
s.S1.1      s   S1    1      4
w.S26.1     w  S26    1      5
s.S1.2      s   S1    2      5
w.S26.2     w  S26    2      9
s.S1.3      s   S1    3      6
w.S26.3     w  S26    3      4
s.S1.4      s   S1    4     10
w.S26.4     w  S26    4      7
s.S1.5      s   S1    5      3
w.S26.5     w  S26    5      3
s.S1.6      s   S1    6      6
w.S26.6     w  S26    6      7
s.S1.7      s   S1    7      6
w.S26.7     w  S26    7      3
s.S1.8      s   S1    8      9
w.S26.8     w  S26    8      5

The 'time' variable is not really what you wanted but refers to the  
sequence along the original wide column names
You can add the desired  Ref, Var and Time columms with these  
constructions:

 > str(times<-rep(c(1,2), length=nrow(myData)*8 )  )
  num [1:408] 1 2 1 2 1 2 1 2 1 2 ...
 > str(times<-rep(c("F","J"), each=2, length=nrow(myData)*8 )  )
  chr [1:408] "F" "F" "J" "J" "F" "F" "J" "J" "F" "F" ...
 > str(times<-rep(c("Me","She"), each=4, length=nrow(myData)*8 )  )
  chr [1:408] "Me" "Me" "Me" "Me" "She" "She" "She" "She" ...

longData <- reshape(myData, idvar=c("Group","Subj"),
        ction="long",
        varying=2:9,
        v.names=c("value") )
longData$Time <- rep(c(1,2), length=nrow(myData)*8 )
longData$Var <- rep(c("F","J"), each=2, length=nrow(myData)*8 )
longData$Ref <- rep(c("Me","She"), each=4, length=nrow(myData)*8 )
longData <- longData[order(longData$Subj), ]

Looking at Jim Lemon's response, I think he just misinterpreted the  
structure of your data but gave you a perfectly usable response. You  
could have done much the same thing with a minor modification:

 > str(rep_n_stack(myData,matrix(c(2,3,6,7,4,5,8,9),nrow=1,byrow=TRUE)))
'data.frame':	408 obs. of  4 variables:
  $ Group : Factor w/ 2 levels "s","w": 1 1 1 1 1 1 1 1 1 1 ...
  $ Subj  : Factor w/ 51 levels "S1","S10","S11",..: 1 12 23 34 45 48  
49 50 51 2 ...
  $ group1: Factor w/ 8 levels "Me.F.1","Me.F.2",..: 1 1 1 1 1 1 1 1 1  
1 ...
  $ value1: int  4 6 7 8 10 5 13 8 6 14 ...

Now you can just split apart the 'group1' column with sub() to make  
the three specified columns.
-- 
David.

>
> On Fri, Oct 7, 2011 at 7:16 AM, Jim Lemon <jim at bitwrit.com.au> wrote:
>> On 10/07/2011 07:28 AM, Gang Chen wrote:
>>>
>>> I have some data 'myData' in wide form (attached at the end), and
>>> would like to convert it to long form. I wish to have five variables
>>> in the result:
>>>
>>> 1) Subj: factor
>>> 2) Group: between-subjects factor (2 levels: s / w)
>>> 3) Reference: within-subject factor (2 levels: Me / She)
>>> 4) F: within-subject factor (2 levels: F1 / F2)
>>> 5) J: within-subject factor (2 levels: J1 / J2)
>>
>> Hi Gang,
>> I don't know whether this is the format you want, but:
>>
>> library(prettyR)
>> rep_n_stack(mydata,matrix(c(2,3,6,7,4,5,8,9),nrow=2,byrow=TRUE))
>>
>> Jim
>>
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.

David Winsemius, MD
West Hartford, CT