Chapter 1: Data Structures and Methods of Series
Chapter Introduction
Every statistical model you will fit in this course — a regression in Chapter 4, a Bayesian posterior in Chapter 5, a GARCH volatility model in Chapter 6 — consumes one or more columns of numbers. In pandas a column is a Series: a one-dimensional array of values that carries a label for every entry and a single data type for all of them. A DataFrame, which Chapter 2 introduces, is nothing more than a dictionary of Series that share an index. If you understand what a Series is and what its thirty or so everyday methods do, you understand ninety per cent of the data-preparation code you will ever write.
The reason to spend a whole chapter on one object is that a Series is not a list. A Python list holds things; it cannot take its own mean, it cannot add ten to every element, and it has no idea which entry corresponds to which day. A Series can do all three, and it does them vectorised — the operation runs in compiled C over the whole array at once rather than in an interpreted Python loop — and aligned, meaning that when two Series meet in an expression, pandas first matches their labels and only then operates. Alignment is the feature that makes pandas safe for financial data with ragged calendars, and it is also the source of the most common surprise in the language: the appearance of NaN where you expected a number.
This chapter therefore has three recurring themes. The first is the index: what it is, how it is created, how it drives arithmetic, and the difference between selecting by label (.loc, right-inclusive) and by position (.iloc, right-exclusive). The second is missing data: how NaN arises, why aggregations skip it while element-wise operations propagate it, and how to count, drop, fill or interpolate it according to what a gap means. The third is immutability by default: almost every method returns a new Series and leaves the original untouched, which is a blessing when you chain methods and a trap when you write into a slice with two pairs of brackets.
The datasets are the ones the course uses throughout. A 20 000-product sample of a Kaggle retail inventory file supplies list prices with a long right tail and a spike of free items — the ideal material for medians, clipping and binning. A table of 3 084 US public companies supplies a dividend-yield column that is blank for more than half the firms, so that missing data is real rather than simulated. Daily closes for Apple (2015–2024), Microsoft (2015–2018) and the S&P 500, hourly electricity load for the AEP utility in 2017, daily Delhi weather, one trading day of Apple one-minute bars and two years of NVDA closes supply everything the time-series section needs: returns, rolling windows, drawdowns and resampling. Every number printed below is the number the companion slide deck prints; the two are meant to be read together.
By the end of the chapter you should be able to build a Series from a list, a dictionary or a column; predict what happens when two Series with different indexes are added; compute any descriptive statistic and know whether pandas divided by \(n\) or \(n-1\); replace a loop with np.where, .where or .clip; slice a time series by label or position without an off-by-one error; and turn a price series into returns, moving averages, an equity curve, a drawdown series and a monthly or five-minute table. Chapter 2 builds on every one of these moves in two dimensions.
Table of Contents
- Series Basics, Operators, Aggregation and Conversion
- Manipulation: apply, where, Missing Data, Sorting, Clipping, Ranking, Binning
- Indexing: rename, reset_index, loc and iloc, Sampling, Reindexing
- Dates and Time: Shifting, Rolling, Cumulative, Resampling, Plotting
Series Basics, Operators, Aggregation and Conversion
A list cannot take its own mean
Suppose five monthly sales figures arrive as a plain Python list, alist = [300, 100, 0, 200, 1000]. The list can be appended to, sorted and indexed by position, but ask it for alist.mean() and Python raises AttributeError: 'list' object has no attribute 'mean'. A list is a container, not a statistical object. It also has no idea that the first entry is January: the correspondence between position 0 and a month lives in your head, not in the data.
Wrapping the same five numbers in pd.Series changes three things at once. The values acquire roughly two hundred methods — mean, median, std, quantile, rank, rolling, plot and the rest of this chapter. They acquire an index: a label for each entry, which pandas invents as RangeIndex(start=0, stop=5, step=1) when you supply none, and which you can set to month names, tickers or timestamps. And they acquire a single dtype: every element of a Series shares one type — int64 here, float64 if any value has a decimal point, object if a string sneaks in — which is what allows the arithmetic to run in compiled code.
A Series can be built from three kinds of input. From a list you supply the values and optionally an index= of the same length and a name=. From a dictionary the keys become the labels automatically — the most natural way to build a small labelled Series by hand. From a NumPy array the array’s dtype carries over, so np.arange(5) * 10.0 gives float64 even though every value is a whole number. The cell below builds all three and shows the two doors into a labelled Series: .loc["Jan"] by label and .iloc[0] by position.
The list raised; the Series returned 320.0. The from_dict index is Index(['Jan', 'Feb', 'March'], dtype='object') — the dictionary keys, in insertion order. The array-built Series is float64 and the list-built one int64: the dtype is inferred from the values you pass, not chosen by you, and you will see later in this section how to change it explicitly. Finally sales.loc["Jan"] and sales.iloc[0] both return 300, and sales.loc["May"] and sales.iloc[-1] both return 1 000. For a string index the two doors never collide; for an integer index they can, and the whole of Section 3 is about keeping them apart.
sales.index, sales.dtype, sales.shape, sales.name and sales.values are attributes — written without parentheses, they describe the object. sales.mean(), sales.sort_values(), sales.plot() are methods — written with parentheses, they compute something. The rule holds throughout pandas: intrinsic properties are attributes, work is a method. Confusing the two is the source of TypeError: 'RangeIndex' object is not callable (you wrote sales.index()) and of <bound method Series.mean of ...> printed instead of a number (you forgot the parentheses).
Comparisons return masks
Comparing a Series with a number does not return True or False; it returns a Series of booleans with the same index and the same length, one answer per element. This object is called a mask, and indexing a Series with a mask keeps exactly the rows where the mask is True. The mask is the single most important idiom in pandas: every filter you write — days with a loss, customers who defaulted, products above the median price — is a mask, and every conditional statistic is a statistic computed over a masked Series.
Two masks combine with & (and), | (or) and ~ (not). They must not be combined with Python’s keywords and, or, not: those keywords ask for a single truth value, and a five-element Series has five, so pandas raises ValueError: The truth value of a Series is ambiguous. And because & binds more tightly than >=, each comparison must sit inside its own parentheses; Section 3 shows the error you get without them.
The median of sales is 200. Before you run the cell, predict which month labels survive sales[sales > sales.median()], and the dtype of the mask itself.
The mask has dtype bool and reads [True, False, False, False, True]; only January (300) and May (1 000) exceed the median of 200, so the filtered Series is {'Jan': 300, 'May': 1000}. The compound mask keeps the three months with at least 100 and fewer than 500 units — January, February and April. Notice that the filtered result kept its labels: filtering never renumbers, which is what lets you line the result up with other Series later.
A real column: 20 000 products
The rest of this section uses a column from a real file. The Kaggle sales-analysis dataset lists products a retailer must decide to keep or discontinue: PriceReg is the list price, SoldFlag is 1 if the product sold in the past six months, ReleaseNumber counts how many times it has been re-released, and ReleaseYear records when. The full file has 198 917 rows split between historical products and the active inventory awaiting a decision; the course server holds a 20 000-row sample of the historical part.
import isom5650
df = isom5650.data.inventory() # or pd.read_csv(url) with the Google-Drive link in the notebook
price = df["PriceReg"]Reading a CSV returns a DataFrame; df["PriceReg"] (or the attribute form df.PriceReg, which works when the column name is a valid identifier) returns the column as a Series named after the column, carrying the frame’s RangeIndex. From that moment everything in this chapter applies to it. The first thing to try is arithmetic with a scalar, which broadcasts: (ReleaseNumber + 2) / 100 adds 2 to every element and divides every result by 100, in one expression and no loop. Two Series of the same index combine element by element, so ReleaseNumber * price is a rough revenue figure for every product at once.
The frame is (20000, 14), the column is a float64 Series, and its first three prices are 44.99, 24.81, 46.0 with SoldFlag 0.0 for all three (only about ten per cent of these products sell in any six-month window). The first ReleaseNumber is 15, so (15 + 2) / 100 = 0.17, and the list continues 0.09, 0.02. Revenues are 674.85, 173.67, 0.0 — the third product has never been released — and the column sums to 10.57 million. Try alist + 2 on a plain list and Python raises TypeError: can only concatenate list (not "int") to list; broadcasting is a NumPy privilege that a Series inherits.
Index alignment: the rule that governs every operator
Here is the property that separates pandas from a fancy list, and the one that produces the most confusion. When two Series meet in an arithmetic expression, pandas does not pair them up position by position. It first takes the union of the two indexes, reindexes both operands onto that union, and only then operates element by element. A label that exists in only one operand has no partner; there is nothing to add, so the result at that label is NaN. Nothing is raised, nothing is truncated, and the result may be longer than either input.
Two consequences follow immediately. First, pd.Series([1, 2, 3, 4]) + pd.Series([100, 200, 300]) does not fail on a length mismatch: labels 0–2 get sums and label 3 gets NaN. Second, the result’s dtype is float64 even though every input was an integer, because NaN is a floating-point value — there is no integer NaN. If you would rather treat the missing side as zero, the method form series1.add(series2, fill_value=0) does exactly that, and it chains: .add(...).div(10) applies the division to the outcome of the addition, which is the method-chaining style Chapter 2 develops.
The same rule applies to a real calendar. appl.csv holds 2 516 Apple closes from 2015-01-02 to 2024-12-31; microsoft.csv holds 780 Microsoft closes from 2014-12-31 to 2018-02-05. Add them and pandas aligns on the date label: only dates present in both files receive a sum. In a hand-written loop you would build a lookup dictionary, test membership and decide a policy for misses — one + folds all three steps in. Comparison operators also have method twins — .gt, .lt, .eq, .between — and NumPy’s np.logical_and is a third spelling of &; the cell closes with the three spellings on sales.
The length-mismatched sum is [101.0, 202.0, 303.0, nan] with dtype float64; with fill_value=0 the last entry becomes 4.0. The chained Sold.add(New_Release, fill_value=0).div(10) gives [0.1, 0.1, 0.0, 0.2, 0.2] for the first five products. For the two stocks the union holds 2 517 date labels — 2 516 Apple dates plus the one Microsoft date, 2014-12-31, that Apple’s file does not contain — of which 779 carry a sum and 1 738 are NaN. The first two matched days are 2015-01-02 (74.09) and 2015-01-05 (72.89), each the sum of an Apple close near 27 and a Microsoft close near 47. The three spellings of “between 100 and 500” all return {'Jan': 300, 'April': 200}; ~s1 flips the mask to {'Feb': 100, 'March': 0}; .between is inclusive at both ends by default and so counts three months.
Alignment prevents you from adding Apple’s Monday to Microsoft’s Tuesday. But it never warns. If you expected 780 sums and got 779, or expected two entries and got three with two NaN, alignment is the cause. The habit that protects you: after any binary operation on Series from different sources, print len() and .isna().sum(). Section 3 introduces reindex, the tool for aligning two calendars deliberately rather than by accident.
Descriptive statistics and the ddof question
Every summary statistic is one method call: mean, median, mode, std, var, min, max, quantile, skew, kurt. A quick review of what they measure: central tendency (a typical value, \(E[X]\)); dispersion (variance \(E[(X-\mu)^2]\), its square root the standard deviation, the range, and the inter-quartile range \(Q_3 - Q_1\)); and shape (skewness from the third central moment, kurtosis from the fourth). One keyword changes the answer of the dispersion measures, and you must know which way it changes it.
The sample standard deviation divides the sum of squared deviations by \(n-1\); the population standard deviation divides by \(n\):
\[ s = \sqrt{\frac{\sum_{i=1}^{n}(x_i - \bar x)^2}{n-1}}, \qquad \sigma = \sqrt{\frac{\sum_{i=1}^{n}(x_i - \mu)^2}{n}}. \]
The \(n-1\) is Bessel’s correction. Because \(\bar x\) is estimated from the same data, the deviations from it are on average slightly smaller than the deviations from the true mean \(\mu\) — the sample mean is, by construction, the point that minimises the sum of squared deviations — so dividing by \(n\) would underestimate the variance. Dividing by \(n-1\) removes that bias exactly (for the variance; for the standard deviation only approximately). The keyword that controls this in pandas is ddof, “delta degrees of freedom”: the divisor is \(n - \text{ddof}\). pandas defaults to ddof=1; NumPy’s np.std defaults to ddof=0. The same column therefore gives two different numbers depending on which library you call, and since a smaller divisor gives a larger result, the pandas default is always the larger of the two.
How large is the gap? For the price column, with \(n = 20\,000\), the ratio \(\sqrt{n/(n-1)}\) is \(1.000025\) — the two estimates differ in the fourth decimal place. For the five-element sales Series the ratio is \(\sqrt{5/4} = 1.118\), an 11.8 % difference. That is the rule of thumb: ddof matters when \(n\) is small, which is precisely when you are least able to tell from the number itself which convention was used. Use ddof=1 whenever your data are a sample from a larger process — a decade of daily returns, a month of delivery times, a survey — which is almost always; use ddof=0 only when you genuinely hold the whole population, such as the salaries of every employee in a firm.
Mean 109.852, median 89.95, mode 0.0. The mean sits well above the median because the distribution has a long right tail — the maximum is 2 800 while three quarters of the products cost less than 147.40 — and the mode is zero because 607 products are listed free. price.std() is 85.9252 and price.std(ddof=0) is 85.9230, and np.std(price) reproduces the latter: NumPy’s default is the population divisor even when handed a pandas Series. The range is 2 800 and the inter-quartile range 95.41, a robust spread that ignores the tails the standard deviation is inflated by. For the five sales figures the sample standard deviation is 396.232 against a population value of 354.401, ratio 1.118 as computed above.
Leave .std() and .var() at their pandas defaults. ddof=1 is what Excel’s STDEV.S, statsmodels, and every statistics textbook mean by “the standard deviation of a sample”. When your number disagrees with a classmate’s in the third decimal place, ddof is the cause nineteen times out of twenty. When you must call NumPy, write np.std(x, ddof=1) explicitly.
The mean of a mask, agg, and value_counts
A boolean is arithmetically 1 for True and 0 for False. Summing a mask therefore counts the True entries, and taking its mean gives the proportion of True entries — a trick you will use all term to compute default rates, hit rates, missing-value rates and, in Chapter 2, p-values. price.gt(50) is the method spelling of price > 50; its sum is the number of products above 50 and its mean is the share.
The agg method (short for aggregate) accepts the name of a statistic as a string, a list of names, or any function of your own that maps a Series to a scalar. Three spellings — price.mean(), price.agg("mean") and price.agg(my_mean) — return the same number; the value of agg is that a list returns a labelled Series of statistics and a function lets you define a statistic pandas does not ship, such as the coefficient of variation \(s / \bar x\). describe() bundles eight of the standard ones. idxmax and idxmin return the label of the extreme rather than its value, which is what you need when the label is a date. For a discrete or text column the summary is value_counts(), the frequency table, and with normalize=True it returns shares instead of counts.
15 146 of the 20 000 prices exceed 50, so price.gt(50).mean() is 0.757: three quarters of the catalogue. idxmax returns 15511, the row label of the 2 800 maximum. The list form of agg returns mean 109.852, standard deviation 85.925, skewness 3.777 and kurtosis 70.075 — a normal distribution has skewness 0 and (excess) kurtosis 0, so this column is heavily right-skewed with a tail far fatter than Gaussian, which is why the median was a better “typical price” than the mean. The coefficient of variation is 0.7822 and the custom inter-quartile range 95.41, matching the direct computation above. Release years: 2010 leads with 1 796 products, then 2008 (1 743), 2009 (1 730), 2007 (1 722) and 2006 (1 573); there are 61 distinct years and the top one holds 9.0 % of products.
Conversion: astype and to_datetime
A Series has one dtype and changing it is an explicit act. astype("float32") stores every number in four bytes instead of eight, halving memory — useful for large tables, at the cost of precision: 44.99 becomes 44.990002 in single precision. astype(int) turns the strings "1", "2", "3" into integers so they can be summed. Two facts about astype matter more than any particular conversion. It never modifies in place — it returns a new Series, and the original keeps its dtype — and it is the only sanctioned way to change a dtype; assigning a string into a numeric Series does not “convert” it, it silently degrades the whole Series to object.
The conversion that matters most is text to dates. A CSV stores dates as strings, and read_csv leaves them as strings unless you tell it otherwise. A date stored as text can be printed but not reasoned about: it sorts alphabetically ("10/09" before "2/01"), it cannot be compared with a real date, and it has no year or weekday. pd.to_datetime parses the strings into Timestamp objects backed by the datetime64[ns] dtype; from then on the index supports .year, comparisons with dates, and — Section 3 — slicing by year or month with a partial string. When the dates are the values of a Series rather than its index, the same properties are reached through the .dt accessor, because Series.year alone would be ambiguous between “an attribute called year that you added” and “the year inside the datetime values”.
The float64 column occupies 160 000 bytes and its float32 copy 80 000; the first value prints as 44.99 at display precision, and price.dtype is still float64 because astype returned a copy. The three text digits sum to 6 once converted. The index of history starts life as str, and the text sort puts "12/31" before "2/01" — alphabetical, not chronological. After pd.to_datetime the first label is a Timestamp, .year works, and the comparison with a real date keeps the three October days before the 12th. For the Series of dates, .dt.day_name() reports that 9 and 10 October 2022 were a Sunday and a Monday.
Arithmetic aligns on the union of the two indexes before operating. A label with no partner receives NaN; nothing is raised or truncated. Because NaN is a float, an integer result becomes float64. Use .add(other, fill_value=0) to treat the missing side as zero.
Larger. pandas divides by \(n-1\) (ddof=1, the sample standard deviation); NumPy divides by \(n\) (ddof=0, population). The ratio is \(\sqrt{n/(n-1)}\): negligible at \(n = 20\,000\) (85.9252 vs 85.9230) but 11.8 % at \(n = 5\).
True counts as 1 and False as 0, so the mean of a mask is the proportion of elements satisfying the condition — price.gt(50).mean() = 0.757. The same trick computes missing-value rates (isna().mean()), hit rates, default rates and bootstrap p-values.
Manipulation: apply, where, Missing Data, Sorting, Clipping, Ranking, Binning
apply, a loop, or a vectorised operator?
Every row-by-row loop you are tempted to write over a Series has a one-line vectorised replacement, and the replacement is not only shorter but hundreds of times faster. The mechanism is worth understanding because it decides how you write pandas for the rest of your career. A Series stores its values in a contiguous NumPy array. A method such as .gt(50) hands the whole array to compiled C code, which walks it in one tight machine loop and returns a new array. apply(f), by contrast, calls your Python function f once per element: 20 000 interpreter round trips, each with the overhead of a function call, a boxed Python float and a boxed Python boolean. An explicit for loop does the same work with the same overhead, minus the small cost of the function call. apply is therefore not vectorisation; it is a loop wearing a method’s clothes.
The cell times the three approaches on the 20 000 list prices with time.perf_counter(), the high-resolution clock. Timings vary with the machine and with the browser’s Pyodide runtime, so do not memorise the milliseconds; memorise the ratio. The lecture notebook reports 67 ms for apply against 0.15 ms for .gt — a factor of about 400; on a laptop the ratio is nearer 100; in the browser it may be either. What never changes is that the vectorised call is the cheapest by one or two orders of magnitude, and that all three give the same answer.
All three agree (True True); only the last is essentially free. The rule that follows: reach for apply only when no vectorised method exists — a genuinely irregular per-element computation, a string-parsing rule, a call into an external library. Before writing apply(lambda ...), ask whether np.where, .where, .clip, .rank, .replace or pd.cut — the rest of this section — already does the job.
where and mask: if-else without a loop
The first vectorised replacement for a loop is the conditional. s.where(cond, other) keeps the values of s wherever cond is True and replaces the rest with other, which defaults to NaN. s.mask(cond, other) is its mirror image: it replaces where cond is True. Neither changes the length of the Series — that is what distinguishes them from a boolean filter s[cond], which drops rows. Because the length is unchanged, the result still aligns with every other column of the frame, which is why where is the right tool for “set to zero but keep the row” and a filter is the right tool for “drop the row”.
The second is NumPy’s np.where(cond, if_true, if_false), which returns an array (wrap it in pd.Series with the original index to get a Series back) and reads like the sentence you meant. Labelling every product “Expensive” or “Cheap” is an if-else on every row; the cell does it three ways — apply with a Python function, np.where, and two chained .where calls — and checks that they agree. One warning about where with a string other: the moment a string enters a numeric Series the whole Series becomes object dtype, and arithmetic on it stops working.
Predict the first five values of price.where(price.ge(50), other=0) given that the first five prices are 44.99, 24.81, 46.0, 100.0, 121.95, and the length of price.where(price.ge(50)).
where keeps the two prices at or above 50 and zeroes the other three: [0.0, 0.0, 0.0, 100.0, 121.95]. mask does the opposite: [44.99, 24.81, 46.0, 0.0, 0.0]. The "Big" version works — [44.99, 24.81, 46.0, 'Big', 'Big'] — but the dtype is now object. The length is 20 000 either way. All three labelling methods produce {'Expensive': 15146, 'Cheap': 4854}, the same 15 146 that price.gt(50).sum() counted in Section 1, and equals confirms they are identical element for element.
The lecture notebook’s practice downloads a stock with yfinance and asks for the Sharpe ratio via agg and the standard deviation of returns on winning days via where:
import yfinance as yf
close = yf.download("AAPL", start="2015-01-01")["Close"].squeeze()
ret = close.pct_change().dropna()
sharpe = ret.agg("mean") / ret.agg("std") # daily; × sqrt(252) to annualise
win_std = ret.where(ret > 0).std() # NaN on losing days, skipped by stdOn the course copy of appl.csv the numbers are sharpe = 0.0581, win_std = 0.0127, and the losing-day standard deviation 0.0130. Note that where was used, not a filter: the NaNs it leaves on losing days are simply skipped by .std(), which is the next topic.
Missing data: how NaN behaves
NaN — “not a number” — is how pandas marks a missing value in a numeric Series. It is not an error and not zero; it is a placeholder that must be counted, dropped or filled before any model sees it, because statsmodels and scikit-learn refuse to fit on it. The inventory sample has no missing prices, so this subsection uses the course’s table of 3 084 US public companies, where dividendYield is blank for every firm that pays no dividend. (The full inventory file has the same feature in a different column: SoldFlag is missing for every Active product, 61.8 % of the rows, because the outcome is what the retailer is trying to predict.)
company = isom5650.data.publicCompany() returns the same table (3 084 tickers, sector, marketCap, dividendYield, …) with the ticker as the index.
isna() (alias isnull()) returns a mask that is True at the holes; notna() is its complement. By the trick of Section 1, isna().sum() counts the holes and isna().mean() is the missing rate. count() returns the number of non-missing values, which is not len(). dropna() removes the holes. And now the property that costs beginners the most hours: NaN behaves two different ways depending on what you do to it. Aggregations skip it — sum, mean, std, median, max all silently compute over the present values, as if the holes were not there. Element-wise operations propagate it — NaN + 1 is NaN, NaN * 0 is NaN, so dy + 1 has exactly as many holes as dy. The first behaviour is convenient and dangerous: the mean you print is a mean of the 1 474 dividend payers, not of the 3 084 companies, and nothing on screen says so.
Once counted, a hole must be handled, and the right method depends on what the gap means. dropna() discards the row — safe when the value is genuinely unknown and the rows are plentiful. fillna(value) substitutes a constant: zero for a confirmed non-payer’s dividend yield, the median or the mode for a value you would rather not invent. ffill() carries the last observed value forward — the natural choice for prices, where the last known quote is what a trader would have used, and wrong for a gap that means “the shop was closed”. interpolate() draws a straight line between the neighbours — what a chart wants, not what a trader knew. The cell shows all four on a four-element toy and closes by filling the dividend yields with their median.
1 610 of 3 084 dividend yields are missing — a missing rate of 0.522, more than half. 1 474 are present, and the three spellings (notna().sum(), the ~isnull() mask, dropna()) agree. The sum is 48.277 and the mean 0.0328, both over the 1 474 payers; count() says 1 474 while len() says 3 084. (dy + 1) still has 1 610 holes, and (dy * 0).sum() is 0.0 — the multiplication left NaN in the holes, and the sum then skipped them. On the toy: dropna leaves [1.0, 4.0]; fillna(0) gives [1.0, 0.0, 0.0, 4.0]; ffill gives [1.0, 1.0, 1.0, 4.0]; interpolate places the two missing values evenly at 2.0 and 3.0. After filling with the median, zero dividend yields are missing.
The lecture notebook asks you to fill the gaps in a 0/1 SoldFlag with the mode. The cell below runs without error and prints a result — but it is wrong. Find the bug before reading on.
sold.mode() returns a Series, not a scalar — there can be several modes, so pandas always returns a Series of them, here pd.Series([0.0]) with index [0]. fillna given a Series aligns it on the index and fills each hole with the value at the matching label. The only label in the mode Series is 0, and position 0 of sold is not missing, so nothing is filled: the output still shows two NaN. The fix is to pull out the scalar — sold.fillna(sold.mode().loc[0]) or sold.mode().iloc[0] — after which the result is [0.0, 1.0, 0.0, 0.0, 0.0, 0.0], no NaN, and the total is still 1. The lesson generalises: whenever you hand a Series to a method that accepts either a scalar or a Series, the Series form aligns, and alignment with the wrong index is a silent no-op.
Sorting, clipping, and duplicates
sort_values() returns a new Series in ascending order; ascending=False puts the maximum first. Two things about it are easy to forget. The row labels travel with their values — a sorted Series is a permutation of (label, value) pairs, not of values alone — so sort_index() afterwards restores the original order and forgets the value sort. And sorting returns a new Series: price itself is untouched, unlike list.sort(), which reorders in place. That non-mutation is the default for almost every pandas method; the inplace=True keyword exists but is discouraged, because it breaks method chains and hides state changes.
Quantiles are cut points on the sorted data. s.quantile(0.8) == 21 means that 80 % of the observations lie at or below 21 and 20 % above. Clipping — also called winsorising — uses two quantiles as a cap and a floor: clip(lower=lo, upper=hi) pulls every value below lo up to lo and every value above hi down to hi, and leaves the values in between untouched. Clipping at the 5th and 95th percentiles therefore alters exactly 10 % of the observations and removes the influence of the tails on the standard deviation without deleting any rows. Whether that is cleaning or destroying the signal depends on the question: for a list-price column whose 2 800 maximum is almost certainly an error, it is cleaning; for a return series whose 5 % tails are where the risk lives, it is a mistake, and Chapter 2 gives the tails their own theory.
drop_duplicates() removes repeated values, and the keep argument decides which copy survives: "first" (the default) keeps the earliest position, "last" the latest, and False keeps neither — every value that appears more than once is removed entirely.
Ascending order puts three free items at the top, with labels 4725, 17419 and 5531 — labels that tell you which products are free. Descending order puts {15511: 2800.0, 4071: 2083.09, 2218: 1580.0} first, and the subsequent sort_index(ascending=False) reverts to label order, showing rows 19999, 19998 and 19997. The clip cut points are 12.50 and 271.667; exactly 1 000 prices lie below the first and 1 000 above the second. Clipping raises the minimum from 0 to 12.50, lowers the maximum from 2 800 to 271.67, moves the mean only from 109.85 to 106.73, and cuts the standard deviation from 85.93 to 71.16 — a 17 % reduction in the dispersion measure from touching 10 % of the rows, which tells you how much of that standard deviation was tail. For dlist, keep="first" retains the 20 at position 1, keep="last" retains the one at position 3, and keep=False returns {0: 40, 2: 30, 4: 10} with no 20 at all.
Ranking and replacing
rank() assigns 1 to the smallest value, 2 to the next, and so on, with ties receiving the average of the ranks they would have occupied (method="average", the default; "min", "max", "first" and "dense" are alternatives). With pct=True the ranks are divided by the count, giving a percentile rank in \((0, 1]\) — the maximum is exactly 1.0. That one call turns any cross-section into a scale-free feature: the top-decile stock by momentum, the bottom-quartile customer by spend. It is the workhorse feature of Project 2 and the foundation of the learning-to-rank methods in Chapter 7, and it is robust to outliers by construction, because a 2 800 outlier gets rank 11 whether it is 2 800 or 28 000.
replace maps values to values. Given a list and a single replacement, every listed value becomes that replacement; given a dictionary, each key maps to its own value. It is the tool for recoding — turning ranks 1–4 into the label "lowest", turning a survey’s "Y"/"N" into 1/0 — and, as with where, the moment a string enters a numeric Series the dtype becomes object. The cell works on partPrice = price.loc[:10]: the first eleven prices, because .loc is inclusive of its end label — a fact Section 3 makes much of.
Eleven prices. The first four, 44.99, 24.81, 46.0, 100.0, receive ranks 3.0, 1.0, 4.0, 7.0: 24.81 is the smallest of the eleven, 44.99 the third smallest, and the 207.80 at position 7 gets rank 11. The list form of replace relabels ranks 1–4 as "lowest" (four entries), the dictionary form only ranks 1–3 (three entries), and in both cases the remaining entries stay floats inside an object Series. The percentile ranks run from 0.091 for the minimum to 1.0 for the maximum, in steps of \(1/11\).
Binning: equal width or equal count?
Binning turns a number into a category, and categories are what groupby in Chapter 2 consumes. Two conventions exist. pd.cut(s, bins=k) divides the range of the data into \(k\) intervals of equal width; pd.qcut(s, k) divides the observations into \(k\) groups of equal count by cutting at quantiles. On symmetric data the two agree; on a right-skewed column like list prices they could not be more different. The range runs from 0 to 2 800, so four equal-width bins are 700 wide — and since the median is 89.95 and the 95th percentile 271.67, almost every product lands in the first bin while the other three hold a handful of outliers. qcut instead places about 5 000 products in each bin, with edges at the quartiles. pd.cut also accepts an explicit list of edges, which is how you encode a business rule — “free, under 1 000, under 4 000, above” — and expose the 607 free items as a bin of their own. Intervals are right-closed by default: (0, 1000] includes 1 000 and excludes 0.
With prices from 0 to 2 800 and a median of 89.95, predict how many of the 20 000 products fall in the first of four equal-width bins — roughly a quarter, a half, or almost all?
Equal width: 19 990 of 20 000 products in (-2.8, 700], then 7, 2 and 1 in the three upper bins — the leftmost edge is pushed slightly below zero so that the minimum is included. The notebook’s explicit edges give [607, 19389, 4, 0]: 607 free items, 19 389 priced up to 1 000, four between 1 000 and 4 000, none above. qcut produces four bins of 5 007, 5 111, 4 884 and 4 998 products with edges at 51.99, 89.95 and 147.40 — the quartiles you met in Section 1. The rule: cut when the width of a bin has a meaning (age bands, price tiers), qcut when you want equal-sized groups (deciles of a signal, quartiles of income).
The notebook’s practice — rlist = pd.Series([100, 120, 50, 200, 40]), label "strong" where weight > 50 else "slim" — is pd.Series(np.where(rlist > 50, "strong", "slim")), giving ['strong', 'strong', 'slim', 'strong', 'slim']. The apply and double-where versions give the same answer; np.where is the one to remember.
where keeps the length: values where cond is True are kept, the rest replaced by 0 (or NaN by default). A boolean filter s[cond] drops the False rows and returns a shorter Series. Use where when the result must still align with other columns; use a filter when you want the rows gone. mask is where with the condition inverted.
s.mean() returns a number — the mean over the 1 474 present values — because aggregations skip NaN by default. (s + 1).isna().sum() returns 1 610, because element-wise operations propagate NaN. Same missing value, opposite treatments; count() (1 474) is not len() (3 084).
It is cleaning when the tails are errors or irrelevant to the question (a 2 800 list price in a catalogue whose 95th percentile is 272). It is destructive when the tails are the object of study — daily returns, where the 5 % tails carry the risk that VaR, drawdown and extreme-value theory (Chapter 2) are built to measure. Clipping alters exactly 10 % of the observations and no row count; always report the cut points.
Indexing: rename, reset_index, loc and iloc, Sampling, Reindexing
The index is the address book
The index of a Series is its address book: a label for every value, used by alignment, by selection, by joins and by every plot’s x-axis. This section is about editing that address book and about reading from it without error. The reason it deserves its own section is that pandas offers two addressing schemes — by label and by position — with different slicing conventions, and code that confuses them produces results that are wrong by one row and pass every test.
Renaming comes in three forms. rename with a function applies it to every label; rename with a dictionary changes only the labels you list and leaves the others alone; and assigning to .index replaces the entire label set at once, in place, and must be given exactly the right number of labels. Distinct from all three is rename_axis, which changes the name of the index itself — the header printed above the labels, Date or Datetime in a file read with index_col= — and not the labels. That name seems cosmetic until you call reset_index(), which turns the index into an ordinary column: the column is named after the index, so the name is what your DataFrame’s column will be called.
reset_index() on a Series returns a two-column DataFrame — the old index and the values — with a fresh RangeIndex. With drop=True the old index is discarded and only the values remain under a RangeIndex. That option is handy after a filter has left you with gaps in the row numbers, and dangerous for a time series: dropping a DatetimeIndex throws away the dates, after which no alignment, resampling or date slicing is possible. The cell demonstrates on one year of hourly electricity load for American Electric Power, a column of the PJM hourly file.
energy = isom5650.data.energy() returns the full PJM hourly load table (2004–2018, 178 258 rows) with Datetime as the index; aep = energy["AEP"]. The course server holds the same file; usecols= reads only the two columns you need and .loc["2017"] keeps the 8 759 hours of 2017.
The function form produces ['ID-ONE', 'ID-TWO', 'ID-THREE']; the dictionary form changes only the first label, ['jan', 'TWO', 'THREE']; the assignment replaces all three, after which the Series reads {'jan': 100, 'feb': 200, 'march': 50}. The 2017 load has 8 759 hourly readings (one hour short of \(365 \times 24\): the file skips the hour lost to daylight-saving time), and its index is named Datetime. After rename_axis("FancyName") the reset frame’s first column is FancyName; without it the columns are ['Datetime', 'AEP']. With drop=True only the megawatt values survive, labelled 0 and 1 — the timestamps are gone.
.loc is right-inclusive, .iloc is right-exclusive
.loc selects by label and .iloc by integer position. Both accept a single value, a list, a slice, or a boolean mask. The one difference you must internalise is the treatment of a slice’s end: .loc[a:b] includes both a and b, while .iloc[i:j] includes position i and excludes position j, exactly as a Python list does. The two conventions exist for good reasons. When you slice by label you name the end you want — “January through March”, “01:00 to 05:00” — and it would be perverse for the last named label to be left out; besides, for a non-numeric index there is no way to say “the label just before b”. When you slice by position you inherit Python’s half-open convention, under which s.iloc[:k] has exactly \(k\) elements and s.iloc[:k] followed by s.iloc[k:] partitions the Series with no overlap and no gap — the property a train/test split relies on.
On the hourly load, aep.loc["2017-01-01 01:00":"2017-01-01 05:00"] returns five rows, 01:00 through 05:00 inclusive. aep.iloc[0:5] also returns five rows — positions 0 to 4, that is 00:00 through 04:00. The counts agree; the rows do not: the label slice ends at 05:00, the position slice at 04:00. Two further details in the cell: .loc[["label"]] with a list of one label returns a one-row Series rather than a scalar, which keeps the index around; and int(len(aep) * 0.7) followed by iloc[:trainsize] is how you carve the first 70 % of a time-ordered Series into a training set — by position, because “the first 70 %” is a positional idea.
Five rows ending at 2017-01-01 05:00:00 by label; five rows ending at 2017-01-01 04:00:00 by position. The single-label list returns a one-element Series showing 12 876 MW at 01:00 with its Datetime header intact. The 70 % training slice holds 6 131 hours and ends at 11:00 on 13 September 2017.
Partial-date strings and the slicing trap
A DatetimeIndex accepts partial date strings in .loc, and they slice whole periods: "2020" means every row in 2020, "2020-03" every row in March 2020, and "2020-01-01":"2020-12-31" the same year spelled out. This is one of the most pleasant features of pandas — a backtest window reads like a sentence — and it is inclusive at both ends, as every .loc slice is. The cell uses the S&P 500 daily closes from sp500.csv.
The trap appears when label and position slices are mixed in one piece of code. January 2020 trading days sit at positions 0 (2 January), 1 (3 January), 2 (6 January), 3 (7 January), 4 (8 January). sp500.loc["2020-01-06":"2020-01-08"] and sp500.iloc[2:5] both return the three closes for 6, 7 and 8 January — but for opposite reasons: .loc includes its end label, .iloc excludes its end position, and they agree only because 2:5 happens to span three positions. Change one number, to iloc[2:4], and 8 January silently disappears. A backtest that computes its training window with .loc and its test window with .iloc is off by one day at every boundary, and no exception is ever raised.
"2020" returns 253 trading days, "2020-03" the 22 trading days of March, and the spelled-out range the same 253. The March 2020 low was 2 237.40 on the 23rd, the bottom of the pandemic crash. The first five 2020 labels are 2, 3, 6, 7 and 8 January. The label slice and iloc[2:5] both give [3246.28, 3237.18, 3253.05]; iloc[2:4] gives only the first two.
In Growth in a Time of Debt (2010) Reinhart and Rogoff reported that countries with public debt above 90 % of GDP averaged −0.1 % real growth, a figure cited by the UK Treasury, the European Commission and the US Congress to justify austerity. In 2013 Thomas Herndon found that the spreadsheet’s averaging formula covered rows 30–44 instead of 30–49: Australia, Austria, Belgium, Canada and Denmark were silently excluded, and with other corrections the figure became +2.2 %. The pandas equivalent is an .iloc end one row short, or a NaN skipped by mean(). Print len(), print isna().sum(), and never trust an aggregate whose denominator you have not seen.
Conditional selection, the parentheses trap, and bare brackets
.loc also accepts a boolean mask, or a function that returns one. The function form — price.loc[compare] or price.loc[lambda s: s > s.quantile(0.99)] — is useful inside a method chain, where the intermediate Series has no name you could write a mask against. Compare it with where from Section 2: price.loc[price > Q3] drops the rows below the third quartile, while price.where(price.gt(Q3)).dropna() reaches the same 4 998 rows in two steps.
Combining masks has one syntactic trap that produces a genuine error rather than a silent one, which makes it the friendly trap of this chapter. Python’s & binds more tightly than >, so r > 0.02 & r < 0.05 is parsed as r > (0.02 & r) < 0.05 — a bitwise AND between a float and a Series — and raises TypeError. The cure is parentheses around every comparison: (r > 0.02) & (r < 0.05). The keywords and, or, not are no cure: they demand a single truth value and raise ValueError on a Series.
The dangerous trap is the bare bracket. s[...] with no .loc or .iloc has to guess whether you meant a label or a position, and its guess depends on the index. For a string index s["Jan"] is unambiguous. For an integer index that is not in order — which is exactly what sort_values produces — s[0] means label 0, not the first element. Sort the prices descending: the index becomes 15511, 4071, 2218, …, and top[0] returns 44.99, the price of the product whose label is 0, while top.iloc[0] returns the 2 800 maximum. Bare brackets also change meaning between a scalar (label lookup) and a slice (s[0:3] is positional even on a labelled index), and pandas 2.x has begun deprecating the positional fallback. One extra word — .loc or .iloc — removes every ambiguity.
top = price.sort_values(ascending=False) has index 15511, 4071, 2218, …. Predict top[0] and top.iloc[0] before running.
The third quartile is 147.40 and 4 998 products lie above it, both ways. price.loc[compare] returns the 15 146 products above 50 from Section 1, and the lambda picks out the top one per cent, whose three largest members are 1 580, 2 083.09 and 2 800. Then the trap: top[0] is 44.99 and top.iloc[0] is 2 800.0. The bare bracket matched the label, and the first three labels of the sorted Series are [15511, 4071, 2218] — the products with the three highest prices.
Of Microsoft’s 779 trading days from 2015 to early 2018, 41 had a return between +2 % and +5 % and 83 moved more than 2 % in either direction. Without the parentheses the expression raises TypeError. The lecture notebook’s practice — find the worst days, those with a loss beyond 5 % — is one .loc with a mask: three days, the worst being 27 January 2015 at −9.25 %, the day after a disappointing earnings report.
head, tail, sample, and reindex
head(n) and tail(n) return the first and last \(n\) rows; sample draws rows at random. Three arguments matter. n or frac sets the size — frac=0.002 of 20 000 is 40 rows. random_state fixes the seed so the draw is reproducible; without it every run differs, which is fatal for a result anyone must check. replace=True allows the same row to be drawn more than once, so a 500-row sample with replacement will typically contain fewer than 500 distinct rows — that repetition is the ingredient of the bootstrap in Chapter 2, where you resample your own data to estimate the uncertainty of a statistic.
reindex conforms a Series to a list of labels you supply: existing labels are selected in the order given, and labels that do not exist are invented with NaN as their value (and the dtype becomes float). .loc with a list containing an unknown label raises KeyError instead. That contrast is the point: reindex is the deliberate version of the alignment that happens automatically in arithmetic, and it is how you place two calendars on a common footing — reindex the sparse series onto the dense one’s index, then decide what to do with the NaN (ffill for prices, zero for volumes, dropna for everything else).
With seed 0 the five sampled labels are [19134, 4981, 16643, 19117, 5306]; frac=0.002 gives 40 rows; and of 500 draws with replacement only 492 are distinct — eight products came up twice. reindex returns {'jan': 100.0, 'march': 50.0, 'Tom': nan, 'Katy': nan} — the two real labels selected, the two invented ones NaN, the integers now floats — while .loc with the same unknown labels raises KeyError.
Writing into a slice: the SettingWithCopy trap
Everything so far has read from a Series. Writing into part of one is where the most common pandas bug lives. Suppose you want to add 100 to every price above 50 among the first five products. The correct form is a single .loc on the left-hand side: p.loc[p > 50] = p.loc[p > 50] + 100. pandas evaluates the mask, locates the rows in p itself, and writes the new values into them.
The incorrect form uses two pairs of brackets — chained indexing — such as q[q > 50].loc[3] = 0. Python evaluates this left to right: q[q > 50] is an ordinary expression that returns a new Series (a filter cannot return a view, because the selected rows are not contiguous in memory), and .loc[3] = 0 then writes into that temporary object, which is discarded the moment the statement ends. q is unchanged. Older pandas printed a SettingWithCopyWarning for this pattern; pandas 2.x with Copy-on-Write enabled makes the discard the guaranteed behaviour and may say nothing at all, which is why the cell suppresses warnings rather than relying on them. Note also that head(5).copy() was used to build p and q: without .copy(), p would itself be a slice of price, and whether writing into it propagates back to price depends on the pandas version — another reason to make your intention explicit.
The single-.loc form gives [44.99, 24.81, 46.0, 200.0, 221.95] — the two prices above 50 each gained 100. The chained form prints [44.99, 24.81, 46.0, 100.0, 121.95]: the write went into a temporary and vanished. The rule is short enough to memorise: one .loc to write, never chained brackets. When you catch yourself writing df[...][...] = or s[cond][i] =, rewrite it as df.loc[rows, cols] = or s.loc[cond] =.
The notebook’s practice pairs every Friday close with the following Monday for NVDA. On the course file nvda_spy_daily_2023_2024.csv (501 trading days), nvda[nvda.index.day_name() == "Friday"] selects 102 Fridays, and nvda.shift(-1) — introduced in the next section — puts each following trading day’s close on the Friday’s row:
nvda = pd.read_csv(URL + "nvda_spy_daily_2023_2024.csv", index_col="Date", parse_dates=True)["NVDA"]
fridays = nvda[nvda.index.day_name() == "Friday"]
pairs = pd.DataFrame({"friday": fridays, "next": nvda.shift(-1).loc[fridays.index]}).loc slices by label and includes the end label; .iloc slices by position and excludes the end position (Python’s half-open convention). They agree here only because positions 1, 2, 3 happen to be Tue, Wed, Thu. Change to .iloc[1:3] and only Tue and Wed remain. Mixing the two conventions in one backtest is off by one row at every boundary.
44.99 — the price of the product whose label is 0, not the maximum. With an integer index a bare s[0] is a label lookup; top.iloc[0] is what returns the 2 800 maximum. Always write .loc or .iloc.
q[q > 50] is an expression that returns a new, temporary Series (a boolean filter cannot be a view). .loc[3] = 0 writes into that temporary, which is then discarded. Nothing reaches q, and pandas may or may not warn. The fix is one .loc on the left-hand side: q.loc[(q > 50) & (q.index == 3)] = 0, or simply q.loc[3] = 0.
Dates and Time: Shifting, Rolling, Cumulative, Resampling, Plotting
A date column is text until you say otherwise
A price is a number; a return is a relation between today and yesterday. Every quantity in this section — return, moving average, drawdown, monthly mean — is a relation between a value and its neighbours in time, and so every one of them requires a Series that knows what time it is. That knowledge is not automatic. read_csv keeps a date column as strings unless you pass parse_dates=True (with index_col=, as every stock file in this chapter has done) or convert afterwards with pd.to_datetime. The conversion turns each string into a Timestamp and the column’s dtype into datetime64[ns]; from that moment the .dt accessor exposes the parts of each date — year, month, dayofweek (Monday is 0), day_name(), is_month_end — and strftime turns dates back into text in any layout, which is how you produce a report’s "01/01/13" from a machine’s 2013-01-01.
The cell uses the Delhi daily-weather table, 1 462 rows from 2013-01-01 to 2017-01-01, whose date column arrives as text. It then applies the same tools to a finance question the lecture notebook poses: the weekend effect, the folklore that Monday returns are systematically lower than Friday’s because investors sell on Mondays after digesting bad weekend news. With a DatetimeIndex the test is a mask on index.day_name() and two means.
weather = isom5650.data.weather() returns the same Delhi table. For the weekend effect the notebook downloads ten years of a ticker with yfinance; appl.csv holds the same decade for Apple.
The table is 1 462 × 5; the first date is a str before conversion and a Timestamp after, and the column’s dtype is datetime64[ns]. 1 January 2013 was a Tuesday, so dayofweek is 1. The data touch five calendar years but contain 48 month-ends — twelve for each of 2013–2016; the single day of 2017 is not a month-end. strftime("%d/%m/%y") gives "01/01/13" and "02/01/13", and parsing the %m/%d/%y layout back recovers 2013-01-01.
The weekend effect, on 2 515 Apple daily returns from 2015 to 2024: Mondays averaged +0.23 % and Fridays +0.01 % — the opposite sign to the folklore. Neither number deserves a trading rule: 0.2 % per day against a daily standard deviation of 1.79 % is well inside noise, and Chapter 2 supplies the two-sample test that makes “inside noise” precise. Twenty-two days lost more than 5 %; the worst, 16 March 2020 at −12.86 %, was indeed a Monday — the day the Federal Reserve’s emergency rate cut failed to calm the pandemic sell-off — but Mondays also supplied several of the best days, which is what an average of +0.23 % means.
shift: from prices to returns
shift(1) moves every value one row later, so that the value on today’s row is yesterday’s. The first row has no predecessor and becomes NaN; the last value falls off the end. shift(-1) moves the other way — tomorrow’s value sits on today’s row, and the NaN appears at the end. That one method turns a price series into a return series, because the simple return is
\[ r_t = \frac{P_t - P_{t-1}}{P_{t-1}}, \]
today minus yesterday, over yesterday — in pandas, (P - P.shift(1)) / P.shift(1), or with the method spellings P.subtract(P.shift(1)).div(P.shift(1)). pct_change() is the same line pre-written and gives an identical result. The cell also computes the forward return P.shift(-1) relative to P: tomorrow versus today. Its last entry is NaN because tomorrow is not yet known, and that is exactly why the forward return is the target \(y\) of every forecasting model in this course and never a feature — a model that sees shift(-1) on its input side has been shown the answer.
The notebook downloads Microsoft with yfinance (isom5650.data.getStock("MSFT")); microsoft.csv holds the same ticker from 2014-12-31 to 2018-02-05, and .loc["2015":] keeps the 779 trading days from 2 January 2015.
In the head, shift(1) is NaN on 2 January 2015 and carries 46.76 — the first close — onto the 5 January row; shift(-1) carries 46.33, the second close, back onto the first row. In the tail, shift(-1) is NaN on the last day, 5 February 2018. The manual return and pct_change agree (True); the first two returns are −0.92 % and −1.47 %. Over 2015–2018 Microsoft’s mean daily return was 0.091 % with a standard deviation of 1.42 %. The forward return ends [-0.0412, nan]: the 4.12 % fall from 2 to 5 February 2018 is known on the 2nd only in hindsight.
Rolling windows
A rolling mean replaces each value by the average of the last \(k\) values, including the current one. The first \(k-1\) rows have too few predecessors and are NaN — rolling(10) costs nine rows, rolling(40) thirty-nine — and the first non-missing value equals the plain mean of the first \(k\) closes. A short window reacts: it follows every dip. A long window filters: it smooths the dips away and lags behind turns. The two together are the oldest trend signal in finance — buy when the fast average crosses above the slow — and you will test it in Project 1. Any function can roll: rolling(k).apply(f) evaluates f on each window (with the same apply-is-a-loop cost as before), while mean, std, median, min, max and sum have fast built-in versions.
Nine and thirty-nine leading NaN; the tenth value of the fast average, 46.41, is the mean of the first ten closes. In the figure the 40-day line barely notices the dips of mid-2016 that the 10-day line follows down and back up.
The most important rolling statistic in this course is not the mean but the standard deviation of returns: r.rolling(21).std() is the realised volatility over the past trading month, and multiplied by \(\sqrt{252}\) it is annualised, because variance scales with time and there are about 252 trading days in a year. This is the quantity that GARCH models in Chapter 6 model rather than measure. The cell also introduces the cumulative family: cumsum, cumprod, cummax and cummin carry a running total, product, record high and record low. The product of gross returns \(\prod (1 + r_t) = \prod P_t / P_{t-1}\) telescopes to \(P_T / P_0\), the growth of one dollar invested at the start — so (1 + r).cumprod() is the equity curve of a buy-and-hold backtest, in one line. The sum of simple returns is not the same thing: it ignores compounding.
The rolling three-day medians begin 46.33, 46.23, 46.23. The 21-day realised volatility peaked at 43.2 % annualised on 18 September 2015, in the aftermath of the August 2015 China devaluation shock, and averaged 14.3 % through the calm of 2017. Compounded growth is 1.882 — exactly the ratio of the last close (88.00) to the first (46.76): one dollar became $1.88. The running sum of simple returns says 0.711, a different and wrong number — it corresponds to a 71 % gain, not 88 %. cummax ends at the overall maximum, and cummin never moves once the 40.29 low of early 2016 is in; in the figure cummax is the staircase of record highs the price keeps returning to.
Drawdown
The drawdown at time \(t\) is how far the price sits below its running record high, as a fraction of that high:
\[ \text{drawdown}_t = \frac{\text{cummax}_t - P_t}{\text{cummax}_t}, \]
and the maximum drawdown is the worst peak-to-trough loss in the sample — the number an investor would have suffered had they bought at the worst possible moment and held to the bottom. It is one of the two statistics isom5650.metrics.PerformanceMeasure returns for grading the projects, the other being the Sharpe ratio. Note that the formula needs the running maximum, cummax(), not the overall maximum max(): a peak reached after the trough cannot have been drawn down from.
The lecture notebook asks for the maximum drawdown of ten years of Apple. The cell runs and prints a drawdown — but the number is wrong, and so is the date. Find the bug before reading on.
The cell used aapl.max(), the overall maximum of the decade — a 2024 close near 259 — and measured every day’s distance below it. That reports a “drawdown” of about 0.91 on the day of the decade’s lowest price in 2016, which is not a loss anyone suffered: the 2024 peak had not happened yet. Replace aapl.max() with aapl.cummax() and the drawdown becomes 0.3873 on 3 January 2019 — a 38.7 % fall from the record close of 58.02 on 3 October 2018 to 35.55 three months later, the day Apple cut its revenue guidance on Chinese demand. The bug is a look-ahead: the wrong formula lets the future decide the present, the same sin as feeding shift(-1) to a model.
Resampling
Resampling changes the clock. Daily to monthly is down-sampling: many rows collapse into one by an aggregation you choose. Monthly to daily is up-sampling: rows are invented and you decide how to fill them. The syntax is resample(rule).aggregation(), where rule names the bin — "ME" month-end, "QE" quarter-end, "W" week ending Sunday, "2ME" two months, "5min" five minutes, "1h" an hour — and the aggregation is any of mean, sum, last, first, count, quantile, ohlc, or agg(...). resample is groupby for time, and Chapter 2’s groupby will feel familiar because of it.
Two conventions matter. The label of a bin is its boundary, not a data point: "ME" labels every month with its last calendar day — 31 January 2015, a Saturday, even though the last trading day was the 30th — and "MS" would use the first. And the first and last bins are usually partial: Microsoft’s first 2015 row is Friday 2 January, so the first weekly bin, labelled by Sunday 4 January, holds one observation where a full week holds five. Always check count() on the first and last bins before trusting a weekly or monthly statistic.
The aggregation must match what the number means. A .sum() of daily closes is meaningless; a .mean() of daily volume understates the month’s turnover. Use mean for a level, last for a closing price, sum for a flow such as volume or sales, ohlc to rebuild candlesticks. And there is a second verb besides agg: resample("ME").transform("mean") returns one row per day, each carrying its month’s mean, so that you can subtract it from the daily value. agg collapses; transform broadcasts. The same pair reappears with groupby in Chapter 2.
The monthly means for January, February and March 2015 are 45.51, 43.08 and 42.13, labelled 31 January, 28 February and 31 March. There are 38 months and 13 quarters; the lower quartile of the first quarter’s closes is 42.01; the first two two-month sums are 910.28 and 1 745.39 (the first covers one month only — another partial bin). The first three weekly counts are 1, 5, 5; the weekly closes are labelled by Sundays, 46.76 for the week ending 4 January and 47.19 for the week ending the 11th; there are 163 weeks and none is empty.
transform returns 779 rows against agg’s 38. The first three January 2018 closes sit 4.13, 3.73 and 2.97 below that month’s mean. In the figure the step line is the monthly mean broadcast back to every day, and the 20-day rolling mean is its smooth cousin — one steps at month boundaries, the other slides.
Intraday bars and up-sampling
The same machinery works at any frequency. A US trading session runs from 09:30 to 16:00; the course file holds Apple’s 390 one-minute bars for 2 November 2020, stamped 09:31 through 16:00. resample("5min").ohlc() rebuilds five-minute candlesticks — open, high, low and close of each bin — and resample("1h").sum() on the volume column gives hourly turnover. Predict how many five-minute bars 390 minutes produce before you run the cell; the answer is not 78.
Up-sampling is the reverse and adds no information, only rows. resample("1min").asfreq() places three five-minute closes on a one-minute grid and leaves the eight new minutes empty; ffill() holds the last known value — what a trader knew at each minute — and interpolate() draws straight lines between the known points — what a chart wants and what nobody knew.
Bins are anchored on the clock at 09:30, 09:35, … Given bars stamped 09:31 through 16:00, how many five-minute bins are non-empty?
Not 78 but 79. The bins are anchored on the clock, so the first bar, labelled 09:30, holds only the 09:31–09:34 prints, and the 16:00 print sits alone in a bar of its own at the end: the last row shows open, high, low and close all equal to 108.78. The first bar opened at 109.58 and closed at 110.11; hourly volume in the first three hours was 16.1, 22.0 and 15.4 million shares. Three five-minute closes become eleven one-minute rows, eight of them NaN; ffill repeats 110.11 for five minutes and then jumps to 110.26, while interpolate climbs 110.11, 110.14, 110.17, 110.20, 110.23, 110.26 in equal steps.
Plotting a Series
There are two ways to draw a Series: hand its values to matplotlib directly, or call the .plot family that every Series carries — plot.line, plot.hist, plot.kde, plot.box, plot.bar, plot.barh, plot.pie. pandas reads the index as the x-axis and the values as the y-axis and passes everything else (figsize, color, title, ax) to matplotlib. fig.add_subplot(rows, cols, n) places panel \(n\) on a grid, and since every Series.plot.* accepts ax=, pandas draws into the panel you chose.
One histogram argument deserves explanation. With density=True the bar heights are rescaled so that the total area of the bars is 1 — each bar’s area is the fraction of observations in that bin. Raw counts depend on the sample size and the bin width; densities are comparable across samples and can be overlaid on a theoretical probability density function, which is what the kernel density estimate (plot.kde) in the second panel is. The lecture notebook opens its plotting section with 5 000 draws from a normal distribution with mean 10 and standard deviation 5 — the one place in this chapter where the data are simulated — and the first panel reproduces it with a fixed seed.
The bar areas sum to exactly 1.0; the simulated sample has mean 9.985 and standard deviation 4.966, within sampling error of 10 and 5. The middle panel is the picture of fat tails: the kernel density over the return histogram has a sharper peak and longer tails than a normal curve of the same variance, and the numbers agree — excess kurtosis 10.77 against 0 for a Gaussian, with a mild positive skew of 0.49. Chapter 2 tests this formally and Chapter 5 builds a regression that tolerates it. The right panel is the 2015–2018 price, the same Series plt.plot drew earlier in three panels’ worth of code.
For a categorical Series the recipe is value_counts() followed by plot.bar(), plot.barh() or plot.pie(). Prefer bars: a pie chart hides that the two largest sectors are almost tied.
Eleven sectors; Healthcare (500) and Technology (486) lead, then Financial Services (447). The horizontal bars show the 14-company gap at a glance; the pie shows two wedges of nearly the same size and asks you to guess.
shift(-1) puts tomorrow’s price on today’s row; its last entry is NaN because tomorrow is unknown. A model given it as an input has been shown the answer — look-ahead bias — and its backtest will be spectacular and worthless. The same sin, in a different costume, is computing drawdown with max() instead of cummax().
The product of gross returns telescopes: \(\prod_t P_t / P_{t-1} = P_T / P_0\), the growth of one dollar — 1.882 for Microsoft 2015–18, exactly the last close over the first. The cumulative sum of simple returns (0.711) ignores compounding and is not a quantity anyone earns.
Bins are anchored on the clock (09:30, 09:35, …). The first bin holds only 09:31–09:34, and the 16:00 print falls alone into a bin of its own at the end. Partial first and last bins are the rule for every resample — check count() on both before trusting the aggregate.
Chapter Wrap-up
You now own the object that every later chapter feeds into a model, and you own it at the level of mechanism rather than recipe. A Series is values plus an index plus one dtype. Arithmetic and comparisons are vectorised and align on the index before they operate, so unmatched labels become NaN and integers become floats; .add(fill_value=) and reindex are the deliberate forms of that alignment. NaN is skipped by aggregations and propagated by element-wise operations — the same missing value, two behaviours — and isna().mean() is the missing rate. std() divides by \(n-1\) unless told otherwise, and the difference is 11.8 % at \(n=5\). where, np.where, clip, rank, replace and pd.cut replace loops; apply is a loop in disguise. .loc[a:b] includes b, .iloc[i:j] excludes j, bare brackets guess, and writing through chained brackets is discarded. shift builds returns, rolling builds moving averages and realised volatility, cumprod builds an equity curve, cummax builds a drawdown, and resample changes the clock with an aggregation that must match what the number means.
The professional habits are shorter still. Print len() and isna().sum() after every binary operation and every filter. Print the first and last bin after every resample. Test any slicing rule you are unsure of on a five-element Series before trusting it on a backtest. Never let shift(-1) or a global max() see the future. And when an AI copilot hands you apply(lambda ...), ask it for the vectorised version and for pandas 2.2 spellings — "ME" not "M", .ffill() not fillna(method=...).
Chapter 2 puts many Series side by side. A DataFrame has two axes, and every method here reappears with an axis= argument; alignment happens on rows and columns; groupby generalises resample to any key; pivot, melt and concat reshape tables; and the second half of the chapter computes on those tables the statistics this chapter only hinted at — distributions, hypothesis tests for the weekend effect, association, and the extreme-value theory that gives the fat tails in the Microsoft histogram their own model.
The companion slide deck for this chapter, with predict-then-run exercises for every cell above, is at statpython.pages.dev.
← Chapter 0: Basics of Python · Contents · Chapter 2: DataFrames →