In uva-bi-sdad/dc.food.insecurity: Social Data Commons: Health, Behavior, Diet: Food Insecurity
load packages
library(sf)
library(tidyverse)
library(tmap)
library(tmaptools)
library(tigris)
library(tidycensus)
library(rmapshaper)
library(matrixStats)
library(SpatialAcc)
library(reticulate)
library(dplyr)
library(tidygeocoder)
library(readxl)
library(data.table)
ADDING COUNTY LEVEL FOOD INSECURITY TO THE DATABASE
mmg2019 <- read_excel("/project/biocomplexity/sdad/projects_data/mc/data_commons/Feeding_America/Map_the_Meal_Gap_Data/MMG2021_2019Data_ToShare.xlsx", sheet = 2)
mmg2019$Food_Insecurity_Rate <- mmg2019$`2019 Food Insecurity Rate`
mmg2019$Child_Food_Insecurity_Rate <- mmg2019$`2019 Child food insecurity rate`
mmg2019$Cost_Per_Meal <- mmg2019$`2019 Cost Per Meal`
mmg2019$Num_Food_Insecure <- mmg2019$`# of Food Insecure Persons in 2019`
mmg2019$Num_Child_Food_Insecure <- mmg2019$`# of Food Insecure Children in 2019`
# there was a methodological change in 2018, but I keep the data (not sure how they changed it and if they also changed 2019)
mmg2018 <- read_excel("/project/biocomplexity/sdad/projects_data/mc/data_commons/Feeding_America/Map_the_Meal_Gap_Data/MMG2020_2018Data_ToShare.xlsx", sheet = 1)
mmg2018 <- mmg2018[2:nrow(mmg2018),]
mmg2018$Food_Insecurity_Rate <- mmg2018$...4
mmg2018$Child_Food_Insecurity_Rate <- mmg2018$...13
mmg2018$FIPS <- mmg2018$`Data from MMG 2020 are NOT directly comparable to data from any prior MMG study due to methodological changes made in 2020.`
mmg2018$Cost_Per_Meal <- mmg2018$...17
mmg2018$Num_Food_Insecure <- mmg2018$...5
mmg2018$Num_Child_Food_Insecure <- mmg2018$...14
mmg2017 <- read_excel("/project/biocomplexity/sdad/projects_data/mc/data_commons/Feeding_America/Map_the_Meal_Gap_Data/MMG2019_2017Data_ToShare.xlsx", sheet = 1)
mmg2017$Food_Insecurity_Rate <- mmg2017$`2017 Food Insecurity Rate`
mmg2017$Child_Food_Insecurity_Rate <- mmg2017$`2017 Child food insecurity rate`
mmg2017$Cost_Per_Meal <- mmg2017$`2017 Cost Per Meal`
mmg2017$Num_Food_Insecure <- mmg2017$`# of Food Insecure Persons in 2017`
mmg2017$Num_Child_Food_Insecure <- mmg2017$`# of Food Insecure Children in 2017`
mmg2016 <- read_excel("/project/biocomplexity/sdad/projects_data/mc/data_commons/Feeding_America/Map_the_Meal_Gap_Data/MMG2018_2016Data_ToShare.xlsx", sheet = 1)
mmg2016$Food_Insecurity_Rate <- mmg2016$`2016 Food Insecurity Rate`
mmg2016$Child_Food_Insecurity_Rate <- mmg2016$`2016 Child food insecurity rate`
mmg2016$Cost_Per_Meal <- mmg2016$`2016 Cost Per Meal`
mmg2016$Num_Food_Insecure <- mmg2016$`# of Food Insecure Persons in 2016`
mmg2016$Num_Child_Food_Insecure <- mmg2016$`# of Food Insecure Children in 2016`
mmg2015 <- read_excel("/project/biocomplexity/sdad/projects_data/mc/data_commons/Feeding_America/Map_the_Meal_Gap_Data/MMG2017_2015Data_ToShare.xlsx", sheet = 1)
mmg2015$Food_Insecurity_Rate <- mmg2015$`2015 Food Insecurity Rate`
mmg2015$Child_Food_Insecurity_Rate <- mmg2015$`2015 Child food insecurity rate`
mmg2015$Cost_Per_Meal <- mmg2015$`2015 Cost Per Meal`
mmg2015$Num_Food_Insecure <- mmg2015$`# of Food Insecure Persons in 2015`
mmg2015$Num_Child_Food_Insecure <- mmg2015$`# of Food Insecure Children in 2015`
mmg2014 <- read_excel("/project/biocomplexity/sdad/projects_data/mc/data_commons/Feeding_America/Map_the_Meal_Gap_Data/MMG2016_2014Data_ToShare.xlsx", sheet = 1)
mmg2014$Food_Insecurity_Rate <- mmg2014$`2014 Food Insecurity Rate`
mmg2014$Child_Food_Insecurity_Rate <- mmg2014$`2014 Child food insecurity rate`
mmg2014$Cost_Per_Meal <- mmg2014$`2014 Cost Per Meal`
mmg2014$Num_Food_Insecure <- mmg2014$`# of Food Insecure Persons in 2014`
mmg2014$Num_Child_Food_Insecure <- mmg2014$`# of Food Insecure Children in 2014`
mmg2013 <- read_excel("/project/biocomplexity/sdad/projects_data/mc/data_commons/Feeding_America/Map_the_Meal_Gap_Data/MMG2015_2013Data_ToShare.xlsx", sheet = 2)
mmg2013$Food_Insecurity_Rate <- mmg2013$`2013 Food Insecurity Rate`
mmg2013$Child_Food_Insecurity_Rate <- mmg2013$`2013 Child food insecurity rate`
mmg2013$Cost_Per_Meal <- mmg2013$`2013 Cost Per Meal`
mmg2013$Num_Food_Insecure <- mmg2013$`# of Food Insecure Persons in 2013`
mmg2013$Num_Child_Food_Insecure <- mmg2013$`# of Food Insecure Children in 2013`
mmg2012 <- read_excel("/project/biocomplexity/sdad/projects_data/mc/data_commons/Feeding_America/Map_the_Meal_Gap_Data/MMG2014_2012Data_ToShare.xlsx", sheet = 2)
mmg2012$Food_Insecurity_Rate <- mmg2012$`2012 Food Insecurity Rate`
mmg2012$Child_Food_Insecurity_Rate <- mmg2012$`2012 Child food insecurity rate`
mmg2012$Cost_Per_Meal <- mmg2012$`2012 Cost Per Meal`
mmg2012$Num_Food_Insecure <- mmg2012$`# of Food Insecure Persons in 2012`
mmg2012$Num_Child_Food_Insecure <- mmg2012$`# of Food Insecure Children in 2012`
# we dont have ACS variables needed for these years - not 100%
mmg2011 <- read_excel("/project/biocomplexity/sdad/projects_data/mc/data_commons/Feeding_America/Map_the_Meal_Gap_Data/MMG2013_2011Data_ToShare.xlsx", sheet = 2)
mmg2011$Food_Insecurity_Rate <- mmg2011$`2011 Food Insecurity Rate`
mmg2011$Child_Food_Insecurity_Rate <- mmg2011$`2011 Child Food Insecurity Rate`
mmg2011$Cost_Per_Meal <- mmg2011$`Cost Per Meal`
mmg2011$Num_Food_Insecure <- mmg2011$`Number of Food Insecure Persons in 2011`
mmg2011$Num_Child_Food_Insecure <- mmg2011$`Number of Food Insecure Children in 2011`
mmg2010 <- read_excel("/project/biocomplexity/sdad/projects_data/mc/data_commons/Feeding_America/Map_the_Meal_Gap_Data/MMG2012_2010Data_ToShare.xlsx", sheet = 2)
mmg2010$Food_Insecurity_Rate <- mmg2010$`Food Insecurity Rate`
mmg2010$Child_Food_Insecurity_Rate <- mmg2010$`Child food insecurity rate`
mmg2010$Cost_Per_Meal <- mmg2010$`Cost Per Meal`
mmg2010$Num_Food_Insecure <- mmg2010$`Number of Food Insecure Persons`
mmg2010$Num_Child_Food_Insecure <- mmg2010$`Estimated number of food insecure children`
files <- list(mmg2019, mmg2018, mmg2017, mmg2016, mmg2015, mmg2014, mmg2013, mmg2012, mmg2011, mmg2010)
prepate feeding america data and standardize
# start with 2019 and go backward
year <- 2019
all_county_fa_data <- matrix(, nrow = 0, ncol = 5)
for (i in 1:length(files))
{
my_data_2 <- files[[i]]
# change fips to characters and where codes only have 4 characters, add 0 to beginning
my_data_2$FIPS <- as.character(my_data_2$FIPS)
idxs <- which(nchar(my_data_2$FIPS) == 4)
my_data_2$FIPS[idxs] <- paste0(0, my_data_2$FIPS[idxs])
# merge acs data wth food insecurity data from feeding america
county_fa_data <- my_data_2[, c("Food_Insecurity_Rate", "Child_Food_Insecurity_Rate", "Cost_Per_Meal", "Num_Food_Insecure", "Num_Child_Food_Insecure", "FIPS")] %>% mutate(yr = year)
all_county_fa_data <- rbind(all_county_fa_data, county_fa_data)
year <- year - 1
}
format and send county level food insecurity measurements to database
us.co <- get_acs(geography = "county",
year = 2019,
variables = c(tpop = "B01003_001"),
survey = "acs5",
output = "wide",
geometry = TRUE)
all_county_fa_data_2 <- all_county_fa_data %>%
mutate(Food_Insecurity_Rate = as.numeric(Food_Insecurity_Rate) * 100,
Child_Food_Insecurity_Rate = as.numeric(Child_Food_Insecurity_Rate) * 100,
Cost_Per_Meal = as.numeric(Cost_Per_Meal)) %>%
gather(measure, value, c(Food_Insecurity_Rate, Child_Food_Insecurity_Rate, Cost_Per_Meal, Num_Food_Insecure, Num_Child_Food_Insecure)) %>%
merge(st_drop_geometry(us.co)[, c("GEOID", "NAME")], by.x = "FIPS", by.y = "GEOID") %>%
rename(geoid = FIPS,
year = yr,
region_name = NAME) %>%
mutate(measure_type = ifelse(measure %in% c("Food_Insecurity_Rate", "Child_Food_Insecurity_Rate"), "percent", ifelse(measure == "Cost_Per_Meal", "cost", "count")),
measure_units = as.character(NA),
region_type = "county") %>%
relocate("geoid", "region_type", "region_name", "year", "measure", "value", "measure_type", "measure_units") %>%
mutate(value = as.numeric(value))
# send national data to databse
source("~/git/VDH/src/helper_functions.R")
con <- get_db_conn(db_pass = "rsu8zvrsu8zv")
dc_dbWriteTable(con, "dc_health_behavior_diet", "reg_ct_fa_2010_2019_food_insecurity_and_cost", all_county_fa_data_2)
dbDisconnect(con)
virginia_fa_data <- all_county_fa_data_2[substr(all_county_fa_data_2$geoid, 1, 2) == "51",]
ncr_fa_data <- all_county_fa_data_2 %>% filter(geoid %in% c("51013", "51059", "51107", "51510", "51600", "51153", "51683", "51685", "51610", "11001", "24031", "24033", "24017", "24021"))
# send subset data to db
con <- get_db_conn(db_pass = "rsu8zvrsu8zv")
dc_dbWriteTable(con, "dc_health_behavior_diet", "va_ct_fa_2010_2019_food_insecurity_and_cost", va_county_fa_data_db_3)
dc_dbWriteTable(con, "dc_health_behavior_diet", "ncr_ct_fa_2010_2019_food_insecurity_and_cost", va_county_fa_data_db_3)
dbDisconnect(con)
get necessary county level ACS variables
# start with 2019 and go backward
year <- 2019
all_county_fa_data <- matrix(, nrow = 0, ncol = 7)
for (i in 1:length(files))
{
us.co <- get_acs(geography = "county",
year = year,
variables = c(tpop = "B01003_001",
male_under_5 = "B01001_003",
male_5_9 = "B01001_004",
male_10_14 = "B01001_005",
male_15_17 = "B01001_006",
female_under_5 = "B01001_027",
female_5_9 = "B01001_028",
female_10_14 = "B01001_029",
female_15_17 = "B01001_030"),
survey = "acs5",
output = "wide",
geometry = TRUE)
us.co2 <- us.co %>%
mutate(child_pop = male_under_5E + male_5_9E + male_10_14E + male_15_17E + female_under_5E + female_5_9E + female_10_14E + female_15_17E)
my_data_2 <- files[[i]]
# change fips to characters and where codes only have 4 characters, add 0 to beginning
my_data_2$FIPS <- as.character(my_data_2$FIPS)
idxs <- which(nchar(my_data_2$FIPS) == 4)
my_data_2$FIPS[idxs] <- paste0(0, my_data_2$FIPS[idxs])
# merge acs data wth food insecurity data from feeding america
county_fa_data <- merge(my_data_2[, c("Food_Insecurity_Rate", "Child_Food_Insecurity_Rate", "Cost_Per_Meal", "Num_Food_Insecure", "Num_Child_Food_Insecure", "FIPS")], st_drop_geometry(us.co2)[, c("tpopE", "child_pop", "GEOID")], by.x = "FIPS", by.y = "GEOID") %>% mutate(yr = year)
all_county_fa_data <- rbind(all_county_fa_data, county_fa_data)
year <- year - 1
}
formatting health district data and sending to database
health_district <- read.csv("/project/biocomplexity/sdad/projects_data/vdh/va_county_to_hd.csv")
health_district$county_id <- as.character(health_district$county_id)
con <- get_db_conn(db_pass = "rsu8zvrsu8zv")
health_district_geoids <- st_read(con, query = "SELECT * FROM dc_geographies.va_hd_vdh_2021_health_district_geo_names")
dbDisconnect(con)
va_county_fa_data_db <- all_county_fa_data[substr(all_county_fa_data$FIPS, 1, 2) == "51", ]
va_county_fa_data_db_2 <- va_county_fa_data_db %>%
merge(health_district, by.x = "FIPS", by.y = "county_id") %>%
select(-hpr_name) %>%
mutate(yr = as.character(yr),
Cost_Per_Meal = as.numeric(Cost_Per_Meal),
Num_Food_Insecure = as.numeric(Num_Food_Insecure),
Num_Child_Food_Insecure = as.numeric(Num_Child_Food_Insecure)) %>%
group_by(health_district, yr) %>%
summarise(tpop = sum(tpopE),
Tot_Food_Insecure = sum(Num_Food_Insecure),
child_pop = sum(child_pop),
Tot_Child_Food_Insecure = sum(Num_Child_Food_Insecure),
cost_x_people = sum(Cost_Per_Meal * tpopE))
va_county_fa_data_db_3 <- va_county_fa_data_db_2 %>%
mutate(Food_Insecurity_Rate = Tot_Food_Insecure / tpop,
Child_Food_Insecurity_Rate = Tot_Child_Food_Insecure / child_pop,
Cost_Per_Meal = cost_x_people / tpop) %>%
select(-c(tpop, child_pop, cost_x_people)) %>%
rename(Num_Child_Food_Insecure = Tot_Child_Food_Insecure,
Num_Food_Insecure = Tot_Food_Insecure) %>%
merge(health_district_geoids, by.x = "health_district", by.y = "region_name") %>%
gather(measure, value, c(Num_Food_Insecure, Num_Child_Food_Insecure, Food_Insecurity_Rate, Child_Food_Insecurity_Rate, Cost_Per_Meal)) %>%
mutate(measure_type = ifelse(measure %in% c("Food_Insecurity_Rate", "Child_Food_Insecurity_Rate"), "percent", ifelse(measure == "Cost_Per_Meal", "cost", "count")),
measure_units = as.character(NA)) %>%
rename(region_name = health_district,
year = yr) %>%
relocate("geoid", "region_type", "region_name", "year", "measure", "value", "measure_type", "measure_units")
con <- get_db_conn(db_pass = "rsu8zvrsu8zv")
dc_dbWriteTable(con, "dc_health_behavior_diet", "va_hd_fa_2010_2019_food_insecurity_and_cost", va_county_fa_data_db_3)
dbDisconnect(con)
uva-bi-sdad/dc.food.insecurity documentation built on June 12, 2022, 5:52 a.m.
R Package Documentation
Browse R Packages
We want your feedback!
Note that we can't provide technical support on individual packages. You should contact the package authors for that.
load packages
library(sf) library(tidyverse) library(tmap) library(tmaptools) library(tigris) library(tidycensus) library(rmapshaper) library(matrixStats) library(SpatialAcc) library(reticulate) library(dplyr) library(tidygeocoder) library(readxl) library(data.table)
ADDING COUNTY LEVEL FOOD INSECURITY TO THE DATABASE
mmg2019 <- read_excel("/project/biocomplexity/sdad/projects_data/mc/data_commons/Feeding_America/Map_the_Meal_Gap_Data/MMG2021_2019Data_ToShare.xlsx", sheet = 2) mmg2019$Food_Insecurity_Rate <- mmg2019$`2019 Food Insecurity Rate` mmg2019$Child_Food_Insecurity_Rate <- mmg2019$`2019 Child food insecurity rate` mmg2019$Cost_Per_Meal <- mmg2019$`2019 Cost Per Meal` mmg2019$Num_Food_Insecure <- mmg2019$`# of Food Insecure Persons in 2019` mmg2019$Num_Child_Food_Insecure <- mmg2019$`# of Food Insecure Children in 2019` # there was a methodological change in 2018, but I keep the data (not sure how they changed it and if they also changed 2019) mmg2018 <- read_excel("/project/biocomplexity/sdad/projects_data/mc/data_commons/Feeding_America/Map_the_Meal_Gap_Data/MMG2020_2018Data_ToShare.xlsx", sheet = 1) mmg2018 <- mmg2018[2:nrow(mmg2018),] mmg2018$Food_Insecurity_Rate <- mmg2018$...4 mmg2018$Child_Food_Insecurity_Rate <- mmg2018$...13 mmg2018$FIPS <- mmg2018$`Data from MMG 2020 are NOT directly comparable to data from any prior MMG study due to methodological changes made in 2020.` mmg2018$Cost_Per_Meal <- mmg2018$...17 mmg2018$Num_Food_Insecure <- mmg2018$...5 mmg2018$Num_Child_Food_Insecure <- mmg2018$...14 mmg2017 <- read_excel("/project/biocomplexity/sdad/projects_data/mc/data_commons/Feeding_America/Map_the_Meal_Gap_Data/MMG2019_2017Data_ToShare.xlsx", sheet = 1) mmg2017$Food_Insecurity_Rate <- mmg2017$`2017 Food Insecurity Rate` mmg2017$Child_Food_Insecurity_Rate <- mmg2017$`2017 Child food insecurity rate` mmg2017$Cost_Per_Meal <- mmg2017$`2017 Cost Per Meal` mmg2017$Num_Food_Insecure <- mmg2017$`# of Food Insecure Persons in 2017` mmg2017$Num_Child_Food_Insecure <- mmg2017$`# of Food Insecure Children in 2017` mmg2016 <- read_excel("/project/biocomplexity/sdad/projects_data/mc/data_commons/Feeding_America/Map_the_Meal_Gap_Data/MMG2018_2016Data_ToShare.xlsx", sheet = 1) mmg2016$Food_Insecurity_Rate <- mmg2016$`2016 Food Insecurity Rate` mmg2016$Child_Food_Insecurity_Rate <- mmg2016$`2016 Child food insecurity rate` mmg2016$Cost_Per_Meal <- mmg2016$`2016 Cost Per Meal` mmg2016$Num_Food_Insecure <- mmg2016$`# of Food Insecure Persons in 2016` mmg2016$Num_Child_Food_Insecure <- mmg2016$`# of Food Insecure Children in 2016` mmg2015 <- read_excel("/project/biocomplexity/sdad/projects_data/mc/data_commons/Feeding_America/Map_the_Meal_Gap_Data/MMG2017_2015Data_ToShare.xlsx", sheet = 1) mmg2015$Food_Insecurity_Rate <- mmg2015$`2015 Food Insecurity Rate` mmg2015$Child_Food_Insecurity_Rate <- mmg2015$`2015 Child food insecurity rate` mmg2015$Cost_Per_Meal <- mmg2015$`2015 Cost Per Meal` mmg2015$Num_Food_Insecure <- mmg2015$`# of Food Insecure Persons in 2015` mmg2015$Num_Child_Food_Insecure <- mmg2015$`# of Food Insecure Children in 2015` mmg2014 <- read_excel("/project/biocomplexity/sdad/projects_data/mc/data_commons/Feeding_America/Map_the_Meal_Gap_Data/MMG2016_2014Data_ToShare.xlsx", sheet = 1) mmg2014$Food_Insecurity_Rate <- mmg2014$`2014 Food Insecurity Rate` mmg2014$Child_Food_Insecurity_Rate <- mmg2014$`2014 Child food insecurity rate` mmg2014$Cost_Per_Meal <- mmg2014$`2014 Cost Per Meal` mmg2014$Num_Food_Insecure <- mmg2014$`# of Food Insecure Persons in 2014` mmg2014$Num_Child_Food_Insecure <- mmg2014$`# of Food Insecure Children in 2014` mmg2013 <- read_excel("/project/biocomplexity/sdad/projects_data/mc/data_commons/Feeding_America/Map_the_Meal_Gap_Data/MMG2015_2013Data_ToShare.xlsx", sheet = 2) mmg2013$Food_Insecurity_Rate <- mmg2013$`2013 Food Insecurity Rate` mmg2013$Child_Food_Insecurity_Rate <- mmg2013$`2013 Child food insecurity rate` mmg2013$Cost_Per_Meal <- mmg2013$`2013 Cost Per Meal` mmg2013$Num_Food_Insecure <- mmg2013$`# of Food Insecure Persons in 2013` mmg2013$Num_Child_Food_Insecure <- mmg2013$`# of Food Insecure Children in 2013` mmg2012 <- read_excel("/project/biocomplexity/sdad/projects_data/mc/data_commons/Feeding_America/Map_the_Meal_Gap_Data/MMG2014_2012Data_ToShare.xlsx", sheet = 2) mmg2012$Food_Insecurity_Rate <- mmg2012$`2012 Food Insecurity Rate` mmg2012$Child_Food_Insecurity_Rate <- mmg2012$`2012 Child food insecurity rate` mmg2012$Cost_Per_Meal <- mmg2012$`2012 Cost Per Meal` mmg2012$Num_Food_Insecure <- mmg2012$`# of Food Insecure Persons in 2012` mmg2012$Num_Child_Food_Insecure <- mmg2012$`# of Food Insecure Children in 2012` # we dont have ACS variables needed for these years - not 100% mmg2011 <- read_excel("/project/biocomplexity/sdad/projects_data/mc/data_commons/Feeding_America/Map_the_Meal_Gap_Data/MMG2013_2011Data_ToShare.xlsx", sheet = 2) mmg2011$Food_Insecurity_Rate <- mmg2011$`2011 Food Insecurity Rate` mmg2011$Child_Food_Insecurity_Rate <- mmg2011$`2011 Child Food Insecurity Rate` mmg2011$Cost_Per_Meal <- mmg2011$`Cost Per Meal` mmg2011$Num_Food_Insecure <- mmg2011$`Number of Food Insecure Persons in 2011` mmg2011$Num_Child_Food_Insecure <- mmg2011$`Number of Food Insecure Children in 2011` mmg2010 <- read_excel("/project/biocomplexity/sdad/projects_data/mc/data_commons/Feeding_America/Map_the_Meal_Gap_Data/MMG2012_2010Data_ToShare.xlsx", sheet = 2) mmg2010$Food_Insecurity_Rate <- mmg2010$`Food Insecurity Rate` mmg2010$Child_Food_Insecurity_Rate <- mmg2010$`Child food insecurity rate` mmg2010$Cost_Per_Meal <- mmg2010$`Cost Per Meal` mmg2010$Num_Food_Insecure <- mmg2010$`Number of Food Insecure Persons` mmg2010$Num_Child_Food_Insecure <- mmg2010$`Estimated number of food insecure children` files <- list(mmg2019, mmg2018, mmg2017, mmg2016, mmg2015, mmg2014, mmg2013, mmg2012, mmg2011, mmg2010)
prepate feeding america data and standardize
# start with 2019 and go backward year <- 2019 all_county_fa_data <- matrix(, nrow = 0, ncol = 5) for (i in 1:length(files)) { my_data_2 <- files[[i]] # change fips to characters and where codes only have 4 characters, add 0 to beginning my_data_2$FIPS <- as.character(my_data_2$FIPS) idxs <- which(nchar(my_data_2$FIPS) == 4) my_data_2$FIPS[idxs] <- paste0(0, my_data_2$FIPS[idxs]) # merge acs data wth food insecurity data from feeding america county_fa_data <- my_data_2[, c("Food_Insecurity_Rate", "Child_Food_Insecurity_Rate", "Cost_Per_Meal", "Num_Food_Insecure", "Num_Child_Food_Insecure", "FIPS")] %>% mutate(yr = year) all_county_fa_data <- rbind(all_county_fa_data, county_fa_data) year <- year - 1 }
format and send county level food insecurity measurements to database
us.co <- get_acs(geography = "county", year = 2019, variables = c(tpop = "B01003_001"), survey = "acs5", output = "wide", geometry = TRUE) all_county_fa_data_2 <- all_county_fa_data %>% mutate(Food_Insecurity_Rate = as.numeric(Food_Insecurity_Rate) * 100, Child_Food_Insecurity_Rate = as.numeric(Child_Food_Insecurity_Rate) * 100, Cost_Per_Meal = as.numeric(Cost_Per_Meal)) %>% gather(measure, value, c(Food_Insecurity_Rate, Child_Food_Insecurity_Rate, Cost_Per_Meal, Num_Food_Insecure, Num_Child_Food_Insecure)) %>% merge(st_drop_geometry(us.co)[, c("GEOID", "NAME")], by.x = "FIPS", by.y = "GEOID") %>% rename(geoid = FIPS, year = yr, region_name = NAME) %>% mutate(measure_type = ifelse(measure %in% c("Food_Insecurity_Rate", "Child_Food_Insecurity_Rate"), "percent", ifelse(measure == "Cost_Per_Meal", "cost", "count")), measure_units = as.character(NA), region_type = "county") %>% relocate("geoid", "region_type", "region_name", "year", "measure", "value", "measure_type", "measure_units") %>% mutate(value = as.numeric(value)) # send national data to databse source("~/git/VDH/src/helper_functions.R") con <- get_db_conn(db_pass = "rsu8zvrsu8zv") dc_dbWriteTable(con, "dc_health_behavior_diet", "reg_ct_fa_2010_2019_food_insecurity_and_cost", all_county_fa_data_2) dbDisconnect(con) virginia_fa_data <- all_county_fa_data_2[substr(all_county_fa_data_2$geoid, 1, 2) == "51",] ncr_fa_data <- all_county_fa_data_2 %>% filter(geoid %in% c("51013", "51059", "51107", "51510", "51600", "51153", "51683", "51685", "51610", "11001", "24031", "24033", "24017", "24021")) # send subset data to db con <- get_db_conn(db_pass = "rsu8zvrsu8zv") dc_dbWriteTable(con, "dc_health_behavior_diet", "va_ct_fa_2010_2019_food_insecurity_and_cost", va_county_fa_data_db_3) dc_dbWriteTable(con, "dc_health_behavior_diet", "ncr_ct_fa_2010_2019_food_insecurity_and_cost", va_county_fa_data_db_3) dbDisconnect(con)
get necessary county level ACS variables
# start with 2019 and go backward year <- 2019 all_county_fa_data <- matrix(, nrow = 0, ncol = 7) for (i in 1:length(files)) { us.co <- get_acs(geography = "county", year = year, variables = c(tpop = "B01003_001", male_under_5 = "B01001_003", male_5_9 = "B01001_004", male_10_14 = "B01001_005", male_15_17 = "B01001_006", female_under_5 = "B01001_027", female_5_9 = "B01001_028", female_10_14 = "B01001_029", female_15_17 = "B01001_030"), survey = "acs5", output = "wide", geometry = TRUE) us.co2 <- us.co %>% mutate(child_pop = male_under_5E + male_5_9E + male_10_14E + male_15_17E + female_under_5E + female_5_9E + female_10_14E + female_15_17E) my_data_2 <- files[[i]] # change fips to characters and where codes only have 4 characters, add 0 to beginning my_data_2$FIPS <- as.character(my_data_2$FIPS) idxs <- which(nchar(my_data_2$FIPS) == 4) my_data_2$FIPS[idxs] <- paste0(0, my_data_2$FIPS[idxs]) # merge acs data wth food insecurity data from feeding america county_fa_data <- merge(my_data_2[, c("Food_Insecurity_Rate", "Child_Food_Insecurity_Rate", "Cost_Per_Meal", "Num_Food_Insecure", "Num_Child_Food_Insecure", "FIPS")], st_drop_geometry(us.co2)[, c("tpopE", "child_pop", "GEOID")], by.x = "FIPS", by.y = "GEOID") %>% mutate(yr = year) all_county_fa_data <- rbind(all_county_fa_data, county_fa_data) year <- year - 1 }
formatting health district data and sending to database
health_district <- read.csv("/project/biocomplexity/sdad/projects_data/vdh/va_county_to_hd.csv") health_district$county_id <- as.character(health_district$county_id) con <- get_db_conn(db_pass = "rsu8zvrsu8zv") health_district_geoids <- st_read(con, query = "SELECT * FROM dc_geographies.va_hd_vdh_2021_health_district_geo_names") dbDisconnect(con) va_county_fa_data_db <- all_county_fa_data[substr(all_county_fa_data$FIPS, 1, 2) == "51", ] va_county_fa_data_db_2 <- va_county_fa_data_db %>% merge(health_district, by.x = "FIPS", by.y = "county_id") %>% select(-hpr_name) %>% mutate(yr = as.character(yr), Cost_Per_Meal = as.numeric(Cost_Per_Meal), Num_Food_Insecure = as.numeric(Num_Food_Insecure), Num_Child_Food_Insecure = as.numeric(Num_Child_Food_Insecure)) %>% group_by(health_district, yr) %>% summarise(tpop = sum(tpopE), Tot_Food_Insecure = sum(Num_Food_Insecure), child_pop = sum(child_pop), Tot_Child_Food_Insecure = sum(Num_Child_Food_Insecure), cost_x_people = sum(Cost_Per_Meal * tpopE)) va_county_fa_data_db_3 <- va_county_fa_data_db_2 %>% mutate(Food_Insecurity_Rate = Tot_Food_Insecure / tpop, Child_Food_Insecurity_Rate = Tot_Child_Food_Insecure / child_pop, Cost_Per_Meal = cost_x_people / tpop) %>% select(-c(tpop, child_pop, cost_x_people)) %>% rename(Num_Child_Food_Insecure = Tot_Child_Food_Insecure, Num_Food_Insecure = Tot_Food_Insecure) %>% merge(health_district_geoids, by.x = "health_district", by.y = "region_name") %>% gather(measure, value, c(Num_Food_Insecure, Num_Child_Food_Insecure, Food_Insecurity_Rate, Child_Food_Insecurity_Rate, Cost_Per_Meal)) %>% mutate(measure_type = ifelse(measure %in% c("Food_Insecurity_Rate", "Child_Food_Insecurity_Rate"), "percent", ifelse(measure == "Cost_Per_Meal", "cost", "count")), measure_units = as.character(NA)) %>% rename(region_name = health_district, year = yr) %>% relocate("geoid", "region_type", "region_name", "year", "measure", "value", "measure_type", "measure_units") con <- get_db_conn(db_pass = "rsu8zvrsu8zv") dc_dbWriteTable(con, "dc_health_behavior_diet", "va_hd_fa_2010_2019_food_insecurity_and_cost", va_county_fa_data_db_3) dbDisconnect(con)
R Package Documentation
Browse R Packages
We want your feedback!
Note that we can't provide technical support on individual packages. You should contact the package authors for that.
Embedding an R snippet on your website
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.