""" DTE Punjab - Big Data & Data Science Training Dashboard Flask backend: loads XLSX, exposes JSON API endpoints. """ import os import io import json import pandas as pd from flask import Flask, render_template, jsonify, send_file, request from dotenv import load_dotenv load_dotenv() app = Flask(__name__) DATA_FILE = os.path.join(os.path.dirname(__file__), "data", "DTE_all_Batch.xlsx") GMAP_KEY = os.getenv("GOOGLE_MAPS_API_KEY", "") # Coordinates & Normalisation Maps DISTRICT_COORDS = { "Amritsar": (31.6340, 74.8723), "Ludhiana": (30.9010, 75.8573), "Patiala": (30.3398, 76.3869), "Jalandhar": (31.3260, 75.5762), "Bathinda": (30.2110, 74.9455), "Rupnagar": (30.9686, 76.5253), "Mohali": (30.7046, 76.7179), "SAS Nagar Mohali": (30.7046, 76.7179), "Hoshiarpur": (31.5347, 75.9114), "Moga": (30.8171, 75.1683), "Gurdaspur": (32.0398, 75.4058), "Ferozepur": (30.9233, 74.6150), "Tarn Taran": (31.4519, 74.9282), "Faridkot": (30.6645, 74.7550), "Barnala": (30.3776, 75.5483), "Mansa": (29.9877, 75.3974), "Fatehgarh sahib": (30.6492, 76.3903), "Shaheed Bhagat Singh Nagar":(31.1285, 76.1148), } COLLEGE_MAP = { "Government Polytechnic College, Bhikhiwind": "GPC Bhikhiwind", "Government Polytechnic College Guru Teg Bahadur Garh (Moga)": "GPC GTB Garh Moga", "Government Polytechnic College, Dinanagar": "GPC Dinanagar", "Government. Polytecnic College Jalandhar": "GPC Jalandhar", "Government Polytechnic College,Jalandhar": "GPC Jalandhar", "Government Polytechnic College, Jalandhar": "GPC Jalandhar", "Sant Baba Attar Singh Government. Polytechnic College , Badbar": "SBAS GPC Badbar", "Sant Baba Attar Singh Government. Polytechnic College, Badbar": "SBAS GPC Badbar", "Government. Polytecnic College , Amritsar": "GPC Amritsar", "Government Polytechnic College, Amritsar": "GPC Amritsar", "Mai Bhago Government Polytechnic College For Girls, Amritsar": "Mai Bhago GPC Amritsar", "Government Polytechnic College for Girls,Amritsar": "Mai Bhago GPC Amritsar", "Government. Polytechnic College, Bathinda": "GPC Bathinda", "Government Polytechnic College, Bathinda": "GPC Bathinda", "Government Polytechnic College Khunimajra": "GPC Khunimajra", "Government.Polytechnic College, Khunimajra": "GPC Khunimajra", "Government polytechnic khunimajra": "GPC Khunimajra", "Government Polytechnic College, Khunimajra": "GPC Khunimajra", "S. R. S. Government Polytechnic College Ludhiana": "SRS GPC Ludhiana", "Government Polytechnic College, Ludhiana": "SRS GPC Ludhiana", "SRS Government Polytechnic College, Ludhiana": "SRS GPC Ludhiana", "Government Polytechnic College Ferozpur": "GPC Ferozepur", "Government Polytechnic College Ferozepur": "GPC Ferozepur", "Government Polytechnic College,Ferozepur": "GPC Ferozepur", "Government Polytechnic College, Ropar": "GPC Rupnagar", "Government Polytechnic College, Rupnagar": "GPC Rupnagar", "Government Polytechnic College, Patiala": "GPC Patiala", "Government. Polytechnic College, Patiala": "GPC Patiala", "Government Polytechnic College, Bareta": "GPC Bareta", "Shaheed Nand Singh Government. Polytechnic College, Bareta": "GPC Bareta", "Shaheed Nand Singh Government Polytechnic College Bareta": "GPC Bareta", "Government Polytechnic College Kotkapura": "GPC Kotkapura", "Government Polytechnic College, Kotkapura": "GPC Kotkapura", "Pt. J. R. Government. Polytechnic College Hoshiarpur": "Pt. JR GPC Hoshiarpur", "Pt. J.R. Government Polytechnic College, Hoshairpur": "Pt. JR GPC Hoshiarpur", "S. Amarjit Singh Sahi Government Polytechnic College, Talwara": "SASS GPC Talwara", "SASS Government Polytechnic College, Talwara": "SASS GPC Talwara", "Government Polytechnic College, Behram": "GPC Behram", "Shri Guru Hargobind Sahib Government Polytechnic College Ranwan": "SGHS GPC Ranwan" } COLLEGE_COORDS = { "GPC Bhikhiwind": (31.3283, 74.7001), "GPC GTB Garh Moga": (30.8229, 75.1742), "GPC Dinanagar": (32.1384, 75.4667), "GPC Jalandhar": (31.3200, 75.6000), "SBAS GPC Badbar": (30.3444, 75.6263), "GPC Amritsar": (31.6366, 74.8745), "Mai Bhago GPC Amritsar": (31.6212, 74.8872), "GPC Bathinda": (30.2223, 74.9542), "GPC Khunimajra": (30.7303, 76.6669), "SRS GPC Ludhiana": (30.9022, 75.8341), "GPC Ferozepur": (30.9329, 74.6210), "GPC Rupnagar": (30.9634, 76.5312), "GPC Patiala": (30.3294, 76.3860), "GPC Bareta": (29.8711, 75.7118), "GPC Kotkapura": (30.5843, 74.8252), "Pt. JR GPC Hoshiarpur": (31.5369, 75.9224), "SASS GPC Talwara": (31.9546, 75.8715), "GPC Behram": (31.1118, 76.0123), "SGHS GPC Ranwan": (30.6480, 76.3821) } # Data Loading def load_data() -> pd.DataFrame: raw = pd.read_excel(DATA_FILE, sheet_name=0, header=None) headers = raw.iloc[6].tolist() headers = [str(h).strip() if pd.notna(h) else f"col_{i}" for i, h in enumerate(headers)] df = raw.iloc[7:].copy() df.columns = headers df = df.reset_index(drop=True) df.dropna(how="all", inplace=True) rename_map = { "SNo.": "sno", "Full Name": "name", "Gender": "gender", "Mobile No.": "mobile", "Designation": "designation", "Branch/Trade": "branch", "Email Id": "email", "College Name": "college", "District": "district", } df.rename(columns={k: v for k, v in rename_map.items() if k in df.columns}, inplace=True) def batch_label(sno): try: n = int(float(sno)) if n <= 30: return "Batch 1 (9–13 Feb)" if n <= 53: return "Batch 2 (16–20 Feb)" return "Batch 3 (23–27 Feb)" except Exception: return "Unknown" df["batch"] = df["sno"].apply(batch_label) for col in ["name","gender","designation","branch","college","district","email","mobile"]: if col in df.columns: df[col] = df[col].astype(str).str.strip().replace("nan", "N/A") if "college" in df.columns: df["college"] = df["college"].map(COLLEGE_MAP).fillna(df["college"]) if "designation" in df.columns: df["designation"] = df["designation"].str.upper() df["designation"] = df["designation"].str.replace(r'\s+', ' ', regex=True).str.strip() desig_map = { "SR. LECTURER": "Senior Lecturer", "SR LECTURER": "Senior Lecturer", "SENIOR LECTURER": "Senior Lecturer", "LECTURER": "Lecturer", "SYSTEM ANALYST": "System Analyst", "HOD": "HOD" } df["designation"] = df["designation"].map(desig_map).fillna(df["designation"].str.title()) branch_map = { "computer science and engineering": "CSE", "information technology": "IT", "information technology ": "IT", "computer engineering": "CE", } if "branch" in df.columns: df["branch"] = df["branch"].str.lower().map(branch_map).fillna(df["branch"]) return df # API Endpoints @app.route("/api/dashboard-data") def dashboard_data(): df = load_data() return jsonify(df.to_dict(orient="records")) @app.route("/api/summary") def summary(): df = load_data() gender_counts = df["gender"].value_counts().to_dict() desig_counts = df["designation"].value_counts().to_dict() branch_counts = df["branch"].value_counts().to_dict() district_counts= df["district"].value_counts().to_dict() batch_counts = df["batch"].value_counts().to_dict() college_counts = df["college"].value_counts().head(10).to_dict() total = len(df) female_pct = round(gender_counts.get("Female", 0) / total * 100, 1) senior_pct = round(desig_counts.get("Senior Lecturer", 0) / total * 100, 1) hod_count = desig_counts.get("HOD", 0) # Batch-wise gender breakdown batch_gender = {} for batch in df["batch"].unique(): b_df = df[df["batch"] == batch] batch_gender[batch] = b_df["gender"].value_counts().to_dict() return jsonify({ "total_participants": total, "total_colleges": df["college"].nunique(), "total_districts": df["district"].nunique(), "female_pct": female_pct, "hod_count": hod_count, "senior_pct": senior_pct, "gender_counts": gender_counts, "designation_counts": desig_counts, "branch_counts": branch_counts, "district_counts": district_counts, "batch_counts": batch_counts, "top_colleges": college_counts, "batch_gender": batch_gender, }) @app.route("/api/map-data") def map_data(): df = load_data() # Grouping by normalized college name to render bubbles over actual colleges grouped = df.groupby("college").agg( count=("name", "count"), district=("district", "first"), names=("name", list), designations=("designation", lambda x: x.value_counts().to_dict()), genders=("gender", lambda x: x.value_counts().to_dict()), ).reset_index() features = [] district_counts = {} for _, row in grouped.iterrows(): college_name = row["college"] district_name = row["district"] # Base coordinate is the district coordinate to place them visually "on the district" base_coords = DISTRICT_COORDS.get(district_name) if not base_coords: base_coords = (31.1471, 75.3412) # Fallback center # Add a slight offset for multiple colleges in the same district to prevent overlapping bubbles c_idx = district_counts.get(district_name, 0) district_counts[district_name] = c_idx + 1 offset_lat, offset_lng = 0, 0 if c_idx == 1: offset_lat, offset_lng = 0.03, 0.03 elif c_idx == 2: offset_lat, offset_lng = -0.03, -0.03 elif c_idx == 3: offset_lat, offset_lng = 0.03, -0.03 elif c_idx == 4: offset_lat, offset_lng = -0.03, 0.03 features.append({ "college": college_name, "district": district_name, "count": int(row["count"]), "lat": base_coords[0] + offset_lat, "lng": base_coords[1] + offset_lng, "sample": row["names"][:5], "designations": row["designations"], "genders": row["genders"], }) return jsonify({"features": features, "gmaps_key": GMAP_KEY}) @app.route("/api/export") def export_csv(): """Export filtered data as CSV.""" df = load_data() batch = request.args.get("batch", "") desig = request.args.get("designation", "") district= request.args.get("district", "") if batch: df = df[df["batch"] == batch] if desig: df = df[df["designation"] == desig] if district: df = df[df["district"] == district] cols = ["sno","name","gender","designation","branch","college","district","email","mobile","batch"] cols = [c for c in cols if c in df.columns] buf = io.StringIO() df[cols].to_csv(buf, index=False) buf.seek(0) return send_file( io.BytesIO(buf.getvalue().encode()), mimetype="text/csv", as_attachment=True, download_name="DTE_participants.csv" ) @app.route("/api/stats/advanced") def advanced_stats(): """Return advanced statistics for the insights panel.""" df = load_data() # College with most participants top_college = df["college"].value_counts().idxmax() top_college_count = int(df["college"].value_counts().max()) # District with most participants top_district = df["district"].value_counts().idxmax() top_district_count = int(df["district"].value_counts().max()) # Gender per batch pivot = df.pivot_table(index="batch", columns="gender", aggfunc="size", fill_value=0) pivot_dict = pivot.to_dict() return jsonify({ "top_college": top_college, "top_college_count": top_college_count, "top_district": top_district, "top_district_count": top_district_count, "gender_by_batch": pivot_dict, "avg_per_district": round(len(df) / df["district"].nunique(), 1), "unique_colleges": int(df["college"].nunique()), }) # Pages @app.route("/") def index(): return render_template("index.html") if __name__ == "__main__": app.run(debug=True, port=5000)