R help - Mar 2008 - Converting a data frame with values into a matrix/

Dear Group, 
I have a data frame like the following:


x <- c("Mike","A",0.01)
x1 <- c("Carl","A",0.2)
x2 <- c("Gene","C",0.3)
x3 <- c("James","A",-0.3)
x4 <- c("Dough","B",0)
xx <- rbind(x,x1,x2,x3,x4)
colnames(xx)<-c("Name","Class","NES")
xx <-as.data.frame(xx)
> xx
    Name Class  NES
x   Mike     A 0.01
x1  Carl     A  0.2
x2  Gene     C  0.3
x3 James     A -0.3
x4 Dough     B    0


Now I want to create a matrix with unique xx$Name on
columns and unique xx$Class as rows.  I want to fill
my choice of values (in this case 1) if data point not
available. 


xy <-
matrix(1,length(unique(xx$Class)),length(unique(xx[,1])))
colnames(xy)<-unique(xx[,1])
rownames(xy)<-unique(xx$Class)
> xy
  Mike Carl Gene James Dough
A    1    1    1     1     1
C    1    1    1     1     1
B    1    1    1     1     1





I would love to have :

  Mike Carl Gene James Dough
A    0.01    0.2    1     -0.3     1
C    1    1    1     0.3     1
B    1    1    1     1    0




If I am not wrong this is called contigency or
frequeny table. 

I tried xtabs on this.  
> z <- xtabs(NES ~ Name+Class,data=xx)
Error in Summary.factor(4L, na.rm = FALSE) : 
  sum not meaningful for factors


I tried on other data frames, it worked. BUT the
problem is it gives me 0.0000 even a value is not
available for that row and column.  So if I have data
-0.00 it is considered 0. 

I tried. drop.unused.levels = T, I did not get what I
want. I want all row.col values not available to be 1.


Is there any other trick where I map by row and column
names instead of using advanced xtabs. 

thanks
Srini

Perhaps in this case:

noquote(with(xx, tapply(NES, list(Class, Name), paste)))


On 12/03/2008, Srinivas Iyyer <srini_iyyer_bio at yahoo.com>
wrote:
> Dear Group,
>  I have a data frame like the following:
>
>
>  x <- c("Mike","A",0.01)
>  x1 <- c("Carl","A",0.2)
>  x2 <- c("Gene","C",0.3)
>  x3 <- c("James","A",-0.3)
>  x4 <- c("Dough","B",0)
>  xx <- rbind(x,x1,x2,x3,x4)
>  colnames(xx)<-c("Name","Class","NES")
>  xx <-as.data.frame(xx)
>
>  > xx
>     Name Class  NES
>  x   Mike     A 0.01
>  x1  Carl     A  0.2
>  x2  Gene     C  0.3
>  x3 James     A -0.3
>  x4 Dough     B    0
>
>
>  Now I want to create a matrix with unique xx$Name on
>  columns and unique xx$Class as rows.  I want to fill
>  my choice of values (in this case 1) if data point not
>  available.
>
>
>  xy <-
>  matrix(1,length(unique(xx$Class)),length(unique(xx[,1])))
>  colnames(xy)<-unique(xx[,1])
>  rownames(xy)<-unique(xx$Class)
>
>  > xy
>   Mike Carl Gene James Dough
>  A    1    1    1     1     1
>  C    1    1    1     1     1
>  B    1    1    1     1     1
>
>
>
>
>
>  I would love to have :
>
>   Mike Carl Gene James Dough
>  A    0.01    0.2    1     -0.3     1
>  C    1    1    1     0.3     1
>  B    1    1    1     1    0
>
>
>
>
>  If I am not wrong this is called contigency or
>  frequeny table.
>
>  I tried xtabs on this.
>
>  > z <- xtabs(NES ~ Name+Class,data=xx)
>  Error in Summary.factor(4L, na.rm = FALSE) :
>   sum not meaningful for factors
>
>
>  I tried on other data frames, it worked. BUT the
>  problem is it gives me 0.0000 even a value is not
>  available for that row and column.  So if I have data
>  -0.00 it is considered 0.
>
>  I tried. drop.unused.levels = T, I did not get what I
>  want. I want all row.col values not available to be 1.
>
>
>  Is there any other trick where I map by row and column
>  names instead of using advanced xtabs.
>
>  thanks
>  Srini
>
>  ______________________________________________
>  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.
>

-- 
Henrique Dallazuanna
Curitiba-Paran?-Brasil
25? 25' 40" S 49? 16' 22" O

The way that you have set up the data.frame is rather
unusual. It makes NES a factor which I suspect you
don't want. Do str(xx) to see what I mean

Here is a way that I think does what you want but with
the data.frame constructed in a different manner. 
Again do str(aa) to see the difference
====================================================aa <- data.frame(Name=c(
"Mike" ,"Carl", "Gene",
"James","Dough"),
         Class=c(  "A",  "A",  "C", 
"A",  "B"),
         NES=c( 0.01, 0.2, 0.3, -0.3, 0) )  
aa
         
library(reshape)
mm  <- melt(aa, id=c("Name", "Class"))
mm1  <- cast(mm, Class~Name)

aba <- mm1[,2:6]
aba[is.na(aba)] <- 1

final <- data.frame(mm1$Class, aba)
names(final) <- names(mm1) 
final

==================================================
--- Srinivas Iyyer <srini_iyyer_bio at yahoo.com> wrote:
> Dear Group, 
> I have a data frame like the following:
> 
> 
> x <- c("Mike","A",0.01)
> x1 <- c("Carl","A",0.2)
> x2 <- c("Gene","C",0.3)
> x3 <- c("James","A",-0.3)
> x4 <- c("Dough","B",0)
> xx <- rbind(x,x1,x2,x3,x4)
> colnames(xx)<-c("Name","Class","NES")
> xx <-as.data.frame(xx)
> 
> > xx
>     Name Class  NES
> x   Mike     A 0.01
> x1  Carl     A  0.2
> x2  Gene     C  0.3
> x3 James     A -0.3
> x4 Dough     B    0
> 
> 
> Now I want to create a matrix with unique xx$Name on
> columns and unique xx$Class as rows.  I want to fill
> my choice of values (in this case 1) if data point
> not
> available. 
> 
> 
> xy <-
>
matrix(1,length(unique(xx$Class)),length(unique(xx[,1])))
> colnames(xy)<-unique(xx[,1])
> rownames(xy)<-unique(xx$Class)
> 
> > xy
>   Mike Carl Gene James Dough
> A    1    1    1     1     1
> C    1    1    1     1     1
> B    1    1    1     1     1
> 
> 
> 
> 
> 
> I would love to have :
> 
>   Mike Carl Gene James Dough
> A    0.01    0.2    1     -0.3     1
> C    1    1    1     0.3     1
> B    1    1    1     1    0
> 
> 
> 
> 
> If I am not wrong this is called contigency or
> frequeny table. 
> 
> I tried xtabs on this.  
> 
> > z <- xtabs(NES ~ Name+Class,data=xx)
> Error in Summary.factor(4L, na.rm = FALSE) : 
>   sum not meaningful for factors
> 
> 
> I tried on other data frames, it worked. BUT the
> problem is it gives me 0.0000 even a value is not
> available for that row and column.  So if I have
> data
> -0.00 it is considered 0. 
> 
> I tried. drop.unused.levels = T, I did not get what
> I
> want. I want all row.col values not available to be
> 1.
> 
> 
> Is there any other trick where I map by row and
> column
> names instead of using advanced xtabs. 
> 
> thanks
> Srini
> 
> ______________________________________________
> 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.
>

>  mm1  <- cast(mm, Class~Name)
>  aba <- mm1[,2:6]
>  aba[is.na(aba)] <- 1
>  final <- data.frame(mm1$Class, aba)
>  names(final) <- names(mm1)
>  final
You can abbreviate all this to:

final <- cast(mm, Class ~ Name, fill = 1)

Hadley

-- 
http://had.co.nz/