NumPy & pandas, section 3 of 4, 5h

pandas on a real table

By the end: You can load, inspect, clean, aggregate, join and reshape a real dataset in pandas without loops and without silent mistakes.

Loading with the right types
Looking before doing anything
Filtering and new columns
Group-by with named aggregations
Joins that check themselves
Dates and resampling

New to numpy & pandas? NumPy is a Python library for holding large grids of numbers and doing arithmetic on all of them in one instruction, rather than one at a time in a loop. pandas builds on it for labelled tables with named columns and mixed types, which is the shape real data arrives in; its table is called a DataFrame, and you filter, join and group it in memory on your own machine. Why it matters

pandas is where most data work in Python actually happens: a table held in memory, with named columns and typed values, that you filter, group, join and reshape. This section takes one real dataset from a CSV file through to a weekly summary per model, doing each step the way that survives contact with bad data. The habits are the point, particularly declaring types on load, looking at the rows before transforming them, and asking a join to check its own assumptions. They are what separates a number you can quote from one that is quietly wrong.

Step            Command                                    What it saves you from
load            read_csv(dtype=..., parse_dates=...)       silent object columns, 10x memory
look            info(), describe(), value_counts()         a whole analysis of bad data
filter          df[mask].copy()                            writing through a filtered view
derive          vectorised arithmetic, not apply           a job that takes an hour
aggregate       groupby().agg(named=("col", "fn"))         unreadable multi-level columns
join            merge(validate=..., indicator=True)        rows multiplying, totals doubling
resample        set_index(date).resample("D")              missing days vanishing from a chart

Worth memorising as a checklist. Every line of it exists because of a specific way the obvious version goes wrong.

Load it properly#

import pandas as pd

calls = pd.read_csv(
    "llm_calls.csv",
    parse_dates=["created_at"],
    dtype={"model": "category", "status": "category", "user_id": "int64"},
)

Stating the types does three jobs. It stops the inference that turns a column with one stray value into object, which is pandas storing Python objects and is both slow and eight times the memory. It makes dates into real timestamps so that comparison, sorting and resampling work, rather than strings where '2026-3-1' sorts after '2026-12-01'. And a category column stores each distinct string once, which on a model or status column is usually the largest saving in the frame. Add usecols to read only the columns you need, and nrows=1000 while you are still working out what the file contains.

Look first#

calls.info()                           # columns, types, missing counts, memory
calls.describe()                       # numeric summaries: look for impossible values
calls["status"].value_counts(dropna=False)
calls.sample(5, random_state=0)        # read actual rows

Five minutes here saves hours later. Negative token counts, a status of 'OK ' with a trailing space, or a column that is 40% empty change everything you do next.

Read each of those for something specific. info tells you whether any column came in as object when you expected a number, and how many nulls each has. describe shows min and max, which is where impossible values live: a negative token count, a latency of zero, a date in 1970 that is really a missing value written as an epoch. value_counts with dropna=False is the one that finds the trailing space, because 'ok' and 'ok ' appear as two rows; add normalize=True and it gives proportions, which is how you notice that twenty percent of calls errored. And sample beats head, because the first five rows of a file are often a header artefact or the oldest and least representative data in it.

calls["status"].value_counts(normalize=True, dropna=False)
# ok       0.8
# error    0.2

calls.duplicated(subset=["request_id"]).sum()     # are the keys actually unique
calls["created_at"].agg(["min", "max"])           # is the date range what you think
calls.isna().mean().sort_values(ascending=False)  # fraction missing, worst first

Four lines that answer the four questions that invalidate an analysis: what proportion, are keys unique, what period, and how complete.

Filter and derive#

ok = calls[calls["status"] == "ok"].copy()
ok["cost_usd"] = ok["input_tokens"] * 3e-6 + ok["output_tokens"] * 15e-6   # vectorised
ok["slow"] = ok["latency_ms"] > 5_000

Illustrative prices. .copy() states that this is now your own frame, rather than an attempt to write through a filter, which does not update the original.

Derive columns with arithmetic on whole columns, never with apply over rows. The difference is not marginal. Computing a product two ways on two hundred thousand rows: apply with axis=1 took 1,445 ms and the vectorised expression took 0.32 ms, a factor of about four and a half thousand, because apply builds a Python Series object for every row.

# 1,445 ms on 200,000 rows
df["c"] = df.apply(lambda r: r["a"] * r["b"], axis=1)

# 0.32 ms on the same rows
df["c"] = df["a"] * df["b"]

# For branching, np.where and np.select stay vectorised:
import numpy as np
ok["tier"] = np.where(ok["cost_usd"] > 0.01, "expensive", "cheap")
ok["band"] = np.select(
    [ok["latency_ms"] < 500, ok["latency_ms"] < 2000],
    ["fast", "normal"],
    default="slow",
)

np.select is the vectorised if/elif/else chain, and the one people reach for apply instead of. Conditions are evaluated in order; default catches the rest.

Group and aggregate#

per_model = ok.groupby("model", observed=True).agg(
    calls=("user_id", "size"),
    users=("user_id", "nunique"),
    cost=("cost_usd", "sum"),
    p95_ms=("latency_ms", lambda s: s.quantile(0.95)),
).sort_values("cost", ascending=False)

#         calls  users     cost  p95_ms
# model
# sonnet      2      2  0.01290  817.00
# haiku       2      2  0.00225  154.25

observed=True matters on a categorical key: without it, pandas produces a row for every category that exists in the type, including ones with no rows in this frame.

Three details are worth knowing here. size counts rows and count counts non-null values in the named column, so on a column with nulls they disagree and the wrong one gives you a denominator that is too small. The group key becomes the index; add as_index=False, or call reset_index(), when you want it back as a column for a later merge. And to keep every original row while attaching a per-group figure, use transform rather than agg, which is how a share-of-group column is computed in one line.

ok["model_cost"] = ok.groupby("model", observed=True)["cost_usd"].transform("sum")
ok["share_of_model"] = ok["cost_usd"] / ok["model_cost"]

# Top 3 calls per user by cost, the pandas spelling of the SQL window pattern:
top3 = (ok.sort_values("cost_usd", ascending=False)
          .groupby("user_id", observed=True)
          .head(3))

groupby().head(n) after a sort is the whole top-N-per-group pattern. It does not need a rank column.

Joins that check themselves#

users = pd.read_csv("users.csv")
merged = ok.merge(users, on="user_id", how="left", validate="many_to_one", indicator=True)
print(merged["_merge"].value_counts())      # how many calls found no user?
# both          4
# left_only     1
# right_only    0

validate raises an error if users has duplicate ids, which would otherwise silently multiply your rows and your cost totals. indicator shows how many rows failed to match. Without validate, five rows joined to a users table with one duplicate id come back as seven, every sum over them is inflated, and there is no error and no warning — only a revenue figure that is wrong in a way nobody notices for a month.

# MergeError: Merge keys are not unique in right dataset;
#             not a many-to-one merge

# When it fires, find them and decide what one row per key means:
dupes = users[users.duplicated("user_id", keep=False)].sort_values("user_id")
users_latest = (users.sort_values("updated_at")
                     .drop_duplicates("user_id", keep="last"))

Usually the right table is a history and you wanted the latest row per key. drop_duplicates after a sort is the pandas version of the SQL latest-row pattern.

Check the row count across every merge

assert len(merged) == len(ok) after a left join you believe is a lookup costs nothing and catches the entire class of quiet duplication. Do the same after a filter you believe removes nothing. Most wrong numbers in a data pipeline are a row count that changed when it should not have.

Time#

daily = ok.set_index("created_at").resample("D")["cost_usd"].sum()
# 2026-03-01    0.00885
# 2026-03-02    0.00540
# 2026-03-03    0.00000     <- days with no calls are present, as zero
# ...
# 2026-03-09    0.00090

weekly_by_model = ok.pivot_table(index=pd.Grouper(key="created_at", freq="W"),
                                 columns="model", values="cost_usd", aggfunc="sum",
                                 observed=True)
# model         haiku  sonnet
# created_at
# 2026-03-01  0.00135  0.0075
# 2026-03-08      NaN  0.0054     <- NaN, not 0: no haiku calls that week

resample fills the gaps in time, which is what groupby on a date column does not do. That difference is why a chart built with groupby joins straight across an outage.

Two things to get right about time. resample needs a datetime index, which is what the set_index is for; Grouper does the same job inline when the frame has other grouping keys. And a frequency string means the period it ends on: W is weeks ending Sunday by default, ME is month end, MS is month start. Getting that wrong shifts every figure by a period, which is hard to see and easy to present.

Store timestamps in UTC and convert only when displaying. A daily total computed in local time counts the wrong hours on the days the clocks change, and comparing two days across a daylight saving boundary compares 23 hours with 25.

calls["created_at"] = pd.to_datetime(calls["created_at"], utc=True, errors="coerce")
calls["created_at"].isna().sum()     # rows whose date would not parse: check this

local = calls["created_at"].dt.tz_convert("Europe/London")
calls["hour"] = local.dt.hour
calls["date"] = local.dt.date

errors='coerce' turns unparseable dates into NaT rather than raising. That is useful only if you then count them, which is what the second line is for.

Where people go wrong

Looping over rows with iterrows. It is often a hundred times slower than the vectorised equivalent and hides bugs in per-row logic.

If this comes up in an interview

After a join, your total revenue doubled. What happened?

The right-hand table probably had duplicate keys, so each left row matched several right rows and was repeated. Check key uniqueness before joining, use validate to enforce the expected relationship, and compare row counts before and after.

Then they push further

How would you find the duplicates?

Group the right table by the join key and count, or use duplicated on the key column, and inspect those rows. Often it is a history table where each change adds a row, and you need the latest one per key.

What gets marked up. Marked up for naming duplicate keys immediately and for a preventive habit such as validate.

Build this: Take any public CSV with a date, a category and a number. Load it with correct types, inspect it, produce a daily and weekly summary per category, join a second table with validate, and write down three data problems you found.

Watch it explained

23 min

Python Pandas Tutorial (Part 1): Getting Started with Data Analysis - Installation and Loading Data

Corey Schafer

23 min

Learning Pandas for Data Analysis? Start Here.

Rob Mulla

This is one section of NumPy & pandas, 18 hours across 4 sections. Read it in full to get the whole topic in order.

Previous sectionMemory, views and larger-than-memory data Next sectionWhen pandas is slow: vectorising, Polars and DuckDB