2.1 — DataFrame Axes, Sorting, Index Alignment, agg and apply

Chapter 2 · DataFrames

Prof. Xuhu Wan

Section 2.1 · Chapter 2 · Learning Statistics with Python

DataFrame Axes, Sorting, Index Alignment, agg and apply

DataFrames

Prof. Xuhu Wan

ISOM, HKUST Business School · 2026 Edition

DataFrame Axes, Sorting, Index Alignment, agg and apply

A DataFrame is a dict of Series sharing one index — two axes, labelled both ways. Every method you learnt in Chapter 1 now needs one more decision, which axis?, and every arithmetic operation silently aligns on labels before it computes. In this section you predict the shape of each result before pandas shows it.

Does read_csv know that dates are dates?

PJM is the grid operator for the eastern United States; each column is a utility’s hourly load in megawatts. We load five utilities with parse_dates=True and index_col="Datetime".

After loading, what does raw.loc[“2017-07-04 15:00”, “AEP”] return?

An error — the index holds strings, not dates
The AEP load (one number) at 15:00 on 4 July 2017
Every row of July 2017
A boolean mask

Which axis is which?

df.axes is a list of two Index objects. Commit before you run.

What does df.axes[1] return?

The DatetimeIndex of 8 760 timestamps
The column Index(['AEP', 'COMED', 'DAYTON', 'DEOK', 'DOM'])
The second row of the frame
An integer, 5

Sum down or sum across?

df.sum(axis=0) and df.sum(axis=1) are both legal. Predict the length of each.

How many numbers does df.sum(axis=1) return?

5 — one per column
8 760 — one per row (the total load in that hour)
1 — the grand total
It raises: you cannot sum a DatetimeIndex

Read axis=1 as “collapse the columns”. The row-wise sum is the combined load of the five utilities in that hour. The default is always column-wise; getting this orientation right is most of what makes DataFrame code correct.

Your turn: interquartile range of each row

Row-wise statistics use the same axis=1 switch. quantile accepts it too.

Compute the interquartile range of each row (across the five utilities) into iqr_row. Hint: df.quantile(q, axis=1) gives one quantile per row.

Sorting rows by a column — what happens to the index?

sort_values(by="AEP", ascending=False) puts the peak-load hours first.

After df.sort_values(by=“AEP”, ascending=False), what is the DatetimeIndex?

Unchanged — still 1 Jan 00:00 … 31 Dec 23:00
Reset to 0, 1, 2, …
Reordered with the rows — the first label is the peak-load hour
Dropped; sorting requires a RangeIndex

Your turn: sort the columns by the first row

sort_values also takes axis=1: then by= names a row label and the columns are reordered.

Reorder the columns of df so that the values in the first row are ascending, storing the result in by_first. Hint: df.sort_values(by=df.index[0], axis=1).

Making changes: add, derive, drop

New columns are assignments. drop returns a new frame — predict what happens to the original.

After df2 = df.drop([“Month”, “Season”], axis=1), does df still contain Month?

Yes — drop returns a copy; df is unchanged
No — drop always edits in place
Only the Season column survives
It raises unless inplace=True

rename, assign, replace

Three ways to change content without touching the row axis.

assign is chainable and never edits in place; df["new"] = … is faster (roughly 15× on the full file) but mutates. replace takes a scalar, a list, or a dict of old → new.

Index alignment: predict the shape of df1 + df2

df1 is rows 0–2 × columns 0–2. df2 is rows 2–3 × columns 0–3. Their labels overlap in exactly one row.

What is the shape of df1 + df2, and how many cells are non-NaN?

(3, 3), all filled
(2, 3), all filled
(4, 4), only 3 cells filled
It raises — shapes differ

Duplicated index labels

Stacking df1 on df2 with pd.concat keeps every row — including the one whose timestamp both frames share.

df1 covers 00:00–02:00 and df2 covers 02:00–03:00. What does this print?

print(pd.concat([df1, df2]).index.duplicated().sum())

1

.index.duplicated() is a boolean mask; keep="last" marks every copy except the last as a duplicate. In §2.2 you will meet a real duplicated timestamp that no concat created.

agg: several statistics in one call

agg takes a list of function names, or a dict mapping columns to their own lists. Predict the shape of the dict version.

What does num.agg({“AEP”: [“sum”,“count”], “DEOK”: [“mean”,“count”]}) return?

A Series of 4 numbers
A 3 × 2 frame with two NaN cells
A 2 × 2 frame, fully filled
An error — the lists have different functions

apply: your own function, row by row or column by column

The notebook’s example: build \(Y = 10 + 0.8X + \text{noise}\) with a function that receives one row at a time. Predict the column means.

X is np.arange(100). What is the first number of newdf.apply(np.mean).round(2)?

newdf.apply(np.mean)["X"]

49.5

apply(..., axis=1) is how you build a feature from several other features. It is a Python loop under the hood — fine for thousands of rows, slow for millions, where the vectorised 10 + 0.8*newdf.X + newdf.Noise wins by 10–100×. §2.4 returns to this.

Your turn: row range two ways

Compute the range (max − min) of each row of num twice: rng_agg via .agg(["max","min"], axis=1) and a subtraction, and rng_apply via .apply with a lambda. The two must agree.

What you discovered

read_csv(parse_dates=True, index_col=…) arrives date-typed: a string like "2017-07-04 15:00" or "2017" indexes straight in.

axis=0 collapses rows (one result per column, the default); axis=1 collapses columns (one result per row). sum, quantile, agg, apply, drop, sort_index all obey it.

sort_values carries the index with the rows; sort_index() restores time order; sort_values(by=<row>, axis=1) reorders columns.

drop, rename, assign, replace return new frames — nothing changes unless you re-assign or say inplace=True.

Arithmetic aligns on the union of labels: df1 + df2 was 4 × 4 with only 3 real cells. pd.concat keeps duplicated labels; ~index.duplicated(keep="last") removes them.

Next: §2.2 — the real file has holes and a duplicated hour. Audit first, then decide.