Cumulative Antibiogram Builder and Resistance Trend Dashboard from Hospital Lab Exports

The problem: hospitals have the data but not the table

When a doctor starts antibiotics for a seriously ill patient, culture results are still days away. The choice is guided by the hospital's cumulative antibiogram: a table showing, for each common bacterium, what share of past isolates were susceptible to each antibiotic in that hospital. Without it, doctors guess, often reaching for broad-spectrum drugs that fuel resistance.

Antimicrobial resistance is a serious problem across South Asia. Many hospital labs in Pakistan already record every culture and susceptibility result in a lab information system or a spreadsheet. What is missing is the careful step that turns those records into a correct antibiogram, because doing it properly means following specific rules and cleaning messy data, which nobody has time for.

What already exists and the gap

• WHONET, free software supported by WHO's collaborating centre, manages microbiology lab data and is used in over 120 countries. It is powerful, Windows-based and needs training; many small labs never adopt it.
• The AMR R package (Berends and colleagues, Journal of Statistical Software, 2022) provides excellent functions for cleaning resistance data, selecting first isolates under CLSI M39 and calculating resistance. It is a library for analysts who write R.
• Commercial lab information systems sometimes include antibiogram reports, usually in larger private hospitals.

The gap is a ready tool for a small or medium hospital: upload this year's lab export, fix the mappings it asks about, and get a correct antibiogram, ward tables and trend charts, without writing code.

Inspiration

O'Brien and Stelling's paper "WHONET: An Information System for Monitoring Antimicrobial Resistance" (Emerging Infectious Diseases, 1995) described why local lab data, analysed consistently, is the foundation of resistance surveillance. The Clinical and Laboratory Standards Institute's M39 guideline sets the rules for cumulative antibiograms, such as using only the first isolate of a species per patient per period and reporting only organisms with enough isolates. Your tool makes those rules easy to apply.

What you build

• A lab manager uploads an Excel or CSV export and maps unfamiliar names (for example "E.coli", "E coli" and "Escherichia coli") to standard codes once; the tool remembers.
• The tool cleans the data, removes duplicates, selects first isolates and computes susceptibility percentages.
• The infection control team views ward tables and quarterly trends, and prints the annual antibiogram.
• The data quality report tells the lab what to fix at source next year.

The technically interesting part

• Messy names. Organism and antibiotic names arrive in many spellings and abbreviations. Combine a standard reference list, fuzzy matching and a confirmation screen, so humans approve mappings and the tool learns from them.
• First isolates. Implement the CLSI M39 first-isolate rule correctly and test it on tricky cases: same patient, same organism, different wards, different dates.
• Small numbers. A ward with eight isolates of an organism should not show a percentage at all. Apply the minimum count and show confidence intervals for trends, so a jump from 60 to 70 percent on small samples is not over-read.
• Intrinsic resistance. Some organisms are naturally resistant to some drugs. Mark these cells clearly instead of reporting them as results.
• Privacy. Patient identifiers are needed for first-isolate selection but should never leave the hospital. Run locally and hash identifiers after import.

How to evaluate it

• Correctness: reproduce an antibiogram from a public or synthetic dataset with the AMR R package and compare results cell by cell.
• Rule tests: a suite of hand-built cases for first-isolate selection, minimum counts and intrinsic resistance.
• Mapping effort: time for a lab user to map a new export, and how much it drops the second time.
• Usefulness: show the output to one microbiologist or infection control nurse and record their corrections.

Suggested plan

• Weeks 1 to 3: learn the basics of susceptibility testing and CLSI M39, study the AMR package, build a synthetic dataset.
• Weeks 4 to 7: importers, name mapping, data quality report.
• Weeks 8 to 10: first isolates, antibiogram calculation, rule tests.
• Weeks 11 to 13: ward tables, trends, printable output.
• Weeks 14 to 15: expert feedback, report and demo.

Path to a product

Antimicrobial stewardship programmes are expanding, and accreditation bodies increasingly expect a current antibiogram. A free open source tool with paid setup and support for hospital groups is a credible route, and the project leads naturally into health data roles with hospitals, public health programmes and research groups.

Scope warning

Do not give treatment recommendations; the tool reports past susceptibility, and clinicians decide. Do not use real patient data without the hospital's permission and an ethics approval; a synthetic dataset is enough for development. Support two export formats well before adding a third.

Learn what you need on Sefism

• Data analytics roadmap: cleaning, aggregation and presenting tables clinicians trust.
• SQL and databases roadmap: storing results and mappings for year-on-year comparisons.
• pandas User Guide: grouping, deduplication and pivot tables.
• R for Data Science (2e): if you build on the AMR R package.
• Should I learn Python or R for data science?: choosing the language for this project.

Get help with this idea

Most of the work here is careful rules and cleaning, and the risk is a table with subtle errors. Tauseef can help you plan the test cases, find a clinical adviser and keep the scope to one semester. If you are new here, create a free account first.

• Discuss this idea with a mentor
• Send a query to Tauseef
• Book a 1:1 session