1→ Hospital Data Pipeline
A Python batch data pipeline that processes hospital data through Bronze → Silver → Gold layers
and generates visualizations.
Pipeline Architecture
Bronze Layer (Raw Ingestion)
• Reads source files from /data directory
• Stores exact raw copies as CSV files in /bronze
• No transformations applied
• Files: [Link], [Link], [Link]
Silver Layer (Cleaning & Standardization)
• Clean Vitals: Renames patientId to patient_id, converts UNIX timestamps to datetime,
ensures numeric types for hr, ox, sys, dia
• Clean Labs: Renames columns (patientId → patient_id, test → lab_test, value → lab_value),
converts timestamps, ensures numeric lab values
• Patient Master Table: Combines EHR data with latest vitals and latest lab results per test for
each patient
• Files: clean_vitals.csv, clean_labs.csv, patient_master.csv
Gold Layer (Anomaly Detection)
Detects anomalies based on:
• High Heart Rate: HR > 120 bpm
• Low Oxygen: OX < 92%
• High Blood Pressure: SYS > 160 OR DIA > 100 mmHg
• File: [Link]
Data Cleaning Details
Latest Record Selection
• Vitals: Sorted by timestamp, grouped by patient_id, selected last record
• Labs: Sorted by timestamp, grouped by patient_id and lab_test, selected last record per test
Type Conversions
• UNIX timestamps converted to pandas datetime
• All vital signs and lab values converted to numeric with error handling
Visualizations
1. Heart Rate Trend (hr_trend.png): Multi-line plot showing heart rate over time for all patients
2. Oxygen Distribution (oxygen_distribution.png): Histogram with threshold line at 92% 3.
Anomaly Counts (anomaly_counts.png): Bar chart showing count of each anomaly type How
to Run
python [Link]
Re-runnable Pipeline Behavior
The pipeline is fully idempotent and re-runnable:
• Always reads fresh input files from /data
• Overwrites all outputs in bronze, silver, and gold layers
• Regenerates all visualizations
• No caching or state persistence
Adding New Data: Simply add rows to input files ([Link], [Link], [Link]) and re-run python
[Link]. All outputs will automatically reflect the new data.
Tech Stack
• Python
• pandas
• numpy
• matplotlib
• json
• pathlib
Project Structure
project/
├── data/ # Input files
├── bronze/ # Raw ingested data
├── silver/ # Cleaned and standardized data
├── gold/ # Anomaly detection results
├── visualizations/ # Generated plots
├── src/ # Source code modules
│ ├── [Link]
│ ├── [Link]
│ ├── [Link]
│ ├── [Link]
│ └── [Link]
├── [Link] # Pipeline orchestration
└── [Link]
Execution Flow
1. Setup: Create required folders
2. Bronze: Ingest raw data
3. Silver: Clean and standardize data, create patient master
4. Gold: Detect anomalies
5. Visualization: Generate plots
Each step prints a log message indicating completion.
2→ RetailPulse — Retail Analytics & Loyalty Engine
A complete data pipeline for retail loyalty analytics. Raw CSV files go in, validated clean data comes
out, and we generate business insights about customer loyalty, RFM segmentation, and predictive
analytics along the way.
What This Project Does
RetailPulse transforms messy retail data into actionable business
intelligence: • Ingests raw sales data from CSV files
• Validates and cleans data with detailed error tracking
• Calculates customer loyalty points based on business rules
• Segments customers using RFM (Recency, Frequency, Monetary) analysis •
Predicts customer spend, inventory restocking needs, and promotion sensitivity •
Visualizes insights through interactive and static dashboards
No external databases or ML libraries required. 100% logic-based analytics using SQLite.
The Problem We're Solving
Retail stores collect lots of data — sales transactions, customer info, product details, loyalty
programs. But this data is:
• Spread across different CSV files
• Full of errors and inconsistencies (missing values, bad formats, special characters)
• Hard to analyze in its raw form
• Growing every day
Store managers need a system that can:
• Clean and validate data automatically
• Track what's wrong with bad data (not just delete it)
• Calculate loyalty points and customer segments
• Generate predictive insights for inventory and marketing
• Show everything in easy-to-understand dashboards
How We Built It
The Pipeline (5 Scripts)
We use a sequential pipeline where each script has one clear job:
Script Layer Purpose
01_setup_db.py Schema Creates all database tables (core + rejected + analytics)
02_etl_pipeline.py ETL Validates, cleans, and loads data; rejects bad records
03_loyalty_rfm.py Analytics Calculates loyalty points and RFM customer segments
04_predictive.py Predictions Forecasts spend, restock flags, promotion sensitivity
05_dashboard.py Visualization Generates charts and dashboards
Data Flow
CSV Files → ETL Validation → Clean Tables → Analytics → Predictions →
Dashboard ↓
Rejected Tables
(with reasons)
What Makes This Different
Smart Data Quality Handling
Most pipelines either accept everything (garbage in, garbage out) or reject everything (one bad
record breaks the whole job). We do it smarter:
• Bad records go to *_rejected tables with specific error reasons
• You can see exactly what's wrong with each record
• Clean data keeps flowing
• You can fix and reprocess rejected records later
Real Analytics, Not Just Counts
The analytics layer calculates meaningful business metrics:
• Loyalty Points Engine: Automatic points calculation based on configurable rules •
RFM Segmentation: Identifies high-value and at-risk customers • Spend
Forecasting: Predicts next month spend using 3-month moving averages •
Promotion Sensitivity: Classifies customers as HIGH/MEDIUM/LOW responders •
Restock Predictions: Flags products likely to run out of stock
Tech Stack
Component Technology
Language Python 3.8+
Database SQLite3 (zero config)
Data Processing pandas
Visualization matplotlib, Streamlit, Plotly
Testing pytest (96 unit tests)
Error Handling Custom exception hierarchy with logging
Project Structure
RetailPulse/
├── data/
│ ├── raw/ # Source CSV files
│ ├── cleaned/ # Validated records (exported)
│ └── rejected/ # Bad records with reasons
├── db/
│ └── [Link] # SQLite database (auto-created)
│
├── src/
│ ├── 01_setup_db.py # Database schema creation │ ├──
02_etl_pipeline.py # ETL: validate, clean, load │ ├──
03_loyalty_rfm.py # Loyalty points + RFM segmentation │ ├──
04_predictive.py # Predictive analytics
│ ├── 05_dashboard.py # Static matplotlib dashboard │ ├──
05_dashboard_streamlit.py # Interactive web dashboard │
├── generate_er_diagram.py # ER diagram generator │ └──
utils/
│ └── error_handler.py # Centralized error handling
├── tests/
│ ├── test_setup_db.py # Database tests (14 tests) │
├── test_etl_pipeline.py # ETL tests (27 tests) │ ├──
test_loyalty_rfm.py # Loyalty/RFM tests (22 tests) │ └──
test_predictive.py # Predictive tests (33 tests) │
├── output/
│ ├── [Link] # Combined 4-chart dashboard │
└── chart*.png # Individual chart images
├── logs/
│ └── retailpulse_*.log # Daily rotating log files
│
├── diagrams/
│ └── er_diagram.png # Database ER diagram │
└── [Link]
The Data We Work With
Master Data (configuration, rarely changes)
File Records Description
[Link] 5 Store locations and regions
[Link] 49 Product catalog with pricing
loyalty_rules.csv 5 Point calculation rules by tier
promotion_details.csv 10 Promotional campaigns
Transactional Data (grows daily)
File Records Description
customer_details.csv 199 Customer profiles
store_sales_header.csv ~2000 Transaction headers
store_sales_line_items.csv ~5000 Individual items per transaction
Setup Instructions
Prerequisites
• Python 3.8 or higher
• pip (Python package manager)
Install Dependencies
pip install pandas matplotlib streamlit plotly pytest
Prepare Data
Ensure CSV files are in data/raw/:
• [Link], [Link], customer_details.csv
• promotion_details.csv, loyalty_rules.csv
• store_sales_header.csv, store_sales_line_items.csv
How to Run
Full Pipeline (First Time Setup)
Run all scripts in sequence:
# Step 1: Create database tables
python src/01_setup_db.py
# Step 2: Run ETL pipeline
python src/02_etl_pipeline.py
# Step 3: Calculate loyalty points and RFM
segments python src/03_loyalty_rfm.py
# Step 4: Run predictive analytics
python src/04_predictive.py
# Step 5: Generate dashboard
python src/05_dashboard.py
Total time: ~30 seconds
Interactive Dashboard
Launch the Streamlit web dashboard:
streamlit run src/05_dashboard_streamlit.py
Open browser to [Link] to
see: • Sales trends and store performance •
Customer loyalty distribution
• At-risk customer alerts
• Top products analysis
Run Unit Tests
Verify everything works correctly:
# Run all 96 tests
python -m pytest tests/ -v
# Run specific test module
python -m pytest tests/test_etl_pipeline.py -v
# Run with coverage report
python -m pytest tests/ --cov=src
Data Quality Validation
The ETL pipeline enforces 5 types of validation:
1. Required Fields Check
Mandatory columns cannot be empty:
• store_id, product_id, customer_id, transaction_id
• store_name, product_name, first_name
2. Data Type Validation
• Numbers must be numeric (prices, quantities, amounts)
• Dates must be valid date formats
• IDs are normalized (float → string conversion)
3. Business Rules
• Prices and quantities must be non-negative
• Stock levels must be valid integers
• Percentages must be between 0-100
4. Character Stripping
Automatically removes special characters from amounts:
• Currency symbols: $, ₹, £, €
• Formatting: ,, %
5. Rejection Tracking
Bad records aren't deleted — they're moved to *_rejected tables with specific error
messages: -- Example: See why records were rejected
SELECT * FROM products_rejected;
-- Shows: product_id, all columns, reject_reason
Error Handling
Custom Exception Hierarchy
RetailPulseError (base)
├── DatabaseError # Connection, query, table issues
├── FileError # Missing files, permission errors
├── ETLError # Validation, transformation failures
├── AnalyticsError # Calculation, prediction errors
├── DataValidationError # Invalid data format/values
└── ConfigurationError # Missing config, invalid settings
Features
Feature Description
Logging Daily rotating logs in logs/retailpulse_YYYYMMDD.log
Decorators @handle_exceptions for consistent error handling
Retry Logic @retry_on_error for transient failures
Validation Utils validate_file_exists(), validate_directory_exists()
Exit Codes All scripts return 0 (success) or 1 (error)
Example: Graceful Error Recovery
# Individual row errors don't crash the pipeline
for row in csv_data:
try:
validate_and_insert(row)
except DataValidationError as e:
insert_to_rejected_table(row, reason=str(e))
continue # Keep processing other rows
Unit Testing
96 tests covering all major functionality:
Test Coverage by Module
Module Tests Coverage
test_setup_db.py 14 Table creation, constraints, idempotency
Module Tests Coverage
test_etl_pipeline.py 27 Validation, type casting, CSV ingestion
test_loyalty_rfm.py 22 Points calculation, tier assignment, RFM logic
test_predictive.py 33 Spend forecast, restock flags, promo sensitivity
Key Test Categories
Database Tests
• Tables created correctly with proper schemas
• Primary keys and foreign key relationships
• Default values applied
• Idempotent creation (can run multiple times)
ETL Tests
• Special character stripping ($100 → 100)
• Null value handling in mandatory columns
• Negative value detection
• Incremental load without duplicates
• Data type casting (dates, floats)
Analytics Tests
• Loyalty tier boundaries (Bronze/Silver/Gold)
• RFM recency calculation
• At-risk customer flagging (>30 days)
• High spender identification (top 20%)
Predictive Tests
• 3-month moving average calculation
• Restock threshold logic
• Promotion sensitivity classification
• Edge cases (zero history, large values, decimals)
Running Tests
# All tests with verbose output
python -m pytest tests/ -v
# Stop on first failure
python -m pytest tests/ -x
# Run tests matching a pattern
python -m pytest tests/ -k "loyalty"
# Generate HTML report
python -m pytest tests/ --html=[Link]
Database Schema
Core Tables (7)
Table Primary Key Description
stores store_id Store locations and regions
products product_id Product catalog with pricing
customer_details customer_id Customer profiles and loyalty info
store_sales_header transaction_id Sales transaction headers
store_sales_line_items line_item_id Individual items per transaction
promotion_details promotion_id Promotional campaigns
loyalty_rules rule_id Point calculation rules
Rejected Tables (7)
Mirror tables with additional reject_reason column:
• stores_rejected, products_rejected, customer_details_rejected •
store_sales_header_rejected, store_sales_line_items_rejected •
promotion_details_rejected, loyalty_rules_rejected
Analytics Tables (2)
Table Description
rfm_summary Customer recency, frequency, monetary scores
customer_predictions Predicted next month spend
Customer Segments
Segment Code Criteria Business Action
High Spender HS Top 20% by monetary VIP treatment, exclusive offers
value
At Risk AR No purchase in 30+ days Retention campaigns,
win-back offers
Note: HS takes priority if customer qualifies for both.
Loyalty Tiers
Tier Points Required Benefits
Gold ≥ 1000 Premium rewards, priority support
Silver ≥ 500 Standard rewards
Bronze < 500 Basic rewards
Output Files
After running the full pipeline:
Location Contents
db/[Link] SQLite database with all data
data/cleaned/*.csv Validated clean records
data/rejected/*.csv Rejected records with reasons
output/[Link] Combined 4-chart dashboard
Location Contents
output/chart*.png Individual chart images
logs/retailpulse_*.log Execution logs
3→ IT Ticket Resolution Suggestion Engine (AI + NLP-Based Support Assistant)
Full-stack IT support ticket system that uses a hybrid NLP approach to instantly suggest solutions for
users. It first uses text similarity (TF-IDF + Cosine Similarity from Scikit-Learn) to find the top historical
matches, and then runs those matches through the Groq LLaMA 3.1 8B LLM to generate exactly 5
synthesized, actionable resolution points.
Architecture
• Frontend: Streamlit
• Backend API: FastAPI
• Database: PostgreSQL (NeonDB) or Local SQLite via SQLAlchemy
• NLP Engine: TF-IDF & Cosine Similarity using Scikit-Learn
• LLM Engine: Groq API (LLaMA 3.1 8B Model)
Pre-requisites
• Python 3.10+
Setup Instructions
1. Clone the repository and go to the project directory (if not already there).
2. Setup virtual environment (optional but recommended):
python -m venv venv
venv\Scripts\activate
3. Install Dependencies: Inside ticket-ai/backend, run:
pip install -r [Link]
4. Environment Variables Configs: By default, the project uses a local SQLite database and a
hardcoded Groq API key for quick demonstration. If you want to use Neon PostgreSQL or
your securely inject your own Groq API Key, create a .env file inside backend/ and set:
DATABASE_URL=postgresql://user:password@endpoint...
SECRET_KEY=your-secret...
GROQ_API_KEY=gsk_your_api_key_here...
5. Seed the Database: To ensure the ML Model works properly, run the database seeding script
to populate ~50 synthetic historical tickets and resolutions. From the backend/ directory:
python seed_db.py
Running the Application
1. Start the FastAPI Backend
From the backend/ directory, start uvicorn:
uvicorn main:app --reload
The backend will run on [Link] You can check the Autogenerated API docs
at [Link]
2. Start the Streamlit Frontend
In a new terminal tab, from the frontend/ directory, start Streamlit:
streamlit run [Link]
This will open up the Streamlit UI in your default browser automatically.
4→ Doctor Appointment System
A role-based hospital appointment management system supporting Admin, Doctor, and Patient
workflows with strict Online/Offline mode separation, slot locking, appointment lifecycle tracking,
prescriptions, and revenue reporting.
Project Overview
This system is designed for a hospital where:
• Doctors manage availability and consultations
• Patients book appointments (Online / Offline)
• Admin monitors appointments, revenue, and system data
⚠ Strict Rule: Online and Offline consultations must use different doctors.
User Roles & Responsibilities
1. Admin (Hospital Authority)
Responsibilities
• Manage doctors and specialities
• Define doctor duty shifts (Morning / Evening / Night) •
Create and manage appointment slots
• Assign token numbers per slot
• Control booking time windows
• Monitor all appointments
• Generate revenue and patient reports
• Maintain full database control
Features
Dashboard Overview
• Total appointments (Daily / Weekly / Monthly) •
Revenue by doctor
• Revenue by speciality
• Online vs Offline distribution
• Appointment completion rate
Doctor Management
• Add / Update / Deactivate doctors
• Assign speciality
• Define consultation mode (Online / Offline) •
Set consultation fee
Shift Management
Admin assigns doctors to predefined shifts:
• Morning Shift (e.g., 9:00 AM – 1:00 PM) •
Evening Shift (e.g., 2:00 PM – 6:00 PM) • Night Shift
(e.g., 7:00 PM – 11:00 PM) Each shift includes:
• Date
• Start time
• End time
• Mode (Online / Offline)
�� Slot & Token Management
• Generate time-based slots within assigned shift
• Assign token number for each slot
• Define slot duration (e.g., 15 / 20 / 30 minutes)
• Automatically mark slot as booked when reserved
• Prevent double booking using transactional locking
Patient Records Access
• View complete patient profiles
• Access appointment history
• Monitor prescription records
System Monitoring
• Track appointment lifecycle
• View cancelled / no-show reports
• Audit log tracking
⚠ Admin has full control over doctor availability, shifts, and token generation.
2. Doctor Role
Responsibilities
• Conduct consultations
• View assigned shift schedule
• Update appointment status
• Add prescriptions
• Provide video link for online consultations
Features
Schedule Access
• View weekly assigned duty schedule (Read-only)
• View shift timing (Morning / Evening / Night)
• See booked patient list with token numbers
Appointment Handling
• View appointment details
• Access patient medical history
• Update appointment status:
o confirmed
o completed
o cancelled
o no-show
Prescription Management
• Add diagnosis notes
• Add prescribed medicines
• Add follow-up instructions
• Timestamp auto-recorded
Online Consultation
• Add secure video consultation link
• Visible only to booked patient
Offline Consultation
• Clinic address displayed automatically
⚠ Doctors cannot create, edit, or delete availability slots. Availability is strictly managed by Admin.
3. Patient Role
Responsibilities
• Browse doctors
• Filter by consultation mode
• Book available slots
• Attend consultation
• Access health history
Features
Dashboard
• Toggle between Online / Offline mode •
View upcoming appointments
• View past appointments
Doctor Filtering
• Filter by:
o Speciality
o Availability
o Mode (Online / Offline)
Appointment Booking
• Select available shift
• Select available time slot
• Receive token number
• Slot auto-locked upon booking
Medical History
• View past prescriptions
• View consultation notes
• Track appointment history
Online Appointment
• Access video link from dashboard
Offline Appointment
• View clinic address and shift timing