Proof of concept

Clinic data aggregation and reporting

A system that takes in the Excel files that local clinics send every quarter, checks and aggregates them, and produces a report with charts. Nobody has to update totals or charts by hand any more, and likely input errors are flagged automatically. The demo on this page recreates the approach with fictional clinics.

For demonstration only, with fictional clinics. It is implemented separately from the actual system, and every facility name and value is fictional.

The problem: Regional infection-control networks collect figures such as hand-rub and antibiotic use and notifiable infections from several clinics every quarter and compile them into a report. Each clinic fills in the Excel template a little differently. By hand, updating totals and charts takes time, and wrong units or values pasted into the wrong column are easy to miss.
Status: Being evaluated as a proof of concept.
My role: Planning, design and implementation, on my own.
Technology in the real system: Python, openpyxl (reading Excel), DuckDB (aggregation), python-pptx (generating PowerPoint). It runs on a local machine at a fixed time every day and rebuilds the report only when new files arrive. No data leaves the machine.
How this demo differs: This demo is a small implementation written from scratch for display. Ingestion, checks, aggregation and charts are actually computed when this page is built. The real system handles more items (individual infections and antibiotics) and trends over past quarters, and outputs a PowerPoint file. The demo only draws the charts on the page.

Demo: from submitted files to the report

File and facility names are in Japanese, as in the real system. Every facility name and value is fictional.

All eight clinics submitted the template as intended. Everything is taken in as is, and the checks find nothing.

Quarter: April–June 2026, compared with January–March 2026

1. Ingestion

The facility is identified from the name written in the workbook, matched against the roster after normalizing spelling. Sheets are found by name, not by position.

A診療所.xlsx→ A診療所
Ingested
As is
B診療所.xlsx→ B診療所
Ingested
As is
C診療所.xlsx→ C診療所
Ingested
As is
D診療所.xlsx→ D診療所
Ingested
As is
E診療所.xlsx→ E診療所
Ingested
As is
F診療所.xlsx→ F診療所
Ingested
As is
G診療所.xlsx→ G診療所
Ingested
As is
H診療所.xlsx→ H診療所
Ingested
As is

2. Checks

Missing submissions, wrong units, values pasted into the wrong column and sudden increases from the previous quarter are flagged. The checks only report; a person decides whether to correct a value.

No problems found.

3. Aggregation

8 of 8 clinics submitted

Hand-rub use is computed per month as usage ÷ patients and averaged over three months; counts are summed over three months. Values in parentheses are from the previous quarter.

Facility	Hand rub (mL/patient)	Influenza (cases)	COVID-19 (cases)	Oral antibiotics (total)
A診療所	4.00(3.80)	4(40)	15(20)	295(305)
B診療所	2.58(2.40)	2(18)	7(8)	134(136)
C診療所	5.50(5.20)	9(55)	24(25)	397(400)
D診療所	2.00(2.10)	2(12)	6(6)	96(93)
E診療所	2.92(2.80)	5(30)	15(15)	215(224)
F診療所	4.50(4.20)	0(6)	4(4)	61(62)
G診療所	2.67(2.60)	4(22)	12(12)	167(169)
H診療所	5.50(5.30)	7(45)	21(22)	341(350)

4. Report charts

The charts for the report are drawn from the aggregated values. Clinics that did not submit get no bar, so they are not confused with zero.

For demonstration only, with fictional clinics. It is implemented separately from the actual system, and every facility name and value is fictional.

Slide 1

Hand-rub use (mL per patient, highest first)

	This quarter
C診療所	5.50
H診療所	5.50
F診療所	4.50
A診療所	4.00
E診療所	2.92
G診療所	2.67
B診療所	2.58
D診療所	2.00
Median 3.46

Slide 2

COVID-19 notifications by clinic

Previous
This quarter

	Previous	This quarter
A	20	15
B	8	7
C	25	24
D	6	6
E	15	15
F	4	4
G	12	12
H	22	21

Slide 3

Oral antibiotic use by class (per clinic)

Previous
This quarter

	Previous	This quarter
3rd-gen ceph.	64.0	58.9
Macrolides	43.4	40.8
Quinolones	22.5	22.4
Penicillins	87.5	91.3