DEV Community: Adam

Cleaning Messy CSVs in pandas: A Practical Cheat Sheet

Adam — Tue, 29 Sep 2026 08:43:20 +0000

Real-world CSVs are almost never clean. Column names have trailing spaces, dates come in five different formats, numbers arrive as text with dollar signs, and there's always a "N/A" hiding where a number should be. Before you can analyze anything, you have to fix all of that.

This is the set of pandas moves I run through on nearly every messy file. Keep it handy — it covers maybe 80% of the cleaning you'll ever need.

Load it defensively

Half of cleaning is loading the file without pandas silently guessing wrong.

import pandas as pd

df = pd.read_csv(
    "data.csv",
    dtype=str,              # read everything as text first, convert on purpose later
    skipinitialspace=True,  # strip spaces after delimiters
    na_values=["", "NA", "N/A", "null", "-", "None"],
)

Reading everything as strings up front sounds counterintuitive, but it stops pandas from turning a ZIP code like 07001 into the number 7001. You convert types deliberately once you know what each column actually is.

Tidy up column names

Messy headers break everything downstream. Normalize them once.

df.columns = (
    df.columns
    .str.strip()          # remove leading/trailing spaces
    .str.lower()          # consistent casing
    .str.replace(r"\s+", "_", regex=True)  # spaces -> underscores
    .str.replace(r"[^\w]", "", regex=True) # drop punctuation
)

Now " Customer Name " becomes customer_name, and you can use clean attribute access like df.customer_name.

Strip whitespace from the values too

Header spaces are obvious; value spaces are the ones that silently ruin your groupby.

str_cols = df.select_dtypes(include="object").columns
df[str_cols] = df[str_cols].apply(lambda s: s.str.strip())

"New York" and "New York " look identical on screen but count as two different groups until you do this.

Fix numbers stored as text

Currency and percentages usually arrive as strings.

df["price"] = (
    df["price"]
    .str.replace(r"[$,]", "", regex=True)  # remove $ and thousands commas
    .astype(float)
)

df["rate"] = df["rate"].str.rstrip("%").astype(float) / 100

"$1,299.00" becomes 1299.0, and "8.5%" becomes 0.085. If some rows won't convert, use pd.to_numeric(df["price"], errors="coerce") to turn the bad ones into NaN instead of crashing.

Parse dates that come in many formats

df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce")

errors="coerce" is the important part — anything unparseable becomes NaT (missing) instead of raising an exception. After parsing, check how many failed:

print(df["order_date"].isna().sum(), "dates failed to parse")

If that number is high, your column probably mixes formats and you may need format= or dayfirst=True.

Handle missing values on purpose

Don't blindly drop or fill. Decide per column.

# See where the gaps are
print(df.isna().sum())

# Fill categorical gaps with a real label
df["region"] = df["region"].fillna("unknown")

# Fill numeric gaps with the median (robust to outliers)
df["age"] = df["age"].fillna(df["age"].median())

# Drop rows only when a truly required field is missing
df = df.dropna(subset=["customer_id"])

Filling a category with "unknown" is honest; filling it with a made-up number is not. Keep the two straight.

Kill duplicate rows

before = len(df)
df = df.drop_duplicates()
print(f"Removed {before - len(df)} exact duplicates")

# Or dedupe on a business key, keeping the most recent
df = df.sort_values("order_date").drop_duplicates(subset="order_id", keep="last")

Exact-duplicate removal is safe. Key-based dedup is where you decide which copy survives — usually the newest.

Standardize categorical text

The same category often shows up spelled three ways.

df["status"] = df["status"].str.lower().str.strip()

df["status"] = df["status"].replace({
    "cancelled": "canceled",
    "complete": "completed",
    "in progress": "in_progress",
})

print(df["status"].value_counts())

That last value_counts() is your friend — it instantly reveals "Active", "active", and "ACTIVE " all masquerading as different values.

Validate before you trust it

Cleaning without checking is just hoping. A few quick asserts catch problems early.

assert df["customer_id"].notna().all(), "Missing customer IDs remain"
assert (df["price"] >= 0).all(), "Negative prices found"
assert df["order_date"].between("2020-01-01", "2030-01-01").all(), "Dates out of range"

If any of these trip, you found a data problem before it silently corrupted a report.

A reusable skeleton

Wrap the whole thing so you can rerun it on next month's file without thinking.

def clean(path):
    df = pd.read_csv(path, dtype=str, skipinitialspace=True,
                     na_values=["", "NA", "N/A", "null", "-"])
    df.columns = df.columns.str.strip().str.lower().str.replace(r"\s+", "_", regex=True)
    str_cols = df.select_dtypes(include="object").columns
    df[str_cols] = df[str_cols].apply(lambda s: s.str.strip())
    df = df.drop_duplicates()
    return df

The real takeaway

Data cleaning feels like a slog because people treat it as a one-off scramble every time. It isn't. The tasks repeat — strip headers, fix types, coerce dates, dedupe, validate — so turn them into functions you rerun instead of steps you redo. Your future self, staring at next quarter's export, will thank you.

If you'd like these patterns already assembled into tested, parameterized scripts — column normalizers, type coercers, a dedupe helper, a validation harness — I bundled eight of them into a Python Data Cleaning Toolkit. It's the same techniques from this post, packaged so you can point them at a file and go. And if you'd rather build your own from the snippets above, that works just as well — the important thing is to stop cleaning by hand.

7 Python Scripts That Kill Repetitive Busywork

Adam — Tue, 29 Sep 2026 08:43:19 +0000

Every job has a pile of small, boring tasks: renaming files, merging spreadsheets, cleaning up a messy Downloads folder, stitching PDFs together. None of them are hard. They're just tedious, and they eat an hour here and an hour there until a whole afternoon is gone.

The good news is that most of this busywork is a few lines of Python away from never bothering you again. Below are seven small scripts I actually reach for. They use mostly the standard library, so there's very little to install, and each one is short enough to read and understand in a minute.

1. Organize a messy folder by file type

Point this at your Downloads folder and it sorts everything into subfolders by extension.

from pathlib import Path
import shutil

folder = Path.home() / "Downloads"

for file in folder.iterdir():
    if file.is_file():
        ext = file.suffix.lower().lstrip(".") or "no_extension"
        dest = folder / ext
        dest.mkdir(exist_ok=True)
        shutil.move(str(file), str(dest / file.name))

.pdf files go into a pdf/ folder, .jpg into jpg/, and so on. Run it whenever Downloads gets out of hand.

2. Bulk rename files with a consistent pattern

Camera exports, scanned invoices, screenshots — anything with ugly names. This renames every file in a folder to a clean, zero-padded sequence while keeping the original extension.

from pathlib import Path

folder = Path("./photos")
prefix = "vacation_"

files = sorted(p for p in folder.iterdir() if p.is_file())

for i, file in enumerate(files, start=1):
    new_name = f"{prefix}{i:03d}{file.suffix.lower()}"
    file.rename(file.with_name(new_name))

You get vacation_001.jpg, vacation_002.jpg, and so on. The :03d keeps them sorting correctly even past 100 files.

3. Merge a stack of CSVs into one

You've got a folder of monthly exports and you want a single file. This concatenates every CSV, keeping the header only once.

import csv
from pathlib import Path

folder = Path("./reports")
out_file = folder / "combined.csv"

csv_files = sorted(folder.glob("*.csv"))
header_written = False

with out_file.open("w", newline="", encoding="utf-8") as out:
    writer = csv.writer(out)
    for path in csv_files:
        with path.open(newline="", encoding="utf-8") as f:
            reader = csv.reader(f)
            header = next(reader)
            if not header_written:
                writer.writerow(header)
                header_written = True
            writer.writerows(reader)

No pandas required. It streams row by row, so it handles files too big to open in Excel.

4. Merge multiple PDFs into one

Combining receipts, contract pages, or chapters. This needs one package: pip install pypdf.

from pathlib import Path
from pypdf import PdfWriter

folder = Path("./pdfs")
writer = PdfWriter()

for pdf in sorted(folder.glob("*.pdf")):
    writer.append(str(pdf))

with (folder / "merged.pdf").open("wb") as f:
    writer.write(f)

They're merged in filename order, so name them 01_intro.pdf, 02_body.pdf if order matters.

5. Find and remove duplicate files

Duplicates hide everywhere — the same photo saved three times, the same download grabbed twice. This finds true duplicates by hashing file contents (not just matching names).

import hashlib
from pathlib import Path

folder = Path("./cleanup")

def file_hash(path, chunk=8192):
    h = hashlib.sha256()
    with path.open("rb") as f:
        while block := f.read(chunk):
            h.update(block)
    return h.hexdigest()

seen = {}
for file in folder.rglob("*"):
    if file.is_file():
        digest = file_hash(file)
        if digest in seen:
            print(f"Duplicate: {file}  (copy of {seen[digest]})")
            # file.unlink()  # uncomment to actually delete
        else:
            seen[digest] = file

I always run it once with the delete line commented out, eyeball the list, then uncomment. Hashing content means it catches duplicates even when the filenames differ.

6. Batch-resize images

Shrinking a folder of images for the web or email. Needs pip install pillow.

from pathlib import Path
from PIL import Image

folder = Path("./images")
out = folder / "resized"
out.mkdir(exist_ok=True)
max_width = 1200

for img_path in folder.glob("*.jpg"):
    with Image.open(img_path) as img:
        if img.width > max_width:
            ratio = max_width / img.width
            new_size = (max_width, int(img.height * ratio))
            img = img.resize(new_size)
        img.save(out / img_path.name, quality=85)

It preserves aspect ratio and only shrinks images that are actually too wide.

7. Clean out old files on a schedule

Log folders, temp exports, and caches accumulate forever. This deletes files older than a cutoff.

import time
from pathlib import Path

folder = Path("./logs")
days = 30
cutoff = time.time() - days * 86400

for file in folder.rglob("*"):
    if file.is_file() and file.stat().st_mtime < cutoff:
        print(f"Deleting {file}")
        file.unlink()

Pair it with your OS scheduler (cron on Linux/macOS, Task Scheduler on Windows) and it maintains itself.

The real takeaway

The point isn't any single script — it's the habit. The next time you catch yourself doing the same fiddly thing by hand for the third time, stop and ask: could this be a loop over a folder? Nine times out of ten it can. Start a scripts/ folder, drop these in, and add to it every time busywork shows up. Within a month you'll have a personal toolkit that quietly does an hour of grunt work a day for you.

If you'd rather skip the assembly and start from a set of polished, ready-to-run versions of scripts like these — with error handling, arguments, and sensible defaults already wired in — I packaged nine of them into a Python Automation Toolkit. It's the same spirit as this post, just saved-you-the-typing. Either way, the win is the same: stop doing by hand what a short loop can do for you.

The Boring Asset That Breaks the Bank: Why Tokenized U.S. Treasuries Are the Real Supercycle

Adam — Tue, 29 Sep 2026 08:09:54 +0000

All figures below are approximate and reflect a mid-2026 snapshot. This is general information, not financial, investment, or legal advice.

Start here: the everyday version

Imagine your checking account did three things it can't do today. It paid you the same interest as a good savings account — every single day. It let you send that money to anyone, anywhere on Earth, in seconds, at 2 a.m. on a Sunday. And it let you plug that money directly into other software the way you plug a debit card into a checkout page — no bank, no wire, no waiting.

That combination — cash that earns, moves instantly, and connects to anything — doesn't really exist in normal banking. It's starting to exist in a corner of finance almost nobody outside the industry is watching: tokenized U.S. Treasuries. And the reason it matters isn't that a dull asset got a shiny new coat of paint. It's that this quietly recreates money itself into something better than the digital dollars we use now.

Let me explain it from scratch, then make the case for why this specific thing — not tokenization in general — is a genuine turning point.

What a U.S. Treasury is (60 seconds, no jargon)

A U.S. Treasury is a loan you make to the U.S. government. You hand over money for a set period; it hands back your money plus interest. Because the U.S. government has never failed to pay, these are treated as the safest, most "boring" investment in the world — the thing giant institutions park cash in when they want it to be safe and earn a little. Trillions of dollars sit here. It is the bedrock the entire financial system is built on.

Now the twist.

What "tokenizing" it means (also no jargon)

To tokenize a Treasury is to create a digital token — think of it like a claim ticket — that represents your ownership of that safe government loan, and to record who holds that ticket on a blockchain.

A blockchain is just a shared record book that many independent computers keep copies of at once. Because no single company owns it and everyone's copy has to agree, the record is very hard to fake or quietly alter — and, crucially for this story, it never closes. There's no 9-to-5, no weekends off, no "please allow 2 business days."

So a tokenized Treasury is the same safe government loan you could always own — but the record of who owns it now lives on this always-open shared ledger instead of inside one fund company's private, business-hours computer system. Nothing about the underlying asset changed. What changed is the plumbing — and the plumbing turns out to be most of the game.

Who's actually building this (and why that's the tell)

The easiest way to know something is real and not hype is to look at who is spending money on it. Here it isn't anonymous startups. It's the largest, most conservative names in finance (all sizes approximate, mid-2026):

BlackRock — the world's biggest asset manager — runs the largest tokenized Treasury fund (called BUIDL), at roughly $2.5 billion and growing.
Franklin Templeton — a decades-old fund giant — was an early mover with its on-chain government money fund, around $2 billion.
Ondo Finance, WisdomTree, and Circle (via its Hashnote acquisition) round out a field of serious, regulated players.

Altogether, roughly $6–7 billion of tokenized Treasuries now sit on blockchains, up sharply and still climbing. When the incumbents who have the most to lose from disruption are the ones leading it, that's not a fad — that's a migration.

The original point most coverage misses

Here's the part usually buried under buzzwords, and it's the real reason to care.

You've probably heard of stablecoins — digital dollars (like USDC) that always aim to be worth exactly $1, used to move money around the crypto world quickly. They solved movement: send a dollar anywhere, instantly. But they have a dirty secret. They pay you nothing. When you hold a stablecoin, the company that issued it takes your actual dollars, buys real Treasuries with them, and keeps the interest. You get the convenience; they keep the yield. It's the same trick a bank plays with your checking account, just faster.

A tokenized Treasury collapses that arrangement. It's a digital dollar-equivalent that moves like a stablecoin and pays you the interest — because you own the interest-earning asset directly.

That reframes the whole "supercycle." The interesting event isn't that a boring asset went digital. It's that we now have, for the first time, money that is simultaneously spendable, sendable, programmable, and yield-bearing. Once that exists, the obvious question follows: why would anyone hold a digital dollar that pays them nothing when they could hold one that pays 4–5%? The boring asset doesn't just join the digital-money world — it threatens to eat the incumbents of it. That's a supercycle: not a single product winning, but the ground rules of "what money should do" shifting underneath everyone.

What genuinely changes at scale

Strip the buzzwords and three practical things improve:

Settlement gets instant. Today, when big institutions trade securities, the actual handoff of money and asset typically takes about two business days to finalize. During those two days, capital is frozen in transit — trillions of dollars, industry-wide, just waiting. On a blockchain the handoff can be near-instant, freeing that trapped money to do something useful.
It never closes. Traditional markets keep office hours; blockchains run 24/7/365. Money can move and settle on a holiday, overnight, across time zones — matching how global business actually operates.
It connects to other software directly. Because the token is just data on an open ledger, other programs can use it automatically — as safe collateral for a loan, as the cash leg of a trade, as the reserve behind a payment app — without a human faxing paperwork between institutions. (In the industry this is called composability; in plain terms, it means these financial pieces snap together like Lego instead of being welded shut inside one company.)

What still has to happen (the honest part)

This is early, and a compelling case has to include what could stall it. Widespread adoption realistically needs:

Clear, boring regulation. The single biggest unlock so far wasn't a technical breakthrough — it was legal clarity. The GENIUS Act of 2025 set clearer U.S. rules for digital dollars, which gave institutions the confidence to route real money onto these rails. More such clarity — on who can issue, who can hold, and how it's taxed — is the gating factor, not the code.
Trusted custody. Ordinary people and institutions need dead-simple, insured, "if I lose my password I don't lose my life savings" ways to hold these tokens. Until holding one is as easy and safe-feeling as a bank app, mass adoption stays capped.
Deep liquidity and distribution. It has to be effortless to get in and out at a fair price, and it has to be offered where normal people already are — inside familiar apps and platforms, not a separate crypto obstacle course.

None of these are exotic. They're the same unglamorous conditions — rules, safekeeping, easy access — that every financial innovation needs before it goes mainstream. That's exactly why this is credible rather than hype: the remaining work is ordinary.

The takeaway

Tokenized U.S. Treasuries are the clearest proof yet that real-world assets are moving onto blockchains for practical, unsexy reasons: instant settlement, always-on availability, and the ability to snap into other software — led by the most trusted names in finance, unlocked by regulation, not speculation.

But the deeper story is the one hiding in plain sight: this is the moment money stopped being a choice between "earns interest" and "moves freely," and became both at once. The most boring asset in the world may end up rewriting the most basic thing in finance — what a dollar is allowed to do while it sits still.

Watch this category. Not for the tokens — for what they quietly do to everything else.

Reminder: All figures are approximate and reflect a mid-2026 snapshot. This article is general information only and is not financial, investment, tax, or legal advice. Do your own research.