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
one approach is the following: # say 'Data' is your data frame DataNew <- reshape(Data, direction = "long", varying = list(2:length(Data))) DataNew$year <- rep(2000:2009, each = 12) DataNew I hope it helps. Best, Dimitris On 11/17/2010 3:03 PM, Graves, Gregory wrote:> 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. >-- Dimitris Rizopoulos Assistant Professor Department of Biostatistics Erasmus University Medical Center Address: PO Box 2040, 3000 CA Rotterdam, the Netherlands Tel: +31/(0)10/7043478 Fax: +31/(0)10/7043014 Web: http://www.erasmusmc.nl/biostatistiek/
Dear Gregory,> Is there an easier, cleaner way to do this? Thanks.There are of course several ways... (assuming yearmonth to be a data.frame) --- 1 --- year <- colnames (yearmonth) [-1] year <- gsub ("^[^[:digit:]]*([[:digit:]]*[^[:digit:]]*$)", "\\1", year) year <- as.numeric (year) month <- yearmonth$month precip <- as.matrix (yearmonth [, -1]) long.df <- data.frame (month = rep (month, length (year)), year = rep (year, each = nrow (yearmonth)), precipitation = as.numeric (precip)) If you're about to do this more often: --- 2 --- package hyperSpec (outdated on CRAN, if you want to install it use the version on rforge) has a function array2df which helps with this transformation: long.df <- array2df (precip, label.x = "precipitation", levels = list (month = month, year = year) --- 3 --- depending on your file (are the column names numbers without the Xs?) you may be able to abuse a hyperSpec object to read your data easily: x <- read.txt.wide ("filename", ...more options...) then as.long.df (x) is about what you want. (You'd probably want to rename the columns) HTH Claudia> > 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.-- Claudia Beleites Dipartimento dei Materiali e delle Risorse Naturali Universit? degli Studi di Trieste Via Alfonso Valerio 6/a I-34127 Trieste phone: +39 0 40 5 58-37 68 email: cbeleites at units.it
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.