Issue with gt table color fill

cholo.trem · February 28, 2020, 12:09am

Hello,
I have created a gt table using the function data_color to fill in the cells with a palette. However, I do not want to color the final row, which in most cases reflects the n of the column, while the other rows reflect percentages.This completely distorts my table as I get one cell per column filled with a dark red color while the rest are filled with a light red shade. I would greatly appreciate any advice on how to solve this issue. This is the code I used for the table, I have attached a picture of it at the end of the post:

CCAA = c("Madrid", "Cataluña", "* Barcelona", "Andalucía", "C.Valenciana",
         "País Vasco", "Galicia", "Castilla y León", "Castilla-La Mancha", "Aragón",
         "Murcia", "Baleares", "Canarias", "Asturias", "Navarra", "Cantabria",
         "Extremadura", "Rioja", "Ceuta", "Melilla", "TOTAL")
total1 <- c(26.4, 15.4, 12.6, 10.9, 7.8, 7.2, 6.5, 4.4, 2.8, 2.8, 
            2.7, 2.6, 2.5, 2.1, 1.8, 1.3, 1.2, 0.7, 0.5, 0.4, 398053)
hombres1 <- c(25.7, 20.5, 15.8, 10.5, 7.2, 6.4, 3.1, 3.1, 1.9, 1.6, 
              2.9, 8.2, 3.7, 1.7, 1.3, 1.0, 0.8, 0.4, 0.5, 0.3, 17832)
mujeres1 <- c(26.5, 15.1, 12.5, 10.9, 7.9, 7.2, 6.7, 4.5, 2.9, 2.8,
              2.8, 2.3, 2.5, 2.1, 1.8, 1.3, 1.2, 0.7, 0.5, 0.4, 380203)
total2<- c(35.6, 18.0, 14.7, 8.0, 7.5, 6.5, 1.8, 3.0, 2.1, 3.6, 2.6,
          2.8, 1.5, 1.3, 1.5, 1.0, 0.4, 0.8, 1.2, 0.9, 168823)
hombres2 <- c(28.3, 23.5, 18.1, 9.6, 7.1, 6.4, 1.4, 2.3, 1.2, 1.8, 
              2.2, 8.4, 2.6, 1.3, 1.0, 0.9, 0.4, 0.4, 0.8, 0.5, 10141)
mujeres2 <- c(36.0, 17.7, 14.5, 7.9, 7.5, 6.5, 1.9, 3.1, 2.2, 3.8,
              2.6, 2.4, 1.4, 1.3, 1.6, 1.0, 0.4, 0.8, 1.2, 1.0, 158682)
total3 <- c(57.0, 49.7, 49.5, 31.1, 40.3, 38.4,12.0, 28.9, 31.6, 55.5,
            39.6,45.7,25.5,25.6,36.3,31.1,15.8,50.2,97.9,95.0,42.4)
hombres3 <- c(62.6,65.2,65.0,52.1,56.0,56.8,24.9,41.8,35.4,61.6,
              61.7,58.3,40.0,43.1,45.0,48.8,28.1,50.8,98.8,94.7,56.9)
mujeres3 <- c(56.8,48.8,48.6,30.1,39.6,37.7,11.7,28.5,31.5,55.4,
              38.9,43.6,24.5,24.9,36.0,30.4,15.5,50.2,97.8,95.0,41.7)

dat <- data.frame(CCAA, total1, hombres1, mujeres1, total2, hombres2, mujeres2, total3, hombres3, mujeres3)




dat  %>% 
  gt() %>%
  tab_spanner(
    label = "Afiliados en %",
    columns = vars(total1,hombres1,mujeres1)) %>%
  tab_spanner(
    label = "Afiliados extranjeros en %",
    columns = vars(total2, hombres2, mujeres2)) %>%
  tab_spanner(
    label = "Afiliados extranjeros / afiliados en %",
    columns = vars(total3, hombres3, mujeres3)) %>% 
  cols_label(total1 = "Total",
             hombres1 ="Hombres",
             mujeres1="Mujeres",
             total2="Total",
             hombres2="Hombres",
             mujeres2="Mujeres",
             total3="Total",
             hombres3="Hombres",
             mujeres3="Mujeres") %>%  
  data_color(columns=vars(total1, hombres1, mujeres1,total2, hombres2, mujeres2,total3, hombres3, mujeres3),
             colors = scales::col_numeric(
               palette = as.character(paletteer::paletteer_d(palette = "ggsci::red_material")),
               domain = NULL))

nirgrahamuk · February 28, 2020, 1:27am

I eliminated colour on the final row:



dat %>%
  gt() %>%
  tab_spanner(
    label = "Afiliados en %",
    columns = vars(total1, hombres1, mujeres1)
  ) %>%
  tab_spanner(
    label = "Afiliados extranjeros en %",
    columns = vars(total2, hombres2, mujeres2)
  ) %>%
  tab_spanner(
    label = "Afiliados extranjeros / afiliados en %",
    columns = vars(total3, hombres3, mujeres3)
  ) %>%
  cols_label(
    total1 = "Total",
    hombres1 = "Hombres",
    mujeres1 = "Mujeres",
    total2 = "Total",
    hombres2 = "Hombres",
    mujeres2 = "Mujeres",
    total3 = "Total",
    hombres3 = "Hombres",
    mujeres3 = "Mujeres"
  ) %>%
  data_color(
    columns = vars(total1, hombres1, mujeres1, total2, hombres2, mujeres2, total3, hombres3, mujeres3),
    colors = scales::col_numeric(
      palette = as.character(paletteer::paletteer_d(palette = "ggsci::red_material")),
      domain = NULL
    )
  ) %>%
  tab_style(
    style = cell_fill(color = "white"),
    locations = cells_body(rows = CCAA == "TOTAL")
  ) %>%
  tab_style(
    style = cell_text(color = "black"),
    locations = cells_body(rows = CCAA == "TOTAL")
  )

cholo.trem · February 28, 2020, 7:00am

The problem with this solution is that while it eliminates color in the final row, the other rows are still the same light red color. What I would like is for dat_color to disregard the final row in terms of coloring.

nirgrahamuk · February 28, 2020, 10:02am

What colour would that make the final row?

cholo.trem · February 28, 2020, 11:19am

It should make that row white. I think I may not be explaining myself properly. I'll try to do so better with an example. For instance, if we don't take into account the final row of the first colored column ("Afiliados en %" -"Total"), the highest value is 26.4(Madrid), while the lowest is 0.4(Melilla). What I want is for Madrid to be in a dark shade of red, and Melilla in a light shade, with the remaining values shades of red that are in between. At the moment, as the value in the final row is so much higher than all the other values, it adopts the darkest shade of red with all the other values adopting the lightest shade. Using the tab_style function makes the final row white, but all the other rows remain the same shade of light red.

nirgrahamuk · February 28, 2020, 11:28am

I think I understand. It's a matter of contrast, the last row having affected the presentation of the rows above. Unfortunately data_color() seems a flawed approach as the documentation for it says explicitly that it works over all rows of selected columns. It may be that other table formatting packages are more suited to your needs. Would you consider switching?

cholo.trem · February 28, 2020, 11:40am

I think switching may indeed be the best option, do you know of any packages that could do this?

nirgrahamuk · February 28, 2020, 11:52am

This seems promising:
https://cran.r-project.org/web/packages/ztable/vignettes/heatmapTable.html

cholo.trem · February 28, 2020, 11:58am

Ok, I will check it out. Thanks a lot for your help!

andresrcs · February 28, 2020, 8:28pm

I don't know what your TOTAL row represents but you can add summary rows with gt so if you can calculate the values from the data you are presenting you could modify this to suit your needs.

CCAA = c("Madrid", "Cataluña", "* Barcelona", "Andalucía", "C.Valenciana",
         "País Vasco", "Galicia", "Castilla y León", "Castilla-La Mancha", "Aragón",
         "Murcia", "Baleares", "Canarias", "Asturias", "Navarra", "Cantabria",
         "Extremadura", "Rioja", "Ceuta", "Melilla", "TOTAL")
total1 <- c(26.4, 15.4, 12.6, 10.9, 7.8, 7.2, 6.5, 4.4, 2.8, 2.8, 
            2.7, 2.6, 2.5, 2.1, 1.8, 1.3, 1.2, 0.7, 0.5, 0.4, 398053)
hombres1 <- c(25.7, 20.5, 15.8, 10.5, 7.2, 6.4, 3.1, 3.1, 1.9, 1.6, 
              2.9, 8.2, 3.7, 1.7, 1.3, 1.0, 0.8, 0.4, 0.5, 0.3, 17832)
mujeres1 <- c(26.5, 15.1, 12.5, 10.9, 7.9, 7.2, 6.7, 4.5, 2.9, 2.8,
              2.8, 2.3, 2.5, 2.1, 1.8, 1.3, 1.2, 0.7, 0.5, 0.4, 380203)
total2<- c(35.6, 18.0, 14.7, 8.0, 7.5, 6.5, 1.8, 3.0, 2.1, 3.6, 2.6,
           2.8, 1.5, 1.3, 1.5, 1.0, 0.4, 0.8, 1.2, 0.9, 168823)
hombres2 <- c(28.3, 23.5, 18.1, 9.6, 7.1, 6.4, 1.4, 2.3, 1.2, 1.8, 
              2.2, 8.4, 2.6, 1.3, 1.0, 0.9, 0.4, 0.4, 0.8, 0.5, 10141)
mujeres2 <- c(36.0, 17.7, 14.5, 7.9, 7.5, 6.5, 1.9, 3.1, 2.2, 3.8,
              2.6, 2.4, 1.4, 1.3, 1.6, 1.0, 0.4, 0.8, 1.2, 1.0, 158682)
total3 <- c(57.0, 49.7, 49.5, 31.1, 40.3, 38.4,12.0, 28.9, 31.6, 55.5,
            39.6,45.7,25.5,25.6,36.3,31.1,15.8,50.2,97.9,95.0,42.4)
hombres3 <- c(62.6,65.2,65.0,52.1,56.0,56.8,24.9,41.8,35.4,61.6,
              61.7,58.3,40.0,43.1,45.0,48.8,28.1,50.8,98.8,94.7,56.9)
mujeres3 <- c(56.8,48.8,48.6,30.1,39.6,37.7,11.7,28.5,31.5,55.4,
              38.9,43.6,24.5,24.9,36.0,30.4,15.5,50.2,97.8,95.0,41.7)

dat <- data.frame(CCAA, total1, hombres1, mujeres1, total2, hombres2, mujeres2, total3, hombres3, mujeres3)

library(gt)


dat %>%
    head(-1) %>% 
    gt(rowname_col = "CCAA") %>%
    tab_spanner(
        label = "Afiliados en %",
        columns = vars(total1,hombres1,mujeres1)) %>%
    tab_spanner(
        label = "Afiliados extranjeros en %",
        columns = vars(total2, hombres2, mujeres2)) %>%
    tab_spanner(
        label = "Afiliados extranjeros / afiliados en %",
        columns = vars(total3, hombres3, mujeres3)) %>% 
    cols_label(total1 = "Total",
               hombres1 ="Hombres",
               mujeres1="Mujeres",
               total2="Total",
               hombres2="Hombres",
               mujeres2="Mujeres",
               total3="Total",
               hombres3="Hombres",
               mujeres3="Mujeres") %>%  
    data_color(columns=vars(total1, hombres1, mujeres1,total2, hombres2, mujeres2,total3, hombres3, mujeres3),
               colors = scales::col_numeric(
                   palette = as.character(paletteer::paletteer_d(palette = "ggsci::red_material")),
                   domain = NULL)) %>%
    summary_rows(columns=vars(total1, hombres1, mujeres1,total2, hombres2, mujeres2,total3, hombres3, mujeres3),
        fns = list(TOTAL = "sum"))

cholo.trem · February 29, 2020, 2:28pm

Thanks for the advice, but TOTAL represents something that can't be calculated with the summary_rows function. I actually found that the best solution is to add the row on Excel after pasting the table. Not a smooth solution but does the trick.

system · March 21, 2020, 2:28pm

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.