R wanting to limit the amount of digits from csv f

Question

There are plenty of threads about people thinking they have lost digits when reading in a csv file, and it's just a digits setting that isn't displaying all of them.

I on the other hand want to round or truncate my incoming data to two decimal places. I am having an issue in Rmarkdown where I can't limit the decimal places after highlighting fields.

This has lead to me attempting to round before the highlighting, but that leads to undesirable results if I go lower than 4 places since I am working with numbers near 0. It'll also throw scientific numbers in from time to time....which just doesn't look clean in my table.

df.csv

     Sample   blank   square   stool   ball       triangle     circle    hammer     dog
 1:    16-ww3 0.00090 0.93100 0.01219 0.00006      0.00606      0.00180 0.00000 0.00003
 2:      17-e 0.00034 0.67452 0.00297 0.00006      0.00357      0.00172 0.00008 0.00001
 3:    21-r9a 0.00186 0.34577 0.01558 0.00020      0.02277      0.00586 0.00009 0.00012
 4:       7-d 0.00003 0.00352 0.01179 0.00003      0.01640      0.56326 0.00349 0.00064
 5:    7401-1 0.00151 0.55153 0.00196 0.00017      0.00055      0.00029 0.00012 0.00000
 6:    7401-2 0.00056 0.50825 0.00433 0.00010      0.00000      0.00008 0.00006 0.00003

Code

library(kableExtra)
library(magrittr)

DF <- read.csv(file="df.csv"), header=TRUE, sep=",",stringsAsFactors = FALSE)

DF[1:nrow(DF), 2:ncol(DF)] <- round(DF[1:nrow(DF), 2:ncol(DF)], 4)

paint <- function(x) {
  ifelse(x < 0.1, "white", ifelse(x < 0.2, "yellow", "red"))
}

DF %<>%
  mutate_if(is.numeric, function(x) {
   cell_spec(x, background = paint(x), format = "latex") 
  })

kable(DF, caption = "Highlighted numbers near zero", digits = 2, format = "latex", booktabs = T, escape = F, longtable = T)%>%
  kable_styling(latex_options = c("striped", "hold_position", "repeat_header", font_size = 6))%>%
  landscape()%>%
  row_spec(0, angle = 45)

Desired output

 numbers formatted like:  0.00  0.01  0.10  1.10  
 AND highlighted as described in the paint function

Answer 1

If I understand correctly what you want, this should work:

First let's convert your numeric columns into character vectors, with the required number of decimal places, using formatC

DF[, -1] = lapply(DF[, -1], formatC, format = 'f', flag='0', digits = 2)

Now we can apply the latex formatting using:

DF[,-1] = lapply(DF[,-1], function(x) cell_spec(x, background = paint(x), format = "latex"))

Note that paint still works on the character vectors, because the comparison to a numeric value coerces them to numeric.

Here's a complete reproducible example, demonstrating this in practice

set.seed(1234)
DF <- data.frame(V1 = sample(letters,10,T), V2 = abs(rnorm(10)), V3 = abs(rnorm(10)))
DF[, -1] = lapply(DF[, -1], formatC, format = 'f', flag='0', digits = 2)
DF[, -1] = lapply(DF[,-1], function(x) cell_spec(x, background = paint(x), format = "latex"))
#    V1                       V2                        V3
# 1   c   \\cellcolor{red}{0.51} \\cellcolor{yellow}{0.11}
# 2   q   \\cellcolor{red}{0.57}    \\cellcolor{red}{0.51}
# 3   p   \\cellcolor{red}{0.55}    \\cellcolor{red}{0.91}
# 4   q   \\cellcolor{red}{0.56}    \\cellcolor{red}{0.84}
# 5   w   \\cellcolor{red}{0.89}    \\cellcolor{red}{2.42}
# 6   q   \\cellcolor{red}{0.48} \\cellcolor{yellow}{0.13}
# 7   a   \\cellcolor{red}{1.00}    \\cellcolor{red}{0.49}
# 8   g   \\cellcolor{red}{0.78}    \\cellcolor{red}{0.44}
# 9   r \\cellcolor{white}{0.06}    \\cellcolor{red}{0.46}
# 10  n   \\cellcolor{red}{0.96}    \\cellcolor{red}{0.69}

kable(DF, caption = "Highlighted numbers near zero", 
  digits = 2, format = "latex", booktabs = T, escape = F, longtable = T) %>%
  kable_styling(latex_options = c("striped", "hold_position", 
    "repeat_header", font_size = 6)) %>%
  landscape() %>%
  row_spec(0, angle = 45)

# \begin{landscape}
# \begin{longtable}{lll}
# \caption{\label{tab:}Highlighted numbers near zero}\\
# \toprule
# \rotatebox{45}{V1} & \rotatebox{45}{V2} & \rotatebox{45}{V3}\\
# \midrule
# \endfirsthead
# \caption[]{Highlighted numbers near zero \textit{(continued)}}\\
# \toprule
# \rotatebox{45}{V1} & \rotatebox{45}{V2} & \rotatebox{45}{V3}\\
# \midrule
# \endhead
# \
# \endfoot
# \bottomrule
# \endlastfoot
# \rowcolor{gray!6}  c & \cellcolor{red}{0.51} & \cellcolor{yellow}{0.11}\\
# q & \cellcolor{red}{0.57} & \cellcolor{red}{0.51}\\
# \rowcolor{gray!6}  p & \cellcolor{red}{0.55} & \cellcolor{red}{0.91}\\
# q & \cellcolor{red}{0.56} & \cellcolor{red}{0.84}\\
# \rowcolor{gray!6}  w & \cellcolor{red}{0.89} & \cellcolor{red}{2.42}\\
# \addlinespace
# q & \cellcolor{red}{0.48} & \cellcolor{yellow}{0.13}\\
# \rowcolor{gray!6}  a & \cellcolor{red}{1.00} & \cellcolor{red}{0.49}\\
# g & \cellcolor{red}{0.78} & \cellcolor{red}{0.44}\\
# \rowcolor{gray!6}  r & \cellcolor{white}{0.06} & \cellcolor{red}{0.46}\\
# n & \cellcolor{red}{0.96} & \cellcolor{red}{0.69}\\*
#   \end{longtable}
# \end{landscape}