Chapter 2 · Learning Statistics with Python
Inspect tables, investigate and repair defects, validate joins, build grouped features, then explain the result.
Prof. Xuhu Wan
ISOM, HKUST Business School · 2026 Edition
Chapter 2 combines the column foundations into complete tables.
| Section | Concept | Tool |
|---|---|---|
| 2.1 DataFrame Foundations | Inspect, update and summarise a table | pd.read_csv, .agg |
| 2.2 Data Quality: Audit, Repair and Selection | Discover defects, repair with evidence, then select rows | .isna, .drop_duplicates |
| 2.3 Grouped Tables, Joins and Reshaping | Group observations, validate joins and choose a representation | .groupby, .merge, .pivot |
| 2.4 Feature Pipelines and Research Applications | Build readable transformations and validate grouped features | .assign, .pipe, grouped windows |
| 2.5 Interactive Visualisation | Map columns to charts, traces and subplots | Plotly Express, Graph Objects, make_subplots |
| Part | Question |
|---|---|
| 2.1.1 Table structure and axes | What does each row and column represent? |
| 2.1.2 Ordering, column updates and export | How does the table change? |
| 2.1.3 Alignment and table summaries | Which index values, column names and axis does the calculation use? |
Question: What does each row and column represent?
Result: A DataFrame with explicit columns that identify observations and a checked output shape.
A table extends the Series to two labelled axes. Establish its observation unit first.
pd.read_csv(url) returns a DataFrame. usecols=[...] chooses file columns, index_col= chooses index values, and parse_dates=True parses the selected date index.
| Selection | Meaning |
|---|---|
df["AEP"] |
One named column as a Series |
df.loc[index_value, "AEP"] |
One labelled row and one named column |
df.loc[rows, columns] |
Label-based selection on both axes |
df.iloc[row_position, column_position] |
Position-based selection on both axes |
In .loc[:, columns], : means all rows. A date stored as text is not yet a DatetimeIndex.
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".
parse_dates turns the text column into datetime64 and index_col makes it the index, so a plain string indexes straight into the DatetimeIndex: you get the single AEP number for that hour. No strptime, no set_index afterwards — and .loc[‘2017’] selects a whole year the same way.
Before running: After loading, what does raw.loc["2017-07-04 15:00", "AEP"] return?
An empty DataFrame can acquire named columns. A dictionary supplies the same columns in one call; each list must have the same length.
1. Start with two row positions
2. Add one column at a time
3. Build the same table from a dictionary
Both methods give rows 0, 1 and columns ticker, close. .equals(...) returns True when the tables match.
One row represents one illustrative security. set_index("ticker") makes its identifier the row index; verify_integrity=True rejects repeated index values.
1. Move ticker into the index
2. Select by an index value
.index.name reports the index name; .loc selects an index value and a column.
3. Restore ticker as a column
reset_index() preserves the identifiers. The restored table matches the original (True).
The DataFrame’s row index is axis 0 and its column names are axis 1. df.axes contains these two Index objects in that order.
For reductions such as mean, sum and quantile:
| Argument | Direction | Output |
|---|---|---|
axis=0 |
Down the rows | One result per column |
axis=1 |
Across the columns | One result per row |
State the output length and units first. Summing hourly power readings and summing utilities at one hour represent different quantities.
df.axes is a list of two Index objects. Read the index values and column names in the printed table and match them to each axis.
axes[0] is the row index (the DatetimeIndex), axes[1] is the column Index. axis=0 means ‘down the rows’, axis=1 means ‘across the columns’ — the same numbering you pass to sum(), drop() and sort_index().
Before running: What does df.axes[1] return?
df.sum(axis=0) and df.sum(axis=1) are both legal. Compare the length and index of each result.
axis=1 collapses the columns, giving 8 760 combined hourly loads in MW. axis=0 collapses the rows, giving 5 sums of recorded MW readings. Computing energy in MWh additionally requires observation intervals and complete coverage.
Before running: How many numbers does df.sum(axis=1) return?
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.
Question: How does the table change?
Result: A checked table update and a CSV round-trip that preserves identifiers.
With the table structure known, inspect how ordering and updates affect it.
sort_values(by="AEP", ascending=False) puts the peak-load hours first.
The index values travel with their rows: the first row of the sorted frame is the timestamp of the peak. The frame is no longer chronological, which is why sort_index() exists to put it back.
Before running: After df.sort_values(by="AEP", ascending=False), what is the DatetimeIndex?
sort_values also takes axis=1: then by= names a index value 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).
New columns are assignments. drop returns a new frame — compare the original with the returned table.
drop() returns a new DataFrame and leaves df untouched unless you pass inplace=True or re-assign df = df.drop(…). Forgetting this is the most common ‘my column is still there’ bug.
Before running: After df2 = df.drop(["Month", "Season"], axis=1), does df still contain Month?
rename(columns=...) changes column names. replace(...) changes matching values inside a column. Both return a new result.
Change column names
Change category values
CSV stores text and values, without pandas dtype metadata. Save identifiers once; explicitly restore dates and dtypes when loading.
The file is saved in the current Python working directory. Reset a meaningful index first if its values must be saved as columns.
Question: Which index values, column names and axis does the calculation use?
Result: A labelled result with the aggregation axis explained.
Table calculations require both index/column matching and an explicit reduction axis.
df1 is rows 0–2 × columns 0–2. df2 is rows 2–3 × columns 0–3. Their index values overlap in exactly one row.
Arithmetic aligns on the union of index values and the union of column names: 4 rows × 4 columns. Any cell whose index value or column name is missing from either operand becomes NaN, so only the shared row (02:00) and the shared three columns carry numbers.
Before running: What is the shape of df1 + df2, and how many cells are non-NaN?
df.agg(["min", "max"]) calculates named summaries. A dictionary such as {"AEP": ["mean", "std"]} selects different summaries for different columns.
df.apply(function, axis=0) passes each column Series to a function. With axis=1, it passes each row Series. The function’s return value determines the combined result.
Prefer built-in vectorised methods for ordinary arithmetic. Use apply when a supplied function is necessary, and state what that function receives and returns before running it.
agg takes a list of function names, or a dict mapping columns to their own lists. Compare the requested functions with the row index values and column names of the result.
The result has one row per distinct function name (sum, count, mean = 3 rows) and one column per named input column (2). Cells that were not requested — mean of AEP, sum of DEOK — are NaN.
Before running: What does num.agg({"AEP": ["sum","count"], "DEOK": ["mean","count"]}) return?
Build \(Y = 10 + 0.8X + \text{noise}\) with a function that receives one row at a time. Read the new Y column beside X and Noise, then inspect the column means.
With axis=1, each row is a Series whose index is the column names. row.loc["X"] selects its X value.
Before running: X runs from 0 to 99. Compare X, Noise and the new Y column, then explain why the mean of X is 49.5.
Use vectorised arithmetic for 10 + 0.8*newdf.X + newdf.Noise; use apply for a custom function that needs a row or column. Section 2.4 connects simple column calculations into a readable pipeline.
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.
| Part | Question |
|---|---|
| 2.2.1 Missingness and duplicate observations | What is missing, repeated or ambiguous? |
| 2.2.2 Rows, columns and time subsets | Which observations belong in the analysis? |
Question: What is missing, repeated or ambiguous?
Result: A defect report before any rows or values are changed.
An inspectable table can still contain missing or repeated observations. Measure them first.
df.isna() gives one Boolean per cell. .sum(axis=0) counts missing cells per column; .mean(axis=0) gives each column’s missing fraction.
dropna(subset=[...]) tests only named required columns. fillna supplies a replacement; ffill carries past values and bfill uses later values.
Choose a policy from the variable’s meaning, source and decision clock. Fit statistical replacements on training rows. Record row counts and missingness before and after the operation; a successful run does not prove a sound repair.
AEP_hourly.csv contains 178 258 hours, 12 utilities, lots of NaN. We keep four utilities and 2011–2012.
isna() gives a boolean frame; the first sum() counts True per column, the second sum() adds those up; dividing by .size (rows × columns) gives the fraction of missing cells. The per-column counts are the number you act on — one column here is essentially empty.
Before running: What does load.isna().sum().sum() / load.size measure?
Inspect AEP’s missing 04:00 observation on 6 December 2012. Trace the timestamp supplying each replacement.
bfill copies a later observation into the missing hour, using information unavailable at that decision time. ffill uses past observations. Statistical replacements must be fitted on training data; a constant such as zero still needs a valid meaning.
Before running: Which fill is look-ahead bias when the rows are ordered in time?
dropna() removes any row with any NaN. Compare the retained tables and count their rows.
NI is present in exactly one hour of 17 540 — and in that hour COMED and FE are missing too, so a plain dropna() keeps nothing. subset=[‘FE’] drops only the rows missing the column you actually model, keeping 13 916; dropna(axis=1, thresh=…) keeps the columns that are at least half present.
Before running: How many rows does load.dropna() keep?
Rule of thumb from the lecture
Choose a fill from the variable’s meaning, not its missing fraction alone. Fit median or mode replacements on training rows. A long constant fill can shrink spread; retain the missingness audit and measure its effect.
Build clean from load in two moves: drop the hopeless NI column (drop(columns=…)), then drop the rows that still have a NaN. Result: no NaN, three columns, more than 13 000 rows.
Compare the observed FE values with a copy in which missing values are replaced by its mean.
Fill the NaN in load["FE"] with the column mean, into fe_mean. It must contain no NaN and its mean must equal the pre-fill mean (why is that automatic?).
Switch to the smaller pjm_hourly.csv (2014–2017). It has no NaN — but some timestamps appear twice. Print the repeated rows and count the duplicated timestamp index values.
Before running: Inspect every repeated timestamp below. Which index values occur twice, and how does duplicated() count the later copies?
Each repeated index value is 02:00 on the first Sunday of November in the archived file. The two records have different loads. Inspect timezone and source clock conventions before deciding whether either observation is expendable.
drop_duplicates(subset=…) decides which copy survives. Compare the retained rows and the reported counts.
keep=‘first’ and keep=‘last’ each delete one copy per repeated index value (4 rows gone). keep=False deletes every copy — both rows of each pair — so 8 rows disappear.
Before running: hours has 4 duplicated timestamps. How many rows does keep=False remove?
Using the boolean mask form hours.loc[~hours.index.duplicated(keep=…)], remove both copies of every duplicated hour, into nodup. Check: 8 rows fewer than hours.
Question: Which observations belong in the analysis?
Result: An explicit subset with identities and chronological order preserved.
After checking missing values and duplicates, declare which rows and columns belong in the analysis.
A Boolean mask inside .loc[...] selects rows, keeping all columns. Combine conditions with & (and) or | (or), with parentheses around each comparison.
& binds tighter than >, so Python reads 15000 & hours.AEP first — a bitwise AND of an integer with a float Series — and raises a TypeError. Parentheses around each comparison fix it; query() avoids the issue by parsing the string itself.
Before running: What happens with hours.loc[hours.AEP > 15000 & hours.AEP < 16000]?
.query() writes the filter as a string: and / or instead of & / |, and @name splices in a Python variable. Compare its rows with the boolean-mask result.
Identical rows. query reads the column names from the string and evaluates the same logic; the difference is legibility for long conditions and the @variable syntax. This example checks row equivalence; it does not benchmark speed.
Before running: hours.query("AEP > 15000 and AEP < 16000") returns…
isin tests each value against a list. Combine with day_name() to split weekends from weekdays.
~ negates a mask. Weekend mean load is 13 795 MW versus 15 240 MW on weekdays. This descriptive difference does not establish its cause. Section 2.3 computes it with groupby.
A DataFrame is a dict of Series sharing one index. Pull one column out and ask what it is.
A single column is a Series — the object from Chapter 1. That is the ‘dict of Series’ idea made concrete. Asking for a list of columns, hours[[‘AEP’]], returns a DataFrame instead, which is what models expecting a 2-D X need.
Before running: What does hours["AEP"] return?
Before running: Compare the printed endpoint index values. How does the inclusive .loc endpoint differ from the excluded .iloc stop position?
loc[mask, columns] does both selections in one call.
Select the last two columns of hours, keeping only the rows where the last column (DEOK) is above its own mean, into sel.
For time series you never shuffle: the first 80 % of hours train, the last 20 % test.
Split hours by position: train = first 80 % of rows, test = the remaining rows, in time order.
| Part | Question |
|---|---|
| 2.3.1 Grouped aggregation, transform and filter | Should the result have one row per group or per observation? |
| 2.3.2 Joins on multiple columns and validation | Which rows survive the join, and can repeated matches multiply rows? |
| 2.3.3 MultiIndex and long/wide representations | How should the same observations be arranged? |
Question: Should the result have one row per group or per observation?
Result: A grouped result with its output shape checked.
A selected sample can be divided into meaningful groups. Identify what one output row represents.
df.groupby("sector")["feature"] divides observations by a grouping column and selects the values to process.
| Final operation | Result shape | Use |
|---|---|---|
.agg("mean") |
One summary per group | Compare sectors |
.transform("mean") |
One aligned value per input row | Subtract each row’s sector mean |
.filter(function) |
Original rows of qualifying groups | Retain groups passing a rule |
A feature to assign back to the original rows normally needs transform. State whether the output represents groups or individual observations.
Group two years of hourly load by weekday. Check that the result has one row for each observed weekday.
groupby splits the rows into seven groups, applies mean() to each group’s AEP, and combines the seven results into a Series indexed by weekday. That is aggregation. transform (two slides on) is the other shape — it keeps every original row.
Before running: How many rows does pjm.groupby("WeekDay")["AEP"].mean() return?
One row per group (7 weekdays) and one column per aggregation (mean, std, iq = 3). agg([…]) turns a list of functions into a summary table; the named form agg(name=(column, function)) is the cleanest way to get well-labelled columns from different source columns.
Before running: pjm.groupby("WeekDay")["AEP"].agg(["mean", "std", iq]) returns a…
Standardise AEP within each weekday. Compare the input and output indexes and lengths.
transform applies the function to each group and then broadcasts the result back to the original rows, so the output has exactly len(pjm) entries, aligned to pjm’s index — ready to be assigned as a new column.
Before running: What is the length of pjm.groupby("WeekDay")["AEP"].transform(lambda x: (x - x.mean()) / x.std())?
The final Sunday hour is 1.7 sd above the overall mean and 2.4 sd above the Sunday mean. These are full-sample descriptive statistics. For prediction, estimate weekday means and scales on training data only.
Before running: Group means of B: foo → 3, bar → 4. Which rows survive filter(lambda g: g["B"].mean() > 3)?
Use groupby("WeekDay")["AEP"].apply(...) with a lambda to compute the interquartile range (75 % − 25 % quantile) of AEP for each weekday, into iq_day.
groupby(level=0).mean() groups repeated values of the first row-index level. Here we explicitly average repeated timestamps for a descriptive weekly report. Then choose the weekly statistic and inspect coverage. A mean load has units MW; energy over hourly readings has units MWh.
The first Sunday bucket has only 24 valid hours; the next has 168. Taking every 168th row keeps one observation per step and does not compute weekly mean load.
Question: Which rows survive the join, and can repeated matches multiply rows?
Result: A validated join with unmatched rows accounted for.
Check for missing values in the columns used for matching. State which columns to match and how many rows each match may produce.
Text columns hide numbers ('-' for missing income). pd.to_numeric(errors="coerce") turns the junk into NaN.
size counts university-year records, not distinct institutions. Print it beside the research-score mean and state the sample used to rank countries.
pd.merge(..., on=["ticker", "date"], how=...) matches values in both named columns. validate checks whether each match has the expected number of rows; indicator=True reports unmatched rows.
Two tables → one. The 2015 list has 401 universities, the 2016 list 800. pd.merge(u15, u16, on="university_name", how=…). Compare the retained rows and the reported counts.
inner keeps only names present in both years — fewer than 401. left keeps every 2015 row (401) and fills NaN for the ones that left the 2016 list. outer keeps the union: 401 + 800 − 393 matched = 808. Rule of thumb: left to enrich a spine, inner for matched-only, outer to audit gaps.
Before running: Compare the unmatched rows below. Which year does each join preserve, and where do missing values appear?
How many 2015 universities have no 2016 row? Use the left merge: count the NaN in its research_16 column, into dropped (an integer). Check it against len(u15) - len(inner).
Load nine stocks from the public monthly panel: three each in Technology, Financial Services and Energy. Use the ticker and date columns together to identify each row. The stock selection and sector classifications are fixed for this teaching sample.
The sample contains 639 stock/month rows: nine stocks and 71 dates. It is a current-survivor teaching sample with unverified historical sector/availability metadata; use it to practise table operations and feature calculations.
Joining only on ticker would pair observations from different months. Here the reference table deliberately omits one security-month so the coverage check has something to find.
Before running: A left join loses one reference match. How many candidate rows should remain?
The join on ticker and date retains 639 candidates: 638 matched rows and one unmatched row. Require the intended cardinality and an explicit unmatched-record policy.
merge matches rows using the chosen columns and combines their remaining columns. DataFrame.join normally matches indexes. pd.concat joins whole objects along the requested axis.
| Operation | What it does | Important keywords |
|---|---|---|
merge |
Match rows using the chosen columns | on, how, validate |
join |
Match row indexes by default | how, validate |
concat(axis=0) |
Append rows; align column names | axis, ignore_index |
concat(axis=1) |
Add columns; align row indexes | axis, join, keys |
concat uses join="outer" by default; join="inner" keeps only the common index values for axis=1. ignore_index=True replaces the concatenation-axis index, so retain meaningful identifiers elsewhere first.
pd.concat concatenates a list of Series or DataFrames. Use axis=1 for columns and axis=0 for rows. Use pd.concat for appending rows; DataFrame.append() has been removed.
Concatenating two Close series with different date ranges. Inspect the unmatched dates in the combined table.
concat(axis=1) performs an outer join on the index values: every date from either series appears, and a series that has no value for that date gets NaN. The keys= parameter names the resulting columns.
Before running: msft covers 2015–2018, fb only 2016. What does pd.concat([msft, fb], axis=1) contain for 2015 rows?
Allowing repeated stock/date pairs in both tables would conceal this defect. Duplicate one reference row, then ask pandas to require at most one matching row on each side.
many_to_one requires unique stock/date pairs in the reference table; one_to_one requires both sides unique. Check missing values in the matching columns: pandas can match a missing value to another missing value.
pair holds MSFT and FB prices on the same 2016 dates. Set rho to the Pearson correlation of their daily percentage changes (a plain float).
Question: How should the same observations be arranged?
Result: A reshaped table that retains the stock and date for every observation.
After checking that each stock/date pair occurs once, move those values between index levels and columns.
AAPL appears on several dates; a date appears for several stocks. The (ticker, date) pair identifies a row. Two index levels belong to the same row axis; they are not the row and column axes.
| row index | ticker | date | ret_1_0 |
|---|---|---|---|
| 0 | AAPL | 2020-01-31 | 0.054010 |
| 1 | AAPL | 2020-02-29 | -0.114701 |
| 2 | MSFT | 2020-01-31 | 0.079455 |
| 3 | MSFT | 2020-02-29 | -0.045688 |
| ticker | date | ret_1_0 |
|---|---|---|
| AAPL | 2020-01-31 | 0.054010 |
| AAPL | 2020-02-29 | -0.114701 |
| MSFT | 2020-01-31 | 0.079455 |
| MSFT | 2020-02-29 | -0.045688 |
Teal cells form the row index. Each row still has one return. A MultiIndex can have more than two levels.
Use two stocks and three month-end dates from the validated research table. Keep the complete small input visible; the reverse date order will make the sorting step clear.
set_index(["ticker", "date"]) uses the columns in that order: outer level ticker, inner level date. verify_integrity=True rejects duplicate complete pairs. By default, the selected columns leave the data columns.
index.names names the levels; index.nlevels is 2; index.is_unique checks the complete pair, not each level separately.
sort_index() orders rows by the outer level first, then the inner level within it. It moves complete rows, so returns stay attached to their stock and date. sort_values() orders by data-column values instead.
Sort all levels before the date-range examples below. Selection using an exact index value does not itself require a sorted index.
reset_index(level="date") moves only the named level into an ordinary column. The ticker level remains the row index. Here, level means which component of the MultiIndex, not a row number.
Use the name "date" rather than its position 1 when possible. A repeated ticker index is expected after this partial reset.
set_index("date", append=True) adds the date column as another index level. append=True retains the existing ticker index; the default append=False would replace that index.
The result matches panel (True). This append is a keyword of set_index, not the removed DataFrame method.
reset_index() without level moves every row-index level into columns and creates a default integer index. This is useful before a column-based merge or export.
The default drop=False preserves the identifiers as columns. drop=True discards them instead; use it only when those identifiers may be lost.
panel.loc[rows, columns] still has two axes. With ticker first, "AAPL" selects all its dates. A single ticker removes that selected level from the result; a list retains the MultiIndex.
Both outputs are DataFrames with the same three returns. Compare their index names and structure.
xs means cross-section. level="date" selects the date level even though it is not first. drop_level=False keeps the selected date level in the result.
Both select AAPL and MSFT on 29 February. drop_level changes the returned index structure, not which observations are selected.
A tuple gives both index values in level order: ("AAPL", "2020-02-29"). The comma outside that tuple separates row selection from column selection.
Left: a row returned as a Series. Right: a single number. loc selects by index values; iloc continues to select by integer positions.
pd.IndexSlice lets you combine lists, exact values and ranges inside a MultiIndex row selector. The slots follow the current index-level order.
First slot → ticker
Second slot → date: → every value of that level
["ret_1_0"] keeps a DataFrame.
The outer comma separates rows and columns.
idx = pd.IndexSlice is a convenience alias for writing slices across index levels. It does not create an index or change the table.
Here idx = pd.IndexSlice makes a short name for the helper. The first example selects two stocks over a date range; the second uses : for every stock on one date. loc ranges include both endpoints.
These selectors retain both row-index levels. They select existing observations; they do not add missing stock/date combinations.
swaplevel("ticker", "date") exchanges the two levels. It does not sort the rows. Sorting afterwards puts dates first and tickers together within each date.
Choose ticker-first for a stock history, or date-first for a cross-section. The same six observations and returns remain.
The small example used six observations to expose the structure. Apply the same two-level index to all 639 validated stock/month rows. Selecting a stock or a range changes the sample, not the stored panel.
unstack(level="ticker") moves the ticker level from the row index to columns. Date stays in the row index. The values are rearranged, not calculated again.
| ticker | date | ret_1_0 |
|---|---|---|
| AAPL | 2020-01-31 | 0.054010 |
| AAPL | 2020-02-29 | -0.114701 |
| MSFT | 2020-01-31 | 0.079455 |
| MSFT | 2020-02-29 | -0.045688 |
| date / ticker | AAPL | MSFT |
|---|---|---|
| 2020-01-31 | 0.054010 | 0.079455 |
| 2020-02-29 | -0.114701 | -0.045688 |
Each return moves once. No averaging. level="ticker" names the level to move; an omitted level means the last level.
Select the return column as a Series, then move its ticker level to columns. Print the input Series directly so its index, name and dtype remain visible.
Four indexed values become a 2 × 2 table. Repeated complete index pairs must be resolved before unstack; it cannot choose between two values for one cell.
stack() moves the column axis into the innermost row-index level. This frame has one column level, named ticker; it becomes the second row-index level.
| date / ticker | AAPL | MSFT |
|---|---|---|
| 2020-01-31 | 0.054010 | 0.079455 |
| 2020-02-29 | -0.114701 | -0.045688 |
| date | ticker | ret_1_0 |
|---|---|---|
| 2020-01-31 | AAPL | 0.054010 |
| 2020-01-31 | MSFT | 0.079455 |
| 2020-02-29 | AAPL | -0.114701 |
| 2020-02-29 | MSFT | -0.045688 |
The new level is appended after the existing date index. Compare date / ticker here with the original ticker / date.
A wide table contains a cell for each row/column combination. If a pair was absent, unstack places NaN there. This example deliberately removes the last of the four observed returns.
| ticker | date | ret_1_0 |
|---|---|---|
| AAPL | 2020-01-31 | 0.054010 |
| AAPL | 2020-02-29 | -0.114701 |
| MSFT | 2020-01-31 | 0.079455 |
| date / ticker | AAPL | MSFT |
|---|---|---|
| 2020-01-31 | 0.054010 | 0.079455 |
| 2020-02-29 | -0.114701 | NaN |
NaN means no observed return for that pair. Replacing it with zero would assert a zero return without evidence.
Here dropna() explicitly keeps only non-missing observations after stacking. It would also remove originally recorded missing returns, so it is not a universal round-trip policy.
.T exchanges the two axes of a DataFrame. It does not move just one MultiIndex level and does not aggregate. Compare this with unstack("ticker") and stack().
The result still has four cells. Its row index is now ticker and its columns are dates.
After stacking, swaplevel restores the original level order; sort_index restores row order. Check the data values and both index levels.
The complete sample is 71 dates × 9 stocks; both checks return True. With gaps, state the missing-combination policy before claiming that a round trip preserves the source.
pivot rearranges unique pairs. pivot_table can aggregate repeated pairs. melt turns headers into ordinary columns containing names and values.
| ticker | date | ret_1_0 |
|---|---|---|
| AAPL | 2020-01-31 | 0.054010 |
| AAPL | 2020-02-29 | -0.114701 |
| MSFT | 2020-01-31 | 0.079455 |
| MSFT | 2020-02-29 | -0.045688 |
| date / ticker | AAPL | MSFT |
|---|---|---|
| 2020-01-31 | 0.054010 | 0.079455 |
| 2020-02-29 | -0.114701 | -0.045688 |
pivot(index="date", columns="ticker", values="ret_1_0") goes right.melt(id_vars="date", value_vars=["AAPL", "MSFT"], var_name="ticker", value_name="ret_1_0") goes left after restoring date as a column.
index="date" supplies the output row index; columns="ticker" supplies the headers; values="ret_1_0" supplies the cell contents. pivot does not aggregate; duplicate complete pairs raise an error.
The values and axes match the earlier unstack("ticker") result (True). The input representation differs: columns here, index levels there.
id_vars keeps identifier columns; value_vars chooses which columns to lengthen. var_name names the column holding former headers; value_name names the column holding their values.
Two dates × two selected value columns become four rows. The output has ordinary columns and a default row index; melt does not itself create a MultiIndex.
pivot_table(..., aggfunc="mean") combines scores for each country/year pair.
| country | year | research |
|---|---|---|
| HK | 2012 | 58.6 |
| HK | 2012 | 80.1 |
| HK | 2013 | 64.6 |
| HK | 2013 | 85.9 |
| UK | 2012 | 94.2 |
| UK | 2012 | 96.6 |
| UK | 2013 | 95.6 |
| UK | 2013 | 98.1 |
| country / year | 2012 | 2013 |
|---|---|---|
| HK | 69.35 | 75.25 |
| UK | 95.4 | 96.85 |
HK / 2012: (58.6 + 80.1) / 2 = 69.35.index="country" → rows; columns="year" → headers; values="research" → scores; aggfunc="mean" → how scores are combined.
The small sample uses HKU, HKUST, Oxford and Cambridge in 2012–2013. aggfunc defaults to "mean", but writing it explicitly states what each cell represents.
pivot would reject these repeated pairs. Aggregation loses individual university scores; melt cannot recover them. fill_value replaces missing output cells after aggregation; leave them visible unless a replacement is justified.
Two routes produce the same country/year means, shown in the final table.
Direct route: pivot_table(..., aggfunc="mean") → the same table on the right.
| country | year | research |
|---|---|---|
| HK | 2012 | 58.6 |
| HK | 2012 | 80.1 |
| HK | 2013 | 64.6 |
| HK | 2013 | 85.9 |
| UK | 2012 | 94.2 |
| UK | 2012 | 96.6 |
| UK | 2013 | 95.6 |
| UK | 2013 | 98.1 |
| country | year | mean research |
|---|---|---|
| HK | 2012 | 69.35 |
| HK | 2013 | 75.25 |
| UK | 2012 | 95.4 |
| UK | 2013 | 96.85 |
| country / year | 2012 | 2013 |
|---|---|---|
| HK | 69.35 | 75.25 |
| UK | 95.4 | 96.85 |
mean() calculates four values. unstack(level="year") only rearranges them. Both routes give the same four means.
Read the intermediate result before reshaping it. level="year" selects a named index level; it does not mean a DataFrame column or a year value such as 2012.
The numerical check returns True. Use the same aggregation, grouping columns and missing-value rules before treating the two routes as equivalent.
The full selected university sample produces 24 country/year means. Keep record counts beside the means: changing sample composition can change a country average.
Apply the same mappings to stockdata2.csv: one row per date/stock pair. pivot gives 2 305 dates × 5 stock columns; melt restores 11 525 rows. These are recorded price levels, not returns.
pd.crosstab(index=..., columns=...) counts category combinations by default. To summarise a numeric column, also pass values=... and aggfunc="mean". Here it uses the same row groups, column groups and mean as pivot_table.
inventory.csv (Kaggle retail inventory, 20 000 SKUs). Use pivot_table(index="ReleaseYear", columns="New_Release_Flag", values="PriceReg") for releases from 2010 on, then compare its index and column names with the source rows.
Before running: Compare the seven release-year index values and the two flag columns with the source table. What does one cell summarise?
pd.crosstab counts combinations when no values or aggfunc is supplied. A grouped count is a MultiIndex Series; choose explicitly which level to move to columns.
stack() moves the flag columns into an inner index level after release year. Each value is still a previously computed mean price; stacking does not calculate another mean.
.T exchanges the complete row and column axes. Flags become the row index; release years become the column headers. The mean prices do not change.
pd.cut assigns numeric values to intervals. bins supplies the interval boundaries; labels supplies one name per interval. By default, right=True gives right-closed intervals, such as \((150, 300]\).
Before running: pd.cut([100, 200, 300, 400, 50, 175], bins=[0, 150, 300, 1000], labels=["low", "mid", "high"]) — note 300 sits on the 150–300 edge.
300 belongs to mid, whose interval is \((150, 300]\). Values outside the supplied intervals and missing inputs receive a missing category. In the inventory example, the resulting price-tier Series becomes the row grouping for crosstab.
Keep the 2016 universities with world_rank ≤ 100 in top100 (exactly 100 rows), then plot citations against rank.
Plotly Express (px) creates interactive figures; §2.5 explains its interfaces.
In Colab
Copy complete code includes this earlier setup and the visible code below. Run it in Colab or local Python.
Introduced earlier: groupby: one number per group; A second frame: Times university rankings 2011–2016; melt and pivot: long ↔ wide; Your turn: citations vs rank, top 100 of 2016
# Previously introduced: groupby: one number per group
import numpy as np, pandas as pd
url = "https://statpython.pages.dev/data/"
# Previously introduced: A second frame: Times university rankings 2011–2016
uni = pd.read_csv(url + "university.csv")
uni["total_score"] = pd.to_numeric(uni["total_score"], errors="coerce")
# Previously introduced: melt and pivot: long ↔ wide
long = pd.read_csv(url + "stockdata2.csv", usecols=["Date", "stock", "value"])
wide = long.pivot(index="Date", columns="stock", values="value")
wide.index = pd.to_datetime(wide.index)
# Previously introduced: Your turn: citations vs rank, top 100 of 2016
uni["world_rank"] = pd.to_numeric(uni["world_rank"].astype(str).str.replace("=", ""), errors="coerce")
top100 = uni[(uni.year == 2016) & (uni.world_rank <= 100)]
# Table display: retain every column and keep rows on one line.
import pandas as pd
pd.set_option('display.max_columns', None)
pd.set_option('display.max_colwidth', None)
pd.set_option('display.width', None)
pd.set_option('display.expand_frame_repr', False)| Part | Question |
|---|---|
| 2.4.1 Readable chains and safe updates | Can another analyst follow every transformation? |
| 2.4.2 Organise a monthly stock table | How do we keep each stock’s rows in order? |
| 2.4.3 Feature definitions, neutralisation and validation | How is this feature defined and checked? |
Parts 2.4.1–2.4.3 build a readable feature pipeline: organise the observations, calculate grouped measurements and check the result.
Question: How do daily prices become a checked feature table?
Result: Build the table step by step, then express the same steps as a short pipeline.
A market-risk team receives Microsoft prices. It needs a daily return and a flag for moves larger than 3% in either direction, followed by a monthly count.
Date index
price
252 stock-day rows
price · ret · large_move
251 observed-return rows
observations · mean_return
large_moves
12 month rows
A feature is a derived measurement attached to an observation. A pipeline is an ordered sequence of transformations. Here, each daily row describes one stock on one date.
Read the supplied prices, retain adjusted close and select 2015. Rename the column price so the calculation is easy to read. The date is the index; the single column contains prices in USD per share.
There are 252 rows and one column. Adjusted close accounts for the adjustments in this supplied historical file; this exercise does not establish when a vendor published each adjusted value.
pct_change(fill_method=None) computes \(p_t/p_{t-1}-1\) without filling missing prices. The result is a decimal: 0.03 means 3%. .copy() creates a separate working table.
The first return is NaN: the selected table has no preceding price. This is the return ending on the row’s date, available after that close; it is not the following day’s return.
dropna(subset=["ret"]) retains rows with an observed return. subset names the columns checked for missing values.
1. Retain observed returns
2. Add the flag
abs() measures magnitude in either direction. Column arithmetic handles all rows without a Python loop. A missing return is excluded from this report, never treated as zero.
table.assign(new_column=values) returns a DataFrame with the added column. Save that return value to keep it. The original table retains its columns.
Create a new table
Inspect the original
assigned has price and ret; prices still has only price.
A lambda is a short function written as lambda parameter: expression. In assign, pandas supplies the current DataFrame as that parameter.
| Part | Meaning in this example |
|---|---|
table |
A local parameter name for the DataFrame received by the function |
: |
Separates the parameter from the returned expression |
table["price"].pct_change(...) |
Calculates the new column from that table |
The two versions give the same result. The lambda is useful when an earlier step changes the table that the next step receives.
Read from top to bottom: calculate returns → retain observed returns → add the flag. Each method receives the preceding method’s result. The parentheses allow line breaks.
Both versions produce the same index, columns and values. Use named intermediate tables when a step needs investigation; use a short chain when its sequence is already clear.
The daily features are ready. Now create a separate monthly report. index.to_period("M") converts each date to its calendar month; it keeps one value per daily row.
The observation unit is still a stock-day. Several dates now share the same month value; no rows have been aggregated yet.
groupby("month") collects the rows of each month. In named agg, each output is written as new_name=("input_column", "operation").
The output has 12 monthly rows. count counts observed returns; Boolean sum counts True flags. mean_return is the average daily return, not the compounded monthly return. January has 19 observed returns because the first selected day’s return is unavailable.
A function gives a repeated transformation a name. Its first parameter is the input DataFrame; it returns the transformed table. nlargest(n, column) selects the n rows with the largest values of that column.
The function does not change the original monthly table. Tied counts follow nlargest’s default first-occurrence rule.
table.pipe(function, argument) calls function(table, argument): the table is supplied as the first argument. The function determines what is returned.
Here pipe is another way to call the function just taught. It is useful for inserting a named calculation into a longer sequence; ordinary function calls remain clear for a standalone step.
Creating an independent subset and editing it does not add a column to its source table. .copy() makes the intended separation explicit.
Edit the separate table
Inspect the source table
The source still has no review column. Writing to the subset is appropriate when that separate table is the intended output.
For frame.loc[rows, column] = value, the Boolean condition chooses rows and the column name identifies the destination. Both refer directly to frame.
There are 16 check rows and 235 normal rows. Avoid chained assignment such as frame.loc[rows]["review"] = value: under copy-on-write it cannot update the original. Warning behaviour depends on pandas settings and version.
The team now wants to review moves larger than 2%. Keep the same 251 observed returns and print the revised table.
Change the threshold in the supplied code from 3% to 2%. Do not change the price input or replace missing returns with zero.
Explain why the flag count changes while the number of daily observations stays the same.
Question: How do we organise each stock’s rows before calculating historical values?
Result: A sorted monthly table, checked stock/month rows and grouped historical values.
The team compares nine stocks at each month-end. Each row represents one stock and one month.
Use the ticker and date columns together to identify each row. A stock can appear on several dates, and a date can contain several stocks.
This teaching file contains one row per month for every selected stock. The stock selection and sector classifications are fixed.
| Column | Meaning |
|---|---|
| ticker | Which stock? |
| date | Which month-end? |
| Column | Role |
|---|---|
| ret_1_0 | Return ending this month |
| Column | Role |
|---|---|
| momentum_3m | Compound three earlier months |
parse_dates=["date"] creates a datetime column. Inspect the source before filtering. Returns in this file use decimals: 0.05 means 5%.
isin retains the named stocks. The date condition retains 2019 onwards. Sort by stock and then date before within-stock time calculations; reset_index(drop=True) supplies a simple unique row index.
The sample contains 639 rows: 9 stocks × 71 months. Ticker is sufficient for this fixed classroom file; it is not a permanent real-world security identifier.
duplicated(id_columns) marks repeated stock/month pairs. Check that the ticker and date columns have no missing values and that each pair occurs once. assert stops execution when its condition is false.
An empty problem table is the expected result here. Investigate a failed check before merging, rolling or fitting.
groupby("ticker")["ret_1_0"].shift(1) shifts separately inside each stock. The first row of every stock becomes missing. An ordinary whole-column shift could copy the previous stock’s last return.
| Stock / month | Return |
|---|---|
| A / Jan | rA1 |
| A / Feb | rA2 |
| B / Jan | rB1 |
| B / Feb | rB2 |
| Stock / month | Lag |
|---|---|
| A / Jan | NaN |
| A / Feb | rA1 |
| B / Jan | NaN |
| B / Feb | rB1 |
Read the printed rows in date order: February takes January’s return; January has no earlier row in this sample. Compare the starts of AAPL and MSFT.
There are nine missing first lags: one for each stock.
rolling(3, min_periods=3).mean() uses the current and previous two rows within a stock and requires three observed values. The result is a Series indexed by ticker and original row index.
The extra ticker level records group membership. Do not assign these two-level index values directly to a table with a one-level row index.
droplevel(0) removes only the added ticker level. reindex(research.index) puts the values in the original row order. Shift the completed mean by one within each stock to exclude the current month.
The first three rows per stock are warm-up rows: 27 missing shifted means. The fourth row uses the first three completed months.
Use shift(2) to get the value two rows earlier within each stock. This teaching file has one row per month for every stock.
Change the supplied one-row shift to a two-row shift. Print the first four rows of each of the two stocks.
Question: What does a feature measure, and which rows and dates define it?
Result: Explicit feature definitions, intermediate calculations and dated validation checks.
A feature name alone does not specify its calculation. Record the columns that identify an observation, formula, input, units, window endpoint, availability and missing-value policy.
| Feature | Definition used in this section |
|---|---|
| Lagged return | Previous completed month’s decimal return |
| Three-month momentum | Compound the previous three months, excluding the current month |
| Three-month volatility | Sample standard deviation of those months’ log returns |
| Sector-relative momentum | Subtract the same-date sector mean |
Use the same feature_lab table and momentum_3m column throughout the following comparisons.
At the end of April, the three-month example uses January, February and March. April’s realised return is excluded by our definition; May’s outcome is future information.
Different strategies may include the current completed month. Either choice must be declared before calculation.
| Month | Use? |
|---|---|
| January | Yes |
| February | Yes |
| March | Yes |
| Month | Role |
|---|---|
| April | Decision; skip current month |
| May | Future target; never an input |
For simple returns, momentum is \(\prod_j(1+r_j)-1\). Two moves of +10% and −10% leave \(1.1\times0.9-1=-0.01\), a 1% loss.
np.log(x) means \(\ln(x)\); np.log1p(r) means \(\ln(1+r)\). A 5% return needs \(\ln(1.05)\). Sum the log returns, then use np.expm1(x) — meaning \(e^x-1\) — to recover the compound return. Require \(r>-1\).
Use the same within-stock lag already taught. Keeping the shifted log return as a column makes the window inputs visible.
The missing first value is expected. Do not fill it with a return from another stock or an invented zero.
Sum three shifted log returns within each stock, restore the original row index, then convert back to a simple cumulative return. min_periods=3 refuses an incomplete window.
The fourth monthly row is the first valid three-month value for each stock. Across nine stocks, 612 rows are valid and 27 are warm-up rows.
The sample standard deviation is \(s=\sqrt{\sum_j(x_j-\bar x)^2/(n-1)}\). Here \(x_j\) is a monthly log return. ddof=1 uses the sample denominator; min_periods=3 requires three observations.
Both use the same past-only window. Momentum records compounded direction; volatility records dispersion. Annualising a monthly standard deviation by \(\sqrt{12}\) assumes uncorrelated-return scaling.
Stocks in the same sector can share a broad movement. Subtract the same-date sector mean to measure each stock relative to its peers: \(x_{i,t}-\bar x_{s,t}\).
The diagram uses an illustrative three-stock sector. The code on the next slide uses the actual research table.
| Stock | x |
|---|---|
| A | 0.1 |
| B | 0.2 |
| C | 0.3 |
| Stock | x − mean |
|---|---|
| A | −0.1 |
| B | 0 |
| C | 0.1 |
| Stock | z-score |
|---|---|
| A | −1 |
| B | 0 |
| C | 1 |
groupby(["date", "sector"]) creates the peer groups. transform("mean") repeats each group mean along its original rows, so subtraction aligns with the feature Series.
Use the first month with a complete historical window to inspect the comparison. Subtract each same-month sector mean from the three-month momentum just calculated.
A sector z-score is \((x-\bar x_s)/s_s\). transform("std") supplies the peer sample standard deviation. Replace zero dispersion with missing before division; a one-stock group also has no sample standard deviation.
Each sector in this fixed sample has three stocks. Never replace an undefined peer score with a neutral zero without an explicit policy.
groupby("date").rank(pct=True) orders stocks against all stocks at that date. It does not subtract sector effects. With tied values, pandas uses average ranks by default.
A rank describes position within the same-month comparison. Larger momentum receives a larger percentile rank; ties receive their average rank.
A causality check reruns exactly the declared past-only formula. The function sorts the input, lags by stock, forms a three-row window and restores the caller’s row index.
Change returns strictly after a fixed cutoff. A correctly past-only feature at or before the cutoff must remain unchanged. assert_series_equal checks the values and pandas structure, including the index.
Matching earlier outputs is evidence about code timing. It does not verify a vendor’s historical release times or the original universe.
A single whole-sample percentage can hide a broken month. count() reports valid values per feature and date; a missing-value fraction reports the share unavailable.
The first three dates have zero ready three-month features; the fourth has nine. Inspect peer counts and unusually large or small values as well. A valid extreme observation is not automatically a data defect.
A missing feature needs an explanation. The first three months of a three-month historical window are unavailable because there is not enough earlier data; filling them with zero would change the meaning.
Retain the stock and date columns, report coverage by month and investigate unexpected gaps. Compare the input and output tables before using a derived column.
| Part | Question |
|---|---|
| 2.5.1 Plotly Express and column mappings | Which columns control the chart? |
| 2.5.2 Graph Objects, traces and subplots | Which trace belongs on which panel? |
The same observations and variables can be drawn through different interfaces.
| Compare | Matplotlib | Plotly |
|---|---|---|
| Draw | plt.scatter(x, y) or ax.scatter(x, y) |
px.scatter(df, x="a", y="b") |
| Figure structure | Figure → Axes → drawing objects | Figure → data traces + layout |
| Inspect | Static image here; interactive backends also exist | Browser hover, zoom and toolbar |
| Share | PNG / SVG / PDF | Interactive HTML or static image |
Use the same sample, units and axis ranges. The library does not change what the data mean.
Keep the first three university observations from 2016 and their two score columns.
One point represents one university. Both examples use exactly these rows and the same axis ranges.
scatter draws paired Series; Axes sets labels and limits.
Hover reveals the university for the same three score pairs.
Question: Which columns control the chart?
Result: An interactive comparison with stable identities, scales and labels.
The engineering workflow produced a table. Map its columns to an interactive chart. Run the Python boxes here, then hover, zoom or change a column name and run again.
Reuse the original university ranking file. Parse numeric columns, keep explicit names and select a stated sample.
One row describes one university in one year. Inspect the original column names and values before changing them.
Remove the tied-rank = marker. errors="coerce" leaves rank bands missing instead of inventing an exact rank.
Keep rows satisfying both conditions, then inspect the selected table and missingness.
After removing the tied-rank = marker, the sample contains 100 universities and no missing rank, citations or teaching values. Rank bands elsewhere in the source become missing numeric ranks rather than invented exact positions.
Map one numeric column to the horizontal axis and name the sample in the title.
Hover reveals the displayed bins. nbins guides automatic binning; use explicit xbins through Graph Objects when exact shared edges matter.
Map country to colour and retain university identity in hover information.
Country colour gives groups; numeric colour gives a continuous scale.
Label a small subset of observations. Add a separate annotation for the sample definition.
text labels data rows. An annotation can explain the chart in data coordinates or normalised paper coordinates.
Express accepts a list of value columns. Here both scores use the same 0–100 scale.
Sort the horizontal coordinate before connecting points. This is a rank profile, not a time trend.
Rotate the 3D view to inspect teaching, research and citations together.
Rotation changes apparent distances and overlap. Use a 2D comparison or facets when a reader must compare values precisely.
Keep the three countries with the most observations in this sample and share axis ranges.
Compare the top 20 in each year. University name identifies the same institution across frames.
Use Play or the year slider to compare six frames, 2011–2016. The downloadable lab uses the same data.
Stable identities preserve which observation moves and fixed scales prevent rescaling from looking like change.
Discuss: What makes year-to-year animated points comparable?
Question: Which trace belongs on which panel?
Result: An editable figure with explicit trace and layout choices.
Explicit traces and layouts provide control beyond the first column mapping.
Build one trace and one layout, then combine them in a Figure.
The trace supplies data and its drawing mode. The layout supplies the shared chart presentation.
The same structure uses data and layout entries. Use documented properties, then let go.Figure validate them.
One trace survives and the title is Dictionary figure. Dictionaries are useful for serialisation; they follow the same figure schema.
Construct a rank profile, add the second score and refine titles without rebuilding the data.
add_trace changes the plotted data series. update_layout changes shared presentation. update_traces changes trace properties.
Place the teaching and research profiles in separate panels with a shared score range.
Express creates Graph Objects figures; add_trace and update_layout can refine either interface.
Discuss: How do add_trace and update_layout refine a figure created with either interface?
Change x_column from "teaching" to "research", then press Run Code. Compare the horizontal axis and hover information.
The same 100 university rows remain in the chart; changing the mapping changes which score is compared with citations.
Plotly make_subplots uses one-based row and column positions; route each trace with row and col.
Discuss: Which operation places a Plotly trace in the second panel of a one-row grid?
For the group deliverable, produce one labelled chart of missingness, group coverage or a derived feature. State the sample, units and data limitation.
Recreate it in Express and refine one layout property. Recheck row counts whenever you change the chart’s sample.
You should now be able to turn an unfamiliar table into a checked dataset and explain each transformation.
| Task | Evidence to show |
|---|---|
| Inspect and repair | Row meaning, shape, missing values and repeated observations |
| Group and join | Expected output shape and explained row counts |
| Reshape | The same observations represented in long or wide form |
| Build a feature | A stated formula and calculations within each stock |
| Explain the result | Input/output previews, coverage checks and a labelled chart |
Three prompts that make an LLM useful for this chapter — and the pitfall each one guards against.
df.index.duplicated().sum() and df.isna().sum(); if either is non-zero, stop and show me the offending rows.” Repeated local index values and missing columns require explicit policies..shape after every load, reshape or merge (read_csv, pivot_table, melt, unstack, concat, pd.merge) and explain each change in row count.” Reconcile row identifiers and counts: 2 305 × 5 melts to 11 525 × 3; the 2015/2016 inner join keeps 393 of 401 left rows. A recurring capacity-sized count is a signal to investigate, not proof of truncation..loc[rows, cols] = or as an assign inside a chain — never df[mask][col] = … — and tell me if pandas would raise SettingWithCopyWarning.” Under copy-on-write, an intermediate-object write does not update its parent; warning or error behaviour depends on pandas version and settings.On 4 October 2020, PHE reported 15 841 positive tests from 25 September–2 October omitted from daily reporting and delayed transfer to contact tracing. The patients had received their test results. PHE statement, Parliament update.
Legacy .xls sheets hold at most 65 536 rows. Verify file capacity, source row identifiers and counts before publication; successful execution alone does not establish completeness. Microsoft compatibility limits.
To: Head of Forecasting, grid analytics From: <Your name>, data engineering Subject: Handling of missing and duplicated hours in
AEP_hourly.csv/pjm_hourly.csvbefore modelling Date: 2026-09-15Recommendation: For this proposed aligned model, use AEP, COMED and FE; drop NI. Evaluate forward fills for the two single-hour gaps with imputation flags; exclude FE’s five-month gap rather than invent loads. Preserve repeated local-time records with a repeated-index flag until source clock and timezone evidence resolves their identity. Choose aggregation for a stated hourly-profile estimand.
Evidence: -
load.isna().sum()on 2011–2012 (17 540 hours): AEP and COMED each miss 1 hour, FE misses 3 624 (January–May 2011), NI is present in 1 hour of 17 540. A plaindropna()keeps 0 rows;dropna(subset=["FE"])keeps 13,916. - Filling FE’s gap with its median keeps every row but cuts the column’s standard deviation from 1,422.5 to 1,267.7 MW: a fifth of the series would sit at one value, understating the variance any risk or forecast model is built on. - At the missing hour,bfilluses a later observation unavailable then.ffilluses earlier information but still assumes the carried load is suitable. Fit statistical replacements on training data and validate their effect. -pjm_hourly.csv2014–2017 has no NaN and 4 repeated local index values, each 02:00 on the first Sunday of November. The paired loads differ; the file supplies no timezone offset.keep="first"or"last"removes one row per index value;keep=Falseremoves both. These row policies do not establish which physical observations the index values represent.Caveats: - Excluding FE’s gap removes January–May 2011 for an aligned three-utility model. Separately dropping each utility’s missing row keeps 17 539 AEP or COMED records; filling instead retains records with explicit imputation flags. - Retaining, dropping or aggregating repeated index values changes weights and coverage. Measure its effect on the actual hourly means and peak audit; a row-count fraction alone does not determine the change in a mean.
Next step: Assert the declared schema, expected row counts and identifying-column uniqueness. Report missingness and repeated local index values for investigation before aggregation; retain source provenance and the chosen policy.
| Concept | Tool |
|---|---|
| Axes, sorting, alignment | df.axes, sum(axis=), sort_values, sort_index, df1 + df2 |
| Aggregation | agg([...]), agg({col: [...]}), agg(name=(col, fn)), apply(f, axis=) |
| Missing and duplicates | isna().sum(), fillna, ffill, dropna(subset=), index.duplicated, drop_duplicates(keep=) |
| Filtering and subsets | (a) & (b), query, isin, loc[mask, cols], iloc |
| Reshaping and joining | groupby().agg/transform/filter, pd.merge(how=), pivot_table, crosstab, melt, stack/unstack, pd.concat, pd.cut |
| Research joins and panels | merge(on=[...], validate=, indicator=), set_index, xs, swaplevel |
| Grouped features and checks | grouped shift/rolling, transform, counts and missing-value reports |
| Visual communication | Plotly Express, Graph Objects, make_subplots |
| Idioms | (df.query().assign().groupby().agg()), pipe, one .loc[rows, cols] =, vectorised column arithmetic |
Next: Chapter 3 — Reshaping Statistics.
ISOM 5650 · Prof. Xuhu Wan · HKUST Business School