Color cells of a matrix as in Excel

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Color cells of a matrix as in Excel

John Nicholas-2
All,

I frequently make spreadsheets in Excel in which I rank values in columns
by stop-light colors (red is bad, yellow is OK, green is good).

Image and heatmap expect a matrix in which all the data are in the same
scale, but I frequently have different scales in different columns. ie.
Column one runs from 1-10 while column 2 runs from 1-100. I thus need to
define a separate color ramp for each column. In addition, sometimes the
smaller numbers are colored green, while sometimes the larger numbers are
preferred and colored green. I also want to print in each cell the numeric
value, and I need to show row names. A crude example without the color:

RowNames        Col1     Col2
----------------------------------------------
Row1                   1          1
Row2                   5         50
Row3                  7.5       80
Row4                  10        99
......

Is there any R package that can do something similar? Can I create a plot
that is a matrix of rectangles and get color and text that way?

Any help is greatly appreciated.

Thanks,

John

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Color cells of a matrix as in Excel

Kenneth Frost
Hi, John-
Would it be possible to scale the values in each column to fall between 0 and 1 (or standardize them) and use a single color ramp?
Ken

On 02/14/12, John Nicholas   wrote:

> All,
>
> I frequently make spreadsheets in Excel in which I rank values in columns
> by stop-light colors (red is bad, yellow is OK, green is good).
>
> Image and heatmap expect a matrix in which all the data are in the same
> scale, but I frequently have different scales in different columns. ie.
> Column one runs from 1-10 while column 2 runs from 1-100. I thus need to
> define a separate color ramp for each column. In addition, sometimes the
> smaller numbers are colored green, while sometimes the larger numbers are
> preferred and colored green. I also want to print in each cell the numeric
> value, and I need to show row names. A crude example without the color:
>
> RowNames        Col1     Col2
> ----------------------------------------------
> Row1                   1          1
> Row2                   5         50
> Row3                  7.5       80
> Row4                  10        99
> ......
>
> Is there any R package that can do something similar? Can I create a plot
> that is a matrix of rectangles and get color and text that way?
>
> Any help is greatly appreciated.
>
> Thanks,
>
> John
>
> [[alternative HTML version deleted]]
>
> ______________________________________________
> [hidden email] 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.

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Color cells of a matrix as in Excel

Kenneth Frost
Hi, John-
Sorry if my last email was not clear.  In R I imagine you having two matrices. One with the raw values and one with values scaled or standardized by column. The matrix with the values scaled by column is used to make the heatmap (i.e. color the image) and the matrix with the raw values is then overlaid on the heatmap. The details of the image or heatmap functions escape me as I have not worked with them for several years. Without doing this myself (and without a lot of time to try it out), I cannot speak for its difficulty, although I don't imagine it being too tedious. It is likely that others will have good ideas and I have copied this back to the list to hopefully find out...
Ken      

On 02/14/12, John Nicholas   wrote:

> I can do that for the color ramp (although a bit tedious to do for each new spreadsheet), but then how do I get the actual values printed in the cells?
>
>
>
>
>
> On Tue, Feb 14, 2012 at 2:35 PM, Kenneth Frost <[hidden email] <[hidden email]>> wrote:
>
>
>
> > Hi, John-
> >
>
> > Would it be possible to scale the values in each column to fall between 0 and 1 (or standardize them) and use a single color ramp?
> >
>
> > Ken
> >
>
> >
> >
>
> > On 02/14/12, John Nicholas   wrote:
> >
>
> > > All,
> >
>
> > >
> >
>
> > > I frequently make spreadsheets in Excel in which I rank values in columns
> >
>
> > > by stop-light colors (red is bad, yellow is OK, green is good).
> >
>
> > >
> >
>
> > > Image and heatmap expect a matrix in which all the data are in the same
> >
>
> > > scale, but I frequently have different scales in different columns. ie.
> >
>
> > > Column one runs from 1-10 while column 2 runs from 1-100. I thus need to
> >
>
> > > define a separate color ramp for each column. In addition, sometimes the
> >
>
> > > smaller numbers are colored green, while sometimes the larger numbers are
> >
>
> > > preferred and colored green. I also want to print in each cell the numeric
> >
>
> > > value, and I need to show row names. A crude example without the color:
> >
>
> > >
> >
>
> > > RowNames        Col1     Col2
> >
>
> > > ----------------------------------------------
> >
>
> > > Row1                   1          1
> >
>
> > > Row2                   5         50
> >
>
> > > Row3                  7.5       80
> >
>
> > > Row4                  10        99
> >
>
> > > ......
> >
>
> > >
> >
>
> > > Is there any R package that can do something similar? Can I create a plot
> >
>
> > > that is a matrix of rectangles and get color and text that way?
> >
>
> > >
> >
>
> > > Any help is greatly appreciated.
> >
>
> > >
> >
>
> > > Thanks,
> >
>
> > >
> >
>
> > > John
> >
>
> > >
> >
>
> >
> >
> > >       [[alternative HTML version deleted]]
> >
>
> > >
> >
>
> > > ______________________________________________
> >
>
> > > [hidden email] <[hidden email]> 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.
> >
>
> >
> >
>
> >
>
>
>
>
>

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Color cells of a matrix as in Excel

jholtman
In reply to this post by John Nicholas-2
Check out the XLConnect package for formatting a spreadsheet.

On Tue, Feb 14, 2012 at 5:21 PM, John Nicholas <[hidden email]> wrote:

> All,
>
> I frequently make spreadsheets in Excel in which I rank values in columns
> by stop-light colors (red is bad, yellow is OK, green is good).
>
> Image and heatmap expect a matrix in which all the data are in the same
> scale, but I frequently have different scales in different columns. ie.
> Column one runs from 1-10 while column 2 runs from 1-100. I thus need to
> define a separate color ramp for each column. In addition, sometimes the
> smaller numbers are colored green, while sometimes the larger numbers are
> preferred and colored green. I also want to print in each cell the numeric
> value, and I need to show row names. A crude example without the color:
>
> RowNames        Col1     Col2
> ----------------------------------------------
> Row1                   1          1
> Row2                   5         50
> Row3                  7.5       80
> Row4                  10        99
> ......
>
> Is there any R package that can do something similar? Can I create a plot
> that is a matrix of rectangles and get color and text that way?
>
> Any help is greatly appreciated.
>
> Thanks,
>
> John
>
>        [[alternative HTML version deleted]]
>
> ______________________________________________
> [hidden email] 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.

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Color cells of a matrix as in Excel

baptiste auguie-5
In reply to this post by John Nicholas-2
A minimum code to plot a coloured matrix with text labels could be the
following:


library(grid)
library(scales)
library(RColorBrewer)


diverging_palette <- function(d = NULL, centered = FALSE, midpoint = 0,
                              colors = brewer.pal(7,"PRGn")){

  half <- length(colors)/2

  if(!length(colors)%%2) stop("requires odd number of colors")

  values <-  if(centered) {
    low <- seq(min(d), midpoint, length=half)
    high <- seq(midpoint, max(d), length=half)
    c(low[-length(low)], midpoint, high[-1])
  } else {
    mabs <- max(abs(d - midpoint))
    seq(midpoint-mabs, midpoint + mabs, length=length(colors))
  }

  gradient_n_pal(colors, values = values)

}


matrixGrob <- function(d){

  nc <- ncol(d)
  nr <- nrow(d)

  palet <- diverging_palette(d, center=FALSE)

  fill.matrix <- palet(d)
  dim(fill.matrix) <- dim(d)

  ## matrix of square tiles
  rg <- rasterGrob(fill.matrix, width=unit(1, "npc"), height=unit(1,
"npc"), interpolate = FALSE)
  ## position of the labels
  xy <- expand.grid(y = rescale(seq_len(nr), c(0.5/nr, 1 - 0.5/nr)   ),
                    x = rescale(seq_len(nc), c(0.5/nc, 1 - 0.5/nc))
                    )

  ## text
  tg <- textGrob(label=round(d, 3), x=xy$x, y= 1 - xy$y, def="npc")

  gTree(children=gList(rg, tg))
}


grid.matrix <- function(d)
  grid.draw(matrixGrob(d))

d <- cbind(x=rnorm(10), y=rnorm(10))
grid.newpage()
grid.matrix(d)

I don't think it would be very hard to adapt to your needs, basically
you need to add rownames on top, apply() the scale to each column
independently (as opposed to the full matrix at the moment) and
combine the results in a fill.matrix. Having one colour scale across
the matrix makes more sense, imho.


HTH,

b.


On 15 February 2012 11:21, John Nicholas <[hidden email]> wrote:

> All,
>
> I frequently make spreadsheets in Excel in which I rank values in columns
> by stop-light colors (red is bad, yellow is OK, green is good).
>
> Image and heatmap expect a matrix in which all the data are in the same
> scale, but I frequently have different scales in different columns. ie.
> Column one runs from 1-10 while column 2 runs from 1-100. I thus need to
> define a separate color ramp for each column. In addition, sometimes the
> smaller numbers are colored green, while sometimes the larger numbers are
> preferred and colored green. I also want to print in each cell the numeric
> value, and I need to show row names. A crude example without the color:
>
> RowNames        Col1     Col2
> ----------------------------------------------
> Row1                   1          1
> Row2                   5         50
> Row3                  7.5       80
> Row4                  10        99
> ......
>
> Is there any R package that can do something similar? Can I create a plot
> that is a matrix of rectangles and get color and text that way?
>
> Any help is greatly appreciated.
>
> Thanks,
>
> John
>
>        [[alternative HTML version deleted]]
>
> ______________________________________________
> [hidden email] 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.

______________________________________________
[hidden email] 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.