4

I have just learnt KableExtra and know to how to use conditionally formating the entire column using mutate() as explained in the doc using mutate such as:

mutate(
   mpg = cell_spec(mpg, background = ifelse(mpg > 20, "red", "blue"))
)

But what I don't know is, how to change background colours of only certain rows in each column whilst all rows are being displayed.

For example, my data:

df <- data.frame( region1 = c("A", sample(1:5,3)),
                  region2 = c("B", sample(1:5,3)),
                  region3 = c("C", sample(1:5,3)),
                  region4 = c("A", sample(1:5,3)) )

Now I want to format only second and third row. I dont want to change the color background of first and last row. These second and third row should be 'red' when above 1, or yellow when equal to 1 or green when below 1.

Some one could help me this?

doctshind s
  • 380
  • 3
  • 13
  • Will only the first row be letters and all other rows be numeric? – Dan Jun 03 '18 at 11:42
  • I needed to display a single table where few rows are strings and rest are numbers. I had to format numers in the different columns. I dont know how to handle dataframe with mixed type. Should I keep them serperately? If so how will I display them as a single table..:-( – doctshind s Jun 03 '18 at 16:09
  • I try to adhere to the tidy data format (see this [PDF](http://vita.had.co.nz/papers/tidy-data.html) for Hadley Wickham's paper on the subject) – in short, rows are observations, while columns are variables – so I would keep letters and numbers together if they refer to the same variable. – Dan Jun 04 '18 at 11:19

2 Answers2

4

Here's an example that ignores the first and last rows and colours according to value, like you say, but ignores letters.

First, I load the libraries.

# Load libraries
library(knitr)
library(kableExtra)

Next, I create a dummy data frame.

# Create data frame   
df <- data.frame( region1 = c(sample(c(-5:5, letters[1:5]), 10, replace = TRUE)),
                  region2 = c(sample(c(-5:5, letters[1:5]), 10, replace = TRUE)),
                  region3 = c(sample(c(-5:5, letters[1:5]), 10, replace = TRUE)),
                  region4 = c(sample(c(-5:5, letters[1:5]), 10, replace = TRUE)), stringsAsFactors = FALSE )

Here, I define the function for formatting cells. I ignore the first and last rows and check if the character is a letter or number, then colour accordingly.

foo <- function(x, n, nmax){
  cell_spec(x, background = ifelse(is.na(as.numeric(x)), "white",
                                   ifelse(n == nmax | n == 1, "white",
                                          ifelse(x > 1, "red",
                                                 ifelse(x < 1, "green", "yellow")))))
}

Finally, I apply the function.

df %>% 
  mutate_all(funs(foo(., n = row_number(), nmax = n()))) %>% 
  kable(escape = FALSE) %>% 
  kable_styling()
Dan
  • 11,370
  • 4
  • 43
  • 68
  • It is just fabulous. I have learnt and extended as per your help. Thanks a lot for saving my time. I was about to give up. Somebody told coding is poetry. But I see that in your function usage! thanks again! – doctshind s Jun 06 '18 at 07:52
0

That's not a good design for a dataframe: columns need to be all one type, so your numbers will be coerced to character.

Nevertheless, you can do what you ask for as follows.

fixcol <- function(col) {
  x <- as.numeric(col[2:3])
  x <- cell_spec(x, background = ifelse(x > 1, "red", ifelse(x == 1, "yellow", "green")))
  col[2:3] <- x
  col
}

df <- as.data.frame(lapply(df, fixcol))
kable(df, escape = FALSE)
user2554330
  • 37,248
  • 4
  • 43
  • 90