sorted reshaping?

2 messages · ivo welch, Gabor Grothendieck

Original

ivo welch

Sat, Jan 23, 2010 5:25 AM #

thank you, dennis.? your example was much better than my own, too.

for everyone else's benefit who will be searching for the answers for
this problem on r-help, let me extend this a little.?? the problem
arises primarily in data sets that are sparse.? in my case, the first
(ordered) firm does not have data in the first (ordered) year.

yields

   firm resp.2004 resp.2002 resp.2003 resp.2005 resp.2006
1     0    1.0000        NA        NA        NA        NA
2     1   -0.4483   -0.7623    1.0625    0.4654  -0.36041
7     2   -0.1373   -0.8648    0.7272    0.1637  -0.09235
12    3    1.0835    0.4544    0.1577   -1.6991  -1.04559
17    4    1.1390    2.1633   -1.2298    0.9460  -0.28682
22    5   -0.8906    1.0084   -0.1948   -0.6801  -0.47192


in contrast, the library(reshape) function did yield the desired order:

Loading required package: plyr

Using resp as value column.  Use the value argument to cast to
override this choice

firm    2002    2003    2004    2005     2006
1    0      NA      NA  1.0000      NA       NA
2    1 -0.7623  1.0625 -0.4483  0.4654 -0.36041
3    2 -0.8648  0.7272 -0.1373  0.1637 -0.09235
4    3  0.4544  0.1577  1.0835 -1.6991 -1.04559
5    4  2.1633 -1.2298  1.1390  0.9460 -0.28682
6    5  1.0084 -0.1948 -0.8906 -0.6801 -0.47192


I think this example would make a nice addition to "?reshape".  for
"see also", I would add library(reshape).  finally, it would be nice
if an email would be on each R help (in R documentation) to make
suggestions for improvements.

regards,

/iaw
----
Ivo Welch (ivo.welch at brown.edu, ivo.welch at gmail.com)

Gabor Grothendieck

Sat, Jan 23, 2010 5:36 AM #

This also works (giving a matrix so use as.data.frame if you want it
as a data frame):

year
firm        2002        2003       2004       2005        2006
  0           NA          NA  1.0000000         NA          NA
  1   0.86456075 -1.06794272  1.1850129 -0.1385206 -0.41101249
  2   0.04505626  0.24331227 -0.7902969  0.6833840  0.17823240
  3  -2.07018155  0.32203467 -1.0867019  1.1990902 -0.86256005
  4  -1.04872782 -0.90006314  1.0398810 -0.3545491 -0.93241925
  5  -1.55728162  0.98297102  1.0280005  1.6397312  0.79278075
  6   0.21446824  0.10913775  1.1284486 -0.4148321  0.95917517
  7  -1.97895845 -0.05366712  0.9740745  0.8070221  1.33701029
  8  -1.94635463 -1.03172741  1.5351331 -2.0643955  0.63561387
  9  -0.08866245  0.42450753 -0.8108118  0.5155799 -1.54716628
  10  0.11008895  0.18344337  0.3891310  0.9016668  0.04618046

On Sat, Jan 23, 2010 at 8:25 AM, ivo welch <ivowel at gmail.com> wrote:

thank you, dennis.? your example was much better than my own, too.

for everyone else's benefit who will be searching for the answers for
this problem on r-help, let me extend this a little.?? the problem
arises primarily in data sets that are sparse.? in my case, the first
(ordered) firm does not have data in the first (ordered) year.

firm <- c(0,rep(1:10, each = 5))
year <- c(2004,rep(2002:2006, 10))
resp <- c(1.0,rnorm(50))
dat.long <- data.frame(firm = firm, year = year, resp = resp)
dat.wide <- reshape(dat.long, direction = 'wide', idvar = 'firm', timevar= 'year')
head(dat.wide)

yields

? firm resp.2004 resp.2002 resp.2003 resp.2005 resp.2006
1 ? ? 0 ? ?1.0000 ? ? ? ?NA ? ? ? ?NA ? ? ? ?NA ? ? ? ?NA
2 ? ? 1 ? -0.4483 ? -0.7623 ? ?1.0625 ? ?0.4654 ?-0.36041
7 ? ? 2 ? -0.1373 ? -0.8648 ? ?0.7272 ? ?0.1637 ?-0.09235
12 ? ?3 ? ?1.0835 ? ?0.4544 ? ?0.1577 ? -1.6991 ?-1.04559
17 ? ?4 ? ?1.1390 ? ?2.1633 ? -1.2298 ? ?0.9460 ?-0.28682
22 ? ?5 ? -0.8906 ? ?1.0084 ? -0.1948 ? -0.6801 ?-0.47192


in contrast, the library(reshape) function did yield the desired order:

library(reshape)

Loading required package: plyr

dat.cast <- cast(dat.long, firm ~ year)

Using resp as value column. ?Use the value argument to cast to
override this choice

head(dat.cast)

?firm ? ?2002 ? ?2003 ? ?2004 ? ?2005 ? ? 2006
1 ? ?0 ? ? ?NA ? ? ?NA ?1.0000 ? ? ?NA ? ? ? NA
2 ? ?1 -0.7623 ?1.0625 -0.4483 ?0.4654 -0.36041
3 ? ?2 -0.8648 ?0.7272 -0.1373 ?0.1637 -0.09235
4 ? ?3 ?0.4544 ?0.1577 ?1.0835 -1.6991 -1.04559
5 ? ?4 ?2.1633 -1.2298 ?1.1390 ?0.9460 -0.28682
6 ? ?5 ?1.0084 -0.1948 -0.8906 -0.6801 -0.47192


I think this example would make a nice addition to "?reshape". ?for
"see also", I would add library(reshape). ?finally, it would be nice
if an email would be on each R help (in R documentation) to make
suggestions for improvements.

regards,

/iaw
----
Ivo Welch (ivo.welch at brown.edu, ivo.welch at gmail.com)

______________________________________________
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.