formatting a subset of cells with kable and kableExtra

Question

I am trying to become familiar with rmarkdown with a simple task. However, I'm stuck on a basic step in rendering a table. I would like some row/column combinations to be formatted in currency and some in percents. Consider this simplified setup and dataframe, focusing on the percent problem:

library(tidyverse)
library(kableExtra)
#> 
#> Attaching package: 'kableExtra'
#> The following object is masked from 'package:dplyr':
#> 
#>     group_rows
library(formattable)

year <- c('2014', '2015' , 'pctDiff')
q1 <- c(35288 , 36032 , 0.0211)
q2 <- c(30273 , 29669 , -.0172)
df <- data.frame(year, q1 , q2)
df
#>      year         q1         q2
#> 1    2014 35288.0000 30273.0000
#> 2    2015 36032.0000 29669.0000
#> 3 pctDiff     0.0211    -0.0172

df %>% percent(.[3 , c(2:3)] , 0)
#> Error in as_numeric(x): 'list' object cannot be coerced to type 'double'
percent(df[3 , c(2:3)])
#> [1] 2.11%  -1.72%

I would like the formatting commands to work in a pipe, but my attempt generated an error. The second attempt produced the formats I wanted but isn't practical since the rest of the df isn't there. (Following a stackoverflow post, I also tried using list and unlist, but came up with the same error.) I couldn't find documentation or examples for changing the format in row/col locations. In gt, the formatting task is no problem but I need to knit the rmarkdown code to something other than html, and gt does not produce pretty tables in either pdf or Word yet. Thanks for any help!

user_3460670 · Answer

First, this uses mutate_at to change only the columns that start with "q".

To clarify the ifelse: if the row number of the row is n(), then it must be evaluating the last row, as n() should contain the numeric value of the number of rows total (in this case, 3).

If you want the columns to be mixed and include either "%" or "$", the columns will need to be character.

If it is the third row, then for the TRUE condition, set as.character(percent(.)). This converts the value to a percentage, and makes it a character value.

If it is not the third row (i.e., it is row 1 or 2), then it is the FALSE condition, and it sets to as.character(currency(.)). Similarly, it converts the value to currency, and then makes sure it is a character value.

In both the TRUE and FALSE conditions, the . refers to the starting numeric value (in either q1 or q2 columns).

library(tidyverse)
library(kableExtra)
library(formattable)

df %>%
  mutate_at(vars(starts_with("q")), funs(
    ifelse(row_number() == n(), 
           as.character(percent(.)), 
           as.character(currency(.))))) %>%
  kbl() %>%
  kable_styling()

Output

formatting a subset of cells with kable and kableExtra

1 Answers