Date Parsing Basics: a date stored as a string sorts and filters wrong

Project at a glance

• Track: Data Engineering
• Difficulty: Absolute beginner · 2/10
• Time: ~0.5 h for Part A · ~1.5 h more for the optional Part B
• You’ll use: pandas
• ▶ Runs in your browser (nothing to install) — Part A uses only pandas, numpy and the Python standard library — all run in the browser. A full local setup is documented below too.
• New to this? This is a beginner project — you can’t break anything. Never coded? You can still run and read every step — writing your own code is only the optional Part B. Click ▶ Run below to start in your browser.

Before you start

New to this? A string is text — the characters "2024-3-9", not the day itself. When you sort or compare text, the computer goes left to right, character by character, the way a dictionary orders words. That is fine for words and wrong for dates: as text, "2024-3-9" comes after "2024-3-10", because at the first differing character 9 beats 1. A datetime is the parsed value — a real point on the calendar — and it compares the way you expect. Parsing is the step that turns "2024-3-9" (text) into March 9, 2024 (a datetime). This project teaches you to always parse first, with an explicit format (you tell pandas exactly how the string is laid out), and shows two ways skipping that step silently gives the wrong answer.

1. The Brief

You’re handed an event log and asked one innocent question: what was the most recent event? The timestamps are stored as strings, and you can answer in one line — df["logged_at"].max() — and get an event from nearly two weeks before the real latest one, because the log’s dates aren’t zero-padded and text sorts "2024-3-9" after "2024-3-22". This is the single most common data-handling bug there is: treating a date as the text it happens to be stored as. It shows up as “latest” rows that aren’t latest, date-range filters that miss half their rows, and reports off by exactly one month because someone read 5/10 as May 10 in a file that meant 5 October. You’ll build the correct answer, watch the naive one go wrong in two different ways, and learn the one habit — parse to a datetime with an explicit format — that fixes the whole family.

2. Difficulty & time

Difficulty 2/10. One tool (pandas), one concept (parse before you compare), and Part A’s path is fully specified — the low end of the scale. It sits below idempotent-loads (3), data-contracts (3), and csv-to-parquet (3): each of those has more moving parts (a re-runnable upsert, a multi-rule validation engine, columnar storage with predicate pushdown), whereas this turns on a single idea applied cleanly. It sits beside wide-to-tidy (2) — another one-tool, one-concept data-engineering fix where the whole lesson is recognising the trap. It stays off the floor of the scale because the skill is judgment (spotting that a column is a string pretending to be a date), not a syntax drill. Part A is a short read-and-run; Part B is where you do the work.

3. What you’ll be able to do after

• Recognise when a date column is really a string (dtype object/str), and explain why sorting or max() on it gives a calendar-wrong answer.
• Parse a date column to a real datetime with an explicit format, instead of letting pandas guess, and say why the explicit format is safer.
• Read a D/M/Y vs M/D/Y file correctly, and predict what a month-first guess does to it (silently drops days > 12, swaps the rest into the wrong month).
• Filter events into a date range and trust the count.

4. Prereqs & time box

Part A: ~30 minutes. Part B: ~1.5 hours. Both are hard caps — if Part A runs long, you’re overthinking it. You need to read a CSV with pandas and call one or two functions on a column; nothing more.

For where to write and run Python (install, virtual environment, per-OS terminal, running a script step by step), see Start Here guide. No account needed and no GPU.

5. Setup & data

The data is synthetic, generated locally by data/make_data.py from a fixed seed (byte-identical every run) — see Sources. Generate it once:

python data/make_data.py

That writes data/events_a.csv (Part A — a mobile app’s event log with non-zero-padded timestamps) and data/events_b.csv (Part B — a European payment export with day-first dates). Nothing is downloaded; there is no external source. Synthetic is the right call here: the lesson is whether your parsed date matches the true calendar date, and only generated data lets you know that truth in advance.

6. Part A — Guided Build

Prefer the browser? If this project shows a ▶ Run button, you can run Part A right on the page — no setup. The commands below are for running on your own computer.

▶ Try it now — run the real Part A right here in your browser. This one button makes the data and runs Part A for you — you do not need to install anything, and you do not need to type any of the python … commands anywhere on this page (those are only for running on your own computer instead). First run loads Python + this project’s libraries: usually ~10–20 s, a little longer for machine-learning projects.

Run the guided script (or paste it cell-by-cell — each # %% is one step):

python part_a/find_latest_event.py

It walks six steps and writes part_a/latest_event_a.csv. The shape of the argument:

• STEP 1. Load the log. Checkpoint: 40 events, and logged_at comes in as text (dtype object/str), not a datetime. Every comparison is a string comparison until you parse.
• STEP 2. The naive answer — max() of the raw strings. Checkpoint: it picks E1029 at 2024-3-9 7:15, a March 9 morning event. It only “wins” because "2024-3-9…" sorts after every "2024-3-1x"/"2024-3-2x" string.
• STEP 3–4. Parse with the explicit format "%Y-%m-%d %H:%M", then take the max of the real datetimes. Checkpoint: the true latest is E1035 at 2024-3-22 20:15 — thirteen days later. Same data, one line of parsing, opposite answer.
• STEP 5. Sort by string vs by datetime, side by side, and see that the string order isn’t a rougher sort — it’s a different order entirely, driven by the leftmost differing character.
• STEP 6. Write a two-row summary (naive pick vs true latest) to eyeball later.

Why this and not that

• Why not just df["logged_at"].max()? Because on an object column that’s a string max — it answers “which text is largest,” not “which moment is latest.” The two agree only when the strings happen to be zero-padded and in Y-M-D order, which you don’t control upstream.
• Why pass an explicit format= instead of letting pandas guess? A guess can be wrong (Part B shows exactly how) and can silently produce NaT. When you know the layout, state it: the parse is faster, and a string that doesn’t match the format errors loudly instead of guessing.
• Why not “just zero-pad the strings”? You’d be patching one file’s symptom. Parsing to a datetime fixes the class of bug regardless of how upstream wrote the text.

Functions you’ll meet (and where to read more)

Function	What it does here
pandas.read_csv	Load events_a.csv into a DataFrame (dates arrive as text)
pandas.to_datetime	Parse the string column to real datetimes with an explicit format= (STEP 3)
Series.idxmax	Find the row of the max — of the strings (wrong) vs the datetimes (right)
DataFrame.sort_values	Order by string vs by datetime to see the two orders differ (STEP 5)

The Python strftime/strptime format codes (%Y %m %d %H %M) are the alphabet for the format= string — bookmark that page.

If something looks off

• If your naive and parsed “latest” agree, check that logged_at really loaded as text and that the file still has its single-digit-day events — those are what break the string sort.
• If parsing raises, your format= doesn’t match the strings. Compare it character-for-character against one value in the column; a stray separator or a missing %H:%M is the usual cause.
• A number that surprises you is the point of this project, not a bug — read it against the checkpoints above before assuming you broke something.

7. Part B — Your Turn

Part B is the optional “your turn” half — a guided fill-in-the-blank, not a blank page. You get a working starter (part_b/starter.py) that already runs; your job is to finish the one # TODO it marks out. More open than Part A’s step-by-step, but you are never staring at an empty file. If you finished Part A and want to prove the skill, this is where it happens. New to Python? It’s completely fine to stop after Part A and come back later.

Same skill, new setting. A payment gateway’s events_b.csv is a European export: dates are written day/month/year, so "5/10/2024" means 5 October. Finance wants the count of events in October 2024. The starter lets pandas guess the format — which assumes month-first — and prints a count that’s badly wrong in two silent ways at once: every true day > 12 can’t be a month, so it becomes NaT and is dropped; and every day ≤ 12 has its day and month swapped into the wrong month. The # TODO is the same move as Part A: parse with the right explicit format (here, day-first) before you filter.

Acceptance criteria (checkable)

Your part_b/starter.py writes part_b/out/report.json with:

• events_in_range — the number of events whose date falls in October 2024 (inclusive),
• total_events — the total number of rows in the file.

Then:

python part_b/starter.py
pytest part_b/test_solution.py -q

You’re done when the tests pass: total_events is exact and events_in_range lands within 1% of the true October count (the reference hits it exactly). The starter’s guess-the-format count is about 79% too low and fails.

Constraints — what must be true of your solution, not how to get there

• Every row must parse to a real date — none silently dropped to NaT. If some don’t parse, your format is wrong for this file, not the data.
• The count is a straight range filter on the parsed dates: start ≤ date ≤ end, inclusive.
• Don’t “fix” it by discarding the days > 12. They’re valid October dates; a month-first read is what makes them look impossible.

Hints

Hint 1 — why the starter's count is so low

The starter lets pandas guess, and pandas reads the first field as the month. So "25/10/2024" (25 October) has “month 25” — impossible — and becomes NaT, dropped. More than half the file has a day > 12, so most rows vanish before you even filter. The days ≤ 12 that survive get their day and month swapped.

Hint 2 — tell pandas the layout instead of guessing

The file is day/month/year. pandas.to_datetime takes a format= argument built from the format codes: %d = day, %m = month, %Y = 4-digit year. Put them in the order the file actually uses.

Hint 3 — the shape of the answer

dt = pd.to_datetime(events["event_date"], format="%d/%m/%Y"), then count where (dt >= "2024-10-01") & (dt <= "2024-10-31"). With every row parsed, the October count jumps well above the starter’s number.

8. Self-check

• After the fix, no date should be NaT — check parsed.isna().sum() is 0. If it isn’t, the format is still wrong for some rows.
• Your October count should be higher than the starter’s, because the starter was throwing away every day > 12. If it’s lower, you filtered the wrong range.
• Re-running python data/make_data.py never changes the data (it’s seeded), so your count shouldn’t move between runs.
• If you can state, in one sentence, why the same string is a different date under %d/%m vs %m/%d, you’ve got the transferable skill — the specific count is secondary.

9. Stretch

• In Part A, add a column that flags which events a text sort mis-orders relative to the true datetime order, and count how many rows are affected.
• In Part B, group the correctly-parsed events by month and print the per-month counts, then do the same on the naive (guessed) parse and put the two side by side — see exactly which months gained and lost rows to the swap.
• (Genuinely hard.) You’re given a third file where the format is genuinely mixed — some rows D/M/Y, some M/D/Y, with no flag saying which. Work out what extra information you’d need to disambiguate a row like "4/5/2024", why days ≤ 12 are unrecoverable without it, and what a defensible policy would be (reject? infer per-source? require an upstream fix?).

10. Ship it

Put this in a repo README so it counts as a portfolio piece: one sentence on the trap (“a text max() reported an event thirteen days too early because the dates were unparsed strings”), the naive vs parsed answers side by side, and the one-line rule (parse to a datetime with an explicit format before you sort, compare, or filter). Add a short note on Part B showing you transferred the same idea to a different failure — a day-first file a month-first guess mis-buckets — and recovered the honest October count. Recognising the same skill under a different surface is what an interviewer is actually checking for.

11. Sources

See SOURCES.md. The data is synthetic, generated by data/make_data.py from a fixed seed; there is no external source, and nothing is downloaded or committed.

Next up

Finished this one? Continue the Data Engineering track:

String Key Cleaning: ’ Apple ’, ‘apple’, and ‘APPLE’ are one product, not three →  ·  Browse all courses