Chapter 5: Reshape and Combine

Most questions about a running system need more than one table, or one table seen another way. Which endpoint is slowest, which team owns it, and when did traffic peak? Chapter 4 cleaned a single DataFrame; this chapter asks it questions.

groupby splits the rows by a key, runs a reduction on each group and combines the results, one row per group; agg runs several at once. sort_values orders rows, merge joins two tables on a key, and pivot_table spreads one key across the columns. For time, pd.to_datetime turns text into datetimes, .dt reads their parts, resample groups them by the hour and rolling slides a window along.

import pandas as pdlogs = pd.read_csv('data/logs.csv')logs['time'] = pd.to_datetime(logs['time'])logs['hour'] = logs['time'].dt.hourlogs.sort_values('ms', ascending=False).head(3)logs.groupby('endpoint')['ms'].mean()per_endpoint = logs.groupby('endpoint').agg(requests=('ms', 'size'), mean_ms=('ms', 'mean'))owners = pd.read_csv('data/owners.csv')per_endpoint.merge(owners, on='endpoint')report = per_endpoint.merge(owners, on='endpoint', how='left')hourly = logs.resample('h', on='time').agg(requests=('ms', 'size'))hourly['requests'].rolling(2).mean()logs.pivot_table(index='endpoint', columns='hour', values='ms', aggfunc='count', fill_value=0)

A merge has to decide what happens to a key with no partner. The default, inner, drops it. Left keeps every row of the left table and fills the gaps with NaN, and outer keeps every key from both sides. So count the rows: here eight requests become seven, eight or nine.

import pandas as pd
logs = pd.read_csv("data/logs.csv")
owners = pd.read_csv("data/owners.csv")
len(logs), len(owners)

(8, 3)

# /upload has no owner, and nobody called /billing
[len(logs.merge(owners, on="endpoint", how=how)) for how in ("inner", "left", "outer")]

[7, 8, 9]

pd.concat puts tables with the same columns one under another. It keeps each row’s index label, so labels can repeat until you pass ignore_index=True.

import pandas as pd
web1 = pd.DataFrame({"endpoint": ["/login", "/search"], "ms": [120, 340]})
web2 = pd.DataFrame({"endpoint": ["/login"], "ms": [95]})
pd.concat([web1, web2])

  endpoint   ms
0   /login  120
1  /search  340
0   /login   95

pd.concat([web1, web2], ignore_index=True)

  endpoint   ms
0   /login  120
1  /search  340
2   /login   95

This is where people slip. If a key appears twice in the right table, every left row with that key comes back twice, and nothing warns you: a second owner for /login turns eight requests into twelve. Pass validate='many_to_one' to the merge and pandas raises instead.

import pandas as pd
logs = pd.read_csv("data/logs.csv")
owners = pd.read_csv("data/owners.csv")
# /login moved to a new team, and the old row was never removed
handover = pd.DataFrame({"endpoint": ["/login"], "team": ["platform"]})
owners = pd.concat([owners, handover], ignore_index=True)
owners

   endpoint      team
0    /login  identity
1   /search    search
2  /billing  payments
3    /login  platform

len(logs.merge(owners, on="endpoint", how="left"))

12

logs.merge(owners, on="endpoint", how="left", validate="many_to_one")

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

Duplicates in right:
 endpoint
  /login ...

resample is a groupby on time bins, with one difference: it makes a row for every hour, even an hour with no requests. Grouping by the hour from .dt leaves 11:00 out, so the quiet hour vanishes instead of showing 0.

import pandas as pd
logs = pd.read_csv("data/logs.csv")
logs["time"] = pd.to_datetime(logs["time"])
logs.groupby(logs["time"].dt.hour).size()

time
9     4
10    3
12    1
dtype: int64

logs.resample("h", on="time").size()

time
2026-09-01 09:00:00    4
2026-09-01 10:00:00    3
2026-09-01 11:00:00    0
2026-09-01 12:00:00    1
Freq: h, dtype: int64

Dates written day first are the other trap. pd.to_datetime reads 01/09/2026 as 9 January, without a warning, and only complains once a day is over 12. Give it the format and it reads what you meant.

import warnings
import pandas as pd
# 1 and 2 September, written day first
stamps = pd.Series(["01/09/2026 09:05", "02/09/2026 10:15"])
with warnings.catch_warnings(record=True) as caught:
    parsed = pd.to_datetime(stamps)
parsed.dt.month.tolist(), len(caught)

([1, 2], 0)

pd.to_datetime(stamps, format="%d/%m/%Y %H:%M").dt.month.tolist()

[9, 9]

pivot_table takes the mean of each cell unless you pass aggfunc, and a pair with no rows gets NaN. melt goes back the other way, to one row per value: the long form that groupby and merge work on.

import pandas as pd
logs = pd.read_csv("data/logs.csv")
logs["hour"] = pd.to_datetime(logs["time"]).dt.hour
# aggfunc is "mean" unless you pass another
wide = logs.pivot_table(index="endpoint", columns="hour", values="ms")
wide

hour         9       10     12
endpoint                      
/login    510.0   110.0  130.0
/search   325.0   280.0    NaN
/upload     NaN  1500.0    NaN

wide.reset_index().melt(id_vars="endpoint", value_name="mean_ms")

  endpoint hour  mean_ms
0   /login    9    510.0
1  /search    9    325.0
2  /upload    9      NaN
3   /login   10    110.0
4  /search   10    280.0
5  /upload   10   1500.0
6   /login   12    130.0
7  /search   12      NaN
8  /upload   12      NaN

apply calls a Python function once per value. That is a loop, so chapter 3’s rule holds: when a vectorized expression exists, it gives the same Series many times faster.

import time
import numpy as np
import pandas as pd
ms = pd.Series(np.random.default_rng(0).integers(50, 2000, size=200_000))
ms.apply(lambda v: v / 1000).equals(ms / 1000)

True

def best_seconds(work, repeats=3):
    timings = []
    for _ in range(repeats):
        start = time.perf_counter()
        work()
        timings.append(time.perf_counter() - start)
    return min(timings)
looped = best_seconds(lambda: ms.apply(lambda v: v / 1000))
vectorized = best_seconds(lambda: ms / 1000)
looped > 10 * vectorized

True

The habit this chapter adds is to count rows around every combine. Before a groupby, a merge or a resample, say how many rows it should give, then check. A join that doubled some rows without a warning reports wrong numbers.

Homework

The packet has three parts: predict how many rows ten merge, concat and groupby lines give; count requests per hour with resample, then per endpoint per hour, joined to their owners; and fix two bugs, a pd.to_datetime call that reads day-first dates month first and an apply that should be one vectorized step.