[R] reshape

[jim holtman]

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 <nrm2010 at zoho.com> 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 <at> humboldt <dot> 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.