Hospital Data Analysis Application

A desktop application for analyzing tabular data in natural language. It works on any CSV or Excel file; the bundled sample is hospital visits, but you can import sales, inventory, staffing, or anything else.

Any spreadsheet is imported into a SQLite database. You type an analytical prompt (for example, "average wait time by department" or "total revenue by region"), which is translated into a safe, read-only SQL query. Results are shown as a table and a chart, and can be exported to Power BI.

Translation is done by Gemini AI via the Google Gemini API free tier:

• Get a free key at https://aistudio.google.com/apikey (just a Google account, no credit card). The built demo app ships with a bundled key (see default_key.py) so it runs out of the box. If no key is bundled, the app asks for one on first launch in a dialog (with a "Test key" button) and remembers it. You can set or change it anytime via the API key… button. A GEMINI_API_KEY environment variable (or the gemini CLI), or a key you enter yourself, always takes priority over the bundled one.
• On import, the app inspects the file's actual columns and types and gives them to the model, so it can answer free-form questions about whatever data you loaded.
• An AI key is required. Without one the app can import and browse data but cannot translate prompts; there is no offline query engine.

It is safe by construction: AI-generated SQL is validated to be a single SELECT statement, and all queries run over a read-only database connection, so a prompt can never modify your data.

Why this app (and how it complements Power BI)

Power BI is far more powerful than this app. The two serve different people and different moments, and this app is meant to be the easy, safe front door rather than a replacement.

Where this app helps:

• Natural language, not a learning curve. A clinician or ward administrator can type "average wait time by department last month" and get an answer, with no data models, DAX, or Power Query to learn first. The intended user is someone who would never open Power BI.
• Minimal setup, runs on your machine. Double-click, import a spreadsheet, ask questions. No Microsoft account, no Pro license, no Azure or gateway. Your data file stays local and queries run locally; only the column names, types, and a few sample values are sent to the AI in order to translate a question (a fully offline model is on the roadmap).
• Read-only by guarantee. The app cannot modify the source data (enforced in three layers), so a non-expert can explore freely without risk.
• Curated, not a blank canvas. It focuses on answering the question you asked, which is a feature for non-analysts.

Where Power BI wins: rich interactive dashboards, drill-through and cross-filtering, scheduled or live refresh, sharing and publishing, and custom metrics (DAX) across many tables.

How they fit together: this is not a replacement for Power BI; it comes before it, or serves a different person. Non-technical staff do quick, safe exploration here, then hand off the result as a CSV to an analyst who builds the polished dashboard. That handoff is what the Power BI export below is for.

Setup & Run

pip install -r requirements.txt
python generate_sample_data.py   # creates sample_data/hospital_visits.csv & .xlsx
python app.py

In the app: click Import CSV/Excel…, pick sample_data/hospital_visits.csv, type a prompt (or pick one from the Examples dropdown), and press Analyze.

To verify everything without the GUI: python smoke_test.py

Building a standalone .exe (to share)

To give the app to someone who does not have Python installed, build a self-contained Windows executable with PyInstaller:

pip install -r requirements.txt   # includes pyinstaller
python build.py

This produces:

Output	What it is
dist/HospitalDataAnalysis.exe	The standalone app; no Python needed to run it
release/HospitalDataAnalysis/	The exe plus sample_data/ and this README, ready to run
release/HospitalDataAnalysis.zip	The folder above, zipped for sharing

Send someone release/HospitalDataAnalysis.zip. They unzip it and double-click HospitalDataAnalysis.exe; the first launch takes a few seconds while the bundle unpacks. The bundled sample_data/ lets them try it immediately.

Notes:

• When running as an .exe, the working database is stored per-user at %LOCALAPPDATA%\HospitalDataAnalysis\hospital.db (a writable location, separate from the exe). Each launch starts with no data loaded.
• Windows SmartScreen may warn about an unrecognized publisher because the exe is unsigned; choose More info → Run anyway.
• This demo build bundles your Gemini API key (from default_key.py) into the exe, so recipients can run analyses out of the box with no setup. The key is baked into the binary, so only share the exe with people you would share the key with. To ship a build without your key, delete default_key.py before running build.py; recipients are then prompted for their own key on first launch (stored per-user at %LOCALAPPDATA%\HospitalDataAnalysis\config.json).

Supported prompts

Ask anything answerable from the columns in your file, in free-form English. After you import a file, the app shows a few example prompts tailored to your data in the Examples dropdown (generated from the actual column names and types).

For the bundled hospital sample:

• count of visits by department
• average patient_age by department
• how many visits each month
• busiest doctors

For a sales file you might ask:

• total revenue by region
• top 10 products by quantity sold
• average order value by month

Because the query is generated from your prompt each time, phrasing is flexible; there is no fixed list of supported patterns.

Power BI

Every result offers two handoff buttons:

• Export CSV for Power BI: pick a location and save the result as CSV. In Power BI Desktop, use Get Data → Text/CSV to load it.
• Export & Open in Power BI: saves the CSV to a temp file, copies its path to the clipboard, and launches Power BI Desktop. Choose Get Data → Text/CSV and paste the path to load the data. It finds Power BI whether it was installed from the standalone installer or the Microsoft Store; if it cannot be located, the CSV is still saved.

The app also shows its own bar and line charts, so Power BI is optional.

Data format

No fixed schema; any CSV or Excel file works. On import the app:

• keeps whatever columns the file has (blank or duplicate headers are cleaned up),
• infers column types (numbers, text) from the data,
• lightly normalizes obvious date columns to ISO YYYY-MM-DD so date and month grouping works,
• replaces the previous dataset (one active table at a time).

A comma-separated file with a header row loads most reliably. The whole file is loaded into memory, so its size is bounded by available RAM rather than a fixed row limit.

Architecture

Component	Module
Data ingestion & storage	database.py: pandas import into SQLite (hospital.db), plus schema introspection
Prompt to SQL translation	ai_translator.py: Gemini API, with the prompt built from the live schema
Result types & example prompts	query_engine.py: TranslatedQuery and schema-aware examples
Query execution	database.py::run_query: read-only connection
Visualization & reporting	app.py: embedded matplotlib charts plus CSV export and Power BI hand-off

Data integrity (the read-only guarantee) is enforced at the engine level: analytical queries run over a SQLite connection opened with mode=ro, so even a malformed or hostile query cannot modify the data.