In uva-bi-sdad/dc.staple.food.basket: Social Data Commons: Health, Behavior, Diet: Staple Food Basket

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)

giant data (obviously would want this to come from scraper, but ran into issues across the board)

# Giant
giant_addys <- c("3115 Lee Hwy., Arlington, VA 22201", "3450 Washington Blvd., Arlington, VA 22201", "3336 Wisconsin Ave NW, Washington, DC 20016",
  "6360 Seven Corners Ctr, Falls Church, VA 22044", "1454 Chain Bridge Rd., McLean, VA 22101", "2501 9th Road South, Arlington, VA 22204",
  "5400 Westbard Ave., Bethesda, MD 20816", "4303 Connecticut Ave NW, Washington DC 20008", "1230 W. Broad St., Falls Church, VA 22046",
  "3480 S. Jefferson St., Falls Chuch, VA 22041", "1345 Park Road NW, Washington DC 20010", "7142 Arlington Road, Bethesda, MD 20814",
  "2901-11 S. Glebe Rd., Arlington, VA 22206", "3131 Duke Streeet, Alexandria, VA 22314", "621 East Glebe Road, Alexandria, VA 22305",
  "6200 Little River Turnpike, Alexandria VA 22312", "6800 Richmond Hwy., Alexandria, VA 22306")
# "5463 Wisconsin Avenue, Chevy Chase, MD 20815" was dropped

giant_present <- list(
     c(T, T, T, T, T, T, T, T, T, T, T, F, T, T, T, T, T, T, T), # 1
     c(T, T, T, T, T, T, T, T, T, T, T, F, T, T, T, T, T, T, T), # 2
     c(T, T, T, T, T, T, T, T, T, T, T, T, T, T, T, T, T, T, T), # 3
     c(T, T, T, T, T, T, T, T, T, T, T, F, T, T, T, T, T, T, T), # 4
     c(T, T, T, T, T, T, T, T, T, T, T, F, T, T, T, T, T, T, T), # 5
     c(T, T, T, T, T, T, T, T, T, T, T, T, T, T, T, T, T, T, T), # 6
     c(T, T, T, T, T, T, T, T, T, T, T, F, T, T, T, T, T, T, T), # 7
     c(T, T, T, T, T, T, T, T, T, T, T, F, T, T, T, T, T, T, T), # 8
     c(T, T, T, T, T, T, T, T, T, T, T, F, T, T, T, T, T, T, T), # 9
     c(T, T, T, T, T, T, T, T, T, T, T, T, T, T, T, T, T, T, T), # 10
     c(T, T, T, T, T, T, T, T, T, T, T, T, T, T, T, T, T, T, T), # 11
     c(T, T, T, T, T, T, T, T, T, T, T, F, T, T, T, T, T, T, T), # 12
     c(T, T, T, T, T, T, T, T, T, T, T, F, T, T, T, T, T, T, T), # 13
     c(T, T, T, T, T, T, T, T, T, T, T, F, T, T, T, T, T, T, T), # 14
     c(T, T, T, T, T, T, T, T, T, T, T, F, T, T, T, T, T, T, T), # 15
     c(T, T, T, T, T, T, T, T, T, T, T, F, T, T, T, T, T, T, T), # 16
     c(T, T, T, T, T, T, T, T, T, T, T, F, T, T, T, T, T, T, T)) # 17

giant_prices <- list(
     c(16 / 6 * 1.25, 3.99 / 2, 2.29, 4.98, 0.08 * 16, 0.23 * 16, 0.30 * 16, 0.19 / 0.75 * 16, 1.50,
       0.05 * 16, 0.14 * 16, NA, 0.34 * 8, 1.50, 0.03 * 16, 0.12 * 16, 1.14 / 0.4, 0.37 * 16, 0.67 * 16 / 5), #1
     c(16 / 6 * 1.25, 3.99 / 2, 2.29, 4.98, 0.08 * 16, 0.23 * 16, 0.30 * 16, 0.19 / 0.75 * 16, 1.50,
       0.05 * 16, 0.14 * 16, NA, 0.34 * 8, 1.50, 0.03 * 16, 0.12 * 16, 1.14 / 0.4, 0.37 * 16, 0.67 * 16 / 5), #2
     c(16 / 6 * 1.25, 3.99 / 2, 1.50, 4.98, 0.08 * 16, 0.23 * 16, 0.30 * 16, 0.19 / 0.75 * 16, 1.50,
       0.05 * 16, 0.14 * 16, 0.14 * 16, 0.34 * 8, 1.50, 0.03 * 16, 0.12 * 16, 1.14 / 0.4, 0.37 * 16, 0.67 * 16 / 5), #3
     c(16 / 6 * 1.25, 3.99 / 2, 1.50, 4.98, 0.08 * 16, 0.23 * 16, 0.30 * 16, 0.19 / 0.75 * 16, 1.50,
       0.05 * 16, 0.14 * 16, NA, 0.34 * 8, 1.50, 0.03 * 16, 0.12 * 16, 1.14 / 0.4, 0.37 * 16, 0.67 * 16 / 5), #4
     c(16 / 6 * 1.25, 3.99 / 2, 1.50, 4.98, 0.08 * 16, 0.23 * 16, 0.30 * 16, 0.19 / 0.75 * 16, 1.50,
       0.05 * 16, 0.14 * 16, NA, 0.34 * 8, 1.50, 0.04 * 16, 0.12 * 16, 1.14 / 0.4, 0.37 * 16, 0.67 * 16 / 5), #5
     c(16 / 6 * 1.25, 3.99 / 2, 1.50, 4.98, 0.08 * 16, 0.23 * 16, 0.30 * 16, 0.19 / 0.75 * 16, 1.50,
       0.05 * 16, 0.14 * 16, 0.14 * 16, 0.34 * 8, 1.50, 0.03 * 16, 0.12 * 16, 1.14 / 0.4, 0.37 * 16, 0.67 * 16 / 5), #6
     c(16 / 6 * 1.25, 3.99 / 2, 1.50, 4.98, 0.08 * 16, 0.23 * 16, 0.30 * 16, 0.19 / 0.75 * 16, 1.50,
       0.05 * 16, 0.14 * 16, NA, 0.34 * 8, 1.50, 0.03 * 16, 0.12 * 16, 1.14 / 0.4, 0.37 * 16, 0.67 * 16 / 5), #7
     c(16 / 6 * 1.25, 3.99 / 2, 1.50, 4.98, 0.08 * 16, 0.23 * 16, 0.30 * 16, 0.19 / 0.75 * 16, 1.50,
       0.05 * 16, 0.14 * 16, NA, 0.34 * 8, 1.50, 0.03 * 16, 0.12 * 16, 1.14 / 0.4, 0.37 * 16, 0.67 * 16 / 5), #8
     c(16 / 6 * 1.25, 3.99 / 2, 1.50, 4.98, 0.08 * 16, 0.23 * 16, 0.30 * 16, 0.19 / 0.75 * 16, 1.50,
       0.05 * 16, 0.14 * 16, NA, 0.34 * 8, 1.50, 0.03 * 16, 0.12 * 16, 1.14 / 0.4, 0.37 * 16, 0.67 * 16 / 5), #9
     c(16 / 6 * 1.25, 3.99 / 2, 1.50, 4.98, 0.08 * 16, 0.23 * 16, 0.30 * 16, 0.19 / 0.75 * 16, 1.50,
       0.05 * 16, 0.14 * 16, 0.14 * 16, 0.34 * 8, 1.50, 0.03 * 16, 0.12 * 16, 1.14 / 0.4, 0.37 * 16, 0.67 * 16 / 5), #10
     c(16 / 6 * 1.25, 3.99 / 2, 1.50, 4.98, 0.08 * 16, 0.23 * 16, 0.30 * 16, 0.19 / 0.75 * 16, 1.50,
       0.05 * 16, 0.14 * 16, 0.14 * 16, 0.34 * 8, 1.50, 0.03 * 16, 0.12 * 16, 1.14 / 0.4, 0.37 * 16, 0.67 * 16 / 5), #11
     c(16 / 6 * 1.25, 3.99 / 2, 1.50, 4.98, 0.08 * 16, 0.23 * 16, 0.30 * 16, 0.19 / 0.75 * 16, 1.50,
       0.05 * 16, 0.14 * 16, NA, 0.34 * 8, 1.50, 0.03 * 16, 0.12 * 16, 1.14 / 0.4, 0.37 * 16, 0.67 * 16 / 5), #12
     c(16 / 6 * 1.25, 3.99 / 2, 2.29, 4.98, 0.08 * 16, 0.23 * 16, 0.30 * 16, 0.19 / 0.75 * 16, 1.50,
       0.05 * 16, 0.14 * 16, NA, 0.34 * 8, 1.50, 0.04 * 16, 0.12 * 16, 1.14 / 0.4, 0.37 * 16, 0.67 * 16 / 5), #13
     c(16 / 6 * 1.25, 3.99 / 2, 1.50, 4.98, 0.08 * 16, 0.23 * 16, 0.30 * 16, 0.19 / 0.75 * 16, 1.50,
       0.05 * 16, 0.14 * 16, NA, 0.34 * 8, 1.50, 0.04 * 16, 0.12 * 16, 1.14 / 0.4, 0.37 * 16, 0.67 * 16 / 5), #14
     c(16 / 6 * 1.25, 3.99 / 2, 2.29, 4.98, 0.08 * 16, 0.23 * 16, 0.30 * 16, 0.19 / 0.75 * 16, 1.50,
       0.05 * 16, 0.14 * 16, NA, 0.34 * 8, 1.50, 0.03 * 16, 0.12 * 16, 1.14 / 0.4, 0.37 * 16, 0.67 * 16 / 5), #15
     c(16 / 6 * 1.25, 3.99 / 2, 1.50, 4.98, 0.08 * 16, 0.23 * 16, 0.30 * 16, 0.19 / 0.75 * 16, 1.50,
       0.05 * 16, 0.14 * 16, NA, 0.34 * 8, 1.50, 0.03 * 16, 0.12 * 16, 1.14 / 0.4, 0.37 * 16, 0.67 * 16 / 5), #16
     c(16 / 6 * 1.25, 3.99 / 2, 1.50, 4.98, 0.08 * 16, 0.23 * 16, 0.30 * 16, 0.19 / 0.75 * 16, 1.50,
       0.05 * 16, 0.14 * 16, NA, 0.34 * 8, 1.50, 0.03 * 16, 0.12 * 16, 1.14 / 0.4, 0.37 * 16, 0.67 * 16 / 5)) #17

# per lb avocado (best deal)
#assume bunch of beets is ~2lb: https://www.foodbankcny.org/assets/Documents/Vegetable-conversion-chart.pdf
# cheapest lb of black beans
# cheese - per lb of shredded cheese?
# corn tortillas per lb
# crackers - WHAT TYPE OF CRACKERS... - store brand cheapest generic grackers...
# canned green chiles??
# assume jalapeno weighs 0.75 oz
# lentils --> chickpeas
# mixed fruit (del monte ???)
# nopalitos
# poblanos?
# White rice, salsa,
# squash -- zucchini or yellow squash?? - assume weight is .4 lb ??

giant_present_matrix <- matrix(unlist(giant_present), ncol = 19, byrow = T)
giant_prices_matrix <- matrix(unlist(giant_prices), ncol = 19, byrow = T)

harris teeter data

# HT
ht_addys <- c("2425 N. Harrison St, Arlington, VA 22207", "301 West Broad, Falls Church, VA 22046", "954 South George Mason, Arlington, VA 22204",
  "4550 King Street, Alexandria, VA 22302", "1631 Kalorama Rd NW Suite 100, Washington DC 20009", "4250 Campbell Ave, Arlington, VA 22206",
  "3600 S Glebe Rd, Arlington, VA 22202", "4641 Duke St, Alexandria, VA 22304", "735 N. Saint Asaph St., Alexandria, VA 22314",
  "6351 Columbia Pike, Falls Church, VA 22041")

ht_present <- list(
     c(T, F, T, T, T, T, T, T, F, T, T, T, F, T, T, T, T, T, T), # 1
     c(T, F, T, T, T, T, T, T, F, T, T, F, F, T, T, T, T, T, T), # 2
     c(T, F, T, T, T, T, T, T, F, F, T, T, F, T, T, T, T, T, T), # 3
     c(T, F, T, T, T, T, T, T, F, F, T, F, F, T, T, T, T, T, T), # 4
     c(T, F, T, T, T, T, T, T, F, F, T, F, F, T, T, T, T, T, T), # 5
     c(T, F, T, T, T, T, T, T, F, F, T, F, F, T, T, T, T, T, T), # 6
     c(T, F, T, T, T, T, T, T, F, T, T, F, F, T, T, T, T, T, T), # 7
     c(T, F, T, T, T, T, T, T, F, F, T, F, F, T, T, T, T, T, T), # 8
     c(T, F, T, T, T, T, T, T, F, F, T, F, F, T, T, T, T, T, T), # 9
     c(T, F, T, T, T, T, T, T, F, F, T, F, F, T, T, T, T, T, T)) # 10

ht_prices <- list(
     c(2.50, NA, 1.59, 0.22 * 16, 0.16 * 16, 0.20 * 16, 0.79 * 4, 2.29, NA,
       0.19 * 16, 0.17 * 16, 0.11 * 16, NA, 1.59, 0.04 * 16, 2.89, 2.19, 2.49, 1.49), #1
     c(2.50, NA, 1.59, 0.22 * 16, 0.16 * 16, 0.20 * 16, .79 * 4, 2.29, NA,
       0.21 * 16, 0.17 * 16, NA, NA, 1.59, 0.04 * 16, 2.89, 2.19, 2.49, 1.49), #2
     c(2.50, NA, 1.59, 0.22 * 16, 0.16 * 16, 0.20 * 16, 0.79 * 4, 2.29, NA,
       NA, 0.17 * 16, 0.11 * 16, NA, 1.59, 0.04 * 16, 2.89, 2.19, 2.49, 1.49), #3
     c(2.50, NA, 1.59, 0.22 * 16, 0.16 * 16, 0.20 * 16, 1.59 * 4, 2.29, NA,
       NA, 0.17 * 16, NA, NA, 1.59, 0.04 * 16, 2.89, 2.19, 2.49, 1.49), #4
     c(2.50, NA, 1.59, 0.22 * 16, 0.16 * 16, 0.20 * 16, 1.59 * 4, 2.29, NA,
       NA, 0.17 * 16, NA, NA, 1.59, 0.04 * 16, 2.89, 2.19, 2.49, 1.49), #5
     c(2.50, NA, 1.59, 0.22 * 16, 0.16 * 16, 0.20 * 16, 1.59 * 4, 2.29, NA,
       NA, 0.17 * 16, NA, NA, 1.59, 0.04 * 16, 2.89, 2.19, 2.49, 1.49), #6
     c(1.25 * 16 / 6, NA, 1.59, 0.22 * 16, 0.16 * 16, 0.20 * 16, 1.59 * 4, 2.29, NA,
       0.21 * 16, 0.17 * 16, NA, NA, 1.59, 0.04 * 16, 2.89, 2.19, 2.49, 1.49), #7
     c(1.25 * 16 / 6, NA, 1.59, 0.22 * 16, 0.16 * 16, 0.20 * 16, 1.59 * 4, 2.29, NA,
       NA, 0.17 * 16, NA, NA, 1.59, 0.04 * 16, 2.89, 2.19, 2.49, 1.49), #8
     c(1.25 * 16 / 6, NA, 1.59, 0.22 * 16, 0.16 * 16, 0.20 * 16, 1.59 * 4, 2.29, NA,
       NA, 0.17 * 16, NA, NA, 1.59, 0.04 * 16, 2.89, 2.19, 2.49, 1.49), #9
     c(1.25 * 16 / 6, NA, 1.59, 0.22 * 16, 0.16 * 16, 0.20 * 16, 1.59 * 4, 2.29, NA,
       NA, 0.17 * 16, NA, NA, 1.59, 0.04 * 16, 2.89, 2.19, 2.49, 1.49)) #10

ht_present_matrix <- matrix(unlist(ht_present), ncol = 19, byrow = T)
ht_prices_matrix <- matrix(unlist(ht_prices), ncol = 19, byrow = T)

safeway data

# Safeway
sw_addys <- c("3713 Lee Hwy., Arlington, VA 22207", "2500 N Harrison St., Arlington, VA 22207", "6244 Old Dominion Dr., Mclean, VA 22101",
  "5101 Wilson Blvd., Arlington, VA 22205", "4701 Sangamore Rd., MD 20816", "1855 Wisconsin Ave NW, Washington DC 20007",
  "6118 Arlington Blvd., Falls Church, VA 22044", "1747 Columbia Rd. NW, Washingon DC, 20009", "3526 King St., Alexandria, VA 22302",
  "299 S Van Dorn St., Alexandria, VA 22304", "500 S Royal St., Alexandria, VA 22314", "6130 Rose Hill Dr., Alexandria, VA 22310",
  "1525 Wilson Blvd., Arlington, VA 22209", "1624 Belle View, Alexandria, VA 22307") # SKIP Balducci's

sw_present <- list(
     c(T, T, T, T, T, T, T, T, T, T, T, F, F, T, T, T, T, T, T), # 1
     c(T, T, T, T, T, T, T, T, T, T, T, F, F, T, T, T, T, T, T), # 2
     c(T, T, T, T, T, T, T, T, T, T, T, F, F, T, T, T, T, T, T), # 3
     c(T, T, T, T, T, T, T, T, T, T, T, F, F, T, T, T, T, T, T), # 4
     c(T, T, T, T, T, T, T, T, T, T, T, F, F, T, T, T, T, T, T), # 5
     c(T, T, T, T, T, T, T, T, T, T, T, F, F, T, T, T, T, T, T), # 6
     c(T, T, T, T, T, T, T, T, T, T, T, F, F, T, T, T, T, T, T), # 7
     c(T, T, T, T, T, T, T, T, T, T, T, F, F, T, T, T, T, T, T), # 8
     c(T, T, T, T, T, T, T, T, T, T, T, F, F, T, T, T, T, T, T), # 9
     c(T, T, T, T, T, T, T, T, T, T, T, F, F, T, T, T, T, T, T), # 10
     c(T, T, T, T, T, T, T, T, T, T, T, F, F, F, T, T, T, T, T), # 11
     c(T, T, T, T, T, T, T, T, T, T, T, F, F, T, T, T, T, T, T), # 12
     c(T, T, T, T, T, T, T, T, T, T, T, F, F, T, T, T, T, T, T), # 13
     c(T, T, T, T, T, T, T, T, T, T, T, F, F, T, T, T, T, T, T)) # 14

# ASSUME ONE BUNCH OF BEETS IS 2 LBS
sw_prices <- list(
     c(0.71 * 16 / 6, 2.99 / 2, 1.99, 0.28 * 16, 4.49 / 73.4 * 16, 0.15 * 16, 1 * 4, 2.49, 0.14 * 16,
       0.75, 0.16 * 16, NA, NA, 1.99, 0.07 * 16, 0.22 * 16, 2.49, 2.99, 2.49), #1
     c(0.71 * 16 / 6, 2.99 / 2, 1.99, 0.28 * 16, 4.49 / 73.4 * 16, 0.15 * 16, 1 * 4, 2.49, 0.14 * 16,
       0.75, 0.16 * 16, NA, NA, 1.99, 0.07 * 16, 0.22 * 16, 2.49, 2.99, 2.49), #2
     c(0.71 * 16 / 6, 2.99 / 2, 1.99, 0.28 * 16, 4.49 / 73.4 * 16, 0.15 * 16, 1 * 4, 2.49, 0.14 * 16,
       0.75, 0.16 * 16, NA, NA, 1.99, 0.07 * 16, 0.22 * 16, 2.49, 2.99, 2.49), #3
     c(0.71 * 16 / 6, 2.99 / 2, 1.99, 0.28 * 16, 4.49 / 73.4 * 16, 0.15 * 16, 1 * 4, 2.49, 0.14 * 16,
       0.75, 0.16 * 16, NA, NA, 1.99, 0.07 * 16, 0.22 * 16, 2.49, 2.99, 2.49), #4
     c(0.71 * 16 / 6, 2.99 / 2, 1.99, 0.28 * 16, 4.49 / 73.4 * 16, 0.15 * 16, 1 * 4, 2.49, 0.14 * 16,
       0.75, 0.16 * 16, NA, NA, 1.99, 0.07 * 16, 0.22 * 16, 2.49, 2.99, 2.49), #5
     c(0.71 * 16 / 6, 2.99 / 2, 1.99, 0.28 * 16, 4.49 / 73.4 * 16, 0.15 * 16, 1 * 4, 2.49, 0.14 * 16,
       0.75, 0.16 * 16, NA, NA, 1.99, 0.07 * 16, 0.22 * 16, 2.49, 2.99, 2.49), #6
     c(0.71 * 16 / 6, 2.99 / 2, 1.99, 0.28 * 16, 4.49 / 73.4 * 16, 0.15 * 16, 1 * 4, 2.49, 0.14 * 16,
       0.75, 0.16 * 16, NA, NA, 1.99, 0.07 * 16, 0.22 * 16, 2.49, 2.99, 2.49), #7
     c(0.71 * 16 / 6, 2.99 / 2, 1.99, 0.28 * 16, 4.49 / 73.4 * 16, 0.15 * 16, 1 * 4, 2.49, 0.14 * 16,
       0.75, 0.16 * 16, NA, NA, 1.99, 0.07 * 16, 0.22 * 16, 2.49, 2.99, 2.49), #8
     c(0.71 * 16 / 6, 2.99 / 2, 1.99, 0.28 * 16, 4.49 / 73.4 * 16, 0.15 * 16, 1 * 4, 2.49, 0.14 * 16,
       0.75, 0.16 * 16, NA, NA, 1.99, 0.07 * 16, 0.22 * 16, 2.49, 2.99, 2.49), #9
     c(0.71 * 16 / 6, 2.99 / 2, 1.99, 0.28 * 16, 4.49 / 73.4 * 16, 0.15 * 16, 1 * 4, 2.49, 0.14 * 16,
       0.75, 0.16 * 16, NA, NA, 1.99, 0.07 * 16, 0.22 * 16, 2.49, 2.99, 2.49), #10
     c(0.71 * 16 / 6, 2.99 / 2, 1.99, 0.28 * 16, 4.49 / 73.4 * 16, 0.15 * 16, 1 * 4, 2.49, 0.14 * 16,
       0.75, 0.16 * 16, NA, NA, NA, 0.07 * 16, 0.22 * 16, 2.49, 2.99, 2.49), #11
     c(0.71 * 16 / 6, 2.99 / 2, 1.99, 0.28 * 16, 4.49 / 73.4 * 16, 0.15 * 16, 1 * 4, 2.49, 0.14 * 16,
       0.75, 0.16 * 16, NA, NA, 1.99, 0.07 * 16, 0.22 * 16, 2.49, 2.99, 2.49), #12
     c(0.71 * 16 / 6, 2.99 / 2, 1.99, 0.28 * 16, 4.49 / 73.4 * 16, 0.15 * 16, 1 * 4, 2.49, 0.14 * 16,
       0.75, 0.16 * 16, NA, NA, 1.99, 0.07 * 16, 0.22 * 16, 2.49, 2.99, 2.49), #13
     c(0.71 * 16 / 6, 2.99 / 2, 1.99, 0.28 * 16, 4.49 / 73.4 * 16, 0.15 * 16, 1 * 4, 2.49, 0.14 * 16,
       0.75, 0.16 * 16, NA, NA, 1.99, 0.07 * 16, 0.22 * 16, 2.49, 2.99, 2.49)) #14

sw_present_matrix <- matrix(unlist(sw_present), ncol = 19, byrow = T)
sw_prices_matrix <- matrix(unlist(sw_prices), ncol = 19, byrow = T)

aldi data

# Aldi
aldi_addys <- c("155 Hillwood Ave, Falls Church, VA 22046", "5725 Columbia Pike, Falls Church, VA 22041", "4602 Kenmore Ave, Alexandria, VA 22304")

aldi_present <- list(
     c(T, F, T, T, T, T, F, T, F, T, T, F, F, T, T, T, T, F, T), # 1
     c(T, F, T, T, T, T, F, T, F, T, T, F, F, T, T, T, T, F, T), # 2
     c(T, F, T, T, T, T, F, T, F, T, T, F, F, T, T, T, T, F, T)) # 3

# ASSUME ONE BUNCH OF BEETS IS 2 LBS
# ASSUME avocado = standard hass avocado 
aldi_prices <- list(
     c(0.89 * 16 / 6, NA, 2.45 / 2, 2.85, 1.35 / 25 * 16, 2.69 / 13.7 * 16, NA, 0.89 * 2, NA,
       2.35 / 4.4, 1.09 / 15 * 16, NA, NA, 1.55 / 2, 1.85 / 3, 1.05 / 24 * 16, 1.65, NA, 3.19 / 20 * 16), #1
     c(0.89 * 16 / 6, NA, 2.45 / 2, 2.85, 1.35 / 25 * 16, 2.69 / 13.7 * 16, NA, 0.89 * 2, NA,
       2.35 / 4.4, 1.09 / 15 * 16, NA, NA, 1.55 / 2, 1.85 / 3, 1.05 / 24 * 16, 1.65, NA, 3.19 / 20 * 16), #2
     c(0.89 * 16 / 6, NA, 2.45 / 2, 2.85, 1.35 / 25 * 16, 2.69 / 13.7 * 16, NA, 0.89 * 2, NA,
       2.35 / 4.4, 1.09 / 15 * 16, NA, NA, 1.55 / 2, 1.85 / 3, 1.05 / 24 * 16, 1.65, NA, 3.19 / 20 * 16)) #3

aldi_present_matrix <- matrix(unlist(aldi_present), ncol = 19, byrow = T)
aldi_prices_matrix <- matrix(unlist(aldi_prices), ncol = 19, byrow = T)

target data

# I don't think this is hitting exactly 5 miles out, but close enough (only site without distances - so using nearby cities)
# Target
target_addys <- c("740 N Glebe Rd., Arlington, VA 22203", "1500 Wilson Blvd., Rosslyn, VA 22209", "6100 Arlington Blvd., Falls Church, VA 22044",
  "500 S. Washington St., Falls Church, VA 22046", "5115 Leesburg Pike, Falls Church, VA 22041", "3101 Richmond Hwy., Alexandria, VA 22305")

target_present <- list(
     c(T, F, F, T, T, T, T, T, F, F, T, F, F, T, T, T, F, F, T), # 1
     c(T, F, F, T, T, T, T, T, F, F, T, F, F, T, T, T, F, F, T), # 2
     c(T, F, T, T, T, T, T, T, T, F, T, F, F, T, T, T, F, F, T), # 3
     c(T, F, T, T, F, T, T, F, T, F, T, F, F, T, T, T, F, F, T), # 4
     c(T, F, T, T, T, T, T, T, T, T, T, F, F, T, T, T, F, F, T), # 5
     c(T, F, T, T, F, T, T, F, T, F, T, F, F, T, T, T, F, F, T)) # 6

# ASSUME ONE BUNCH OF BEETS IS 2 LBS
# ASSUME avocado = standard hass avocado 
target_prices <- list(
     c(0.75 * 16 / 6, NA, NA, 4.19, 2.79 / 25 * 16, 2.79 * 2, 0.99 * 4, 1.89 / 10 * 16, NA,
       NA, 1.79 / 15 * 16, NA, NA, 1.59, 0.89, 2.49, NA, NA, 1.69), #1

     c(0.75 * 16 / 6, NA, NA, 4.19, 2.79 / 25 * 16, 2.79 * 2, 0.99 * 4, 1.89 / 10 * 16, NA,
       NA, 1.79 / 15 * 16, NA, NA, 1.59, 0.89, 2.49, NA, NA, 1.69), #2

     c(0.75 * 16 / 6, NA, 1.79, 3.79, 2.79 / 25 * 16, 2.79 * 2, 0.95 * 4, 1.89 / 10 * 16, 1.79,
       NA, 1.79 / 15 * 16, NA, NA, 1.79, 1.59 / 2, 1.49 / 24 * 16, NA, NA, 1.69), #3

     c(0.89 * 16 / 6, NA, 1.79, 1.99 * 2, NA, 2.79 * 2, 0.95 * 4, NA, 1.79,
       NA, 1.79 / 15 * 16, NA, NA, 1.79, 1.59 / 2, 1.49 / 24 * 16, NA, NA, 1.69), #4

     c(0.75 * 16 / 6, NA, 1.79, 1.99 * 2, 2.79 / 25 * 16, 2.79 * 2, 0.95 * 4, 1.89 / 10 * 16, 1.79,
       3.59 / 4.4, 2.59, NA, NA, 1.79, 1.59 / 2, 1.49 / 24 * 16, NA, NA, 1.69), #5

     c(0.75 * 16 / 6, NA, 1.79, 3.79, 2.79 / 25 * 16, 2.79 * 2, 0.95 * 4, 1.89 / 10 * 16, 1.79,
       NA, 1.79 / 15 * 16, NA, NA, 1.79, 1.59 / 2, 1.49 / 24 * 16, NA, NA, 1.69)) #6

target_present_matrix <- matrix(unlist(target_present), ncol = 19, byrow = T)
target_prices_matrix <- matrix(unlist(target_prices), ncol = 19, byrow = T)

whole foods data

# Whole Foods
wf_addys <- c("520 12th St. South, Ste. 100, Arlington, VA 22202", "2700 Wilson Blvd., Arlington, VA 22201", "2201 I Street NW, Washington DC 20037",
  "101 H St. SE, Washington DC 20003", "5269 River Rd, Bethesda, MD 20816", "1440 P Street NW, Washington DC 20005",
  "1700 Duke St, Alexandria, VA 22314", "7511 Leesburk Pike, Falls Church, VA 22043", "4530 40th St. NW, Tenley Circle, Washington DC 20016",
  "4420 Willard Ave., Chevy Chase, MD 20815")

wf_present <- list(
     c(T, T, T, T, T, T, T, T, T, F, F, F, T, T, T, T, T, T, T), # 1
     c(T, T, T, T, T, T, T, T, T, F, F, F, T, T, T, T, T, T, T), # 2
     c(T, T, T, T, T, T, T, T, T, F, F, F, T, T, T, T, T, F, T), # 3
     c(T, T, T, T, T, T, T, T, T, F, F, F, T, T, T, T, T, T, T), # 4
     c(T, T, T, T, T, T, T, T, T, F, F, F, T, T, T, T, T, T, T), # 5
     c(T, T, T, T, T, T, T, T, T, F, F, F, T, T, T, T, T, T, T), # 6
     c(T, T, T, T, T, T, T, T, T, F, F, F, T, T, T, T, T, T, T), # 7
     c(T, T, T, T, T, T, T, T, T, F, F, F, T, T, T, T, T, T, T), # 8
     c(T, T, T, T, T, T, T, T, T, F, F, F, T, T, T, T, T, T, T), # 9
     c(T, T, T, T, T, T, T, T, T, F, F, F, T, T, T, T, T, T, T)) # 10

# ASSUME avocado = standard hass avocado
# now sure what is meant by mixed fruit once again...
wf_prices <- list(
     c(1 * 16 / 6, 2.49, 3.19, 5.99, 1.59 * 2, 2.99, 1.89 * 4, 1.99, 2.49,
       NA, NA, NA, 3.99, 1.99, 5.99 / 5, 5.69 / 2, 1.99, 3.49, 1.99), #1
     c(1 * 16 / 6, 2.49, 3.19, 5.99, 1.59 * 2, 2.99, 1.89 * 4, 2.99, 2.49,
       NA, NA, NA, 3.99, 3.19, 5.99 / 5, 5.69 / 2, 1.99, 2.99, 1.99), # 2
     c(1 * 16 / 6, 2.49, 3.19, 5.99, 1.59 * 2, 2.99, 1.89 * 4, 1.99, 2.49,
       NA, NA, NA, 3.99, 1.99, 5.99 / 5, 5.69 / 2, 1.99, NA, 1.99), # 3
     c(1 * 16 / 6, 2.49, 3.19, 5.99, 1.59 * 2, 2.99, 1.89 * 4, 3.99, 2.49,
       NA, NA, NA, 3.99, 1.99, 5.99 / 5, 5.69 / 2, 1.99, 2.99, 1.99), # 4
     c(1 * 16 / 6, 2.49, 3.19, 5.99, 1.59 * 2, 2.99, 1.89 * 4, 1.99, 2.49,
       NA, NA, NA, 3.99, 1.99, 5.99 / 5, 5.69 / 2, 1.99, 2.99, 1.99), # 5
     c(1 * 16 / 6, 2.49, 3.19, 5.99, 1.59 * 2, 2.99, 1.89 * 4, 3.99, 2.49,
       NA, NA, NA, 3.99, 1.99, 5.99 / 5, 5.69 / 2, 1.99, 2.99, 1.99), # 6
     c(1 * 16 / 6, 2.49, 3.19, 5.99, 1.59 * 2, 2.99, 1.89 * 4, 1.99, 2.49,
       NA, NA, NA, 3.99, 1.99, 5.99 / 5, 5.69 / 2, 1.99, 2.99, 1.99), # 7
     c(1.25 * 16 / 6, 2.49, 3.19, 5.99, 2.69 / 9.3 * 16, 2.99, 1.89 * 4, 1.99, 2.49,
       NA, NA, NA, 3.99, 1.99, 5.99 / 5, 5.69 / 2, 1.99, 3.49, 1.99), # 8
     c(1 * 16 / 6, 2.49, 3.19, 5.99, 1.59 * 2, 2.99, 1.89 * 4, 3.99, 2.49,
       NA, NA, NA, 3.99, 1.99, 5.99 / 5, 5.69 / 2, 1.99, 2.99, 1.99), # 9
     c(1 * 16 / 6, 2.49, 3.19, 5.99,1.59 * 2 , 2.99, 1.89 * 4, 3.99, 3.19,
       NA, NA, NA, 3.99, 1.99, 5.99 / 5, 5.69 / 2, 1.99, 2.99, 1.99)) # 10

wf_present_matrix <- matrix(unlist(wf_present), ncol = 19, byrow = T)
wf_prices_matrix <- matrix(unlist(wf_prices), ncol = 19, byrow = T)

lidl data

# Lidl
lidl_addys <- c("2901 Gallows Road, Falls Church, VA 22042", "6548 Little River Turnpike, Alexandria, VA 22312") # "3500 S. Clark St., Arlington, VA 22202",  = express store, so dropped

lidl_present <- list(
     c(T, T, T, T, T, T, F, F, F, F, F, F, F, T, T, T, T, F, T), # 1
     c(T, T, T, T, T, T, F, F, F, F, F, F, F, T, T, T, T, F, T)) # 2

# ASSUME beet price is per lb???
# ASSUME avocado = standard hass avocado 
lidl_prices <- list(
     c(1.09 * 16 / 6, 2.24, 1.58, 0.233 * 16, 0.112 * 16, 0.145 * 16, NA, NA, NA,
       NA, NA, NA, NA, 2.16, 0.039 * 16, 0.051 * 16, 1.48, NA, 1.29), #1
     c(1.09 * 16 / 6, 2.24, 1.58, 0.233 * 16, 0.112 * 16, 0.145 * 16, NA, NA, NA,
       NA, NA, NA, NA, 2.16, 0.039 * 16, 0.051 * 16, 1.48, NA, 1.29)) #2

lidl_present_matrix <- matrix(unlist(lidl_present), ncol = 19, byrow = T)
lidl_prices_matrix <- matrix(unlist(lidl_prices), ncol = 19, byrow = T)

all_matrix <- rbind(giant_present_matrix, ht_present_matrix, sw_present_matrix, aldi_present_matrix, target_present_matrix, wf_present_matrix, lidl_present_matrix)

latinx.data <- data.frame(
  address = c(giant_addys, ht_addys, sw_addys, aldi_addys, target_addys, wf_addys, lidl_addys),
  chain =  c(rep(c("Giant", "Harris Teeter", "Safeway", "Aldi", "Target", "Whole Foods","Lidl"), c(length(giant_addys), length(ht_addys), length(sw_addys), length(aldi_addys), length(target_addys), length(wf_addys), length(lidl_addys)))),
  num_items = rowSums(all_matrix),
  item = all_matrix)

# library(tidygeocoder)
# 
# # geocode addresses
# # installed google api key
# 
# readRenviron("~/.Renviron")
# # register_google(Sys.getenv("GOOGLEGEOCODE_API_KEY"))
# Sys.getenv("GOOGLEGEOCODE_API_KEY")
# 
# data.latlon <- geocode(latinx.data, address, method = "osm")
# d2 <- st_as_sf(cbind(latinx.data, data.latlon),
#                coords = c("lon", "lat"))

# latinx.data
d2 <- st_read("/project/biocomplexity/sdad/projects_data/mc/data_commons/dc_health_behavior_diet/arlington_stores.shp")

ar.bg <- get_acs(geography = "block group",
                 year = 2019,
                 variables = c(bg_pop = "B01003_001"),
                 state = "VA",
                 county = "013",
                 survey = "acs5",
                 output = "wide",
                 geometry = T)

ar.bg.centroid <- ar.bg %>% st_centroid()
d2.2 <- d2[-c(61),]
d2.2 <- d2.2 %>% st_set_crs(st_crs(ar.bg.centroid))

dist.mat <- st_distance(d2.2, ar.bg.centroid) %>%
    units::set_units(mi)

functions for gravity model

get_distance_matrix <- function(dist.mat, num_items)
{
  store_dist <- as.data.frame(as.table(dist.mat))
  colnames(store_dist) <- c("store", "bg", "crow_dist")
  store_dist$crow_dist <- as.numeric(store_dist$crow_dist)
  store_dist$store <- rep(d2.2$address, times = dim(ar.bg.centroid)[1])
  store_dist$num_items <- rep(num_items, times = dim(ar.bg.centroid)[1])
  store_dist$bg <- rep(ar.bg.centroid$GEOID, each = dim(d2.2)[1])
  return(store_dist)
}

get_stores_within_distance_of_bg <- function(store_dist, bglist, dist_mi = 10)
{
  bg_idx <- vector(mode = "numeric", length = length(bglist))
  for(i in 1:length(bglist))
  {
    bg_idx[i] <- store_dist %>%
      filter(bg == bglist[i], crow_dist <= dist_mi) %>%
      summarise(idx = sum(num_items / (crow_dist ^ 2), na.rm = T))
  }
  return(unlist(bg_idx))
}

format_idx <- function(a, geog = ar.bg.centroid)
{
  idx.dt <- data.table(idx = a, GEOID = geog$GEOID)
  idx.sf <- left_join(ar.bg, idx.dt, by = "GEOID")
  idx.sf$norm.idx <- (idx.sf$idx - min(idx.sf$idx, na.rm = T)) / (max(idx.sf$idx, na.rm = T) - min(idx.sf$idx, na.rm = T)) * 100
  idx.sf$ecdf.idx <- ecdf(idx.sf$idx)(idx.sf$idx)* 100
  return(idx.sf)
}

make_arlington_plot <- function(idx.sf.avocado, num_items, title)
{
  idx.sf.avocado$ecdf.idx[idx.sf.avocado$bg_popE <= 10] = NA
  tm_shape(idx.sf.avocado, unit = "mi") +
  tm_polygons(col = "ecdf.idx",
              style = "fixed",
              palette = "Blues",
              border.alpha = 0,
              title = "Percentile Rank",
              breaks = c(0, 20, 40, 60, 80, 100)) +
  tm_shape(ar.bg) +
  tm_polygons(alpha = 0) +
  tm_shape(d2.2[num_items > 0,]) +
  tm_dots(col = "pink", alpha = 0.75, size = 0.5) + 
  tm_scale_bar(position = c("left", "top")) +
  tm_layout(main.title = title,
            main.title.size = 0.95,
            frame = F,
            legend.outside = T,
            legend.outside.position = "right")
}

make plots and get data

### how do we visualize where these stores are located - explains why the map is different (could put points down where located)
library(data.table)
store_dist <- get_distance_matrix(dist.mat, latinx.data$num_items)
a <- get_stores_within_distance_of_bg(store_dist, ar.bg.centroid$GEOID)
all.access <- format_idx(a)

store_dist <- get_distance_matrix(dist.mat, latinx.data$item.1)
a <- get_stores_within_distance_of_bg(store_dist, ar.bg.centroid$GEOID)
avocado.access <- format_idx(a)

store_dist <- get_distance_matrix(dist.mat, latinx.data$item.18)
a <- get_stores_within_distance_of_bg(store_dist, ar.bg.centroid$GEOID)
tomatillo.access <- format_idx(a)

store_dist <- get_distance_matrix(dist.mat, latinx.data$item.12)
a <- get_stores_within_distance_of_bg(store_dist, ar.bg.centroid$GEOID)
nopalito.access <- format_idx(a)

make_arlington_plot(all.access, latinx.data$num_items, "Latinx Staple Food Item Access")
make_arlington_plot(avocado.access, latinx.data$item.1, "Avocado Access")
make_arlington_plot(tomatillo.access, latinx.data$item.18, "Tomatillo Access")
make_arlington_plot(nopalito.access, latinx.data$item.12, "Nopalito Access")

re-do at civic association level

con <- get_db_conn()
ar_civic_associations <- st_read(con, query = "SELECT * FROM dc_geographies.va_013_arl_2020_civic_associations")
dbDisconnect(con)

ar.ca.centroid <- ar_civic_associations %>% st_centroid()
d2.2 <- d2[-c(61),] %>% st_set_crs(st_crs(ar.ca.centroid))
dist.mat <- st_distance(d2.2, ar.ca.centroid) %>% units::set_units(mi)

new functions for civic association (probably could use the ones above, but ran into some issues)

get_distance_matrix2 <- function(dist.mat, num_items)
{
  store_dist <- as.data.frame(as.table(dist.mat))
  colnames(store_dist) <- c("store", "bg", "crow_dist")
  store_dist$crow_dist <- as.numeric(store_dist$crow_dist)
  store_dist$store <- rep(d2.2$address, times = dim(ar.ca.centroid)[1])
  store_dist$num_items <- rep(num_items, times = dim(ar.ca.centroid)[1])
  store_dist$bg <- rep(ar.ca.centroid$OBJECTID, each = dim(d2.2)[1])
  return(store_dist)
}

get_stores_within_distance_of_bg2 <- function(store_dist, bglist, dist_mi = 10)
{
  bg_idx <- vector(mode = "numeric", length = length(bglist))
  for(i in 1:length(bglist))
  {
    bg_idx[i] <- store_dist %>%
      filter(bg == bglist[i], crow_dist <= dist_mi) %>%
      summarise(idx = sum(num_items / (crow_dist ^ 2), na.rm = T))
  }
  return(unlist(bg_idx))
}

format_idx2 <- function(a, geog = ar.ca.centroid)
{
  idx.dt <- data.table(idx = a, OBJECTID = geog$OBJECTID)
  idx.sf <- left_join(ar_civic_associations, idx.dt, by = "OBJECTID")
  idx.sf$norm.idx <- (idx.sf$idx - min(idx.sf$idx, na.rm = T)) / (max(idx.sf$idx, na.rm = T) - min(idx.sf$idx, na.rm = T)) * 100
  idx.sf$ecdf.idx <- ecdf(idx.sf$idx)(idx.sf$idx)* 100
  return(idx.sf)
}

make_arlington_plot2 <- function(idx.sf.avocado, num_items, title)
{
  idx.sf.avocado$ecdf.idx[idx.sf.avocado$bg_popE <= 10] = NA
  tm_shape(idx.sf.avocado, unit = "mi") +
  tm_polygons(col = "ecdf.idx",
              style = "fixed",
              palette = "Blues",
              border.alpha = 0,
              title = "Percentile Rank",
              breaks = c(0, 20, 40, 60, 80, 100)) +
  tm_shape(ar_civic_associations) +
  tm_polygons(alpha = 0) +
  tm_shape(d2.2[num_items > 0,]) +
  tm_dots(col = "pink", alpha = 0.75, size = 0.5) + 
  tm_scale_bar(position = c("left", "top")) +
  tm_layout(main.title = title,
            main.title.size = 0.95,
            frame = F,
            legend.outside = T,
            legend.outside.position = "right")
}

make_arlington_plot2_wo_points <- function(idx.sf.avocado, num_items, title)
{
  idx.sf.avocado$ecdf.idx[idx.sf.avocado$bg_popE <= 10] = NA
  tm_shape(idx.sf.avocado, unit = "mi") +
  tm_polygons(col = "ecdf.idx",
              style = "fixed",
              palette = "Blues",
              border.alpha = 0,
              title = "Percentile Rank",
              breaks = c(0, 20, 40, 60, 80, 100)) +
  tm_shape(ar_civic_associations) +
  tm_polygons(alpha = 0) +
  tm_scale_bar(position = c("left", "top")) +
  tm_layout(main.title = title,
            main.title.size = 0.95,
            frame = F,
            legend.outside = T,
            legend.outside.position = "right")
}

store_dist2 <- get_distance_matrix2(dist.mat, latinx.data$num_items)
a <- get_stores_within_distance_of_bg2(store_dist2, ar.ca.centroid$OBJECTID)
all.access <- format_idx2(a)

store_dist2 <- get_distance_matrix2(dist.mat, latinx.data$item.1)
a <- get_stores_within_distance_of_bg2(store_dist2, ar.ca.centroid$OBJECTID)
avocado.access <- format_idx2(a)

store_dist2 <- get_distance_matrix2(dist.mat, latinx.data$item.18)
a <- get_stores_within_distance_of_bg2(store_dist2, ar.ca.centroid$OBJECTID)
tomatillo.access <- format_idx2(a)

store_dist2 <- get_distance_matrix2(dist.mat, latinx.data$item.12)
a <- get_stores_within_distance_of_bg2(store_dist2, ar.ca.centroid$OBJECTID)
nopalito.access <- format_idx2(a)

make_arlington_plot2(all.access, latinx.data$num_items, "Latinx Staple Food Item Access")
make_arlington_plot2(avocado.access, latinx.data$item.1, "Avocado Access")
make_arlington_plot2(tomatillo.access, latinx.data$item.18, "Tomatillo Access")
make_arlington_plot2(nopalito.access, latinx.data$item.12, "Nopalito Access")

make_arlington_plot2_wo_points(all.access, latinx.data$num_items, "Latinx Staple Food Item Access")
make_arlington_plot2_wo_points(avocado.access, latinx.data$item.1, "Avocado Access")
make_arlington_plot2_wo_points(tomatillo.access, latinx.data$item.18, "Tomatillo Access")
make_arlington_plot2_wo_points(nopalito.access, latinx.data$item.12, "Nopalito Access")

GETTING ACTUAL DRIVE TIMES

library(osrm)
get_times <- function(demand, new_supply, transportation)
{
  new_supply <- new_supply %>% mutate(lon = st_coordinates(new_supply)[,1], lat = st_coordinates(new_supply)[,2])
  demand <- demand %>% mutate(lon = st_coordinates(demand)[,1], lat = st_coordinates(demand)[,2])

  # options for OSRM 
  options(osrm.server = Sys.getenv("OSRM_SERVER"), osrm.profile = transportation)

  # where do we get actual longitude-latitude data for the stores
  start.time <- Sys.time()
  all_data <- matrix(, nrow = 0, ncol = nrow(new_supply))

  # maximum number of requests that OSRM can handle at a time
  max.size <- 1000000
  n <- floor(max.size / nrow(new_supply))
  chunks <- ceiling((nrow(demand)) / n)
  for (i in 1:chunks)
  {
    if (i != chunks)
    {
      matrix <- osrmTable(src = demand[(1 + n * (i - 1)):(n * i), c("GEOID", "lon", "lat")],
                        dst = new_supply[, c("address", "lon", "lat")])$durations
    }
    else
    {
      matrix <- osrmTable(src = demand[(1 + n * (i - 1)):nrow(demand), c("GEOID", "lon", "lat")],
                        dst = new_supply[, c("address", "lon", "lat")])$durations
    }
    # 
    if (i == ceiling(chunks / 4)) {print("25%")}
    if (i == ceiling(chunks / 2)) {print("50%")}
    if (i == ceiling(3 * chunks / 4)) {print("75%")}
    all_data <- rbind(all_data, matrix)
  }

  #
  end.time <- Sys.time()
  print(end.time - start.time)
  # colnames(all_data) <- new_supply$address
  # times <- as.data.frame(as.table(all_data))
  # colnames(times) <- c("origin", "dest", "cost")
  # times$origin <- rep(demand$GEOID, times = dim(d2.2)[1])
  # return(times)
  return(t(all_data))
}

get_times_2 <- function(demand, new_supply, transportation)
{
  new_supply <- new_supply %>% mutate(lon = st_coordinates(new_supply)[,1], lat = st_coordinates(new_supply)[,2])
  demand <- demand %>% mutate(lon = st_coordinates(demand)[,1], lat = st_coordinates(demand)[,2])

  # options for OSRM 
  options(osrm.server = Sys.getenv("OSRM_SERVER"), osrm.profile = transportation)

  # where do we get actual longitude-latitude data for the stores
  start.time <- Sys.time()
  all_data <- matrix(, nrow = 0, ncol = nrow(new_supply))

  # maximum number of requests that OSRM can handle at a time
  max.size <- 1000000
  n <- floor(max.size / nrow(new_supply))
  chunks <- ceiling((nrow(demand)) / n)
  for (i in 1:chunks)
  {
    if (i != chunks)
    {
      matrix <- osrmTable(src = demand[(1 + n * (i - 1)):(n * i), c("GIS_ID", "lon", "lat")],
                        dst = new_supply[, c("address", "lon", "lat")])$durations
    }
    else
    {
      matrix <- osrmTable(src = demand[(1 + n * (i - 1)):nrow(demand), c("GIS_ID", "lon", "lat")],
                        dst = new_supply[, c("address", "lon", "lat")])$durations
    }
    # 
    if (i == ceiling(chunks / 4)) {print("25%")}
    if (i == ceiling(chunks / 2)) {print("50%")}
    if (i == ceiling(3 * chunks / 4)) {print("75%")}
    all_data <- rbind(all_data, matrix)
  }

  #
  end.time <- Sys.time()
  print(end.time - start.time)
  # colnames(all_data) <- new_supply$address
  # times <- as.data.frame(as.table(all_data))
  # colnames(times) <- c("origin", "dest", "cost")
  # times$origin <- rep(demand$GEOID, times = dim(d2.2)[1])
  # return(times)
  return(t(all_data))
}

dist.mat2 <- get_times(ar.bg.centroid, d2.2[-c(61),], "car")

dist.mat2 <- get_times_2(ar.ca.centroid, d2.2, "car")
store_dist2.1 <- get_distance_matrix2(dist.mat2, latinx.data$item.1)
store_dist2.2 <- get_distance_matrix2(dist.mat2, latinx.data$item.2)
store_dist2.3 <- get_distance_matrix2(dist.mat2, latinx.data$item.3)
store_dist2.4 <- get_distance_matrix2(dist.mat2, latinx.data$item.4)
store_dist2.5 <- get_distance_matrix2(dist.mat2, latinx.data$item.5)
store_dist2.6 <- get_distance_matrix2(dist.mat2, latinx.data$item.6)
store_dist2.7 <- get_distance_matrix2(dist.mat2, latinx.data$item.7)
store_dist2.8 <- get_distance_matrix2(dist.mat2, latinx.data$item.8)
store_dist2.9 <- get_distance_matrix2(dist.mat2, latinx.data$item.9)
store_dist2.10 <- get_distance_matrix2(dist.mat2, latinx.data$item.10)
store_dist2.11 <- get_distance_matrix2(dist.mat2, latinx.data$item.11)
store_dist2.12 <- get_distance_matrix2(dist.mat2, latinx.data$item.12)
store_dist2.13 <- get_distance_matrix2(dist.mat2, latinx.data$item.13)
store_dist2.14 <- get_distance_matrix2(dist.mat2, latinx.data$item.14)
store_dist2.15 <- get_distance_matrix2(dist.mat2, latinx.data$item.15)
store_dist2.16 <- get_distance_matrix2(dist.mat2, latinx.data$item.16)
store_dist2.17 <- get_distance_matrix2(dist.mat2, latinx.data$item.17)
store_dist2.18 <- get_distance_matrix2(dist.mat2, latinx.data$item.18)
store_dist2.19 <- get_distance_matrix2(dist.mat2, latinx.data$item.19)
store_dist2.all <- get_distance_matrix2(dist.mat2, latinx.data$num_items)

a.1 <- get_stores_within_distance_of_bg2(store_dist2.1, ar.ca.centroid$OBJECTID)
a.2 <- get_stores_within_distance_of_bg2(store_dist2.2, ar.ca.centroid$OBJECTID)
a.3 <- get_stores_within_distance_of_bg2(store_dist2.3, ar.ca.centroid$OBJECTID)
a.4 <- get_stores_within_distance_of_bg2(store_dist2.4, ar.ca.centroid$OBJECTID)
a.5 <- get_stores_within_distance_of_bg2(store_dist2.5, ar.ca.centroid$OBJECTID)
a.6 <- get_stores_within_distance_of_bg2(store_dist2.6, ar.ca.centroid$OBJECTID)
a.7 <- get_stores_within_distance_of_bg2(store_dist2.7, ar.ca.centroid$OBJECTID)
a.8 <- get_stores_within_distance_of_bg2(store_dist2.8, ar.ca.centroid$OBJECTID)
a.9 <- get_stores_within_distance_of_bg2(store_dist2.9, ar.ca.centroid$OBJECTID)
a.10 <- get_stores_within_distance_of_bg2(store_dist2.10, ar.ca.centroid$OBJECTID)
a.11 <- get_stores_within_distance_of_bg2(store_dist2.11, ar.ca.centroid$OBJECTID)
a.12 <- get_stores_within_distance_of_bg2(store_dist2.12, ar.ca.centroid$OBJECTID)
a.13 <- get_stores_within_distance_of_bg2(store_dist2.13, ar.ca.centroid$OBJECTID)
a.14 <- get_stores_within_distance_of_bg2(store_dist2.14, ar.ca.centroid$OBJECTID)
a.15 <- get_stores_within_distance_of_bg2(store_dist2.15, ar.ca.centroid$OBJECTID)
a.16 <- get_stores_within_distance_of_bg2(store_dist2.16, ar.ca.centroid$OBJECTID)
a.17 <- get_stores_within_distance_of_bg2(store_dist2.17, ar.ca.centroid$OBJECTID)
a.18 <- get_stores_within_distance_of_bg2(store_dist2.18, ar.ca.centroid$OBJECTID)
a.19 <- get_stores_within_distance_of_bg2(store_dist2.19, ar.ca.centroid$OBJECTID)
a.all <- get_stores_within_distance_of_bg2(store_dist2.all, ar.ca.centroid$OBJECTID)

# Latino - Avocado, Beets, Black Beans, Cheese, Corn Tortillas, Crackers, Green Chiles, Jalapenos, Legumes, Masa
# Mixed Fruit, Nopalitos, Peppers, Pinto Beans, Rice, Salsa, Squash, Tomatillos, Tomatoes

all.access.1 <- st_drop_geometry(format_idx2(a.1)) %>% select(LABEL, GIS_ID, idx, norm.idx, ecdf.idx) %>%
  rename(avocado_idx = idx, avocado_norm_idx = norm.idx, avocado_ecdf_idx = ecdf.idx)
all.access.2 <- cbind(all.access.1, st_drop_geometry(format_idx2(a.2)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(beets_idx = idx, beets_norm_idx = norm.idx, beets_ecdf_idx = ecdf.idx))
all.access.3 <- cbind(all.access.2, st_drop_geometry(format_idx2(a.3)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(blackbeans_idx = idx, blackbeans_norm_idx = norm.idx, blackbeans_ecdf_idx = ecdf.idx))
all.access.4 <- cbind(all.access.3, st_drop_geometry(format_idx2(a.4)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(cheese_idx = idx, cheese_norm_idx = norm.idx, cheese_ecdf_idx = ecdf.idx))
all.access.5 <- cbind(all.access.4, st_drop_geometry(format_idx2(a.5)) %>%  select(idx, norm.idx, ecdf.idx) %>%
                        rename(corntortillas_idx = idx, corntortillas_norm_idx = norm.idx, corntortillas_ecdf_idx = ecdf.idx))
all.access.6 <- cbind(all.access.5, st_drop_geometry(format_idx2(a.6)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(crackers_idx = idx, crackers_norm_idx = norm.idx, crackers_ecdf_idx = ecdf.idx))
all.access.7 <- cbind(all.access.6, st_drop_geometry(format_idx2(a.7)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(greenchiles_idx = idx, greenchiles_norm_idx = norm.idx, greenchiles_ecdf_idx = ecdf.idx))
all.access.8 <- cbind(all.access.7, st_drop_geometry(format_idx2(a.8)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(jalapenos_idx = idx, jalapenos_norm_idx = norm.idx, jalapenos_ecdf_idx = ecdf.idx))
all.access.9 <- cbind(all.access.8, st_drop_geometry(format_idx2(a.9)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(legumes_idx = idx, legumes_norm_idx = norm.idx, legumes_ecdf_idx = ecdf.idx))
all.access.10 <- cbind(all.access.9, st_drop_geometry(format_idx2(a.10)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(masa_idx = idx, masa_norm_idx = norm.idx, masa_ecdf_idx = ecdf.idx))
all.access.11 <- cbind(all.access.10, st_drop_geometry(format_idx2(a.11)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(mixedfruit_idx = idx, mixedfruit_norm_idx = norm.idx, mixedfruit_ecdf_idx = ecdf.idx))
all.access.12 <- cbind(all.access.11, st_drop_geometry(format_idx2(a.12)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(nopalitos_idx = idx, nopalitos_norm_idx = norm.idx, nopalitos_ecdf_idx = ecdf.idx))
all.access.13 <- cbind(all.access.12, st_drop_geometry(format_idx2(a.13)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(peppers_idx = idx, peppers_norm_idx = norm.idx, peppers_ecdf_idx = ecdf.idx))
all.access.14 <- cbind(all.access.13, st_drop_geometry(format_idx2(a.14)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(pintobeans_idx = idx, pintobeans_norm_idx = norm.idx, pintobeans_ecdf_idx = ecdf.idx))
all.access.15 <- cbind(all.access.14, st_drop_geometry(format_idx2(a.15)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(rice_idx = idx, rice_norm_idx = norm.idx, rice_ecdf_idx = ecdf.idx))
all.access.16 <- cbind(all.access.15, st_drop_geometry(format_idx2(a.16)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(salsa_idx = idx, salsa_norm_idx = norm.idx, salsa_ecdf_idx = ecdf.idx))
all.access.17 <- cbind(all.access.16, st_drop_geometry(format_idx2(a.17)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(squash_idx = idx, squash_norm_idx = norm.idx, squash_ecdf_idx = ecdf.idx))
all.access.18 <- cbind(all.access.17, st_drop_geometry(format_idx2(a.18)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(tomatillos_idx = idx, tomatillos_norm_idx = norm.idx, tomatillos_ecdf_idx = ecdf.idx))
all.access.19 <- cbind(all.access.18, st_drop_geometry(format_idx2(a.19)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(tomatoes_idx = idx, tomatoes_norm_idx = norm.idx, tomatoes_ecdf_idx = ecdf.idx))
all.access.all <- cbind(all.access.19, st_drop_geometry(format_idx2(a.all)) %>% select(idx, norm.idx, ecdf.idx) %>%
  rename(all_idx = idx, all_norm_idx = norm.idx, all_ecdf_idx = ecdf.idx))

all.access.final.ca <- all.access.all %>%
  gather(measure, value, -c(LABEL, GIS_ID)) %>%
  mutate(year = "2021", measure_type = "index", measure_units = as.character(NA), region_type = "neighborhood") %>%
  rename(region_name = LABEL, geoid = GIS_ID) %>% 
  relocate("geoid", "region_type", "region_name", "year", "measure", "value", "measure_type", "measure_units")

# con <- get_db_conn()
# dc_dbWriteTable(con, "dc_working", "va_013_civic_sdad_2021_latino_basket_gravity_model", all.access.final.ca)
# dbDisconnect(con)

dist.mat <- get_times(ar.bg.centroid, d2.2, "car") # works for block groups
store_dist1.1 <- get_distance_matrix(dist.mat, latinx.data$item.1)
store_dist1.2 <- get_distance_matrix(dist.mat, latinx.data$item.2)
store_dist1.3 <- get_distance_matrix(dist.mat, latinx.data$item.3)
store_dist1.4 <- get_distance_matrix(dist.mat, latinx.data$item.4)
store_dist1.5 <- get_distance_matrix(dist.mat, latinx.data$item.5)
store_dist1.6 <- get_distance_matrix(dist.mat, latinx.data$item.6)
store_dist1.7 <- get_distance_matrix(dist.mat, latinx.data$item.7)
store_dist1.8 <- get_distance_matrix(dist.mat, latinx.data$item.8)
store_dist1.9 <- get_distance_matrix(dist.mat, latinx.data$item.9)
store_dist1.10 <- get_distance_matrix(dist.mat, latinx.data$item.10)
store_dist1.11 <- get_distance_matrix(dist.mat, latinx.data$item.11)
store_dist1.12 <- get_distance_matrix(dist.mat, latinx.data$item.12)
store_dist1.13 <- get_distance_matrix(dist.mat, latinx.data$item.13)
store_dist1.14 <- get_distance_matrix(dist.mat, latinx.data$item.14)
store_dist1.15 <- get_distance_matrix(dist.mat, latinx.data$item.15)
store_dist1.16 <- get_distance_matrix(dist.mat, latinx.data$item.16)
store_dist1.17 <- get_distance_matrix(dist.mat, latinx.data$item.17)
store_dist1.18 <- get_distance_matrix(dist.mat, latinx.data$item.18)
store_dist1.19 <- get_distance_matrix(dist.mat, latinx.data$item.19)
store_dist1.all <- get_distance_matrix(dist.mat, latinx.data$num_items)

b.1 <- get_stores_within_distance_of_bg(store_dist1.1, ar.bg.centroid$GEOID)
b.2 <- get_stores_within_distance_of_bg(store_dist1.2, ar.bg.centroid$GEOID)
b.3 <- get_stores_within_distance_of_bg(store_dist1.3, ar.bg.centroid$GEOID)
b.4 <- get_stores_within_distance_of_bg(store_dist1.4, ar.bg.centroid$GEOID)
b.5 <- get_stores_within_distance_of_bg(store_dist1.5, ar.bg.centroid$GEOID)
b.6 <- get_stores_within_distance_of_bg(store_dist1.6, ar.bg.centroid$GEOID)
b.7 <- get_stores_within_distance_of_bg(store_dist1.7, ar.bg.centroid$GEOID)
b.8 <- get_stores_within_distance_of_bg(store_dist1.8, ar.bg.centroid$GEOID)
b.9 <- get_stores_within_distance_of_bg(store_dist1.9, ar.bg.centroid$GEOID)
b.10 <- get_stores_within_distance_of_bg(store_dist1.10, ar.bg.centroid$GEOID)
b.11 <- get_stores_within_distance_of_bg(store_dist1.11, ar.bg.centroid$GEOID)
b.12 <- get_stores_within_distance_of_bg(store_dist1.12, ar.bg.centroid$GEOID)
b.13 <- get_stores_within_distance_of_bg(store_dist1.13, ar.bg.centroid$GEOID)
b.14 <- get_stores_within_distance_of_bg(store_dist1.14, ar.bg.centroid$GEOID)
b.15 <- get_stores_within_distance_of_bg(store_dist1.15, ar.bg.centroid$GEOID)
b.16 <- get_stores_within_distance_of_bg(store_dist1.16, ar.bg.centroid$GEOID)
b.17 <- get_stores_within_distance_of_bg(store_dist1.17, ar.bg.centroid$GEOID)
b.18 <- get_stores_within_distance_of_bg(store_dist1.18, ar.bg.centroid$GEOID)
b.19 <- get_stores_within_distance_of_bg(store_dist1.19, ar.bg.centroid$GEOID)
b.all <- get_stores_within_distance_of_bg(store_dist1.all, ar.bg.centroid$GEOID)

# Latino - Avocado, Beets, Black Beans, Cheese, Corn Tortillas, Crackers, Green Chiles, Jalapenos, Legumes, Masa
# Mixed Fruit, Nopalitos, Peppers, Pinto Beans, Rice, Salsa, Squash, Tomatillos, Tomatoes

b.all.access.1 <- st_drop_geometry(format_idx(b.1)) %>% select(NAME, GEOID, idx, norm.idx, ecdf.idx) %>%
  rename(avocado_idx = idx, avocado_norm_idx = norm.idx, avocado_ecdf_idx = ecdf.idx)
b.all.access.2 <- cbind(b.all.access.1, st_drop_geometry(format_idx(b.2)) %>% select(idx, norm.idx, ecdf.idx) %>%
                          rename(beets_idx = idx, beets_norm_idx = norm.idx, beets_ecdf_idx = ecdf.idx))
b.all.access.3 <- cbind(b.all.access.2, st_drop_geometry(format_idx(b.3)) %>% select(idx, norm.idx, ecdf.idx) %>%
                          rename(blackbeans_idx = idx, blackbeans_norm_idx = norm.idx, blackbeans_ecdf_idx = ecdf.idx))
b.all.access.4 <- cbind(b.all.access.3, st_drop_geometry(format_idx(b.4)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(cheese_idx = idx, cheese_norm_idx = norm.idx, cheese_ecdf_idx = ecdf.idx))
b.all.access.5 <- cbind(b.all.access.4, st_drop_geometry(format_idx(b.5)) %>%  select(idx, norm.idx, ecdf.idx) %>%
                        rename(corntortillas_idx = idx, corntortillas_norm_idx = norm.idx, corntortillas_ecdf_idx = ecdf.idx))
b.all.access.6 <- cbind(b.all.access.5, st_drop_geometry(format_idx(b.6)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(crackers_idx = idx, crackers_norm_idx = norm.idx, crackers_ecdf_idx = ecdf.idx))
b.all.access.7 <- cbind(b.all.access.6, st_drop_geometry(format_idx(b.7)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(greenchiles_idx = idx, greenchiles_norm_idx = norm.idx, greenchiles_ecdf_idx = ecdf.idx))
b.all.access.8 <- cbind(b.all.access.7, st_drop_geometry(format_idx(b.8)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(jalapenos_idx = idx, jalapenos_norm_idx = norm.idx, jalapenos_ecdf_idx = ecdf.idx))
b.all.access.9 <- cbind(b.all.access.8, st_drop_geometry(format_idx(b.9)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(legumes_idx = idx, legumes_norm_idx = norm.idx, legumes_ecdf_idx = ecdf.idx))
b.all.access.10 <- cbind(b.all.access.9, st_drop_geometry(format_idx(b.10)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(masa_idx = idx, masa_norm_idx = norm.idx, masa_ecdf_idx = ecdf.idx))
b.all.access.11 <- cbind(b.all.access.10, st_drop_geometry(format_idx(b.11)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(mixedfruit_idx = idx, mixedfruit_norm_idx = norm.idx, mixedfruit_ecdf_idx = ecdf.idx))
b.all.access.12 <- cbind(b.all.access.11, st_drop_geometry(format_idx(b.12)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(nopalitos_idx = idx, nopalitos_norm_idx = norm.idx, nopalitos_ecdf_idx = ecdf.idx))
b.all.access.13 <- cbind(b.all.access.12, st_drop_geometry(format_idx(b.13)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(peppers_idx = idx, peppers_norm_idx = norm.idx, peppers_ecdf_idx = ecdf.idx))
b.all.access.14 <- cbind(b.all.access.13, st_drop_geometry(format_idx(b.14)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(pintobeans_idx = idx, pintobeans_norm_idx = norm.idx, pintobeans_ecdf_idx = ecdf.idx))
b.all.access.15 <- cbind(b.all.access.14, st_drop_geometry(format_idx(b.15)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(rice_idx = idx, rice_norm_idx = norm.idx, rice_ecdf_idx = ecdf.idx))
b.all.access.16 <- cbind(b.all.access.15, st_drop_geometry(format_idx(b.16)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(salsa_idx = idx, salsa_norm_idx = norm.idx, salsa_ecdf_idx = ecdf.idx))
b.all.access.17 <- cbind(b.all.access.16, st_drop_geometry(format_idx(b.17)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(squash_idx = idx, squash_norm_idx = norm.idx, squash_ecdf_idx = ecdf.idx))
b.all.access.18 <- cbind(b.all.access.17, st_drop_geometry(format_idx(b.18)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(tomatillos_idx = idx, tomatillos_norm_idx = norm.idx, tomatillos_ecdf_idx = ecdf.idx))
b.all.access.19 <- cbind(b.all.access.18, st_drop_geometry(format_idx(b.19)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(tomatoes_idx = idx, tomatoes_norm_idx = norm.idx, tomatoes_ecdf_idx = ecdf.idx))
b.all.access.all <- cbind(b.all.access.19, st_drop_geometry(format_idx(b.all)) %>% select(idx, norm.idx, ecdf.idx) %>%
  rename(all_idx = idx, all_norm_idx = norm.idx, all_ecdf_idx = ecdf.idx))

all.access.final.bg <- b.all.access.all %>%
  gather(measure, value, -c(NAME, GEOID)) %>%
  mutate(year = "2021", measure_type = "index", measure_units = as.character(NA), region_type = "block group") %>%
  rename(region_name = NAME, geoid = GEOID) %>% 
  relocate("geoid", "region_type", "region_name", "year", "measure", "value", "measure_type", "measure_units")

## send to db
# con <- get_db_conn()
# dc_dbWriteTable(con, "dc_working", "va_013_bg_sdad_2021_latino_basket_gravity_model", all.access.final.bg)
# dbDisconnect(con)

LOOKING NOW AT EAST AFRICAN BASKET

giant

# Giant
giant_addys <- c("3115 Lee Hwy., Arlington, VA 22201", # 1
                 "3450 Washington Blvd., Arlington, VA 22201", # 2
                 "3336 Wisconsin Ave NW, Washington, DC 20016", # 3
                 "6360 Seven Corners Ctr, Falls Church, VA 22044", # 4
                 "1454 Chain Bridge Rd., McLean, VA 22101", # 5
                 "2501 9th Road South, Arlington, VA 22204", # 6
                 "5400 Westbard Ave., Bethesda, MD 20816", # 7
                 "4303 Connecticut Ave NW, Washington DC 20008", # 8
                 "1230 W. Broad St., Falls Church, VA 22046", # 9
                 "3480 S. Jefferson St., Falls Chuch, VA 22041", # 10
                 "1345 Park Road NW, Washington DC 20010", # 11
                 "7142 Arlington Road, Bethesda, MD 20814", # 12
                 "2901-11 S. Glebe Rd., Arlington, VA 22206", # 13
                 "3131 Duke Streeet, Alexandria, VA 22314", # 14
                 "621 East Glebe Road, Alexandria, VA 22305", # 15
                 "6200 Little River Turnpike, Alexandria VA 22312", # 16
                 "6800 Richmond Hwy., Alexandria, VA 22306") # 17
# "5463 Wisconsin Avenue, Chevy Chase, MD 20815" was dropped

# East African - Barley, Curry Powder, Black Eye Peas, Dried Beans, Dried Dates, Fava Beans, Garbanzo Beans, Goat, Lentils,
# Red Chori Beans, Basmati Rice, Spaghetti Pasta, Teff, Tomato Sauce, Tuna

ea_giant_present <- list(
     c(T, T, T, T, T, F, T, F, T, F, T, T, T, T, T), # 1 - what type ot dried beans?? - the cheapest beans?
     c(T, T, T, T, T, F, T, F, T, F, T, T, T, T, T), # 2
     c(T, T, T, T, T, F, T, F, T, F, T, T, T, T, T), # 3
     c(T, T, T, T, T, F, T, F, T, F, T, T, T, T, T), # 4
     c(T, T, T, T, T, F, T, F, T, F, T, T, T, T, T), # 5
     c(T, T, T, T, T, F, T, F, T, F, T, T, T, T, T), # 6
     c(T, T, T, T, T, F, T, F, T, F, T, T, T, T, T), # 7
     c(T, T, T, T, T, F, T, F, T, F, T, T, T, T, T), # 8
     c(T, T, T, T, T, F, T, F, T, F, T, T, T, T, T), # 9
     c(T, T, T, T, T, T, T, F, T, F, T, T, T, T, T), # 10
     c(T, T, T, T, T, F, T, F, T, F, T, T, T, T, T), # 11
     c(T, T, T, T, T, F, T, F, T, F, T, T, T, T, T), # 12
     c(T, T, T, T, T, F, T, F, T, F, T, T, T, T, T), # 13
     c(T, T, T, T, T, F, T, F, T, F, T, T, T, T, T), # 14
     c(T, T, T, T, T, F, T, F, T, F, T, T, T, T, T), # 15
     c(T, T, T, T, T, F, T, F, T, F, T, T, T, T, T), # 16
     c(T, T, T, T, T, T, T, F, T, F, T, T, F, T, T)) # 17

ea_giant_prices <- list(
     c(1.50, 0.61 * 16, 0.03 * 16, 0.06 * 16, 0.30 * 16, NA, 0.06 * 16, NA, 2.09, NA, 0.14 * 16, 0.06 * 16, 0.32 * 16, 0.04 * 16, 0.21 * 16), #1
     c(2.19, 0.61 * 16, 0.03 * 16, 0.09 * 16, 0.30 * 16, NA, 0.06 * 16, NA, 2.09, NA, 0.14 * 16, 0.06 * 16, 1.77 * 16 / 4, 0.04 * 16, 0.21 * 16), #2
     c(1.50, 0.61 * 16, 0.03 * 16, 0.09 * 16, 0.30 * 16, NA, 0.06 * 16, NA, 2.09, NA, 0.14 * 16, 0.06 * 16, 1.77 * 16 / 4, 0.04 * 16, 0.21 * 16), #3
     c(1.50, 0.61 * 16, 0.03 * 16, 0.09 * 16, 0.30 * 16, NA, 0.06 * 16, NA, 2.09, NA, 0.14 * 16, 0.06 * 16, 1.77 * 16 / 4, 0.04 * 16, 0.21 * 16), #4
     c(1.50, 0.61 * 16, 0.03 * 16, 0.09 * 16, 0.30 * 16, NA, 0.06 * 16, NA, 2.09, NA, 0.14 * 16, 0.06 * 16, 1.77 * 16 / 4, 0.04 * 16, 0.21 * 16), #5
     c(1.50, 0.61 * 16, 0.03 * 16, 0.09 * 16, 0.30 * 16, NA, 0.06 * 16, NA, 2.09, NA, 0.14 * 16, 0.06 * 16, 1.77 * 16 / 4, 0.04 * 16, 0.21 * 16), #6
     c(1.50, 0.61 * 16, 0.03 * 16, 0.09 * 16, 0.30 * 16, NA, 0.06 * 16, NA, 2.09, NA, 0.14 * 16, 0.06 * 16, 1.77 * 16 / 4, 0.04 * 16, 0.21 * 16), #7
     c(1.50, 0.61 * 16, 0.03 * 16, 0.09 * 16, 0.30 * 16, NA, 0.06 * 16, NA, 2.09, NA, 0.14 * 16, 0.06 * 16, 0.32 * 16, 0.04 * 16, 0.21 * 16), #8
     c(1.50, 0.61 * 16, 0.03 * 16, 0.09 * 16, 0.30 * 16, NA, 0.06 * 16, NA, 2.09, NA, 0.14 * 16, 0.06 * 16, 0.32 * 16, 0.04 * 16, 0.21 * 16), #9
     c(1.50, 0.61 * 16, 0.03 * 16, 0.09 * 16, 0.30 * 16, 0.36 * 16, 0.06 * 16, NA, 2.09, NA, 0.14 * 16, 0.06 * 16, 1.77 * 16 / 4, 0.04 * 16, 0.21 * 16), #10
     c(1.50, 0.61 * 16, 0.03 * 16, 0.09 * 16, 0.30 * 16, NA, 0.06 * 16, NA, 2.09, NA, 0.14 * 16, 0.06 * 16, 0.32 * 16, 0.04 * 16, 0.21 * 16), #11
     c(1.50, 0.61 * 16, 0.08 * 16, 0.09 * 16, 0.30 * 16, NA, 0.06 * 16, NA, 2.09, NA, 0.14 * 16, 0.06 * 16, 0.32 * 16, 0.04 * 16, 0.21 * 16), #12
     c(2.19, 0.61 * 16, 0.03 * 16, 0.09 * 16, 0.30 * 16, NA, 0.06 * 16, NA, 2.09, NA, 0.14 * 16, 0.06 * 16, 0.32 * 16, 0.04 * 16, 0.21 * 16), #13
     c(1.50, 0.61 * 16, 0.03 * 16, 0.09 * 16, 0.30 * 16, NA, 0.06 * 16, NA, 2.09, NA, 0.14 * 16, 0.06 * 16, 0.32 * 16, 0.04 * 16, 0.21 * 16), #14
     c(1.50, 0.61 * 16, 0.03 * 16, 0.09 * 16, 0.30 * 16, NA, 0.06 * 16, NA, 2.09, NA, 0.14 * 16, 0.06 * 16, 0.32 * 16, 0.04 * 16, 0.21 * 16), #15
     c(1.50, 0.61 * 16, 0.03 * 16, 0.09 * 16, 0.30 * 16, NA, 0.06 * 16, NA, 2.09, NA, 0.14 * 16, 0.06 * 16, 0.32 * 16, 0.04 * 16, 0.21 * 16), #16
     c(1.50, 0.61 * 16, 0.03 * 16, 0.09 * 16, 0.30 * 16, 0.16 * 16, 0.06 * 16, NA, 2.09, NA, 0.14 * 16, 0.06 * 16, NA, 0.04 * 16, 0.21 * 16)) #17

# per lb avocado (best deal)
#assume bunch of beets is ~2lb: https://www.foodbankcny.org/assets/Documents/Vegetable-conversion-chart.pdf
# cheapest lb of black beans
# cheese - per lb of shredded cheese?
# corn tortillas per lb
# crackers - WHAT TYPE OF CRACKERS... - store brand cheapest generic grackers...
# canned green chiles??
# assume jalapeno weighs 0.75 oz
# lentils --> chickpeas
# mixed fruit (del monte ???)
# nopalitos
# poblanos?
# White rice, salsa,
# squash -- zucchini or yellow squash?? - assume weight is .4 lb ??

ea_giant_present_matrix <- matrix(unlist(ea_giant_present), ncol = 15, byrow = T)
ea_giant_prices_matrix <- matrix(unlist(ea_giant_prices), ncol = 15, byrow = T)

harris teeter

# HT
ht_addys <- c("2425 N. Harrison St, Arlington, VA 22207", # 1
              "301 West Broad, Falls Church, VA 22046", # 2
              "954 South George Mason, Arlington, VA 22204", # 3
              "4550 King Street, Alexandria, VA 22302", # 4
              "1631 Kalorama Rd NW Suite 100, Washington DC 20009", # 5
              "4250 Campbell Ave, Arlington, VA 22206", # 6
              "3600 S Glebe Rd, Arlington, VA 22202", # 7
              "4641 Duke St, Alexandria, VA 22304", # 8
              "735 N. Saint Asaph St., Alexandria, VA 22314", # 9
              "6351 Columbia Pike, Falls Church, VA 22041") # 10

# East African - Barley, Curry Powder, Black Eye Peas, Dried Beans, Dried Dates, Fava Beans, Garbanzo Beans, Goat, Lentils,
# Red Chori Beans, Basmati Rice, Spaghetti Pasta, Teff, Tomato Sauce, Tuna

ea_ht_present <- list(
     c(T, T, T, T, T, F, T, F, T, F, T, T, F, T, T), # 1
     c(T, T, T, T, T, F, T, F, T, F, T, T, F, T, T), # 2
     c(T, T, T, T, T, F, T, F, T, F, T, T, F, T, T), # 3
     c(T, T, T, T, T, F, T, F, T, F, T, T, F, T, T), # 4
     c(T, T, T, T, T, F, T, F, T, F, T, T, F, T, T), # 5
     c(T, T, T, T, T, F, T, F, T, F, T, T, F, T, T), # 6
     c(T, T, T, T, T, F, T, F, T, F, T, T, F, T, T), # 7
     c(T, T, T, T, T, F, T, F, T, F, T, T, F, T, T), # 8
     c(T, T, T, T, T, F, T, F, T, F, T, T, F, T, T), # 9
     c(T, T, T, T, T, F, T, F, T, F, T, T, F, T, T)) # 10

ea_ht_prices <- list(
     c(0.23 * 16, 0.75 * 16, 0.05 * 16, 0.09 * 16, 0.30 * 16, NA, 0.05 * 16, NA, 1.59, NA, 1.80, 0.99, NA, 0.04 * 16, 0.20 * 16), #1
     c(0.23 * 16, 2.55 * 16, 0.06 * 16, 0.09 * 16, 0.30 * 16, NA, 0.05 * 16, NA, 2.49, NA, 1.30, 0.99, NA, 0.04 * 16, 0.20 * 16), #2
     c(0.23 * 16, 0.50 * 16, 0.05 * 16, 0.09 * 16, 0.72 * 16, NA, 0.05 * 16, NA, 1.59, NA, 1.80, 0.99, NA, 0.04 * 16, 0.20 * 16), #3
     c(0.23 * 16, 0.50 * 16, 0.05 * 16, 0.09 * 16, 0.72 * 16, NA, 0.05 * 16, NA, 2.49, NA, 1.30, 0.99, NA, 0.04 * 16, 0.20 * 16), #4
     c(0.23 * 16, 3.01 * 16, 0.05 * 16, 0.09 * 16, 0.30 * 16, NA, 0.05 * 16, NA, 2.49, NA, 1.80, 0.99, NA, 0.04 * 16, 0.20 * 16), #5
     c(0.23 * 16, 3.01 * 16, 0.09 * 16, 0.09 * 16, 0.58 * 16, NA, 0.05 * 16, NA, 1.59, NA, 1.30, 0.99, NA, 0.04 * 16, 0.20 * 16), #6
     c(0.23 * 16, 0.75 * 16, 0.05 * 16, 0.09 * 16, 0.30 * 16, NA, 0.05 * 16, NA, 1.59, NA, 1.30, 0.99, NA, 0.04 * 16, 0.20 * 16), #7
     c(0.23 * 16, 2.55 * 16, 0.05 * 16, 0.09 * 16, 0.30 * 16, NA, 0.05 * 16, NA, 1.59, NA, 1.30, 0.99, NA, 0.04 * 16, 0.20 * 16), #8
     c(0.23 * 16, 2.55 * 16, 0.05 * 16, 0.09 * 16, 0.58 * 16, NA, 0.05 * 16, NA, 2.49, NA, 1.30, 0.99, NA, 0.04 * 16, 0.20 * 16), #9
     c(0.23 * 16, 0.75 * 16, 0.05 * 16, 0.09 * 16, 0.30 * 16, NA, 0.05 * 16, NA, 2.49, NA, 1.80, 0.99, NA, 0.04 * 16, 0.20 * 16)) #10

ea_ht_present_matrix <- matrix(unlist(ea_ht_present), ncol = 15, byrow = T)
ea_ht_prices_matrix <- matrix(unlist(ea_ht_prices), ncol = 15, byrow = T)

safeway

# Safeway
sw_addys <- c("3713 Lee Hwy., Arlington, VA 22207", # 1
              "2500 N Harrison St., Arlington, VA 22207", # 2
              "6244 Old Dominion Dr., Mclean, VA 22101",# 3
              "5101 Wilson Blvd., Arlington, VA 22205", # 4
              "4701 Sangamore Rd., MD 20816", # 5
              "1855 Wisconsin Ave NW, Washington DC 20007", # 6
              "6118 Arlington Blvd., Falls Church, VA 22044", # 7
              "1747 Columbia Rd. NW, Washingon DC, 20009",# 8
              "3526 King St., Alexandria, VA 22302",# 9
              "299 S Van Dorn St., Alexandria, VA 22304", # 10
              "500 S Royal St., Alexandria, VA 22314",# 11
              "6130 Rose Hill Dr., Alexandria, VA 22310", # 12
              "1525 Wilson Blvd., Arlington, VA 22209", # 13
              "1624 Belle View, Alexandria, VA 22307") # 14
#SKIP Balducci's

# East African - Barley, Curry Powder, Black Eye Peas, Dried Beans, Dried Dates, Fava Beans, Garbanzo Beans, Goat, Lentils,
# Red Chori Beans, Basmati Rice, Spaghetti Pasta, Teff, Tomato Sauce, Tuna

ea_sw_present <- list(
     c(T, T, T, T, T, T, T, F, T, F, T, T, F, T, T), # 1
     c(T, T, T, T, T, T, T, F, T, F, T, T, F, T, T), # 2
     c(T, T, T, T, T, T, T, F, T, F, T, T, F, T, T), # 3
     c(T, T, T, T, T, F, T, F, T, F, T, T, F, T, T), # 4
     c(T, T, T, T, T, T, T, F, T, F, T, T, F, T, T), # 5
     c(T, T, T, T, T, T, T, F, T, F, T, T, F, T, T), # 6
     c(T, T, T, T, T, T, T, F, T, F, T, T, F, T, T), # 7
     c(T, T, T, T, T, T, T, F, T, F, T, T, F, T, T), # 8
     c(T, T, T, T, T, F, T, F, T, F, T, T, F, T, T), # 9
     c(T, T, T, T, T, T, T, F, T, F, T, T, F, T, T), # 10
     c(T, T, T, T, T, F, T, F, T, F, T, T, F, T, T), # 11
     c(T, T, T, T, T, T, T, F, T, F, T, T, F, T, T), # 12
     c(T, T, T, T, T, F, T, F, T, F, T, T, F, T, T), # 13
     c(T, T, T, T, T, T, T, F, T, F, T, T, F, T, T)) # 14

# East African - Barley, Curry Powder, Black Eye Peas, Dried Beans, Dried Dates, Fava Beans, Garbanzo Beans, Goat, Lentils,
# Red Chori Beans, Basmati Rice, Spaghetti Pasta, Teff, Tomato Sauce, Tuna

# beans barley?
# check out basmati rice
ea_sw_prices <- list(
     c(2.19, 2.20 * 16, 2.19, 1.49, 0.31 * 16, 5.99 * 4, 0.06 * 16, NA, 1.69, NA, 0.08 * 16, 0.88, NA, 0.06 * 16, 0.16 * 16), #1
     c(2.19, 2.20 * 16, 2.19, 1.49, 0.31 * 16, 5.99 * 4, 0.06 * 16, NA, 1.79, NA, 0.08 * 16, 0.88, NA, 0.06 * 16, 0.16 * 16), #2
     c(2.19, 2.20 * 16, 2.19, 1.49, 0.31 * 16, 5.99 * 4, 0.06 * 16, NA, 1.79, NA, 0.08 * 16, 0.88, NA, 0.06 * 16, 0.16 * 16), #3
     c(2.19, 2.97 * 16, 2.19, 1.49, 0.31 * 16, NA, 0.06 * 16, NA, 1.79, NA, 0.08 * 16, 0.88, NA, 0.06 * 16, 0.16 * 16), #4
     c(1.99, 2.20 * 16, 2.19, 1.49, 0.31 * 16, 5.99 * 4, 0.06 * 16, NA, 1.79, NA, 0.08 * 16, 0.88, NA, 0.06 * 16, 0.16 * 16), #5
     c(1.99, 2.20 * 16, 2.19, 1.49, 0.31 * 16, 5.99 * 4, 0.06 * 16, NA, 1.79, NA, 0.08 * 16, 0.88, NA, 0.06 * 16, 0.16 * 16), #6
     c(1.99, 0.61 * 16, 2.19, 1.49, 0.31 * 16, 0.18 * 16, 0.06 * 16, NA, 1.79, NA, 0.08 * 16, 0.88, NA, 0.06 * 16, 0.16 * 16), #7
     c(1.99, 3.61 * 16, 2.19, 1.49, 0.31 * 16, 5.99 * 4, 0.06 * 16, NA, 1.99, NA, 0.08 * 16, 0.88, NA, 0.06 * 16, 0.16 * 16), #8
     c(1.99, 2.20 * 16, 2.19, 1.49, 0.31 * 16, NA, 0.06 * 16, NA, 1.99, NA, 0.08 * 16, 0.88, NA, 0.06 * 16, 0.16 * 16), #9
     c(1.59, 2.20 * 16, 2.19, 1.49, 0.31 * 16, 5.99 * 4, 0.06 * 16, NA, 1.79, NA, 0.08 * 16, 0.99, NA, 0.06 * 16, 0.16 * 16), #10
     c(2.19, 3.61 * 16, 2.19, 2.19, 0.31 * 16, NA, 0.06 * 16, NA, 2.19, NA, 0.08 * 16, 0.99, NA, 0.06 * 16, 0.16 * 16), #11
     c(2.19, 1.25 * 16, 2.19, 1.49, 0.31 * 16, 5.99 * 4, 0.06 * 16, NA, 1.79, NA, 0.08 * 16, 0.99, NA, 0.06 * 16, 0.16 * 16), #12
     c(2.19, 2.20 * 16, 2.19, 1.49, 0.31 * 16, NA, 0.06 * 16, NA, 1.99, NA, 0.08 * 16, 0.99, NA, 0.06 * 16, 0.16 * 16), #13
     c(2.19, 2.20 * 16, 2.19, 1.49, 0.31 * 16, 5.99 * 4, 0.06 * 16, NA, 1.79, NA, 0.08 * 16, 0.99, NA, 0.06 * 16, 0.16 * 16)) #14

ea_sw_present_matrix <- matrix(unlist(ea_sw_present), ncol = 15, byrow = T)
ea_sw_prices_matrix <- matrix(unlist(ea_sw_prices), ncol = 15, byrow = T)

aldi

# Aldi
aldi_addys <- c("155 Hillwood Ave, Falls Church, VA 22046",
                "5725 Columbia Pike, Falls Church, VA 22041",
                "4602 Kenmore Ave, Alexandria, VA 22304")

ea_aldi_present <- list(
     c(F, F, F, T, T, F, F, F, F, F, T, T, F, T, T), # 1
     c(F, F, F, T, T, F, F, F, F, F, T, T, F, T, T), # 2
     c(F, F, F, T, T, F, F, F, F, F, T, T, F, T, T)) # 3

# East African - Barley, Curry Powder, Black Eye Peas, Dried Beans, Dried Dates, Fava Beans, Garbanzo Beans, Goat, Lentils,
# Red Chori Beans, Basmati Rice, Spaghetti Pasta, Teff, Tomato Sauce, Tuna

ea_aldi_prices <- list(
     c(NA, NA, NA, 1.55, 5.49, NA, NA, NA, NA, NA, 3.15 / 2, 1.25 / 2, NA, 0.99 / 24 * 16, 0.75 / 5 * 16), #1
     c(NA, NA, NA, 1.55, 5.49, NA, NA, NA, NA, NA, 3.15 / 2, 1.25 / 2, NA, 0.99 / 24 * 16, 0.75 / 5 * 16), #2
     c(NA, NA, NA, 1.55, 5.49, NA, NA, NA, NA, NA, 3.15 / 2, 1.25 / 2, NA, 0.99 / 24 * 16, 0.75 / 5 * 16)) #3

ea_aldi_present_matrix <- matrix(unlist(ea_aldi_present), ncol = 15, byrow = T)
ea_aldi_prices_matrix <- matrix(unlist(ea_aldi_prices), ncol = 15, byrow = T)

target

# I don't think this is hitting exactly 5 miles out, but close enough (only site without distances - so using nearby cities)
# Target
target_addys <- c("740 N Glebe Rd., Arlington, VA 22203", # 1
                  "1500 Wilson Blvd., Rosslyn, VA 22209", # 2
                  "6100 Arlington Blvd., Falls Church, VA 22044", # 3
                  "500 S. Washington St., Falls Church, VA 22046", # 4
                  "5115 Leesburg Pike, Falls Church, VA 22041", # 5
                  "3101 Richmond Hwy., Alexandria, VA 22305") # 6

ea_target_present <- list(
  c(F, F, F, T, F, F, T, F, T, F, T, T, F, T, T), # 1
  c(F, F, F, T, F, F, F, F, T, F, T, T, F, T, T), # 2
  c(F, T, F, T, F, F, T, F, T, F, T, T, F, T, T), # 3
  c(F, T, F, T, F, F, T, F, T, F, T, T, F, T, T), # 4
  c(F, T, F, T, T, F, T, F, T, F, T, T, F, T, T), # 5
  c(F, T, F, T, T, F, T, F, T, F, F, T, F, T, T)) # 6

# East African - Barley, Curry Powder, Black Eye Peas, Dried Beans, Dried Dates, Fava Beans, Garbanzo Beans, Goat, Lentils,
# Red Chori Beans, Basmati Rice, Spaghetti Pasta, Teff, Tomato Sauce, Tuna

ea_target_prices <- list(
  c(NA, NA, NA, 1.89, NA, NA, 1.19 / 15 * 16, NA, 1.89, NA, 7.99 / 30 * 16, 0.85, NA, 0.65 / 15 * 16, 3.69 / 20 * 16), #1
  c(NA, NA, NA, 1.89, NA, NA, NA, NA, 1.89, NA, 7.99 / 30 * 16, 0.85, NA, 0.55 / 15 * 16, 3.69 / 20 * 16), #2
  c(NA, 4.69 / 1.9 * 16, NA, 1.29, NA, NA, 1.29 / 15.5 * 16, NA, 1.99, NA, 3.99 / 2, 0.85, NA, 1.19 / 29 * 16, 3.89 / 20 * 16), #3
  c(NA, 2.99 / 0.9 * 16, NA, 3.59 / 2, NA, NA, 1.99, NA, 1.99, NA, 7.69 / 30 * 16, 0.85, NA, 0.65 / 15 * 16, 3.89 / 20 * 16), #4
  c(NA, 2.99 / 0.9 * 16, NA, 1.29, 2.99 / 10 * 16, NA, 1.19 / 15 * 16, NA, 1.99, NA, 7.69 / 30 * 16, 0.85, NA, 1.19 / 29 * 16, 3.89 / 20 * 16), #5
  c(NA, 4.69 / 1.9 * 16, NA, 1.29, 2.99 / 10 * 16, NA, 1.19 / 15 * 16, NA, 1.99, NA, NA, 1.49 / 2, NA, 1.19 / 29 * 16, 3.89 / 20 * 16)) #6

ea_target_present_matrix <- matrix(unlist(ea_target_present), ncol = 15, byrow = T)
ea_target_prices_matrix <- matrix(unlist(ea_target_prices), ncol = 15, byrow = T)

whole foods

# Whole Foods
wf_addys <- c("520 12th St. South, Ste. 100, Arlington, VA 22202", # 1
              "2700 Wilson Blvd., Arlington, VA 22201",  # 2
              "2201 I Street NW, Washington DC 20037", # 3
              "101 H St. SE, Washington DC 20003", # 4
              "5269 River Rd, Bethesda, MD 20816", # 5
              "1440 P Street NW, Washington DC 20005", # 6
              "1700 Duke St, Alexandria, VA 22314",  # 7
              "7511 Leesburk Pike, Falls Church, VA 22043",  # 8
              "4530 40th St. NW, Tenley Circle, Washington DC 20016", # 9
              "4420 Willard Ave., Chevy Chase, MD 20815")  # 10

ea_wf_present <- list(
  c(T, T, T, T, T, F, T, F, T, F, T, T, T, T, T), # 1
  c(T, T, T, T, T, F, T, F, T, F, T, T, T, T, T), # 2
  c(T, T, T, T, T, F, T, F, T, F, T, T, T, T, T), # 3
  c(T, T, T, T, T, F, T, F, T, F, T, T, T, T, T), # 4
  c(T, T, T, T, T, F, T, F, T, F, T, T, T, T, T), # 5
  c(T, T, F, T, T, F, T, F, T, F, T, T, T, T, T), # 6
  c(T, T, F, T, T, F, T, F, T, F, T, T, T, T, T), # 7
  c(T, T, T, T, T, F, T, F, T, F, T, T, T, T, T), # 9
  c(T, T, T, T, T, F, T, F, T, F, T, T, T, T, T), # 10
  c(T, T, T, T, T, F, T, F, T, F, T, T, T, T, T)) # 11

# East African - Barley, Curry Powder, Black Eye Peas, Dried Beans, Dried Dates, Fava Beans, Garbanzo Beans, Goat, Lentils,
# Red Chori Beans, Basmati Rice, Spaghetti Pasta, Teff, Tomato Sauce, Tuna

# what if a store has barley, peas + lentils sold together...
# using pinto beans for dried beans??
# check curry powders???
ea_wf_prices <- list(
  c(3.29 / 8.8*16, 2.49 / 1.5*16, 1.49 / 15.5*16, 1.99, 3.49 * 2, NA, 0.89 / 15.5*16, NA, 2.49, NA, 3.79 / 2, 1.09, 6.69 / 20*16, 0.99 * 2, 2.09 / 5*16), # 1
  c(3.29 / 8.8*16, 2.49 / 1.5*16, 1.47, 2.39 / 15*16, 3.49 * 2, NA, 0.89 / 15.5*16, NA, 2.49, NA, 3.79 / 2, 1.09, 6.69 / 20*16, 0.99 * 2, 2.09 / 5*16), # 2
  c(3.29 / 8.8*16, 2.49 / 1.5*16, 1.49 / 15.5*16, 1.99, 3.49 * 2, NA, 0.89 / 15.5*16, NA, 2.49, NA, 3.79 / 2, 1.09, 6.69 / 20*16, 0.99 * 2, 2.09 / 5*16), # 3
  c(3.29 / 8.8*16, 2.49 / 1.5*16, 1.47, 2.39 / 15*16, 3.49 * 2, NA, 0.89 / 15.5*16, NA, 1.99, NA, 3.79 / 2, 1.09, 6.69 / 20*16, 0.99 * 2, 2.09 / 5*16), # 4
  c(3.29 / 8.8*16, 2.49 / 1.5*16, 1.47, 2.39 / 15*16, 3.49 * 2, NA, 0.89 / 15.5*16, NA, 2.49, NA, 3.79 / 2, 1.09, 6.69 / 20*16, 0.99 * 2, 2.09 / 5*16), # 5
  c(3.29 / 8.8*16, 6.29 / 3*16, NA, 1.99, 3.49 * 2, NA, 0.89 / 15.5*16, NA, 2.49, NA, 3.79 / 2, 1.09, 6.69 / 20*16, 0.99 * 2, 2.09 / 5*16), # 6
  c(1.99, 5.29 / 2.8*16, NA, 1.99, 3.49 * 2, NA, 0.89 / 15.5*16, NA, 2.99, NA, 3.79 / 2, 1.09, 6.69 / 20*16, 0.99 * 2, 2.09 / 5*16), # 7
  c(3.29 / 8.8*16, 5.29 / 2.8*16, 1.47, 1.99, 3.49 * 2, NA, 0.89 / 15.5*16, NA, 2.49, NA, 3.79 / 2, 1.09, 6.69 / 20*16, 0.99 * 2, 2.09 / 5*16), # 8
  c(3.29 / 8.8*16, 5.29 / 2.8*16, 1.47, 1.99, 3.49 * 2, NA, 0.89 / 15.5*16, NA, 2.49, NA, 3.79 / 2, 1.09, 6.69 / 20*16, 0.99 * 2, 2.09 / 5*16), # 9
  c(1.99, 5.29 / 2.8*16, 1.49 / 15.5*16, 1.99, 3.49 * 2, NA, 0.89 / 15.5*16, NA, 2.49, NA, 3.79 / 2, 1.09, 6.69 / 20*16, 0.99 * 2, 2.09 / 5*16)) # 10

ea_wf_present_matrix <- matrix(unlist(ea_wf_present), ncol = 15, byrow = T)
ea_wf_prices_matrix <- matrix(unlist(ea_wf_prices), ncol = 15, byrow = T)

lidl

# Lidl
lidl_addys <- c("2901 Gallows Road, Falls Church, VA 22042", # 1
                "6548 Little River Turnpike, Alexandria, VA 22312") # 2
# "3500 S. Clark St., Arlington, VA 22202",  = express store, so dropped

ea_lidl_present <- list(
  c(F, T, T, T, T, F, T, F, T, F, F, T, F, T, T), # 1
  c(F, T, T, T, T, F, T, F, T, F, F, T, F, T, T)) # 2

# East African - Barley, Curry Powder, Black Eye Peas, Dried Beans, Dried Dates, Fava Beans, Garbanzo Beans, Goat, Lentils,
# Red Chori Beans, Basmati Rice, Spaghetti Pasta, Teff, Tomato Sauce, Tuna

ea_lidl_prices <- list(
  c(NA, 0.497 * 16, 0.05 * 16, 1.58, 0.713 * 16, NA, 3.5 * 16, NA, 1.98, NA, NA, 6.8 * 16, NA, 3.3 * 16, 18.6 * 16), #1
  c(NA, 0.497 * 16, 0.05 * 16, 1.58, 0.713 * 16, NA, 3.5 * 16, NA, 1.98, NA, NA, 6.8 * 16, NA, 3.3 * 16, 18.6 * 16)) #2

ea_lidl_present_matrix <- matrix(unlist(ea_lidl_present), ncol = 15, byrow = T)
ea_lidl_prices_matrix <- matrix(unlist(ea_lidl_prices), ncol = 15, byrow = T)

sum up number of items per store; get locations; finish gravity model (and make specific plot for certain items!)

ea_all_matrix <- rbind(ea_giant_present_matrix, ea_ht_present_matrix, ea_sw_present_matrix, ea_aldi_present_matrix, ea_target_present_matrix, ea_wf_present_matrix, ea_lidl_present_matrix)

eastaf.data <- data.frame(
  address = c(giant_addys, ht_addys, sw_addys, aldi_addys, target_addys, wf_addys, lidl_addys),
  chain =  c(rep(c("Giant", "Harris Teeter", "Safeway", "Aldi", "Target", "Whole Foods","Lidl"), c(length(giant_addys), length(ht_addys), length(sw_addys), length(aldi_addys), length(target_addys), length(wf_addys), length(lidl_addys)))),
  num_items = rowSums(ea_all_matrix),
  item = ea_all_matrix)

gravity model plot

### how do we visualize where these stores are located - explains why the map is different (could put points down where located)
store_dist <- get_distance_matrix(dist.mat, eastaf.data$num_items)
a <- get_stores_within_distance_of_bg(store_dist, ar.bg.centroid$GEOID, dist_mi = 15)
all.access <- format_idx(a)

# store_dist <- get_distance_matrix(dist.mat2, latinx.data$item.1)
# a <- get_stores_within_distance_of_bg(store_dist, ar.bg.centroid$GEOID, dist_mi = 15)
# avocado.access <- format_idx(a)
# 
# store_dist <- get_distance_matrix(dist.mat2, latinx.data$item.18)
# a <- get_stores_within_distance_of_bg(store_dist, ar.bg.centroid$GEOID, dist_mi = 15)
# tomatillo.access <- format_idx(a)
# 
# store_dist <- get_distance_matrix(dist.mat2, latinx.data$item.12)
# a <- get_stores_within_distance_of_bg(store_dist, ar.bg.centroid$GEOID, dist_mi = 15)
# nopalito.access <- format_idx(a)

make_arlington_plot(all.access, eastaf.data$num_items, "East African Staple Food Items Access")
# make_arlington_plot(avocado.access, latinx.data$item.1, "Avocado Access")
# make_arlington_plot(idx.sf.tomatillo, latinx.data$item.18, "Tomatillo Access")
# make_arlington_plot(idx.sf.nopalito, latinx.data$item.12, "Nopalito Access")

make general plots

dist.mat2 <- get_times_2(ar.ca.centroid, d2.2, "car")
store_dist2 <- get_distance_matrix2(dist.mat2, eastaf.data$num_items)
a <- get_stores_within_distance_of_bg2(store_dist2, ar.ca.centroid$OBJECTID)
all.access <- format_idx2(a)

make_arlington_plot2(all.access, eastaf.data$num_items, "Latinx Staple Food Item Access")
make_arlington_plot2_wo_points(all.access, eastaf.data$num_items, "East African Staple Food Item Access")

get data for civic associations, east african staples

dist.mat2 <- get_times_2(ar.ca.centroid, d2.2, "car")
store_dist2.1 <- get_distance_matrix2(dist.mat2, eastaf.data$item.1)
store_dist2.2 <- get_distance_matrix2(dist.mat2, eastaf.data$item.2)
store_dist2.3 <- get_distance_matrix2(dist.mat2, eastaf.data$item.3)
store_dist2.4 <- get_distance_matrix2(dist.mat2, eastaf.data$item.4)
store_dist2.5 <- get_distance_matrix2(dist.mat2, eastaf.data$item.5)
store_dist2.6 <- get_distance_matrix2(dist.mat2, eastaf.data$item.6)
store_dist2.7 <- get_distance_matrix2(dist.mat2, eastaf.data$item.7)
store_dist2.8 <- get_distance_matrix2(dist.mat2, eastaf.data$item.8)
store_dist2.9 <- get_distance_matrix2(dist.mat2, eastaf.data$item.9)
store_dist2.10 <- get_distance_matrix2(dist.mat2, eastaf.data$item.10)
store_dist2.11 <- get_distance_matrix2(dist.mat2, eastaf.data$item.11)
store_dist2.12 <- get_distance_matrix2(dist.mat2, eastaf.data$item.12)
store_dist2.13 <- get_distance_matrix2(dist.mat2, eastaf.data$item.13)
store_dist2.14 <- get_distance_matrix2(dist.mat2, eastaf.data$item.14)
store_dist2.15 <- get_distance_matrix2(dist.mat2, eastaf.data$item.15)
store_dist2.all <- get_distance_matrix2(dist.mat2, eastaf.data$num_items)

a.1 <- get_stores_within_distance_of_bg2(store_dist2.1, ar.ca.centroid$OBJECTID)
a.2 <- get_stores_within_distance_of_bg2(store_dist2.2, ar.ca.centroid$OBJECTID)
a.3 <- get_stores_within_distance_of_bg2(store_dist2.3, ar.ca.centroid$OBJECTID)
a.4 <- get_stores_within_distance_of_bg2(store_dist2.4, ar.ca.centroid$OBJECTID)
a.5 <- get_stores_within_distance_of_bg2(store_dist2.5, ar.ca.centroid$OBJECTID)
a.6 <- get_stores_within_distance_of_bg2(store_dist2.6, ar.ca.centroid$OBJECTID)
a.7 <- get_stores_within_distance_of_bg2(store_dist2.7, ar.ca.centroid$OBJECTID)
a.8 <- get_stores_within_distance_of_bg2(store_dist2.8, ar.ca.centroid$OBJECTID)
a.9 <- get_stores_within_distance_of_bg2(store_dist2.9, ar.ca.centroid$OBJECTID)
a.10 <- get_stores_within_distance_of_bg2(store_dist2.10, ar.ca.centroid$OBJECTID)
a.11 <- get_stores_within_distance_of_bg2(store_dist2.11, ar.ca.centroid$OBJECTID)
a.12 <- get_stores_within_distance_of_bg2(store_dist2.12, ar.ca.centroid$OBJECTID)
a.13 <- get_stores_within_distance_of_bg2(store_dist2.13, ar.ca.centroid$OBJECTID)
a.14 <- get_stores_within_distance_of_bg2(store_dist2.14, ar.ca.centroid$OBJECTID)
a.15 <- get_stores_within_distance_of_bg2(store_dist2.15, ar.ca.centroid$OBJECTID)
a.all <- get_stores_within_distance_of_bg2(store_dist2.all, ar.ca.centroid$OBJECTID)

# East African - Barley, Curry Powder, Black Eye Peas, Dried Beans, Dried Dates, Fava Beans, Garbanzo Beans, Goat, Lentils,
# Red Chori Beans, Basmati Rice, Spaghetti Pasta, Teff, Tomato Sauce, Tuna

all.access.1 <- st_drop_geometry(format_idx2(a.1)) %>% select(LABEL, GIS_ID, idx, norm.idx, ecdf.idx) %>%
  rename(barley_idx = idx, barley_norm_idx = norm.idx, barley_ecdf_idx = ecdf.idx)
all.access.2 <- cbind(all.access.1, st_drop_geometry(format_idx2(a.2)) %>% select(idx, norm.idx, ecdf.idx) %>%
  rename(currypowder_idx = idx, currypowder_norm_idx = norm.idx, currypowder_ecdf_idx = ecdf.idx))
all.access.3 <- cbind(all.access.2, st_drop_geometry(format_idx2(a.3)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(blackeyepeas_idx = idx, blackeyepeas_norm_idx = norm.idx, blackeyepeas_ecdf_idx = ecdf.idx))
all.access.4 <- cbind(all.access.3, st_drop_geometry(format_idx2(a.4)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(driedbeans_idx = idx, driedbeans_norm_idx = norm.idx, driedbeans_ecdf_idx = ecdf.idx))
all.access.5 <- cbind(all.access.4, st_drop_geometry(format_idx2(a.5)) %>%  select(idx, norm.idx, ecdf.idx) %>%
                        rename(drieddates_idx = idx, drieddates_norm_idx = norm.idx, drieddates_ecdf_idx = ecdf.idx))
all.access.6 <- cbind(all.access.5, st_drop_geometry(format_idx2(a.6)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(favabeans_idx = idx, favabeans_norm_idx = norm.idx, favabeans_ecdf_idx = ecdf.idx))
all.access.7 <- cbind(all.access.6, st_drop_geometry(format_idx2(a.7)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(garbanzo_idx = idx, garbanzo_norm_idx = norm.idx, garbanzo_ecdf_idx = ecdf.idx))
all.access.8 <- cbind(all.access.7, st_drop_geometry(format_idx2(a.8)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(goat_idx = idx, goat_norm_idx = norm.idx, goat_ecdf_idx = ecdf.idx))
all.access.9 <- cbind(all.access.8, st_drop_geometry(format_idx2(a.9)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(lentils_idx = idx, lentils_norm_idx = norm.idx, lentils_ecdf_idx = ecdf.idx))
all.access.10 <- cbind(all.access.9, st_drop_geometry(format_idx2(a.10)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(redchori_idx = idx, redchori_norm_idx = norm.idx, redchori_ecdf_idx = ecdf.idx))
all.access.11 <- cbind(all.access.10, st_drop_geometry(format_idx2(a.11)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(basmati_idx = idx, basmati_norm_idx = norm.idx, basmati_ecdf_idx = ecdf.idx))
all.access.12 <- cbind(all.access.11, st_drop_geometry(format_idx2(a.12)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(pasta_idx = idx, pasta_norm_idx = norm.idx, pasta_ecdf_idx = ecdf.idx))
all.access.13 <- cbind(all.access.12, st_drop_geometry(format_idx2(a.13)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(teff_idx = idx, teff_norm_idx = norm.idx, teff_ecdf_idx = ecdf.idx))
all.access.14 <- cbind(all.access.13, st_drop_geometry(format_idx2(a.14)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(tomatosauce_idx = idx, tomatosauce_norm_idx = norm.idx, tomatosauce_ecdf_idx = ecdf.idx))
all.access.15 <- cbind(all.access.14, st_drop_geometry(format_idx2(a.15)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(tuna_idx = idx, tuna_norm_idx = norm.idx, tuna_ecdf_idx = ecdf.idx))
all.access.all <- cbind(all.access.15, st_drop_geometry(format_idx2(a.all)) %>% select(idx, norm.idx, ecdf.idx) %>%
  rename(all_idx = idx, all_norm_idx = norm.idx, all_ecdf_idx = ecdf.idx))

all.access.final.ca <- all.access.all %>%
  select(-c(goat_idx, goat_norm_idx, goat_ecdf_idx, redchori_idx, redchori_norm_idx, redchori_ecdf_idx)) %>%
  gather(measure, value, -c(LABEL, GIS_ID)) %>%
  mutate(year = "2021", measure_type = "index", measure_units = as.character(NA), region_type = "neighborhood") %>%
  rename(region_name = LABEL, geoid = GIS_ID) %>% 
  relocate("geoid", "region_type", "region_name", "year", "measure", "value", "measure_type", "measure_units")

# con <- get_db_conn()
# dc_dbWriteTable(con, "dc_working", "va_013_civic_sdad_2021_east_african_basket_gravity_model", all.access.final.ca)
# dbDisconnect(con)

getting data for block groups + east african basket

dist.mat <- get_times(ar.bg.centroid, d2.2, "car")
store_dist1.1 <- get_distance_matrix(dist.mat, eastaf.data$item.1)
store_dist1.2 <- get_distance_matrix(dist.mat, eastaf.data$item.2)
store_dist1.3 <- get_distance_matrix(dist.mat, eastaf.data$item.3)
store_dist1.4 <- get_distance_matrix(dist.mat, eastaf.data$item.4)
store_dist1.5 <- get_distance_matrix(dist.mat, eastaf.data$item.5)
store_dist1.6 <- get_distance_matrix(dist.mat, eastaf.data$item.6)
store_dist1.7 <- get_distance_matrix(dist.mat, eastaf.data$item.7)
store_dist1.8 <- get_distance_matrix(dist.mat, eastaf.data$item.8)
store_dist1.9 <- get_distance_matrix(dist.mat, eastaf.data$item.9)
store_dist1.10 <- get_distance_matrix(dist.mat, eastaf.data$item.10)
store_dist1.11 <- get_distance_matrix(dist.mat, eastaf.data$item.11)
store_dist1.12 <- get_distance_matrix(dist.mat, eastaf.data$item.12)
store_dist1.13 <- get_distance_matrix(dist.mat, eastaf.data$item.13)
store_dist1.14 <- get_distance_matrix(dist.mat, eastaf.data$item.14)
store_dist1.15 <- get_distance_matrix(dist.mat, eastaf.data$item.15)
store_dist1.all <- get_distance_matrix(dist.mat, eastaf.data$num_items)

b.1 <- get_stores_within_distance_of_bg(store_dist1.1, ar.bg.centroid$GEOID)
b.2 <- get_stores_within_distance_of_bg(store_dist1.2, ar.bg.centroid$GEOID)
b.3 <- get_stores_within_distance_of_bg(store_dist1.3, ar.bg.centroid$GEOID)
b.4 <- get_stores_within_distance_of_bg(store_dist1.4, ar.bg.centroid$GEOID)
b.5 <- get_stores_within_distance_of_bg(store_dist1.5, ar.bg.centroid$GEOID)
b.6 <- get_stores_within_distance_of_bg(store_dist1.6, ar.bg.centroid$GEOID)
b.7 <- get_stores_within_distance_of_bg(store_dist1.7, ar.bg.centroid$GEOID)
b.8 <- get_stores_within_distance_of_bg(store_dist1.8, ar.bg.centroid$GEOID)
b.9 <- get_stores_within_distance_of_bg(store_dist1.9, ar.bg.centroid$GEOID)
b.10 <- get_stores_within_distance_of_bg(store_dist1.10, ar.bg.centroid$GEOID)
b.11 <- get_stores_within_distance_of_bg(store_dist1.11, ar.bg.centroid$GEOID)
b.12 <- get_stores_within_distance_of_bg(store_dist1.12, ar.bg.centroid$GEOID)
b.13 <- get_stores_within_distance_of_bg(store_dist1.13, ar.bg.centroid$GEOID)
b.14 <- get_stores_within_distance_of_bg(store_dist1.14, ar.bg.centroid$GEOID)
b.15 <- get_stores_within_distance_of_bg(store_dist1.15, ar.bg.centroid$GEOID)
b.all <- get_stores_within_distance_of_bg(store_dist1.all, ar.bg.centroid$GEOID)

# East African - Barley, Curry Powder, Black Eye Peas, Dried Beans, Dried Dates, Fava Beans, Garbanzo Beans, Goat, Lentils,
# Red Chori Beans, Basmati Rice, Spaghetti Pasta, Teff, Tomato Sauce, Tuna

b.all.access.1 <- st_drop_geometry(format_idx(b.1)) %>% select(NAME, GEOID, idx, norm.idx, ecdf.idx) %>%
  rename(barley_idx = idx, barley_norm_idx = norm.idx, barley_ecdf_idx = ecdf.idx)
b.all.access.2 <- cbind(b.all.access.1, st_drop_geometry(format_idx(b.2)) %>% select(idx, norm.idx, ecdf.idx) %>%
  rename(currypowder_idx = idx, currypowder_norm_idx = norm.idx, currypowder_ecdf_idx = ecdf.idx))
b.all.access.3 <- cbind(b.all.access.2, st_drop_geometry(format_idx(b.3)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(blackeyepeas_idx = idx, blackeyepeas_norm_idx = norm.idx, blackeyepeas_ecdf_idx = ecdf.idx))
b.all.access.4 <- cbind(b.all.access.3, st_drop_geometry(format_idx(b.4)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(driedbeans_idx = idx, driedbeans_norm_idx = norm.idx, driedbeans_ecdf_idx = ecdf.idx))
b.all.access.5 <- cbind(b.all.access.4, st_drop_geometry(format_idx(b.5)) %>%  select(idx, norm.idx, ecdf.idx) %>%
                        rename(drieddates_idx = idx, drieddates_norm_idx = norm.idx, drieddates_ecdf_idx = ecdf.idx))
b.all.access.6 <- cbind(b.all.access.5, st_drop_geometry(format_idx(b.6)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(favabeans_idx = idx, favabeans_norm_idx = norm.idx, favabeans_ecdf_idx = ecdf.idx))
b.all.access.7 <- cbind(b.all.access.6, st_drop_geometry(format_idx(b.7)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(garbanzo_idx = idx, garbanzo_norm_idx = norm.idx, garbanzo_ecdf_idx = ecdf.idx))
b.all.access.8 <- cbind(b.all.access.7, st_drop_geometry(format_idx(b.8)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(goat_idx = idx, goat_norm_idx = norm.idx, goat_ecdf_idx = ecdf.idx))
b.all.access.9 <- cbind(b.all.access.8, st_drop_geometry(format_idx(b.9)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(lentils_idx = idx, lentils_norm_idx = norm.idx, lentils_ecdf_idx = ecdf.idx))
b.all.access.10 <- cbind(b.all.access.9, st_drop_geometry(format_idx(b.10)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(redchori_idx = idx, redchori_norm_idx = norm.idx, redchori_ecdf_idx = ecdf.idx))
b.all.access.11 <- cbind(b.all.access.10, st_drop_geometry(format_idx(b.11)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(basmati_idx = idx, basmati_norm_idx = norm.idx, basmati_ecdf_idx = ecdf.idx))
b.all.access.12 <- cbind(b.all.access.11, st_drop_geometry(format_idx(b.12)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(pasta_idx = idx, pasta_norm_idx = norm.idx, pasta_ecdf_idx = ecdf.idx))
b.all.access.13 <- cbind(b.all.access.12, st_drop_geometry(format_idx(b.13)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(teff_idx = idx, teff_norm_idx = norm.idx, teff_ecdf_idx = ecdf.idx))
b.all.access.14 <- cbind(b.all.access.13, st_drop_geometry(format_idx(b.14)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(tomatosauce_idx = idx, tomatosauce_norm_idx = norm.idx, tomatosauce_ecdf_idx = ecdf.idx))
b.all.access.15 <- cbind(b.all.access.14, st_drop_geometry(format_idx(b.15)) %>% select(idx, norm.idx, ecdf.idx) %>%
                        rename(tuna_idx = idx, tuna_norm_idx = norm.idx, tuna_ecdf_idx = ecdf.idx))
b.all.access.all <- cbind(b.all.access.15, st_drop_geometry(format_idx(b.all)) %>% select(idx, norm.idx, ecdf.idx) %>%
  rename(all_idx = idx, all_norm_idx = norm.idx, all_ecdf_idx = ecdf.idx))

all.access.final.bg <- b.all.access.all %>%
  select(-c(goat_idx, goat_norm_idx, goat_ecdf_idx, redchori_idx, redchori_norm_idx, redchori_ecdf_idx)) %>%
  gather(measure, value, -c(NAME, GEOID)) %>%
  mutate(year = "2021", measure_type = "index", measure_units = as.character(NA), region_type = "block group") %>%
  rename(region_name = NAME, geoid = GEOID) %>% 
  relocate("geoid", "region_type", "region_name", "year", "measure", "value", "measure_type", "measure_units")

# con <- get_db_conn()
# dc_dbWriteTable(con, "dc_working", "va_013_bg_sdad_2021_east_african_basket_gravity_model", all.access.final.bg)
# dbDisconnect(con)

correct the formatting of neighborhoods with the update

con <- get_db_conn()
arl_bg_la_basket <- st_read(con, query = "SELECT * FROM dc_working.va_013_bg_sdad_2021_latino_basket_gravity_model")
arl_ca_la_basket <- st_read(con, query = "SELECT * FROM dc_working.va_013_civic_sdad_2021_latino_basket_gravity_model")
arl_bg_ea_basket <- st_read(con, query = "SELECT * FROM dc_working.va_013_bg_sdad_2021_east_african_basket_gravity_model")
arl_ca_ea_basket <- st_read(con, query = "SELECT * FROM dc_working.va_013_civic_sdad_2021_east_african_basket_gravity_model")
dbDisconnect(con)

arl_ca_la_basket$geoid <- str_replace_all(tolower(arl_ca_la_basket$region_name), " ", "_")
arl_ca_ea_basket$geoid <- str_replace_all(tolower(arl_ca_ea_basket$region_name), " ", "_")

ca_names_csv <- read.csv("/project/biocomplexity/sdad/projects_data/mc/data_commons/dc_health_behavior_diet/va_catr_sdad_2021_mrfei.csv")
arl_ca_la_basket2 <- left_join(arl_ca_la_basket, ca_names_csv[, c("region_name", "geoid")], by = "region_name") %>%
  select(-geoid.x) %>% rename(geoid = geoid.y) %>%
  relocate("geoid", "region_type", "region_name", "year", "measure", "value", "measure_type", "measure_units")

arl_ca_ea_basket2 <- left_join(arl_ca_ea_basket, ca_names_csv[, c("region_name", "geoid")], by = "region_name") %>%
  select(-geoid.x) %>% rename(geoid = geoid.y) %>%
  relocate("geoid", "region_type", "region_name", "year", "measure", "value", "measure_type", "measure_units")

# con <- get_db_conn()
# dc_dbWriteTable(con, "dc_working", "va_013_civic_sdad_2021_latino_basket_gravity_model_update", arl_ca_la_basket2)
# dc_dbWriteTable(con, "dc_working", "va_013_civic_sdad_2021_east_african_basket_gravity_model_update", arl_ca_ea_basket2)
# dbDisconnect(con)

# length(unique(arl_ca_ea_basket2$measure))

change neighborhood names

map <- jsonlite::read_json("https://raw.githubusercontent.com/uva-bi-sdad/dc.geographies/main/data/va013_geo_arl_2021_civic_associations/distribution/va013_geo_arl_2021_civic_associations.geojson")
name_map <- unlist(lapply(map$features, function(e)
  structure(e$properties$geoid, names = e$properties$region_name)))
data <- read.csv(gzfile("data/va_013_cabg_sdad_2021_latino_east_african_gravity_models.csv.xz"))
data[data$region_name == "Chain Bridge Forest, Bridge, Forest", "region_name"] <- "Chain Bridge Forest"
su <- data$region_name %in% names(name_map)
data[su, "geoid"] <- name_map[data[su, "region_name"]]
write.csv(data, xzfile("data/va_013_cabg_sdad_2021_latino_east_african_gravity_models.csv.xz"), row.names = FALSE)

uva-bi-sdad/dc.staple.food.basket documentation built on June 11, 2022, 11:46 p.m.