The newest addition to the practice, built for the jobs that outgrow a spreadsheet: bigger files, repeatable pipelines, and the parts VBA was never meant to reach.
BlackTor's work is covered by client NDAs, so the examples below don't describe a real client, project or dataset. To keep things concrete, they're all set at "Ride Me Cycles": a fictitious multi-branch bike retailer invented for this site. The techniques are real; Ride Me Cycles and everything about it are not.
Status: the newest addition to the practice: no client-facing Python work yet. The examples below set out the kind of problem it's being built to solve.
Illustrative, per the note above: not real client work.
The problem: Ride Me Cycles' weekly P&L pack took an analyst two hours of manual Excel work before it could go out to head office.
The approach: A script reads the same source workbook, applies the same logic as a formula-driven pandas pipeline, and writes a formatted P&L report automatically, no macro required.
The result: Two hours of copy-paste becomes a script run before the kettle boils.
The problem: A folder of monthly branch sales exports needed the same product-code cleanup by hand every time, before anyone could trust the totals in them.
The approach: A script normalises product codes, fixes date formats and flags rows that fail validation, so cleanup happens once, in code, not every branch, every month, by eye.
The result: The same fifteen checks, run the same way, every branch, every month.
The problem: A handful of VBA macros needed to check live staff rota data held outside Excel's reach, and VBA's own tools weren't built for it.
The approach: A lightweight Python service sits behind the workbook, called over a local socket, handling the rota lookups VBA can't reach cleanly.
The result: The workbook keeps its familiar face; the heavy lifting moves somewhere better suited to it.
These mirror the joins on the SQL page exactly, against the same four Ride Me Cycles tables (Branches, Staff, Products, Sales), so the two pages can be read side by side: a pandas DataFrame merge covers the same ground as a SQL JOIN, a few differ only in which language does the work, and the last example shows the two disciplines meeting directly, with Python handing the join itself to the database rather than doing it in memory.
pd.merge() defaults to how='inner', which behaves exactly like SQL's INNER JOIN: a row only survives if its key finds a match in both frames. Chaining three merges, one per lookup table, is pandas' equivalent of the SQL page's three-table join, widening each sale with its branch, staff and product details before the date filter and sort run over the combined result.
how='left' keeps every row from staff even when recent_sales has nothing to offer it, filling the sales columns with NaN, the pandas equivalent of SQL's NULL. indicator=True adds a _merge column recording where each row's match came from ('both', 'left_only' or 'right_only'); filtering to left_only is pandas' version of the SQL page's WHERE s.sale_id IS NULL anti-join, isolating exactly the staff who don't appear in recent_sales at all. Filtering recent_sales down to the last 30 days before the merge, rather than after, matters the same way the SQL page's ON-clause-versus-WHERE-clause note does: filtering after a left join can silently drop the very unmatched rows this query exists to find.
Exactly the same two-step shape as the SQL page's BranchCategoryMargin query: the merge happens first, at row level, then groupby collapses the widened rows down to one per branch/category pair before the aggregate functions run. The two assign() calls read like the SQL query's calculated SELECT columns, added as their own step rather than folded into one long expression, which is usually easier to debug when a margin figure looks wrong: gross_profit and margin_pct can each be checked on their own.
Nothing stops the same window-function query from the SQL page being handed straight to the database and read back as a DataFrame with pd.read_sql(). Once the source tables are bigger than comfortably fits in memory, pushing the join, the grouping and the ranking down to the database and only pulling back the handful of already-ranked rows is usually faster than pulling every raw sale into pandas and doing the same work there; pandas earns its keep on what happens to top_sellers next, not on redoing a join the database was already built to do well.
Merging sales with itself on the columns that should identify a matching pair produces one row for every combination of two sales sharing that branch, product and date, with suffixes=('_a', '_b') keeping the two sides' other columns apart. staff_id_a < staff_id_b does the same double duty as the SQL page's self-join filter: it drops a row from matching against itself, and it drops the same pair appearing twice in mirrored order, leaving one row per genuinely distinct pair worth a human glance.
The sidebar carries the short version; this is the longer one, for whoever wants the detail behind it.
Pandas is built on top of numpy, and its vectorised operations push the work down into compiled C code that operates on an entire column at once. A Python-level loop, whether written as a for loop over the DataFrame or via iterrows or apply, has to go back through the Python interpreter for every single row: creating objects, looking up attributes, and paying that overhead thousands or millions of times over. The vectorised equivalent pays that overhead once, for the whole operation, which is where the speed difference comes from.
The practical effect is easy to see on something like a conditional column, for example flagging rows above a threshold. Written as a loop that appends to a list row by row, it might take minutes on a few hundred thousand rows. The same result using a boolean mask or numpy.where runs in a fraction of a second, because the comparison and the assignment both happen across the whole column in one pass rather than one value at a time.
Not every calculation vectorises cleanly, particularly logic where one row's result depends on the previous row's outcome in a way that is not a simple cumulative sum or shift. In those cases it is usually still worth vectorising everything that can be vectorised, and reserving a loop or apply only for the small part of the logic that genuinely needs it, rather than defaulting to row-by-row processing for the whole pipeline.
A virtual environment is an isolated Python interpreter with its own site-packages directory. Without one, every pip install runs against the single global environment shared by whatever else happens to be installed on that machine, whether that is another project, a one-off script from months ago, or a tool installed for an unrelated purpose.
The failure mode this avoids is a quiet one. One project depends on a particular library version, and a second, unrelated project on the same machine later gets upgraded, pulling in a newer version of that same library globally. If the newer version has changed a default, removed a method, or altered rounding behaviour, the first project keeps running without raising an error; it simply starts producing different output the next time it happens to run, often weeks after anyone touched the code that broke.
Setting up a virtual environment, whether via venv, virtualenv, or a tool such as poetry or conda, takes a couple of minutes and costs nothing to maintain beyond activating it before installing packages. It is worth doing even for what looks like a throwaway script, since throwaway scripts have a habit of being run again long after the fact, on a machine whose global environment has since moved on.
Type hints are not enforced at runtime by default; Python still executes an incorrectly typed call without complaint. What they do is give a static checker such as mypy, and an editor's autocomplete, something concrete to work with. A signature that names its parameter and return types tells a reader exactly what goes in and what comes back, without them having to read the function body or trace how it is called elsewhere to work that out.
The value shows up most on a function nobody has looked at in a while, including its original author. Without hints, working out whether a parameter expects a list of strings or a single string, or whether a return value is a DataFrame or a plain dictionary, means reading through the logic or, worse, running it and inspecting the result. With hints, that information is sitting on the signature itself.
Hints also move a category of bug earlier. Passing the wrong type into a function often does not fail immediately; it fails several calls later, inside some other function that was never expecting that type, at which point the error message points to the wrong place entirely. Running a static checker, even just occasionally or in a CI step, catches that mismatch at the call site instead, before the code has run at all.
Building a file path by concatenating strings with a forward slash, or worse a hardcoded backslash, bakes in an assumption about the operating system the code will run on. pathlib.Path replaces that with an object that knows how to join, resolve and inspect paths correctly for whichever platform is actually running the code, using the same overloaded slash operator to join components regardless.
This tends to surface at an awkward moment: a script written and tested on a Windows laptop, with paths built as string concatenation using a backslash separator, is moved onto a Linux server to run on a schedule via cron. On Linux the backslash is not a separator at all, so the constructed string is read as a single filename containing a literal backslash character, and the script fails to find a file that plainly exists.
Beyond avoiding that particular class of bug, pathlib also replaces a lot of manual string slicing that path handling otherwise involves. Getting a file's extension, parent folder, or stem, or iterating over files matching a pattern, are all direct methods and properties on the Path object rather than something to reconstruct with split and join each time.
Output sent to print goes to standard output, which for a script running on a schedule with nobody watching a terminal is frequently discarded, or at best sitting in a scheduler's own log that nobody thinks to check. The logging module is built for exactly this situation: it supports severity levels, timestamps on every line, and handlers that write to a file, including a rotating file handler that manages its own size, independently of whether a human happens to be present when it runs.
The difference matters most when something goes wrong overnight. A script that fails at two in the morning with only print statements leaves no trace by the time anyone looks the next day; the terminal it ran in is long gone. The same script using proper logging with a file handler leaves a timestamped record of each step it completed and the full traceback of whatever raised the exception, so the next morning's check is reading a log rather than guessing.
Logging levels also mean diagnostic detail does not have to be stripped out for production use. Debug-level messages can stay in the code permanently, filtered out of normal runs but available by turning the level up when something needs investigating, while warning and error level messages can be routed through additional handlers, for example to send an alert, without touching the rest of the logging calls.
A with block relies on a resource implementing the context manager protocol: an enter method that runs on entry and an exit method that is guaranteed to run on exit, including when an exception is raised partway through the block. That guarantee is the entire point; it is effectively a try/finally baked into the object itself, rather than something the calling code has to remember to write.
A manual open followed by a close further down the function does not have that guarantee. If an error is raised somewhere between the two calls, close is simply never reached, and the file handle or database connection stays open. On a script that runs once this rarely matters, but on a long-running or frequently invoked unattended process, each leaked handle adds up, and it is possible to exhaust the operating system's limit on open file descriptors, at which point every subsequent open call starts failing too, often with an error that gives no hint the real cause was several functions earlier.
The same protocol is available for resources beyond files and database connections. Using contextlib's context manager decorator, a temporary directory, a database transaction that should roll back on error, or any custom setup and teardown pair can be wrapped the same way, so cleanup logic lives in one place and applies consistently regardless of how the code inside the block exits.
A requirements file with unpinned or loosely pinned entries tells pip to install whatever the latest compatible version happens to be at install time. That is fine the day the environment is first built, but it means the environment recreated from that same file six months later can end up with entirely different library versions, including transitive dependencies that were never listed explicitly at all. A proper lockfile records the exact versions actually resolved and installed, not just the top-level constraints.
The failure mode this avoids is subtle rather than a hard crash. A report produced in January with one set of library versions gets rerun in July after a fresh install pulls in newer ones; a default parameter in a widely used function has changed between those versions, or a rounding behaviour has shifted slightly, and the script still runs without error but produces a number that does not quite match the original. Tracing that back to a library version change, months after the fact, is far harder than checking a lockfile would have been.
This matters most wherever a result needs to be reproducible on demand: an audit, a query about how a figure was calculated, or simply rerunning last quarter's numbers to sanity check the current ones. Paired with a per-project virtual environment, a checked-in lockfile means the environment that produced a given output can be recreated exactly, rather than approximately, however much later it is needed.
Checking the shape, types and expected ranges of incoming data before any processing begins means a source file that is wrong in some way gets caught at the point it enters the pipeline, rather than being processed regardless and producing a result that only looks wrong once someone reads the finished output.
Without that check, a source csv missing a column, or with a numeric column that has come through as text because of a stray formatting character in one row, does not necessarily raise an error at all. Pandas will often read it, arithmetic on a text column may coerce or produce a missing value silently, and that propagates through every downstream calculation. The first anyone hears of it is a total that looks implausible in a finished report, at which point tracing it back to the original source file means retracing the entire pipeline.
Validation does not need to be elaborate to be useful: an assertion on the expected columns, dtypes and a sensible value range for each, run immediately after the data is loaded, is often enough. Libraries such as pandera or pydantic can formalise this into a reusable schema definition where the checks are more involved, but the underlying principle is the same either way, sometimes summarised as failing fast: catch the problem as close as possible to where it entered, while it is still obvious what the source of it was.
A script that always needs a different input each time it runs is easier and safer to use with a command-line interface, built with argparse or click, than with a constant edited at the top of the file before each run. These libraries handle parsing, type conversion and default values, and generate help text automatically, so the script's inputs are documented by running it with a help flag rather than by reading the source.
The edited-constant approach fails in a fairly ordinary way: someone runs the script for February, forgets to change the constant back from January, and gets a result for the wrong month with no error to flag it, because nothing about editing a string constant looks wrong. A CLI removes that step entirely; the month or file is supplied as an argument at run time, so there is no leftover edit sitting in the file from the last run to be forgotten.
A CLI also makes a script usable in ways a hardcoded constant does not. It can be called from another script or from a scheduler with different arguments each time, without touching the file at all, and the same script can be run for several inputs in a loop from the command line, which is awkward to do safely when the input is baked into the source.
A small number of automated tests around the core logic of a script, feeding known inputs into a function and checking the output against an expected result, run automatically and take a few seconds, but they cover a change that manual review often does not: whether a later edit to that function still produces the same answer it always did for the cases that matter.
The usual scenario is a function that calculates some aggregation or rate getting modified to handle a new requirement, and the change to the logic, entirely reasonable on its own, has a side effect on an edge case that was not being thought about at the time: an empty input, a value of zero, or a rounding boundary. Without a test sitting on that function, the first sign of the problem is a reviewer, or the person relying on the report, noticing a number looks slightly off, at which point the work of tracking down which change caused it, and redoing whatever depended on it, is considerably more time than writing the test would have taken.
The tests do not need to cover every function or every branch to be worth having; a handful around the calculations that other things depend on is usually enough to catch the regressions that matter most. As a side effect, the tests themselves also serve as a plain record of what the function is meant to do for a given input, which is useful on its own the next time someone, including its original author, needs to change it.