Python for Urban Data Analysis: A Beginner's Guide
If your data work currently lives entirely in Excel, learning Python can feel like an unnecessary detour. Excel handles pivot tables, formulas, and charts perfectly well — so why learn an entirely new tool?
The honest answer is that Excel and Python are not competitors. They solve different problems. Excel is excellent for ad hoc analysis, quick calculations, and sharing results with non-technical colleagues. Python becomes valuable the moment your work involves repetitive tasks, large datasets that slow Excel down, data scattered across multiple systems that need to be combined, or any process you find yourself doing the same way every week.
This guide introduces Python for exactly that situation — a local government or urban data professional who is comfortable in Excel and wants to understand where Python actually helps, without pretending you need a computer science degree to get there.
Why Python Matters for Government Data Work
Local government data professionals work with a wider variety of data sources than almost any other field. Billing system exports, call center platform data, GIS shapefiles, Census Bureau datasets, work order systems, and budget spreadsheets all need to be cleaned, combined, and analyzed — often using slightly different formats and structures every time.
Python excels at handling everything from small Excel files to massive datasets, and in 2026 the core skills that matter remain constant: understanding data, asking the right questions, and communicating insights clearly. For government professionals specifically, Python earns its place in your toolkit through three capabilities Excel struggles with: automating tasks you currently do manually every week, combining and converting data between systems that do not naturally talk to each other, and handling datasets too large for Excel to process smoothly. Global Market Insights
You do not need to replace Excel with Python. You need to know when reaching for Python saves you time that Excel cannot.
The One Library That Matters Most: pandas
If you learn nothing else from Python for data work, learn pandas. Pandas is arguably the most important Python package for data manipulation and exploratory data analysis.
Pandas revolves around two primary data structures — a one-dimensional Series and a two-dimensional DataFrame — and a DataFrame is essentially a table very similar to an Excel spreadsheet, with rows and columns you can filter, sort, and calculate against. If you can think in terms of an Excel spreadsheet, you can think in terms of a pandas DataFrame. The mental model transfers directly — you are simply writing code instead of clicking through menus. MarketsandMarkets
Setting Up Python on Your Computer
Before writing any code you need Python installed along with the pandas library.
Step 1 — Install Python
Go to python.org and download the latest version for your operating system. During installation on Windows, check the box that says "Add Python to PATH" — this is the single most common setup mistake and skipping it causes confusing errors later.
Step 2 — Install a code editor
Visual Studio Code, free from Microsoft, is the standard choice. It works on Windows and Mac and has excellent built-in support for Python.
Step 3 — Install pandas
Open a terminal or command prompt and type:
pip install pandas
This downloads and installs the pandas library so your code can use it.
Step 4 — Verify your installation
Create a new file called test.py and type:
python
import pandas as pd
print(pd.__version__)Run the file. If a version number prints without errors, you are ready to begin.
Your First Real Task: Reading an Excel File
Most government data work starts the same way Excel work does — with a file someone exported from a system. Here is how to load that file into Python:
python
import pandas as pd
df = pd.read_csv('call_center_data.csv')
print(df.head())That read_csv line does the same thing as opening a file in Excel — except instead of seeing it visually, you now have it loaded into a variable called df that you can manipulate with code. The head() function shows you the first five rows, just to confirm the data loaded correctly. A typical workflow looks like loading the file, then checking df.head(), df.info(), and df.describe() to understand what you are working with before doing anything else. Global Market Insights
If your file is an Excel workbook rather than a CSV, the process is nearly identical:
python
df = pd.read_excel('billing_data.xlsx', sheet_name='Raw Data')Five Tasks Every Government Data Professional Should Learn First
1. Filtering rows — the pandas equivalent of an Excel filter
python
delinquent = df[df['Account Status'] == 'Delinquent']This creates a new table containing only delinquent accounts — equivalent to applying a filter in Excel, but reusable in code rather than manually re-clicking every time.
2. Counting and grouping — the pandas equivalent of a pivot table
python
status_counts = df.groupby('Account Status')['Account ID'].count()This counts how many accounts fall into each status category — the same result you would get from a pivot table in Excel, but instantly recalculable any time your underlying data changes.
3. Combining multiple files — the task Excel struggles with most
python
jan_data = pd.read_csv('january_calls.csv')
feb_data = pd.read_csv('february_calls.csv')
combined = pd.concat([jan_data, feb_data])This is where Python earns its place. Combining twelve months of call center exports into a single dataset for annual analysis is tedious and error-prone in Excel. In Python it is three lines of code that work identically every time you run them.
4. Cleaning inconsistent data
python
df['Answered'] = df['Answered'].str.strip().str.title()Government data exports are rarely perfectly clean. This line removes extra spaces and standardizes capitalization in a column — fixing the kind of inconsistency that silently breaks Excel formulas and COUNTIF functions without any obvious error message.
5. Exporting your results back to Excel
python
status_counts.to_excel('summary_report.xlsx')Python does not need to replace the tools your colleagues already use. Once your analysis is complete, export the results back into an Excel file that anyone in your department can open and review.
A Realistic First Project
The best way to learn is on a real task. Start small — pick a real-world dataset today, whether sales, customer, or public government data, and begin practicing, because consistency beats perfection.
A good first project for a government data professional is automating a report you currently build manually. If you spend thirty minutes every Monday combining the previous week's call center export, calculating service level percentage, and formatting it for your supervisor — that exact task is an ideal first Python script. It has clear inputs, a clear desired output, and you already know what correct looks like because you have done it by hand many times.
Write the script in small pieces. Load the data. Print it to confirm it loaded correctly. Calculate one metric. Print that. Calculate the next metric. Build incrementally rather than trying to write the entire script at once.
Where Python Fits Alongside PowerBI and Excel
Python, Excel, and PowerBI are not competing tools — they serve different stages of the same workflow. Python excels at the unglamorous work of cleaning, combining, and preparing data from multiple inconsistent sources. PowerBI excels at presenting that prepared data visually to non-technical stakeholders. Excel remains excellent for quick ad hoc analysis and sharing simple results with colleagues who will never install Python.
A realistic government data workflow often looks like this: Python pulls and cleans data from multiple systems, the cleaned output feeds into a PowerBI dashboard for ongoing monitoring, and Excel handles the one-off analysis requests that come up between dashboard refreshes. Learning Python does not mean abandoning the tools you already know — it means adding a capability that handles the parts of your job those tools were never designed for.
Final Thoughts
You do not need to become a software engineer to benefit from Python. You need to recognize the specific moments in your work — the repetitive monthly report, the data combination task across multiple exports, the inconsistent formatting that breaks your Excel formulas — where a small amount of code saves a meaningful amount of time.
Start with pandas. Start with a task you already understand how to do by hand. Build the script in small, testable pieces. The skill compounds quickly once you have automated your first real report.
Once you have your data cleaned and ready, the next step is often turning it into a dashboard your team can actually use.
Ready to build dashboards for your department?
The Local Government Dashboard Template Pack includes three Excel dashboards built specifically for city analysts and utility managers — call center performance, utility billing, and public works service requests. Each template comes pre-loaded with fictional data so you can see it working immediately.
