[R] reshape data from long to wide format

[Gabor Grothendieck]

On Fri, Sep 9, 2011 at 7:28 AM, maxbre <mbressan at arpa.veneto.it> wrote:
> This is my reproducible example:
>
> example<-structure(list(SENSOR = structure(1:6, .Label = c("A", "B", "C",
> "D", "E", "F"), class = "factor"), VALUE = c(270, 292.5, 0, 45,
> 247.5, 315), DATE = structure(1:6, .Label = c(" 01/01/2010 1",
> " 01/01/2010 2", " 01/01/2010 3", " 01/01/2010 4", " 01/01/2010 5",
> " 01/01/2010 6"), class = "factor")), .Names = c("SENSOR", "VALUE",
> "DATE"), class = "data.frame", row.names = c("1", "2", "3", "4",
> "5", "6"))
>
> I need to resahpe "example"  in a wide format so that “SENSOR” appear as
> columns and “DATE” as rows with corresponding “VALUE” as value;
>
> I thought it was very simple so that I’ve been trying this:
>
> dcast(example,DATE~SENSOR)
>
> But I've got this message:
>
> Using DATE as value column.  Use the value argument to cast to override this
> choice
> Errore in `[.data.frame`(data, , variables, drop = FALSE) :
>  undefined columns selected
>
> and even if by using the value argument sorted out any good result…
>
> sorry for the very trivial question, I've been looking at documentation and
> forums anywhere but I was not successful at all and now I’m somehow in the
> right middle of nowhere…
>
> any help or hint for this?
>

1. Try this:

> ex2 <- transform(example, DATE = as.Date(DATE, format = "%m/%d/%Y"))
>
> library(reshape2)
> m <- melt(ex2, id = c("DATE", "SENSOR"))
> dcast(m,DATE ~ SENSOR)
        DATE   A     B C  D     E   F
1 2010-01-01 270 292.5 0 45 247.5 315


2. or using xtabs and ex2 from above:

> xtabs(VALUE ~ DATE + SENSOR, ex2)
            SENSOR
DATE             A     B     C     D     E     F
  2010-01-01 270.0 292.5   0.0  45.0 247.5 315.0


3. or using reshape and ex2 from above:

> reshape(ex2, dir = "wide", timevar = "SENSOR", idvar = "DATE")
        DATE VALUE.A VALUE.B VALUE.C VALUE.D VALUE.E VALUE.F
1 2010-01-01     270   292.5       0      45   247.5     315


4. or using zoo (which works with example directly).  The read.zoo
statements splits on column 1 and also converts DATE (column 3) to
"Date" class at the same time.  The result is a zoo object z.  If you
want a data frame DF then add the last statement:

> library(zoo)
> z <- read.zoo(example, index = 3, split = 1, format = "%m/%d/%Y")
> DF <- data.frame(date = time(z), coredata(z)); DF
        date   A     B C  D     E   F
1 2010-01-01 270 292.5 0 45 247.5 315


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