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
- grid.arrange's relative height argument didn't works well in RMarkdown
- How apply a function to every element of a matrix?
- Polar plot in R using ggplot2
- Find total length of time in multiple values of a dataset that overlaps with a given period of time
- Faster than R's `simplify2array`?
- What is the most simple way to label a data.table frequency chart in R?
- How to show dates in a secondary x-axis while the primary x-axis is numerical?
- online compiler for r-markdown document
- Unecessarily large column widths in HTML tables published by R Quarto when the table header contains special characters
- Bootstrapping a vector of results, by group in R
- Speeding Up Monte Carlo Simulations for Weather-Dependent Claims
- Is there an R class which allows to list 2 elements but shows only the first of them by default when called?
- Convert day of week number to weekday name in R
- How do you use "<<-" (scoping assignment) in R?
- Using reactivePoll to monitor and process file changes
- How to find out which graph is most similar to another graph?
- ggplot2: rectangular background behind axis text in circular bar plot
- How to simulate button click using rvest
- CMIP6, automating by month
- Covert dummy variables to single categorical in R?
- Animated reducing bar chart with gganimate
- Create an object class matrix/array from data frame
- How to add a vector of names to `sort_by()` in base R
- Among many subfolders, find CSVs starting with a given string and within ZIPs or not, and merge them while adding their names in a new column with R
- How to add a greek letter to an axis label to a 3D plot in plotly?
- bigrquery + dplyr error when using functions and sourcing from separate R script
- Shiny App Error: /v1/applications/ 400 - Validation Error Execution halted
- Rupee Unicode replacement not working in R
- Mutate multiple variable to create multiple new variables
- How to use the Stata equivalent of a foreach loop in R with mutate()