01Brief06 / 12

The latest status, out of an event log

latest_status(frame) takes an event log — order_id, status, updated_at — and returns each order’s current status. The rows arrive in no particular order, and two events for one order can share a timestamp.

Function	latest_status()
Track	Tables with pandas
Difficulty
Time	~26 min
Concepts	drop_duplicates with subset and keep sort before you dedupe a stable sort so ties keep their order
Cases	9
Needs	pandas about 9 MB, because pandas brings numpy with it, fetched once and then cached by your browser

02The contract

Return a dict of order_id to the status from that order’s most recent event.
The rows arrive in arbitrary order; updated_at is an ISO-8601 timestamp string.
Where two events for one order share a timestamp, the one further down the frame wins.
Every order in the log appears exactly once in the result.
Keys come back sorted by order_id.
An empty log gives an empty dict.
The caller’s frame is not modified — not its row order, not its contents.

03Why this one

An event log is append-only: every status change is a new row, and the current state of anything is whichever of its rows is newest. Reducing the log to the current state is the second most common thing a table gets asked for, and it has exactly one trap — the row you keep is decided by the order the rows are in, so if you have not sorted them, you have picked a row at random and called it the latest.

—The concept behind it

Dictionaries on pythonmastery.io →

That is the reading half. Read it there if the idea is new, then come back here — explanation is what makes something make sense, and practice is what makes it stay.

—Worth doing first

One row per channel, and a cost per click

Not a gate — start this one whenever you like. These cover ideas it leans on, and a tick means you have already done them.

—What this leads to

These build on what you are about to write here.

04Study first: which row does drop_duplicates keep

drop_duplicates does not know what 'latest' means. It knows first and last, and those are positions in the frame you hand it.

import pandas as pd
log = pd.DataFrame({'k': ['a', 'b', 'a'], 'n': [1, 2, 3]})

# subset says which columns define a duplicate. Without it, a row counts as a
# duplicate only if *every* column matches — almost never what you want here.
log.drop_duplicates('k')                  # keep='first' is the default
#    k  n
# 0  a  1
# 1  b  2       the a with n=3 is gone

log.drop_duplicates('k', keep='last')
#    k  n
# 1  b  2
# 2  a  3       and now the a with n=1 is gone

# The surviving rows stay in their original order, which is why 'b' comes
# out first there. keep decides which row; it does not reorder anything.

# The ordering tool, and the keyword that matters for ties:
log.sort_values('k', kind='stable')       # ties keep their original order
log.sort_values('k')                      # kind='quicksort': ties may not

'last' means last in the frame, not latest in time
The two coincide only when the frame is sorted by time, which an event log arriving from a queue, a join, or a concat of two files is not. So the sequence is always sort, then dedupe — and if you find yourself reasoning about which row 'usually' comes first, that is the bug arriving. One sort_values is cheaper than the incident.
subset is what makes a duplicate a duplicate
drop_duplicates() with no arguments compares whole rows, so two events for the same order with different timestamps are not duplicates at all and nothing is dropped. subset='order_id' says 'one row per order'. Getting this wrong is silent in both directions: too narrow a subset drops rows you wanted, too wide drops none at all.
kind='stable' is how you make the tie rule yours
sort_values defaults to quicksort, which is fast and makes no promise about equal values — two events with the same timestamp can come out in either order, and the one you keep changes between pandas versions and even between data sizes. kind='stable' (mergesort, by another name) guarantees that equal rows stay in the order they arrived, which turns 'whatever happens' into a rule you can write in the contract and test.

your_code.py

05One way to write it

import pandas as pd


def latest_status(frame):
    # Time order first — 'last' is a position, so it only means 'latest'
    # once the frame is sorted. kind='stable' keeps events that share a
    # timestamp in the order they arrived, which is the documented tie rule.
    ordered = frame.sort_values("updated_at", kind="stable")

    # One row per order, keeping the one furthest down the sorted frame.
    current = ordered.drop_duplicates("order_id", keep="last")

    statuses = dict(zip(current["order_id"], current["status"]))
    return {key: statuses[key] for key in sorted(statuses)}

sort then dedupe, in that order, every time

drop_duplicates has no notion of newest — it keeps the first or the last row it meets. So the sort is not a tidying step you could skip on a small frame; it is what gives 'last' its meaning. Written the other way round the function still returns one status per order, still looks plausible, and is wrong for every order whose newest event was not physically last in the file.

dict(zip(column, column)) is the cheapest way out of two columns

set_index('order_id')['status'].to_dict() does the same job and builds an index on the way; zip just walks the two columns. Both are fine — what matters is doing it after the reduction, on a frame with one row per key, so there is no question of which value wins. A dict built from a frame that still has duplicates silently keeps the last one it happens to see.

ISO-8601 is why comparing the timestamps as text is safe here

'2026-01-04T11:30' sorts correctly as a string because the format goes from the largest unit to the smallest with fixed-width, zero-padded fields — that is the property the standard exists for. Any other format, and '02/03/2026' vs '10/01/2026' sorts by the day of the month; then you need pd.to_datetime first, and you need to know which of month and day comes first in your data.

—Other ways to write itall of these pass

Every version below is correct and verified against the same cases by the build. They are here because knowing which one to reach for is the part that takes judgement, and the trade-off is stated rather than left for you to guess.

groupby().last() on the sorted framefine

import pandas as pd


def latest_status(frame):
    ordered = frame.sort_values('updated_at', kind='stable')
    current = ordered.groupby('order_id')['status'].last()
    return {key: current[key] for key in sorted(current.index)}

Reads as 'the last event per order' rather than 'drop the others', and it gives you the sorted keys for free because groupby sorts them. It also quietly needs the same sort beforehand for exactly the same reason — last is still a position.

06When not to write this at all

Sorting the whole log to keep one row per key is the clear version, and at tens of millions of rows it is also the slow one — you are ordering everything to answer a question about maxima. There, groupby('order_id')['updated_at'].idxmax() finds the winning row per group without a global sort, and frame.loc[those] collects them; the catch is that idxmax breaks ties by taking the *first* maximum, which is the opposite of this contract’s rule, so you would have to reverse the frame or change the rule. Also: if the log is genuinely append-only and arrives in time order — a single-writer file, not a concat of several — the sort is a no-op you could drop. Assert the order instead of assuming it: frame['updated_at'].is_monotonic_increasing is one line and it fails loudly the day somebody adds a second writer.

07Take it further

Return the timestamp alongside the status, so a caller can see how stale each order is.
Add an as_of argument and answer 'what was each order’s status on this date', ignoring later events.
Reverse the tie rule — the earlier row wins — and change only one thing to do it.