R Notebook

library(ggplot2)
library(tidyr)
library(dplyr)
library(readxl)
library(stringr)

#cleaning and isolating data from food insecurity dataset

  #isolate ny in the data
fi_bystate = food_insecurity_df %>% filter(State=='NY')

  #isolate 2021 in the data
fi_bystate_2021 = fi_bystate %>% filter(Year == 2021)

#colnames(fi_bystate_2021)

  #split county, state into seperate columsn to isolate county
  #https://www.geeksforgeeks.org/how-to-split-column-into-multiple-columns-in-r-dataframe/
fi_bystate_2021[c('County', 'State')] <- str_split_fixed(fi_bystate_2021$`County, State`, ',', 2)

  #drop unnecessary columns
  #https://stackoverflow.com/questions/4605206/drop-data-frame-columns-by-name
drops = c("FIPS", "State", "County, State", "SNAP Threshold", "% FI ≤ SNAP Threshold", 
                   "% FI > SNAP Threshold" )
fi_bystate_2021 = fi_bystate_2021[ , !(names(fi_bystate_2021) %in% drops)]

  #drop "county" from county identifier
fi_bystate_2021[c('County', 'drop')] <- str_split_fixed(fi_bystate_2021$County, ' ', 2)
fi_bycounty_2021 = fi_bystate_2021[ , !(names(fi_bystate_2021) %in% c("drop", "Year"))]

  #rearrange columns so county is is the first column
fi_bycounty_2021 = fi_bycounty_2021 %>% select("County", everything())

  #rename 2 columns
fi_bycounty_2021$County[31] <- "New York"
fi_bycounty_2021$County[45] <- "St. Lawrence"

  #fix variable types
#str(fi_bycounty_2021)

fi_bycounty_2021$`Food Insecurity Rate among Black Persons (all ethnicities)` = as.numeric(fi_bycounty_2021$`Food Insecurity Rate among Black Persons (all ethnicities)`)

Warning: NAs introduced by coercion

fi_bycounty_2021$`Food Insecurity Rate among Hispanic Persons (any race)` = as.numeric(fi_bycounty_2021$`Food Insecurity Rate among Hispanic Persons (any race)`)

Warning: NAs introduced by coercion

fi_bycounty_2021$`Food Insecurity Rate among White, non-Hispanic Persons` = as.numeric(fi_bycounty_2021$`Food Insecurity Rate among White, non-Hispanic Persons`)

#str(fi_bycounty_2021)

  #cleaned and adjusted dataset before joining
fi_bycounty_2021

NA
NA

#splitting health issues dataframe into county and region dataframes
health_issues_df["County"]

  #identify columns that seperate the two
regions = c("REGION: Capital Region", "REGION: Central NY", "REGION: Finger Lakes", "REGION: Long Island", "REGION: Mid-Hudson", "REGION: Mohawk Valley", "REGION: New York City", "REGION: North Country", "REGION: Southern Tier", "REGION: Tug Hill Seaway", "REGION: Western NY", "New York City", 
"NYS Exclusive of NYC", "Statewide")

hi_bycounty = health_issues_df[!health_issues_df$County %in% regions, ]
hi_byregion = health_issues_df[health_issues_df$County %in% regions, ]

  #divide all values by 100 so they appear as decimals
hi_columns = colnames(hi_bycounty)
hi_columns <- hi_columns[-1]

hi_bycounty[hi_columns] <- hi_bycounty[hi_columns] / 100


  #cleaned and adjusted dtaset before joining
hi_bycounty

#joining the datasets

  #initial test
df_merged <- merge(x = hi_bycounty, y = fi_bycounty_2021, by = "County", all = TRUE)

  #realized that "Statewide" was in the hi data but not fi data
    #went back to the hi cleaning data, removed "Statewide from the hi_bycounty, and moved it to     hi_byregion

df_merged

```