pandas I: Data Wrangling

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

A spreadsheet analyst spends most of a real workday not modeling, filtering rows, matching two tables on a key, dropping the duplicate that snuck in from a re-export, reshaping a wide report into something a pivot table can eat. That work has a name, data wrangling, and it has a proper tool, built for exactly this job at a scale and speed no spreadsheet can match: pandas, the library that gives Python two structures, the Series and the DataFrame, purpose-built to hold labeled, tabular data and to let you filter, clean, merge, group, and reshape it in a handful of expressive, vectorized lines instead of a maze of nested loops or a hundred manual clicks. This module teaches that toolbox from first principles, and only that toolbox, no statistics, no machine learning, no time-series machinery yet. Those come later, and they come faster because this module made you fluent in the plumbing first. Where a spreadsheet formula operates one cell, or one dragged range, at a time, a pandas operation operates on an entire column, or an entire grouped slice of one, in a single call; that is the real reason a well-written pandas pipeline is not just faster to run than a spreadsheet macro, but faster to verify, one line with one clearly stated intent, instead of forty rows of formulas any one of which might have a range reference that silently failed to update.

Everything below is drilled on real, shipped data from two markets, so by the end you are not manipulating toy numbers, you are cleaning, filtering, joining, and reshaping the same shape of rows a real desk would receive. The primary running data is a 500-trading-day snapshot of daily OHLCV prices for 15 fictional India-listed names (nse-eod, NSE-style, ₹) and 15 fictional US-listed names (us-eod, NYSE-style, $), plus each market's matching fundamentals file (india-fundamentals, sec-fundamentals), all seeded synthetic look-alikes, documented as such in app/public/datasets/MANIFEST.json, never claimed as real company data. Every idea taught here also becomes a graded, auto-checked coding exercise in this module's code pack (pack id DA1.02, exercises DA1.02-e01 through DA1.02-e08), real pandas, executed live in your browser against hidden tests you cannot see and cannot hardcode your way past, with at least one India-flavored and one US-flavored hidden test on every market-data exercise. If you can trace it by hand, you can code it; if you can code it, the pack will prove it.

One more thing before you start: this module runs on pandas 3.x, and where that version's behavior is specific and worth knowing precisely (not vaguely) this text says so, verified directly rather than assumed from an older tutorial. Software changes under your feet; the discipline of checking what your actual tool does, right now, rather than trusting what you remember it doing, is itself part of what this module is teaching.

Learning objectives

By the end you can:

1. Explain what a Series and a DataFrame actually are (a labeled one-dimensional array, and a dict-like collection of aligned Series sharing one index) and state why the index, not row position, is what pandas aligns on whenever two pandas objects meet.
2. Read a real CSV into a DataFrame with pd.read_csv, and treat .shape, .dtypes, .head()/.tail(), .info(), and .describe() as a mandatory ritual, never trusting a frame's contents until you have looked at all five.
3. Select data correctly and safely: build a boolean mask from one or more conditions, choose .loc (label-based) versus .iloc (position-based) deliberately, and combine a mask with a column list inside a single .loc[mask, cols] call rather than chaining two separate [] lookups.
4. Add and derive columns the vectorized way (df["x"] = ..., .assign()) as the default, and explain (with a concrete case) why row-by-row .apply(axis=1) is a last resort, not a habit.
5. Handle missing data on purpose: distinguish isna()/notna(), choose deliberately between dropna() and fillna()/ffill()/bfill(), and perform every fill or drop per group (per ticker, per account) whenever the data holds more than one entity.
6. Find and remove duplicate rows correctly with duplicated()/drop_duplicates(), using subset= and keep= precisely enough to never over-delete a genuine second trade or under-delete an accidental resubmission.
7. Perform group-wise computation with the split-apply-combine pattern: .groupby(...).agg(...) (including named aggregation) to collapse to one row per group, versus .groupby(...).transform(...) to broadcast a per-group result back onto every original row, and state which shape each produces before you run either.
8. Combine tables correctly: choose the right pd.merge how= (inner/left/right/outer) for the question being asked, explain in one sentence why validate= exists, and reach for pd.concat (not merge) when you are stacking rows, not joining on a key.
9. Reshape between wide and long form with .melt() and .pivot_table(), and say, for a given next step (a groupby, a merge, a plot), which shape it actually needs.
10. Assemble the operations above into one short, ordered pipeline (read → inspect → clean → merge/reshape → group → sort) against real India (₹, NSE-style) and US ($, NYSE-style) data, matching this module's code pack (DA1.02, exercises DA1.02-e01 through DA1.02-e08).
11. (Productivity objective: R10 duality.) Use an AI coding assistant to scaffold repetitive pandas boilerplate and to explain an unfamiliar error message, while reviewing every line it produces for the silent, non-crashing bugs pandas code is especially prone to, a wrong axis, a merge that quietly drops or multiplies rows, a fill that should have been grouped and wasn't, and never trusting an AI-authored transformation until you have checked its output against a small case you can verify by hand.

The duality, stated once (R10). Objectives 1–10 are the understanding objective: the gate (quiz ≥ 85%, code-pack ≥ 85% with hidden tests green) rewards being able to write and trace this pandas yourself, on data you have never seen before. Objective 11 is the productivity objective: knowing how a real data professional uses an AI copilot on top of that understanding, never instead of it. A copilot can type a groupby call faster than you. It cannot pass this module's hidden tests for you, and it cannot tell you (without your own verification) whether the row count it just produced is the row count you actually wanted.

Prerequisites & connections

Builds on. DA1.01 (NumPy & Vectorized Computation), this branch's first node, establishes the array-and-vectorization mindset that pandas is built directly on top of, a DataFrame's numeric columns are NumPy arrays underneath, and the habit of reaching for a whole-array operation instead of a Python-level loop carries straight over into every df["x"] = ... line in this module. If you have not taken DA1.01, this module still holds together as long as you already have ordinary Python fluency, variables, functions, for loops, list and dict literals, from any source; this program's own recommended, non-blocking route to that fluency is CS1.01/CS1.02, but per R11 no branch hard-gates on another. Within the quant-ds branch itself, DA1.01 → DA1.02 is a real, sequential, enforced chain; DA1.01 has no prerequisite of its own and opens on day one, exactly like CS1.01 and QD1.01 do for their own branches.

Feeds forward. DA1.03 (pandas II. Cleaning, Time Series & the Excel↔Python Bridge) assumes every operation in this module is already fluent, and deliberately does NOT repeat any of it, instead it takes on the two things this module leaves alone on purpose: full datetime-index/time-series machinery (.resample(), rolling windows as a first-class topic, .dt accessor depth beyond plain filtering) and the canonical Excel/Power Query↔pandas bridge (cross-referencing EX7.02). This module touches dates only as plain sortable, filterable values, it never builds a DatetimeIndex and never calls .resample(), precisely so DA1.03 has real, undiluted work to do (R1: reference, don't duplicate). DA1.04 (EDA & Visualization) takes the clean, tidy tables this module teaches you to produce and turns them into exhibits following the program's dataviz grammar. DA1.05 (Statistics in Code) runs real statistical tests over data wrangled exactly this way, and per the program's C-3 binding boundary, DA1.05 (and DA2.01/DA2.02) own translating a statistical or ML concept into working code; the theory of what a p-value, a train/test split, or an overfit model actually means belongs to G1's QM branch and is never re-derived here or there. DA2.01/DA2.02 (supervised/unsupervised ML) cannot begin without a learner who can reliably produce a clean, correctly-merged, correctly-shaped feature table from raw sources, that production line is this module's entire job. Further out, QD1.01 (Market & Fundamental Data Pipelines) leans on these exact same operations at production scale (point-in-time joins, survivorship-controlled panels) so nothing you learn here is thrown away; it is the same merge and groupby, done more carefully, on data that can quietly lie to you if you are not. One discipline habit this module installs pays off earliest there: every merge-cardinality guard (validate=) and every per-group fill you practice here is the exact same muscle a point-in-time, survivorship-controlled panel demands at full scale, the difference is only that a mistake in that later setting is quietly invisible until a backtest reports a return that could never actually have been earned.

Cross-branch companions (soft, not gates). CS1.01/CS1.02 give you the general-purpose Python this module assumes throughout (functions, loops, dict/list literals, the difference between a statement and an expression): recommended, not required, per R11. CS2.03 (Databases & SQL for Analysts) teaches the same relational ideas, filtering, joining, aggregating, one row per group, in SQL, against the identical finance-db.sqlite schema referenced across this branch. If a pd.merge ever feels unfamiliar, the equivalent SQL JOIN in CS2.03 may click faster, and vice versa; each module teaches its own syntax exactly once (R1), and neither re-derives the relational algebra the other already covers. QM1.01 (Probability, Distributions & Estimation), in G1's finance-plus galaxy, is where the statistical meaning of a mean, a standard deviation, or a distribution shape is actually derived, this module computes .describe()'s summary numbers constantly and deliberately never explains what they mean beyond the arithmetic, exactly per the C-3 boundary; QM1.01 (and, in this branch, DA1.05) is where that meaning lives. CS2.04 (Systems & Data-Intensive Thinking) covers row-versus-columnar storage and Parquet at the systems level; this module treats every file as a plain CSV precisely so that comparison has two genuinely different formats to weigh against each other later, not one format taught twice.

Core teaching content

1. Why pandas: the tool that generalizes the spreadsheet

A spreadsheet is a grid of cells you manipulate by pointing, clicking, and dragging a formula down a column, trusting that the range reference updated correctly. pandas gives you the same mental picture (rows, columns, a "sheet") but as first-class objects you manipulate with code: every filter, every formula-down-a-column, every VLOOKUP-style match becomes one written, re-runnable, testable line instead of a sequence of clicks you half-remember tomorrow. pandas has exactly two structures worth holding in your head from day one. A Series is a one-dimensional, labeled array, a single column, with values and an index of labels for those values. A DataFrame is the two-dimensional table, best understood as a dict-like collection of Series that all share the same index, one Series per column. Almost everything this module teaches is really "how do I do X to a DataFrame," and almost every DataFrame operation is secretly a coordinated operation across its column-Series, aligned by that shared index. Hold that word (alignment) because it is the single idea that most distinguishes pandas from a plain list of lists or a raw NumPy array, and it resurfaces at every join, every groupby, every arithmetic operation between two pandas objects for the rest of this module (and the rest of this branch).

2. The Series: index + values

import pandas as pd

closes = pd.Series([1129.08, 1126.63, 1128.91], index=["Mon", "Tue", "Wed"], name="close")
closes["Tue"]        # 1126.63 — label-based lookup
closes.values         # array([1129.08, 1126.63, 1128.91]) — the raw NumPy array underneath
closes.index          # Index(['Mon', 'Tue', 'Wed'], dtype='object')

A Series is values (a NumPy array) plus index (labels for those values) plus an optional name. If you do not supply an index, pandas gives you the default RangeIndex, plain integer positions 0, 1, 2, ..., which is why a freshly built Series or DataFrame looks like a plain list-with-row-numbers until you deliberately set a more meaningful index. The consequential fact is alignment: when you combine two Series in an arithmetic expression, pandas lines them up by index label, not by position. pd.Series([1, 2], index=["a", "b"]) + pd.Series([10, 20], index=["b", "a"]) gives a: 21, b: 12, matched by label, and anywhere a label exists on one side but not the other, the result is NaN. This is exactly the mechanism that makes a later pd.merge and a later groupby behave sensibly; internalize it here, on the simplest possible object, before it reappears wearing a table's clothing.

3. The DataFrame: construction, dtypes, and the index

You will meet DataFrames built three ways in this module: from a dict of equal-length lists (pd.DataFrame({"ticker": [...], "close": [...]})), from a list of row-dicts (pd.DataFrame([{"ticker": "NILAM", "close": 1129.08}, ...]), this module's most common shape for a small hand-built test fixture), and from a real file (pd.read_csv(...), Section 4). Whichever way you build one, three attributes are always worth checking immediately: .columns (the column labels, in order), .dtypes (one dtype per column, every column in a DataFrame can hold a different type, unlike a plain 2-D NumPy array), and .shape (a (rows, cols) tuple, the DataFrame's version of a spreadsheet's used range). The index defaults to a RangeIndex unless you call .set_index(...), a real column (a date, a ticker) can become the index, at which point label-based lookups (.loc["NILAM"]) start working directly against it; this module deliberately keeps the default integer index for almost everything, because a meaningful index is exactly DA1.03's territory once dates start doing real work (R1).

4. Reading real data: pd.read_csv and the mandatory inspection ritual

import pandas as pd

df = pd.read_csv("nse-eod.csv")
df.shape        # (7500, 7) — 15 tickers x 500 trading days
df.dtypes       # ticker: str, date: str, open/high/low/close: float64, volume: int64
df.head(2)      # first two rows, sanity-check the columns and values
df.info()       # column names, non-null counts, dtypes, memory — one call, five answers
df.describe()   # count/mean/std/min/25%/50%/75%/max for every numeric column

pd.read_csv is where almost every real pandas session begins, and it is also where the FIRST bug in most real pipelines is silently planted: read_csv guesses every column's dtype from the text it sees, and a date column always comes back as plain text, not a real datetime, nothing about a string that looks like "2024-07-01" tells pandas it is a date unless you say so with pd.to_datetime(df["date"]). Do this conversion immediately, before any date comparison or sort, because comparing un-parsed date strings happens to work for "YYYY-MM-DD" text (it sorts correctly as strings too) but silently breaks the moment your data ever uses a different date format, do it right from the start rather than relying on a lucky coincidence of format. Before touching anything else, run the five-call ritual above: .shape catches a file that read fewer or more rows than you expected; .dtypes catches a numeric column that accidentally became text (usually because of a stray non-numeric value somewhere in the file); .head()/.tail() catch a column that shifted (wrong delimiter, an extra header row); .info() catches missing values you did not expect (its Non-Null Count column should equal .shape[0] unless you know a column is meant to have gaps); .describe() catches an impossible number (a negative volume, a price of zero) before it propagates into every downstream computation. One version-specific fact worth stating precisely rather than assuming from an older tutorial: in this app's pandas (3.x), a text column comes back as a dedicated string/str dtype, not the classic catch-all object, verified directly against this exact shipped version. The old idiom df.dtypes == object to "find my text columns" therefore silently finds nothing here; the version-stable way to ask the same question is pd.api.types.is_string_dtype(...) (and its siblings is_numeric_dtype/is_datetime64_any_dtype), which answer correctly regardless of which pandas version is actually running. This is exactly the kind of fact software changes out from under you, check what your tool does today, not what a five-year-old answer online says it does. DA1.02-e01 grades exactly this read-and-fix-dtype step against the real, shipped nse-eod/us-eod files.

5. Quick exploration: .unique(), .value_counts(), and renaming columns

Before any cleaning or reshaping, an analyst usually asks a handful of small, specific questions that a spreadsheet answers with a filter and an eyeball: which distinct values does this column actually hold, and how often does each one occur?

df["ticker"].unique()          # array(['NILAM', 'TARANG', ...]) — distinct values, first-seen order
df["ticker"].nunique()         # 15 — just the count of distinct values
df["ticker"].value_counts()    # one row per distinct value, sorted by count descending

value_counts() is the fastest reflex check for a file you suspect might be incomplete: on a clean EOD file, every one of the 15 tickers should show exactly 500 rows, and any ticker showing fewer has missing days somewhere you have not yet accounted for: far quicker than eyeballing .head() and hoping nothing is missing further down the file. The same method works on any categorical column, not only an identifier, a sector or a side (BUY/SELL) column's value_counts() is a one-line sanity check on class balance before you ever reach for a groupby.

Two more small, constant habits belong here. Renaming: a file rarely arrives with exactly the column names you want to write code against: df.rename(columns={"px_close": "close", "sym": "ticker"}) returns a new frame with just those columns relabeled, everything else untouched; prefer it over rebuilding the whole columns list by hand, which silently breaks the moment the file's column ORDER changes even if the names you care about did not. Explicit casting: df["volume"].astype("int64") or df["close"].astype("float64") forces a dtype directly, useful the moment read_csv's automatic inference guesses wrong, a numeric column that arrived as text because one stray row held a comma or a currency symbol, and it is the same general tool behind the specific pd.to_datetime fix Section 4 already made you reach for.

6. Selection: boolean masks, .loc vs. .iloc, and the chained-indexing trap

A boolean mask is a Series of True/False, one per row, built from a condition: df["ticker"] == "NILAM". Combine conditions with & (and), | (or), ~ (not) (never Python's own and/or, which do not work element-wise on a Series) and always wrap each condition in parentheses, because &/| bind tighter than comparison operators in Python, so df.ticker == "NILAM" & df.close > 1000 silently parses wrong; write (df.ticker == "NILAM") & (df.close > 1000) every time. Once you have a mask, .loc[mask, columns] is how you select rows AND columns in one call: .loc is label-based: df.loc[3, "close"] means "the row labeled 3 and the column labeled close," which happens to equal position 3 only because the default index is a RangeIndex. .iloc is position-based: df.iloc[0, -1] always means "the first row, the last column," regardless of what the index labels actually are. Use .loc when you are selecting by a meaningful condition or a real label; use .iloc when you genuinely mean "the Nth row" (the first, the last, a fixed offset).

Here is the trap Common Mistakes returns to, made concrete: df[df["ticker"] == "NILAM"]["close"] = 0, two chained square-bracket lookups where you meant one assignment. In this app's pandas (3.x, where Copy-on-Write is the mandatory default, not an opt-in setting), running exactly that line raises a hard, precisely-worded error the moment you try it:

ChainedAssignmentError: A value is being set on a copy of a DataFrame or Series
through chained assignment. ... Try using '.loc[row_indexer, col_indexer] = value'
instead, to perform the assignment in a single step.

That is a genuine improvement over pandas's older behavior, where the identical line could silently do nothing to your real DataFrame, or fire a warning you might have learned to ignore, depending on the exact circumstances, this version tells you clearly, at the moment of the mistake, exactly what to write instead. But the underlying discipline is unchanged across every pandas version you will ever touch: build the mask, then do the whole selection and the whole assignment in one .loc[mask, "close"] = 0 call, never two bracket lookups chained together when you intend to mutate. DA1.02-e02 grades exactly this pattern: one .loc[mask, cols] call, boolean-and-range selection combined, never chained.

7. Deriving columns: vectorized operations versus .apply()

Adding a new column the pandas way is almost always a single vectorized expression: df["daily_return"] = df["close"] / df["close"].shift(1) - 1 operates on the ENTIRE column at once, in compiled code, with no explicit Python-level loop, this is the same array-at-a-time thinking DA1.01 built with NumPy, now wearing a DataFrame's clothes. .assign() does the same thing in a chainable, non-mutating style: df.assign(daily_return=lambda d: d["close"].pct_change()) returns a new frame with the column added, useful mid-chain. Contrast this with df.apply(lambda row: row["close"] / row["open"] - 1, axis=1), which calls your lambda once per row, in a genuine Python-level loop under the hood, measurably slower on any real-sized frame, and a common source of subtle correctness bugs too, because a row passed to axis=1 apply is itself a Series with mixed types silently coerced to a common dtype (a row containing a string ticker and a float close might both arrive as object), which can quietly break arithmetic that worked fine on the original typed column. Reach for .apply(axis=1) only when a computation genuinely cannot be expressed as a column-at-a-time operation, it is a last resort, never a default habit.

The vectorized instinct gets sharper, not simpler, the moment your DataFrame holds more than one entity. df["close"].pct_change() computes a plain, ungrouped day-over-day change straight down the column, which is exactly wrong the instant df stacks multiple tickers, because the LAST day of ticker A and the FIRST day of ticker B are adjacent ROWS but have nothing to do with each other, and a bare .pct_change() will confidently compute a "return" across that seam. The fix is df.groupby("ticker")["close"].pct_change(), same vectorized method, computed within each group, correctly NaN at every group's first row and never bleeding across a ticker boundary. DA1.02-e03 grades exactly this: a daily_return column that is right at a ticker boundary, not just right in the middle of one ticker's own series.

{
  "skill": "Turning a slow, row-by-row .apply(axis=1) transformation into a correct vectorized one",
  "use": "Paste a working-but-slow `.apply(axis=1)` pandas function and ask an AI coding assistant to draft a vectorized (or `.groupby(...).transform(...)`) rewrite — using it to accelerate a mechanical translation you already understand, not to invent logic you don't.",
  "tools": ["Chat assistants — Claude, ChatGPT (paste code, no execution)", "IDE-integrated copilots — GitHub Copilot, Cursor, or a Claude Code / editor extension (inline suggestions inside your actual file)"],
  "prompt": "Here is a pandas function using `.apply(axis=1)`: <PASTE>. Rewrite it using vectorized column operations (or `.groupby(...).transform(...)` if it is a per-group computation) with identical behavior, including on edge cases like an empty DataFrame, a single-row group, or a NaN. Explain exactly what changed and why the rewrite is faster.",
  "verify": "Run BOTH versions on the same real slice of data you can check by hand (a 3-row, 2-ticker frame is enough) and assert the outputs are identical before deleting the slow version. A rewrite that looks more 'pandas-idiomatic' can silently change behavior at a group boundary or on a NaN — exactly the class of bug this module's Common Mistakes section names — and an AI cannot certify against your specific data; only you, checking, can.",
  "diy": "You must already be able to say, by hand, what the slow `.apply` version computes for a small example — that is exactly what this section and code-pack exercise DA1.02-e03 build. AI accelerates the rewrite; it cannot verify correctness for you.",
  "market": "US"
}

8. Missing data: NaN, isna()/notna(), dropna(), and fillna()/ffill()/bfill()

A missing value in pandas is (almost always) the float NaN ("Not a Number") a special value with one famously counter-intuitive property borrowed from IEEE floating-point arithmetic: float("nan") == float("nan") is False, so you never test for missingness with == NaN. Use df["close"].isna() (or its opposite, .notna()) instead, both return a clean boolean mask you can sum (.isna().sum() counts missing values per column) or filter on. df.dropna() removes any row containing at least one NaN, across every column, by default (how="any"), which quietly drops a row because of a single irrelevant NaN in a column you never cared about. Two disciplined refinements: subset=[...] restricts the check to exactly the columns that matter (df.dropna(subset=["close"]) drops a row only if close itself is missing, ignoring a NaN elsewhere), and how="all" only drops a row if every named column is missing. df.fillna(0) replaces every NaN with a fixed value, appropriate for a count or a flag column where "missing" genuinely means zero, and financially dangerous for a price, where a missing close is not the same fact as a zero close.

For a time-ordered price or fundamentals series specifically, the honest fill is almost always forward-fill, df["close"].ffill() ("carry the last known value forward") never backward-fill, .bfill(), which would let a value from a LATER date appear at an earlier one. That is look-ahead bias in its smallest, most innocent-looking form: a single .bfill() call quietly handing your earlier self information it could not possibly have had yet, and if that filled series is ever fed into a strategy backtest later in this branch (QD1.03 builds the no_lookahead harness that catches exactly this class of bug at scale), a .bfill()-filled input will make the backtest lie about how well the strategy would actually have performed, live, in the past. Every fill decision here is per-column and deliberate, never a blanket "fill everything the same way", a genuinely missing price and a genuinely missing sector label do not carry the same meaning, and treating them identically is a modeling choice, not a technicality. And exactly as with the day-over-day return in Section 7, any fill on data holding more than one entity must run per group: df.groupby("ticker")["close"].transform(lambda s: s.ffill()) fills each ticker only from its own history, never leaking a neighboring ticker's price across the boundary. DA1.02-e04 grades exactly this, sort, then group-wise forward-fill, then drop what still cannot be repaired, in that order, because doing these three steps out of order produces a plausible-looking but wrong result.

9. Duplicates: duplicated() and drop_duplicates()

A real trade blotter, order log, or re-exported report occasionally contains an exact duplicate row, the same trade submitted twice by a flaky gateway, the same filing appended twice by an export job. df.duplicated() returns a boolean mask, True on every row that is a byte-for-byte repeat of an earlier one (by default, across every column); df.drop_duplicates() removes them, keeping the first occurrence by default (keep="first"; keep="last" or keep=False, which drops every copy including the first, are also available for when the shape of the problem calls for them). The precise, easy-to-get-wrong parameter is subset=, restricting duplicate detection to only the columns that define "the same record" (df.drop_duplicates(subset=["ticker", "date"]) treats two rows as duplicates if they share a ticker and date, even if some other column differs), get subset= wrong in either direction and you either fail to catch a real duplicate or wrongly collapse two genuinely different trades that happen to share a subset of fields. DA1.02-e05 grades exactly this distinction: an exact duplicate must collapse, and a row differing in even one field (a different quantity, a different price) must survive untouched.

10. GroupBy: split-apply-combine

Nearly every "what does this look like BY ticker / BY sector / BY account" question is one call: df.groupby("ticker"). Conceptually, pandas splits the frame into one sub-frame per distinct group value, lets you apply a computation to each piece independently, and combines the pieces back into one result, the same pattern as an Excel PivotTable's row grouping, expressed as code instead of a dialog box. Two combine shapes matter, and confusing them is one of Common Mistakes' entries below: .agg(...) collapses each group to a single summary row: df.groupby("ticker")["close"].agg(start="first", end="last") returns one row per ticker, with named output columns start/end (this "named aggregation" syntax, keyword equals aggregation function, is the readable, self-documenting way to name your outputs instead of relying on pandas' default column-naming). .transform(...) instead broadcasts a per-group result back onto every original row, so the output has the same number of rows as the input: df.groupby("ticker")["close"].transform("mean") gives every row of a given ticker that ticker's own mean close, ready to sit alongside the original data (useful for, say, "how far is today's close from this ticker's own average"). Ask, before you write either call, "do I want one row per group, or the original row count with a group-level answer attached to each row", the answer tells you which of the two to reach for.

One more precise distinction worth keeping ready, because the two look interchangeable and are not: df.groupby("ticker").size() counts every row in each group, full stop, while df.groupby("ticker")["close"].count() counts only the non-null values of close within each group. The two agree exactly when a group has no missing data, and quietly diverge (size() staying put, count() dropping) the moment it does. Asking for the wrong one is a small, silent way to over- or under-report how much real data a group actually has.

11. Combining tables: pd.merge and pd.concat

pd.merge joins two DataFrames on a shared key, exactly like a SQL JOIN (CS2.03 teaches the SQL side of this same idea against the identical finance-db.sqlite schema; R1, the underlying relational logic is taught once, not twice). The how= parameter decides what happens to a key present on one side but not the other: how="inner" keeps only tickers present on both sides (any unmatched row on either side vanishes silently, the single most common source of an unexpectedly small result); how="left" keeps every row of the left frame, filling NaN for any right-side columns with no match; how="right" is the mirror image; how="outer" keeps every row from both sides, NaN wherever a match is missing. Reach for how="left" whenever the question is "enrich table A with table B's columns, but never lose a row of A", which is most real analyst joins. The parameter worth adding as a habit, not an afterthought, is validate=: prices.merge(fundamentals, on="ticker", how="left", validate="one_to_one") makes pandas itself raise a MergeError if either side ever turns out to have more than one row per ticker, catching a silent one-to-many row-multiplication at the exact moment it would first occur, rather than three steps later when a downstream sum is mysteriously double what it should be. indicator=True is the companion habit for auditing a join's coverage: it adds a _merge column reading both/left_only/right_only for every row, letting you literally count how many keys failed to match before you decide whether that is expected or a bug.

pd.concat is a different operation entirely, and reaching for merge when you meant concat (or vice versa) is a real, common mistake: concat stacks DataFrames that already share the same columns: pd.concat([us_df, in_df], ignore_index=True) glues two markets' price tables into one long table, row-wise, with no key-matching involved at all (ignore_index=True gives the result a fresh 0..n-1 index instead of keeping each piece's original, now-duplicated, index labels). Use concat when you are combining rows that mean the same thing across sources (this month's US prices and this month's India prices); use merge when you are combining columns that describe the same entity from two different sources, matched by a key. DA1.02-e07 grades the merge-with-cardinality-guard pattern directly; DA1.02-e08 uses concat to stack two markets before grouping them.

{
  "skill": "Diagnosing a pandas merge that silently produced the wrong number of rows",
  "use": "When a `pd.merge` result has an unexpected row count, describe the two input shapes and the merge call to an AI assistant to get a ranked list of likely causes — then check each candidate yourself against the real frames, rather than trusting the AI's diagnosis as fact.",
  "tools": ["Chat assistants — Claude, ChatGPT (paste the merge call plus `df.shape`/`df.head()` of both sides)", "IDE-integrated copilots for drafting a `validate=`/`indicator=True` audit snippet"],
  "prompt": "I merged a DataFrame of shape <A> with one of shape <B> on column '<KEY>' using how='<HOW>' and got a result of shape <RESULT> — I expected <EXPECTED>. List the most likely causes in order, and write one short pandas snippet I can run to confirm or rule out each one (e.g. checking for duplicate keys with `.duplicated()`, or comparing dtypes on the join column).",
  "verify": "Every candidate cause must be confirmed against your ACTUAL two DataFrames, never accepted because it sounds plausible — an AI can name a real pandas failure mode that simply isn't the one present in your specific data. Re-run the merge with `validate=` set to what you actually intend and let pandas itself raise if your cardinality assumption was wrong; that check outranks any AI explanation.",
  "diy": "You must already know what `validate=` and `indicator=True` do and be able to read a `MergeError` yourself — this section and code-pack exercise DA1.02-e07 are where that understanding is built, not optional background.",
  "market": "IN"
}

12. Reshaping: .melt() and .pivot_table()

A fundamentals table naturally arrives wide: one row per (ticker, fiscal_year), with revenue, net_income, total_assets, total_liabilities sitting side by side as separate columns. That shape is fine to read but awkward to group, plot, or filter by metric, you cannot easily ask "give me every net_income value across companies" without first reshaping. .melt() turns wide into long: df.melt(id_vars=["ticker", "fiscal_year"], value_vars=["revenue", "net_income", "total_assets", "total_liabilities"], var_name="metric", value_name="value") produces one row per (ticker, fiscal_year, metric), with the metric's name in one column and its value in another, id_vars names the columns that identify a record, value_vars names the columns being unpacked into rows. .pivot_table(index=[...], columns=..., values=...) is the inverse move, long back to wide, and it is worth knowing precisely because it is not a pure mechanical un-melt: pivot_table aggregates (mean, by default) whenever more than one row shares the same (index, columns) combination, which is exactly what makes it different from the stricter .pivot() (no aggregation, raises on a genuine duplicate combination). Ask, before reaching for either: does the NEXT step in your pipeline want "one row per metric" (long, feeds a groupby or a plot beautifully) or "one row per entity with metrics as columns" (wide, feeds a merge or a report table)? DA1.02-e06 grades the wide-to-long melt directly.

13. Putting it together: the pipeline, and where this leaves you

Every real wrangling task is some ordering of the pieces above: read (Section 4) → inspect (the five-call ritual, plus Section 5's quick exploration) → clean (Sections 6–9: select what you need, derive what's missing, fill or drop deliberately, collapse duplicates) → combine or reshape (Sections 10–12: group, merge, melt/pivot, in whichever order the question demands) → sort and present. This module's synthesis exercise, DA1.02-e08, runs that exact chain end to end: tag two markets' price tables, concat them into one frame, groupby by (market, ticker) to collapse each to a start/end close with named aggregation, derive a total_return column, and sort by it, the same end/start - 1 total-return formula CS1.01 taught you to compute for one series by hand, now applied to every ticker in both markets in a single vectorized pass, not re-derived (R1). Nothing from here forward in the quant-ds branch asks you to write a raw Python loop over rows to answer a tabular question; it asks you to recognize which of these dozen operations the question actually is, and to reach for it directly. That recognition, "this is a groupby-agg question," "this needs a left join with a cardinality guard," "this wants long form before it wants a plot", is the actual, transferable skill this module was built to install; the specific method names are simply how you spell it out once you already know which shape the answer needs.

Common mistakes & how experts think differently

1. Chained indexing to assign a value: df[mask]["col"] = value. In this app's pandas (3.x, mandatory Copy-on-Write), this now raises a precise ChainedAssignmentError naming the fix; in an older pandas install the identical line could silently do nothing to your real DataFrame, or only warn. Either way, the fix has never changed: do the whole selection and the whole assignment in one .loc[mask, "col"] = value call. Experts write the single-call form as a reflex, not because they memorized a warning, but because they think in terms of "one clear operation," not "two lookups that happen to look chainable."
2. Forgetting groupby before a per-entity operation. df["close"].pct_change(), .ffill(), .cumsum(), any of these run bare across a multi-entity frame silently compute a value that BLEEDS across the boundary between one ticker's last row and the next ticker's first row. The number that comes out is a real, computable float, it is simply meaningless. Experts ask "does this frame hold more than one entity?" before every such operation, as automatically as checking a light switch, and reach for .groupby("ticker")[...] the instant the answer is yes.
3. Treating df.dtypes == object as "how I find my text columns." This idiom is genuinely outdated in this app's pandas: text columns default to a dedicated string dtype here, which does not equal object. Experts test dtype categories, not dtype identity: pd.api.types.is_string_dtype(...), is_numeric_dtype(...), is_datetime64_any_dtype(...), precisely because these stay correct as the library's internal defaults evolve underneath them.
4. Reaching for .apply(axis=1) as a default habit. It is measurably slower than a vectorized column operation on any real-sized frame, and it silently coerces a row's mixed-dtype values to a common type, which can hide a correctness bug a typed column would never allow. Experts reserve axis=1 apply for the rare case that genuinely cannot be expressed column-at-a-time, and they say so out loud in a code comment when they do.
5. Merging without validate=. A silent one-to-many join multiplies rows with zero error message, the first symptom is usually a downstream sum or count that is mysteriously too large, discovered far from the actual merge line. Experts add the cardinality guard on the FIRST write of the merge, not after an hour of debugging a suspiciously large result.
6. Trusting dropna()'s default. how="any" drops a row because of a single NaN in a column that was never relevant to the question at hand. Experts always pass subset=[...], naming exactly the columns whose missingness actually matters, so a row is never lost for a reason nobody intended.
7. Backward-filling a price or fundamentals series. .bfill() lets a later value appear at an earlier date, look-ahead bias in miniature, and exactly the class of error QD1.03's no_lookahead harness exists to catch at full backtest scale. Experts default to .ffill() for time-ordered financial data and treat any .bfill() on such a series as a decision that needs its own justification in writing, not a reflex.
8. Confusing .agg()'s row-collapse with .transform()'s row-preserving broadcast. Using the wrong one produces an output shape that looks plausible (it runs without error) while silently losing (or duplicating) exactly the row-level detail the next pipeline step needed. Experts state out loud, before writing either call, "do I want one row per group or the original row count with a group answer attached," and let that answer choose the method.
9. Forgetting ignore_index=True when concatenating. pd.concat([us_df, in_df]) without it keeps each piece's ORIGINAL row labels, so the combined frame can carry duplicate index values, a 0 from us_df and an unrelated row's 0 from in_df. df.loc[0] on the result then silently returns two rows, not one, and any later step that assumed a unique index inherits the same ambiguity. Experts pass ignore_index=True (or call .reset_index(drop=True) immediately after) as a reflex on every concat, not only once something has already gone wrong downstream.

Worked examples

Worked example 1: Reading and inspecting a real EOD file (India, ₹)

Before writing a single transformation, an analyst must know exactly what nse-eod.csv actually contains. Load it and run the inspection ritual.

Solution. Step 1: load and fix dtypes. df = pd.read_csv("nse-eod.csv"); df["date"] = pd.to_datetime(df["date"]). Step 2: shape and columns. df.shape is (7500, 7): 15 tickers × 500 trading days, columns ticker, date, open, high, low, close, volume. Step 3: dtypes. ticker/date are a text (string) dtype before/after the fix respectively (date becomes datetime64 after pd.to_datetime); open/high/low/close are float64; volume is int64. Step 4, isolate one name and describe it. df[df.ticker == "NILAM"]["close"].describe() gives count 500, mean 946.76, std 106.81, min 742.47, 25% 876.92, 50% 921.44, 75% 1020.96, max 1202.39, a plausible, gap-free 500-row series with no missing values (.isna().any() is False). Step 5, read it. Nothing here is a transformation yet, it is a verification that the raw material is what you assumed, before a single line of cleaning logic is trusted to run on top of it. DA1.02-e01 grades exactly this read-plus-dtype-fix step against the real file.

Worked example 2: Boolean selection and the chained-indexing trap (US, $)

An analyst needs BRPEAK's closes for July 1–5, 2024 only, and then (incorrectly, at first) tries to flag one day as an outlier.

Solution. Step 1, build the mask and select in one .loc call. window = df.loc[(df.ticker == "BRPEAK") & (df.date >= "2024-07-01") & (df.date <= "2024-07-05"), ["date", "close"]] returns exactly five rows: closes 88.19, 87.69, 87.91, 84.85, 87.69 for July 1 through 5. Step 2, the wrong way to flag July 4. window[window.date == "2024-07-04"]["close"] = None: two chained bracket lookups. In this app's pandas, this line raises ChainedAssignmentError immediately, with a message naming the fix. Step 3, the right way. window.loc[window.date == "2024-07-04", "close"] = None, one .loc call, selection and assignment together, succeeds cleanly and actually mutates window. Read it. The bug in Step 2 was never really about pandas version trivia; it was about writing two operations where the tool needed one. Whether your pandas raises a clear error (as here), a silent no-op, or a warning, the fix is identical, and experts write it that way from the first draft, not after the error teaches them.

Worked example 3: GroupBy split-apply-combine across 15 holdings (both markets)

A 15-holding illustrative portfolio (synthetic-portfolio, 500 trading days) carries a weight and a daily_return per holding per day. An analyst wants each holding's mean daily return, ranked, and a sanity check that the weights actually form a real portfolio.

Solution. Step 1, the sanity check first. port.groupby("date")["weight"].sum() should be (and is) ~1.0 for every one of the 500 dates, real portfolio weights sum to 1, and any date where this check failed would mean the weight data itself was broken before a single return was ever computed. Step 2, the actual groupby. port.groupby("ticker")["daily_return"].mean().sort_values(ascending=False) collapses 7,500 rows to 15, one mean return per holding. The top of the ranking is MYNAH at a mean daily return of roughly 0.001248; the bottom includes IRNVLE at roughly -0.000836. Step 3, read the shape. This is .agg()'s signature move: one row per group. Had the question instead been "attach each holding's own mean return as a new column, without losing any of the 7,500 daily rows," the tool would have been .transform("mean"), not .agg(...), same underlying computation, opposite output shape. Read it. The weight-sum check in Step 1 is not decoration; verifying an input's basic invariants (weights sum to 1, a rate is between 0 and 1, a count is non-negative) before trusting any aggregation built on top of it is exactly the habit that catches a broken upstream feed before it quietly corrupts every number downstream.

Worked example 4: Merging price and fundamentals, with a cardinality guard (India, ₹)

An analyst has NILAM and SARASWATI's most recent close prices and most recent filed fundamentals in two separate small tables, and wants them joined into one row per company: safely.

Solution. Step 1, the two input tables. prices_latest = pd.DataFrame({"ticker": ["NILAM", "SARASWATI"], "close": [802.05, 1287.33]}); fundamentals_latest = pd.DataFrame({"ticker": ["NILAM", "SARASWATI"], "net_income": [391.20, 116.45], "revenue": [3478.94, 1473.48]}), both real, filed values from the shipped nse-eod/india-fundamentals files (NILAM's latest close on 2026-05-29; each ticker's FY2025 filing). Step 2, the guarded merge. prices_latest.merge(fundamentals_latest, on="ticker", how="left", validate="one_to_one") returns two rows, each carrying ticker, close, net_income, revenue, and raises nothing because both sides genuinely have exactly one row per ticker. Step 3, prove the guard actually guards something. Duplicate one row in fundamentals_latest (append a second NILAM row) and re-run the identical merge call, it now raises MergeError: Merge keys are not unique in right dataset; not a one-to-one merge, caught at the merge line itself rather than surfacing three steps later as a mysteriously doubled net_income sum. Read it. validate= cost one keyword argument and caught, immediately and by name, a class of bug that a real production pipeline could otherwise ship silently for weeks. DA1.02-e07 grades this exact merge, including the left-join guarantee that a ticker with no fundamentals match survives with NaN rather than vanishing.

Worked example 5: Melting wide fundamentals into tidy long form (US, $)

BRPEAK and CLRRDG's FY2024 fundamentals arrive wide (one row each, four metric columns) and an analyst wants them long, ready for a per-metric groupby.

Solution. Step 1, the wide input, real filed values. wide = pd.DataFrame([{"ticker": "BRPEAK", "fiscal_year": 2024, "revenue": 326.86, "net_income": 42.96, "total_assets": 568.75, "total_liabilities": 352.96}, {"ticker": "CLRRDG", "fiscal_year": 2024, "revenue": 409.08, "net_income": 49.01, "total_assets": 836.34, "total_liabilities": 384.44}]). Step 2: melt. long = wide.melt(id_vars=["ticker", "fiscal_year"], value_vars=["revenue", "net_income", "total_assets", "total_liabilities"], var_name="metric", value_name="value") produces eight rows (two tickers × four metrics) each with metric naming which figure it is and value holding the number; sorted by ticker, fiscal_year, metric, BRPEAK's net_income row reads 42.96 and CLRRDG's total_assets row reads 836.34. Step 3, the inverse, briefly. long.pivot_table(index=["ticker", "fiscal_year"], columns="metric", values="value") reconstructs the original wide shape (mean-aggregated, though here every group has exactly one row, so the aggregation is a no-op), proving melt and pivot are genuine inverses of each other, not two unrelated tricks. Read it. The long shape is not "more correct" than the wide one, it is simply the shape a groupby("metric") or a faceted plot (DA1.04) can actually consume; the wide shape is what a merge or a printed report wants. Reshaping is choosing the right shape for the NEXT step, not a virtue in itself. DA1.02-e06 grades exactly this melt.

Worked example 6: Synthesis: concat two markets, groupby-agg, derive, sort (both markets)

An analyst has three days of real closes for one India ticker and one US ticker, in two separate per-market frames, and wants a single ranked snapshot of both.

Solution. Step 1, the two inputs. in_df holds NILAM's real closes for 2024-07-01 through 07-03: 1129.08, 1126.63, 1128.91. us_df holds BRPEAK's: 88.19, 87.69, 87.91. Step 2: tag and stack. Add market="IN" to in_df and market="US" to us_df, then pd.concat([us_df, in_df], ignore_index=True), six rows, one frame, no key-matching, just a row-wise stack. Step 3: group, aggregate, derive. both.groupby(["market", "ticker"])["close"].agg(start="first", end="last").reset_index() collapses to two rows (one per market-ticker pair) with named start/end columns; g["total_return"] = g["end"] / g["start"] - 1, the same formula from CS1.01, computed for both tickers in one vectorized line. NILAM's three-day return is 1128.91 / 1129.08 - 1 ≈ -0.000151; BRPEAK's is 87.91 / 88.19 - 1 ≈ -0.003175. Step 4: sort and read. Sorted by total_return descending, NILAM ranks first, its small loss was smaller than BRPEAK's. Read it. Every piece used here: concat, groupby(...).agg(...) with named outputs, a derived column, sort_values, was taught separately in Sections 6, 7, 10, and 11; this example (and DA1.02-e08, which grades it directly) is proof that the pieces compose into one short, readable pipeline, not proof of a new idea.

Worked example 7: Duplicate index labels after concat, and why ignore_index=True matters (both markets)

An analyst stacks three days of NILAM's real closes with three days of BRPEAK's, forgetting the one keyword argument that keeps the result unambiguous.

Solution. Step 1, the mistake. in_df and us_df (built as in Worked Example 6) each carry their own default RangeIndex, 0, 1, 2. pd.concat([us_df, in_df]), no ignore_index, glues them row-wise WITHOUT renumbering: the combined index reads 0, 1, 2, 0, 1, 2, six rows, two of them labeled 0. Step 2, the symptom. combined.loc[0] no longer returns a single row, it returns a two-row DataFrame (BRPEAK's first day AND NILAM's first day), because .loc matches every row carrying that label. Any later line that assumed .loc[0] meant "the first row" now silently receives two rows instead of one, with no exception raised anywhere along the way. Step 3, the fix, and why it is a reflex, not an afterthought. pd.concat([us_df, in_df], ignore_index=True) (equivalently, a bare .reset_index(drop=True) immediately after) assigns a fresh 0..5 index: combined.loc[0] now unambiguously means BRPEAK's first row, and only that. Read it. Nothing here raised an exception at the point of the mistake: pd.concat without ignore_index is completely legal Python, which is exactly what makes it dangerous: the ambiguity sits latent in the combined frame until some LATER line trips over it, often far from the actual concat call. Experts add ignore_index=True at the moment they write the concat, the same way they add validate= at the moment they write a merge, before a bug has the chance to exist, not after one is found and traced backward.

Practice set

Work each problem fully before reading its solution. Unless stated otherwise, df refers to a DataFrame with columns ticker, date, close holding more than one ticker.

P1 (guided). Series vs. DataFrame, in one sentence each. What is a Series, and what is a DataFrame, in terms of each other?

Solution. A Series is a one-dimensional labeled array (values plus an index). A DataFrame is a dict-like collection of Series that all share the same index, one Series per column.

P2 (guided). Trace a .loc selection. Given df with rows for tickers A (closes 10, 12) and B (closes 50, 48), what does df.loc[df.ticker == "B", ["close"]] return?

Solution. A one-column DataFrame (not a Series, the column list ["close"] keeps it 2-D) with the two B rows: closes 50 and 48, original row labels preserved (no reset_index was called).

P3. .loc vs. .iloc failure modes. df has a default RangeIndex of 0..99. After df = df[df.ticker == "A"], does df.loc[0] still work? Does df.iloc[0] still work? Why might they now disagree?

Solution. .loc[0] works only if row-label 0 survived the filter (it selects by LABEL, and filtering does not renumber labels). .iloc[0] always works if the filtered frame is non-empty, it always means "the first row that's actually here," regardless of what its label is. They can disagree the moment the filter drops row 0 but keeps some later-labeled row as the new first row, exactly why .reset_index(drop=True) is often used right after a filter, to make the two agree again.

P4. Predict the bleed bug. df stacks ticker A (closes 100, 110) then ticker B (closes 50, 45), in that row order. What does bare df["close"].pct_change() give for B's first row, and why is it wrong?

Solution. It computes (50 - 110) / 110 ≈ -0.545, treating B's first close as if it followed A's last close, a meaningless number computed across two unrelated tickers. The fix is df.groupby("ticker")["close"].pct_change(), which correctly gives NaN for B's first row.

P5. dropna() defaults. df has columns close (no missing values) and note (a free-text column, often missing). What does bare df.dropna() do to a row where only note is NaN? What is the fix if note should not affect anything?

Solution. Bare dropna() uses how="any" across ALL columns, so a row with only note missing is dropped anyway, losing a perfectly good close value for an irrelevant reason. Fix: df.dropna(subset=["close"]), naming only the column whose missingness actually matters.

P6. Fill direction and look-ahead bias. A daily close series is missing 2024-07-04. Which fill is financially honest (forward-fill or backward-fill) and why would the other one matter later in this program?

Solution. Forward-fill (ffill), carry 07-03's close forward, since that is genuinely the last price known at that time. Backward-fill would let 07-05's close appear on 07-04, handing an earlier date information from the future, look-ahead bias, exactly the defect QD1.03's no_lookahead harness is built to catch in a full backtest.

P7. drop_duplicates with subset=. Two rows share the same ticker and date but have different close values (a genuine same-day data correction, not a duplicate submission). What does df.drop_duplicates(subset=["ticker", "date"]) do to them, and is that correct?

Solution. It keeps only the FIRST of the two (default keep="first") and silently discards the corrected value, likely wrong here, since the rows are not truly duplicates, just a shared key. The fix is either not using subset= that narrowly (letting the differing close keep both rows distinct) or explicitly deciding, and documenting, which of the two values is authoritative before dropping the other.

P8. .agg() vs. .transform() shape. df has 500 rows across 15 tickers. What is the row count of df.groupby("ticker")["close"].agg("mean")? What is the row count of df.groupby("ticker")["close"].transform("mean")?

Solution. .agg("mean"): 15 rows, one per group. .transform("mean"): 500 rows, the original row count, each row carrying its own ticker's mean broadcast back onto it.

P9. Merge how= and row counts. prices has 15 tickers; fundamentals has fundamentals for only 12 of them. State the resulting row count for how="inner", how="left", and how="outer" (assume each side has at most one row per ticker, and no ticker exists in fundamentals that is missing from prices).

Solution. how="inner": 12 (only tickers present on both sides). how="left": 15 (every prices row kept; 3 tickers get NaN fundamentals). how="outer": 15 (same as left here, since no fundamentals-only ticker exists in this scenario).

P10. What validate= actually catches. prices.merge(fundamentals, on="ticker", how="left", validate="one_to_one") raises a MergeError. What does that tell you, precisely, and what does it NOT tell you?

Solution. It tells you that at least one ticker value appears more than once on the left, the right, or both, a cardinality violation of the "one_to_one" assumption. It does NOT tell you which specific ticker is duplicated, or whether the duplicate is a genuine data error versus an intentional many-row case that calls for a different validate= value ("one_to_many", etc.), that diagnosis is still yours to do, typically with .duplicated() on the join key.

P11. Melt/pivot as inverses. A wide fundamentals frame has one row per ticker and four metric columns. After .melt(id_vars=["ticker"], value_vars=[the four metrics]), how many rows result for 10 tickers? What single call reconstructs the original wide shape?

Solution. 40 rows (10 tickers × 4 metrics). long.pivot_table(index="ticker", columns="metric", values="value") reconstructs the wide shape (aggregating only if a duplicate (ticker, metric) combination existed, which it does not here).

P12. Vectorized vs. .apply(axis=1). Two ways to compute a mid column: df["mid"] = (df["high"] + df["low"]) / 2 versus df.apply(lambda r: (r["high"] + r["low"]) / 2, axis=1). Which is preferred, and name two independent reasons.

Solution. The vectorized version. Reason 1 (performance): it operates on the whole column in compiled code, with no per-row Python-level loop. Reason 2 (correctness risk): axis=1 apply hands your lambda a row as a Series with mixed dtypes silently coerced to a common type, which can hide a subtle type-related bug that the original, properly-typed columns would not have had.

P13. Spot the chained-indexing bug. df[df["ticker"] == "NILAM"]["close"] = 0 is meant to zero out NILAM's closes. Name the bug and the one-line fix.

Solution. Bug: two chained bracket lookups (df[mask] then ["close"] = 0) instead of one combined selection-and-assignment, in this app's pandas this raises ChainedAssignmentError; in other pandas versions it can silently fail to mutate df. Fix: df.loc[df["ticker"] == "NILAM", "close"] = 0.

P14. Multi-key sort order. df has rows (ticker="B", date="2024-01-01"), (ticker="A", date="2024-01-02"), (ticker="A", date="2024-01-01"). What order does df.sort_values(["date", "ticker"]) produce?

Solution. Sort by date first, ticker second, within ties: (A, 2024-01-01), (B, 2024-01-01), (A, 2024-01-02), both 01-01 rows come before either 01-02 row, and within 01-01 the tickers are alphabetical.

P15 (numeric, India ₹). SARASWATI's real closes for 2024-07-01 to 07-03 are 735.27, 746.08, 718.17. By hand, using groupby(...).pct_change() logic, what is the daily_return for the middle row?

Solution. (746.08 - 735.27) / 735.27 ≈ 0.01470 (about +1.47%). The first row's daily_return is NaN (no prior day within the group); this middle-row computation never looks outside SARASWATI's own three rows.

P16 (numeric, US $). prices = {"ticker": ["BRPEAK", "CLRRDG", "NEWTICK"], "close": [58.78, 254.83, 10.0]}; fundamentals has rows only for BRPEAK and CLRRDG. After prices.merge(fundamentals, on="ticker", how="left", validate="one_to_one"), how many rows result, and what is true of NEWTICK's fundamentals columns?

Solution. 3 rows (every prices row survives under how="left"). NEWTICK's fundamentals columns (e.g. net_income, revenue) are NaN, present as a row, missing as data, never silently dropped.

P17. Spotting an incomplete file with value_counts(). A clean EOD file should show exactly 500 rows for every ticker. df["ticker"].value_counts() shows NILAM at 500 but TARANG at 487. What does this tell you, and precisely what should you check next?

Solution. TARANG is missing 13 trading days somewhere in the file, value_counts() only tells you a ticker is short, never WHERE the gap sits. Next: filter to TARANG alone, sort by date, and scan for a break in the expected trading-day sequence (or diff consecutive dates) to find exactly which day(s) are missing before deciding how, or whether, to fill them.

P18. .size() vs. .count(). A ticker X has 500 rows, of which 3 have a missing close. What does df.groupby("ticker").size() return for X? What does df.groupby("ticker")["close"].count() return for X?

Solution. .size() returns 500, every row in the group, regardless of any missing values. ["close"].count() returns 497, only the non-null close values. The two diverge by exactly the number of missing values in that column for that group; asking for the wrong one silently over- or under-reports how much real data you actually have.

Applied mini-project

Wrangle a two-market research snapshot, end to end.

Using the shipped nse-eod/us-eod files (and, if you want the fuller version, india-fundamentals/sec-fundamentals), build one small, ordered pipeline and hand in the resulting table plus a one-paragraph account of every decision you made along the way. This is deliberately the smallest possible version of a real desk task (pull two markets' raw feeds, clean them, combine them, and hand back one trustworthy table) and every step maps to a specific section of this module, so a gap in the result points you straight back to the section that covers it.

Do this, in order:

1. Read and inspect. Load both EOD files, fix the date dtype on each, and run the five-call inspection ritual on each before touching either further.
2. Pick two tickers per market (four total) and select each ticker's closes for one real calendar month of your choosing, using a single .loc[mask, cols] call per ticker, no chained indexing.
3. Derive each ticker's daily_return correctly (grouped, so no cross-ticker bleed even though you built the four series separately here, state in your write-up why grouping still matters in principle).
4. Introduce, then fix, one missing-data case. Deliberately blank out one day's close for one ticker, forward-fill it correctly (per ticker), and explain in one sentence why you chose ffill and not fillna(0) or bfill.
5. Combine the two markets with pd.concat, tagging a market column first, and pass ignore_index=True, say in your write-up what would have gone wrong at the next step if you had forgotten it.
6. Group and summarize with a named .agg(), one row per (market, ticker), at least a start/end/total_return.
7. Sort the result by total_return descending and present it as your final table.

Stretch goal (optional, not scored). Pull each ticker's most recent fundamentals row from india-fundamentals/sec-fundamentals, merge it onto your four-row summary with how="left", validate="one_to_one", and melt the four fundamentals metrics to long form alongside it, a working, if small, demonstration that every operation in this module composes into one pipeline, not four unrelated tricks.

Deliverable and self-check. One final summary table (four rows, one per ticker) plus your write-up. Score yourself out of 10: 2 points for the inspection ritual genuinely run and reported on (not skipped); 2 points for zero chained-indexing anywhere in your code; 2 points for the missing-data fix being correctly grouped and correctly justified (ffill, not bfill, not a blanket zero-fill); 2 points for a correct concat (with ignore_index=True) + named .agg() combination producing exactly one row per (market, ticker); 2 points for a correctly computed, correctly sorted total_return. Pass at 8/10 with no zero on any item. Keep this pipeline: DA1.02-e08's synthesis exercise is the same shape, graded automatically.

Reading & resources

Everything below is an anchor to verify against and learn from, never a substitute for running the code yourself. Free unless marked; level tags reflect the ramp from intuition to fluency.

The official spine:

• pandas User Guide, 10 minutes to pandas and Essential basic functionality, the fastest accurate on-ramp to Series/DataFrame construction, selection, and the inspection ritual. [Free] [Beginner]
• pandas User Guide, Copy-on-Write: the primary source for exactly the chained-indexing behavior this module verifies and teaches; read it once you have hit a ChainedAssignmentError yourself, not before. [Free] [Intermediate]
• pandas User Guide, Merge, join, concatenate and compare and Group by: split-apply-combine, the official, precise reference for how=, validate=, and the agg-vs-transform distinction. [Free] [Intermediate]
• pandas API reference for read_csv, merge, groupby, melt, pivot_table, the ground truth for every parameter this module names; when in doubt about a keyword argument's exact default, this is the primary source, not a blog post. [Free] [Intermediate–Advanced]

Deeper and applied:

• Wes McKinney, Python for Data Analysis (the pandas creator's own book), the deepest single applied treatment of everything in this module, and the natural next stop before DA1.03. [Paid] [Beginner–Intermediate]
• Tom Augspurger, Modern Pandas (blog series), idiomatic, chainable pandas style, including exactly the vectorized-vs-.apply() judgment this module drills. [Free] [Intermediate]
• pandas release notes (whatever version you are actually running: pd.__version__), the primary source for any version-specific behavior, including the Copy-on-Write change this module names precisely rather than assumes; treat "what pandas does" as perishable and check it, the same discipline AI0.01 teaches for a model's knowledge cutoff. [Free] [Intermediate]
• Hadley Wickham, Tidy Data (2014 paper), the original, language-agnostic case for the "one row per observation, one column per variable" shape this module's melt/pivot section is built around; short, and worth reading once even though its code examples are in R, not Python. [Free] [Intermediate]

This program's own anchors:

• CS2.03 (Databases & SQL for Analysts): the same relational operations (filter, join, aggregate) taught in SQL against the identical finance-db.sqlite schema; read side by side with this module's merge/groupby sections if either syntax feels unfamiliar (R1).
• app/public/datasets/MANIFEST.json, the authoritative provenance record for every dataset id used in this module and its code pack: kind (real/synthetic), seed, and a plain-English provenance note for each.
• SEC EDGAR and BSE/NSE company filings, the real primary sources for genuine fundamentals data, should you ever want to practice this module's merge/melt patterns on real (not seeded-synthetic) figures. [Free] [Beginner–Advanced]

Do this, not just read: open a Python REPL, import pandas as pd, load one of the shipped CSVs, and run the five-call inspection ritual on it right now, before starting the practice set. Ten minutes of looking at .dtypes and .describe() on data you did not write yourself will teach you more about what can go quietly wrong than any amount of reading about it in the abstract.

Teach it back & journal

Feynman prompt. Explain to a spreadsheet-fluent colleague who has never used pandas why df[df.ticker=="X"]["close"] = 0 is not "just a slightly different way to write a VLOOKUP-and-overwrite", using the idea that pandas is asking you to do the selection AND the assignment in one .loc[mask, col] = value step, not two. Then explain, in the same breath, why a groupby("ticker") has to come before a pct_change() or a ffill() the moment more than one entity's rows sit in the same table, using the trophy-and-suitcase-style idea that "the row right before this one" is only meaningful within the same ticker, never across a boundary to a different one. If your colleague can, unprompted, predict that a bare merge might silently drop rows and ask "what does how= do here?", you have installed the mental model this module exists to build.

Journal prompt. For the practice problems and the applied mini-project, keep a running list of every line of your own code that DIDN'T do what you expected on the first try. For each one: what did you expect, what actually happened, and which of this module's Common Mistakes (or none of them, a genuinely new one) does it match? Then write a paragraph on this: an analyst's edge with pandas is not knowing more method names than the next person, it's the habit of verifying a transformation's OUTPUT against a small case you can check by hand, every time, rather than trusting that a line of code that "ran without error" therefore "did the right thing." Which single habit from this module (the five-call inspection ritual, grouping before any per-entity operation, or adding validate= to every merge) would most change how much you trust your own pandas code by next week, and what will you do today to make it automatic?

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.