pandas II: Cleaning, Time Series & the Excel↔Python Bridge

Branch: Data Science & ML (quant-ds) · Finance galaxy, Quant-Code region · Node DA1.03 · ~18 focused hours · Gate: quiz ≥ 85% + code-pack ≥ 85% (hidden tests green on at least 85% of exercises)

DA1.02 taught you pandas on data that already behaves: a clean CSV, sensible column names, numbers that are already numbers. Almost no real data looks like that. A broker's export arrives with currency symbols glued to the digits, blank cells where a feed timed out, the same row submitted twice by a flaky retry, a date column that silently means two different things on two different machines, and a wide crosstab shaped for a human eye rather than a program. Practitioners routinely put the "cleaning tax" at somewhere around 60–80% of a real analytics project's time, a figure worth treating as an order of magnitude, not a citation, but not one you should be surprised by once you have lived it. This module is where you stop being surprised. It teaches the pandas vocabulary for making data trustworthy, missing values, type coercion, duplicates, string repair, reshaping, and then the pandas vocabulary for time itself: a real datetime index, resampling, rolling windows, and the point-in-time joins that every later quant module leans on.

Both threads converge on one deliverable this module exists to hand you: the canonical Excel↔Python bridge, a single reference mapping every Power Query cleaning-and-reshaping step you already know (EX4.01, EX4.02) and the portfolio-tracker pipeline you already built (EX7.02, Capstone 3) onto its exact pandas equivalent, in both directions, with an honest answer to "which tool, when." You are not learning pandas instead of Power Query. You are learning that they are the same handful of ideas, coerce the type, fill down, remove the duplicate, unpivot the crosstab, merge on a key, group and total, wearing two different syntaxes, and that a professional analyst moves fluently between whichever one the room, the scale, and the audience actually call for.

Everything below is graded. This module's code pack (pack id DA1.03, exercises DA1.03-e01 through DA1.03-e09) turns every cleaning and reshaping idea into a real, hidden-tested pandas function, checked, as always in this program, against both an India (₹, nse-eod-flavored) and a US ($, us-eod-flavored) case, so a solution that only works on the example you were shown cannot pass.

Learning objectives

By the end you can:

1. Explain pandas' vocabulary for missing data: NaN/None/pd.NA, isna()/notna(), dropna(), fillna(), .ffill()/.bfill(), .interpolate(), and choose correctly among them for a given gap.
2. Coerce types deliberately: astype(), pd.to_numeric(..., errors="coerce"), pd.to_datetime(..., dayfirst=...), and the category dtype, including the exact locale trap (dd/mm vs. mm/dd) that also bites Excel's Change Type, and its pandas-side fix.
3. Find and remove duplicate rows correctly with duplicated()/drop_duplicates(subset=, keep=), distinguishing an exact duplicate from a merely-similar row.
4. Clean and reshape text with the .str accessor (strip, replace, contains, extract, split, case normalization) and explain why a merge or group-by key must always be cleaned first.
5. Reshape data both ways: pd.melt() to unpivot a wide crosstab into tidy long form, and .pivot_table() to pivot it back, and state why "tidy" (one row per observation) is the shape every downstream tool wants.
6. Combine frames correctly: pd.concat() to stack same-shaped tables (Append) versus pd.merge() to join on a key (Merge), including every join kind (how="left"/"right"/"inner"/"outer") and how to isolate an anti-join.
7. Aggregate with .groupby(...).agg(...), and read its result correctly (including its default sort order, and its most common surprise).
8. Build and use a real DatetimeIndex: pd.to_datetime, set_index, pd.date_range, tz_localize/tz_convert, as the foundation every time-series operation below assumes.
9. Resample a time series to a new frequency (.resample(...).last()/.mean()/.ohlc()), and explain why this is not the same operation as slicing every Nth row.
10. Compute rolling and expanding statistics (.rolling(window).mean()/.std()), and shift, difference, and percent-change a series (.shift(), .diff(), .pct_change()), including a first, mechanics-only look at pd.merge_asof for a point-in-time join.
11. Recite the canonical Excel/Power Query ↔ pandas bridge for every step above, in both directions, and apply a concrete decision framework for which tool a given task actually calls for.
12. (Productivity objective: R10 duality.) Use an AI coding assistant to draft a cleaning pipeline or translate an existing Power Query M script into pandas, while verifying every line yourself, running this module's own hidden tests before trusting any of it, and never accepting a translation you cannot personally justify step by step.

The duality, stated once (R10). Objectives 1–11 are the understanding objective: the gate (quiz ≥ 85%, code-pack ≥ 85%) rewards being able to write and explain this pandas yourself, from a blank editor. Objective 12 is the productivity objective: a real developer routinely has an AI assistant draft the first pass of a cleaning script or translate an M query, the skill is knowing enough, from objectives 1–11, to catch the draft when it is subtly wrong, and to never ship a line you cannot defend.

Prerequisites & connections

Builds on. DA1.01 (NumPy & Vectorized Computation) gave you the array mechanics (vectorization, broadcasting, why a loop over a numpy array is the wrong instinct) that pandas is built on top of; every Series here is a labeled numpy array underneath. DA1.02 (pandas I. Data Wrangling) gave you the Series/DataFrame object model itself: construction, .loc/.iloc indexing and selection, basic filtering and sorting, and a first, single pass at reading a clean CSV. This module assumes all of that and does not re-teach it (R1), if you find yourself unsure what .loc[] does or how a DataFrame is shaped, that is DA1.02's territory, not this one's. What DA1.02 did not cover, and what this module owns outright, is the fact that real data arrives dirty (cleaning) and that a huge share of finance data is fundamentally ordered by time in a way a plain row index does not capture (time series), plus the deliberate, named payoff of connecting all of it back to the Excel/Power Query skills you may already carry from the Excel track.

A note if you came from the Excel track. EX4.01 (Power Query I. Import & Clean) and EX4.02 (Power Query II. M & Pipelines) taught you Change Type, Trim/Clean, Fill Down, Remove Duplicates, Unpivot Other Columns, Group By, Append vs. Merge Queries, and the try ... otherwise error-row reflex, and EX7.02 (Excel Capstones, Capstone 3) had you build a real Power Query portfolio tracker on top of all of it. This module leans on every one of those names throughout and duplicates none of their teaching (R1), it assumes you already know what those Power Query operations do and shows you their exact pandas equivalent. If you have not done the Excel track, every mapping below still stands alone: each pandas technique is taught in full here, with the Excel-side name given only as a one-line orientation, not a prerequisite.

Feeds forward. DA1.04 (EDA & Visualization) assumes data that is already clean and correctly typed, the charts you build there are only as honest as the cleaning you do here. DA1.05 (Statistics in Code) will take the rolling means, returns, and resampled series you learn to compute here and connect them to the statistical theory of what they mean (autocorrelation, stationarity, the variance of a mean), that theory is QM's and DA1.05's territory (C-3 binding boundary); this module owns only the correct pandas mechanics that compute the numbers, never their statistical interpretation. DA2.01/DA2.02 (ML I/II) assume you can build a leak-free, correctly-typed feature table without a second thought. And QD1.01 (Market & Fundamental Data Pipelines), which builds point-in-time, survivorship-controlled panels, is where pd.merge_asof (previewed here only mechanically) becomes a load-bearing tool for the single most important discipline in that module: never letting tomorrow's information leak into today's row.

Core teaching content

Why cleaning is most of the job

DA1.02 handed you tables that were already trustworthy so you could learn the object model without fighting the data at the same time. That was a deliberate simplification, and it is now over. A real feed hands you text that looks like a number, a blank cell where a value should be, and a row you have already seen. None of these announce themselves with an error, a spreadsheet's SUM silently ignores a text cell, and pandas.to_numeric will happily hand you a wrong-looking-right NaN if you are not watching for it. The discipline this module installs is simple to state and easy to skip under deadline pressure: look at what you were actually handed before you trust any number computed from it. Every section below is a named, checkable answer to one flavor of "the data is not what it claims to be."

Missing data: the pandas vocabulary for absence

pandas represents "no value here" a few overlapping ways: NaN (numpy.nan, the classic floating-point missing marker, note that NaN != NaN is True, the single most surprising fact about it), Python's own None (common in object-dtype columns), and the newer, more consistent pd.NA. You rarely need to construct these yourself; you need to detect and act on them. df.isna() (equivalently .isnull()) returns a same-shaped boolean mask; .notna() is its negation; df["col"].isna().sum() is the single most common first line of any real cleaning script, it tells you the size of the problem before you decide what to do about it.

Four responses to a gap, each solving a genuinely different problem:

• dropna(): delete the row (or column, with axis=1) entirely. Right when a missing value makes the whole row untrustworthy or unusable (a price you cannot compute a return from).
• fillna(value): replace with a fixed value, most often 0 for a count that is genuinely absent-means-zero, or a sentinel your downstream code expects. Wrong the moment "missing" and "genuinely zero" are different facts you need to keep straight, filling a missing price with 0 manufactures a catastrophic, fictional crash.
• .ffill() / .bfill(): carry the last (or next) known value forward/backward. Right for a status or label that is genuinely unchanged until stated otherwise (a stock's sector, a merged-cell region label, this is pandas' Fill Down/Fill Up, mapped in full below) or, cautiously, for a short gap in an otherwise continuous price series. Wrong for a gap so long that "the last known value" is a fiction by the time you reach it.
• .interpolate(): fill by linear (or other) interpolation between the two known neighbors. Right when the underlying quantity plausibly moved smoothly through the gap (a daily index level missing one day). Wrong for anything that jumps discretely (a headcount, a dividend flag), interpolating between two integers can hand you a nonsensical 47.5 employees.

None of these four is "the correct one" in the abstract; each is correct for a specific reason a value is missing, and picking the wrong one is a silent, plausible-looking error exactly like the ones AI0.01 taught you to distrust in a model's fluent prose, a filled-in number looks exactly as confident whether it is right or manufactured.

Type coercion: astype, to_numeric, to_datetime, and the locale trap

A column read from text is, by default, text, pandas will not silently decide a column "is really" numeric or a date any more than Excel decides a cell "is really" a number before you tell it to. df["col"].astype(float) converts a column whose values are already cleanly numeric-looking strings, but it raises the instant it meets anything it cannot parse, exactly the "honest crash" AI0.01 preferred over a silent wrong answer, and exactly wrong for a column you know contains some genuine junk. For that case, pd.to_numeric(df["col"], errors="coerce") converts what it can and turns everything else into NaN, the pandas-side version of Power Query's try ... otherwise reflex (EX4.02), and the exact technique this module's own DA1.03-e01 grades.

Dates carry a trap you will already recognize if you did EX4.01: the same digit string means two different dates depending on locale. pd.to_datetime("05/03/2025") defaults to US-style mm/dd, reading it as May 3rd; the identical string, read dd/mm (India/UK convention), means March 5th. This is not a pandas bug, it is the identical ambiguity EX4.01 warned about under Change Type ▸ Using Locale…; pandas' fix is the explicit dayfirst=True argument: pd.to_datetime("05/03/2025", dayfirst=True) gives March 5th. The rule in both tools is the same, never trust a slash-delimited date's default parse without knowing your source's convention, and state it explicitly rather than hoping the ambient locale happens to match.

The category dtype is worth a mention here even though it looks like a minor detail: a column of a small number of repeated string values (a sector, an exchange, a rating) stored as category instead of raw object text uses dramatically less memory and makes groupby faster, free once you know to reach for .astype("category") on a column you know is low-cardinality.

{
  "skill": "Drafting a first-pass pandas cleaning pipeline from a description of dirty data",
  "use": "Describe the mess in a raw column (currency symbols, blanks, an inconsistent date format) to a coding assistant and have it draft the to_numeric/to_datetime/str-cleaning lines — then verify every line against this module's own hidden tests before trusting it on anything else.",
  "tools": ["AI coding assistants — Claude, ChatGPT, GitHub Copilot", "This module's code pack's own visible tests, as your first line of defense"],
  "prompt": "I have a pandas column of raw strings that should be numeric prices. Some cells have a currency symbol, some have thousands commas, some are blank or 'N/A'. Write the pandas lines to clean this into a float column with missing values as NaN, and explain each line.",
  "verify": "Run the draft on a small example you have already hand-computed the right answer for (this module's own DA1.03-e01 sample is exactly built for this). If it does not reproduce your by-hand answer, the draft is wrong regardless of how clean the code looks — a plausible-looking regex or an errors= argument silently pointed the wrong way both run without crashing. Then run it against DA1.03-e01's visible AND hidden tests before trusting the pattern on real data.",
  "diy": "You must already understand what pd.to_numeric(errors='coerce') and the locale trap in pd.to_datetime actually do (this section) before you can tell a correct draft from a subtly wrong one — an AI assistant will produce confident code for either.",
  "market": "IN"
}

Duplicates: duplicated() and drop_duplicates()

df.duplicated() returns a boolean mask marking every row that is an exact repeat of an earlier one (keep="first" by default, the first occurrence is not marked, every later repeat is); df.drop_duplicates() uses the same logic to actually remove them. Two arguments matter in practice: subset=[...] restricts "duplicate" to agreement on only certain columns (a re-quoted price with a fresh timestamp is not a duplicate of the whole row, but might be one on subset=["ticker","date"] if you want "one row per ticker per day"), and keep= chooses which occurrence survives ("first", "last", or False to drop every copy including the first, useful when a repeat itself signals corrupted data you want gone entirely). This is the direct pandas equivalent of Power Query's Home ▸ Remove Duplicates (EX4.01): same purpose, more explicit knobs.

String cleaning: the .str accessor

Every string method you would reach for by hand is available, vectorized, through a Series' .str accessor: .str.strip() (Power Query's Trim), a regex-aware .str.replace(pattern, repl, regex=True) (Clean, and much more), .str.contains(pattern) for filtering, .str.extract(pattern) to pull a captured group into a new column, .str.split(delim, expand=True) (Split Column), and .str.lower()/.str.upper() for case normalization. That last one carries a genuine trap worth naming explicitly: a merge or groupby key is matched exactly, case-sensitively, precisely the same behavior EX4.01 warned about for Power Query's own merge keys, so "Delhi" and "delhi" are two different groups to both tools unless you normalize case (and .str.strip() away stray whitespace) on every key column before you group, merge, or append on it. This single habit (clean the key before you join it, not after) prevents the single most common silent-data-loss bug in this entire module.

Reshaping, both ways: melt and pivot_table

A wide crosstab (one row per entity, one column per period (Product down the side, Jan/Feb/Mar across the top)) is built for a human eye and is exactly the shape EX4.01 spent an entire section warning you that every downstream tool (a chart, a further groupby, a model) refuses to work with cleanly. The fix, both there and here, is tidy data: one row per observation. pd.melt(df, id_vars=["Product"], value_vars=["Jan","Feb","Mar"], var_name="Month", value_name="Sales") is the exact pandas equivalent of Power Query's Unpivot Other Columns, it melts the three value columns into two new ones (Month, holding what used to be a column header, and Sales, holding what used to be a cell), one row per (product, month) pair.

The reverse operation (needed the moment you must hand a human a report rather than feed a program) is .pivot_table(index=, columns=, values=, aggfunc=), Power Query's Pivot Column. Give it the column to spread across the top (columns=), the column to keep as the row label (index=), and the column holding the numbers (values=); aggfunc= decides what happens if more than one row shares the same (index, column) combination, "first" if that should never happen and you want to see it plainly if it does, "sum"/"mean" if a genuine rollup is intended. This is the direct sibling of .pivot() (no aggfunc, and it raises the instant it meets a duplicate index/column pair, rather than silently choosing one), reach for pivot_table the moment "there might be more than one matching row" is even a possibility, and reach for the stricter pivot() only when you have already proven there cannot be.

Combining frames: concat (Append) versus merge (Merge)

Exactly as in Power Query, there are two structurally different ways to combine two tables, and confusing them is the single most common reshaping mistake in this module.

pd.concat([df1, df2], ignore_index=True) stacks same-shaped tables into more rows, this month's export and last month's, three regional files, a folder of monthly CSVs read in a loop. It is the direct equivalent of Power Query's Home ▸ Append Queries, and it assumes (or silently tolerates, with NaN filling any gap) that both frames share the same columns.

pd.merge(df1, df2, on="key", how=...) joins different tables that share a key into more columns, the query-side lookup, exactly Power Query's Home ▸ Merge Queries. The how= argument is Power Query's Join Kind dropdown, one to one:

An anti-join ("which rows had no match at all") is not a separate Power Query Join Kind by accident of naming; both tools build it the same way: run the outer join you need with indicator=True (pandas) or expand and inspect (Power Query), then keep only the rows flagged as unmatched (df[merged["_merge"] == "left_only"]). This is exactly how you would find, say, every holding whose ticker has gone missing from today's price feed, the same diagnostic EX4.01's mini-project used a Left Anti merge for.

Group aggregation: .groupby(...).agg(...)

df.groupby("state")["revenue"].sum() is Power Query's Group By (and a worksheet PivotTable's core operation) in one line: split the frame into one bucket per distinct value of the grouping column, apply an aggregate to each bucket, and reassemble one row per group. .agg(["sum","mean","count"]) computes several aggregates over the same grouping in a single pass, and grouping by more than one column (.groupby(["state","product"])) buckets on every distinct combination, exactly a two-level Power Query Group By.

The single most common groupby surprise deserves calling out by name here rather than letting you discover it as a bug later: by default, groupby sorts its groups alphabetically by the grouping key, so grouping a Month column of "Apr", "May", "Jun" text values returns them in the order Apr, Jun, May, not calendar order, because "Jun" sorts alphabetically before "May". This is not a bug in groupby; it is doing exactly what it was asked (sort the group labels), and the fix is to either group by a real datetime/period value instead of a text month name, or to convert the label to an ordered category first. Watch for this every time a grouping key is a name for something that has a natural order other than alphabetical (months, quarters, letter grades, small/medium/large).

Time series foundations: the DatetimeIndex

Every technique in the rest of this section assumes one thing: the frame's row label (its index) is a real, sorted pandas.DatetimeIndex, not a plain integer row number, and not a column of date-looking text. Build one with df["date"] = pd.to_datetime(df["date"]) followed by df = df.set_index("date"), or directly at read time with pd.read_csv(path, parse_dates=["date"], index_col="date"). pd.date_range(start=, end=, freq=) constructs an evenly-spaced index from scratch, useful for reindexing onto a complete calendar and exposing exactly which dates a source is missing (.reindex(full_range), the gaps arriving as NaN for you to dropna(), ffill(), or leave visible, per the missing-data section above). A DataFrame or Series whose index is genuinely a DatetimeIndex unlocks slicing by string date ranges directly (df["2024-03":"2024-06"]) and every operation below, none of them work correctly, or at all, against a plain row-number index.

Time zones deserve one explicit rule: a Timestamp/DatetimeIndex is either naive (no time zone attached, the default, and the source of most cross-market bugs) or aware (a time zone is attached). tz_localize("America/New_York") attaches a zone to a naive index that you know is already in that zone (it does not convert anything, it only labels); tz_convert("Asia/Kolkata") converts an already-aware timestamp to a different zone's clock time. Comparing or joining a US-market series (naive, implicitly Eastern) against an India-market series (naive, implicitly IST) without localizing both first is a standing invitation to silently compare the wrong hours, worth a habit of localizing explicitly the moment more than one market's clock enters the same notebook.

Resampling: changing frequency correctly

A daily price series does not divide evenly into months or weeks (some months have 21 trading days, some have 23) so taking every 21st row is not the same operation as "the month-end value," and will quietly misalign the moment a holiday shifts the count. .resample(freq) is the correct tool: it buckets the index into genuine calendar periods ("D", "W", "ME" for month-end, "QE" for quarter-end, "YE" for year-end) regardless of how many raw rows fall in each bucket, and you choose what happens to each bucket: .last() (the standard choice for a month-end closing price), .mean(), .sum() (right for a flow quantity like daily volume, wrong for a level quantity like a price), or .ohlc() (open/high/low/close of every bucket in one call, built for exactly this use case). .asfreq(freq) is a plainer relative: it only re-indexes onto the new frequency's exact dates, taking whatever value (or NaN) happens to already sit there, with no aggregation, the right choice when your data is already at, or coarser than, the target frequency and you just need it re-gridded, not summarized.

A perishable-syntax note, worth flagging exactly the way AI0.01 flags model specifics: the single-letter frequency alias "M" for month-end was retired from pandas (a bare "M" now raises ValueError, not merely a warning); the current alias, used throughout this module, is "ME". Always check your installed pandas version's own frequency-alias table before shipping a resample.

Rolling, expanding, and shifting: windows over time

A rolling window looks backward over the trailing N observations from each point: .rolling(20).mean() is a 20-day moving average, .rolling(20).std() a 20-day trailing volatility, the exact vectorized replacement for dragging =AVERAGE()/=STDEV() down 500 spreadsheet rows, and the computation behind every moving-average chart overlay you have ever seen. The first N-1 rows of any rolling statistic are necessarily NaN, there is not yet enough history, and that is correct behavior, not a bug to paper over. An expanding window (.expanding().mean()) is the same idea with no fixed width: it looks back over everything so far, useful for a running, all-history-to-date statistic.

.shift(n) moves values forward (or backward, with a negative n) by n positions without touching the index: series.shift(1) gives you "yesterday's value, aligned to today's row," the building block of almost every other operation here. .diff() is series - series.shift(1) in one call, the absolute change. .pct_change() is (series / series.shift(1)) - 1 in one call, the fractional (percentage) change, and specifically .pct_change(periods=12) on a monthly-resampled series is the pandas-side answer to a DAX time-intelligence measure like SAMEPERIODLASTYEAR/year-over-year (EX5.02): shift twelve months back, then compute the fractional difference, no marked Date table required, because a real DatetimeIndex already carries that structure.

One point-in-time join is worth a first, purely mechanical look here, because it will matter for real the moment QD1.01 builds panels that must never leak future information into a past row: pd.merge_asof(trades, quotes, on="time", direction="backward") matches each row of trades to the most recent row of quotes at or before its own timestamp, never a later one. This is structurally different from an ordinary merge, which matches on exact key equality; merge_asof matches on the nearest prior value of a sorted key, which is exactly "what was known as of this moment," the mechanical seed of the look-ahead discipline QD1.01/QD1.03 will make a formal, graded requirement.

{
  "skill": "Translating an existing Power Query (M) script into pandas, and verifying the translation is faithful",
  "use": "Paste an M query you already trust (e.g. your own EX7.02 portfolio-tracker pipeline) to a coding assistant and ask for a line-by-line pandas translation, explicitly naming which M step maps to which pandas call — then recompute the SAME numbers by hand to confirm the translation actually agrees with the original, not just that it runs.",
  "tools": ["AI coding assistants — Claude, ChatGPT, GitHub Copilot", "A known-correct M pipeline (e.g. your own prior Excel-track work) as the ground truth to check against"],
  "prompt": "Here is a Power Query M script: <PASTE M CODE>. Translate it to pandas, line by line, and for each M step name the Excel/Power Query operation it performs and the exact pandas call you used for it.",
  "verify": "Never trust that a translation 'ran without error' as proof it is correct — a swapped how= (left vs. inner), a forgotten dayfirst=, or a resample bucket choice can all run cleanly and produce a plausible, wrong number. Recompute the original M pipeline's known output by hand (or re-open the .xlsx) and confirm the pandas version reproduces the SAME figures before you trust it on new data.",
  "diy": "You must already be able to read the bridge table below and independently name the pandas equivalent of any M step yourself — an AI translation is only checkable by someone who does not actually need it to survive.",
  "market": "US"
}

The canonical bridge: Excel / Power Query ↔ pandas

Here is the reference this whole module has been building toward, every step above, and a few more, mapped one to one. Treat this table as the thing you actually keep; everything before it was building the vocabulary to read it correctly.

And the direction EX4.01/EX4.02/EX7.02 never had to teach, because Excel is the destination: pandas → Excel, for the moment cleaned, joined, resampled data must become a deliverable a non-technical stakeholder opens directly.

When to use which

Neither tool is "better"; each is the right instrument for a different job, and a professional analyst chooses deliberately rather than out of habit.

• Scale. Excel is comfortable and fast to roughly 100k rows, sluggish well before its hard 1,048,576-row ceiling; pandas holds millions of rows in memory comfortably and chunks past that. A folder of a hundred monthly exports is a pandas loop, not a hundred manual Ctrl+Cs.
• Reproducibility & audit. Power Query's Applied Steps pane is auditable, step by step, by someone who has never written a line of code, a genuine strength for a stakeholder who must trust but cannot read Python. A .py script is auditable by code review and git diff, re-runs identically on new data with zero manual re-clicking, and is the only of the two that can be meaningfully unit-tested (this module's own hidden tests are exactly that discipline, applied to your own work).
• Automation. A Power Query refresh needs the file opened (or a Power BI service schedule); a pandas script runs unattended on a cron job, in a CI pipeline, or as part of a larger program, the natural path to everything QD2.04 (Reproducible Research & Deployment) will formalize.
• Audience & handoff. A colleague who only has Excel needs an .xlsx they can open, filter, and print, ship them one (the reverse-bridge table, above). A teammate maintaining a production pipeline needs a script and a test suite, not a workbook. Choosing the wrong deliverable for the audience, regardless of which tool did the actual computation, is its own failure.
• Complexity of logic. A handful of straightforward transforms reads cleanly as Applied Steps; deeply branchy, many-special-case business logic becomes an unmanageable nest of M ifs long before the equivalent Python functions (each individually named, tested, and readable) become unwieldy.
• What comes next. The moment the task is "and now fit a model / run a statistical test / backtest a strategy," pandas is not a choice among equals, it is the on-ramp to numpy/scikit-learn/statsmodels that the rest of this entire branch is built on. Excel does not lead anywhere further in that direction; pandas is where DA1.04 through QD2.05 all live.

The honest summary: reach for Power Query/Excel when the audience is non-technical, the data comfortably fits on one machine's worksheet, and the task is a one-off or lightly-recurring transform someone must be able to audit by eye. Reach for pandas the instant reproducibility, scale, automation, or "what comes after the cleaning" matter more than a point-and-click audit trail, which, for almost everything from here to the capstone, is the case.

Common mistakes & how experts think differently

1. Comparing a cleaned number to NaN with == and getting silent nonsense. df["x"] == float("nan") is False for every row, including rows that are actually NaN, because NaN != NaN is a property of floating-point NaN itself, not a pandas quirk. The expert always tests for missingness with .isna()/.notna(), never with an equality comparison, and treats any == NaN appearing in review as an automatic red flag.
2. Filling a missing price with 0 "to make the math work." A NaN close and a genuine 0 close are two completely different facts (one is "we don't know," the other is "the security is worthless") and fillna(0) on a price column manufactures a fictional 100% crash that will silently poison every return, volatility, and chart computed downstream. The expert asks why a value is missing before choosing a fill strategy, and treats fillna(0) on anything that isn't genuinely absent-means-zero as a bug, not a convenience.
3. Trusting astype(float) on a column you have not actually looked at. It will raise the instant it meets one un-parseable cell, which sounds safe, but a learner who wraps it in a broad try/except and silently drops the whole column on failure has hidden the very problem they needed to see. The expert profiles the column first (.unique() on the offending values, or pd.to_numeric(errors="coerce").isna().sum()) and chooses to_numeric(errors="coerce") deliberately, then explicitly counts and inspects what got coerced to NaN before moving on.
4. Forgetting the locale trap in pd.to_datetime. A date column from an Indian source parsed with pandas' US-default mm/dd reading silently swaps day and month for any date where both values are ≤ 12 (the 13th onward gives a loud, easy-to-catch error; the 1st–12th gives a wrong, silent date), the exact same trap EX4.01 warned about for Excel's own Change Type. The expert states the source's date convention explicitly and passes dayfirst= accordingly, every time, rather than trusting whatever the ambient default happens to be.
5. Merging or grouping on an uncleaned key. "Delhi " (trailing space) and "Delhi", or "NILAM" and "nilam", are different keys to both pandas and Power Query, a merge silently drops the "unmatched" rows into NaN with zero error message, and a groupby silently creates a spurious second, near-empty group. The expert treats .str.strip() + case normalization on every key column as a mandatory step before any merge or groupby, not an optional nicety.
6. Grouping a month-name text column and being surprised by the order. .groupby("Month") on "Apr"/"May"/"Jun" values sorts alphabetically by default (Apr, Jun, May) not calendar order, a fact this module's own worked example below stumbles into deliberately so you see it once, safely, rather than in a real report. The expert either groups by a real date/period value or converts the label to an explicitly-ordered category before grouping.
7. Treating .resample() as "just take every Nth row." Trading calendars have gaps (weekends, holidays, an exchange closure) that make "every 21st row is a month" false the moment a holiday shifts the count, the row-skipping approach silently drifts out of calendar alignment over a long enough series. The expert always resamples against the real DatetimeIndex, never against raw row position, specifically because the whole point of resample is that it does not care how many raw rows fall in a bucket.
8. Reaching for a row-wise .apply(func, axis=1) before trying a vectorized column. It works, and for a small frame nobody notices; at real scale it can be 10–100× slower than the vectorized arithmetic pandas was built to do, because it is pandas' version of exactly the slow, per-row each that Power Query's own M language uses (EX4.02), except pandas offers a fast alternative that M mostly does not. The expert reaches for plain column arithmetic or .assign() first, and treats .apply(axis=1) as a deliberate, justified last resort, not a reflex.

The thread through all eight: the expert never lets a cleaning or reshaping step run silently on faith. Every fill, every coercion, every merge key, every resample bucket is a deliberate choice made after looking at the data, not a default accepted because it happened not to crash, because in pandas, exactly as in Excel, the dangerous failures are never the ones that raise an error.

Worked examples

A note on the data: every figure below is either explicitly synthetic and seeded (R3) or a direct, cross-checked recomputation of a real worked example from this program's own Excel track, every number in every step below was independently recomputed in pandas before being written down, so if you run the same code you will get the same figures.

WE1: India (₹): the bridge, worked end to end, EX7.02's broker tracker, rebuilt in pandas

The task. EX7.02's Capstone 3 built a Power Query portfolio tracker over five Indian holdings, selecting columns by header, never by position (Table.SelectColumns(Source, {"Symbol","Qty","AvgCost","LTP"})) and adding a MarketValue column with Table.AddColumn(Invoke, "MarketValue", each [Qty]*[LTP], type number). Given the same five holdings: RELIANCE 50 @ LTP ₹2,950; TCS 30 @ ₹3,850; HDFCBANK 80 @ ₹1,620; INFY 60 @ ₹1,380; ITC 200 @ ₹465, and the same stated total invested of ₹5,07,000, rebuild the entire tracker in pandas and confirm you recover EX7.02's own numbers exactly.

Solution. Step 1, the "by header, never by position" discipline, for free. Power Query had to be told, deliberately, to select Table.SelectColumns(..., {"Symbol","Qty","LTP"}) by name so a re-ordered broker export could never silently corrupt the model. In pandas this discipline is not a choice you make, it is the only ordinary way to touch a column: holdings["LTP"] always means "the column named LTP," regardless of which position it happens to sit in. You would have to go out of your way with .iloc[:, 3] to reintroduce the position-dependent fragility Power Query's own convention exists to eliminate.

Step 2, build the frame and the value column, in one line each.

import pandas as pd

holdings = pd.DataFrame({
    "Symbol": ["RELIANCE", "TCS", "HDFCBANK", "INFY", "ITC"],
    "Qty":    [50, 30, 80, 60, 200],
    "LTP":    [2950.0, 3850.0, 1620.0, 1380.0, 465.0],
})
holdings["MarketValue"] = holdings["Qty"] * holdings["LTP"]

This one line, holdings["Qty"] * holdings["LTP"], is the exact vectorized pandas sibling of Table.AddColumn(Invoke, "MarketValue", each [Qty]*[LTP], type number), the same idea (multiply two named columns row for row), without the each/per-row lambda M requires, because pandas arithmetic on two columns is already row-aligned by construction.

Step 3, total market value, and the return, against the stated total invested.

total_invested     = 507_000.0                      # EX7.02's own given figure
total_market_value = holdings["MarketValue"].sum()
pnl                = total_market_value - total_invested
pct_return         = pnl / total_invested

Step 4, the numbers, recomputed. MarketValue per row: RELIANCE 50 × 2950 = 1,47,500; TCS 30 × 3850 = 1,15,500; HDFCBANK 80 × 1620 = 1,29,600; INFY 60 × 1380 = 82,800; ITC 200 × 465 = 93,000. Summed: total market value = ₹5,68,400: matching EX7.02's own P9 exactly. pnl = 5,68,400 − 5,07,000 = ₹61,400; pct_return = 61,400 / 5,07,000 = 12.11%, again, EX7.02's own figures, recovered without retyping a single total by hand.

Read it. Nothing above required a new idea; every line is a direct restatement of a Power Query step you already trust, in the syntax this branch uses instead. That is the entire thesis of the bridge table above, made concrete: the same five holdings, the same arithmetic, the same answer: ₹5,68,400 / ₹61,400 / 12.11%, twice, in two tools, because they were never two different techniques to begin with.

WE2: US ($): cleaning before trusting any aggregate

The task. A US ticker's eight-day daily-close export has a real-world data-quality problem baked in: one row was submitted twice by a flaky retry (an exact duplicate), one day's close is blank, and one day's close arrived as the literal text "N/A". Compute the mean close the naive way (the way a quick =AVERAGE() over the raw column would, silently ignoring blanks and text but not catching the duplicate) and the correctly-cleaned way, and compare.

Solution. Step 1, the raw data, exactly as it arrived.

import pandas as pd

raw = pd.DataFrame({
    "date":  ["2024-01-02","2024-01-03","2024-01-04","2024-01-04",
              "2024-01-05","2024-01-08","2024-01-09","2024-01-10"],
    "close": ["100.00","101.50","99.80","99.80","","N/A","103.20","104.10"],
})

Notice 2024-01-04 appears twice, with the identical close both times (the double-submitted row) while 2024-01-05 is blank and 2024-01-08 is the text token "N/A".

Step 2, the naive number (what a careless AVERAGE() sees). Text and blanks are silently skipped by a spreadsheet AVERAGE, but a duplicated row looks like two perfectly ordinary numbers to it, nothing marks it as a repeat. The six genuinely numeric-looking cells are 100.00, 101.50, 99.80, 99.80, 103.20, 104.10 (the duplicate counted twice, the blank and "N/A" silently dropped): sum = 608.40, naive mean = 608.40 / 6 = 101.40.

Step 3, the cleaned number.

clean = raw.drop_duplicates()                                    # the exact-repeat row is gone
clean["close"] = pd.to_numeric(clean["close"], errors="coerce")  # "" and "N/A" -> NaN
clean = clean.dropna(subset=["close"])
clean_mean = clean["close"].mean()

drop_duplicates() removes the second 2024-01-04 row entirely (both date and close matched exactly); to_numeric(errors="coerce") turns the blank and "N/A" cells into real, detectable NaN; dropna removes those two rows explicitly rather than letting them vanish unremarked. What remains: 100.00, 101.50, 99.80, 103.20, 104.10: five genuine observations. Sum = 508.60, clean mean = 508.60 / 5 = 101.72.

Read it. $101.40 and $101.72 are both entirely plausible average closing prices, neither number looks wrong on its own, which is exactly the danger this whole module exists to defeat. The naive figure is wrong twice over: it double-counts one real day (inflating the sample) while silently discarding two others (shrinking it), two errors that happen to pull in opposite directions here, which is precisely why "it looked reasonable" is worthless as a check. Only by explicitly deduplicating and explicitly coercing-and-counting the missing cells do you know, with certainty, which five numbers you actually averaged.

WE3: India (₹): unpivot, then group by, two different ways

The task. A quarterly sales crosstab arrives with three products down the side and three months across the top (₹, illustrative). Melt it into tidy form, then compute total revenue by product and total revenue by month, and read one genuine surprise off the second result.

Solution. Step 1, the wide crosstab, as it arrived.

Step 2: unpivot.

tidy = pd.melt(df, id_vars=["Product"], value_vars=["Apr","May","Jun"],
               var_name="Month", value_name="Revenue")

Three products × three months melts to 9 tidy rows, each one (Product, Month, Revenue) observation, exactly pd.melt's row count formula from the bridge table, id-rows × value-columns.

Step 3, group by product (the expected shape). tidy.groupby("Product")["Revenue"].sum() gives Dupatta ₹27,900, Kurta ₹58,500, Saree ₹1,08,000, alphabetical by product name, which happens to be a harmless order here because product names carry no natural sequence.

Step 4: group by month (the surprise). tidy.groupby("Month")["Revenue"].sum() gives Apr ₹72,000, Jun ₹70,200, May ₹52,200, in that order. Read the order again: Apr, Jun, May. groupby sorted the text labels alphabetically, and "Jun" alphabetically precedes "May." Nothing computed a wrong number, every total (Apr 45,000+18,000+9,000=72,000; May 22,500+21,000+8,700=52,200; Jun 40,500+19,500+10,200=70,200; grand total 1,94,400, matching Step 3's totals exactly, 27,900+58,500+1,08,000=1,94,400) is correct, but a report that printed this table in groupby's own row order would show a chronologically scrambled quarter with no warning at all.

Read it. This is the Common Mistakes list's month-order trap, caught here on purpose rather than in a live report: the fix is either to group on a real date/period value instead of a bare month name, or to convert Month to an explicitly-ordered category (pd.Categorical(months, categories=["Apr","May","Jun"], ordered=True)) before grouping, so the label sorts the way the calendar does.

WE4: US ($): resampling to month-end, then a rolling volatility check

The task. A run of daily US closes spans three calendar months with gaps (weekends, an occasional missing day). Resample to month-end closes and compute month-over-month returns; then, on a second, denser daily run, compute a 3-day rolling mean and standard deviation.

Solution. Step 1, the daily closes and dates. dates = ["2024-01-03","2024-01-31","2024-02-15","2024-02-29","2024-03-10","2024-03-28"]; closes = [100, 105, 108, 110, 111, 121] ($).

Step 2: resample to month-end.

s = pd.Series(closes, index=pd.to_datetime(dates))
month_end = s.resample("ME").last()
returns = month_end.pct_change()

resample("ME").last() takes, for each calendar month present, the last observation actually available in that month. January's last entry is the row dated 2024-01-31 (close 105), February's is 2024-02-29 (close 110), March's is 2024-03-28 (close 121), regardless of how many raw rows fell in each month, which is exactly why this is not the same as "take every Nth row."

Step 3, the returns. January has no prior month, so its return is undefined (None). February: (110 − 105) / 105 = 4.76%. March: (121 − 110) / 110 = 10.00%. Three months of daily data compressed to two genuine, calendar-aligned month-over-month returns, computed once, correctly, regardless of exactly which days each month happened to report.

Step 4, a rolling check, on a denser run. A fresh eight-day daily-close run: [50, 51, 49, 52, 54, 53, 55, 56]. .rolling(3).mean() and .rolling(3).std() give, index by index: the first two entries None (fewer than 3 observations so far); then (50, 1.00), (50.67, 1.53), (51.67, 2.52), (53.00, 1.00), (54.00, 1.00), (54.67, 1.53), a trailing mean and standard deviation that updates one observation at a time, the exact machinery behind a moving-average price chart or a rolling-volatility risk check.

Read it. Two different windows onto the same idea: resample re-grids irregular daily data onto a coarser, calendar-true frequency (fewer, cleaner observations); rolling slides a fixed-width lens within one frequency (same number of observations, a smoothed statistic at each one). Confusing them (resampling when you meant to smooth, or rolling when you meant to change frequency) produces a number that runs without error and means something other than what you intended.

WE5: India (₹): Merge Queries, a Join Kind gotcha, and the reverse bridge

The task. Five Indian holdings need today's price merged on by ticker; one holding's ticker, WIPRO, has no matching row in today's price feed (a data-feed gap, not a genuine sell). Perform the correct join, show what an inner join would have silently done instead, then write the cleaned result to a formatted .xlsx for a stakeholder who does not use Python.

Solution. Step 1, the two tables and the correct join.

holdings = pd.DataFrame({"Symbol": ["RELIANCE","TCS","HDFCBANK","INFY","WIPRO"],
                          "Qty": [50, 30, 80, 60, 40]})
prices   = pd.DataFrame({"Symbol": ["RELIANCE","TCS","HDFCBANK","INFY"],
                          "LTP": [2950.0, 3850.0, 1620.0, 1380.0]})

merged = holdings.merge(prices, on="Symbol", how="left", indicator=True)
merged["Value"] = merged["Qty"] * merged["LTP"]

how="left" (Power Query's Left Outer) keeps all five holdings; WIPRO's row survives with LTP/Value as null, exactly flagging "we hold this, but have no current price for it," which is the true state of the world.

Step 2, what an inner join would have done instead. holdings.merge(prices, on="Symbol", how="inner") returns only four rows. WIPRO's entire position silently vanishes from the table, with no error, no warning, and no row left behind to even show that anything is missing. A portfolio report built on the inner-joined table would understate holdings by one full position and never say so.

Step 3, isolate the gap explicitly (the anti-join). merged[merged["_merge"] == "left_only"][["Symbol","Qty"]] returns exactly the one-row table {"Symbol": "WIPRO", "Qty": 40}, the same Left Anti diagnostic EX4.01's own mini-project used to hand a "missing master row" list to whoever owns the upstream feed, here surfaced with one filter on the indicator=True column instead of a second Power Query.

Step 4, the reverse bridge: hand the result to a non-technical stakeholder.

with pd.ExcelWriter("holdings_report.xlsx") as xw:
    merged.drop(columns="_merge").to_excel(xw, sheet_name="Holdings", index=False)

The cleaned, joined, gap-flagged table is now a plain .xlsx a colleague can open, filter, and print without ever knowing pandas was involved, the same deliverable EX7.02's Capstone 3 built natively in Excel, arrived at here by pandas for the parts of the pipeline (scale, repeatability, the anti-join diagnostic) that benefit from it.

Read it. One join kind, chosen deliberately, is the difference between a report that honestly shows a data gap and one that silently loses a position; and nothing about doing the cleaning and joining in pandas prevents the final deliverable from being the exact Excel file a non-technical reader expects, the bridge runs both directions, every time, by design.

Practice set

Work every problem before reading its solution. Every number below is fresh (none of it repeats a worked example verbatim) so recall alone cannot substitute for actually doing the pandas.

P1 (guided). Coerce a messy column by hand. Given the raw list ["₹2,500.00", " 340 ", "N/A", "", "₹18"], state what clean_numeric_column (as built in this module and graded by DA1.03-e01) returns for each entry, and why.

Solution. [2500.0, 340.0, None, None, 18.0]. "₹2,500.00" loses its ₹ and comma, then parses to 2500.0; " 340 " strips to "340" then parses to 340.0; "N/A" and "" are both explicitly mapped to missing before parsing is even attempted, so both become None; "₹18" loses its symbol and parses to 18.0. Nothing here is guessed, every entry either survives a defined cleaning step or is explicitly marked missing.

P2 (guided). Choose the right fill. For each gap, name the correct response (dropna, fillna(0), .ffill(), or .interpolate()) and say why the other three are wrong: (a) a single missing day in an otherwise smooth daily index level; (b) a sector label written once per stock and left blank on repeat rows; (c) a trade-count column where "missing" genuinely means "zero trades happened."

Solution. (a) .interpolate(), an index level plausibly moved smoothly through one missing day; .ffill() would flatten a real (if small) move, and dropna would create an artificial gap in a series you likely need continuous. (b) .ffill(), the sector is genuinely unchanged until a new label appears; .interpolate() is meaningless for text, and dropna would delete real rows just because their label was blank by export convention. (c) fillna(0), here, and only here, is "missing" actually synonymous with "zero"; using it on a price column instead (as the Common Mistakes list warns) would manufacture a fictional crash.

P3 (guided). Duplicates, with a subset. A table of (ticker, date, close, source) rows has the same (ticker, date, close) triple appearing twice, but with a different source value each time (two feeds reporting the same true price). Would plain drop_duplicates() remove the repeat? What argument fixes it if not?

Solution. No, plain drop_duplicates() compares every column, and the differing source value makes the two rows not exact duplicates by that definition, so both survive. The fix is drop_duplicates(subset=["ticker","date","close"]), which restricts the duplicate check to only the columns that actually define "the same observation," ignoring which feed happened to report it.

P4. The locale trap, applied. A raw date string "03/07/2024" arrives from an Indian source. What date does pd.to_datetime("03/07/2024") return by default, what date did the source actually mean, and what call fixes it?

Solution. By default (no dayfirst), pandas reads it as US-style mm/dd: March 7, 2024, but an Indian source almost certainly meant dd/mm: July 3, 2024. The fix is pd.to_datetime("03/07/2024", dayfirst=True), which correctly returns July 3rd. This is the identical trap EX4.01 named for Excel's Change Type ▸ Using Locale…, just with a different-looking fix on the pandas side.

P5. Case-sensitivity in a merge key. A holdings table has "NILAM" and a price table has "nilam" for the same stock. What happens if you merge on this key directly, and what is the one-line fix?

Solution. The merge silently fails to match that row, pandas' merge, exactly like Power Query's, matches keys case-sensitively, so "NILAM" and "nilam" are different keys, and a how="left" join would leave that row's price columns as NaN with no error raised. The fix is to normalize case on both sides before merging: df["Symbol"] = df["Symbol"].str.strip().str.upper() (or .lower(), as long as both tables agree), the same discipline EX4.01 taught for Power Query's own case-sensitive keys.

P6. Melt row count. A crosstab has 3 products down the side and 4 quarter columns (Q1–Q4) across the top. After pd.melt(df, id_vars=["Product"], value_vars=["Q1","Q2","Q3","Q4"]), how many tidy rows result, and what are the two new column names before you rename them?

Solution. 3 × 4 = 12 rows. The two new columns default to "variable" (holding the original column header, here the quarter name) and "value" (holding the figure) unless you pass var_name=/value_name= to rename them at melt time, exactly as this module's own melt_wide exercise does.

P7. Pivot back and check the round trip. Take the 12-row tidy table from P6 and pivot it back with .pivot_table(index="Product", columns="Quarter", values="Units", aggfunc="first") (followed by .reset_index()). What must be true of the tidy data for this to exactly recover the original wide table, and what changes if one (Product, Quarter) combination happens to be entirely missing from the tidy data?

Solution. It recovers the original exactly as long as there is precisely one row per (Product, Quarter) combination, which a clean melt always guarantees, since melt-then-pivot-back is a true round trip on data with no missing or duplicated combinations. If one combination is genuinely missing from the tidy data, the pivoted table gets a real NaN in that cell (and, because that column can no longer be a clean integer with a NaN present, its whole column is silently promoted to a floating-point dtype), a visible gap, not a silently wrong number, which is exactly the point of doing the reshape this way rather than hand-copying values into a template.

P8. Group totals, fresh numbers. Rows (region, revenue): (East, 30000), (West, 12000), (East, 18000), (South, 9000), (West, 21000). What does group_totals (as graded by DA1.03-e05) return, and in what order?

Solution. [{"East": 48000}, {"South": 9000}, {"West": 33000}] (as separate dict rows: East 30,000+18,000=48,000; South 9,000; West 12,000+21,000=33,000), sorted alphabetically ascending by the group column, groupby's own default order (East, South, West).

P9. Append or Merge? For each scenario, name the correct pandas operation and one reason: (a) three regional offices each send a CSV with identical columns, and you need one combined table; (b) you have a table of daily prices by ticker and a separate table mapping ticker to sector, and you need the sector attached to every price row.

Solution. (a) pd.concat (Append), the three files share the same shape and you are stacking more rows, not looking anything up. (b) pd.merge (Merge), you are pulling a column from a different-shaped table onto an existing one by a shared key (ticker), adding columns, not rows, the query-side lookup, exactly Power Query's Merge Queries.

P10. Join-kind row counts. holdings has 5 rows (one ticker, WIPRO, absent from prices); prices has 4 rows (all of which also appear in holdings). State the resulting row count for how="left", how="inner", and how="outer".

Solution. how="left": 5 rows (every holding kept; WIPRO's price columns are NaN). how="inner": 4 rows (WIPRO dropped, since only rows matching on both sides survive). how="outer": 5 rows here as well, because every row in prices already has a match in holdings, a Full Outer only grows beyond the larger side's row count when the smaller side also has unmatched rows of its own, which is not the case in this scenario.

P11. Weekly resample. Ten daily closes span Mon 2024-03-04 through Fri 2024-03-15 (two trading weeks, no weekend data): closes [200, 202, 199, 204, 206, 205, 208, 210, 209, 212]. Resample to weekly ("W", week ending Sunday) taking the last value in each week, and compute the one resulting week-over-week return.

Solution. Week 1 (Mon 03-04–Fri 03-08) ends 2024-03-10; its last available close is Friday's 206. Week 2 (Mon 03-11–Fri 03-15) ends 2024-03-17; its last close is Friday's 212. Week-over-week return = (212 − 206) / 206 = 2.91%. The weekend itself contributes no rows and needs none, resample groups by the calendar week regardless of how many trading days actually fall inside it.

P12. Rolling window by hand. For closes [8, 10, 9, 13], hand-compute .rolling(2).mean() for every position.

Solution. [None, 9.0, 9.5, 11.0]. Position 0 has no second observation yet (None); position 1 averages (8+10)/2 = 9.0; position 2 averages (10+9)/2 = 9.5; position 3 averages (9+13)/2 = 11.0, each window sliding forward by exactly one observation, always including the current point and the one immediately before it.

P13. shift vs. diff vs. pct_change. For the series [40, 44, 42], state .shift(1), .diff(), and .pct_change() in full.

Solution. .shift(1) = [None, 40, 44] (each value moved one position later, nothing computed). .diff() = [None, 4, -2] (each value minus its shifted self: 44−40=4, 42−44=−2). .pct_change() = [None, 0.10, -0.0455] (each value divided by its shifted self, minus one: 44/40−1=0.10, 42/44−1≈−0.0455). diff is series - series.shift(1); pct_change is series / series.shift(1) - 1, both are shift, wrapped in one further arithmetic step.

P14. A point-in-time join, by hand. Trades occur at 09:31, 09:33:30, 09:35:10; quotes are posted at 09:30 (price 50.0), 09:32 (50.2), 09:34 (50.5), 09:36 (50.8). Using pd.merge_asof(..., direction="backward"), which quote does each trade match, and why does the 09:36 quote never get used?

Solution. 09:31 matches the 09:30 quote (50.0), the most recent quote at or before it. 09:33:30 matches 09:32 (50.2). 09:35:10 matches 09:34 (50.5). The 09:36 quote is never used because every trade here occurs before it, direction="backward" only ever looks at or before the trade's own timestamp, never forward, which is precisely the point: a trade can never be matched to information that did not exist yet.

P15. Recall the bridge. Without looking it up, name the pandas equivalent of each: (a) Fill Down; (b) Unpivot Other Columns; (c) Merge Queries with Join Kind = Inner; (d) Group By.

Solution. (a) .ffill(). (b) pd.melt(df, id_vars=, value_vars=). (c) pd.merge(df1, df2, on=, how="inner"). (d) .groupby(cols).agg(...). If any of these did not come immediately, re-read the bridge table before the Mastery check, it is the single artifact this module most wants you to keep.

P16 (timed: 8 min). Choose the tool. For each scenario, say Excel/Power Query or pandas, and give the one deciding reason: (a) a solo analyst must hand a finished workbook to a client who will filter and print it themselves, with no Python installed; (b) a nightly job must ingest fifty CSVs, join them against a master reference table, and compute risk metrics, unattended, before 6 a.m.; (c) a one-off, never-repeated cleanup of a single 200-row list ahead of a meeting in ten minutes.

Solution. (a) Excel: the audience's own tooling and the need for them to interact with it directly outweigh everything else; ship the .xlsx (the reverse bridge), even if pandas did the underlying computation. (b) pandas, unattended, scheduled, at real scale, with a reproducible script that can be tested and re-run identically every night; this is precisely the automation case Power Query cannot serve without a human present or a paid service tier. (c) Either is defensible, but Excel usually wins on pure speed for a true one-off: ten minutes and 200 rows do not need a script's reproducibility, and the GUI can plausibly be faster to a finished answer, the moment "one-off" becomes "I'll probably need this again next month," that calculus flips straight back to pandas.

Applied mini-project

Build a two-market cleaning-to-time-series pipeline, and hand off both directions.

Using two small raw exports you construct yourself (mirroring this module's running examples), an India (₹) daily-close series for one ticker across roughly three weeks, deliberately including at least one exact duplicate row, one blank cell, and one "N/A" cell; and a US ($) daily-close series of similar length and similar deliberate mess, build one pipeline, in this module's own Practice/code area or any Python you have installed, that for each market:

1. Cleans the raw close column (clean_numeric_column-style: strip symbols/commas/whitespace, coerce, treat blank/N/A as missing) and deduplicates the raw rows.
2. Builds a real DatetimeIndex from the date column and confirms, explicitly, how many rows were removed by steps 1–2 (print the before/after row counts, never let a cleaning step happen silently).
3. Resamples the cleaned series to week-ending closes and computes week-over-week pct_change().
4. Computes a 3-day rolling mean and standard deviation on the cleaned daily (not resampled) series.
5. Writes the final cleaned, tidy table for both markets to a single formatted .xlsx (one sheet per market) using pd.ExcelWriter, exactly the reverse-bridge deliverable a non-technical stakeholder could open directly.

This is the integration task: every individual piece is a graded, hidden-tested technique somewhere in this module's code pack (DA1.03); the mini-project is where you chain cleaning → a real time index → resampling → rolling statistics → a stakeholder-ready handoff into one working script, for two markets, without re-deriving any single step from scratch.

Self-check rubric, score out of 10, pass at 8/10 with no zero on any item:

Keep this pipeline, cleaning, a real time index, resampling, rolling statistics, and a stakeholder handoff, chained once end to end for two markets, is close to the literal shape of a real analyst's morning task, and it is the exact discipline DA1.04 (charts drawn from what you clean here) and QD1.01 (point-in-time panels built the same way, at real stakes) will assume you already own.

Reading & resources

• pandas' own official User Guide (pandas.pydata.org), the authoritative reference for every method in this module (merge, resample, .str, groupby); the "Working with missing data" and "Time series / date functionality" pages map almost one-to-one onto this module's two halves. [Free] [Beginner–Intermediate]
• Wes McKinney, Python for Data Analysis: written by pandas' original author; its time-series and data-cleaning chapters are the deepest single treatment of exactly this module's content, from someone who built the tool. [Paid] [Intermediate]
• Real Python (realpython.com), example-driven articles on individual pandas topics (the .str accessor, merge vs. concat, time-series resampling); best used to go one level deeper on any single idea above once the fundamentals are solid. [Free tier / Paid membership for full library] [Beginner–Intermediate]
• Microsoft Learn, the official Power Query M reference and the pivot/unpivot/Group By/Join Kind documentation pages this module's bridge table maps against; read alongside the bridge table itself if any Excel-side name feels unfamiliar. [Free] [Intermediate], (the direct Excel-side counterpart to this module's own content; not new material, a cross-check.)
• Kaggle's "Pandas" micro-course: short, free, hands-on exercises that drill exactly the cleaning/reshaping mechanics this module teaches, on real-world-messy sample data. [Free] [Beginner]

Do this, not just read. Open any CSV you have lying around (a bank statement export, a downloaded report, anything with more than fifty rows) and run three things against it before you do anything else with it: df.isna().sum() (how much is actually missing, column by column), df.dtypes (is every column really the type you assumed, or is a number sitting there as text), and df.duplicated().sum() (how many exact-repeat rows exist that you did not know about). Doing this on real, uncurated data (not a tidy classroom example) is worth more than any amount of passive reading, and it is precisely the reflex this module's own hidden tests exist to make automatic.

Teach it back & journal

Feynman prompt. Explain to a colleague who only knows Excel and Power Query why holdings["Qty"] * holdings["LTP"] is doing exactly the same thing as Table.AddColumn(Invoke, "MarketValue", each [Qty]*[LTP], type number), without using the word "vectorized" until you have first explained, in plain language, what makes the pandas line not need Power Query's each. Then explain why pd.to_numeric(s, errors="coerce") and Excel's Change Type followed by try ... otherwise are the same two-step idea (parse; neutralize what fails) wearing two different syntaxes. If your colleague can, unprompted, predict that a pd.merge(..., how="inner") would silently drop a holding whose ticker is missing from today's price feed, the same failure a wrong Join Kind would cause in their own Power Query tracker, you have installed this module's actual payoff: not "pandas is Power Query's replacement," but "these were never two different techniques."

Journal prompt. Over the next few days, keep a short log every time you clean a real dataset, your own bank statement, a report you downloaded, anything with more than a handful of rows. Note: how many rows were duplicated, how many cells were missing, and whether any date or currency column needed you to make an explicit locale decision before trusting it. How many of these problems would a naive SUM/AVERAGE in Excel have silently absorbed without ever telling you? Then write a short paragraph: which single habit from this module: testing for missingness with .isna() rather than ==, cleaning a merge key before joining it, or resampling instead of skipping rows, would most change how confidently you'd hand a number computed from real data to someone else to rely on, and what will you do differently the next time you open a messy CSV because of it.