Local-Food-Benchmarking
Data Science for the Public Good
The Data Science for the Public Good (DSPG) Young Scholars program is an immersive summer program that engages students from across Iowa to work together on projects that address local and state government challenges around critical social issues relevant in the world today. Learn more about the program here.
Wholesale Local Food Benchmarking
Background
The Iowa State Farm Food and Enterprise Development (FEED) is frequently asked for benchmarks on pricing of products both in retail and wholesale spaces. This occurs both within feasibility studies for new farm and food businesses and market assessments, as well as appropriate price points for selling to schools and early care sites, hospitals, universities, and other institutions. There was a need for additional data and research on the potential sales point for these wholesale products when many of our specialty crop producers across the state are operating in direct-to-consumer retail spaces. While data is available from the AMS and USDA (including the Agricultural Census), there is limited aggregation of sales for these products at the local level.
The **goal of the project was to develop a process that provides more localized and up to date information on regional food systems and prices around local products.** For the Final Presentation click here
Objectives
- Data Discovery: Investigate the possibility of scraping data and getting data from social media sites
- Use Google Trends to identify potential key products by terms being searched
- Narrowing down the exact products for analysis
- Compare values of collected data with USDA price data to determine if this helps in better decision making
- Identify potential aspects that could be automated for future data collection
- Investigate potential use of AI to identify impacts to local food markets. For example, weather impacts
Outcomes
- Used Google Trends to select the top five commodities (Apple, Strawberries, Pears, Tomatoes, Green Peppers)
- Compared the difference between USDA Vs local/Grocery/Food hubs price data
- End-to-end data automation process created for data collection (web scraping scheduler)
- Forecasted the prices and the most important variables affecting the prices using weather and economic factors
Workflow
1) Data Collection
- Web scraping from sites like Iowa Food Co-op/Walmart
- Physically attended Farmer’s Market to collect local food prices
- Contacted local Iowa farmers for the selling price information
2) Data Exploration
- Google hits by search for products over years
- Price change over years (example Apple)
- Price difference of organic Vs non-organic prices
- Negative correlation between search trends and prices (Low Price when Google Searches are High)
3) Finalizing the Top Commodities/Products for Analysis
4) Comparison: USDA Vs local/Grocery/Food Hubs Price
- Compare the USDA price with the prices from all the data sources like Walmart or Institution or Wheatsfield
Grower price - price the grower (ex- farmer) receives
Wholesale price - price received by the wholesaler if there is one between the grower and the retailer
Retail price - price received by the store or retail outlet
* Average price is based on sample data points
- Price trend over years for multiple data sources
- Where is your money going when you pay $1 for apple? Is the cost really $1?
5) Data Automation
- Scrape the product information using Python and Postman API for automation (Please refer the codes section for more details)
- Script to schedule the web scraping process to run at a certain time per day/week (For more detailed steps please refer the presentation here)
6) AI: Predicting Average Price of a Commodity
- Regression and XGBoost models used for prediction (Please refer the codes section for more details)
-
Computed average value of each variable at monthly level for years 2016 - 2021. For example: price of a commodity is calculated by taking average of each month for mentioned years
-
Indicators used:
1. Historical USDA retail price data of apples and tomatoes in the Midwest region (USDA Market News) 2. Historical precipitation and temperature data from ISU mesonet: https://mesonet.agron.iastate.edu/ 3. Drought data in Story County, Iowa (drought.gov) 4. 10 Year US Interest Rate (USO_US_Equity) 5. Gold Bullion historical price (GLD_US_equity) 6. Crude oil futures (WTI) 7. BCOM Index Bloomberg Commodity Index (BCOM_Index) - Top features by the model
- Actual Vs Predicted price graph
Discussion and Next Steps
- Further parameter tuning and adding more indicators to bring down the margin of error
- One key prospect in the future is to add weather data specific to where that commodity was grown, for example adding historical weather data from Washington or New York to find the correlation in how weather from those producer regions can directly impact the price of that commodity to the consumer
- Use computer vision algorithms to automate data collection process by extract prices from the pictures from farmer’s market
- Run python scheduler every week to collect the historical prices from websites
Data Sources
Data was obtained from various sources. The ‘Code Link’ column contains link to code that utilizes the data for cleaning/analysis.
| No. | Data Source | Website Link | Code Link | |
|---|---|---|---|---|
| 1 | Walmart - Fresh Fruits | Link | Link | |
| 2 | Walmart - Fresh Vegetables | Link | Link | |
| 3 | Hy-Vee - Fresh Fruits | Link | Link | |
| 4 | Hy-Vee - Fresh Vegetables | Link | Link | |
| 5 | Iowa Food Coop | Link | Link | |
| 6 | Park Slope Food Co-op | Link | - | |
| 7 | Prudent Produce - Fruit | Link | - | |
| 8 | Prudent Produce - Vegetable | Link | - | |
| 9 | USDA - AMS (Apple)* | Link | - | |
| 10 | USDA - AMS (Strawberries)* | Link | - | |
| 11 | USDA - AMS (Pears)* | Link | - | |
| 12 | USDA - AMS (Tomatoes)* | Link | - | |
| 13 | USDA - AMS (Green Pepper)* | Link | - | |
| 14 | Wheatsfield Co-op | Private | ||
| 15 | Sysco - Wholesale Restaurant Food Distributor | Private | ||
| 16 | Iowa State University(ISU) Dining | Private | ||
| 17 | Stock Indexes | Link | - | |
| 18 | Google Trends | Link | Link | |
| 19 | Iowa Environmental Mesonet | Link | - | |
| 20 | National Integrated Drought Information System | Link | - | |
| 21 | Farmers Market | Link | - |
* Retrieved report from 2016-2021
The indicators were computed using public datasets obtained from different sources/agencies. Number of available years and granularity of the data varied across sources. Data sources for few indicators were not identified, therefore the project team could not compute the corresponding indicators. Detailed information about sources used can be found here. [Private]
Code
Both data scraping and model building was implemented in Python. The google trends analysis was done in R. Corresponding code is available on:
- GitHub Repository
- For the Final Presentation click here [Private]
Other Documents
- https://www.agmrc.org/fruit-and-vegetable-market-data?StartDate=2022-03-18&EndDate=2022-03-25&page=1&items=25
- https://www.ers.usda.gov/publications/pub-details/?pubid=101785ohttps://www.ers.usda.gov/data-products/food-dollar-series/
- https://www.ers.usda.gov/data-products/food-consumption-and-nutrient-intakes/
- https://www.ers.usda.gov/data-products/arms-farm-financial-and-crop-production-practices/
- https://www.ers.usda.gov/publications/pub-details/?pubid=42807
Team
The team brought together backgrounds in Computer Science, Data Science, Economics, and Political Science, with interests in applications of technical skills to achieve meaningful impacts for decision making processes related to products at the local level.
- Adisak Sukul - Team Leader, Iowa State University, Computer Science
- Muskan Tantia - Graduate fellow, Iowa State University, Computer Science
- Maxwell Skinner - Intern, Iowa State University, Data Science & Political Science
- Nabil Idris - Intern, Iowa State University, Data Science
- Nayha Hussain - Intern, Clemson University, Computer Science and Economics