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
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}
