How to Automate Monthly Government Reports with Python
Every local government data professional knows the report. It comes due at the end of every month without exception. It pulls data from three or four different systems — the call center platform, the ERP system, the work order management tool, maybe a separate billing export. Someone spends two to four hours combining those exports, reformatting the columns, calculating the metrics, and building the summary table that goes to senior leadership.
Then next month they do it again. Identically. Manually.
This is exactly the kind of work Python was built to eliminate. A monthly report that combines multiple system exports, calculates standard metrics, and formats output for leadership is a perfect automation candidate — predictable inputs, consistent calculations, repeatable output. Once automated, that two-to-four hour monthly task becomes a script that runs in seconds.
This article walks through how to build that automation for a realistic local government scenario: a monthly leadership report that pulls call center performance data, billing and revenue figures, and work order metrics from separate source files and combines them into a single formatted Excel output.
If you are new to Python in a government data context, our Python for urban data analysis guide covers the foundational concepts — pandas DataFrames, reading CSV and Excel files, and basic data manipulation — that this article builds on. If you are already comfortable with pandas basics, you are ready to proceed.
Why This Problem Is Hard Without Python
The manual version of this report has three distinct failure modes that anyone who has built it knows well.
The first is version fragility. Each month's report is a manually assembled workbook. One wrong paste, one accidentally deleted formula, one column header that does not quite match between the call center export and last month's template creates errors that may not be caught until the report reaches leadership — or after.
The second is time cost. Two to four hours per month is 24 to 48 hours per year — the equivalent of three to six full working days spent doing the same task repeatedly. That is analyst time that could be spent on actual analysis rather than data assembly.
The third is institutional knowledge dependency. The person who knows how to build the report is the only person who can build it. If they are on leave when the report is due, or if they leave the organization, the institutional knowledge of which exports to pull, how to combine them, and what the calculations are leaves with them.
A Python automation script solves all three problems simultaneously. The script is the documentation of exactly how the report is built. It produces consistent output every time it runs. And anyone with basic Python familiarity can run it or modify it without needing to reconstruct the institutional knowledge from scratch.
The Libraries You Need
Install the following libraries before writing any code. Open your terminal or command prompt and run:
python
pip install pandas openpyxlpandas handles all the data manipulation — reading your source files, cleaning column inconsistencies, calculating metrics, and combining data from multiple sources into a single DataFrame. If you have used Excel pivot tables and SUMIF formulas, you already understand conceptually what pandas does — it performs the same operations in code rather than through menus and mouse clicks.
openpyxl handles the Excel output — writing your combined data to a formatted .xlsx file with professional formatting, column widths, and multiple sheets. pandas can write basic Excel files on its own, but openpyxl gives you full control over formatting, making the output look like a report rather than a raw data dump.
Step 1: Define Your Source Files
The first step in any report automation script is to clearly define where your data is coming from. For a local government monthly leadership report combining call center data, billing and revenue data, and work order data, your source files typically look like this:
python
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter
from datetime import datetime
# Define source file paths
# Replace these with the actual paths where your exports are saved each month
CALL_CENTER_EXPORT = 'exports/call_center_january_2026.csv'
BILLING_EXPORT = 'exports/billing_revenue_january_2026.xlsx'
WORK_ORDER_EXPORT = 'exports/work_orders_january_2026.xlsx'
# Define the report month for labeling
REPORT_MONTH = 'January 2026'
# Define the output file path
OUTPUT_FILE = f'reports/Leadership_Report_{REPORT_MONTH.replace(" ", "_")}.xlsx'Two things worth noting here. First, defining all file paths at the top of the script rather than buried inside functions means that each month the only thing you need to change is the file names in these four lines — not hunt through the script for every place a file path appears. Second, the output filename is generated automatically from the report month variable, which means you never accidentally overwrite last month's report.
Step 2: Load and Clean Your Call Center Data
Call center platform exports typically come as CSV files with one row per call. The column headers vary between platforms but the data structure is consistent — one row per call, with fields for timestamp, duration, status, and type.
python
def load_call_center_data(filepath):
"""Load and clean call center export."""
df = pd.read_csv(filepath)
# Standardize column names — adjust these to match your platform's export headers
df.columns = df.columns.str.strip().str.lower().str.replace(' ', '_')
# Ensure key fields are correct types
df['hold_time_sec'] = pd.to_numeric(df['hold_time_sec'], errors='coerce').fillna(0)
df['abandon_time_sec'] = pd.to_numeric(df['abandon_time_sec'], errors='coerce').fillna(0)
df['answered'] = df['answered'].str.strip().str.title() # Standardize Yes/No
return df
def calculate_call_metrics(df):
"""Calculate KPIs from raw call data."""
total_calls = len(df)
calls_answered = len(df[df['answered'] == 'Yes'])
# Service level: calls answered within 20 seconds
service_level = len(
df[(df['answered'] == 'Yes') & (df['hold_time_sec'] <= 20)]
) / total_calls if total_calls > 0 else 0
# Abandonment rate: calls abandoned after 150 seconds
abandonment_rate = len(
df[(df['answered'] == 'No') & (df['abandon_time_sec'] > 150)]
) / total_calls if total_calls > 0 else 0
avg_handle_time = df[df['answered'] == 'Yes']['call_duration_sec'].mean()
return {
'Total Calls': total_calls,
'Calls Answered': calls_answered,
'Service Level (≤20 sec)': f'{service_level:.1%}',
'Abandonment Rate (>150 sec)': f'{abandonment_rate:.1%}',
'Avg Handle Time (sec)': round(avg_handle_time, 0) if not pd.isna(avg_handle_time) else 0
}The str.strip().str.title() line on the Answered column is the kind of data cleaning detail that prevents silent errors in your metrics. If your call platform exports "YES", "yes", and "Yes" inconsistently across different months — which many do — your COUNTIF equivalent in pandas will miss records that do not match the expected case. Cleaning it explicitly before calculation eliminates that error category entirely.
The service level and abandonment rate thresholds — 20 seconds and 150 seconds — match the operational standards discussed in our utility billing KPI guide and our call center dashboard tutorial. Change these values to match your department's specific targets.
Step 3: Load and Clean Your Billing and Revenue Data
Billing and revenue data typically comes from an ERP system export as an Excel file. The structure varies significantly between platforms — Tyler Munis, SAP, and Oracle all export differently — but the key fields are consistent: account identifier, billing amount, payment amount, account status, and date.
python
def load_billing_data(filepath):
"""Load and clean billing ERP export."""
df = pd.read_excel(filepath, sheet_name='Raw Data')
# Standardize column names
df.columns = df.columns.str.strip().str.lower().str.replace(' ', '_')
# Ensure numeric fields are correct types
df['amount_billed'] = pd.to_numeric(df['amount_billed'], errors='coerce').fillna(0)
df['amount_paid'] = pd.to_numeric(df['amount_paid'], errors='coerce').fillna(0)
# Standardize account status values
df['account_status'] = df['account_status'].str.strip().str.title()
return df
def calculate_billing_metrics(df):
"""Calculate billing KPIs from raw account data."""
total_billed = df['amount_billed'].sum()
total_collected = df['amount_paid'].sum()
collection_rate = total_collected / total_billed if total_billed > 0 else 0
# Delinquency — accounts with status indicating past due or worse
delinquent_statuses = ['Past Due', 'Delinquent', 'Payment Arrangement',
'Hardship', 'Turned Off']
delinquent_count = len(df[df['account_status'].isin(delinquent_statuses)])
total_accounts = len(df)
delinquency_rate = delinquent_count / total_accounts if total_accounts > 0 else 0
# Turn offs — water accounts only, excluding stormwater
water_accounts = df[df['account_type'] == 'Water']
turn_offs = len(water_accounts[water_accounts['turn_off_executed'] == 'Yes'])
turn_off_rate = turn_offs / len(water_accounts) if len(water_accounts) > 0 else 0
outstanding_balance = total_billed - total_collected
return {
'Total Billed': f'${total_billed:,.2f}',
'Total Collected': f'${total_collected:,.2f}',
'Outstanding Balance': f'${outstanding_balance:,.2f}',
'Collection Rate': f'{collection_rate:.1%}',
'Delinquency Rate': f'{delinquency_rate:.1%}',
'Turn Off Rate (Water Only)': f'{turn_off_rate:.1%}'
}The stormwater exclusion in the turn-off rate calculation is the kind of operational detail that distinguishes a script written by someone inside a utility billing department from a generic template. As discussed in our utility billing dashboard guide, stormwater-only accounts cannot be disconnected — including them in the turn-off rate denominator understates the real disconnection risk among eligible accounts.
Step 4: Load and Clean Your Work Order Data
Work order data often comes from a combination of sources — an ERP module for certain request types and a separate Excel tracker for others. Python handles this gracefully by loading both and combining them before calculation.
python
def load_work_order_data(filepath):
"""Load and clean work order export."""
df = pd.read_excel(filepath)
# Standardize column names
df.columns = df.columns.str.strip().str.lower().str.replace(' ', '_')
# Standardize key text fields
df['status'] = df['status'].str.strip().str.title()
df['priority_level'] = df['priority_level'].str.strip().str.title()
df['within_target'] = df['within_target'].str.strip().str.title()
return df
def calculate_work_order_metrics(df):
"""Calculate work order KPIs."""
total_requests = len(df)
open_requests = len(df[df['status'].isin(['Open', 'In Progress'])])
completed = len(df[df['status'].isin(['Completed', 'Closed - No Action Required'])])
# On-time rate for completed requests
on_time = len(df[df['within_target'] == 'Yes'])
exceeded = len(df[df['within_target'] == 'No'])
on_time_rate = on_time / (on_time + exceeded) if (on_time + exceeded) > 0 else 0
# Emergency requests specifically
emergency_total = len(df[df['priority_level'] == 'Emergency'])
emergency_on_time = len(
df[(df['priority_level'] == 'Emergency') & (df['within_target'] == 'Yes')]
)
emergency_rate = emergency_on_time / emergency_total if emergency_total > 0 else 0
return {
'Total Work Orders': total_requests,
'Open / In Progress': open_requests,
'Completed': completed,
'On-Time Rate': f'{on_time_rate:.1%}',
'Emergency On-Time Rate': f'{emergency_rate:.1%}'
}Step 5: Combine and Write the Report
With your three metric dictionaries calculated, the final step combines them into a formatted Excel report that leadership can open without knowing anything about Python.
python
def write_leadership_report(call_metrics, billing_metrics, wo_metrics,
report_month, output_path):
"""Write all metrics to a formatted Excel leadership report."""
# Convert metric dictionaries to DataFrames
call_df = pd.DataFrame(list(call_metrics.items()),
columns=['Metric', 'Call Center'])
billing_df = pd.DataFrame(list(billing_metrics.items()),
columns=['Metric', 'Billing & Revenue'])
wo_df = pd.DataFrame(list(wo_metrics.items()),
columns=['Metric', 'Work Orders'])
# Write to Excel with multiple sheets
with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
call_df.to_excel(writer, sheet_name='Call Center', index=False)
billing_df.to_excel(writer, sheet_name='Billing Revenue', index=False)
wo_df.to_excel(writer, sheet_name='Work Orders', index=False)
# Apply formatting to each sheet
wb = writer.book
for sheet_name in wb.sheetnames:
ws = wb[sheet_name]
# Format header row
for cell in ws[1]:
cell.font = Font(bold=True, color='FFFFFF')
cell.fill = PatternFill(start_color='0A2E2A', fill_type='solid')
cell.alignment = Alignment(horizontal='center')
# Auto-fit column widths
for col in ws.columns:
max_len = max(len(str(cell.value or '')) for cell in col)
ws.column_dimensions[get_column_letter(col[0].column)].width = max_len + 4
# Alternate row shading
for row_idx, row in enumerate(ws.iter_rows(min_row=2), start=2):
if row_idx % 2 == 0:
for cell in row:
cell.fill = PatternFill(start_color='F2F2F2', fill_type='solid')
print(f'Report written to: {output_path}')
# ── Run the full automation ───────────────────────────────────────────────────
if __name__ == '__main__':
print(f'Generating {REPORT_MONTH} leadership report...')
call_df = load_call_center_data(CALL_CENTER_EXPORT)
billing_df = load_billing_data(BILLING_EXPORT)
wo_df = load_work_order_data(WORK_ORDER_EXPORT)
call_metrics = calculate_call_metrics(call_df)
billing_metrics = calculate_billing_metrics(billing_df)
wo_metrics = calculate_work_order_metrics(wo_df)
write_leadership_report(
call_metrics, billing_metrics, wo_metrics,
REPORT_MONTH, OUTPUT_FILE
)
print('Done.')When you run this script it reads your three source files, calculates all metrics, and writes a formatted three-sheet Excel workbook to your reports folder. The entire process takes under five seconds for typical government dataset sizes.
Step 6: Schedule It to Run Automatically
The script above automates the calculation and formatting. The final step is automating the execution — so the report generates itself on the first working day of each month without anyone having to remember to run it.
On a Mac, the built-in scheduling tool is launchd. Create a plist file in your Library/LaunchAgents folder that specifies when to run the script and points to your Python executable and script file. On Windows, Task Scheduler provides the same capability through a graphical interface.
For most government environments, a simpler approach is scheduling a calendar reminder for the first of each month and running the script manually — which takes ten seconds rather than two to four hours. Full automated scheduling is the next step once you have confirmed the script produces reliable output across several monthly cycles.
Making the Script Resilient
A report automation script that fails silently on the month when your call center export has a new column header is worse than no automation at all — because it fails at exactly the moment you need it most. Three practices make your script resilient against the format changes that government system exports produce periodically.
Add column validation at load time. After reading each source file, check that the expected columns are present before proceeding:
python
required_columns = ['call_id', 'answered', 'hold_time_sec', 'abandon_time_sec']
missing = [col for col in required_columns if col not in df.columns]
if missing:
raise ValueError(f'Missing columns in call center export: {missing}')Log what the script processed. Write a brief log entry each time the script runs — date, source files processed, record counts loaded, and output file written. When something looks wrong in the report, the log tells you immediately whether the issue is in the source data or in the calculation.
Keep the previous month's output. Never overwrite last month's report with this month's. The automated filename with the month and year in it handles this naturally — but make it an explicit practice to archive monthly outputs in a dated folder structure.
From Manual Assembly to Analytical Work
The monthly report described in this article — call center data, billing revenue, work order metrics, combined into a formatted leadership summary — is the kind of work that consumes hours of analyst time every month in government departments across the country. It is important work. Leadership needs it. But the value is in the information it contains, not in the manual assembly process that produces it.
Python automation does not eliminate the analyst's role in that report. It eliminates the assembly work and leaves the analytical work — interpreting the metrics, identifying the story behind the numbers, flagging the issues that need leadership attention. That is a meaningful shift in how a government data analyst spends their time — and it compounds across every repeating task that gets automated.
As explored in our complete Python for urban data analysis guide, report automation is one of the highest-leverage first Python projects because the return is immediate, measurable, and recurring. Build this script once. Run it for twelve months. The time saved is a full working week returned to analytical work that actually requires human judgment.
Ready to start with pre-built government data scripts?
The Python Government Scripts Pack — launching Month 7 — will include five ready-to-run Python scripts for common government data tasks including this monthly report automation script, a Census API data puller, a billing data analyzer, and more. Join the City Data Intelligence newsletter to be notified at launch.
