24 Years of Municipal Finance
Wisconsin’s towns, cities, and villages each file an annual Municipal Financial Report (MFR). Two decades of those filings are a goldmine for anyone studying local government—but they arrive as a sprawl of inconsistent Excel and legacy spreadsheet files, with schemas that drift from year to year, data for a single year split across multiple sheets, and no common key to tie them together. Before any analysis is possible, someone has to make them speak the same language.
This project is that essential work: a Python pipeline that turns 24 years of messy MFR files into one clean, analysis-ready dataset.
What the pipeline does
- Ingests a directory of mixed
.xls/.xlsxfiles spanning 1998–2021, filtering out the noise (stray docx, zip, and misc files). - Normalizes each year’s idiosyncratic layout into a consistent structure, including cleaning up messy file and field names.
- Stitches together years whose data is scattered across several sheets into a single record per municipality-year.
- Combines every year into one long, tidy data structure keyed to a county-and-type mapping, so municipalities can be compared across time and geography.
Why it matters
Reliable public-policy analysis lives or dies on the quality of the underlying data. By making decades of fragmented municipal filings queryable in one place, this pipeline turns a pile of spreadsheets into something a researcher can actually ask questions of—how local revenues, expenditures, and tax rolls shift over time and differ between towns, villages, and cities. It’s the kind of reproducible data-engineering groundwork that makes everything downstream possible.
Asking the data two questions
To show what the cleaned dataset makes possible, I looked at one slice of municipal life across the full 24 years—how Wisconsin communities pay for their sewer systems.
Which cities are ramping up sewer spending fastest
Total annual sewer spending for the eight fastest-growing cities, 1998–2021
Do bigger cities spend more per resident?
Sewer spending per resident vs. population · each municipality averaged over 1998–2021