reshape
Is this closer using the same data:
dcast(x, year+month+day+hhmm+group+number ~ type, mean)
year month day hhmm group number Tair VWC 1 2007 1 1 1100 B 1 0.285404913 1.4337114478 2 2007 1 1 1100 B 2 -0.899893189 1.1004122756 3 2007 1 1 1100 T 1 -0.054938988 0.0565570314 4 2007 1 1 1100 T 10 NaN -0.4645357924 5 2007 1 1 1100 T 2 0.235724005 0.1355795323 6 2007 1 1 1101 B 1 0.008141599 0.2010562273 7 2007 1 1 1101 B 2 -0.094439009 0.7713080532 8 2007 1 1 1101 T 1 1.111981883 -0.5022737361 9 2007 1 1 1101 T 10 NaN 0.3061178598 10 2007 1 1 1101 T 2 0.101879382 -0.1886613029 11 2007 1 2 1100 B 1 -2.189530590 0.0741563102 12 2007 1 2 1100 B 2 0.290204966 1.4341878509 13 2007 1 2 1100 T 1 0.788716089 1.9156394486 14 2007 1 2 1100 T 10 NaN 1.9814202666 15 2007 1 2 1100 T 2 0.255978008 0.0604626563 16 2007 1 2 1101 B 1 -0.479254067 0.7770438119 17 2007 1 2 1101 B 2 0.526466448 -0.1691557783 18 2007 1 2 1101 T 1 2.070198634 0.1129769772 19 2007 1 2 1101 T 10 NaN 1.5263077972 20 2007 1 2 1101 T 2 -0.883571883 -0.5357702486
On Wed, Jun 20, 2012 at 12:33 PM, nrm2010 <nrm2010 at zoho.com> wrote:
Thank you, but it doesn't quite. ?It still needs a new column for each measurement type, i.e., a VWC column that contains the VWC measurements for each subject within group at each time point and a Tair column that contains the Tair measurements for each subject within group at each time point. Toby ---- On Wed, 20 Jun 2012 09:25:08 -0700 jim holtman ?wrote ----
will this do it for you:
require(reshape2) toy <- data.frame(year = rep(2007:2008,each = 20), month = rep(1:5,each = 4, length = 40),
+ day = rep(1:2,each = 2,length = 40), hhmm = rep(1100:1101,length = 40), plot = rep(1,40), + trt=rep(4,40), VWC.B1 = rnorm(40), VWC.B2 = rnorm(40), VWC.T1 = rnorm(40), + VWC.T2 = rnorm(40), VWC.T10 = rnorm(40), Tair.B1 = rnorm(40),Tair.B2 = rnorm(40), + Tair.T1 = rnorm(40), Tair.T2 = rnorm(40))
x <- melt(toy, id = c('year', 'month', 'day', 'hhmm', 'plot', 'trt'))
# parse the type, group and number
x$type <- sub("\..*$", "", x$variable)
x$group <- sub(".*\.(.).*", "\1", x$variable)
x$number <- sub(".*\..", "", x$variable)
head(x)
year month day hhmm plot trt variable value type group number 1 2007 1 1 1100 1 4 VWC.B1 0.35680152 VWC B 1 2 2007 1 1 1101 1 4 VWC.B1 -0.01787670 VWC B 1 3 2007 1 2 1100 1 4 VWC.B1 -0.07361414 VWC B 1 4 2007 1 2 1101 1 4 VWC.B1 0.90117558 VWC B 1 5 2007 2 1 1100 1 4 VWC.B1 0.64824677 VWC B 1 6 2007 2 1 1101 1 4 VWC.B1 0.26769079 VWC B 1
On Wed, Jun 20, 2012 at 12:08 PM, nrm2010 ?wrote:
Hello, helpeRs, I am attempting to reshape (either base R or package reshape) multiple .csv spreadsheets from a very unfortunate wide format to long format. ?Each spreadsheet looks something like this, after being read in to R: toy <- data.frame(year = rep(2007:2008,each = 20), month = rep(1:5,each = 4, length = 40), day = rep(1:2,each = 2,length = 40), hhmm = rep(1100:1101,length = 40), plot = rep(1,40), trt=rep(4,40), VWC.B1 = rnorm(40), VWC.B2 = rnorm(40), VWC.T1 = rnorm(40), VWC.T2 = rnorm(40), VWC.T10 = rnorm(40), Tair.B1 = rnorm(40),Tair.B2 = rnorm(40), Tair.T1 = ?rnorm(40), Tair.T2 = rnorm(40)) Taken together, the columns year, month, day, and hhmm define a timepoint at which at a measurement was taken. ?Plot and treatment do not change within any one spreadsheet. ?Parsing the column names, VWC and Tair are 2 different types of measurements; in the full dataset, there are more than 2 types of measurements. ?The B and T in the column names are grouping variables. ?The numbers following the B or T are subjects within groups. ?So, if a column name is VWC.B1, it is the VWC measurement taken on subject 1 within group B. I am trying to reshape to long format. The long format should have one new column for each measurement type (VWC and Tair in the toy example), a new column indicating the grouping variable (B or T), and a new column identifyng the subject (the number that follow the B or T). I've been unable to come up with a solution that produces the group and subject within group columns as well as both types of measurements. Any assistance would be greatly appreciated. Thank you. Toby Toby Gass, Ph.D. Visiting Lecturer, Forestry and Wildland Resources Humboldt State University email: toby.gass ?humboldt ?edu
______________________________________________ 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.
-- Jim Holtman Data Munger Guru What is the problem that you are trying to solve? Tell me what you want to do, not how you want to do it.
Jim Holtman Data Munger Guru What is the problem that you are trying to solve? Tell me what you want to do, not how you want to do it.