[R] Reorganize(stack data) a dataframe inducing names

[David Winsemius]

On Jul 27, 2011, at 4:28 AM, Francesca wrote:

> Dear Contributors,
> thanks for collaboration.
> I am trying to reorganize data frame, that looks like this:
>
>     n1.Index   Date        PX_LAST    n2.Index   Date.1     PX_LAST.1
> n3.Index       Date.2             PX_LAST.2
> 1     NA        04/02/07    1.34      NA              04/02/07       
> 1.36
>       NA              04/02/07      1.33
> 2     NA        04/09/07    1.34      NA              04/09/07
> 1.36           NA              04/09/07      1.33
> 3     NA         04/16/07    1.34      NA             04/16/07       
> 1.36
>      NA              04/16/07      1.33
> 4     NA         04/30/07    1.36      NA             04/30/07
> 1.40           NA              04/30/07      1.37
> 5     NA        05/07/07    1.36      NA              05/07/07
> 1.40           NA              05/07/07      1.37
> 6     NA         05/14/07    1.36      NA             05/14/07       
> 1.40
>      NA              05/14/07      1.37
> 7     NA         05/22/07    1.36      NA             05/22/07       
> 1.40
>      NA              05/22/07      1.37
>
>
> While what I would like to obtain is:
> I would like to obtain stacked data as:
>
> n1.Index       Date        PX_LAST
> n1.Index    04/02/07    1.34
> n1.Index    04/09/07    1.34
> n1.Index     04/16/07    1.34
> n1.Index     04/30/07    1.36
> n1.Index    05/07/07    1.36
> n1.Index     05/14/07    1.36
> n1.Index     05/22/07    1.36
> n2.Index      04/02/07    1.36
> n2.Index     04/16/07    1.36
> n2.Index     04/16/07    1.36
> n2.Index     04/30/07    1.40
> n2.Index     05/07/07    1.40
> n2.Index     05/14/07    1.40
> n2.Index     05/22/07    1.40
> n3.Index     04/02/07    1.33
> n3.Index     04/16/07    1.33
> n3.Index     04/16/07    1.33
> n3.Index     04/30/07    1.37
>
> I have tried the function stack, but it uses only one argument. Then I
> have tested the melt function from the package reshape, but it
> seems not to be reproducing the correct organization of the data, as
> it takes date as the id values.
> PS: the n1 index names are not ordered in the original database, so
> I cannot fill in the NA with the names using a recursive formula.
> Thank you for any help you can provide.

(only on the last point, since you already have been offered a  
solution ...) You should read more rhelp questions and answers. This  
thread yesterday had three different ways that you could have replaced  
the values of those *.Index columns with their names:

[R] Recoding Multiple Variables in a Data Frame in One Step

Ehlers liked Dunlap's solution, but I thought those two were equally  
clever. Mine was clearly not the best.

> Francesca
>
> -- 
> Francesca
>
> ----------------------------------
> Francesca Pancotto, PhD
> Dipartimento di Economia
> Università di Bologna
> Piazza Scaravilli, 2
> 40126 Bologna
> Office: +39 051 2098135
> Cell: +39 393 6019138
> Web: http://www2.dse.unibo.it/francesca.pancotto/
> ----------------------------------
>
> 	[[alternative HTML version deleted]]
>
> ______________________________________________
> 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