DEV Community

Melnus
Melnus

Posted on

[ShowDev] I Built an Open-Source "Audit Tool" to Detect Government Waste Using Python & Math 🐍📊

#showdev #python #opensource #datascience

The Problem: "Vanity Metrics" in the Public Sector

Governments love big numbers.
"Total budget: $100M!" "Cumulative users: 30,000!"

In the startup world, we call these "Vanity Metrics"—numbers that look good on paper but mean nothing in reality. They often hide the denominator (population or actual needs) to create an illusion of success.

I live in Kashiwa City, Japan. Recently, the city proudly announced "3,000 users!" for a new app project. It sounded impressive until I ran a simple calculation:

City Population: 430,000
Penetration Rate: 0.7%

It wasn't a success. It was a statistical error.
I realized we needed a standardized framework to debug these "political bugs." So, I built one.

The Solution: Standard Block Comparison Method (SBCM)

I developed a logic called SBCM. It normalizes huge, vague numbers into a "Standard Block"—the capacity of a single average municipality.

1. The Algorithm

Instead of looking at the raw number ($V$), we calculate the Effectiveness Impact ($I$).

I=VB I = \frac{V}{B}

Where $B$ (Standard Block) is defined as:

B=P×RN B = \frac{P \times R}{N}
  • P (Population): Total population of the country (e.g., Japan: 124M)
  • N (Number): Total number of basic municipalities (Japan: 1,718)
  • R (Ratio): Target audience ratio (0.0 - 1.0)

By using $N=1718$ as a constant, we define "How many average towns could this project cover?"

2. The Verdict Logic (v2.0)

I codified the criteria to judge the "Social Implementation Stage."

Impact ($I$) Verdict Meaning
I < 1.0 Error Level 💀 Does not even cover 1 municipality.
1.0 <= I < 17 Localized ⚠️ Less than 1% national reach. Experimental.
17 <= I < 172 Penetration 🚀 1% - 10%. "One per classroom" level.
I >= 859 Social OS 👑 Over 50%. Infrastructure like water/electricity.

The Code: Building the CLI Tool 🛠️

I implemented this logic as a Python CLI tool. It uses argparse for easy input and pandas for analyzing financial datasets.

sbcm-calc: Simple Calculator

A tool to verify single metrics instantly.

def calculate_impact(value, population, target_ratio=1.0):
    municipalities = 1718
    standard_block = (population * target_ratio) / municipalities
    return value / standard_block

# Usage: 3,000 users vs 124M population
# Result: Impact = 0.04 (Verdict: Error Level)
Enter fullscreen mode Exit fullscreen mode

sbcm-audit: Budget Portfolio Analysis

This is the core feature. It loads a CSV of government spending (Financial Statements) and calculates the "Budget Distortion Index ($D_{index}$)".

Dindex=IbudgetIcoverage D_{index} = \frac{I_{budget}}{I_{coverage}}

If $D_{index}$ is extremely high, it means "High Cost, Low Reach"—a classic sign of Pork-barrel projects (wasteful spending).

Result Visualization (The Matrix):

  • Quadrant 1 (High Cost / High Reach): Infrastructure ✅
  • Quadrant 2 (Low Cost / High Reach): Innovation (Good DX) 💎
  • Quadrant 4 (High Cost / Low Reach): Distortion (Audit Required) 🚨

Demo: Auditing Kashiwa City 🇯🇵

I ran this tool against my city's financial report.

python budget_distortion_analyzer.py kashiwa_data.csv --pop 435000
Enter fullscreen mode Exit fullscreen mode

Output:

Project A (Tablets):   Budget $10M  | Impact 0.5  | Distortion 20.0  -> 🚨 Q4 (Distorted)
Project B (App):       Budget $2.5M | Impact 0.01 | Distortion 200.0 -> 🚨 Q4 (Distorted)
Project C (Station):   Budget $7M   | Impact 3.0  | Distortion 0.1   -> 💎 Q2 (Efficient)
Enter fullscreen mode Exit fullscreen mode

It successfully detected that the Tablet project and the App project were highly inefficient compared to infrastructure investments.

Tech Stack

  • Python 3.10+
  • Pandas: For data manipulation.
  • NumPy: For Monte Carlo simulations (to verify statistical robustness).
  • Matplotlib: For visualizing the distortion matrix.

Try It Yourself!

I open-sourced this project. You can find the Python scripts, AI prompts for parsing PDFs, and Google Sheets formulas in the repo.

If you are interested in Civic Tech, Data Journalism, or Open Government, please check it out and give it a ⭐!

👉 GitHub: Standard-Block-Comparison-Method

Let's debug our governments with code. 💻🏛️

Top comments (0)