Project Title:
Supporting Eat Greater Des Moines and Food Rescue in Central Iowa
Project Contacts:
Adisak Sukul
adisak@iastate.edu
Associate Teaching Professor, Department of Computer Science
Iowa State University
Matthew Voss
mjvoss@iastate.edu
Master of Public Affairs
University of Wisconsin-Madison
Project Team:
Adisak Sukul, DSPG Faculty Project Lead
Cass Dorius, DSPG Faculty Advisor
Matthew Voss, DSPG Graduate Fellow
Zack Johnson, DSPG Undergraduate Intern
Ellie Uhrhammer, DSPG Undergraduate Intern
Saul Varshavsky, DSPG Undergraduate Intern
Stakeholders:
Eat Greater Des Moines
Aubrey Alvarez, Executive Director, Eat Greater Des Moines
Resources:
Eat Greater Des Moines Website: https://www.eatgreaterdesmoines.org/
Iowa State DSPG Website: https://dspg.iastate.edu/
DSPG EGDM Github repository: https://github.com/DSPG2021/eatgreaterdm
Preliminary dashboard: https://datastudio.google.com/s/sQRldwVrfbE
Dashboard currently used by EGDM: https://datastudio.google.com/s/i5GbchR1p9Y
Project Goals:
This project looks at the non-profit organization Eat Greater Des Moines (EGDM) and its food rescue efforts. EGDM takes donations of surplus food from grocery and convenience stores, restaurants, and other locations and transports it to food pantries, non-profits, schools, housing locations, and other organizations that can distribute food to those that need it. In the project, the team used data provided by EGDM and other sources to demonstrate how food rescue currently happens, how it can be expanded, and which areas can benefit most from food rescue. The team has built a data pipeline and dashboard that is sustainable for EGDM and will be used by the organization moving forward to support their food rescue efforts.
What is Eat Greater Des Moines:
Eat Greater Des Moines is a non-profit in Des Moines, Iowa that works to reduce food insecurity. The organization has several programs, but its biggest is food rescue, which was the focus of our project. Food rescue is different than a typical food donation model. Most food pantries focus on collecting food that is non-perishable, but EGDM food rescue collects food from restaurants, grocery stores, and convenience stores that may soon expire or may be wasted for another reason. Then, EGDM delivers it to food pantries, non-profits, and other community organizations that can distribute the food on a short timeline. (Figure 1)
Figure 1: EGDM Food Delivery Model
EGDM manages the logistics of getting the food to distribution locations, allowing those organizations to then distribute the food to people that need it most. This unique model also allows for a greater variety of food to be delivered than the non-perishable food on which most food pantries focus their efforts. For instance, EGDM is able to deliver bread, fruits, and vegetables, whereas most food pantries would not accept donations of those items.
Data and Methods:
The first step in this project was meeting with Eat Greater Des Moines in a Data Discovery Workshop. In this workshop, we learned about the EGDM food rescue model. We also learned about what objectives EGDM hoped our project would accomplish and how they might use tools that we would create. Finally, we asked about possible data sources, where we learned about the administrative data from EGDM that inform a significant part of our project. Figure 2 has some of the objectives provided by EGDM during the Data Discovery Workshop.
Figure 2: EGDM Mentimeter Responses in Data Discovery Workshop
After the Data Discovery Workshop, the EGDM administrative data sources were then shared with us. We conducted a data inventory, observing every file to see what sort of potential the files may have to help us meet the objectives stated during the Data Discovery Workshop. Among these files were the lists of EGDM partners and contacts, an Excel workbook with food donation and receipt information, and a list of USDA SNAP retailers. Several of these files offered the most data for us to use and investigate further throughout the project.
The next step was to clean the data and work towards creating a Google Cloud MySQL database. All of the data was initially in Excel workbooks, some with separate tabs representing different partner organizations or different types of partner organizations. The first steps of data cleaning involved collecting the data in separate tabs of each file into its own table, standardizing column naming conventions, and checking for duplicates or missing information.
After initial data cleaning, a relational schema was designed (Figure 3) for the structure of the database. We worked to develop linkages between the data tables and fill in missing information, including creating a partner id that would link several of the data tables. This could only be completed after matching the organization names in the data tables to make sure that a consistent partner id would be used across the entire database, and much of the checking was done manually using Excel. Missing address information for many organizations was also filled in manually, since the geospatial element was very important to the project.
Figure 3: Initial Database Relational Schema (not comprehensive)
Code and documentation for data cleaning can be found in the ISU DSPG 2021 eatgreaterdm Github repository. The link to the repository can be found in the Resources section. Most data cleaning was done using Python Google Collaboratory Jupyter notebooks. However, some work was done in R and some work was done manually in Excel/Google Sheets. Manual work is noted in our code documentation.
After data cleaning, partner locations and SNAP retailer data were geocoded using the Google Maps API to find the locations’ corresponding longitude and latitude. The data tables were then added to the EGDM Google Cloud MySQL using the MySQL Workbench data import wizard. Some locations that were not geocoded accurately were edited in an SQL script, which can also be found in the Github repository.
Once the data was cleaned and stored in the Google Cloud MySQL database, we began to analyze the data and create visualizations in both Python (code found in the eatgreaterdm repository) and Google Data Studio. We tried to answer questions such as: Who needs food? Where is food coming from and going? When is food being delivered? Is the food nutritious? Where is there potential for growth? Our results can be seen in the Findings section of this document.
Final Data Products:
Our final products for Eat Greater Des Moines were a collection of data tables and a dashboard. We copied all of the data tables from the Google Cloud MySQL Database to a Google Sheets workbook for easier access and use by EGDM. The sheets of this workbook are the individual tables from the database, including food transactions, partner information, partner contacts, and SNAP retailer information. There is also an additional data sheet with American Community Survey data at the zip code level intended to indicate possible areas of food insecurity.
This Google Sheets workbook is connected to a Google Data Studio dashboard for data visualization, as well as for easy access and use by EGDM. A preliminary dashboard with many visualizations was first presented to EGDM; then recommendations were given for the final dashboard that EGDM will continue to use after this summer. We followed those recommendations then presented the dashboard again to EGDM and talked about how to update the data and further change the dashboard. The links to the dashboards can be found in the Resources section.
EGDM plans to continually update the data that supports the dashboard. The organization also plans to use the dashboard to update the EGDM board about food rescue efforts, as well as provide partner organizations with a way to see potential impact of their food donations and see the larger picture of food rescue in central Iowa.
Findings:
Figure 4: Proportion of Population Receiving SNAP Benefits
Figure 4 shows the proportion of the total population that receives SNAP benefits (e.g. food stamps). This data comes from the U.S. Census Bureau American Community Survey 5-year estimates for 2015-2019. This map can help us identify areas that may have higher food insecurity; these areas may be locations that Eat Greater Des Moines should target for food distribution. It appears that these areas are those closer to the center of Des Moines, perhaps just north of downtown Des Moines. The outer, more suburban, areas appear to have lower proportions of residents that receive SNAP benefits.
Figure 5: EGDM Food Donor and Recipient Maps
Figure 5 displays the locations of addresses for every organization that is either a food recipient (shown by the bottom graph) or a food donor (shown by the top graph). The data for these graphs is from the partner information and food transactions tables from EGDM. The size of the dots corresponds to the number of food products donated or received in pounds, as indicated by the key “Pounds Donated/Received” at the bottom of each graph from Figure 5. Based on the findings from these graphs, we have observed that the locations of food recipient organizations that receive the most pounds of food are primarily located in the actual city of Des Moines. In contrast, the locations of food donor organizations that donate the most pounds of food are mainly located in the suburbs and other cities close by Des Moines, such as Ankeny and Altoona. These maps demonstrate that food currently may be going to the correct areas that were seen in Figure 4. However, the food is coming from outside of those areas, meaning that transportation distances are long. If more donors were close to recipient organizations, it may improve transportation efficiency. That represents a potential opportunity for EGDM to improve food rescue operations.
Figure 6: EGDM Donors and Recipients by Organization Type
Figure 6 displays graphs that show organization type. The data for these graphs is from the partner information and food transactions tables from EGDM. The top graph displays the total number of locations that correspond to each type of food organization. The number of locations for each type of organization are split up into three distinct colors. Green represents the number of locations that correspond to food donors of a given type, orange represents the number of locations that correspond to food recipients of a given type, and black represents the number of locations that correspond to organizations that are unknown as to whether they are food donors or recipients. Based on the results from this bar chart, religious organizations are the most common type of food recipient organization found in the greater Des Moines area; convenience stores are the most common type of food donor organization.
The bottom graph from Figure 6 displays the total pounds of food delivered or received based on each type of organization. The organizations are also categorized based on whether they are food donors (green), food recipients (orange), or of unknown status (black). The bottom graph has similar findings to the top graph, where it is clearly demonstrated that convenience stores donate the most amount of food and religious organizations receive the most amount of food. Therefore, we can infer from Figure 6 that to make the food delivery process timelier and more efficient, it may help for Eat Greater Des Moines to focus its outreach more towards convenience stores and religious organizations in the near future, since convenience stores are more likely to partner with Eat Greater Des Moines when it comes to donating food, especially to religious organizations who mainly tend to receive food to help out their communities.
Figure 7: USDA SNAP Retailers by Donation Status
Figure 7 displays donut charts of active vs. inactive retailers in Des Moines by the number of retailers and estimated pounds of food the retailers contributed, with green representing retailers actively donating to EGDM and orange representing inactive retailers. This data is from USDA SNAP retailers, and the food estimates (pounds) were created and provided by Eat Greater Des Moines. Our most significant finding was that most retailers are not partnered with organizations like EGDM, and more food is being wasted than saved. Roughly a third of excess retailer food in Des Moines is being donated while the rest is thrown out.
Figure 8: USDA SNAP Retailers Food Donation Estimates, by ZIP
Figure 8 displays maps of estimated food donation in pounds by zip code, with darker green indicating larger estimates for active retailers and darker orange representing larger estimates for inactive retailers. Data for these maps is from USDA SNAP retailers, and estimates were created by EGDM. We found that most retailers currently partnered with EGDM are located around West Des Moines and there is substantial opportunity for donor growth in central Des Moines. The opportunity can be seen by the darker colored ZIP code areas just north of downtown Des Moines. Targeting possible donors closer to the central Des Moines area may be an effective way to reduce transportation distances.
Team:
Adisak Sukul DSPG Leadership Team Associate Teaching Professor, Department of Computer Science Iowa State University |
Cass Dorius DSPG Leadership Team Associate Professor of Human Development and Family Studies Iowa State University |
Matthew Voss DSPG Graduate Fellow Master of Public Affairs University of Wisconsin-Madison |
Zack Johnson DSPG Undergraduate Intern Political Science and Statistics Iowa State University |
Ellie Uhrhammer DSPG Undergraduate Intern Math, Data Analytics, and Computer Science Drake University |
Saul Varshavsky DSPG Undergraduate Intern Data Analytics, Artificial Intelligence, and Computer Science Drake University |