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
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 dplyr. 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 = FALSEis required so that the html content generated withcell_specis 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>