Python Pandas for Finance: Cleaning and Analysing Data

Pandas is the reason most finance professionals who learn Python end up using it regularly — it turns the kind of repetitive Excel cleanup that eats a whole afternoon into a script that runs in seconds and, crucially, runs the same way every time. This is a practical starting point for cleaning and analysing finance data with pandas, not a general Python tutorial.

Getting your data in

Most finance data starts life as a CSV export or an Excel file, and pandas reads both directly into a DataFrame — the table-like structure everything else works with:

import pandas as pd
df = pd.read_csv("transactions.csv")
# or: df = pd.read_excel("transactions.xlsx", sheet_name="Data")

The first thing worth running on any new dataset is df.info() and df.head() — the first shows you column names, data types, and how many values are missing in each column; the second shows you the first few rows so you can sanity-check that the import looks right before doing anything else.

Cleaning: the part that takes the longest in Excel

Real finance exports are messy — inconsistent date formats, trailing whitespace in account codes, amounts stored as text with currency symbols attached. Pandas handles this predictably:

df['amount'] = df['amount'].replace('[£$,]', '', regex=True).astype(float)
df['account_code'] = df['account_code'].str.strip()
df['date'] = pd.to_datetime(df['date'], errors='coerce')

The errors='coerce' argument on the date conversion is worth knowing early: instead of the whole operation failing on one bad date, it converts anything it can't parse to NaT (pandas' "not a time" value), so you can find and fix the problem rows with df[df['date'].isna()] rather than hunting through the raw file manually.

Filtering and grouping — your VLOOKUP and PivotTable replacement

Filtering to a specific cost centre or date range looks like this:

q1 = df[(df['date'] >= '2026-01-01') & (df['date'] <= '2026-03-31')]
marketing = df[df['cost_centre'] == 'Marketing']

And where Excel would need a PivotTable, pandas uses groupby:

summary = df.groupby('cost_centre')['amount'].sum().sort_values(ascending=False)

That one line replaces dragging fields into a PivotTable, and — unlike a PivotTable — it's a piece of code you can re-run against next month's export without rebuilding anything.

Merging data from multiple sources

Just like a VLOOKUP or a SQL join, merge() combines data from two DataFrames on a shared column — matching a transactions table to a cost centre reference table, for example:

df = df.merge(cost_centres, on='cost_centre_id', how='left')

The how='left' argument keeps every row from your main table even where there's no match, which — as with a LEFT JOIN in SQL — is usually what you want for a data quality check rather than silently dropping unmatched rows.

Exporting back out

Once the analysis is done, get it back into a format colleagues can use without installing Python:

summary.to_excel("cost_centre_summary.xlsx")

Handling duplicates and reconciliation checks

Duplicate rows are one of the most common data quality problems in finance exports — the same invoice pulled twice from a system integration, or a transaction that appears in both a source file and a correction file. Pandas surfaces these in one line:

duplicates = df[df.duplicated(subset=['transaction_id'], keep=False)]

That returns every row involved in a duplicate, not just the second occurrence, which makes it easier to see which version is correct before deciding which to drop with df.drop_duplicates().

Pandas is also useful for the kind of two-way reconciliation finance teams do at month-end — comparing a general ledger extract against a subledger, for example. Merging with indicator=True shows you exactly which rows only exist on one side:

check = gl.merge(subledger, on='reference', how='outer', indicator=True)
only_in_gl = check[check['_merge'] == 'left_only']
only_in_subledger = check[check['_merge'] == 'right_only']

That's the same logic as a FULL JOIN in SQL, applied without leaving your Python environment — useful if the rest of your cleaning and analysis is already happening there.

A realistic first project

Rather than starting with a tutorial dataset, the fastest way to build confidence is to take one recurring task you already do manually — reconciling two exports, summarising spend by cost centre, or flagging transactions above a threshold for review — and rebuild just that one task in pandas. It's small enough to finish in an afternoon, and because it's your own real data, you'll immediately hit the messy-data problems (bad dates, inconsistent codes, duplicates) that a generic tutorial dataset won't show you.

Why this is worth the setup time

The real payoff isn't any single script — it's that a cleaning-and-summarising process you've built once in pandas can be re-run on next month's data in seconds, with the same logic applied consistently every time. That reliability is usually worth more to a finance team than the raw speed.

If you're completely new to Python, start with Python for Accountants: Getting Started with Data Analysis in Finance before this guide. And for how Python fits alongside the tools you already use, 5 Ways Python is Revolutionizing the Finance Industry covers the wider picture. To build these skills into a structured CPD pathway, browse Learnsignal's CPD courses.

This sits on top of two things worth having first: getting started with Python as an accountant and the Excel techniques pandas replaces. Also worth reading: data analytics for accountants and the PL-300 certification route.