[R] Help with reshaping data.frame

[Gabor Grothendieck]

Try this:

> tst2 <- tst[-(2:3)]
> tst2$timevar <- with(tst, interaction(K2, K3))
> reshape(tst2, dir = "wide", idvar = "K1", timevar = "timevar")
  K1 V1.D.a V2.D.a V3.D.a V1.E.a V2.E.a V3.E.a V1.D.b V2.D.b V3.D.b
V1.E.b V2.E.b V3.E.b
1 10   0.08     99    105     NA     NA     NA     NA     76    775
0.11     85    532
2 20   0.00     79    522   0.08     73    251   0.26     84    372
 NA     NA     NA
3 30   0.31     70    989     NA     38    323   0.24     51    680
0.07     20    364


On Mon, Aug 3, 2009 at 5:23 PM, <davidr at rhotrading.com> wrote:
> I'm having trouble reshaping a data.frame from long to wide.
> (I think that's the right terminology; feel free to educate me.)
> I've looked at the reshape function and package and plyr package,
> but I can't quite figure out how to do this after a dozen variations.
>
> I have a data.frame with more levels than this, but similar to:
>
>> tst
>   K1 K2 K3   V1 V2  V3
> 1  10  D  a 0.08 99 105
> 2  20  D  a 0.00 79 522
> 3  30  D  a 0.31 70 989
> 5  20  E  a 0.08 73 251
> 6  30  E  a   NA 38 323
> 7  10  D  b   NA 76 775
> 8  20  D  b 0.26 84 372
> 9  30  D  b 0.24 51 680
> 10 10  E  b 0.11 85 532
> 12 30  E  b 0.07 20 364
>> dput(tst)
> structure(list(K1 = c(10, 20, 30, 20, 30, 10, 20, 30, 10, 30),
>    K2 = structure(c(1L, 1L, 1L, 2L, 2L, 1L, 1L, 1L, 2L, 2L), .Label =
> c("D",
>    "E"), class = "factor"), K3 = structure(c(1L, 1L, 1L, 1L,
>    1L, 2L, 2L, 2L, 2L, 2L), .Label = c("a", "b"), class = "factor"),
>    V1 = c(0.08, 0, 0.31, 0.08, NA, NA, 0.26, 0.24, 0.11, 0.07
>    ), V2 = c(99, 79, 70, 73, 38, 76, 84, 51, 85, 20), V3 = c(105,
>    522, 989, 251, 323, 775, 372, 680, 532, 364)), .Names = c("K1",
> "K2", "K3", "V1", "V2", "V3"), row.names = c(1L, 2L, 3L, 5L,
> 6L, 7L, 8L, 9L, 10L, 12L), class = "data.frame")
>> str(tst)
> 'data.frame':   10 obs. of  6 variables:
>  $ K1: num  10 20 30 20 30 10 20 30 10 30
>  $ K2: Factor w/ 2 levels "D","E": 1 1 1 2 2 1 1 1 2 2
>  $ K3: Factor w/ 2 levels "a","b": 1 1 1 1 1 2 2 2 2 2
>  $ V1: num  0.08 0 0.31 0.08 NA NA 0.26 0.24 0.11 0.07
>  $ V2: num  99 79 70 73 38 76 84 51 85 20
>  $ V3: num  105 522 989 251 323 775 372 680 532 364
>
> I want a data.frame with columns K1, D.a.V1, D.a.V2, D.a.V3, D.b.V1,
> D.b.V2, ..., E.b.V3,
> with NA's where there is missing data.
>
> Any direction would be appreciated. sessionInfo() below.
>
> Thanks,
> David L. Reiner
> Rho Trading Securities, LLC
>
>
>> sessionInfo()
> R version 2.9.1 (2009-06-26)
> i386-pc-mingw32
>
> locale:
> LC_COLLATE=English_United States.1252;LC_CTYPE=English_United
> States.1252;LC_MONETARY=English_United
> States.1252;LC_NUMERIC=C;LC_TIME=English_United States.1252
>
> attached base packages:
> [1] stats     graphics  grDevices utils     datasets  methods   base
>
>
> other attached packages:
> [1] reshape_0.8.3 plyr_0.1.9
>
> loaded via a namespace (and not attached):
> [1] tools_2.9.1
>
>
>
> This e-mail and any materials attached hereto, including, without limitation, all content hereof and thereof (collectively, "Rho Content") are confidential and proprietary to Rho Trading Securities, LLC ("Rho") and/or its affiliates, and are protected by intellectual property laws.  Without the prior written consent of Rho, the Rho Content may not (i) be disclosed to any third party or (ii) be reproduced or otherwise used by anyone other than current employees of Rho or its affiliates, on behalf of Rho or its affiliates.
>
> THE RHO CONTENT IS PROVIDED AS IS, WITHOUT REPRESENTATIONS OR WARRANTIES OF ANY KIND.  TO THE MAXIMUM EXTENT PERMISSIBLE UNDER APPLICABLE LAW, RHO HEREBY DISCLAIMS ANY AND ALL WARRANTIES, EXPRESS AND IMPLIED, RELATING TO THE RHO CONTENT, AND NEITHER RHO NOR ANY OF ITS AFFILIATES SHALL IN ANY EVENT BE LIABLE FOR ANY DAMAGES OF ANY NATURE WHATSOEVER, INCLUDING, BUT NOT LIMITED TO, DIRECT, INDIRECT, CONSEQUENTIAL, SPECIAL AND PUNITIVE DAMAGES, LOSS OF PROFITS AND TRADING LOSSES, RESULTING FROM ANY PERSON'S USE OR RELIANCE UPON, OR INABILITY TO USE, ANY RHO CONTENT, EVEN IF RHO IS ADVISED OF THE POSSIBILITY OF SUCH DAMAGES OR IF SUCH DAMAGES WERE FORESEEABLE.
>
> ______________________________________________
> 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.
>