rlistloopsformattingformattable

Automate customization of a table with formattable package

I have the following reproducible code to create a Retention Table (percentage numbers have been randomized):

data_F <- "https://raw.githubusercontent.com/max9nc9/Temp/main/Example.csv"
data_F <- read.csv(data_F, sep = ";")
colnames(data_F) <- c("Week_age", "User_Retention", "Sign_Up_Date" ,"Date")       

Final_Retention_Table_All <-
  Retention_Table_All %>% select(Date, Sign_Up_Date, User_Retention)%>% 
  spread(Date, User_Retention)
   
Final_Retention_Table_All <-
  Final_Retention_Table_All %>%
  formattable(list(`2021-03-08`= formatter("span", style=~formattable::style(font.weight = "bold", color=ifelse(`2021-03-08`==1, "black", ifelse(`2021-03-08`>=0.45 &`2021-03-08`<1, "#FFD700", ifelse(`2021-03-08`>=0.25 &`2021-03-08`<0.45, "#146609", ifelse(`2021-03-08`>=0.20 &`2021-03-08`<0.25, "#19b504", ifelse(`2021-03-08`>=0.10 &`2021-03-08`<0.20, "#f08902", "red" ))))))),
                   `2021-03-15`= formatter("span", style=~formattable::style(font.weight = "bold", color=ifelse(`2021-03-15`==1, "black", ifelse(`2021-03-15`>=0.45 &`2021-03-15`<1, "#FFD700", ifelse(`2021-03-15`>=0.25 &`2021-03-15`<0.45, "#146609", ifelse(`2021-03-15`>=0.20 &`2021-03-15`<0.25, "#19b504", ifelse(`2021-03-15`>=0.10 &`2021-03-15`<0.20, "#f08902", "red" ))))))),
                   `2021-03-22`= formatter("span", style=~formattable::style(font.weight = "bold", color=ifelse(`2021-03-22`==1, "black", ifelse(`2021-03-22`>=0.45 &`2021-03-22`<1, "#FFD700", ifelse(`2021-03-22`>=0.25 &`2021-03-22`<0.45, "#146609", ifelse(`2021-03-22`>=0.20 &`2021-03-22`<0.25, "#19b504", ifelse(`2021-03-22`>=0.10 &`2021-03-22`<0.20, "#f08902", "red" ))))))),
                   `2021-03-29`= formatter("span", style=~formattable::style(font.weight = "bold", color=ifelse(`2021-03-29`==1, "black", ifelse(`2021-03-29`>=0.45 &`2021-03-29`<1, "#FFD700", ifelse(`2021-03-29`>=0.25 &`2021-03-29`<0.45, "#146609", ifelse(`2021-03-29`>=0.20 &`2021-03-29`<0.25, "#19b504", ifelse(`2021-03-29`>=0.10 &`2021-03-29`<0.20, "#f08902", "red" ))))))),
                   `2021-04-05`= formatter("span", style=~formattable::style(font.weight = "bold", color=ifelse(`2021-04-05`==1, "black", ifelse(`2021-04-05`>=0.45 &`2021-04-05`<1, "#FFD700", ifelse(`2021-04-05`>=0.25 &`2021-04-05`<0.45, "#146609", ifelse(`2021-04-05`>=0.20 &`2021-04-05`<0.25, "#19b504", ifelse(`2021-04-05`>=0.10 &`2021-04-05`<0.20, "#f08902", "red" ))))))),
                   `2021-04-12`= formatter("span", style=~formattable::style(font.weight = "bold", color=ifelse(`2021-04-12`==1, "black", ifelse(`2021-04-12`>=0.45 &`2021-04-12`<1, "#FFD700", ifelse(`2021-04-12`>=0.25 &`2021-04-12`<0.45, "#146609", ifelse(`2021-04-12`>=0.20 &`2021-04-12`<0.25, "#19b504", ifelse(`2021-04-12`>=0.10 &`2021-04-12`<0.20, "#f08902", "red" ))))))),
                   `2021-04-19`= formatter("span", style=~formattable::style(font.weight = "bold", color=ifelse(`2021-04-19`==1, "black", ifelse(`2021-04-19`>=0.45 &`2021-04-19`<1, "#FFD700", ifelse(`2021-04-19`>=0.25 &`2021-04-19`<0.45, "#146609", ifelse(`2021-04-19`>=0.20 &`2021-04-19`<0.25, "#19b504", ifelse(`2021-04-19`>=0.10 &`2021-04-19`<0.20, "#f08902", "red" ))))))),
                   `2021-04-26`= formatter("span", style=~formattable::style(font.weight = "bold", color=ifelse(`2021-04-26`==1, "black", ifelse(`2021-04-26`>=0.45 &`2021-04-26`<1, "#FFD700", ifelse(`2021-04-26`>=0.25 &`2021-04-26`<0.45, "#146609", ifelse(`2021-04-26`>=0.20 &`2021-04-26`<0.25, "#19b504", ifelse(`2021-04-26`>=0.10 &`2021-04-26`<0.20, "#f08902", "red" ))))))),
                   `2021-05-03`= formatter("span", style=~formattable::style(font.weight = "bold", color=ifelse(`2021-05-03`==1, "black", ifelse(`2021-05-03`>=0.45 &`2021-05-03`<1, "#FFD700", ifelse(`2021-05-03`>=0.25 &`2021-05-03`<0.45, "#146609", ifelse(`2021-05-03`>=0.20 &`2021-05-03`<0.25, "#19b504", ifelse(`2021-05-03`>=0.10 &`2021-05-03`<0.20, "#f08902", "red" ))))))),
                   `2021-05-10`= formatter("span", style=~formattable::style(font.weight = "bold", color=ifelse(`2021-05-10`==1, "black", ifelse(`2021-05-10`>=0.45 &`2021-05-10`<1, "#FFD700", ifelse(`2021-05-10`>=0.25 &`2021-05-10`<0.45, "#146609", ifelse(`2021-05-10`>=0.20 &`2021-05-10`<0.25, "#19b504", ifelse(`2021-05-10`>=0.10 &`2021-05-10`<0.20, "#f08902", "red" ))))))),
                   `2021-05-17`= formatter("span", style=~formattable::style(font.weight = "bold", color=ifelse(`2021-05-17`==1, "black", ifelse(`2021-05-17`>=0.45 &`2021-05-17`<1, "#FFD700", ifelse(`2021-05-17`>=0.25 &`2021-05-17`<0.45, "#146609", ifelse(`2021-05-17`>=0.20 &`2021-05-17`<0.25, "#19b504", ifelse(`2021-05-17`>=0.10 &`2021-05-17`<0.20, "#f08902", "red" ))))))),
                   `2021-05-24`= formatter("span", style=~formattable::style(font.weight = "bold", color=ifelse(`2021-05-24`==1, "black", ifelse(`2021-05-24`>=0.45 &`2021-05-24`<1, "#FFD700", ifelse(`2021-05-24`>=0.25 &`2021-05-24`<0.45, "#146609", ifelse(`2021-05-24`>=0.20 &`2021-05-24`<0.25, "#19b504", ifelse(`2021-05-24`>=0.10 &`2021-05-24`<0.20, "#f08902", "red" ))))))),
                   `2021-05-31`= formatter("span", style=~formattable::style(font.weight = "bold", color=ifelse(`2021-05-31`==1, "black", ifelse(`2021-05-31`>=0.45 &`2021-05-31`<1, "#FFD700", ifelse(`2021-05-31`>=0.25 &`2021-05-31`<0.45, "#146609", ifelse(`2021-05-31`>=0.20 &`2021-05-31`<0.25, "#19b504", ifelse(`2021-05-31`>=0.10 &`2021-05-31`<0.20, "#f08902", "red" ))))))),
                   `2021-06-07`= formatter("span", style=~formattable::style(font.weight = "bold", color=ifelse(`2021-06-07`==1, "black", ifelse(`2021-06-07`>=0.45 &`2021-06-07`<1, "#FFD700", ifelse(`2021-06-07`>=0.25 &`2021-06-07`<0.45, "#146609", ifelse(`2021-06-07`>=0.20 &`2021-06-07`<0.25, "#19b504", ifelse(`2021-06-07`>=0.10 &`2021-06-07`<0.20, "#f08902", "red" ))))))),
                   `2021-06-14`= formatter("span", style=~formattable::style(font.weight = "bold", color=ifelse(`2021-06-14`==1, "black", ifelse(`2021-06-14`>=0.45 &`2021-06-14`<1, "#FFD700", ifelse(`2021-06-14`>=0.25 &`2021-06-14`<0.45, "#146609", ifelse(`2021-06-14`>=0.20 &`2021-06-14`<0.25, "#19b504", ifelse(`2021-06-14`>=0.10 &`2021-06-14`<0.20, "#f08902", "red" ))))))),
                   `2021-06-21`= formatter("span", style=~formattable::style(font.weight = "bold", color=ifelse(`2021-06-21`==1, "black", ifelse(`2021-06-21`>=0.45 &`2021-06-21`<1, "#FFD700", ifelse(`2021-06-21`>=0.25 &`2021-06-21`<0.45, "#146609", ifelse(`2021-06-21`>=0.20 &`2021-06-21`<0.25, "#19b504", ifelse(`2021-06-21`>=0.10 &`2021-06-21`<0.20, "#f08902", "red" ))))))),
                   `2021-06-28`= formatter("span", style=~formattable::style(font.weight = "bold", color=ifelse(`2021-06-28`==1, "black", ifelse(`2021-06-28`>=0.45 &`2021-06-28`<1, "#FFD700", ifelse(`2021-06-28`>=0.25 &`2021-06-28`<0.45, "#146609", ifelse(`2021-06-28`>=0.20 &`2021-06-28`<0.25, "#19b504", ifelse(`2021-06-28`>=0.10 &`2021-06-28`<0.20, "#f08902", "red" ))))))),
                   `2021-07-05`= formatter("span", style=~formattable::style(font.weight = "bold", color=ifelse(`2021-07-05`==1, "black", ifelse(`2021-07-05`>=0.45 &`2021-07-05`<1, "#FFD700", ifelse(`2021-07-05`>=0.25 &`2021-07-05`<0.45, "#146609", ifelse(`2021-07-05`>=0.20 &`2021-07-05`<0.25, "#19b504", ifelse(`2021-07-05`>=0.10 &`2021-07-05`<0.20, "#f08902", "red" ))))))),
                   `2021-07-12`= formatter("span", style=~formattable::style(font.weight = "bold", color=ifelse(`2021-07-12`==1, "black", ifelse(`2021-07-12`>=0.45 &`2021-07-12`<1, "#FFD700", ifelse(`2021-07-12`>=0.25 &`2021-07-12`<0.45, "#146609", ifelse(`2021-07-12`>=0.20 &`2021-07-12`<0.25, "#19b504", ifelse(`2021-07-12`>=0.10 &`2021-07-12`<0.20, "#f08902", "red" ))))))),
                   `2021-07-19`= formatter("span", style=~formattable::style(font.weight = "bold", color=ifelse(`2021-07-19`==1, "black", ifelse(`2021-07-19`>=0.45 &`2021-07-19`<1, "#FFD700", ifelse(`2021-07-19`>=0.25 &`2021-07-19`<0.45, "#146609", ifelse(`2021-07-19`>=0.20 &`2021-07-19`<0.25, "#19b504", ifelse(`2021-07-19`>=0.10 &`2021-07-19`<0.20, "#f08902", "red" ))))))),
                   `2021-07-26`= formatter("span", style=~formattable::style(font.weight = "bold", color=ifelse(`2021-07-26`==1, "black", ifelse(`2021-07-26`>=0.45 &`2021-07-26`<1, "#FFD700", ifelse(`2021-07-26`>=0.25 &`2021-07-26`<0.45, "#146609", ifelse(`2021-07-26`>=0.20 &`2021-07-26`<0.25, "#19b504", ifelse(`2021-07-26`>=0.10 &`2021-07-26`<0.20, "#f08902", "red" ))))))),
                   `2021-08-02`= formatter("span", style=~formattable::style(font.weight = "bold", color=ifelse(`2021-08-02`==1, "black", ifelse(`2021-08-02`>=0.45 &`2021-08-02`<1, "#FFD700", ifelse(`2021-08-02`>=0.25 &`2021-08-02`<0.45, "#146609", ifelse(`2021-08-02`>=0.20 &`2021-08-02`<0.25, "#19b504", ifelse(`2021-08-02`>=0.10 &`2021-08-02`<0.20, "#f08902", "red" ))))))),
                   `2021-08-09`= formatter("span", style=~formattable::style(font.weight = "bold", color=ifelse(`2021-08-09`==1, "black", ifelse(`2021-08-09`>=0.45 &`2021-08-09`<1, "#FFD700", ifelse(`2021-08-09`>=0.25 &`2021-08-09`<0.45, "#146609", ifelse(`2021-08-09`>=0.20 &`2021-08-09`<0.25, "#19b504", ifelse(`2021-08-09`>=0.10 &`2021-08-09`<0.20, "#f08902", "red" ))))))),
                   `2021-08-16`= formatter("span", style=~formattable::style(font.weight = "bold", color=ifelse(`2021-08-16`==1, "black", ifelse(`2021-08-16`>=0.45 &`2021-08-16`<1, "#FFD700", ifelse(`2021-08-16`>=0.25 &`2021-08-16`<0.45, "#146609", ifelse(`2021-08-16`>=0.20 &`2021-08-16`<0.25, "#19b504", ifelse(`2021-08-16`>=0.10 &`2021-08-16`<0.20, "#f08902", "red" ))))))),
                   `2021-08-23`= formatter("span", style=~formattable::style(font.weight = "bold", color=ifelse(`2021-08-23`==1, "black", ifelse(`2021-08-23`>=0.45 &`2021-08-23`<1, "#FFD700", ifelse(`2021-08-23`>=0.25 &`2021-08-23`<0.45, "#146609", ifelse(`2021-08-23`>=0.20 &`2021-08-23`<0.25, "#19b504", ifelse(`2021-08-23`>=0.10 &`2021-08-23`<0.20, "#f08902", "red" ))))))),
                   `2021-08-30`= formatter("span", style=~formattable::style(font.weight = "bold", color=ifelse(`2021-08-30`==1, "black", ifelse(`2021-08-30`>=0.45 &`2021-08-30`<1, "#FFD700", ifelse(`2021-08-30`>=0.25 &`2021-08-30`<0.45, "#146609", ifelse(`2021-08-30`>=0.20 &`2021-08-30`<0.25, "#19b504", ifelse(`2021-08-30`>=0.10 &`2021-08-30`<0.20, "#f08902", "red" ))))))),
                   `2021-09-06`= formatter("span", style=~formattable::style(font.weight = "bold", color=ifelse(`2021-09-06`==1, "black", ifelse(`2021-09-06`>=0.45 &`2021-09-06`<1, "#FFD700", ifelse(`2021-09-06`>=0.25 &`2021-09-06`<0.45, "#146609", ifelse(`2021-09-06`>=0.20 &`2021-09-06`<0.25, "#19b504", ifelse(`2021-09-06`>=0.10 &`2021-09-06`<0.20, "#f08902", "red" ))))))),
                   `2021-09-13`= formatter("span", style=~formattable::style(font.weight = "bold", color=ifelse(`2021-09-13`==1, "black", ifelse(`2021-09-13`>=0.45 &`2021-09-13`<1, "#FFD700", ifelse(`2021-09-13`>=0.25 &`2021-09-13`<0.45, "#146609", ifelse(`2021-09-13`>=0.20 &`2021-09-13`<0.25, "#19b504", ifelse(`2021-09-13`>=0.10 &`2021-09-13`<0.20, "#f08902", "red" ))))))),
                   `2021-09-20`= formatter("span", style=~formattable::style(font.weight = "bold", color=ifelse(`2021-09-20`==1, "black", ifelse(`2021-09-20`>=0.45 &`2021-09-20`<1, "#FFD700", ifelse(`2021-09-20`>=0.25 &`2021-09-20`<0.45, "#146609", ifelse(`2021-09-20`>=0.20 &`2021-09-20`<0.25, "#19b504", ifelse(`2021-09-20`>=0.10 &`2021-09-20`<0.20, "#f08902", "red" ))))))),
                   `2021-09-27`= formatter("span", style=~formattable::style(font.weight = "bold", color=ifelse(`2021-09-27`==1, "black", ifelse(`2021-09-27`>=0.45 &`2021-09-27`<1, "#FFD700", ifelse(`2021-09-27`>=0.25 &`2021-09-27`<0.45, "#146609", ifelse(`2021-09-27`>=0.20 &`2021-09-27`<0.25, "#19b504", ifelse(`2021-09-27`>=0.10 &`2021-09-27`<0.20, "#f08902", "red" )))))))
                   
  ))

With this version, to keep the formatting for all rows/columns, I have to add a new line everytime an addionnal week passed, is there a way to automate this and not having to manually add a new line for every new week? Thanks.

Solution

  • In a first step you can generalize your formatting into an own formatter and do an area formatting in a second step.

    Area formatting applys a formatter to a defined numbers of rows or columns. In general area formatting is done with the following syntax: area(row, col) ~ formatter. Therefore you may need to know the columnindex or the columnnames of the first and the last columns.

    Data

    library(formattable)
library(lubridate)
library(tidyverse)

df <- data.frame(Date = c("2021-01-01",
                          "2021-01-02",
                          "2021-01-03",
                          "2021-01-04",
                          "2021-01-05"),
                 Sign_Up_Date = c("2021-01-01"), 
                 User_Retention = c(1, .5, .3, .2, .1)) %>%
  spread(Date, User_Retention)

    Create own formatter

    
own_formatter <- formatter("span", 
                           style = x ~ style(font.weight = "bold",
                                                       color = ifelse(x == 1, "black", 
                                                                      ifelse(x >=0.45 & x <1, "#FFD700", 
                                                                             ifelse(x >=0.25 & x < 0.45, "#146609",
                                                                                    ifelse(x >= 0.20 & x < 0.25, "#19b504", 
                                                                                           ifelse(x >= 0.10 & x < 0.20, "#f08902", "red" )))))))

    Area formatting

    df %>% 
  formattable(list(area(col = 2:ncol(df)) ~ own_formatter))

    Output enter image description here