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:> tstK1 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.
How about:
tst.long <- melt ( tst, id.vars = c("K1", "K2",
"K3"), variable_name = "V" )
tst2 <- transform ( tst.long, K23Vx = paste ( K2, K3, V, sep = ".")
)
tst.wide <- cast ( tst2 , K1 ~ K23Vx )
tst.wide
> tst.wide
K1 D.a.V1 D.a.V2 D.a.V3 D.b.V1 D.b.V2 D.b.V3 E.a.V1 E.a.V2 E.a.V3 E.b.V1
E.b.V2 E.b.V3
1 10 0.08 99 105 NA 76 775 NA NA NA 0.11
85 532
2 20 0.00 79 522 0.26 84 372 0.08 73 251 NA
NA NA
3 30 0.31 70 989 0.24 51 680 NA 38 323 0.07
20 364>
Eric
----- Original message -----
From: davidr at rhotrading.com
To: R-help at r-project.org
Date: Mon, 3 Aug 2009 16:23:22 -0500
Subject: [R] Help with reshaping data.frame
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.
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.library(reshape) tst_m <- melt(tst, id = c("K1","K2", "K3")) cast(tst_m, K1 ~ K2 + K3 + variable) Hadley -- http://had.co.nz/
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. >