#Download the Austin indicator data set for selecting specified co-morbidities as indicator measures
#of increment of illnesses - Health care
#https://data.austintexas.gov/
#Original data set from: https://data.austintexas.gov/City-Government/Imagine-Austin-Indicators/apwj-7zty/data
#Load of the libraries (it assumes previous packages installation)
library(data.table)
library(dplyr)
##
## Attaching package: 'dplyr'
## The following objects are masked from 'package:data.table':
##
## between, first, last
## The following objects are masked from 'package:stats':
##
## filter, lag
## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, union
library(formattable)
library(tidyr)
#Here is the table data manipulation with the help of extra R packages
#Set a few color variables to make our table more visually appealing
customGreen0 = "#DeF7E9"
customGreen = "#71CA97"
customRed = "#ff7f7f"
#Original post showed fread() as a command for dowloading data from Austin
#?fread with this other command can be seen the specification of the funtion
#as I changed it to read.csv and eliminated < data.table=FALSE> which was added
#at some point inside the fread() as an argument of the function, doing args(fread)
#and/or just fread it can be clearer the utility of the fread function.
austinData<- read.csv("https://raw.githubusercontent.com/lgellis/MiscTutorial/master/Austin/Imagine_Austin_Indicators.csv",
header = TRUE, stringsAsFactors = FALSE )
head(austinData)
## X. Indicator.Name
## 1 11 Homeless Count
## 2 35 Development within the 100 year Floodplain
## 3 41 Amount of Permanently Preserved Land
## 4 66 Annual Unlinked Transit Passenger Trips
## 5 40 Tree Canopy Coverage
## 6 120 Overall Investment ($) in the Arts by the City of Austin
## Indicator.Description
## 1 This indicator measures the total number of persons who were identified and counted as homeless in the federally mandated Austin/Travis County Annual Point-in-Time Count. By its nature a count only includes persons who are homeless on the day of the count. Point-in-time counts tend to underestimate families and children and do not include individuals living in marginal situations.
## 2 This indicator measures the number of habitable buildings located in the 100-year floodplain. Floodplains are the areas likely to flood when our creeks rise and flow over their banks. Tracking development within the floodplain allows us to assess the risk to public safety. The 100-year floodplain is only suited for very limited development due to that risk. Numbers go down with City projects to remove or reduce risk to existing structures. Flood regulations prevent more structures from being introduced to the floodplain. Numbers can increase if areas with existing structures are annexed into the corporate city limits.
## 3 This indicator measures the combined acreage of the City of Austin owned or managed lands that are permanently protected from development. This includes the Balcones Canyonlands Preserve (BCP), the Water Quality Protection Lands (WQPL), and nature reserves managed by the Parks Department. These lands are typically set aside for species and water quality protection. The goal of the BCP is to protect and enhance the habitat of endangered and rare species as mitigation for land development, as well as manage these forest ecosystems to protect water, air, and scenic resources. The primary goal of the WQPL is to produce the optimal level of high quality water to recharge the Barton Springs segment of the Edwards Aquifer by managing protected land to restore prairie-savanna ecosystems and healthy riparian corridors. Nature preserves share these and other goals. (Note: Preservation lands owned or managed by county, state, or federal entities, are not included in these totals but further contribute to these regional goals.)
## 4 This indicator tracks the total number of passenger boardings on all Capital Metro transit options.
## 5 This indicator measures the tree canopy coverage of Austin's full, limited, and extraterritorial jurisdictions. This is represented as the percentage of the total land area that is covered by tree foliage and is strongly correlated with the overall health of the urban environment. This indicator is measured using image analysis and GIS software and is subject to error at smaller scales.
## 6 This indicator measures the amount of funding from the City of Austin invest to provide support to arts activities and contracted arts services in Austin. This includes funding for the Cultural Arts Division of the Economic Development Department, Parks and Recreation Department's Arts and Culture Facilties, the Austin Bergstrom International Airport art, and MCP
## Importance.and.Relevance.to.Imagine.Austin
## 1 Homeless persons are often subjected to the elements, crime, and other maladies which often results in tragic consequences. Imagine Austin calls for reducing homelessness through supportive housing, mental health services/counseling, and alcohol/drug treatment.
## 2 Imagine Austin calls for actions that reduce the threats flooding poses to public safety and private property.
## 3 Imagine Austin calls for actions that direct development away from sensitive environmental resources, protect existing open space and natural resources, and improve air and water quality.
## 4 Imagine Austin seeks to increase public transit ridership by expanding service to activity centers and increasing the efficiency of current system.
## 5 Imagine Austin calls for actions that maintain and increase Austin’s urban forest as a key component of the green infrastructure network. Austin's urban forest provides social, ecological and economic benefits to the community and enhances the quality of life for Austin residents. City policies and practices aim to preserve, maintain, and replace individual trees and the urban forest as a whole.
## 6 Indicator relates to the financial investment offered to the creative community through the EDD Cultural Arts Division's Cultural Funding Programs, Cultural Tourism, and TEMPO temporary public art program.
## X2007 X2008 X2009 X2010 X2011 X2012 X2013 X2014
## 1 NA NA 1771 2087 2362 2244 2090 1987
## 2 NA NA NA NA NA 2380 NA NA
## 3 41001 41170 42647 42658 42690 42698 43383 44561
## 4 34039700 37399300 39438600 35859500 34740300 35512300 36402300 34178500
## 5 NA NA NA 35 NA NA NA 36
## 6 NA NA NA NA NA 8472939 11329219 12715538
## X2015 X2016 X2017 units
## 1 1832 2138 2036 persons
## 2 NA NA 1816 structures
## 3 44587 44569 44575 acres
## 4 34700250 NA NA trips
## 5 NA NA NA percent
## 6 14908283 15352295 NA USD
## Methodology
## 1 Between 2:00 a.m. and 9:00 a.m. on January 28th of each year, over 500 volunteers count the number of children, families and adults experiencing homelessness sleeping outdoors without shelter. Volunteer counted people in cars, tents, parks, under bridges, and on the streets. That same night, staff at agencies operating shelters and transitional housing programs count persons staying sheltered in their facilities as well.
## 2 The City of Austin Watershed Protection Department uses hydrologic & hydraulic engineering models and Geographic Information Systems (GIS) to map floodplains and then identify structures in the floodplain. Capital projects are implemented to reduce flood risk and the results are tracked in GIS.
## 3 Totals were generated by adding together acreage of City of Austin-purchased, owned, or managed lands in the WQPL program, BCP program, and designated "Nature Preserves" in the PARD inventory, including areas located within and outside the City of Austin’s full purpose jurisdiction.
## 4 The total number of passenger boardings on all bus, rail, and mobility services. \nPassengers are counted each time they board a transit vehicle.
## 5 To calculate this indicator color infrared aerials are processed using GIS software to isolate tree canopy reflectivity to estimate total tree canopy coverage; grass, shrubs, and other plants are excluded.
## 6 This indicator is calculated through annual accounting of Hotel Occupancy Tax funding utilized by the Cultural Arts Division.
## Data.Source
## 1 Austin ECHO Point in Time Counts
## 2 City of Austin Watershed Protection Department
## 3 Water Quality Protection Land program totals from 2006 and 2012 bond programs\nBalcones Canyonlands Preserve Annual Reports\nNature Preserves: PARD annual and monthly inventory reports
## 4 Federal Transit Administration
## 5 City of Austin Watershed Protection Department
## 6 City of Austin
attach(austinData)
#View(austinData)
names(austinData)
## [1] "X."
## [2] "Indicator.Name"
## [3] "Indicator.Description"
## [4] "Importance.and.Relevance.to.Imagine.Austin"
## [5] "X2007"
## [6] "X2008"
## [7] "X2009"
## [8] "X2010"
## [9] "X2011"
## [10] "X2012"
## [11] "X2013"
## [12] "X2014"
## [13] "X2015"
## [14] "X2016"
## [15] "X2017"
## [16] "units"
## [17] "Methodology"
## [18] "Data.Source"
#Modify the data set------------------------
i1 <- austinData %>%
filter( Indicator.Name %in%
c('Prevalence of Obesity', 'Prevalence of Tobacco Use',
'Prevalence of Cardiovascular Disease', 'Prevalence of Diabetes')) %>%
dplyr::select(c(`Indicator.Name`, `X2011`, `X2012`, `X2013`, `X2014`, `X2015`, `X2016`)) %>%
#it adds a column of the average of the row for all the years from 2011 to 2016
mutate (Average = round(rowMeans(
#and merging the columns in a single one
cbind(`X2011`, `X2012`, `X2013`, `X2014`, `X2015`, `X2016`), na.rm=T),2),
#it adds one more column <Improvement> to show the increase of the illnessess
`Improvement` = round((`X2011`-`X2016`)/`X2011`*100,2))
i1
## Indicator.Name X2011 X2012 X2013 X2014 X2015 X2016
## 1 Prevalence of Obesity 19.1 23.6 23.3 20.5 24.0 23.2
## 2 Prevalence of Tobacco Use 17.4 15.0 15.3 12.2 16.6 16.7
## 3 Prevalence of Cardiovascular Disease 5.0 4.9 1.5 4.4 4.9 6.2
## 4 Prevalence of Diabetes 8.0 7.2 9.3 7.2 7.5 10.4
## Average Improvement
## 1 22.28 -21.47
## 2 15.53 4.02
## 3 4.48 -24.00
## 4 8.27 -30.00
#0) Use of the formattable function
formattable(i1)
|
Indicator.Name
|
X2011
|
X2012
|
X2013
|
X2014
|
X2015
|
X2016
|
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
|
#1) First Data Table
formattable(i1,
align =c("l","c","c","c","c", "c", "c", "c", "r"),
list(`Indicator Name` = formatter(
"span", style = ~ style(color = "grey",font.weight = "bold"))
))
|
Indicator.Name
|
X2011
|
X2012
|
X2013
|
X2014
|
X2015
|
X2016
|
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
|
#As it can be noted the usage of the formattable() function let the colon name: "Indicator Name"
#be written differently by the orignal
#2) Add the color mapping for all 2011 to 2016
formattable(i1,
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
|
X2011
|
X2012
|
X2013
|
X2014
|
X2015
|
X2016
|
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
|
#3) Add the color bar to the average column
formattable(i1,
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),
#colored calculation of the mean
`Average` = color_bar(customRed)
))
|
Indicator.Name
|
X2011
|
X2012
|
X2013
|
X2014
|
X2015
|
X2016
|
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
|
#4) Add sign formatter to improvement over time
improvement_formatter <-
formatter("span",
style = x ~ style(
font.weight = "bold",
color = ifelse(x > 0, customGreen, ifelse(x < 0, customRed, "black"))))
formattable(i1,
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),
`Average` = color_bar(customRed),
`Improvement` = improvement_formatter
))
|
Indicator.Name
|
X2011
|
X2012
|
X2013
|
X2014
|
X2015
|
X2016
|
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
|
#5) For improvement formatter add icons
# Up and down arrow with greater than comparison from the vignette
improvement_formatter <- formatter("span",
style = x ~ style(font.weight = "bold",
color = ifelse(x > 0, customGreen, ifelse(x < 0, customRed, "black"))),
x ~ icontext(ifelse(x>0, "arrow-up", "arrow-down"), x)
)
formattable(i1, 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),
`Average` = color_bar(customRed),
`Improvement` = improvement_formatter
))
|
Indicator.Name
|
X2011
|
X2012
|
X2013
|
X2014
|
X2015
|
X2016
|
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
|
|
Prevalence of Diabetes
|
8.0
|
7.2
|
9.3
|
7.2
|
7.5
|
10.4
|
8.27
|
-30
|
#6) Add a star to the max value. Use if/else value = max(value)
improvement_formatter <- formatter("span", style = x ~ style(font.weight = "bold",
color = ifelse(x > 0, customGreen, ifelse(x < 0, customRed, "black"))),
x ~ icontext(ifelse(x == max(x), "thumbs-up", ""), x)
)
formattable(i1, align =c("l","c","c","c","c", "c", "c", "c", "r"), list(
`Indicator Name` = formatter("span",
style = x ~ style(color = "gray"),
x ~ icontext(ifelse(x == "Prevalence of Tobacco Use", "star", ""), x)),
`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),
`Average` = color_bar(customRed),
`Improvement` = improvement_formatter
))
|
Indicator.Name
|
X2011
|
X2012
|
X2013
|
X2014
|
X2015
|
X2016
|
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
|
|
Prevalence of Diabetes
|
8.0
|
7.2
|
9.3
|
7.2
|
7.5
|
10.4
|
8.27
|
-30
|
##7) Compare column to column
#Drop the rest and show just 2015 and 2016
i2 <- austinData %>%
filter(`Indicator.Name` %in% c('Prevalence of Obesity', 'Prevalence of Tobacco Use', 'Prevalence of Cardiovascular Disease', 'Prevalence of Diabetes')) %>%
select(c(`Indicator.Name`, `X2015`, `X2016`))
head(i2)
## Indicator.Name X2015 X2016
## 1 Prevalence of Obesity 24.0 23.2
## 2 Prevalence of Tobacco Use 16.6 16.7
## 3 Prevalence of Cardiovascular Disease 4.9 6.2
## 4 Prevalence of Diabetes 7.5 10.4
## Again the x is removed b/c you need to reference two column values, so you need to list them explicitly
formattable(i2, align =c("l","c","c"), list(
`Indicator Name` = formatter("span",
style = ~ style(color = "gray")),
`2016`= formatter("span", style = ~ style(color = ifelse(`2016` >`2015`, "red", "green")),
~ icontext(ifelse(`2016` >`2015`,"arrow-up", "arrow-down"), `2016`))
))
|
Indicator.Name
|
X2015
|
X2016
|
|
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
|
#My version of RStudio Cloud doesn't let me see the green bit of the Years column, otherwise it should work
#Edited By Federica Gazzelloni