library(tidyverse)
library(tidyr)
setwd("~/Data 110")
cities500 <- read_csv("500CitiesLocalHealthIndicators.cdc.csv")
data(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. Filter the dataset
For this assignment, I created a smaller subset of the 500 Cities dataset that focuses on Connecticut and adult prevalence. This helps make sure the dataset under 900 rows.
#Filter dataset for Connecticut cities for obesity measure
healthy_subset <- latlong_clean2 |>
filter(
StateAbbr == "CT",
Measure == "Obesity among adults aged >=18 Years"
) |>
rename(
obesity_percent = Data_Value,
cityname = CityName,
stateabbr = StateAbbr,
latitude = lat,
longitude = long
)# Check how many rows
nrow(healthy_subset)[1] 228head(healthy_subset)# 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 Hartford Census Tract Unhealt… 0937000… Obesit…
5 2017 CT Connecticut Waterbu… Census Tract Unhealt… 0980000… Obesit…
6 2017 CT Connecticut Hartford Census Tract Unhealt… 0937000… Obesit…
# ℹ 10 more variables: Data_Value_Type <chr>, obesity_percent <dbl>,
# PopulationCount <dbl>, latitude <dbl>, longitude <dbl>, CategoryID <chr>,
# MeasureId <chr>, CityFIPS <dbl>, TractFIPS <dbl>, Short_Question_Text <chr>2. Create a Barplot (Non-Map)
Next, I created a barchart that shows the obesity percentage for each Connecticut city. This helps me identify where obesity prevalence is the highest across the state of Connecticut.
#Load ggplot2 for plotting
library(ggplot2)
#Create a barchart ordered by obesity percentage
ggplot(healthy_subset, aes(x = reorder(cityname, obesity_percent),
y = obesity_percent, fill = obesity_percent)) +
geom_col() +
coord_flip() +
labs(title = "Adult Obesity Prevalence by City (Connecticut)",
x = "City",
y = "Obesity Prevalence (%)",
caption = "Source: CDC 500 Cities (2019)") +
theme_minimal()
3. Create a basic map
For here, I used the leaflet package to help me make a simple interactive map. Each circle represents a city, the larger circles the higher obesity prevalence.
library(leaflet)Warning: package 'leaflet' was built under R version 4.5.2leaflet(healthy_subset) |>
addProviderTiles("Esri.WorldStreetMap") |>
addCircles(
lng = ~longitude,
lat = ~latitude,
radius = ~obesity_percent * 800,
color = "#2f9c7b",
fillColor = "#2f9c7b",
fillOpacity = 0.5
)4. Refine your map to include a mouse-click tooltip
With the same map, I added pop-up boxes that shows each city’s name and obesity rate whenever you hover and click on the circles.
popup_info <- paste0(
"<b>City: </b>", healthy_subset$cityname, "<br>",
"<b>Obesity (%): </b>", healthy_subset$obesity_percent
)
leaflet(healthy_subset) |>
addProviderTiles("CartoDB.Positron") |>
addCircles(
lng = ~longitude,
lat = ~latitude,
radius = ~obesity_percent * 800,
color = "#1a3e34", #darker border outline
fillColor = "#2f9c7b", #lighter teal fill
fillOpacity = 0.6,
popup = popup_info
)5. Write a paragraph
To start off, I filtered the dataset of Connecticut cities, I focused on adult obesity rates from the CDC’s 500 Cities dataset. You can see that the obesity percentages ranged from 25% to above 35% which shows higher rates in a few cities. The bar chart shows that the obesity levels vary across the state, with some cities showing higher prevalence than others. The interactive map highlights where obesity is more common which the larger green circles represent higher percentages. This analysis helped me see how public health issues such as obesity differ within one small state, and how GIS tools make those differences visually easier to see.