R help - Jan 2010 - sorted reshaping?

dear R wizards:? I am wrestling with reshape.? I have a long data set
that I want to convert into a wide data set, in which rows are firms
and columns are years.
> summary(rin)
     firm              fyear             sim1
 Min.   :1004.00   Min.   :1964.0   Min.   : -1.00000
 1st Qu.:1010.00   1st Qu.:1979.0   1st Qu.: -0.14334
 Median :1016.00   Median :1986.0   Median :  0.00116
 Mean   :1016.34   Mean   :1986.1   Mean   :  1.03475
 3rd Qu.:1021.00   3rd Qu.:1993.2   3rd Qu.:  0.26931
 Max.   :1034.00   Max.   :2007.0   Max.   :110.66860
 S.D.   :   9.26   S.D.   :  10.0   S.D.   :  8.37811
 T-stat :1551.59   T-stat :2804.7   T-stat :  1.69344
 Obs.   : 200.00   Obs.   : 200.0   Obs.   :188.00000
                                    NA's   : 12.00000

the firms and years are both ordered.   I tried reshape(rin,
timevar="fyear", idvar="gvkey", direction="wide"),
and the shape seems
to be what I want, but the columns come out in random order
(sim1.1980, then sim1.2001, then sim1.1977) and so on.  I would like
years to go from left to right, too.  Is there an easy way to
accomplish this?

sincerely,

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

Hi:

# Start with some fictitious data:
> firm <- rep(1:10, each = 5)
> year <- rep(2002:2006, 10)
> resp <- rnorm(50)
> dat.long <- data.frame(firm = firm, year = year, resp = resp)
> head(dat.long)
  firm year        resp
1    1 2002 -0.77367688
2    1 2003 -0.79069791
3    1 2004  0.69257133
4    1 2005  2.46788204
5    1 2006  0.38892289
6    2 2002 -0.03521033
# Reshape to wide format
> dat.wide <- reshape(dat.long, direction = 'wide', idvar =
'firm', timevar
= 'year')
> head(dat.wide)
   firm   resp.2002   resp.2003   resp.2004  resp.2005  resp.2006
1     1 -0.77367688 -0.79069791  0.69257133  2.4678820  0.3889229
6     2 -0.03521033 -0.01071611 -0.74209425  1.3697428 -1.2277544
11    3  0.29621976  0.28208192 -2.11044822  0.0665793 -0.1803621
16    4  0.84738878  0.50286270  2.44455234  0.6908494  2.2145275
21    5 -0.15898812 -0.41355723  0.13134838 -0.8689705 -0.9267220
26    6 -1.95177791  0.12495833  0.06847564  1.1842542 -1.1663808


You can also use the function cast() from the reshape package:

library(reshape)
dat.cast <- cast(dat.long, firm ~ year)
> dat.cast
   firm        2002        2003        2004       2005       2006
1     1 -0.77367688 -0.79069791  0.69257133  2.4678820  0.3889229
2     2 -0.03521033 -0.01071611 -0.74209425  1.3697428 -1.2277544
3     3  0.29621976  0.28208192 -2.11044822  0.0665793 -0.1803621
4     4  0.84738878  0.50286270  2.44455234  0.6908494  2.2145275
5     5 -0.15898812 -0.41355723  0.13134838 -0.8689705 -0.9267220
6     6 -1.95177791  0.12495833  0.06847564  1.1842542 -1.1663808
7     7 -0.30278432 -0.30268288  0.79178229  0.5302185 -0.3203084
8     8  0.54863467 -0.28418721 -0.29055958 -1.6944445 -1.1673587
9     9 -0.79943512 -1.54412306  0.26626485 -0.8761801 -1.8362077
10   10 -0.82616345 -0.28643172  0.12032793 -1.2606434 
0.6109601
> class(dat.cast)
[1] "cast_df"    "data.frame"
names(dat.cast)[2:6] <- paste('y', 2002:2006, sep = '')

HTH,
Dennis

On Fri, Jan 22, 2010 at 2:04 PM, ivo welch <ivowel@gmail.com> wrote:
> dear R wizards:  I am wrestling with reshape.  I have a long data set
> that I want to convert into a wide data set, in which rows are firms
> and columns are years.
>
> > summary(rin)
>     firm              fyear             sim1
>  Min.   :1004.00   Min.   :1964.0   Min.   : -1.00000
>  1st Qu.:1010.00   1st Qu.:1979.0   1st Qu.: -0.14334
>  Median :1016.00   Median :1986.0   Median :  0.00116
>  Mean   :1016.34   Mean   :1986.1   Mean   :  1.03475
>  3rd Qu.:1021.00   3rd Qu.:1993.2   3rd Qu.:  0.26931
>  Max.   :1034.00   Max.   :2007.0   Max.   :110.66860
>  S.D.   :   9.26   S.D.   :  10.0   S.D.   :  8.37811
>  T-stat :1551.59   T-stat :2804.7   T-stat :  1.69344
>  Obs.   : 200.00   Obs.   : 200.0   Obs.   :188.00000
>                                    NA's   : 12.00000
>
> the firms and years are both ordered.   I tried reshape(rin,
> timevar="fyear", idvar="gvkey",
direction="wide"), and the shape seems
> to be what I want, but the columns come out in random order
> (sim1.1980, then sim1.2001, then sim1.1977) and so on.  I would like
> years to go from left to right, too.  Is there an easy way to
> accomplish this?
>
> sincerely,
>
> /iaw
> ----
> Ivo Welch (ivo.welch@brown.edu, ivo.welch@gmail.com)
>
> ______________________________________________
> R-help@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.
>
	[[alternative HTML version deleted]]

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)