[R] stacking consecutive columns

[Graves, Gregory]

Follows is the exact solution to this:

v <- NULL

#note that decreasing is FALSE so preceding year preceeds

for(i in 2:46) {
  kk <- melt(yearmonth[, c(1, i, i+1)], id.vars="month", variable_name="year")
  v[[i-1]] <- kk[order(kk$year, decreasing=FALSE),]
}

x <- do.call(cbind, v)
write.table(x,"clipboard",sep="	",col.names=NA)  #export to Excell via Ctrl-V


Gregory A. Graves, Lead Scientist
Everglades REstoration COoordination and VERification (RECOVER) 
Restoration Sciences Department
South Florida Water Management District
Phones:  DESK: 561 / 682 - 2429 
                   CELL:  561 / 719 - 8157


-----Original Message-----
From: Patrick Hausmann [mailto:patrick.hausmann at uni-bremen.de] 
Sent: Wednesday, November 17, 2010 9:49 AM
To: r-help at r-project.org; Graves, Gregory
Subject: Re: [R] stacking consecutive columns


Hi Gregory,

is this what you want? Ok, not the most elegant way...

# using 'melt' from the 'reshape' package

library(reshape)
Data <- data.frame(month = 1:12,
                  x2002 = runif(12),
                  x2003 = runif(12),
                  x2004 = runif(12),
                  x2005 = runif(12))

v <- NULL

for(i in 2:4) {
  kk <- melt(Data[, c(1, i, i+1)], id.vars="month", variable_name="year")
  v[[i-1]] <- kk[order(kk$year, decreasing=TRUE),]
}

out <- do.call(cbind, v)

HTH
Patrick

Am 17.11.2010 15:03, schrieb Graves, Gregory:
> I have a file, each column of which is a separate year, and each row of each column is mean precipitation for that month.  Looks like this (except it goes back to 1964).
>
> month        X2000  X2001  X2002  X2003  X2004  X2005  X2006 X2007  X2008  X2009
> 1            1.600  1.010  4.320  2.110  0.925  3.275  3.460 0.675  1.315  2.920
> 2            2.960  3.905  3.230  2.380  2.720  1.880  2.430 1.380  2.480  2.380
> 3            1.240  1.815  1.755  1.785  1.250  3.940 10.025 0.420  2.845  2.460
> 4            3.775  1.350  2.745  0.170  0.710  2.570  0.255 0.425  4.470  1.250
> 5            4.050  1.385  5.650  1.515 12.005  6.895  7.020 4.060  7.725  2.775
> 6            8.635  8.900 15.715 12.680 16.270 12.605  7.095 7.025 10.465  7.345
> 7            5.475  7.955  7.880  6.670  7.955  7.355  5.475 5.650  7.255  7.985
> 8            8.435  5.525  7.120  6.250  7.150  7.610  5.525 6.500  6.275 10.405
> 9            5.855  7.830  7.250  7.355  9.715  7.850  6.385 7.960  4.485  7.250
> 10          7.965 11.915  6.735  8.125  7.855 10.465  4.340 6.165  2.400  3.240
> 11          1.705  1.525  0.905  1.670  1.840  2.100  0.255 2.830  4.425  1.645
> 12          2.335  0.840  0.795  1.890  0.145  1.700  0.260 2.160  2.300  2.220
>
> What I want to do is to stack 2008 data underneath 2009 data, 2007 under 2008, 2006 under 2007, etc.  I have so far figured out that I can do this with the following clumsy approach:
>
> a=stack(yearmonth,select=c(X2009,X2008))
> b=stack(yearmonth,select=c(X2008,X2007))
> x=as.data.frame(c(a,b))
> write.table(x,"clipboard",sep="	",col.names=NA) #then paste this back into Excel to get this
>
>
>   	values	ind	values.1	ind.1
> 1	0.275	X2009	1.285	X2008
> 2	0.41	X2009	3.85	X2008
> 3	1.915	X2009	3.995	X2008
> 4	1.25	X2009	3.845	X2008
> 5	8.76	X2009	2.095	X2008
> 6	8.65	X2009	8.29	X2008
> 7	7.175	X2009	9.405	X2008
> 8	7.19	X2009	13.44	X2008
> 9	8.13	X2009	7.245	X2008
> 10	1.46	X2009	5.645	X2008
> 11	2.56	X2009	0.535	X2008
> 12	5.01	X2009	1.225	X2008
> 13	1.285	X2008	0.72	X2007
> 14	3.85	X2008	1.89	X2007
> 15	3.995	X2008	1.035	X2007
> 16	3.845	X2008	2.86	X2007
> 17	2.095	X2008	3.785	X2007
> 18	8.29	X2008	9.62	X2007
> 19	9.405	X2008	9.245	X2007
> 20	13.44	X2008	5.595	X2007
> 21	7.245	X2008	8.4	X2007
> 22	5.645	X2008	6.705	X2007
> 23	0.535	X2008	1.47	X2007
> 24	1.225	X2008	1.665	X2007
>
>
> Is there an easier, cleaner way to do this?  Thanks.
>
> Gregory A. Graves, Lead Scientist
> Everglades REstoration COoordination and VERification (RECOVER)
> Restoration Sciences Department
> South Florida Water Management District
> Phones:  DESK: 561 / 682 - 2429
>                     CELL:  561 / 719 - 8157
>
> ______________________________________________
> 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.