Tables

Code - R

~/code/R/

#Download the Austin indicator data set
#Original data set from: https://data.austintexas.gov/City-Government/Imagine-Austin-Indicators/apwj-7zty/data
austinData= data.table::fread('https://raw.githubusercontent.com/lgellis/MiscTutorial/master/Austin/Imagine_Austin_Indicators.csv', data.table=FALSE, header = TRUE, stringsAsFactors = FALSE)
i1 <- austinData %>%
  filter(`Indicator Name` %in% 
           c('Prevalence of Obesity', 'Prevalence of Tobacco Use', 
             'Prevalence of Cardiovascular Disease', 'Prevalence of Diabetes')) %>%
  select(c(`Indicator Name`, `2011`, `2012`, `2013`, `2014`, `2015`, `2016`)) %>%
  mutate (Average = round(rowMeans(
    cbind(`2011`, `2012`, `2013`, `2014`, `2015`, `2016`), na.rm=T),2), 
    `Improvement` = round((`2011`-`2016`)/`2011`*100,2))
prevalence = i1

formattable(prevalence, align = c("l", rep("r", NCOL(prevalence) - 1)))

Indicator Name	2011	2012	2013	2014	2015	2016	Average	Improvement
Prevalence of Obesity	19.1	23.6	23.3	20.5	24.0	23.2	22.28	-21.47
Prevalence of Tobacco Use	17.4	15.0	15.3	12.2	16.6	16.7	15.53	4.02
Prevalence of Cardiovascular Disease	5.0	4.9	1.5	4.4	4.9	6.2	4.48	-24.00
Prevalence of Diabetes	8.0	7.2	9.3	7.2	7.5	10.4	8.27	-30.00

prevalence[, "Improvement"] = prevalence[, "Improvement"] / 100
formattable(prevalence, 
            align = c("l",rep("r", NCOL(prevalence) - 1)),
            list(`Indicator Name` = formatter("span", style = ~ style(color = "grey", font.weight = "bold")), 
                 `Average` = color_bar("#FA614B"), 
                 `Improvement` = percent))

Indicator Name	2011	2012	2013	2014	2015	2016	Average	Improvement
Prevalence of Obesity	19.1	23.6	23.3	20.5	24.0	23.2	22.28	-21.47%
Prevalence of Tobacco Use	17.4	15.0	15.3	12.2	16.6	16.7	15.53	4.02%
Prevalence of Cardiovascular Disease	5.0	4.9	1.5	4.4	4.9	6.2	4.48	-24.00%
Prevalence of Diabetes	8.0	7.2	9.3	7.2	7.5	10.4	8.27	-30.00%

customGreen0 = "#DeF7E9"

customGreen = "#71CA97"

customRed = "#ff7f7f"

formattable(prevalence, align =c("l","c","c","c","c", "c", "c", "c", "r"), list(
  `Indicator Name` = formatter("span", style = ~ style(color = "grey",font.weight = "bold")), 
  `2011`= color_tile(customGreen, customGreen0),
  `2012`= color_tile(customGreen, customGreen0),
  `2013`= color_tile(customGreen, customGreen0),
  `2014`= color_tile(customGreen, customGreen0),
  `2015`= color_tile(customGreen, customGreen0),
  `2016`= color_tile(customGreen, customGreen0)
))

Indicator Name	2011	2012	2013	2014	2015	2016	Average	Improvement
Prevalence of Obesity	19.1	23.6	23.3	20.5	24.0	23.2	22.28	-0.2147
Prevalence of Tobacco Use	17.4	15.0	15.3	12.2	16.6	16.7	15.53	0.0402
Prevalence of Cardiovascular Disease	5.0	4.9	1.5	4.4	4.9	6.2	4.48	-0.2400
Prevalence of Diabetes	8.0	7.2	9.3	7.2	7.5	10.4	8.27	-0.3000

formattable(prevalence, 
            align = c("l",rep("r", NCOL(prevalence) - 1)),
            list(`Indicator Name` = formatter("span", style = ~ style(color = "grey", font.weight = "bold")), 
                 `Average` = color_bar("#FA614B"), 
                 `Improvement` = formatter("span", 
                                           x ~ percent(x / 100),
                                           style = x ~ style(color = ifelse(x < 0, "red", "green")))))

Indicator Name	2011	2012	2013	2014	2015	2016	Average	Improvement
Prevalence of Obesity	19.1	23.6	23.3	20.5	24.0	23.2	22.28	-0.21%
Prevalence of Tobacco Use	17.4	15.0	15.3	12.2	16.6	16.7	15.53	0.04%
Prevalence of Cardiovascular Disease	5.0	4.9	1.5	4.4	4.9	6.2	4.48	-0.24%
Prevalence of Diabetes	8.0	7.2	9.3	7.2	7.5	10.4	8.27	-0.30%

formattable(prevalence, 
            align = c("l",rep("r", NCOL(prevalence) - 1)),
            list(`Indicator Name` = formatter("span", style = ~ style(color = "grey", font.weight = "bold")),
                 `Average` = color_bar("#FA614B"), 
                 `Improvement` = formatter("span",
                                           x ~ icontext(ifelse(x < 0, "ok", "remove"), ifelse(x < 0, "Yes", "No")), 
                                           style = x ~ style(color = ifelse(x < 0, "red", "green")))))

Indicator Name	2011	2012	2013	2014	2015	2016	Average	Improvement
Prevalence of Obesity	19.1	23.6	23.3	20.5	24.0	23.2	22.28	Yes
Prevalence of Tobacco Use	17.4	15.0	15.3	12.2	16.6	16.7	15.53	No
Prevalence of Cardiovascular Disease	5.0	4.9	1.5	4.4	4.9	6.2	4.48	Yes
Prevalence of Diabetes	8.0	7.2	9.3	7.2	7.5	10.4	8.27	Yes

formattable(prevalence, align = c("l",rep("r", NCOL(prevalence) - 1)), 
            list(`Indicator Name` = formatter("span", style = ~ style(color = "grey",font.weight = "bold")), 
                 area(col = 2:7) ~ function(x) percent(x / 100, digits = 0),
                 area(col = 2:7) ~ color_tile("#DeF7E9", "#71CA97")))

Indicator Name	2011	2012	2013	2014	2015	2016	Average	Improvement
Prevalence of Obesity	19.1	23.6	23.3	20.5	24.0	23.2	22.28	-0.2147
Prevalence of Tobacco Use	17.4	15.0	15.3	12.2	16.6	16.7	15.53	0.0402
Prevalence of Cardiovascular Disease	5.0	4.9	1.5	4.4	4.9	6.2	4.48	-0.2400
Prevalence of Diabetes	8.0	7.2	9.3	7.2	7.5	10.4	8.27	-0.3000

custom_color_tile <- function (...) 
{
    formatter("span",
              style = function(x) style(display = "block", 
                                        padding = "0 4px", 
                                        `color` = "white", 
                                        `border-radius` = "4px", 
                                        `background-color` = csscolor(gradient(as.numeric(x), 
                                        ...))))
}
formattable(prevalence, align = "r", list(
  `Indicator Name` = formatter("span", style = ~ style(color = "grey",font.weight = "bold")), 
                     area(col = 2:9) ~ function(x) percent(x / 100, digits = 0),
                     area(col = 2:7) ~ custom_color_tile("#B1CBEB", "#3E7DCC")))

Indicator Name	2011	2012	2013	2014	2015	2016	Average	Improvement
Prevalence of Obesity	19.1	23.6	23.3	20.5	24.0	23.2	22%	-0%
Prevalence of Tobacco Use	17.4	15.0	15.3	12.2	16.6	16.7	16%	0%
Prevalence of Cardiovascular Disease	5.0	4.9	1.5	4.4	4.9	6.2	4%	-0%
Prevalence of Diabetes	8.0	7.2	9.3	7.2	7.5	10.4	8%	-0%

prev.sig = prevalence[, c(1, 6:7)]
prev.sig$z = c(-1.97, .12, 2.2, 2.1)
prev.sig

#                         Indicator Name 2015 2016     z
# 1                Prevalence of Obesity 24.0 23.2 -1.97
# 2            Prevalence of Tobacco Use 16.6 16.7  0.12
# 3 Prevalence of Cardiovascular Disease  4.9  6.2  2.20
# 4               Prevalence of Diabetes  7.5 10.4  2.10

formattable(prev.sig,
            list(z = FALSE,
                 `2016` = formatter("span", 
                 style = ~ style(color = ifelse(`2016` > `2015`, "green", "red")),                                    
                 ~ icontext(sapply(`z`, function(x) if (x < -1.96) "arrow-down" else if (x > 1.96) "arrow-up" else ""), `2016`))))

Indicator Name	2015	2016
Prevalence of Obesity	24.0	23.2
Prevalence of Tobacco Use	16.6	16.7
Prevalence of Cardiovascular Disease	4.9	6.2
Prevalence of Diabetes	7.5	10.4

prev.sig = prevalence[, c(1, 6:7)]
prev.sig$z = c(-1.97, .12, 2.2, 2.1)
formattable(prev.sig, list(z = FALSE))

Indicator Name	2015	2016
Prevalence of Obesity	24.0	23.2
Prevalence of Tobacco Use	16.6	16.7
Prevalence of Cardiovascular Disease	4.9	6.2
Prevalence of Diabetes	7.5	10.4

formattable(prev.sig,
            list(z = FALSE,
                 `2016` = formatter("span", 
                 style = ~ style(color = ifelse(`2016` >`2015`, "green", "red")),                                    
                 ~ icontext(sapply(`z`, function(x) if (x < -1.96) "arrow-down" else if (x > 1.96) "arrow-up" else ""), `2016`))))

Indicator Name	2015	2016
Prevalence of Obesity	24.0	23.2
Prevalence of Tobacco Use	16.6	16.7
Prevalence of Cardiovascular Disease	4.9	6.2
Prevalence of Diabetes	7.5	10.4

sparkline(c(1,2,7,6,5), type = "bar", barColor = "green")

df = data.frame("Type" = c("bar", "line", "bullet", "pie", "tristate", "discrete"),
               Sparkline = c(as.character(htmltools::as.tags(sparkline(c(1,2,7,6,5), type = "bar"))), 
                             as.character(htmltools::as.tags(sparkline(c(1,2,7,6,5), type = "line"))), 
                             as.character(htmltools::as.tags(sparkline(c(1,2,7,6,5), type = "bullet"))), 
                             as.character(htmltools::as.tags(sparkline(c(1,2,7,6,5), type = "pie"))), 
                             as.character(htmltools::as.tags(sparkline(c(-1,0,1,1,1,-1,0,2), type = "tristate"))), 
                             as.character(htmltools::as.tags(sparkline(c(1,2,7,6,5), type = "discrete")))))
out = as.htmlwidget(formattable(df))
out$dependencies = c(out$dependencies, htmlwidgets:::widget_dependencies("sparkline", "sparkline"))
out