Tomato & Onion Mandi Price Analytics and Short-Horizon Forecasting for Karnataka (Agmarknet Data)

MandiLens is an applied data-science project on one of India's most consequential public datasets: the daily wholesale prices and arrivals of tomato and onion reported by APMC yards across Karnataka and published on Agmarknet, the Directorate of Marketing & Inspection's price portal. Tomato and onion are the crops whose price crashes and spikes dominate farm-distress headlines in the state — Kolar's tomato growers have repeatedly sold below cost while retail prices stayed high — and both have enough public daily data to support serious analysis.

The project builds a reproducible pipeline: an ingestion layer for Agmarknet price reports (data.gov.in mandi API as a secondary channel), a cleaning layer that harmonises varieties, checks ₹/quintal sanity, handles missing mandi-days and caps outliers, a warehouse (SQLite or MySQL) with date, market and commodity dimensions around a daily fact table, and an analytics layer covering decomposition, volatility, arrivals–price links and festival/monsoon effects. Two models are compared honestly — SARIMA and gradient boosting with lag, rolling and calendar features — under rolling-origin time-series cross-validation, never random k-fold, with RMSE, MAE and MAPE per market. Forecasts carry quantile risk bands (P10/P50/P90), and a rule raises a crash alert when the median forecast falls below a trailing-quantile threshold. A Streamlit dashboard shows history, forecast, bands and alerts per market, commodity and horizon.

It is scoped as a 16-week, two-member VTU MCA Project Phase-2 (22MCA44, Semester 4, 16 credits), with Phase-1 (22MCAL35) covering the literature survey, dataset acquisition and preliminary analysis.

Tomato and onion are Karnataka's most volatility-prone crops: within a single season, Kolar's tomato price can collapse below the cost of harvesting while retail prices in Bengaluru remain high, and onion can reverse the same way. The daily wholesale price and arrival of every APMC yard is published on Agmarknet, but as per-commodity report tables — there is no cleaned, continuous history for a market, no volatility summary, no validated forecast, and no alert before a crash or spike. A farmer or student who wants to understand or anticipate these movements must scrape tables, fix variety and unit noise by hand, and somehow decide which of the many possible models to trust.

The problem is to build a reproducible data-science pipeline and dashboard that turns Karnataka's public Agmarknet tomato and onion data into a curated warehouse, quantifies seasonality and volatility, compares a statistical and a machine-learning forecasting model under rolling-origin validation, expresses uncertainty with quantile bands, and raises threshold-based crash/spike alerts — with every modelling choice documented and defensible in the viva.

Abstract

Tomato and onion prices swing violently in Karnataka's APMC yards: Kolar's tomato auctions have repeatedly collapsed below cultivation cost, while Hubballi's onion market has seen equally sudden spikes. The Directorate of Marketing & Inspection publishes every yard's daily price and arrival on Agmarknet, yet it reaches farmers late, as tables, and nobody as a warning. This project builds MandiLens — an ingestion pipeline for Agmarknet prices, a clean analytical warehouse, a volatility study, and a validated two-model forecasting system with quantile bands and crash alerts in a Streamlit dashboard. It demonstrates the full data-science lifecycle on a real, messy, public dataset.

Introduction

India's horticulture markets punish the uninformed: a farmer seeing yesterday's ₹400/quintal at Kolar has no trend view across yards, no sense of arrivals pressure, and no quantitative warning before a crash. Researchers have studied mandi integration and price transmission, but a reproducible market-level forecasting exercise on Karnataka's tomato and onion data is rare — most coursework stops at plots. This project treats cleaning as a first-class phase, evaluates models the way forecasters should, and ships an interface where analysis is usable, not just displayed.

Existing system and its limitations

• Agmarknet reports are daily tables per commodity/market — excellent data, no analytics, no alerts.
• data.gov.in's mandi price API offers current-day prices in bulk, but only a rolling recent window, not a curated history.
• Commercial price dashboards exist for traders, are closed, and rarely reach farmers.
• Academic papers on Indian vegetable prices typically stop at one commodity, method or split; the validation discipline is what this adds.
• Farmers' own signals are local, not quantitative, and arrive after the price move.

Proposed system

• Ingestion: Agmarknet downloads per market/commodity, cross-checked with the data.gov.in API; raw files archived.
• Cleaning: variety harmonisation, ₹/quintal bounds, missing-day handling, IQR outlier capping — every rule logged as a data-quality metric.
• Warehouse: a star schema (dim_date, dim_market, dim_commodity, fact_price_daily) in SQLite/MySQL with weekly and monthly SQL views.
• Analytics: seasonal-trend decomposition, coefficient of variation by market, arrivals-vs-price lag correlation, festival/monsoon overlays.
• Modelling: SARIMA vs gradient boosting with lag, rolling-mean, arrivals and calendar features; rolling-origin CV; RMSE/MAE/MAPE and a seasonal-naive baseline no model may lose to.
• Risk layer: P10/P50/P90 bands; a crash alert when P50 falls below the trailing 90-day 25th percentile, spike alerts symmetrically.
• Dashboard: Streamlit pages for history, forecast, bands, alerts, metrics and a model card.

Feasibility study

• Technical: Python (22MCA31) and Data Analytics with Python (22MCA31/22MCAL36) cover pandas, statsmodels and scikit-learn; DBMS (22MCA21) covers the warehouse; all open-source, all on a laptop.
• Data: Agmarknet is public and the data.gov.in API is free with registration; only public aggregate data is used — no personal data, no authenticated scraping.
• Economic: ₹0 — public data, open-source stack, no paid APIs.
• Operational: the dashboard needs no installation for a viewer; the pipeline re-runs with one command.
• Schedule: fits the 16-week Phase-2 window, Phase-1 having completed in Semester 3.

In scope

• Tomato and onion daily mandi prices/arrivals for ≥ 5 Karnataka markets (e.g. Kolar, Chintamani, Mysuru, Hubballi, Bengaluru) over at least 3 years of Agmarknet history.
• Public government data only (Agmarknet, data.gov.in); no personal data, no authenticated scraping, no government ID numbers anywhere.
• Per market-commodity modelling: SARIMA, gradient boosting, seasonal-naive baseline; rolling-origin CV; RMSE/MAE/MAPE; P10/P50/P90 bands; crash/spike alert rules.
• SQLite default warehouse (MySQL optional for the DBMS course link), Streamlit dashboard, pytest suite, data-quality report.

Out of scope (v1)

• Real-time intra-day prices or farmer-level transaction data (not public).
• Multivariate models with weather/remote-sensing inputs (candidate future work; weather datasets differ in licensing).
• Price transmission/econometric modelling across states, farm-gate vs retail spread analysis, and crop-yield forecasting.
• A public web deployment with user accounts — the dashboard runs locally or on a lab machine.

The project follows CRISP-DM as its research design, adapted to the two-phase VTU MCA structure: Phase-1 (22MCAL35, Semester 3) covered business understanding, data understanding and a preliminary analysis; Phase-2 (22MCA44, Semester 4) completes deployment-grade build, evaluation and documentation across 16 weeks.

Team split: Member 1 owns ingestion, cleaning and the warehouse; Member 2 owns modelling, validation and the dashboard; both own the EDA and the report chapters of their parts and can each defend the full pipeline at the viva.

Validation discipline: rolling-origin (expanding-window) cross-validation with a fixed 7-day horizon — never random k-fold, which leaks the future into training. Every model must beat the seasonal-naive baseline to be reported; per-market metrics are shown, not just pooled averages, so a model that wins on one yard and loses on another is visible.

MandiLens is a pipeline-plus-dashboard architecture with an analytical warehouse at its centre:

1. Ingestion layer — Python scripts that download Agmarknet price reports per market/commodity into an immutable data/raw/ archive, with the data.gov.in mandi API as a secondary channel; each run writes a provenance record (source, date range, row counts).
2. Processing layer — pandas cleaning rules (variety harmonisation, unit bounds, outlier capping, missing-day policy) that produce tidy daily series and load the warehouse through SQLAlchemy.
3. Warehouse — SQLite by default (MySQL optional): dim_date, dim_market, dim_commodity and fact_price_daily, plus SQL views for weekly/monthly aggregates.
4. Modelling & risk layer — statsmodels SARIMA and scikit-learn HistGradientBoosting behind one common fit/forecast interface; a metrics module computes rolling-origin CV scores; a risk module derives quantile bands and alert states.
5. Presentation layer — a Streamlit dashboard (22MCA24's web layer applied to analytics) reading the same warehouse, plus a written model card.

flowchart TD
  A[Agmarknet reports] --> ING[Ingestion scripts]
  B[data.gov.in mandi API] --> ING
  ING --> RAW[(data/raw archive + provenance)]
  RAW --> CLN[pandas cleaning rules]
  CLN --> WH[(Warehouse: dim_date, dim_market, dim_commodity, fact_price_daily)]
  WH --> EDA[EDA: decomposition, volatility, arrivals vs price]
  WH --> MOD[Modelling: SARIMA vs gradient boosting]
  MOD --> CV[Rolling-origin cross-validation]
  MOD --> RISK[Quantile bands P10/P50/P90 + alert rules]
  WH --> DASH[Streamlit dashboard]
  CV --> DASH
  RISK --> DASH
  EDA --> DASH

Warehouse ER diagram

erDiagram
  DIM_DATE ||--o{ FACT_PRICE_DAILY : "keys"
  DIM_MARKET ||--o{ FACT_PRICE_DAILY : "keys"
  DIM_COMMODITY ||--o{ FACT_PRICE_DAILY : "keys"
  DIM_COMMODITY ||--o{ DIM_VARIETY : "has"
  DIM_VARIETY ||--o{ FACT_PRICE_DAILY : "keys"

  DIM_DATE {
    int dateKey PK
    date fullDate
    int month
    int week
    string festivalFlag
  }
  DIM_MARKET {
    int marketKey PK
    string marketName
    string district
    string state
  }
  DIM_COMMODITY {
    int commodityKey PK
    string commodity
    string group
  }
  DIM_VARIETY {
    int varietyKey PK
    int commodityKey FK
    string variety
  }
  FACT_PRICE_DAILY {
    int dateKey FK
    int marketKey FK
    int varietyKey FK
    decimal minPrice
    decimal maxPrice
    decimal modalPrice
    real arrivalsTonnes
  }

• Weather overlay — IMD gridded rainfall as a model feature for rain-shocked weeks, with proper lag handling.
• More commodities and states — potato, green chilli; Maharashtra and Andhra Pradesh yards for cross-state comparison.
• Price-transmission analysis — farm-gate vs retail spread and market-integration tests between yards.
• Alert delivery — scheduled digest via email/SMS through a free gateway, for a Krishi Vigyan Kendra or FPO pilot.
• Sequence models — an LSTM comparison row, honestly reported against the same rolling-origin protocol.