Daily Outpatient Footfall Forecasting for a District Hospital OPD (Python, Keras, MySQL)

Daily Outpatient Footfall Forecaster is an advanced mini project that predicts tomorrow's outpatient (OPD) attendance for a large government hospital — department by department — and converts each forecast into a simple staff-and-counter rota suggestion.

The motivation is real and public: Rajiv Gandhi Government General Hospital in Chennai, one of India's busiest government hospitals, handles on the order of 12,000–15,000 outpatient visits every day, and hospitals across Tamil Nadu face the same pattern — Monday and post-holiday surges, a drop on Sundays, and visible crowding when the north-east monsoon drives seasonal illness. Hospital National Health Mission (HMIS) reporting already collects OPD attendance as routine indicators, yet the rota is still planned from monthly averages and staff experience. This project treats the OPD count as a multi-horizon time-series forecasting problem: it ingests three years of daily department-wise attendance (a bundled, realistic sample modelled on public reporting patterns, replaceable with a hospital's own HMIS extract), engineers calendar, holiday and rainfall features, and rigorously compares four approaches — a seasonal-naive baseline, SARIMA, Prophet and an LSTM sequence model built in Keras — using MAE, RMSE, MAPE and MASE under walk-forward backtesting.

A Flask dashboard presents per-department forecasts with prediction intervals and a rota table (counters and doctors to open per session). TensorFlow/Keras and Prophet are industry-standard extras beyond the CBCS syllabus; Python (120C1A), RDBMS (320C5B) and Web Technology (320C5C) carry the rest. Only aggregate daily counts are ever stored — no patient-level data.

Large government hospital OPDs plan tomorrow's staffing from yesterday's habits: monthly averages, intuition and firefighting. Attendance, however, follows strong weekly, festival and monsoon cycles that averages ignore, so a predicted Monday surge is staffed the same way as a slack mid-week day. The result is familiar — long queues and token scrambles on peak days, idle counters on others — in facilities where a single hospital can see five-figure daily footfall and every wasted counter is a lost consultation slot.

HMIS makes every facility report OPD attendance, yet that data is never turned into an operational forecast for the facility itself. What is needed is a measured, department-level forecasting system that (a) works from the aggregate daily counts hospitals already produce, (b) tests statistical and sequence models against an honest seasonal-naive baseline with walk-forward validation, (c) translates the forecast into a concrete rota suggestion within administrator-set capacity limits, and (d) stores no patient-identifying information whatsoever.

Abstract

This project builds and rigorously evaluates a forecasting service for daily outpatient attendance in a district hospital. Four models — seasonal naive, SARIMA, Prophet and a Keras LSTM — are compared on three years of daily, department-wise OPD counts with walk-forward backtesting, using MAE, RMSE, MAPE and MASE against the seasonal-naive baseline. The winning model per department drives a Flask dashboard showing a 7-day-ahead forecast with intervals and a session-wise rota suggestion. The full pipeline — MySQL storage, feature builder, model registry, dashboard — is reproducible on a student laptop.

Introduction

Government hospital OPDs in Tamil Nadu run at enormous scale — Rajiv Gandhi Government General Hospital alone reports on the order of twelve to fifteen thousand outpatient visits daily — and attendance is predictable in shape: weekday peaks, Sunday troughs, festival dips and monsoon-season surges of fever cases. Because the shape is regular, forecasting is useful: an extra counter on a predicted Monday surge shortens queues, while over-staffing a Sunday wastes scarce doctors' time. The National Health Mission's HMIS already mandates OPD attendance reporting, so the raw signal exists in every facility; what is missing is a forecast layer on top of it.

Existing System

• Rotas are planned from last month's averages and senior staff intuition; surges are handled reactively by pulling in duty staff.
• Token and queue-management pilots (RGGGH trialled one in 2024) manage the crowd after patients arrive, but do not inform tomorrow's staffing.
• HMIS reports OPD attendance monthly for programme monitoring — retrospective, aggregated for administration, never fed back to the facility as an operational forecast.
• Limitations: no department-level forward view, no holiday or monsoon awareness, no measurable accuracy, and knowledge leaves with the staff who hold it.

Proposed System

• A data layer in MySQL holding daily department-wise attendance, a Tamil Nadu holiday calendar, and rainfall series; the bundled dataset is a documented three-year sample generated to match published reporting patterns, with a loader for a real HMIS extract.
• A feature builder producing lags, rolling means, day-of-week and month encodings, holiday flags (including festivals that shift demand) and monsoon indicators.
• Four-model comparison per department with walk-forward backtesting and MASE scores relative to seasonal naive — no model is adopted without beating the baseline.
• A Keras LSTM trained on sliding windows with early stopping and dropout, alongside SARIMA and Prophet as statistical references.
• A Flask dashboard with forecast charts, intervals, and a rota table translating a predicted surge into counters and doctors per session, within administrator-editable capacity limits.

Feasibility Study

• Technical: all libraries are free; the LSTM trains in minutes on CPU at this data size; MySQL and Flask are already in the CBCS syllabus.
• Economic: zero licence cost; runs on a college laptop with 8 GB RAM, no GPU required.
• Operational: the dashboard needs no training; aggregate counts avoid any patient-privacy surface, so a hospital can pilot it without ethics clearance for records access.

In scope

• One facility, OPD departments (General Medicine, Paediatrics, Surgery, ENT, Ophthalmology and so on), daily granularity.
• Horizons of 1–7 days ahead with prediction intervals.
• Three years of bundled sample data matching public reporting patterns, plus a documented loader for a real HMIS-format extract.
• Four-model comparison with walk-forward backtesting and a model registry in MySQL.
• Rota suggestion logic with editable capacity parameters; Flask dashboard with charts.

Out of scope (v1)

• Inpatient, emergency and admission forecasting; bed-occupancy management.
• Patient-level records, triage or queue-token systems — only aggregate daily counts exist in this system.
• Hourly intra-day forecasting and biometric-gate integration.
• Multi-facility cloud deployment and automated roster approval workflows.
• Causal modelling of disease outbreaks — rainfall and holidays are exogenous features, not epidemiological models.

The project uses an Incremental (evolutionary prototyping) SDLC model: a baseline-forecast pipeline exists by the end of week 2, and each later increment adds a model class or a layer of the application, always leaving the system runnable. With six weeks and two members, this is the only discipline that survives contact with a real deadline.

Team division: Member A owns the data layer, statistical models and evaluation; Member B owns the LSTM, dashboard and rota advisor; both share the report and demo.

Testing approach: unit tests for calendar features (holiday boundaries, festival shifts) and rota capacity arithmetic; integration tests for ETL idempotency; and walk-forward backtesting as the final acceptance test, with a documented failure analysis of the worst-predicted weeks (typically deepavali-week dips and monsoon fever spikes).

The system is a layered analytics architecture: a data layer, a modelling layer and a presentation layer, each independently testable.

1. Data layer (MySQL) — daily attendance per department, the Tamil Nadu holiday calendar, daily rainfall, a model registry, generated forecasts and evaluation metrics. Views expose the aggregates the dashboard needs.
2. Modelling layer (Python) — a feature builder feeds two parallel tracks: statistical models (SARIMA via statsmodels, Prophet) and a Keras LSTM over sliding windows. Every experiment writes its hyper-parameters and metrics to the model registry, and the rota advisor reads only registry-flagged best models.
3. Presentation layer (Flask) — server-rendered pages with Chart.js graphs: forecast per department with intervals, backtest overlays, and the editable rota table.

flowchart TD
  SRC["HMIS-format CSV / bundled sample"] -->|"idempotent load"| ETL["ETL job — pandas"]
  ETL --> DB[("MySQL: visits, holidays, rainfall")]
  DB --> FB["Feature builder — lags, rolling means, calendar"]
  FB --> SM["SARIMA + Prophet"]
  FB --> LSTM["Keras LSTM sequence model"]
  SM -->|"metrics"| REG["Model registry + evaluation tables"]
  LSTM -->|"metrics"| REG
  REG -->|"best per department"| RA["Rota advisor"]
  SM -->|"7-day forecast + intervals"| DB
  LSTM -->|"7-day forecast + intervals"| DB
  DB --> UI["Flask dashboard + Chart.js"]
  RA --> UI
  UI --> A(["Hospital administrator"])
  UI --> M(["Guide / review panel"])

ER diagram

erDiagram
  DEPARTMENT ||--o{ OPD_VISIT : records
  OPD_VISIT }o--|| CALENDAR_DATE : "falls on"
  MODEL ||--o{ FORECAST : generates
  DEPARTMENT ||--o{ FORECAST : receives
  MODEL ||--o{ EVALUATION : "is scored by"
  FORECAST ||--o{ ROTA_SLOT : "drives"

  DEPARTMENT {
    int dept_id PK
    string dept_name UK
    string category
  }
  OPD_VISIT {
    int visit_id PK
    int dept_id FK
    date visit_date
    int patient_count
    string data_source
  }
  CALENDAR_DATE {
    date cal_date PK
    int day_of_week
    boolean is_holiday
    string holiday_name
    boolean is_monsoon
    float rainfall_mm
  }
  MODEL {
    int model_id PK
    string model_name
    string algorithm
    string hyperparameters
    boolean is_best_for_dept
  }
  FORECAST {
    int forecast_id PK
    int dept_id FK
    int model_id FK
    date target_date
    int predicted_count
    int interval_low
    int interval_high
  }
  EVALUATION {
    int eval_id PK
    int model_id FK
    decimal mae
    decimal rmse
    decimal mape
    decimal mase
  }
  ROTA_SLOT {
    int slot_id PK
    int forecast_id FK
    string session
    int counters_open
    int doctors_assigned
  }

Key design decisions: the calendar table carries holidays, festivals and rainfall so that every model consumes identical exogenous features; the model registry stores hyper-parameters with metrics, making the four-model comparison reproducible in the viva; and forecasts are kept per model rather than overwritten, so the dashboard can show why a model was chosen and how alternatives performed on the same weeks.

• Intra-day forecasting on hourly registration data to drive afternoon-session staffing, not just daily planning.
• Admission and bed-demand extension linking OPD volume to next-day inpatient occupancy for the same departments.
• Multivariate deep models — a temporal fusion or transformer-style architecture consuming weather forecasts and epidemic alerts as future covariates.
• Multi-facility deployment with per-facility model registries and a state-level rollup for NHM-style programme monitoring.
• Anomaly overlay — flagging days when actual attendance departs sharply from the forecast band, as an early outbreak signal for the facility.