Onion and Tomato Mandi Price Analytics and Short-Term Forecasting from Agmarknet Data (Python, Power BI)

Mandi Price Analytics and Short-Term Forecasting is a data-analytics project built on Agmarknet, the Government of India portal that publishes daily wholesale prices and arrivals from APMC markets across the country. It turns that public data into something a small market committee or an FPO can actually use: a clean local price database, seasonality and volatility analysis, a one-week-ahead price forecast and dashboards that answer questions traders currently answer from memory.

The project tracks two crops with very different behaviour — onion (storable, prone to seasonal spikes and policy shocks such as export duties) and tomato (highly perishable, sharp gluts and crashes) — across six Karnataka markets: Bengaluru, Kolar, Hubballi, Mysuru, Chitradurga and Belagavi. Three years of daily records (arrivals in quintals; minimum, maximum and modal price) are downloaded as Agmarknet report files, cleaned with pandas, loaded into a MySQL staging database, and analysed.

Four forecasting methods of increasing sophistication — seasonal naive, three-week moving average, Holt's exponential smoothing and a SARIMA model from statsmodels — are compared by walk-forward validation using MAPE and RMSE, so the recommendation is earned by measurement rather than fashion. Power BI Desktop dashboards present price trends, a month-by-month seasonality heatmap, the arrivals-versus-price relationship and forecast-versus-actual charts, plus a two-page printable market bulletin.

The project maps directly onto the BCU Sem 5 Project (24BCAPJ) and the 24BCA52 Data Analytics and 24BCA63 Data Mining courses: real government data, honest cleaning decisions, statistical reasoning and a dashboard deliverable — exactly the combination examiners like to question.

Onion and tomato are the two crops whose wholesale prices hurt Indian consumers and growers most when they swing. The public data that could explain these swings already exists on Agmarknet, but it is published as one-day flat reports: local traders, FPO staff and students who want to understand price behaviour must download files day by day, and even then they have no clean history, no seasonal profile, no measure of volatility and no short-term forecast. Decisions — when to harvest and truck a tomato crop, whether to store onions — rest on anecdote.

There is a need for a reproducible analytics pipeline that converts public Agmarknet reports into a clean, queryable price database, measures how prices and arrivals behave across markets and months, tests which simple forecasting method actually predicts the next week best, and presents all of it in dashboards that a non-programmer can open and read.

Abstract

Wholesale prices of onion and tomato in Karnataka move sharply within a season, yet the people who trade in them mostly read yesterday's Agmarknet table without any historical view. This project builds a reproducible analytics pipeline over three years of Agmarknet daily records for six Karnataka APMC markets: it cleans and stores the data in MySQL, quantifies seasonality, volatility and the arrivals–price relationship, compares four short-term forecasting methods by walk-forward validation, and publishes Power BI dashboards and a fortnightly market bulletin. The best method is selected by measured MAPE and RMSE, not assumption.

Introduction

Agmarknet, run by the Directorate of Marketing & Inspection under the Ministry of Agriculture and Farmers Welfare, records daily arrivals (quintals) and prices (rupees per quintal) for thousands of mandis. The raw reports are flat tables meant for a single day's query: no history, no derived measures, no visual summary. An FPO in Kolar deciding whether to move its tomato crop this week or the next, or a trader in Hubballi planning onion storage, must mentally combine fragmented knowledge of festival demand, harvest waves and rain damage.

Existing system and gap

• Price information is consumed one day at a time; nobody in the local market keeps a structured multi-year history.
• Volatility is discussed anecdotally ("tomato crashed after Diwali last year") with no numbers behind it.
• Published forecasting studies usually model one market's series with heavy techniques, often on private or scraped datasets, and rarely ship a reproducible pipeline or a dashboard a non-programmer can open.
• Small FPOs and market committees cannot afford commercial commodity-analytics subscriptions.

Proposed system

• A cleaning pipeline in pandas that deduplicates records, removes rows where the modal price falls outside the min–max range, normalises units and leaves market-holiday gaps as explicit gaps rather than invented values.
• A MySQL staging database (markets, commodities, daily prices, forecast runs) that both the analysis scripts and Power BI query.
• Descriptive analytics: monthly seasonality profiles, coefficient of variation by market, arrivals-versus-price scatter and correlation, festival-window comparisons.
• Forecasting engine: seasonal naive, 3-week moving average, Holt's linear method and SARIMA, each validated walk-forward over the final 26 weeks; errors reported as MAPE and RMSE per market and crop.
• Power BI dashboards with drill-down from state to market, a seasonality heatmap, forecast-vs-actual charts and a printable two-page bulletin.

Feasibility

• Technical: Agmarknet report downloads need only a browser; pandas, statsmodels, MySQL Community Server and Power BI Desktop are free and run on an ordinary student laptop with 8 GB RAM.
• Economic: zero licence cost. All tools are open-source or free desktop editions; the only recurring input is internet time for the report downloads.
• Operational: the market committee or FPO sees a Power BI file, not code; refreshing it weekly is a five-minute job after the download step. The pipeline is documented so the next batch can extend it.

In scope

• Two commodities (onion, tomato) and six Karnataka markets (Bengaluru, Kolar, Hubballi, Mysuru, Chitradurga, Belagavi).
• Three years of daily wholesale records: arrivals in quintals, minimum, maximum and modal price in ₹/quintal, as published on Agmarknet.
• Daily and weekly aggregation views; one-week-ahead forecasts from four methods with walk-forward validation over the final 26 weeks.
• Power BI dashboards and a two-page printable bulletin; CSV export of cleaned data and forecast metrics.

Out of scope (v1)

• Retail prices, farm-gate prices and price transmission between wholesale and retail.
• Other states' markets and other commodities (the schema supports them, but data collection stays limited).
• Deep-learning models (LSTM), intraday or e-auction data, and any buy/sell recommendation — the project informs, it does not advise.

The team follows the Incremental (iterative) SDLC model: each increment ends with a demo to the guide, and feedback is folded into the next increment. This suits a 14-week Sem 5 timeline in which data availability — not coding difficulty — is the main risk, so the data pipeline is built first.

Testing approach: unit tests (pytest) assert the cleaning invariants — no duplicate market–commodity–date rows, modal price always within min–max, arrivals non-negative; a reproducibility dry-run rebuilds the database from raw CSVs on a teammate's laptop; forecast quality is judged only on the held-out walk-forward window, never on the training period.

The system is a batch analytics pipeline with four layers: acquisition, staging, analysis and presentation. Raw Agmarknet report files land in a versioned folder; a pandas ETL script cleans and loads them into MySQL; Python analysis scripts and the forecasting engine read from MySQL and write results back; Power BI connects to the same MySQL server for all visuals. Keeping MySQL at the centre means the dashboard never depends on a script running first.

flowchart LR
  A["Agmarknet report downloads (CSV)"] --> B["data/raw folder"]
  B --> C["pandas ETL: dedupe, sanity checks, unit normalisation"]
  C --> D[("MySQL: markets, commodities, daily_prices, forecast_runs")]
  D --> E["Exploratory analysis (pandas, Matplotlib)"]
  D --> F["Forecasting engine: seasonal naive, MA, Holt, SARIMA"]
  F --> G["metrics.csv + forecasts.csv"]
  G --> D
  D --> H["Power BI Desktop dashboards"]
  H --> I["Two-page market bulletin (PDF)"]

Staging database — ER diagram

erDiagram
  MARKET ||--o{ DAILY_PRICE : has
  COMMODITY ||--o{ DAILY_PRICE : "is priced as"
  DAILY_PRICE ||--o{ QUALITY_LOG : "passed or failed checks"
  DAILY_PRICE ||--o{ WEEKLY_PRICE : "aggregates into"
  COMMODITY ||--o{ FORECAST_RUN : "is forecast"
  MARKET ||--o{ FORECAST_RUN : "per market"

  MARKET {
    int market_id PK
    string name
    string district
  }
  COMMODITY {
    int commodity_id PK
    string name
    string group_name
  }
  DAILY_PRICE {
    int price_id PK
    int market_id FK
    int commodity_id FK
    date price_date
    decimal arrivals_quintal
    decimal min_price
    decimal max_price
    decimal modal_price
  }
  WEEKLY_PRICE {
    int week_id PK
    int market_id FK
    int commodity_id FK
    date week_start
    decimal avg_modal_price
    decimal total_arrivals
  }
  FORECAST_RUN {
    int run_id PK
    int market_id FK
    int commodity_id FK
    string method
    date forecast_week
    decimal predicted_price
    decimal actual_price
    decimal mape
    decimal rmse
  }
  QUALITY_LOG {
    int log_id PK
    int price_id FK
    string check_name
    string action
  }

A QUALITY_LOG table records every row the ETL dropped or corrected and why, which is the audit trail examiners ask about when they probe data cleaning. The pipeline is deliberately file-driven, so the whole study can be replayed offline on viva day with no internet dependency.

• Automated daily ingestion — a scheduled script that pulls the latest Agmarknet reports each morning so the dashboard is always current.
• More crops and states — the schema already supports commodity and market dimension growth; potato and green chilli are natural additions.
• Richer models — gradient-boosted regressors with arrival and festival features, and an LSTM comparison for the report's discussion chapter.
• Bulletin bot — the two-page bulletin pushed as a WhatsApp message to FPO members every fortnight.
• Price-transmission study — linking these wholesale series with retail CPI data to examine how much of a wholesale spike reaches the consumer.
• Multi-year backtesting service — a small web front end where a user picks a market, commodity and method and receives the backtest report.