library(tidyverse)
library(tidyr)
library(leaflet)
cities500 <- read_csv("500CitiesLocalHealthIndicators.cdc.csv")
data(cities500)
#View(cities500)Healthy Cities GIS Assignment
Load the libraries and set the working directory
The GeoLocation variable has (lat, long) format
Split GeoLocation (lat, long) into two columns: lat and long
latlong <- cities500|>
mutate(GeoLocation = str_replace_all(GeoLocation, "[()]", ""))|>
separate(GeoLocation, into = c("lat", "long"), sep = ",", convert = TRUE)
head(latlong)# A tibble: 6 × 25
Year StateAbbr StateDesc CityName GeographicLevel DataSource Category
<dbl> <chr> <chr> <chr> <chr> <chr> <chr>
1 2017 CA California Hawthorne Census Tract BRFSS Health Outcom…
2 2017 CA California Hawthorne City BRFSS Unhealthy Beh…
3 2017 CA California Hayward City BRFSS Health Outcom…
4 2017 CA California Hayward City BRFSS Unhealthy Beh…
5 2017 CA California Hemet City BRFSS Prevention
6 2017 CA California Indio Census Tract BRFSS Health Outcom…
# ℹ 18 more variables: UniqueID <chr>, Measure <chr>, Data_Value_Unit <chr>,
# DataValueTypeID <chr>, Data_Value_Type <chr>, Data_Value <dbl>,
# Low_Confidence_Limit <dbl>, High_Confidence_Limit <dbl>,
# Data_Value_Footnote_Symbol <chr>, Data_Value_Footnote <chr>,
# PopulationCount <dbl>, lat <dbl>, long <dbl>, CategoryID <chr>,
# MeasureId <chr>, CityFIPS <dbl>, TractFIPS <dbl>, Short_Question_Text <chr>Filter the dataset
Remove the StateDesc that includes the United Sates, select Prevention as the category (of interest), filter for only measuring crude prevalence and select only 2017.
latlong_clean <- latlong |>
filter(StateDesc != "United States") |>
filter(Data_Value_Type == "Crude prevalence") |>
filter(Year == 2017) |>
filter(StateAbbr == "CT") |>
filter(Category == "Unhealthy Behaviors")
head(latlong_clean)# A tibble: 6 × 25
Year StateAbbr StateDesc CityName GeographicLevel DataSource Category
<dbl> <chr> <chr> <chr> <chr> <chr> <chr>
1 2017 CT Connecticut Bridgeport Census Tract BRFSS Unhealthy B…
2 2017 CT Connecticut Danbury City BRFSS Unhealthy B…
3 2017 CT Connecticut Norwalk Census Tract BRFSS Unhealthy B…
4 2017 CT Connecticut Bridgeport Census Tract BRFSS Unhealthy B…
5 2017 CT Connecticut Hartford Census Tract BRFSS Unhealthy B…
6 2017 CT Connecticut Waterbury Census Tract BRFSS Unhealthy B…
# ℹ 18 more variables: UniqueID <chr>, Measure <chr>, Data_Value_Unit <chr>,
# DataValueTypeID <chr>, Data_Value_Type <chr>, Data_Value <dbl>,
# Low_Confidence_Limit <dbl>, High_Confidence_Limit <dbl>,
# Data_Value_Footnote_Symbol <chr>, Data_Value_Footnote <chr>,
# PopulationCount <dbl>, lat <dbl>, long <dbl>, CategoryID <chr>,
# MeasureId <chr>, CityFIPS <dbl>, TractFIPS <dbl>, Short_Question_Text <chr>What variables are included? (can any of them be removed?)
names(latlong_clean) [1] "Year" "StateAbbr"
[3] "StateDesc" "CityName"
[5] "GeographicLevel" "DataSource"
[7] "Category" "UniqueID"
[9] "Measure" "Data_Value_Unit"
[11] "DataValueTypeID" "Data_Value_Type"
[13] "Data_Value" "Low_Confidence_Limit"
[15] "High_Confidence_Limit" "Data_Value_Footnote_Symbol"
[17] "Data_Value_Footnote" "PopulationCount"
[19] "lat" "long"
[21] "CategoryID" "MeasureId"
[23] "CityFIPS" "TractFIPS"
[25] "Short_Question_Text" Remove the variables that will not be used in the assignment
latlong_clean2 <- latlong_clean |>
select(-DataSource,-Data_Value_Unit, -DataValueTypeID, -Low_Confidence_Limit, -High_Confidence_Limit, -Data_Value_Footnote_Symbol, -Data_Value_Footnote)
head(latlong_clean2)# A tibble: 6 × 18
Year StateAbbr StateDesc CityName GeographicLevel Category UniqueID Measure
<dbl> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
1 2017 CT Connecticut Bridgep… Census Tract Unhealt… 0908000… Obesit…
2 2017 CT Connecticut Danbury City Unhealt… 918430 Obesit…
3 2017 CT Connecticut Norwalk Census Tract Unhealt… 0955990… Obesit…
4 2017 CT Connecticut Bridgep… Census Tract Unhealt… 0908000… Curren…
5 2017 CT Connecticut Hartford Census Tract Unhealt… 0937000… Obesit…
6 2017 CT Connecticut Waterbu… Census Tract Unhealt… 0980000… Obesit…
# ℹ 10 more variables: Data_Value_Type <chr>, Data_Value <dbl>,
# PopulationCount <dbl>, lat <dbl>, long <dbl>, CategoryID <chr>,
# MeasureId <chr>, CityFIPS <dbl>, TractFIPS <dbl>, Short_Question_Text <chr>The new dataset “latlong_clean2” is a manageable dataset now.
For your assignment, work with a cleaned dataset where you perform your own cleaning and filtering.
1. Once you run the above code and filter this complicated dataset, perform your own investigation by filtering this dataset however you choose so that you have a subset with no more than 900 observations through some inclusion/exclusion criteria.
Filter chunk here (you may need multiple chunks)
Filter the dataset
only keep California as the StateDesc(because i like this state for its diversity and warm weather), select Prevention as the category (of interest), filter for only measuring age-adjusted prevalence and select only 2017.
latlong_clean3 <- latlong |>
filter(StateDesc == "California") |>
filter(Data_Value_Type == "Age-adjusted prevalence") |>
filter(Year == 2017) |>
filter(GeographicLevel=="City")|>
filter(StateAbbr == "CA") |>
filter(Category == "Prevention")
head(latlong_clean3)# A tibble: 6 × 25
Year StateAbbr StateDesc CityName GeographicLevel DataSource Category
<dbl> <chr> <chr> <chr> <chr> <chr> <chr>
1 2017 CA California Hemet City BRFSS Prevention
2 2017 CA California Modesto City BRFSS Prevention
3 2017 CA California Alhambra City BRFSS Prevention
4 2017 CA California Antioch City BRFSS Prevention
5 2017 CA California Anaheim City BRFSS Prevention
6 2017 CA California Anaheim City BRFSS Prevention
# ℹ 18 more variables: UniqueID <chr>, Measure <chr>, Data_Value_Unit <chr>,
# DataValueTypeID <chr>, Data_Value_Type <chr>, Data_Value <dbl>,
# Low_Confidence_Limit <dbl>, High_Confidence_Limit <dbl>,
# Data_Value_Footnote_Symbol <chr>, Data_Value_Footnote <chr>,
# PopulationCount <dbl>, lat <dbl>, long <dbl>, CategoryID <chr>,
# MeasureId <chr>, CityFIPS <dbl>, TractFIPS <dbl>, Short_Question_Text <chr>#View(latlong_clean3)What variables are included? (can any of them be removed?)
names(latlong_clean3) [1] "Year" "StateAbbr"
[3] "StateDesc" "CityName"
[5] "GeographicLevel" "DataSource"
[7] "Category" "UniqueID"
[9] "Measure" "Data_Value_Unit"
[11] "DataValueTypeID" "Data_Value_Type"
[13] "Data_Value" "Low_Confidence_Limit"
[15] "High_Confidence_Limit" "Data_Value_Footnote_Symbol"
[17] "Data_Value_Footnote" "PopulationCount"
[19] "lat" "long"
[21] "CategoryID" "MeasureId"
[23] "CityFIPS" "TractFIPS"
[25] "Short_Question_Text" Remove the variables that will not be used in the assignment
latlong_clean4 <- latlong_clean3 |>
select(-Measure,-DataSource,-Data_Value_Unit, -DataValueTypeID, -Low_Confidence_Limit, -High_Confidence_Limit, -Data_Value_Footnote_Symbol, -Data_Value_Footnote,-TractFIPS)
head(latlong_clean4)# A tibble: 6 × 16
Year StateAbbr StateDesc CityName GeographicLevel Category UniqueID
<dbl> <chr> <chr> <chr> <chr> <chr> <chr>
1 2017 CA California Hemet City Prevention 633182
2 2017 CA California Modesto City Prevention 648354
3 2017 CA California Alhambra City Prevention 600884
4 2017 CA California Antioch City Prevention 602252
5 2017 CA California Anaheim City Prevention 602000
6 2017 CA California Anaheim City Prevention 602000
# ℹ 9 more variables: Data_Value_Type <chr>, Data_Value <dbl>,
# PopulationCount <dbl>, lat <dbl>, long <dbl>, CategoryID <chr>,
# MeasureId <chr>, CityFIPS <dbl>, Short_Question_Text <chr>#View(latlong_clean4)
#names(latlong_clean4)2. Based on the GIS tutorial (Japan earthquakes), create one plot about something in your subsetted dataset.
First plot chunk here
# non map plot
options(scipen = 999)
p1 <- latlong_clean4 |>
filter(
!is.na(PopulationCount) ,
!is.na(Data_Value) ,
!is.na(Short_Question_Text)) |>
ggplot(aes(x=PopulationCount,y=Data_Value,color=Short_Question_Text))+
#size of the point depending on PopulationCount
geom_point(aes(size=PopulationCount),alpha=0.7)+
#scale_color_gradient(high="#FF2D55",low="#4A90E2")+
xlim(50000,400000)+
labs(title="Health Prevention Rates in California (2017)",
x="Population Count",
y="Data Value (%)",
caption="Source: 500CitiesLocalHealthIndicators.cdc.csv")+
theme_minimal()+
theme(plot.title=element_text(hjust = 0.5))
p1Warning: Removed 28 rows containing missing values or values outside the scale range
(`geom_point()`).
3. Now create a map of your subsetted dataset.
First map chunk here
# leaflet()
leaflet() |>
setView(lng=-119,lat=36,zoom=6) |>
addProviderTiles("Esri.WorldStreetMap") |>
addCircles(
#stroke=FALSE,
data=latlong_clean4,
radius=sqrt(latlong_clean4$PopulationCount),
color="#ff85a2",
fillColor="#ffe066",
fillOpacity=0.5)Assuming "long" and "lat" are longitude and latitude, respectively4. Refine your map to include a mouse-click tooltip
Refined map chunk here
#filter only cholesterol screening to compare cholesterol screening values in CA cities
latlong_clean5 <- latlong_clean4 |>
filter(Short_Question_Text == "Cholesterol Screening")
#turn data_value into categorical variables
latlong_clean6 <-latlong_clean5 |>
mutate(Data_Value1=case_when(
Data_Value >70 & Data_Value <=80 ~"Medium",
Data_Value <86 & Data_Value >80 ~"High",
TRUE~"Others"))
#create popup content
popup1 <- paste0(
"<b>City:</b> ", latlong_clean6$CityName, "<br>",
"<b>Measure:</b> ", latlong_clean6$Short_Question_Text, "<br>",
"<b>Value:</b> ", latlong_clean6$Data_Value, "%<br>",
"<b>Population:</b> ", latlong_clean6$PopulationCount)
#add 3 colors to 3 data value types
pal <- colorFactor(palette=c("#3A86FF","#FF4D8D","gray"),
levels=c("Medium",
"High",
"Others"),
latlong_clean6$Data_Value1)
leaflet(latlong_clean6) |>
setView(lng=-120,lat=36,zoom=6) |>
addProviderTiles("Esri.WorldStreetMap") |>
addCircles(
stroke=FALSE,
data=latlong_clean6,
radius=sqrt(latlong_clean6$PopulationCount*200),
fillColor=~pal( latlong_clean6$Data_Value1),
fillOpacity=0.4,
popup=popup1)Assuming "long" and "lat" are longitude and latitude, respectively5. Write a paragraph
In a paragraph, describe the plots you created and the insights they show.
According to the CDC 500 Cities Project, cholesterol screening and Annual Checkup represent the percentage of adults who have had their cholesterol and annual health situation checked by a health professional.Taking BP Medication and Health Insurance represent percentage of adults who have taken BP Medication and who have bought health insurance.In my scatter plot,i analyzed the health prevention rates in California in 2017.The plot shows that Cholesterol Screening has the highest values, from 75%-85%,followed up by Annual Checkup 60%-70%,Taking BP Medication 46%-58%, and Health Insurance 5%-30%.It indicates that health insurance coverage is relatively low compared with other preventive method. And population in most cities are centered around 70000-250000.There isn’t a strong linear relationship between population count and preventive health method.From the map, we can tell that the bigger circles are mostly around big cities like L.A,San Jose,San Francisco,which shows most population are from these cities.In the interactive map, pink dots are scattered slightly more around some big cities,like L.A, which shows some bigger cities tend to have more cholesterol screening. However,the overall relationship between cholesterol screening and city population is still weak and inconsistent.