Healthy Cities GIS Assignment

\[Source:Matador Network\]

Load the libraries and set the working directory

library(tidyverse)
library(tidyr)
setwd("D:/Data 110/Week 10")
cities500 <- read_csv("500CitiesLocalHealthIndicators.cdc.csv")

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(Category == "Prevention") |>
  filter(Data_Value_Type == "Crude prevalence") |>
  filter(Year == 2017)
head(latlong_clean)

## # A tibble: 6 × 25
##    Year StateAbbr StateDesc  CityName   GeographicLevel DataSource Category  
##   <dbl> <chr>     <chr>      <chr>      <chr>           <chr>      <chr>     
## 1  2017 AL        Alabama    Montgomery City            BRFSS      Prevention
## 2  2017 CA        California Concord    City            BRFSS      Prevention
## 3  2017 CA        California Concord    City            BRFSS      Prevention
## 4  2017 CA        California Fontana    City            BRFSS      Prevention
## 5  2017 CA        California Richmond   Census Tract    BRFSS      Prevention
## 6  2017 FL        Florida    Davie      Census Tract    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>

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"

unique(latlong_clean$StateAbbr)

##  [1] "AL" "CA" "FL" "CT" "IL" "MN" "NY" "PA" "NC" "OH" "OK" "OR" "TX" "RI" "SC"
## [16] "SD" "TN" "UT" "VA" "WA" "AK" "WI" "AZ" "AR" "CO" "DE" "NV" "DC" "GA" "ID"
## [31] "HI" "MA" "MI" "IN" "KS" "KY" "IA" "LA" "MD" "ME" "NH" "NJ" "NM" "MO" "MS"
## [46] "NE" "MT" "ND" "WV" "VT" "WY"

Remove the variables that will not be used in the assignment

prevention <- latlong_clean |>
  select(-DataSource,-Data_Value_Unit, -DataValueTypeID, -Low_Confidence_Limit, -High_Confidence_Limit, -Data_Value_Footnote_Symbol, -Data_Value_Footnote)
head(prevention)

## # A tibble: 6 × 18
##    Year StateAbbr StateDesc  CityName  GeographicLevel Category UniqueID Measure
##   <dbl> <chr>     <chr>      <chr>     <chr>           <chr>    <chr>    <chr>  
## 1  2017 AL        Alabama    Montgome… City            Prevent… 151000   Choles…
## 2  2017 CA        California Concord   City            Prevent… 616000   Visits…
## 3  2017 CA        California Concord   City            Prevent… 616000   Choles…
## 4  2017 CA        California Fontana   City            Prevent… 624680   Visits…
## 5  2017 CA        California Richmond  Census Tract    Prevent… 0660620… Choles…
## 6  2017 FL        Florida    Davie     Census Tract    Prevent… 1216475… Choles…
## # ℹ 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 “Prevention” is a manageable dataset now.

For your assignment, work with the cleaned “Prevention” dataset

1. Once you run the above code, filter this dataset one more time for any particular subset.

Filter chunk here

Filter all Geographical Level the same ->Census Tract

filtered_health <- prevention |>
  filter(GeographicLevel %in% c("Census Tract"))

head(filtered_health)

## # A tibble: 6 × 18
##    Year StateAbbr StateDesc  CityName  GeographicLevel Category UniqueID Measure
##   <dbl> <chr>     <chr>      <chr>     <chr>           <chr>    <chr>    <chr>  
## 1  2017 CA        California Richmond  Census Tract    Prevent… 0660620… Choles…
## 2  2017 FL        Florida    Davie     Census Tract    Prevent… 1216475… Choles…
## 3  2017 FL        Florida    Hialeah   Census Tract    Prevent… 1230000… Visits…
## 4  2017 FL        Florida    Hialeah   Census Tract    Prevent… 1230000… Choles…
## 5  2017 FL        Florida    Miami Be… Census Tract    Prevent… 1245025… Choles…
## 6  2017 CA        California Santa Cl… Census Tract    Prevent… 0669084… Choles…
## # ℹ 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>

Selected (filter) states DC, MD, MN, WV, SD

selected_state <- prevention |>
  filter(!is.na(StateAbbr), StateAbbr %in% c("MD", "SD", "DC", "WV", "MN"))

head(selected_state)

## # A tibble: 6 × 18
##    Year StateAbbr StateDesc   CityName GeographicLevel Category UniqueID Measure
##   <dbl> <chr>     <chr>       <chr>    <chr>           <chr>    <chr>    <chr>  
## 1  2017 MN        Minnesota   Duluth   City            Prevent… 2717000  "Curre…
## 2  2017 SD        South Dako… Rapid C… Census Tract    Prevent… 4652980… "Curre…
## 3  2017 SD        South Dako… Rapid C… City            Prevent… 4652980  "Visit…
## 4  2017 SD        South Dako… Sioux F… Census Tract    Prevent… 4659020… "Chole…
## 5  2017 DC        District o… Washing… Census Tract    Prevent… 1150000… "Takin…
## 6  2017 DC        District o… Washing… Census Tract    Prevent… 1150000… "Visit…
## # ℹ 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>

unique(selected_state$Short_Question_Text)

## [1] "Health Insurance"      "Annual Checkup"        "Cholesterol Screening"
## [4] "Taking BP Medication"

Let’s see what selected 5 states people concern healthcare?

plot1 <- ggplot(selected_state, aes(x = Short_Question_Text, y = Data_Value, color = Short_Question_Text)) +
  geom_jitter() +
  scale_color_viridis_d()+
  facet_wrap(~StateAbbr, scales = "free") +
  theme(
    axis.text.x = element_text(angle = 90, hjust = 1),
    plot.caption = element_text(hjust = 1, vjust = 1)   # This line moves the caption
  ) +
  theme(axis.text.x = element_text(angle = 90, hjust = 1)) +
  labs(title = "Healthcare Concerns in the US: Insights from 5 States",
       subtitle = "Comparison of Healthcare Interests Across 5 Selected States",
caption = "Source: 500 Cities Local Health Indicators") 
plot1

## Warning: Removed 36 rows containing missing values or values outside the scale range
## (`geom_point()`).

All 5 selected states people does not so concern the health insurance than other concern.

Compare health insurance provider all states

filtered_measure <- filtered_health |>
  filter(Short_Question_Text == "Health Insurance") |>
  group_by(StateAbbr)
  
  head(filtered_measure)

## # A tibble: 6 × 18
## # Groups:   StateAbbr [4]
##    Year StateAbbr StateDesc   CityName GeographicLevel Category UniqueID Measure
##   <dbl> <chr>     <chr>       <chr>    <chr>           <chr>    <chr>    <chr>  
## 1  2017 NY        New York    Rochest… Census Tract    Prevent… 3663000… "Curre…
## 2  2017 NC        North Caro… Charlot… Census Tract    Prevent… 3712000… "Curre…
## 3  2017 NC        North Caro… Charlot… Census Tract    Prevent… 3712000… "Curre…
## 4  2017 OH        Ohio        Clevela… Census Tract    Prevent… 3916000… "Curre…
## 5  2017 PA        Pennsylvan… Bethleh… Census Tract    Prevent… 4206088… "Curre…
## 6  2017 PA        Pennsylvan… Pittsbu… Census Tract    Prevent… 4261000… "Curre…
## # ℹ 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>

unique(filtered_measure$Measure)

## [1] "Current lack of health insurance among adults aged 18\x9664 Years"

PopulationCount = number of uninsured people each state

library(ggplot2)
library(plotly)

## 
##  次のパッケージを付け加えます: 'plotly'

##  以下のオブジェクトは 'package:ggplot2' からマスクされています:
## 
##     last_plot

##  以下のオブジェクトは 'package:stats' からマスクされています:
## 
##     filter

##  以下のオブジェクトは 'package:graphics' からマスクされています:
## 
##     layout

library(ggfortify)

 state_population_means <- aggregate(PopulationCount ~ StateAbbr, data = filtered_measure, mean)

plot2 <-ggplot(state_population_means, aes(x = StateAbbr, y = PopulationCount, color = StateAbbr, fill = StateAbbr)) +
  geom_point(size = 4) +
  scale_color_viridis_d()+
  theme(axis.text.x = element_text(angle = 90, hjust = 1)) +
  labs(title = "Number of Uninsured by State",
       subtitle = "Uninsured population",
caption = "Source: 500 Cities Local Health Indicators")
  
plot2

# Convert ggplot to plotly (pass ggplot object)
ggplotly(plot2)

unique(filtered_measure$MeasureId)

## [1] "ACCESS2"

Interestingly, uninsured individuals, often linked with ACCESS2, face significant challenges accessing the clinic. The reasons for this could range from transportation issues to financial constraints. Unfortunately, this dataset doesn’t provide a clear reason for their struggle.

3Now create a map of your subsetted dataset.

First map chunk here

library(leaflet)

map1 <- leaflet() |>
  setView(lng = -90, lat = 40, zoom =4) |>
  addProviderTiles("Esri.WorldStreetMap") |>
  addCircles(
    data = filtered_measure,
    radius = filtered_measure$PopulationCount
)

## Assuming "long" and "lat" are longitude and latitude, respectively

map1

4. Refine your map to include a mousover tooltip

Refined map chunk here

map2 <- leaflet() |>
  setView(lng = -90, lat = 40, zoom = 4) |>
  addProviderTiles("Esri.WorldStreetMap") |>
  addCircles(
    data = filtered_measure,
    lng = ~long,
    lat = ~lat,
    radius = ~PopulationCount,
    color = "darkgreen",
    fillColor = "darkolivegreen2",
    fillOpacity = 0.25
  )
map2

Create pop up screen

pop_health <- paste0(
  "<b>State:</b>, ",  
  filtered_measure$StateDesc, "<br>",
  "<b>City : </b>", filtered_measure$CityName, "<br>",
  "<b>Number of Uninsurer: </b>", filtered_measure$PopulationCount, "<br>",
  "<b>Data Value : </b>", filtered_measure$Data_Value, "<br>",
  "<b>Longditude : </b>", filtered_measure$long, "<br>",
  "<b>Lattitude : </b>", filtered_measure$lat, "<br>"
  
)

Insert popup function

map3 <- leaflet() |>
  setView(lng = -90, lat = 40, zoom = 4) |>
  addProviderTiles("Esri.WorldStreetMap") |>
  addCircles(
    data = filtered_measure,
    lng = ~long,
    lat = ~lat,
    radius = ~PopulationCount,
    color = "darkgreen",
    fillColor = "darkolivegreen2",
    fillOpacity = 0.25,
    popup = pop_health
  )
map3

#5 Paragraph

I studied the Data value for “Short Question Text.” In the beginning, I filtered to limit the “Geographical Level” of the Census Tract, where data extraction is standardized. Since comparing all 50 states would be too many values, I picked a sample of 5 states. The five states were selected for comparison for the following reasons.. 1. Best health insurance - Minnesota 2. Worst Health insurance - West Virginia 3. Best annual checkup - DC 4. Lowest uninsured rate in the US - SD 5. Our State - MD (Google)

Surprisingly, I discovered all five states’ “Health Insurance” Data value is pretty low. (plot 1) They are considerably less aware of health insurance than other health concerns. After I filtered “Health Insurance” of the data flame, I found all the “Measure” value shows “Current lack of health insurance among adults aged 18 and older years,” which I realized the population on the list indicates an “uninsured” population. Interestingly, it was also discovered that a common reason for health screening in the uninsured population is that access to clinics is difficult for one reason or another. This was found by examining the value of the “MeasureId” by Unique Formula. For reference, I created another plot to show the uninsured population of all states. (plot 2)

I wanted to continue working on this dataset, so I summarized the data for the uninsured. However, the data were not linked to the GIS, so I decided to include the number of uninsured people nationwide in this assignment’s map. I could not get the exact size difference (blue screen only) with the course material formula, I found another formular for sizing. Additionally, The pop-up screen includes the state and the city from which the data was extracted.