# Load necessary packages
library(tidyverse)
library(plotly)
library(readxl)
# Load data
initial_data2009 <- read_excel("MMG2011_2009Data_ToShare.xlsx",
sheet = "County")
initial_data2010 <- read_excel("MMG2012_2010Data_ToShare.xlsx",
sheet = "County")
initial_data2011 <- read_excel("MMG2013_2011Data_ToShare.xlsx",
sheet = "2011 County")
initial_data2012 <- read_excel("MMG2014_2012Data_ToShare.xlsx",
sheet = "2012 County")
initial_data2013 <- read_excel("MMG2015_2013Data_ToShare.xlsx",
sheet = "2013 County")
initial_data2014 <- read_excel("MMG2016_2014Data_ToShare.xlsx",
sheet = "2014 County")
initial_data2015 <- read_excel("MMG2017_2015Data_ToShare.xlsx",
sheet = "2015 County")
initial_data2016 <- read_excel("MMG2018_2016Data_ToShare.xlsx",
sheet = "2016 County")
initial_data2017 <- read_excel("MMG2019_2017Data_ToShare.xlsx",
sheet = "2017 County")
initial_data2018 <- read_excel("MMG2020_2018Data_ToShare.xlsx",
sheet = "2018 County")
initial_data2019_2022 <- read_excel("Meal Gap Data exl.xlsx")
# Filter by state and county (2009)
filtered09 <- initial_data2009 %>%
filter(`State Name` == "GA") %>%
select(County = `County Code`, `Child FI Rate 2009` = `Child FI Rate`)
data2009 <- filtered09[c(6, 7, 29, 52, 59, 68, 73, 78, 95, 108, 109, 119, 127, 139, 154), ]
# Filter by state and county (2010)
filtered10 <- initial_data2010 %>%
filter(State == "GA") %>%
mutate(County = str_remove_all(`County, State`, ", Georgia"), `Child FI Rate 2010` = `2010 Child food insecurity rate`, .keep = "none")
data2010 <- filtered10[c(6, 7, 29, 52, 59, 68, 73, 78, 97, 108, 109, 119, 127, 139, 154), ]
# Filter by state and county (2011)
filtered11 <- initial_data2011 %>%
filter(State == "GA") %>%
mutate(County = str_remove_all(`County, State`, ", Georgia"), `Child FI Rate 2011` = `2011 Child Food Insecurity Rate`, .keep = "none")
data2011 <- filtered11[c(6, 7, 29, 52, 59, 68, 73, 78, 97, 108, 109, 119, 127, 139, 154), ]
# Filter by state and county (2012)
filtered12 <- initial_data2012 %>%
filter(State == "GA") %>%
mutate(County = str_remove_all(`County, State`, ", Georgia"), `Child FI Rate 2012` = `2012 Child food insecurity rate`, .keep = "none")
data2012 <- filtered12[c(6, 7, 29, 52, 59, 68, 73, 78, 97, 108, 109, 119, 127, 139, 154), ]
# Filter by state and county (2013)
filtered13 <- initial_data2013 %>%
filter(State == "GA") %>%
mutate(County = str_remove_all(`County, State`, ", Georgia"), `Child FI Rate 2013` = as.numeric(`2013 Child food insecurity rate`), .keep = "none")
data2013 <- filtered13[c(6, 7, 29, 52, 59, 68, 73, 78, 97, 108, 109, 119, 127, 139, 154), ]
# Filter by state and county (2014)
filtered14 <- initial_data2014 %>%
filter(State == "GA") %>%
mutate(County = str_remove_all(`County, State`, ", Georgia"), `Child FI Rate 2014` = `2014 Child food insecurity rate`, .keep = "none")
data2014 <- filtered14[c(6, 7, 29, 52, 59, 68, 73, 78, 97, 108, 109, 119, 127, 139, 154), ]
# Filter by state and county (2015)
filtered15 <- initial_data2015 %>%
filter(State == "GA") %>%
mutate(County = str_remove_all(`County, State`, ", Georgia"), `Child FI Rate 2015` = `2015 Child food insecurity rate`, .keep = "none")
data2015 <- filtered15[c(6, 7, 29, 52, 59, 68, 73, 78, 97, 108, 109, 119, 127, 139, 154), ]
# Filter by state and county (2016)
filtered16 <- initial_data2016 %>%
filter(State == "GA") %>%
mutate(County = str_remove_all(`County, State`, ", Georgia"), `Child FI Rate 2016` = `2016 Child food insecurity rate`, .keep = "none")
data2016 <- filtered16[c(6, 7, 29, 52, 59, 68, 73, 78, 97, 108, 109, 119, 127, 139, 154), ]
# Filter by state and county (2017)
filtered17 <- initial_data2017 %>%
filter(State == "GA") %>%
mutate(County = str_remove_all(`County, State`, ", Georgia"), `Child FI Rate 2017` = `2017 Child food insecurity rate`, .keep = "none")
data2017 <- filtered17[c(6, 7, 29, 52, 59, 68, 73, 78, 97, 108, 109, 119, 127, 139, 154), ]
# Filter by state and county (2018)
filtered18 <- initial_data2018 %>%
filter(State == "GA") %>%
mutate(County = str_remove_all(`County, State`, ", Georgia"), `Child FI Rate 2018` = `2018 Child food insecurity rate`, .keep = "none")
data2018 <- filtered18[c(6, 7, 29, 52, 59, 68, 73, 78, 97, 108, 109, 119, 127, 139, 154), ]
# Filter by state and county (2019-2022)
filtereddata2019_2022 <- dplyr::filter(initial_data2019_2022, `Member 1 ID` == 324 | `County, State` == "Elbert County, Georgia")
# Turn the food insecurity rate from a character/string into a numerical value
# Only keep selected columns: County, FI Rate, Year; multiply FI Rate by 100 to make it a percentage value
data2019_2022 <-
transform(filtereddata2019_2022, `Child FI Rate 2019-2022` = str_remove_all(`Child Food Insecurity Rate`, "%")) %>%
mutate(`Child FI Rate 2019-2022` = as.numeric(`Child FI Rate 2019-2022`), County = str_remove_all(`County, State`, ", Georgia")) %>%
mutate(County, `Child FI Rate 2019-2022` = `Child FI Rate 2019-2022` * 100, Year, .keep = "none")
# Select Food Insecurity Rate column from 2009-2018 (exclude 2010)
FI_2009 <- data2009[2]
FI_2011 <- data2011[2]
FI_2012 <- data2012[2]
FI_2013 <- data2013[2]
FI_2014 <- data2014[2]
FI_2015 <- data2015[2]
FI_2016 <- data2016[2]
FI_2017 <- data2017[2]
FI_2018 <- data2018[2]
# Add single-column FI rate data from 2009, 2011-2018 to the 2010 data as new columns
# I add the remaining data to 2010 to retain the desired names for the counties
# In the 2009 data, the counties are labelled in all caps which I do not want
# Use pivot_longer to stack all the data from each year on top of each other into one column
# Rename columns, mutate Year to remove all letters
# Relocate Year to be the first column
# Multiply Food Insecurity Rate by 100, make Year into an integer
# Select desired columns: Year, County, Food Insecurity Rate
Overall_Child_FI_data_2009_2018 <- data2010 %>%
mutate(FI_2009, FI_2011, FI_2012, FI_2013, FI_2014, FI_2015, FI_2016, FI_2017, FI_2018) %>%
relocate(`Child FI Rate 2009`, .before = `Child FI Rate 2010`) %>%
pivot_longer(`Child FI Rate 2009`:`Child FI Rate 2018`) %>%
mutate(`Child Food Insecurity Rate` = value, County, Year = str_remove_all(name, "Child FI Rate "), .keep = "none") %>%
relocate(Year, .before = County) %>%
mutate(`Child Food Insecurity Rate` = `Child Food Insecurity Rate` * 100, Year = as.integer(Year)) %>%
select(Year, County, `Child Food Insecurity Rate`)
# In a new table, join 2009-2018 data and 2019-2022 data by Year
# Join counties into one column, join FI Rate into one column
# Select desired columns: County, FI Rate, Year
Overall_Child_FI_Data <- full_join(Overall_Child_FI_data_2009_2018, data2019_2022, by = "Year") %>%
mutate(County = coalesce(County.x, County.y), `Child FI Rate` = coalesce(`Child Food Insecurity Rate`, `Child FI Rate 2019-2022`)) %>%
select(County, `Child FI Rate`, Year)Research methodology changed in 2020, so data from 2018 onward should not be compared to data before 2018.