[R] stacking consecutive columns

[David Winsemius]

On Nov 17, 2010, at 10:37 AM, Graves, Gregory wrote:

> 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

The usual method of separating items in columns when exporting to  
Excel would be with tabs so people using this as a template might also  
want to try:

write.table(x,"clipboard",sep="\t",col.names=NA)  #export to Excell  
via Ctrl-V

-- 
David.
>
>
> Gregory A. Graves, Lead Scientist
>
> -----Original Message-----
> From: Patrick Hausmann [mailto:patrick.hausmann at uni-bremen.de]
> Sent: Wednesday, November 17, 2010 9:49 AM
>
> 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.

David Winsemius, MD
West Hartford, CT