``` r
#Assignment 1 code
library(viridis)
## Loading required package: viridisLite
library(tidyverse)
## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr 1.1.4 ✔ readr 2.1.6
## ✔ forcats 1.0.1 ✔ stringr 1.6.0
## ✔ ggplot2 4.0.1 ✔ tibble 3.3.0
## ✔ lubridate 1.9.4 ✔ tidyr 1.3.2
## ✔ purrr 1.2.0
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag() masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
library(sf)
## Linking to GEOS 3.13.0, GDAL 3.8.5, PROJ 9.5.1; sf_use_s2() is TRUE
library(dplyr)
library(magrittr)
##
## Attaching package: 'magrittr'
##
## The following object is masked from 'package:purrr':
##
## set_names
##
## The following object is masked from 'package:tidyr':
##
## extract
library(mapview)
nys_health_df <- read.csv("data/NYS_Health_Facility.csv")
nys_health_df <- nys_health_df[which(!is.na(nys_health_df$Facility.Latitude) & !is.na(nys_health_df$Facility.Longitude)),]
nys_health_sf <- st_as_sf(nys_health_df,
coords = c("Facility.Longitude", "Facility.Latitude"))
st_crs(nys_health_sf) <- 4326
nys_food_df <- read.csv("data/nys_retail_food_store_xy.csv")
nys_food_df <- nys_food_df[which(!is.na(nys_food_df$X) & !is.na(nys_food_df$Y)),]
nys_food_sf <- st_as_sf(nys_food_df, coords=c("X","Y"))
#Join the COVID data to NYC zip code data
postal_codes <- st_read("data/ZIP_CODE_040114/ZIP_CODE_040114.shp")
## Reading layer `ZIP_CODE_040114' from data source
## `/Users/gracedarling/Desktop/Data Analysis and Visualization with R/R-spatial/data/ZIP_CODE_040114/ZIP_CODE_040114.shp'
## using driver `ESRI Shapefile'
## Simple feature collection with 263 features and 12 fields
## Geometry type: POLYGON
## Dimension: XY
## Bounding box: xmin: 913129 ymin: 120020.9 xmax: 1067494 ymax: 272710.9
## Projected CRS: NAD83 / New York Long Island (ftUS)
covid_data <- read.csv("data/tests-by-zcta_2021_04_23.csv")
covid_data$label <- as.integer(covid_data$label, na.rm=T)
## Warning: NAs introduced by coercion
covid_zip <- merge(covid_data, postal_codes, by.x="label",by.y="ZIPCODE")
#Aggregate the NYC food retails store data (points) to the zip code data, so that we know how many retail stores in each zip code area
nyc_counties <- c("Kings", "Bronx", "Queens", "New York", "Richmond")
nyc_food <- nys_food_sf %>% filter(County %in% nyc_counties)
nyc_food_retail <- nyc_food %>% filter(str_detect(Establishment.Type,"A"))
count_per_zip <- nyc_food_retail %>% group_by(Zip.Code) %>% summarize(store_count=n())
count_per_zip <- st_drop_geometry(count_per_zip)
zip_and_stores <- merge(postal_codes, count_per_zip, by.x="ZIPCODE", by.y="Zip.Code")
mapview(zip_and_stores)
#Aggregate the NYC health facilities (points) to the zip code data. Similarly, choose appropriate subtypes such as nursing homes from the facilities.
nys_health_sf
## Simple feature collection with 3848 features and 34 fields
## Geometry type: POINT
## Dimension: XY
## Bounding box: xmin: -79.6299 ymin: -75.45935 xmax: 43.21162 ymax: 44.97849
## Geodetic CRS: WGS 84
## First 10 features:
## Facility.ID Facility.Name Short.Description
## 1 204 Hospice at Lourdes HSPC
## 2 620 Charles T Sitrin Health Care Center Inc NH
## 4 1156 East Side Nursing Home NH
## 5 2589 Wellsville Manor Care Center NH
## 6 3455 Harris Hill Nursing Facility, LLC NH
## 7 3853 Garden City Surgi Center DTC
## 8 4249 Willcare CHHA
## 9 4473 Good Shepherd Hospice HSPC
## 10 6230 NYU Langone Rutherford HOSP-EC
## 11 6482 Endoscopy Center of Long Island, LLC DTC
## Description Facility.Open.Date
## 1 Hospice 06/01/1985
## 2 Residential Health Care Facility - SNF 02/01/1989
## 4 Residential Health Care Facility - SNF 08/01/1979
## 5 Residential Health Care Facility - SNF 02/01/1989
## 6 Residential Health Care Facility - SNF 04/08/1992
## 7 Diagnostic and Treatment Center 04/07/2008
## 8 Certified Home Health Agency 05/15/1990
## 9 Hospice 09/01/2002
## 10 Hospital Extension Clinic 01/01/2006
## 11 Diagnostic and Treatment Center 01/20/2003
## Facility.Address.1 Facility.Address.2 Facility.City
## 1 4102 Old Vestal Road Vestal
## 2 2050 Tilden Avenue New Hartford
## 4 62 Prospect St Warsaw
## 5 4192A Bolivar Road Wellsville
## 6 2699 Wehrle Drive Williamsville
## 7 400 Endo Blvd Garden City
## 8 346 Delaware Avenue Buffalo
## 9 110 Bi-County Boulevard, Suite 114 Farmingdale
## 10 305 Second Ave New York
## 11 711 Stewart Avenue Garden City
## Facility.State Facility.Zip.Code Facility.Phone.Number Facility.Fax.Number
## 1 New York 13850 6077985692 NA
## 2 New York 13413 3157973114 NA
## 4 New York 14569 5857868151 NA
## 5 New York 14895 5855934400 NA
## 6 New York 14221 7166323700 NA
## 7 New York 11530 5168328504 NA
## 8 New York 14202 7168567500 NA
## 9 New York 11735 6314656300 6314656533
## 10 New York 10003 2125986570 NA
## 11 New York 11530 5162273254 NA
## Facility.Website Facility.County.Code Facility.County Regional.Office.ID
## 1 3 Broome 3
## 2 32 Oneida 3
## 4 60 Wyoming 1
## 5 2 Allegany 1
## 6 14 Erie 1
## 7 29 Nassau 7
## 8 14 Erie 1
## 9 29 Nassau 7
## 10 7093 New York 5
## 11 29 Nassau 7
## Regional.Office Main.Site.Name
## 1 Central New York Regional Office
## 2 Central New York Regional Office
## 4 Western Regional Office - Buffalo
## 5 Western Regional Office - Buffalo
## 6 Western Regional Office - Buffalo
## 7 Metropolitan Area Regional Office - Long Island
## 8 Western Regional Office - Buffalo
## 9 Metropolitan Area Regional Office - Long Island
## 10 Metropolitan Area Regional Office - New York City NYU Langone Hospitals
## 11 Metropolitan Area Regional Office - Long Island
## Main.Site.Facility.ID Operating.Certificate.Number
## 1 NA 0301501F
## 2 NA 3227304N
## 4 NA 6027303N
## 5 NA 0228305N
## 6 NA 1406301N
## 7 NA 2905204R
## 8 NA 1401606
## 9 NA 5151501F
## 10 1463 7002053H
## 11 NA 2905202R
## Operator.Name Operator.Address.1
## 1 Our Lady of Lourdes Memorial Hospital Inc 169 Riverside Drive
## 2 Charles T Sitrin Health Care Center, Inc Box 1000 Tilden Avenue
## 4 East Side Nursing Home Inc 62 Prospect Street
## 5 Wellsville Manor LLC 4192a Bolivar Road
## 6 Harris Hill Nursing Facility, LLC 560 Delaware Avenue, Suite 400
## 7 Endo Group, LLC 400 Endo Boulevard
## 8 Western Region Health Corporation Inc 346 Delaware Avenue
## 9 Good Shepherd Hospice 110 Bi-County Boulevard
## 10 NYU Langone Hospitals 550 First Avenue
## 11 Endoscopy Center of Long Island, LLC 711 Stewart Avenue
## Operator.Address.2 Operator.City Operator.State Operator.Zip.Code
## 1 Binghamton New York 13905
## 2 New Hartford New York 13413
## 4 Warsaw New York 14569
## 5 Wellsville New York 14897
## 6 Buffalo New York 14202
## 7 Garden City New York 11530
## 8 Buffalo New York 14202
## 9 Suite 114 Farmingdale New York 11735
## 10 New York New York 10016
## 11 Garden City New York 11530
## Cooperator.Name Cooperator.Address Cooperator.Address.2 Cooperator.City
## 1
## 2
## 4
## 5
## 6
## 7
## 8
## 9
## 10
## 11
## Cooperator.State Cooperator.Zip.Code Ownership.Type
## 1 New York NA Not for Profit Corporation
## 2 New York NA Not for Profit Corporation
## 4 New York NA Business Corporation
## 5 New York NA LLC
## 6 New York NA LLC
## 7 New York NA LLC
## 8 New York NA Business Corporation
## 9 New York NA Not for Profit Corporation
## 10 New York NA Not for Profit Corporation
## 11 New York NA LLC
## Facility.Location geometry
## 1 (42.097095, -75.975243) POINT (-75.97524 42.0971)
## 2 (43.05497, -75.228828) POINT (-75.22883 43.05497)
## 4 (42.738979, -78.12867) POINT (-78.12867 42.73898)
## 5 (42.126461, -77.967834) POINT (-77.96783 42.12646)
## 6 (42.956268, -78.68856) POINT (-78.68856 42.95627)
## 7 (40.733765, -73.591286) POINT (-73.59129 40.73376)
## 8 (42.893864, -78.875824) POINT (-78.87582 42.89386)
## 9 (40.725994, -73.428329) POINT (-73.42833 40.72599)
## 10 (40.734818, -73.983231) POINT (-73.98323 40.73482)
## 11 (40.732571, -73.607971) POINT (-73.60797 40.73257)
nyc_health <- nys_health_sf %>% filter(Facility.County %in% nyc_counties)
nyc_nursing_homes <- nyc_health %>% filter(str_detect(Short.Description, "NH"))
nh_count <- nyc_nursing_homes %>% group_by(Facility.Zip.Code) %>% summarize(nursing_home_count=n())
nh_count <- st_drop_geometry(nh_count)
nh_and_stores <- zip_and_stores %>% left_join(nh_count, by=c("ZIPCODE"="Facility.Zip.Code"))%>%
mutate(nursing_home_count = ifelse(is.na(nursing_home_count), 0, nursing_home_count))
mapview(nh_and_stores)
#Join the Census ACS population, race, and age data to the NYC Planning Census Tract Data
nyc_census <- sf::st_read('data/geo_export_1dc7b645-647b-4806-b9a0-7b79660f120a.shp', stringsAsFactors = FALSE)
## Reading layer `geo_export_1dc7b645-647b-4806-b9a0-7b79660f120a' from data source `/Users/gracedarling/Desktop/Data Analysis and Visualization with R/R-spatial/data/geo_export_1dc7b645-647b-4806-b9a0-7b79660f120a.shp'
## using driver `ESRI Shapefile'
## Simple feature collection with 2165 features and 11 fields
## Geometry type: MULTIPOLYGON
## Dimension: XY
## Bounding box: xmin: -74.25559 ymin: 40.49612 xmax: -73.70001 ymax: 40.91553
## Geodetic CRS: WGS84(DD)
nyc_census %<>% dplyr::mutate(cntyFIPS = case_when(
boro_name == 'Bronx' ~ '005',
boro_name == 'Brooklyn' ~ '047',
boro_name == 'Manhattan' ~ '061',
boro_name == 'Queens' ~ '081',
boro_name == 'Staten Island' ~ '085'),
tractFIPS = paste(cntyFIPS, ct2010, sep='')
)
acs_data <- readLines("data/ACSDP5Y2018.DP05_data_with_overlays_2020-04-22T132935.csv") %>%
magrittr::extract(-2) %>%
textConnection() %>%
read.csv(header=TRUE, quote= "\"") %>%
dplyr::select(GEO_ID,
totPop = DP05_0001E, elderlyPop = DP05_0024E, # >= 65
malePop = DP05_0002E, femalePop = DP05_0003E,
whitePop = DP05_0037E, blackPop = DP05_0038E,
asianPop = DP05_0067E, hispanicPop = DP05_0071E,
adultPop = DP05_0021E, citizenAdult = DP05_0087E) %>%
dplyr::mutate(censusCode = stringr::str_sub(GEO_ID, -9,-1));
acs_data %>%
magrittr::extract(1:10,)
## GEO_ID totPop elderlyPop malePop femalePop whitePop blackPop
## 1 1400000US36005000100 7080 51 6503 577 1773 4239
## 2 1400000US36005000200 4542 950 2264 2278 2165 1279
## 3 1400000US36005000400 5634 710 2807 2827 2623 1699
## 4 1400000US36005001600 5917 989 2365 3552 2406 2434
## 5 1400000US36005001900 2765 76 1363 1402 585 1041
## 6 1400000US36005002000 9409 977 4119 5290 3185 4487
## 7 1400000US36005002300 4600 648 2175 2425 479 2122
## 8 1400000US36005002400 172 0 121 51 69 89
## 9 1400000US36005002500 5887 548 2958 2929 903 1344
## 10 1400000US36005002701 2868 243 1259 1609 243 987
## asianPop hispanicPop adultPop citizenAdult censusCode
## 1 130 2329 6909 6100 005000100
## 2 119 3367 3582 2952 005000200
## 3 226 3873 4507 4214 005000400
## 4 68 3603 4416 3851 005001600
## 5 130 1413 2008 1787 005001900
## 6 29 5905 6851 6170 005002000
## 7 27 2674 3498 3056 005002300
## 8 14 0 131 42 005002400
## 9 68 4562 4237 2722 005002500
## 10 0 1985 1848 1412 005002701
populationdata <- merge(nyc_census, acs_data, by.x='tractFIPS', by.y='censusCode')
#Aggregate the ACS census data to zip code area data
covid_zip <- st_as_sf(covid_zip)
pop_nyc <- sf::st_transform(populationdata, st_crs(covid_zip))
covidPopZipNYC <- sf::st_join(covid_zip,
pop_nyc%>% sf::st_centroid(),
join = st_contains) %>%
group_by(label, PO_NAME, POPULATION, COUNTY, COVID_CASE_COUNT, TOTAL_COVID_TESTS) %>%
summarise(totPop = sum(totPop),
malePctg = sum(malePop)/totPop*100,
asianPop = sum(asianPop),
blackPop = sum(blackPop),
hispanicPop = sum(hispanicPop),
whitePop = sum(whitePop))
## Warning: st_centroid assumes attributes are constant over geometries
## `summarise()` has grouped output by 'label', 'PO_NAME', 'POPULATION', 'COUNTY',
## 'COVID_CASE_COUNT'. You can override using the `.groups` argument.
#Aggregate the ACS census data to zip code area data
complete_data <- st_join(covidPopZipNYC, nh_and_stores, join = st_intersects)
mapview(complete_data)