nyc_zip <- st_read("./CopyOfSection_10/R-Spatial_I_Lab/ZIP_CODE_040114/ZIP_CODE_040114.shp")%>%
  filter(ZIPCODE != "00083")
## Reading layer `ZIP_CODE_040114' from data source 
##   `/Users/jorgesoldevila/Desktop/OneDrive - Hunter - CUNY/R Data Analysis and Visualization/Homework_10/CopyOfSection_10/R-Spatial_I_Lab/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)
nyc_food_store <- st_read("./Untitled/R-Spatial_II_Lab/nycFoodStore.shp")
## Reading layer `nycFoodStore' from data source 
##   `/Users/jorgesoldevila/Desktop/OneDrive - Hunter - CUNY/R Data Analysis and Visualization/Homework_10/Untitled/R-Spatial_II_Lab/nycFoodStore.shp' 
##   using driver `ESRI Shapefile'
## Simple feature collection with 11300 features and 16 fields
## Geometry type: POINT
## Dimension:     XY
## Bounding box:  xmin: -74.2484 ymin: 40.50782 xmax: -73.67061 ymax: 40.91008
## Geodetic CRS:  WGS 84
nyc_food_store_2263 <- st_transform(nyc_food_store,2263)

tests_04_23 <- read_csv("./Untitled/R-Spatial_II_Lab/tests-by-zcta_2021_04_23.csv")

health_facilities <- read_csv("./Untitled/R-Spatial_II_Lab/NYS_Health_Facility.csv")
nyc_hosp <- health_facilities %>% 
  filter(`Facility County Code` == 7093|7095|7094|7096|7097) %>% 
  filter(`Short Description`== "HOSP")

acs_data <- read_csv("./Untitled/R-Spatial_II_Lab/ACSDP5Y2018.DP05_data_with_overlays_2020-04-22T132935.csv")     
acs_data <- acs_data[-1,]
acs_data$bo_code <- NA

nyc_tract <- st_read("./Untitled/R-Spatial_II_Lab/2010 Census Tracts/geo_export_1dc7b645-647b-4806-b9a0-7b79660f120a.shp")
## Reading layer `geo_export_1dc7b645-647b-4806-b9a0-7b79660f120a' from data source `/Users/jorgesoldevila/Desktop/OneDrive - Hunter - CUNY/R Data Analysis and Visualization/Homework_10/Untitled/R-Spatial_II_Lab/2010 Census Tracts/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_tract_2263 <- st_transform(nyc_tract,2263)

1.Join the COVID-19 data to the NYC zip code area data

covid_zip_merge <- left_join(nyc_zip,tests_04_23%>%mutate(MODIFIED_ZCTA=
                    as.character(MODIFIED_ZCTA)), 
                    by =c('ZIPCODE'='MODIFIED_ZCTA'))

2. Aggregate the NYC food retails store data (points) to the zip code data

nyc_food_store_filter <- nyc_food_store_2263 %>% filter(str_detect(Estbl_T,'[AJ]'))
  
covid_food_join <- sf::st_join(covid_zip_merge,nyc_food_store_filter,
                    join=st_contains,left=T) %>% group_by(ZIPCODE)%>% 
                      summarise(storecount = n(),name=max(NEIGHBORHOOD_NAME),
                        borough = max(BOROUGH_GROUP), 
                        case_count = max(COVID_CASE_COUNT),
                        case_rate = max(COVID_CASE_RATE),
                        pop_denominator = max(POP_DENOMINATOR),
                        death_count = max(COVID_DEATH_COUNT),
                        death_rate = max(COVID_DEATH_RATE),
                        perc_positive = max(PERCENT_POSITIVE),  
                        total_tests = max(TOTAL_COVID_TESTS))

3. Aggregate the NYC health facilities (points) to the zip code data

zipC19FoodHosp <- left_join(covid_food_join,nyc_hosp,
              by=c('ZIPCODE'='Operator Zip Code')) %>% 
              group_by(ZIPCODE)%>%
              summarise(hfacility_count = n(),
              storecount=max(storecount),borough=max(borough),
              case_count=max(case_count),case_rate=max(case_rate),
              pop_denominator=max(pop_denominator),
              death_count=max(death_count),death_rate=max(death_rate),
         perc_positive=max(perc_positive),total_tests=max(total_tests))%>%
          ungroup()

4. Join the Census ACS population, race, and age data to the NYC Planning Census Tract Data

cs_data <- dplyr::mutate(acs_data,bo_code = (case_when(str_detect(NAME,'Bronx')==1~'2',
              str_detect(NAME,'Kings')==1~'3',str_detect(NAME,'New York County')==1~'1',
              str_detect(NAME,'Queens')==1~'4',str_detect(NAME,'Richmond')==1~'5',
              TRUE ~ as.character(bo_code))))
acs_data$t_code <- stri_sub(acs_data$GEO_ID,-6)
acs_data$id <- paste(acs_data$bo_code,acs_data$t_code,sep='')

acs_tract <- left_join(nyc_tract_2263,acs_data,by=c("boro_ct201"="id"))%>%
  select(GEO_ID, 
         totPop = DP05_0001E, elderlyPop = DP05_0024E,
         malePop = DP05_0002E, femalePop = DP05_0003E,  
         whitePop = DP05_0037E, blackPop = DP05_0038E,
         asianPop = DP05_0067E, hispanicPop = DP05_0071E,
         adultPop = DP05_0021E, citizenAdult = DP05_0087E)

5. Aggregate the ACS census data to zip code area data

tract_2_zip <- st_join(zipC19FoodHosp,acs_tract%>%st_centroid(),join=st_contains)%>%
  group_by(ZIPCODE,hfacility_count,storecount,borough,case_count,case_rate,
           pop_denominator,death_count,death_rate,perc_positive,total_tests)%>%
  summarise(totPop = sum(as.numeric(totPop)),
            elderlyPop=sum(as.numeric(elderlyPop)),
            malePop=sum(as.numeric(malePop)),
            femalePop=sum(as.numeric(femalePop)),
            whitePop=sum(as.numeric(whitePop)),
            blackPop=sum(as.numeric(blackPop)),
            asianPop=sum(as.numeric(asianPop)),
            hispanicPop=sum(as.numeric(hispanicPop)),
            adultPop=sum(as.numeric(hispanicPop)),
            adultPop=sum(as.numeric(adultPop)),
            citizenAdult=sum(as.numeric(citizenAdult))) %>% ungroup()