Conditional color formatting (like Microsoft Excel) in R?

Question

I have this object in R:

z = structure(list(year_1 = c(2000L, 2000L, 2000L, 2000L, 2000L, 
2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 
2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 2001L, 2001L, 
2001L, 2001L, 2001L, 2001L, 2001L, 2001L, 2001L, 2001L, 2001L, 
2001L, 2001L, 2001L, 2001L, 2001L, 2001L, 2001L, 2001L, 2001L, 
2001L, 2002L, 2002L, 2002L, 2002L, 2002L, 2002L, 2002L, 2002L, 
2002L, 2002L, 2002L, 2002L, 2002L, 2002L, 2002L, 2002L, 2002L, 
2002L, 2002L, 2002L, 2002L, 2003L, 2003L, 2003L, 2003L, 2003L, 
2003L, 2003L, 2003L, 2003L, 2003L, 2003L, 2003L, 2003L, 2003L, 
2003L, 2003L, 2003L, 2003L, 2003L, 2003L, 2003L, 2004L, 2004L, 
2004L, 2004L, 2004L, 2004L, 2004L, 2004L, 2004L, 2004L, 2004L, 
2004L, 2004L, 2004L, 2004L, 2004L, 2004L, 2004L, 2004L, 2004L, 
2004L, 2005L, 2005L, 2005L, 2005L, 2005L, 2005L, 2005L, 2005L, 
2005L, 2005L, 2005L, 2005L, 2005L, 2005L, 2005L, 2005L, 2005L, 
2005L, 2005L, 2005L, 2005L, 2006L, 2006L, 2006L, 2006L, 2006L, 
2006L, 2006L, 2006L, 2006L, 2006L, 2006L, 2006L, 2006L, 2006L, 
2006L, 2006L, 2006L, 2006L, 2006L, 2006L, 2006L, 2007L, 2007L, 
2007L, 2007L, 2007L, 2007L, 2007L, 2007L, 2007L, 2007L, 2007L, 
2007L, 2007L, 2007L, 2007L, 2007L, 2007L, 2007L, 2007L, 2007L, 
2007L, 2008L, 2008L, 2008L, 2008L, 2008L, 2008L, 2008L, 2008L, 
2008L, 2008L, 2008L, 2008L, 2008L, 2008L, 2008L, 2008L, 2008L, 
2008L, 2008L, 2008L, 2008L, 2009L, 2009L, 2009L, 2009L, 2009L, 
2009L, 2009L, 2009L, 2009L, 2009L, 2009L, 2009L, 2009L, 2009L, 
2009L, 2009L, 2009L, 2009L, 2009L, 2009L, 2009L, 2010L, 2010L, 
2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 
2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 
2010L, 2011L, 2011L, 2011L, 2011L, 2011L, 2011L, 2011L, 2011L, 
2011L, 2011L, 2011L, 2011L, 2011L, 2011L, 2011L, 2011L, 2011L, 
2011L, 2011L, 2011L, 2011L, 2012L, 2012L, 2012L, 2012L, 2012L, 
2012L, 2012L, 2012L, 2012L, 2012L, 2012L, 2012L, 2012L, 2012L, 
2012L, 2012L, 2012L, 2012L, 2012L, 2012L, 2012L, 2013L, 2013L, 
2013L, 2013L, 2013L, 2013L, 2013L, 2013L, 2013L, 2013L, 2013L, 
2013L, 2013L, 2013L, 2013L, 2013L, 2013L, 2013L, 2013L, 2013L, 
2013L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 
2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 
2014L, 2014L, 2014L, 2014L, 2015L, 2015L, 2015L, 2015L, 2015L, 
2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 
2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2016L, 2016L, 
2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 
2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 
2016L, 2017L, 2017L, 2017L, 2017L, 2017L, 2017L, 2017L, 2017L, 
2017L, 2017L, 2017L, 2017L, 2017L, 2017L, 2017L, 2017L, 2017L, 
2017L, 2017L, 2017L, 2017L, 2018L, 2018L, 2018L, 2018L, 2018L, 
2018L, 2018L, 2018L, 2018L, 2018L, 2018L, 2018L, 2018L, 2018L, 
2018L, 2018L, 2018L, 2018L, 2018L, 2018L, 2018L, 2019L, 2019L, 
2019L, 2019L, 2019L, 2019L, 2019L, 2019L, 2019L, 2019L, 2019L, 
2019L, 2019L, 2019L, 2019L, 2019L, 2019L, 2019L, 2019L, 2019L, 
2019L, 2020L, 2020L, 2020L, 2020L, 2020L, 2020L, 2020L, 2020L, 
2020L, 2020L, 2020L, 2020L, 2020L, 2020L, 2020L, 2020L, 2020L, 
2020L, 2020L, 2020L, 2020L), year_2 = c(2000L, 2001L, 2002L, 
2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 
2012L, 2013L, 2014L, 2015L, 2016L, 2017L, 2018L, 2019L, 2020L, 
2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 
2009L, 2010L, 2011L, 2012L, 2013L, 2014L, 2015L, 2016L, 2017L, 
2018L, 2019L, 2020L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 
2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 2012L, 2013L, 2014L, 
2015L, 2016L, 2017L, 2018L, 2019L, 2020L, 2000L, 2001L, 2002L, 
2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 
2012L, 2013L, 2014L, 2015L, 2016L, 2017L, 2018L, 2019L, 2020L, 
2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 
2009L, 2010L, 2011L, 2012L, 2013L, 2014L, 2015L, 2016L, 2017L, 
2018L, 2019L, 2020L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 
2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 2012L, 2013L, 2014L, 
2015L, 2016L, 2017L, 2018L, 2019L, 2020L, 2000L, 2001L, 2002L, 
2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 
2012L, 2013L, 2014L, 2015L, 2016L, 2017L, 2018L, 2019L, 2020L, 
2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 
2009L, 2010L, 2011L, 2012L, 2013L, 2014L, 2015L, 2016L, 2017L, 
2018L, 2019L, 2020L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 
2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 2012L, 2013L, 2014L, 
2015L, 2016L, 2017L, 2018L, 2019L, 2020L, 2000L, 2001L, 2002L, 
2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 
2012L, 2013L, 2014L, 2015L, 2016L, 2017L, 2018L, 2019L, 2020L, 
2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 
2009L, 2010L, 2011L, 2012L, 2013L, 2014L, 2015L, 2016L, 2017L, 
2018L, 2019L, 2020L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 
2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 2012L, 2013L, 2014L, 
2015L, 2016L, 2017L, 2018L, 2019L, 2020L, 2000L, 2001L, 2002L, 
2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 
2012L, 2013L, 2014L, 2015L, 2016L, 2017L, 2018L, 2019L, 2020L, 
2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 
2009L, 2010L, 2011L, 2012L, 2013L, 2014L, 2015L, 2016L, 2017L, 
2018L, 2019L, 2020L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 
2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 2012L, 2013L, 2014L, 
2015L, 2016L, 2017L, 2018L, 2019L, 2020L, 2000L, 2001L, 2002L, 
2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 
2012L, 2013L, 2014L, 2015L, 2016L, 2017L, 2018L, 2019L, 2020L, 
2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 
2009L, 2010L, 2011L, 2012L, 2013L, 2014L, 2015L, 2016L, 2017L, 
2018L, 2019L, 2020L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 
2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 2012L, 2013L, 2014L, 
2015L, 2016L, 2017L, 2018L, 2019L, 2020L, 2000L, 2001L, 2002L, 
2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 
2012L, 2013L, 2014L, 2015L, 2016L, 2017L, 2018L, 2019L, 2020L, 
2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 
2009L, 2010L, 2011L, 2012L, 2013L, 2014L, 2015L, 2016L, 2017L, 
2018L, 2019L, 2020L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 
2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 2012L, 2013L, 2014L, 
2015L, 2016L, 2017L, 2018L, 2019L, 2020L), name_count = c(0L, 
0L, 1L, 3L, 1L, 1L, 4L, 1L, 3L, 2L, 3L, 3L, 1L, 2L, 4L, 0L, 4L, 
4L, 3L, 1L, 1L, 3L, 1L, 2L, 5L, 2L, 2L, 1L, 0L, 3L, 2L, 1L, 4L, 
0L, 2L, 4L, 2L, 3L, 4L, 2L, 2L, 1L, 2L, 2L, 1L, 4L, 3L, 2L, 2L, 
0L, 0L, 0L, 3L, 2L, 2L, 2L, 1L, 3L, 3L, 2L, 3L, 6L, 1L, 1L, 1L, 
1L, 3L, 2L, 1L, 2L, 4L, 2L, 3L, 1L, 5L, 1L, 3L, 1L, 1L, 0L, 0L, 
1L, 1L, 2L, 3L, 6L, 3L, 1L, 3L, 1L, 1L, 2L, 3L, 1L, 2L, 4L, 1L, 
1L, 3L, 2L, 5L, 4L, 3L, 2L, 5L, 4L, 4L, 2L, 6L, 3L, 5L, 1L, 1L, 
4L, 2L, 2L, 2L, 2L, 2L, 0L, 2L, 4L, 3L, 0L, 3L, 0L, 2L, 2L, 2L, 
3L, 5L, 0L, 1L, 4L, 2L, 2L, 2L, 4L, 7L, 1L, 1L, 1L, 2L, 2L, 0L, 
1L, 2L, 1L, 1L, 2L, 4L, 3L, 2L, 1L, 5L, 3L, 4L, 3L, 5L, 0L, 4L, 
2L, 3L, 1L, 5L, 2L, 3L, 2L, 0L, 5L, 3L, 5L, 2L, 9L, 1L, 3L, 2L, 
2L, 1L, 0L, 1L, 3L, 1L, 3L, 1L, 2L, 4L, 3L, 3L, 1L, 3L, 1L, 2L, 
1L, 3L, 1L, 5L, 2L, 4L, 1L, 2L, 5L, 1L, 3L, 3L, 1L, 5L, 1L, 3L, 
3L, 2L, 2L, 0L, 0L, 5L, 1L, 6L, 6L, 3L, 5L, 3L, 3L, 4L, 1L, 4L, 
1L, 0L, 6L, 3L, 1L, 4L, 1L, 1L, 2L, 5L, 2L, 3L, 2L, 2L, 2L, 4L, 
0L, 1L, 3L, 0L, 3L, 2L, 1L, 4L, 1L, 8L, 4L, 6L, 1L, 3L, 3L, 3L, 
1L, 2L, 1L, 1L, 0L, 1L, 4L, 1L, 1L, 1L, 2L, 3L, 3L, 3L, 0L, 1L, 
2L, 4L, 2L, 2L, 3L, 0L, 2L, 4L, 2L, 2L, 1L, 2L, 2L, 1L, 3L, 3L, 
1L, 3L, 2L, 4L, 1L, 1L, 4L, 3L, 5L, 1L, 6L, 1L, 4L, 0L, 4L, 2L, 
0L, 1L, 4L, 2L, 1L, 1L, 3L, 2L, 1L, 2L, 3L, 2L, 3L, 3L, 1L, 2L, 
3L, 1L, 0L, 4L, 2L, 2L, 1L, 3L, 3L, 2L, 1L, 1L, 0L, 1L, 3L, 2L, 
2L, 5L, 0L, 3L, 3L, 3L, 3L, 1L, 1L, 6L, 2L, 2L, 4L, 2L, 6L, 1L, 
5L, 2L, 2L, 1L, 2L, 2L, 0L, 0L, 1L, 2L, 3L, 2L, 4L, 0L, 6L, 1L, 
0L, 0L, 2L, 3L, 7L, 2L, 1L, 2L, 2L, 0L, 1L, 2L, 1L, 1L, 3L, 1L, 
1L, 4L, 2L, 6L, 2L, 1L, 4L, 5L, 2L, 3L, 4L, 3L, 2L, 3L, 7L, 2L, 
3L, 4L, 2L, 2L, 2L, 2L, 1L, 3L, 2L, 0L, 0L, 2L, 0L, 0L, 0L, 1L, 
0L, 2L, 0L, 2L, 2L, 2L, 1L, 0L, 0L, 2L, 3L, 4L, 7L, 3L, 3L, 1L, 
1L, 1L, 3L, 2L, 2L, 1L, 4L, 2L)), row.names = c(NA, -441L), class = "data.frame")

I made this into a proper table in R (using the following answer: https://stackoverflow.com/a/79450212/28702910):

library(tidyr)
library(kableExtra)

z |> 
  pivot_wider(names_from = "year_2", values_from = name_count) |> 
  kable() |> 
  kable_styling(c("striped")) |>
  add_header_above(c(" ", "year_2" = 21), align = "l")

Is it possible to do "conditional color formatting," with a gradient based on the value of the cell, as is done in Microsoft Excel?

For example, is it possible to shade individual cells as darker colors of red if the number in the cell is higher ... and remain whiter if the number is lower?

Answer 1

This is a tricky question and the comments give great advice, but none of the resources seemed to tackle your exact question regarding a gradient (please correct me if I'm wrong).

There may be more elegant solutions, but I've come up with a multi-step way to color the background in a gradient based on the value while keeping the display as a kable table (i.e., not a heat map figure). I've broken it up into a few steps for ease of reading:

library(dplyr)
library(tidyr)
library(kableExtra)
library(scales)

# Wide just for ease of reading the answer
z_wide <- z %>%
  pivot_wider(names_from = "year_2", 
              values_from = name_count)

minmax <- range(z_wide[-1]) # get min and max of all data, for gradient

# helper function to define gradient
bg_color <- function(x, min, max){
  norm <- (x - min) / (max - min)
  colorRampPalette(c("white", "red"))(100)[ceiling(norm * 99) + 1]
}

# assign data for ease of reading this answer
z_wide_bg <- z_wide %>%
  mutate(across(-year_1, ~ cell_spec(., 
                                     background = bg_color(as.numeric(.), minmax[1], minmax[2]),
                                     format = "html")))

# code to make table
z_wide_bg %>%
  kable("html", escape = FALSE, align = "c") %>%
  kable_styling("striped") %>%
  add_header_above(c(" " = 1, "year_2" = 21), align = "l")

Output:

Image-based approaches

I don't think this is what you want, but for completeness, if you were just worried about visualizing this you could make a figure, of course, without any transformation of your z data:

library(ggplot2) 
ggplot(z, aes(year_1, year_2)) +
  theme_classic() +
  geom_tile(aes(fill = name_count)) +
  geom_text(aes(label = name_count)) +
  scale_fill_gradient(low = "white", high = "red") +
  scale_x_continuous(breaks = seq(2000, 2020, 1)) +
  scale_y_continuous(breaks = seq(2000, 2020, 1))

Output:

Or in base R using image, inspired by @Friede's comment:

yrs <- 2000:2020
ccols <- colorRampPalette(c("white", "red"))(100)

m <- matrix(z$name_count, nrow = length(yrs), ncol = length(yrs), byrow = TRUE)

image(x = yrs, y = yrs, z = m, col = ccols,
      xlab = "year_1", ylab = "year_2", xaxt = "n", yaxt = "n")
axis(1, at = yrs)
axis(2, at = yrs)

for(i in seq_along(yrs)) {
  for(j in seq_along(yrs)) {
    text(x = yrs[i], y = yrs[j], labels = m[i, j])
  }
}

Although note that in the figure-based approaches, the orientation is changes vs your desired output and you may want to tweak to fit your exact needs/aesthetics.