Mandi Price Forecasting & Selling-Day Advisory for APMC Markets (Python/ML)

Mandi Price Forecasting & Selling-Day Advisory is an applied data-analytics application built for the way farmers and small traders actually sell perishable produce in India. Prices of onion, tomato and potato in APMC (Agricultural Produce Market Committee) mandis swing widely day to day, yet most growers still decide when and where to sell on hearsay, agent advice or yesterday's rate board. This project turns the free daily wholesale price data published on Agmarknet — the Government of India's agricultural marketing portal — and its data.gov.in mirror into a practical decision aid: a seven-day forecast of the modal wholesale price for each commodity–mandi pair, and a comparison of nearby mandis paying more on the same day.

The pipeline fetches daily prices (minimum, maximum, modal and arrivals), cleans and stores them in MySQL, and builds lag, rolling-mean and calendar features. Four forecasting approaches are trained and compared — naive and moving-average baselines, SARIMA (statsmodels), gradient-boosted trees (scikit-learn) and an LSTM network (Keras) — and evaluated with time-ordered backtests using MAE, RMSE and MAPE so that no future information leaks into training. An advisory engine converts forecasts into sell today / hold suggestions and a best-mandi recommendation, presented through a Flask dashboard with Matplotlib and Chart.js charts.

Aligned to the VTU MCA 2022 scheme's Project Phase-2 (22MCA44), the project exercises Python Programming and Data Analytics with Python (22MCA31/22MCAL36), DBMS (22MCA21), Artificial Intelligence (22MCA262) and Deep Learning (22MCA411); scikit-learn, statsmodels, Keras and Flask are industry-standard Python libraries used alongside the syllabus tools.

Farmers selling perishable produce in Indian APMC mandis face high day-to-day price volatility with almost no decision support. They typically sell on the day the produce is ready, at the nearest mandi, guided by the previous day's rate board or an agent's word. Selling two or three days earlier or later, or trucking the load to a mandi twenty kilometres away, often changes the realisation by a large margin — but the farmer has no way to quantify this. Existing portals publish current prices but provide no forecast, no comparison across mandis and no advice, so the information asymmetry stays with the trader.

There is a need for a forecasting and advisory system built entirely on public government data that (a) predicts the next week's wholesale price per commodity and mandi with honest, time-ordered accuracy measures, (b) compares nearby mandis net of transport cost, and (c) presents a conservative, explainable sell/hold recommendation that a non-technical user can act on — without promising guaranteed prices.

Abstract

This project builds a data-driven advisory system for farmers and traders selling perishable produce in Indian APMC mandis. It forecasts the next seven days of wholesale modal prices for selected commodity–mandi pairs from the public Agmarknet dataset, compares nearby mandis, and recommends whether to sell now or hold, and where. Four model families — naive baselines, SARIMA, gradient-boosted trees and an LSTM — are compared with strict time-ordered validation and served through a Flask dashboard over MySQL.

Introduction

India's farm trade runs through thousands of regulated APMC mandis that record daily arrivals and prices. For volatile commodities such as onion and tomato, selling on a good day rather than a bad one — or at a nearby mandi instead of the nearest one — can decide whether a season is profitable. Small growers lack analytical tools: they sell when the produce is ready, not when the price is right. Meanwhile the Government of India publishes every mandi's daily prices openly on Agmarknet — real, large, messy public data.

Existing System and Literature Gap

• Traders and farmers rely on the mandi rate board, phone calls and commission agents — none of whom is accountable for a wrong call.
• The eNAM portal and Agmarknet publish current and historical prices but stop at displaying data; nothing tells a farmer what next week holds.
• Published research on Indian vegetable price forecasting usually stops at accuracy tables for one series, rarely packaged with cross-mandi comparison, an explainable rule and a usable interface.
• Gap: a reproducible system combining per-mandi forecasting, cross-mandi comparison and a conservative sell/hold rule on fully public data.

Proposed System

• A scheduled fetcher downloads daily prices from the data.gov.in Agmarknet feed into a normalised MySQL schema.
• A feature builder creates lag, 7/30-day rolling-mean and month/season features per commodity–mandi series.
• A model suite trains naive, 7-day moving-average, SARIMA, gradient-boosting and LSTM models; a walk-forward backtest with time-ordered splits selects the best per series and reports MAE, RMSE and MAPE against the naive baseline.
• An advisory engine applies a conservative rule — sell when the coming days' forecast stays within a small band of today's price and volatility is low, otherwise hold with a confidence note — and ranks nearby mandis by predicted price minus transport cost.
• A Flask dashboard shows price history, forecasts with error bands, model comparison and the advisory, with CSV export.

Feasibility Study

• Technical: The dataset is public and free (Agmarknet via data.gov.in; CSV bulk files also exist). All libraries are open-source and run on an 8 GB RAM laptop; LSTM training takes minutes, not hours.
• Economic: Zero licence cost; hosting on a free tier or a college server.
• Operational: Farmers reach the dashboard on a phone through a lightweight, large-font page; the advisory is a sentence, not a chart. The backtest protocol allows evaluation at the college itself, with a local Krishi Vigyan Kendra available for feedback.

In scope

• Commodities: onion, tomato, potato; mandis from two or three districts of one state (e.g. Kolar, Bengaluru and Hubballi in Karnataka) so cross-mandi comparison stays meaningful.
• Daily wholesale (modal) price and arrival volume; 7-day forecast horizon; retraining by command, scheduled weekly.
• Advisory for the coming days with a confidence note, plus a mandi ranking table.
• Role-based access: public viewer (read-only) and admin (data fetch, retrain, advisory configuration).

Out of scope (v1)

• Retail prices, futures prices and farm-gate negotiations.
• Payment, trading or e-auction features; the system advises, it does not transact.
• Weather-driven models (rainfall covariates are listed under future scope).
• Native mobile apps and SMS delivery; the dashboard is a responsive web page.

The project follows an incremental SDLC model mapped onto CRISP-DM (Cross-Industry Standard Process for Data Mining): business understanding, data understanding, data preparation, modelling, evaluation and deployment are iterated three times, with a working pipeline demo at the end of every iteration. This suits a forecasting project because model quality is discovered only by iterating on real data.

Testing approach: unit tests (pytest) for the cleaner, feature builder and advisory rules with fixture data; statistical validation through walk-forward backtesting (never random splits, which leak the future); acceptance checks that every dashboard number can be traced to a stored forecast run.

The system is a pipeline of small Python stages around a MySQL store, served by a Flask application.

1. Acquisition layer — a scheduled fetch job downloads daily mandi prices from the data.gov.in API (Agmarknet feed) with a CSV bulk-file fallback, and stages raw responses.
2. Preparation layer — the cleaner normalises units, removes duplicate market-day rows and imputes or flags missing days; the feature builder writes lag, rolling-mean and calendar features per commodity–mandi series.
3. Modelling layer — baseline, SARIMA, gradient-boosting and LSTM trainers write forecasts and per-run metrics into the database, so every dashboard number is reproducible from a stored run.
4. Serving layer — a Flask app exposes the dashboard, advisory endpoint and CSV export; SQLAlchemy handles queries and the HTML/Chart.js front end stays stateless.

flowchart TD
  SRC[("Agmarknet via data.gov.in")] --> FET["Fetch job: API with CSV fallback"]
  FET --> CLN["Cleaner: dedupe, units, missing days"]
  CLN --> DB[("MySQL: commodities, mandis, daily_prices")]
  DB --> FE["Feature builder: lags, rolling means, calendar"]
  FE --> M1["Baselines: naive, 7-day moving average"]
  FE --> M2["SARIMA (statsmodels)"]
  FE --> M3["Gradient boosting (scikit-learn)"]
  FE --> M4["LSTM (Keras)"]
  M1 --> EV["Walk-forward backtest: MAE / RMSE / MAPE"]
  M2 --> EV
  M3 --> EV
  M4 --> EV
  EV --> ADV["Advisory engine: sell or hold + mandi ranking"]
  ADV --> APP["Flask dashboard + CSV export"]
  APP --> USR["Browser: farmer, trader, evaluator"]

Database design (ER)

erDiagram
  COMMODITY ||--o{ DAILY_PRICE : has
  MANDI ||--o{ DAILY_PRICE : reports
  COMMODITY ||--o{ FORECAST : has
  MANDI ||--o{ FORECAST : has
  FORECAST ||--o{ ADVISORY : drives

  COMMODITY {
    int commodity_id PK
    string name UK
    string variety
    string unit
  }
  MANDI {
    int mandi_id PK
    string name
    string district
    string state
  }
  DAILY_PRICE {
    int id PK
    int commodity_id FK
    int mandi_id FK
    date price_date
    decimal min_price
    decimal max_price
    decimal modal_price
    decimal arrivals_tonnes
  }
  FORECAST {
    int run_id PK
    int commodity_id FK
    int mandi_id FK
    date target_date
    string model_name
    decimal predicted_price
    decimal mae
    decimal mape
  }
  ADVISORY {
    int id PK
    int commodity_id FK
    date generated_on
    string recommendation
    string best_mandi
    string confidence_note
  }

Data flow (DFD Level 1)

flowchart TD
  ADM([Admin]) --> P1["1.0 Fetch and clean prices"]
  P1 --> D1[("D1 daily_prices")]
  D1 --> P2["2.0 Build features and train models"]
  P2 --> D2[("D2 forecast runs")]
  D2 --> P3["3.0 Generate advisory"]
  P3 --> D3[("D3 advisories")]
  USR([Farmer / trader]) --> P4["4.0 View dashboard"]
  D3 --> P4
  P4 -- "forecast, mandi ranking, advisory" --> USR

• Add IMD rainfall and festival-calendar covariates, which are strong known seasonal drivers of vegetable prices.
• Extend to more states and to eNAM data, and publish the advisory as a simple SMS or missed-call service for feature-phone users.
• Weekly automated retraining with a job scheduler and model-drift alerts when MAPE degrades.
• Prediction intervals instead of point forecasts, so the advisory can quote a price band.
• A small trader module tracking actual realisations against advisory calls to measure real-world usefulness.