Duplicates and Dates Gone Wrong: A CSV, pandas, and 11 pm

2026-10-12

It's 10:47 on a Tuesday night, and the cursor in your terminal has been blinking at you for a while now.

On the left monitor: a CSV export with 4,812 rows. On the right: a half-written Python script, three Stack Overflow tabs, and a cold cup of tea you made around eight.

You only wanted to send a clean customer list to the newsletter tool. That was the whole plan. Twenty minutes, maybe.

Then you scrolled.

The same person, three times

There's Anna Keller. And there's anna.keller@ with a capital K. And there's "Keller, Anna" with a trailing space you can't see but the computer can.

They are obviously one person. To the file, they are three strangers.

You think: fine, I'll drop the duplicates. You write the line, run it, and the row count barely moves. Of course it doesn't. The rows aren't identical. They're only identical to a human who squints.

So you squint. You sort by name, scroll, and scroll some more. Somewhere around row 900 you realise you've been staring at the same four people for ten minutes.

03/04/2026

Then you spot the dates.

Some say 03/04/2026. Some say 2026-04-03. A few say 3 Apr 26. One, for reasons no one will ever explain, says 04.03.2026.

Is the first one March or April? It depends on who typed it, which tool exported it, and which country that tool thought it was in. You could guess. You could be right most of the time.

But "most of the time" is a strange standard for something that decides who gets an invoice reminder, or a "happy anniversary" email, or a follow-up that is suddenly three months late.

You rub your eyes. It's 11:30.

What it quietly costs

Nobody talks about this part. The file isn't really the problem. The problem is the small, low-grade dread attached to it.

The dread of sending the wrong thing to the wrong person. Of a client noticing the same name twice in a report and wondering, just for a second, how careful you really are. Of hitting "import" and not knowing what the other system will quietly do to your dates.

It costs evenings, obviously. But it also costs that feeling of being on top of your work. You're a competent person. You run a business, or you freelance, or you keep someone else's business running. And yet here you are, at midnight, hand-fixing rows like it's 2009.

You also know it will happen again. Next month's export will arrive with the same quirks, plus a few new ones. The script you're writing tonight is tied to tonight's mess. You'll probably rewrite half of it in four weeks.

That's the part that actually stings.

The small moment of "wait"

Around midnight you stop. You don't find a clever trick. You just lean back and ask a plainer question.

Why am I solving this from scratch every time?

Because surely this isn't a unique problem. Duplicates that differ by a capital letter. Dates in four moods. Phone numbers with and without spaces. Everyone who touches exports meets the same handful of gremlins, in the same order.

It doesn't have to be a fresh puzzle each month. It could be something you set up once, run on a file, and look over. Something that shows you what it would change before it changes anything, and hands you a short list of what it did afterwards.

That's it. That's the whole shift. Less heroics, more routine.

A different Monday

Picture it a few weeks from now.

It's Monday, 9:15. Coffee is still hot. The new export lands in your downloads folder, and you feel nothing in particular, which is exactly the point.

You point your tools at the file. You glance at a summary: this many duplicates found, these dates standardised, these three rows flagged because they genuinely look odd. You check the flagged ones with your own eyes, because those are the ones worth your attention.

Then you save the clean file and move on to the work you actually get paid to think about.

No midnight. No cold tea. No quiet worry that something slipped through. Just a tidy file and a Monday that stays yours.

If this sounded familiar

If you recognised yourself somewhere in that 11 pm scene, we put together the CSV & Excel Data Cleaning Toolkit: a set of Python scripts for the usual suspects in messy exports. Duplicates, date formats, phone numbers, encoding trouble, splitting and merging files, and validation. It does a dry run first and gives you a change report, so you can see what happens before anything is touched.

It's there if you'd like it, no rush: CSV & Excel Data Cleaning Toolkit.

And if you'd rather keep wrestling with your own script for now, that's fine too. Just maybe go to bed first. The file will still be there tomorrow, and it will look a little less personal in daylight.

An illustrative story, written with AI assistance.