thr3ads.net - R help - [R] reshape data from long to wide format [Sep 2011]

If this information is useful, please help other people find it:
Share via:

maxbre

2011-Sep-09 11:28 UTC

[R] reshape data from long to wide format

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?

thank you

max

--
View this message in context:
http://r.789695.n4.nabble.com/reshape-data-from-long-to-wide-format-tp3801381p3801381.html
Sent from the R help mailing list archive at Nabble.com.

Jean V Adams

2011-Sep-09 13:15 UTC

head link

[R] reshape data from long to wide format

maxbre wrote on 09/09/2011 06:28:15 AM:> 
> 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;

It's not clear to me what the wide format of your example would look like. 
 You start with this:

  SENSOR VALUE          DATE
1      A 270.0  01/01/2010 1
2      B 292.5  01/01/2010 2
3      C   0.0  01/01/2010 3
4      D  45.0  01/01/2010 4
5      E 247.5  01/01/2010 5
6      F 315.0  01/01/2010 6

And you want to change it to ... something like this?

         DATE   A     B  C  D     E   F
 01/01/2010 1 270    NA NA NA    NA  NA
 01/01/2010 2  NA 292.5 NA NA    NA  NA
 01/01/2010 3  NA    NA  0 NA    NA  NA
 01/01/2010 4  NA    NA NA 45    NA  NA
 01/01/2010 5  NA    NA NA NA 247.5  NA
 01/01/2010 6  NA    NA NA NA    NA 315

Jean

> 
> 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?
> 
> thank you
> 
> max
> 	[[alternative HTML version deleted]]

Dennis Murphy

2011-Sep-09 17:29 UTC

head link

[R] reshape data from long to wide format

Hi:

There are two ways to go about this, depending on what you want.

# 1: Jean's output format:

# I'm using the reshape package here with cast(),
# but it should work similarly with dcast() in reshape2:> cast(example, DATE ~ SENSOR, value = 'VALUE')           DATE   A     B  C  D     E   F
1  01/01/2010 1 270    NA NA NA    NA  NA
2  01/01/2010 2  NA 292.5 NA NA    NA  NA
3  01/01/2010 3  NA    NA  0 NA    NA  NA
4  01/01/2010 4  NA    NA NA 45    NA  NA
5  01/01/2010 5  NA    NA NA NA 247.5  NA
6  01/01/2010 6  NA    NA NA NA    NA 315

# Method 2: One line per day - requires redefinition of date
example$date <- as.Date('01/01/2010', format = '%m/%d/%Y')
cast(example, date ~ SENSOR, value = 'VALUE')
        date   A     B C  D     E   F
1 2010-01-01 270 292.5 0 45 247.5 315

In both cases, note the use of value = 'VALUE' in the cast() call. It
needs value_var = 'VALUE' instead of value = 'VALUE' if
you're using
dcast() from reshape2 instead:

library('reshape2')
dcast(example, DATE ~ SENSOR, value_var = 'VALUE')
dcast(example, date ~ SENSOR, value_var = 'VALUE')

HTH,
Dennis

On Fri, Sep 9, 2011 at 4: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?
>
> thank you
>
> max
>
> --
> View this message in context:
http://r.789695.n4.nabble.com/reshape-data-from-long-to-wide-format-tp3801381p3801381.html
> Sent from the R help mailing list archive at Nabble.com.
>
> ______________________________________________
> 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.
>

maxbre

2011-Sep-10 12:44 UTC

head link

[R] reshape data from long to wide format

thank you to both you, this is exactly the solution I was looking for

max

--
View this message in context:
http://r.789695.n4.nabble.com/reshape-data-from-long-to-wide-format-tp3801381p3803759.html
Sent from the R help mailing list archive at Nabble.com.

Gabor Grothendieck

2011-Sep-10 13:59 UTC

head link

[R] reshape data from long to wide format

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

Possibly Parallel Threads

Search for more reasonably related threads

R help - Sep 2011 - reshape data from long to wide format

[R] reshape data from long to wide format

[R] reshape data from long to wide format

[R] reshape data from long to wide format

[R] reshape data from long to wide format

[R] reshape data from long to wide format

Possibly Parallel Threads