# 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`, `2009 Budget` = `Weighted Annual Dollars`) %>%
mutate(`2009 Budget` = as.numeric(`2009 Budget`))
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"), `2010 Budget` = `2010 Weighted Annual Food Budget Shortfall`, .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"), `2011 Budget` = `Weighted Annual Food Budget Shortfall`, .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"), `2012 Budget` = `2012 Weighted Annual Food Budget Shortfall`, .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"), `2013 Budget` = `2013 Weighted Annual Food Budget Shortfall`, .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"), `2014 Budget` = `2014 Weighted Annual Food Budget Shortfall`, .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"), `2015 Budget` = `2015 Weighted Annual Food Budget Shortfall`, .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"), `2016 Budget` = `2016 Weighted Annual Food Budget Shortfall`, .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"), `2017 Budget` = `2017 Weighted Annual Food Budget Shortfall`, .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"), `2018 Budget` = `2018 Weighted Annual Food Budget Shortfall`, .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")
# Remove $ sign and , from Weighted Annual Food Budget Shortfall data entries
# Turn 2019-2022 Budget into a number
# Only keep desired columns
data2019_2022 <- filtereddata2019_2022 %>%
mutate(`2019-2022 Budget` = str_remove_all(`Weighted Annual Food Budget Shortfall`, "\\$")) %>%
mutate(`2019-2022 Budget` = str_remove_all(`Weighted Annual Food Budget Shortfall`, ",")) %>%
mutate(County = str_remove_all(`County, State`, ", Georgia"), `2019-2022 Budget`, Year, .keep = "none") %>%
mutate(`2019-2022 Budget` = as.numeric(`2019-2022 Budget`))
# Select Budget 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 budget 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
# Make year into an integer
Overall_Budget_data_2009_2018 <- data2010 %>%
mutate(FI_2009, FI_2011, FI_2012, FI_2013, FI_2014, FI_2015, FI_2016, FI_2017, FI_2018) %>%
relocate(`2009 Budget`, .before = `2010 Budget`) %>%
pivot_longer(`2009 Budget`:`2018 Budget`)%>%
mutate(`Budget` = value, County, Year = str_remove_all(name, " Budget"), .keep = "none") %>%
relocate(Year, .before = County) %>%
mutate(Year = as.integer(Year))
# In a new table, join 2009-2018 data and 2019-2022 data by Year
# Join counties into one column, join budget into one column
# Select desired columns
# Divide budget data by 1000000
Overall_Budget_Data <- full_join(Overall_Budget_data_2009_2018, data2019_2022, by = "Year") %>%
mutate(County = coalesce(County.x, County.y), `Budget` = coalesce(`Budget`, `2019-2022 Budget`)) %>%
select(County, `Budget`, Year) %>%
mutate(`Budget` = `Budget` / 1000000)Research methodology changed in 2020, so data from 2018 onward should not be compared to data before 2018.