Kable column_spec inconsistently formatting columns for Shiny app

I am trying to generate some tables for a shiny app that will use kableExtra's column_spec to conditionally format columns to be red if the value is above a certain amount and green if a value is below a certain amount.

Trying to set up a table so that the % change column is colored based on the value, and the coloring is inconsistent with the logic statement to check it. Some of the values that are colored red do not pass the test. What is a better way to add conditional formatting to this chunk, or am I simple?

dput(tuition_formatted)
structure(list(new_report_date = c("06mar2023", "06mar2023", 
"06mar2023", "06mar2023", "06mar2023", "06mar2023", "06mar2023", 
"06mar2023", "06mar2023", "06mar2023", "06mar2023"), mau = c("UAA", 
"UAA", "UAA", "UAA", "UAA", "UAA", "UAA", "UAA", "UAA", "UAA", 
"UAA"), fee_type = c("10_lower level", "20_upper level", "30_graduate level", 
"50_come home to ak", "60_wue", "70_non-res tuition", "79_other tuition", 
"80_course fees", "85_student fees", "90_facilities fee", "95_network fee"
), new_total_amt = c(13105458, 5469326, 1520224, -558076, -779464, 
3197334, 383246, 1720647.7, 2558324.47, 467556, 846054.5), old_report_date = c("28feb2022", 
"28feb2022", "28feb2022", "28feb2022", "28feb2022", "28feb2022", 
"28feb2022", "28feb2022", "28feb2022", "28feb2022", "28feb2022"
), old_total_amt = c(12989214, 6148041, 1602145, -749950, -712114, 
2720196, 247422, 1823408.72, 2591916.1, 464700, 847045), `percent_change(tuition, 4, 6)` = c(0.89492712953994, 
-11.0395327552305, -5.11320760605313, -25.5849056603774, 9.4577553593947, 
17.5405742821473, 54.8956842964652, -5.63565474228949, -1.29601533012584, 
0.614590058102001, -0.116935936107291), new_report_date = c("06mar2023", 
"06mar2023", "06mar2023", "06mar2023", "06mar2023", "06mar2023", 
"06mar2023", "06mar2023", "06mar2023", "06mar2023", "06mar2023"
), mau = c("UAF", "UAF", "UAF", "UAF", "UAF", "UAF", "UAF", "UAF", 
"UAF", "UAF", "UAF"), fee_type = c("10_lower level", "20_upper level", 
"30_graduate level", "50_come home to ak", "60_wue", "70_non-res tuition", 
"79_other tuition", "80_course fees", "85_student fees", "90_facilities fee", 
"95_network fee"), new_total_amt = c(8372256, 4031839, 2955876, 
-147160, -505975, 2134952, -366465, 1066801, 2860756.5, 106944, 
665812.5), old_report_date = c("28feb2022", "28feb2022", "28feb2022", 
"28feb2022", "28feb2022", "28feb2022", "28feb2022", "28feb2022", 
"28feb2022", "28feb2022", "28feb2022"), old_total_amt = c(7505955, 
4060161, 2941323, -141500, -412631, 1996946, -415665.5, 977499.21, 
2787797, 105576, 636478), `percent_change(tuition, 4, 6)` = c(11.5415160362672, 
-0.697558545092177, 0.494777350192413, 4, 22.6216643926414, 6.91085287233606, 
-11.8365608885029, 9.13574037568788, 2.61710232129527, 1.29574903387133, 
4.60887886148461), new_report_date = c("06mar2023", "06mar2023", 
"06mar2023", "06mar2023", "06mar2023", "06mar2023", "06mar2023", 
"06mar2023", "06mar2023", "06mar2023", "06mar2023"), mau = c("UAS", 
"UAS", "UAS", "UAS", "UAS", "UAS", "UAS", "UAS", "UAS", "UAS", 
"UAS"), fee_type = c("10_lower level", "20_upper level", "30_graduate level", 
"50_come home to ak", "60_wue", "70_non-res tuition", "79_other tuition", 
"80_course fees", "85_student fees", "90_facilities fee", "95_network fee"
), new_total_amt = c(1901016, 835566, 516345, -56034, -264012, 
662220, -78339, 190041, 466000, 71881, 135751), old_report_date = c("28feb2022", 
"28feb2022", "28feb2022", "28feb2022", "28feb2022", "28feb2022", 
"28feb2022", "28feb2022", "28feb2022", "28feb2022", "28feb2022"
), old_total_amt = c(1774422, 976848, 688236, -77542, -284217, 
694482, -138915, 180208, 491378, 74207, 141206), `percent_change(tuition, 4, 6)` = c(7.13437953316629, 
-14.4630484988453, -24.9755897686259, -27.7372262773723, -7.10900473933649, 
-4.64547677261614, -43.6065219738689, 5.45647252064281, -5.16465938646012, 
-3.13447518428181, -3.86315029106412)), class = "data.frame", row.names = c(NA, 
-11L))

  # tuition table that is close to "beta ready".
  output$tuitionTable <- function() {
    tuition_formatted %>%
      select(-c("mau", "mau.1", "mau.2", "fee_type.1", "fee_type.2")) %>%
      dplyr::mutate_if(is.numeric,~round(.,2)) %>%
      kbl(booktabs = T, 
          col.names = c("Fee Type", "Fall 2023 Total", "Fall 2022 Total", "% change",
                        "Fall 2023 Total", "Fall 2022 Total", "% change",
                        "Fall 2023 Total", "Fall 2022 Total", "% change"),
          caption = "Tuition and Revenue ($)",
          format.args = list(big.mark = ",")) %>%
      add_header_above(c(" ","School 1" = 3, "School 2" = 3, "School 3" = 3)) %>%
      column_spec(c(4,7,10), color = "white", 
                  background = ifelse(tuition_formatted$`percent_change(tuition, 4, 6)` >= .01, "red", "green")) %>%
      kable_styling("striped", full_width = T, position = "left", font_size = 12)
  }

tuition_formatted is based on a tuition data.frame that gets processed, and I tried changing from using tuition_formatted to the preprocessed data.frame and that has the same inconsistency issues that the above has.

I tried referencing those columns specifically with

      column_spec(c(4,7,10), color = "white", 
                  background = ifelse(tuition[,7] >= .01, "red", "green"))

which also didn't format the columns correctly

I've also tried referencing the original data.frame column explicitly

      column_spec(c(4,7,10), color = "white", 
                  background = ifelse(tuition$`percent_change(tuition, 4, 6)` >= .01, "red", "green"))

and again it didn't process correctly.

I have a feeling the problem happens in the piping since I am piping in a kable object.

Solution

The names in your dput output don't match the names in your call for this kable, so I modified the select statement. Although, that is not going to be necessary for you.

I've split the column_spec call so that one column doesn't determine the colors of all columns. When I did this I did not use names, I used indices since the names I have in the data are inconsistent with the names you've used.

I did not remove your original call for column_spec, I just commented it out, so that you can see the differences.

tuition_formatted %>% select(3:4, 6:7, 11, 13:14, 18, 20:21) %>% 
  dplyr::mutate_if(is.numeric,~round(.,2)) %>%
  kbl(booktabs = T, 
      col.names = c("Fee Type", "Fall 2023 Total", "Fall 2022 Total", "% change",
                    "Fall 2023 Total", "Fall 2022 Total", "% change",
                    "Fall 2023 Total", "Fall 2022 Total", "% change"),
      caption = "Tuition and Revenue ($)",
      format.args = list(big.mark = ",")) %>%
  add_header_above(c(" ","School 1" = 3, "School 2" = 3, "School 3" = 3)) %>%
  column_spec(4, color = "white", 
              background = ifelse(tuition_formatted[, 7] >= .01, "red", "green")) %>% 
  column_spec(7, color = "white",
              background = ifelse(tuition_formatted[, 14] >= .01, "red", "green")) %>% 
  column_spec(10, color = "white",
              background = ifelse(tuition_formatted[, 21] >= .01, "red", "green")) %>% 
  # column_spec(c(4,7,10), color = "white", 
  # background = ifelse(tuition_formatted$`percent_change(tuition, 4, 6)` >= .01, "red", "green")) %>%
  kable_styling("striped", full_width = T, position = "left", font_size = 12)