Inspiration

What it doesThoughts: The user is asking for a "What we built" or project story for their hackathon submission. They want it in Markdown format with LaTeX support for math. This is typically the detailed writeup that goes on hackathon platforms like Devpost.

Here's a comprehensive hackathon submission story in Markdown format:

🏥 HealthGap India: Data-Driven Healthcare Infrastructure Planning 💡 Inspiration India's healthcare system serves 1.4 billion people across 700+ districts, but infrastructure gaps are massive and unevenly distributed. State governments face an impossible question: with limited budgets, where should we build the next hospital? Which districts need more beds? Where are doctors most urgently needed?

The inspiration came from realizing that India already collects incredible health data through the NFHS-5 survey (National Family Health Survey) covering maternal health, child nutrition, vaccination rates, and chronic disease prevalence. We also have comprehensive facility infrastructure data. But these datasets lived in isolation—no one had connected health needs with infrastructure supply to identify the gaps.

The "aha moment" was simple: what if we could automatically prioritize every district in India by overlaying health demand with healthcare supply? Policymakers could stop guessing and start targeting resources with surgical precision.

🎯 What It Does HealthGap India is an interactive analytics platform that combines NFHS-5 health indicators with facility infrastructure data to identify and prioritize healthcare gaps across 700+ Indian districts.

Three Analytical Views:

1. Gap Analysis

Overlays demand (health needs) with supply (infrastructure availability) Automatically classifies districts as HIGH/MEDIUM/LOW priority Interactive state-level heatmaps show where intervention is most urgent

1. Supply Analysis

Scores districts on infrastructure adequacy using three weighted components: Facility Density (40%): facilities per 100k households Capacity (30%): bed availability per capita Staffing (30%): doctor-to-population ratios Identifies regions with critical infrastructure shortages

1. Demand Analysis

Breaks down health needs across four categories: Maternity Care: institutional birth rates, ANC visits, C-section rates Pediatric Care: vaccination coverage, child stunting, wasting NCD/Specialty Care: diabetes, hypertension, obesity prevalence Overall Access: composite health need across all dimensions Reveals which types of care are most lacking in each region Key Features: ✅ Interactive state-level choropleth maps with color-coded priority levels ✅ District-level detail tables with exportable CSV data ✅ Supply vs Demand scatter plots showing the relationship between need and availability ✅ Real-time filtering by state and priority level ✅ Methodology transparency with expandable explanations of scoring formulas

🔧 How We Built It Data Pipeline

1. Data Sources

NFHS-5 (2019-2021): District-level health indicators covering ~600k households Healthcare Facilities Database (2024): ~180k facilities with location, type, capacity, and staffing data

1. Data Processing (Databricks SQL + Delta Lake)

Standardized district names across datasets (handled spelling variations, administrative changes) Created analytical tables with computed demand scores:

Maternity Demand

0.5 × ( 100 − Institutional Birth% ) + 0.3 × ( 100 − ANC4+% ) + 0.2 × C-Section% Maternity Demand=0.5×(100−Institutional Birth%)+0.3×(100−ANC4+%)+0.2×C-Section%

Pediatric Demand

0.4 × ( 100 − Vaccination% ) + 0.4 × Stunting% + 0.2 × Wasting% Pediatric Demand=0.4×(100−Vaccination%)+0.4×Stunting%+0.2×Wasting%

NCD Demand

0.5 × Overweight% + 0.5 × High Blood Sugar% NCD Demand=0.5×Overweight%+0.5×High Blood Sugar% Aggregated facility data by district for supply metrics Calculated normalized supply scores using percentile ranking:

Supply Score

0.4 × Facility Score + 0.3 × Capacity Score + 0.3 × Staffing Score Supply Score=0.4×Facility Score+0.3×Capacity Score+0.3×Staffing Score

1. Data Governance

Used Unity Catalog for secure, governed data access Implemented row-level security and column masking for sensitive health data Created app service principal with least-privilege access grants Application Architecture Frontend: Streamlit

Clean, intuitive interface requiring zero technical expertise Three-view navigation with sidebar filters Responsive tables with pandas DataFrame styling Real-time category switching for Demand Analysis Visualization: Plotly

Choropleth maps using India state GeoJSON boundaries Interactive hover tooltips with district details Color scales optimized for accessibility (colorblind-friendly) Scatter plots with size encoding for household population Backend: Databricks Apps

Serverless compute with auto-scaling Direct SQL connector with service principal authentication Connection pooling with @st.cache_resource Efficient query patterns with LIMIT 700 to prevent memory issues Data Layer: Unity Catalog + Delta Lake

facilities_analysis.raw.facilities_clean: cleaned facility records facilities_analysis.analytical.district_supply: aggregated supply metrics facilities_analysis.analytical.district_gaps: demand scores + priorities 🚧 Challenges We Faced

1. District Name Standardization Hell Problem: NFHS-5 uses different district names than facility data (e.g., "Bangalore Urban" vs "Bengaluru Urban").

Solution: Created a district standardization function using fuzzy matching and manual mapping tables. Achieved 95%+ match rate across 700+ districts.

1. State Name Mismatch in GeoJSON Problem: Maps weren't rendering—our database had state names in ALL CAPS ("ANDHRA PRADESH") but the GeoJSON expected Title Case ("Andhra Pradesh").

Solution: Implemented a normalize_state_name() function to convert to Title Case before aggregation. Simple fix, but cost us 2 hours of debugging!

1. Pandas Styler: Non-Unique Index Error Problem: When applying color-coding to tables, Pandas threw 'Styler.apply and .map are not compatible with non-unique index or columns.'

Solution: Added .reset_index(drop=True) before styling operations. Also discovered we had duplicate column names ("Facilities per 100k HH") when category selection changed—fixed with conditional column building.

1. Service Principal Permissions Problem: App crashed with "service principal has not been granted access to facilities_analysis catalog."

Solution: Had to grant permissions using the service principal UUID (not the display name with spaces):

GRANT USE CATALOG ON CATALOG facilities_analysis TO 7baace2f-acf4-47bc-bf6e-89e4bb43879b;

1. Scoring Formula Design Problem: How do you combine apples (% of kids vaccinated) with oranges (facility count) into a single priority score?

Solution: Used percentile ranking (0-100 scale) for normalization, then weighted combinations. Tested multiple weight configurations and validated against known high-need districts.

🎉 Accomplishments We're Proud Of ✅ End-to-end data pipeline from raw CSVs to interactive visualizations ✅ Three complementary analytical views that tell the complete supply-demand story ✅ Automatic prioritization across 700+ districts—no manual classification needed ✅ Geographic visualization that makes complex patterns instantly understandable ✅ Production-ready deployment on Databricks Apps with proper security ✅ Transparent methodology with expandable explanations so users trust the scores

Most importantly: we made complex healthcare data accessible to non-technical decision-makers. A state health minister can open this app and immediately see their priority districts—no SQL, no Python, no confusion.

📚 What We Learned Technical Skills Unity Catalog governance: Implementing service principal access grants, managing schemas and catalogs Databricks Apps deployment: The mandatory sequence—always apps get before apps deploy, handling STARTING/STOPPED/RUNNING states Streamlit state management: Category-specific UI with conditional rendering Plotly choropleth maps: GeoJSON integration, color scales, hover customization Pandas styling gotchas: Index uniqueness requirements, apply vs. map differences Domain Knowledge NFHS-5 survey structure: Understanding district-level sampling, indicator definitions Healthcare facility taxonomy: CHC vs PHC vs sub-centers, capacity vs. beds, staffing classifications India administrative geography: District name variations, state reorganizations (Telangana split, Ladakh UT creation) Problem-Solving Insights Scoring is subjective: There's no "perfect" formula—transparency about weights and methodology builds trust Visualization > tables: A single choropleth map communicates gaps better than 700 rows of data Filter wisely: Limiting to 700 districts prevents memory issues while covering 90%+ of population 🚀 What's Next for HealthGap India Immediate Enhancements Facility type breakdown by category: Show which districts lack maternity facilities specifically, not just general shortage Time-series analysis: Track how gaps change over time with new facility construction Budget simulation: "If I have ₹100 crore, which 10 districts should I invest in?" Mobile-responsive design: Optimize for tablets/phones for field use Advanced Features Travel time analysis: Incorporate road networks to calculate facility accessibility by travel time, not just count Predictive modeling: ML models to forecast future demand based on population growth and disease trends Alert system: Notify health departments when a district crosses critical thresholds Multi-country support: Adapt to NFHS-equivalent surveys in other developing nations Policy Integration Integration with National Health Mission: Connect directly to NHM planning workflows Public dashboard: Publish anonymized state-level data for transparency and advocacy

How we built it

Challenges we ran into

Accomplishments that we're proud of

What we learned

What's next for CareGap India

Built With

• apps
• data-platform:-databricks
• database
• delta-lake-languages:-python
• facilities
• healthcare
• pandas
• pandas-deployment:-databricks-apps-(serverless-compute)-data-sources:-nfhs-5-(2019-2021)
• sql-frontend:-streamlit-visualization:-plotly
• streamlit
• unity-catalog