A natural-language analytics tool that lets anyone query Indian government crop production data using plain English — no SQL, no code, no spreadsheets.
Governments publish enormous amounts of structured open data — crop yields, production statistics, district-wise breakdowns — that contain precise, actionable answers to real questions. The problem is not the availability of this data. It is accessibility. The data lives in CSV files and government portals that require technical knowledge to query. A farmer, a policy analyst, or a state official who wants to know "how did paddy production in my state change over the last five years?" has no practical way to get that answer without opening a spreadsheet and writing formulas.
This tool closes that gap. It sits on top of the Indian Crop Production Statistics dataset published by the Ministry of Agriculture and Farmers Welfare on data.gov.in and allows any user to ask questions in plain English and receive back a correct, data-backed answer — a specific number or trend, a chart, and a transparent explanation of exactly how the answer was computed.
The core design principle is trust. Every figure returned by the tool is computed directly from the dataset using pandas. The language model is never asked to recall or estimate a number from memory. If a question cannot be answered from the available data, the tool says so clearly rather than fabricating a response.
Source: Indian Crop Production Statistics — Ministry of Agriculture and Farmers Welfare
Portal: data.gov.in
Coverage: 246,000+ rows across 33 states, 707 districts, 55 crops, and 6 agricultural seasons spanning 1997 to 2015
Columns: State_Name, District_Name, Crop_Year, Season, Crop, Area (hectares), Production (tonnes)
User Question (plain English)
↓
[ Gemma 4 31B ] ←── Schema context (column names, valid values)
↓
JSON Operation Spec (e.g. top_n / trend_over_time / compare)
↓
[ Pandas Executor ] ←── Runs against real CSV data
↓
Result DataFrame
↓
[ Chart Generator ] [ Answer Builder ]
↓ ↓
PNG Chart (filepath) Natural language sentence
↓ ↓
[ Gradio UI ]
↓
Answer + Chart + Provenance
The most important architectural choice in this tool is how the language model interacts with the data. There are two approaches:
Approach A — Code generation: Ask the model to write Python/pandas code and run it with exec(). This is flexible but dangerous — the model could emit os.system(), file reads, or arbitrary code.
Approach B — Structured query (what we use): Constrain the model to return a JSON operation spec describing what to compute, and let trusted Python code execute it. The model never touches the data directly.
We use Approach B. The model receives the question and a schema summary (column names, valid state/crop names, year range) and returns a JSON object like:
{
"operation": "top_n",
"filters": { "Crop": "Rice", "Crop_Year": 2011 },
"groupby": "State_Name",
"metric": "Production",
"agg": "sum",
"n": 5,
"sort": "desc"
}Trusted Python code then runs this against the DataFrame. Every number in the answer traces back to a pandas computation, not to the model's memory.
| Module | Responsibility |
|---|---|
data_loader |
Downloads the CSV, normalizes column names, coerces types, drops rows with missing production values |
schema_builder |
Builds a plain-text summary of the dataset (all states, top crops, year range) injected into every LLM prompt |
executor |
Receives the JSON op spec and runs the actual pandas computation — filtering, grouping, aggregating |
chart_generator |
Renders a matplotlib chart (line for trends, horizontal bar for comparisons) saved as a file to avoid thread-crossing issues |
llm_client |
Handles the single Gemma 4 API call that converts the question into a JSON spec |
pipeline |
Orchestrates all modules in order and returns a consistent (answer, chart_path, provenance) tuple |
ui |
Gradio interface that wires the pipeline to text and image output components |
evaluator |
Runs 8 pre-written questions through the pipeline and prints a pass/fail report |
- top_n — "Which state produced the most wheat in 2015?"
- trend_over_time — "How has sugarcane production in Maharashtra changed over the years?"
- compare — "Compare maize production between Rajasthan, Karnataka and Maharashtra"
- groupby_agg — "Which crop had the highest total production in India in 2012?"
- out_of_scope — "What was India's GDP in 2020?" → Refused clearly
Every answer includes a provenance block showing the exact JSON spec that ran and the steps executed. Any answer can be independently verified by re-running the spec against the raw CSV.
| Component | Choice | Reason |
|---|---|---|
| LLM | Gemma 4 31B via Google AI Studio | Free tier, strong structured output, 256K context |
| Data layer | Pandas | Single CSV fits in memory, sufficient for this scale |
| Chart rendering | Matplotlib (FigureCanvasAgg) | Thread-safe rendering without global pyplot state |
| UI | Gradio | One-line public link, no infrastructure required |
| Runtime | Google Colab | Free GPU/CPU, shareable notebook format |
- Google Colab account
- Free API key from Google AI Studio
- Crop Production CSV from data.gov.in
1. Open the notebook in Google Colab
2. Add your API key to Colab Secrets (🔑 icon in the left sidebar):
Name : GEMINI_API_KEY
Value : your_key_here
3. Upload crop_production.csv when prompted by the data loader cell
4. Run all cells top to bottom (Runtime → Run all)
5. Open the public Gradio URL printed in the UI cell output
✅ Which state produced the most rice in 2011?
✅ How has wheat production in Punjab changed over the years?
✅ What was total sugarcane production in Maharashtra in 2010?
✅ Which 5 states grew the most cotton in 2013?
✅ Compare maize production between Rajasthan, Karnataka and Maharashtra
✅ Which crop had the highest total production in India in 2012?
❌ What was India's GDP growth rate in 2020? → Refused (out of scope)
❌ What is the average annual rainfall in Punjab? → Refused (out of scope)
The current build is locked to a single CSV. A natural extension is to support multiple government datasets simultaneously — crop production, mandi prices, soil health, weather data — and let the model decide which dataset a question requires before constructing the query. This would involve a schema registry and a routing step before the operation planning step.
The first scaling bottleneck is memory. Pandas loads the entire CSV into RAM, which works for 246,000 rows but fails at millions. Replacing the data layer with DuckDB would allow the tool to query Parquet files on disk without loading them entirely, handling datasets orders of magnitude larger without changing the query interface.
Currently, filter matching is case-insensitive but exact. A user typing "Andhra" will not match "Andhra Pradesh", and "paddy" will not match "Rice". Adding a fuzzy matching layer using string similarity or a small lookup dictionary would dramatically improve robustness for non-technical users who may not know the exact names in the dataset.
When an operation returns zero rows — usually because the model used a slightly wrong filter value — the current tool returns a "no data found" message. A more capable version would feed the empty result back to the model and ask it to revise the filter values and retry, with a maximum of two attempts. This self-correction loop would handle edge cases silently and improve the user experience significantly.
The model currently either answers or refuses. A middle ground — routing low-confidence answers to a "needs human review" state — would be valuable in a real government deployment where an uncertain answer is worse than no answer. Confidence could be estimated by checking whether filter values exactly match known values in the schema.
The current build sends schema metadata to Google's API. For deployment within a government ministry, the model would need to run on-premises so that no data — even metadata — leaves the government network. Gemma 4's open weights make this feasible. The API call in llm_client would be replaced with a local inference endpoint with no architectural changes required elsewhere.
A production deployment would require logging every query, the resolved JSON spec, the row count post-filtering, and the final answer with a timestamp and user ID. Row-level security would ensure a district officer can only query data for their own district. Both of these are data layer concerns that sit below the current pipeline and can be added without modifying the LLM or UI layers.
Since the tool is designed for non-technical users, the next natural interface improvement is voice. Adding a speech-to-text step before the LLM call would allow users to ask questions verbally, making the tool accessible to users who are not comfortable typing queries in English.
- Supports 5 operation types — complex multi-step or join-requiring questions are not handled
- No fuzzy name matching — exact Title Case spelling required for filters
- One API call per question — no caching, repeated questions re-call the API
- Year range limited to 1997–2015 — questions about more recent data will be refused
- Provenance is a JSON spec — non-technical users may not find it fully interpretable
Dataset: Open Government Data (OGD) Platform India — data.gov.in
Model: Gemma 4 — Apache 2.0 License