0

I am new to R and we know in Excel it is very easy to conditionally format a cell (change the background colour) by comparing with another column.

I tried to do this in R using KableExtra. But I could not succeed in fetching value from another row and comparing it. My task was making a cell to appear green, yellow or red by comparing a cell on another column in the same row. 

library(kableExtra)
library(dplyr)
library(knitr)
multirow_spec <- function(x, rows, ...) {
  for (row in rows) 
    x <- kableExtra::row_spec(x, row, ...)
  x
}

x <- knitr::kable(head(mtcars), "html")
multirow_spec(x, c(1, 3, 4), background="red")
x

The above code successfully change the background color of the entire row unconditionally. Now what I want is to change the cells color to green(>mtcars$gear) or yellow(=mtcars$gear) or red ('<'mtcars$gear) based on the value under the column mtcars$gear.

could someone help me plz? TIA

doctshind s
  • 380
  • 3
  • 13

1 Answers1

3

The question as it is written is a bit unclear, however, I'll attempt to demonstrate a solution based on my understanding.

First off, I'm unsure what you are trying to accomplish with the function multirow_spec as the package function row_spec is already vectorised for the option row... though not for background.

Second, I'm assuming that you want the colours to vary based on the previous value in the same column (gear) as that is the only one mentioned in the question. We will modify the column using mutate, case_when, and lag all from . We'll use cell_spec to target the individual cell values.

library(dplyr)
library(knitr)
library(kableExtra)

mtcars %>%
  slice(1:10) %>%
  mutate(
    gear = case_when(
      gear > lag(gear) ~ cell_spec(gear, background = "green"),
      gear == lag(gear) ~ cell_spec(gear, background = "yellow"),
      gear < lag(gear) ~ cell_spec(gear, background = "red")
  )) %>%
  kable("html", escape = FALSE)

N.B. The option escape = FALSE is required so that the html content generated with cell_spec is not escaped.

<table>
  <thead>
    <tr>
      <th style="text-align:right;"> mpg </th>
      <th style="text-align:right;"> cyl </th>
      <th style="text-align:right;"> disp </th>
      <th style="text-align:right;"> hp </th>
      <th style="text-align:right;"> drat </th>
      <th style="text-align:right;"> wt </th>
      <th style="text-align:right;"> qsec </th>
      <th style="text-align:right;"> vs </th>
      <th style="text-align:right;"> am </th>
      <th style="text-align:left;"> gear </th>
      <th style="text-align:right;"> carb </th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td style="text-align:right;"> 21.0 </td>
      <td style="text-align:right;"> 6 </td>
      <td style="text-align:right;"> 160.0 </td>
      <td style="text-align:right;"> 110 </td>
      <td style="text-align:right;"> 3.90 </td>
      <td style="text-align:right;"> 2.620 </td>
      <td style="text-align:right;"> 16.46 </td>
      <td style="text-align:right;"> 0 </td>
      <td style="text-align:right;"> 1 </td>
      <td style="text-align:left;"> NA </td>
      <td style="text-align:right;"> 4 </td>
    </tr>
    <tr>
      <td style="text-align:right;"> 21.0 </td>
      <td style="text-align:right;"> 6 </td>
      <td style="text-align:right;"> 160.0 </td>
      <td style="text-align:right;"> 110 </td>
      <td style="text-align:right;"> 3.90 </td>
      <td style="text-align:right;"> 2.875 </td>
      <td style="text-align:right;"> 17.02 </td>
      <td style="text-align:right;"> 0 </td>
      <td style="text-align:right;"> 1 </td>
      <td style="text-align:left;"> <span style="     border-radius: 4px; padding-right: 4px; padding-left: 4px; background-color: yellow;">4</span> </td>
      <td style="text-align:right;"> 4 </td>
    </tr>
    <tr>
      <td style="text-align:right;"> 22.8 </td>
      <td style="text-align:right;"> 4 </td>
      <td style="text-align:right;"> 108.0 </td>
      <td style="text-align:right;"> 93 </td>
      <td style="text-align:right;"> 3.85 </td>
      <td style="text-align:right;"> 2.320 </td>
      <td style="text-align:right;"> 18.61 </td>
      <td style="text-align:right;"> 1 </td>
      <td style="text-align:right;"> 1 </td>
      <td style="text-align:left;"> <span style="     border-radius: 4px; padding-right: 4px; padding-left: 4px; background-color: yellow;">4</span> </td>
      <td style="text-align:right;"> 1 </td>
    </tr>
    <tr>
      <td style="text-align:right;"> 21.4 </td>
      <td style="text-align:right;"> 6 </td>
      <td style="text-align:right;"> 258.0 </td>
      <td style="text-align:right;"> 110 </td>
      <td style="text-align:right;"> 3.08 </td>
      <td style="text-align:right;"> 3.215 </td>
      <td style="text-align:right;"> 19.44 </td>
      <td style="text-align:right;"> 1 </td>
      <td style="text-align:right;"> 0 </td>
      <td style="text-align:left;"> <span style="     border-radius: 4px; padding-right: 4px; padding-left: 4px; background-color: red;">3</span> </td>
      <td style="text-align:right;"> 1 </td>
    </tr>
    <tr>
      <td style="text-align:right;"> 18.7 </td>
      <td style="text-align:right;"> 8 </td>
      <td style="text-align:right;"> 360.0 </td>
      <td style="text-align:right;"> 175 </td>
      <td style="text-align:right;"> 3.15 </td>
      <td style="text-align:right;"> 3.440 </td>
      <td style="text-align:right;"> 17.02 </td>
      <td style="text-align:right;"> 0 </td>
      <td style="text-align:right;"> 0 </td>
      <td style="text-align:left;"> <span style="     border-radius: 4px; padding-right: 4px; padding-left: 4px; background-color: yellow;">3</span> </td>
      <td style="text-align:right;"> 2 </td>
    </tr>
    <tr>
      <td style="text-align:right;"> 18.1 </td>
      <td style="text-align:right;"> 6 </td>
      <td style="text-align:right;"> 225.0 </td>
      <td style="text-align:right;"> 105 </td>
      <td style="text-align:right;"> 2.76 </td>
      <td style="text-align:right;"> 3.460 </td>
      <td style="text-align:right;"> 20.22 </td>
      <td style="text-align:right;"> 1 </td>
      <td style="text-align:right;"> 0 </td>
      <td style="text-align:left;"> <span style="     border-radius: 4px; padding-right: 4px; padding-left: 4px; background-color: yellow;">3</span> </td>
      <td style="text-align:right;"> 1 </td>
    </tr>
    <tr>
      <td style="text-align:right;"> 14.3 </td>
      <td style="text-align:right;"> 8 </td>
      <td style="text-align:right;"> 360.0 </td>
      <td style="text-align:right;"> 245 </td>
      <td style="text-align:right;"> 3.21 </td>
      <td style="text-align:right;"> 3.570 </td>
      <td style="text-align:right;"> 15.84 </td>
      <td style="text-align:right;"> 0 </td>
      <td style="text-align:right;"> 0 </td>
      <td style="text-align:left;"> <span style="     border-radius: 4px; padding-right: 4px; padding-left: 4px; background-color: yellow;">3</span> </td>
      <td style="text-align:right;"> 4 </td>
    </tr>
    <tr>
      <td style="text-align:right;"> 24.4 </td>
      <td style="text-align:right;"> 4 </td>
      <td style="text-align:right;"> 146.7 </td>
      <td style="text-align:right;"> 62 </td>
      <td style="text-align:right;"> 3.69 </td>
      <td style="text-align:right;"> 3.190 </td>
      <td style="text-align:right;"> 20.00 </td>
      <td style="text-align:right;"> 1 </td>
      <td style="text-align:right;"> 0 </td>
      <td style="text-align:left;"> <span style="     border-radius: 4px; padding-right: 4px; padding-left: 4px; background-color: green;">4</span> </td>
      <td style="text-align:right;"> 2 </td>
    </tr>
    <tr>
      <td style="text-align:right;"> 22.8 </td>
      <td style="text-align:right;"> 4 </td>
      <td style="text-align:right;"> 140.8 </td>
      <td style="text-align:right;"> 95 </td>
      <td style="text-align:right;"> 3.92 </td>
      <td style="text-align:right;"> 3.150 </td>
      <td style="text-align:right;"> 22.90 </td>
      <td style="text-align:right;"> 1 </td>
      <td style="text-align:right;"> 0 </td>
      <td style="text-align:left;"> <span style="     border-radius: 4px; padding-right: 4px; padding-left: 4px; background-color: yellow;">4</span> </td>
      <td style="text-align:right;"> 2 </td>
    </tr>
    <tr>
      <td style="text-align:right;"> 19.2 </td>
      <td style="text-align:right;"> 6 </td>
      <td style="text-align:right;"> 167.6 </td>
      <td style="text-align:right;"> 123 </td>
      <td style="text-align:right;"> 3.92 </td>
      <td style="text-align:right;"> 3.440 </td>
      <td style="text-align:right;"> 18.30 </td>
      <td style="text-align:right;"> 1 </td>
      <td style="text-align:right;"> 0 </td>
      <td style="text-align:left;"> <span style="     border-radius: 4px; padding-right: 4px; padding-left: 4px; background-color: yellow;">4</span> </td>
      <td style="text-align:right;"> 4 </td>
    </tr>
  </tbody>
</table>
Kevin Arseneau
  • 6,186
  • 1
  • 21
  • 40
  • Thank you very much for elaborate answer. Yes, my question is not clear but still you answered...Could you please tell me how can I change the colour of those individual cells rather than entire row? – doctshind s Jun 04 '18 at 08:09
  • @doctshinds, please see edit above. Note this is also covered very well in the package [vignette](https://haozhu233.github.io/kableExtra/awesome_table_in_html.html). – Kevin Arseneau Jun 04 '18 at 08:50