[R] stacking consecutive columns

[Gabor Grothendieck]

On Wed, Nov 17, 2010 at 10:37 AM, Graves, Gregory <ggraves at sfwmd.gov> 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
>
>

I get an error when I run that code so I am not completely sure
precisely what the order of columns etc. should be but here is a
solution that can be tweaked that uses zoo's ability to use vector
lags which seems reasonably straight forward.

First we create a ts series, s and then cbind it with year and month
giving s3.  Then we use zoo's lag function together with the vector of
lags 0, 12, 24, ...  Finally use write table to create a csv file and
run the csv file (which under the default set up in Windows launches
Excel and reads it in).  If yearmonth is a matrix rather than a data
frame or if you want reversed rows or columns use the appropriate
commented out lines.

# create a ts series, s, and then cbind it with the year and month giving s3

# s <- ts(c(yearmonth[, -seq(2)]), start = c(2001, 1), freq = 12)
s <- ts(unlist(yearmonth[, -seq(2)]), start = c(2001, 1), freq = 12)
s3 <- cbind(year = floor(time(s)), month = cycle(s), s)

# use zoo's lag to create the desired layout

library(zoo)
numyears <- length(s) / 12
# sl <- na.omit(lag(as.zoo(s3), seq((numyears-2)*12, 0, -12)))
sl <- na.omit(lag(as.zoo(s3), seq(0, (numyears-2)*12, 12)))

# time(sl) <- - time(sl)
write.table(sl, file = "out.csv", row.names = FALSE,
   col.names = rep(c("year", "month", "value"), length = ncol(sl)), sep = ",")
shell("out.csv")

-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com