Chapter 2: DataFrames

Data analysis in the real world looks nothing like a clean textbook exercise. A McKinsey survey of data scientists found that 80% of their working time is spent cleaning, reshaping, and validating data — not building models. Before any machine learning algorithm runs, before any chart gets drawn, before any insight can be communicated, someone has to wrestle raw data into a usable shape. That someone is you, and the primary weapon is the pandas DataFrame.

A brief history: from relational databases to DataFrames

The conceptual foundation for tabular data was laid in 1970 when Edgar Codd, a computer scientist at IBM, published “A Relational Model of Data for Large Shared Data Banks.” Codd’s insight was deceptively simple: organize data as tables (relations) where rows are observations and columns are attributes. From that paper came SQL, the language that powered every enterprise database for the next fifty years — Oracle, MySQL, PostgreSQL, SQL Server. If you have ever heard of a “database schema,” a “primary key,” or a “JOIN,” you are living inside Codd’s 1970 idea.

SQL is powerful, but it requires a database server, a connection, and a query language separate from your analytical code. In the early 2000s, Wes McKinney was a financial analyst tired of context-switching between Excel and statistical tools. In 2008, he released pandas, a Python library built around a single central object: the DataFrame. A DataFrame is a relational table — rows are observations, columns are variables — but it lives inside your Python program, responds to Python syntax, and integrates directly with NumPy, matplotlib, scikit-learn, and every other tool in the data science ecosystem.

The killer feature was making DataFrames first-class Python objects that support mixed data types (numbers, strings, dates, booleans in the same table), carry column names and row labels, handle missing values natively, and compute element-wise arithmetic without explicit loops. SQL does roughly the same things, but with pandas you write df['Profit'] = df['Revenue'] - df['Cost'] instead of a SELECT statement — and the result is immediately available for the next line of analysis.

Why pandas conquered analytics

Three properties sealed the victory. First, mixed types: a single DataFrame can hold dates, categorical strings, floats, and integers simultaneously, which no NumPy array can do natively. Second, the labeled index: every row and column has a name, so df.loc['2024-01-15', 'riders_thousands'] is readable by a human and unambiguous to the machine. Third, time-awareness: date and time indices unlock resampling, rolling windows, lag operators, and date-range slicing — operations that are painful in SQL and trivial in pandas.

Today, pandas is the lingua franca of data analysis in Python. Every major data science library — scikit-learn, statsmodels, seaborn, plotly, XGBoost, PyTorch Lightning — accepts or returns a DataFrame. Learning pandas fluently is not optional for a data analyst; it is the entry ticket.

The data-to-insight pipeline

Every analysis, from a one-page sales report to a production machine learning system, follows the same six-step pipeline:

Load — pull data from CSV, Excel, a SQL database, or an API.
Explore — understand shape, types, and basic statistics.
Clean — handle missing values, fix dtypes, remove duplicates.
Transform — compute new features, reshape, join tables.
Analyze — run statistics, models, or aggregations.
Visualize — communicate findings with charts.

This chapter covers every step. You will learn to load and explore DataFrames, select and filter rows and columns, engineer new features, handle missing data, plot results, standardize variables, and work with time series. These are the exact operations used daily by analysts at Cathay Pacific, Google, Airbnb, and the operations teams of every major retailer and transit authority.

Why this chapter is weighted at 40% of the course

The skills in this chapter appear in every subsequent topic. Linear regression (Chapter 3) requires feature engineering and train/test splitting — both covered here. Clustering (Chapter 4) requires standardization and plotting — both here. If you are shaky on DataFrames, every downstream technique becomes harder. Invest the time now: every hour here pays back ten hours later.

Loading and Exploring Data
Selecting Data
Creating and Modifying Data — and Method Chaining
Key Statistical Methods
Handling Missing Values
Plotting with Pandas
Standardization
Time Series Methods

Loading and Exploring

Loading and Exploring Data

Background: where does business data live?

Every real-world dataset starts its life somewhere outside Python. It may be a CSV exported from an ERP system (SAP, Oracle), a table in a MySQL or PostgreSQL database, a REST API response from Stripe or Salesforce, a spreadsheet emailed by a client, or a parquet file sitting in AWS S3. The first step of every analysis is always the same: get the data into a DataFrame.

pd.read_csv() handles the most common case. For databases, pd.read_sql() accepts a SQL query and a connection. For Excel files, pd.read_excel(). For JSON, pd.read_json(). pandas abstracts away the source; once the data is loaded, all subsequent operations are identical regardless of where it came from.

After loading, the very first thing a disciplined analyst does is look at the data. This sounds obvious, but it is routinely skipped — at great cost. Missing values, wrong dtypes, date columns stored as strings, price columns with currency symbols, columns with 90% zeros: none of these show up until you look. The habits of .head(), .shape, .dtypes, and .describe() are not bureaucratic rituals; they are the fastest path to catching problems before they corrupt your results.

Data quality has four dimensions a good analyst checks: completeness (are values missing?), consistency (does the same entity appear with the same label everywhere?), accuracy (are the values plausible — is a human age of 300 in the data?), and timeliness (is the data fresh enough for the question being asked?). None of these can be assessed without first loading and exploring.

In this section you will practice loading a small public-transit ridership dataset and running the standard first-look commands. By the end, these five lines — df.head(), df.tail(), df.shape, df.dtypes, df.describe() — should be as automatic as saving a file.

The DataFrame Object

A DataFrame is the central data structure of pandas, and the easiest way to picture it is as an Excel spreadsheet that has been promoted into a Python object. It has rows, it has named columns, and it has an index that labels each row. The defining property — the one that gives pandas almost all of its expressive power — is that each column is itself a fully-functional pandas Series, a one-dimensional labeled array that knows its own name, its dtype, and its position within the parent table. When you reach for a column, you are not reaching for a list of numbers; you are reaching for a Series that carries its own metadata and methods.

A DataFrame is most commonly born by reading data from disk or from the network, or by being constructed in memory from a dictionary of columns. The two snippets below show the standard patterns: the first reads a local CSV; the second builds a small ridership table in memory and tells pandas to interpret the Date column as a proper date and to use it as the row index. The parse_dates and index_col arguments — and the equivalent set_index("Date") call on a constructed frame — do a great deal of quiet work: they convert a string column into a DatetimeIndex, which in turn unlocks date-range slicing, resampling, and every other time-aware operation introduced later in this chapter.

import pandas as pd
import numpy as np

# Load from a local CSV file
df = pd.read_csv("data.csv")

# Build a small ridership DataFrame in memory (used throughout this chapter)
rng = np.random.default_rng(7)
dates = pd.date_range("2024-01-01", periods=200, freq="D")
weekday_name = dates.day_name()
is_weekend = dates.dayofweek >= 5
riders = np.where(is_weekend, 3000, 5000) + rng.normal(0, 200, 200)
df = pd.DataFrame({
    "Date": dates,
    "weekday_name": weekday_name,
    "riders_thousands": riders.round(0),
    "rain_mm": rng.exponential(2.0, 200).round(1),
    "is_holiday": rng.random(200) < 0.05,
}).set_index("Date")

A small schema illustration helps fix the mental picture. The table below shows three days of MTR-style ridership data. The leftmost column is the index — the row labels, which here are dates. Each remaining column is a named variable that, taken on its own, forms a one-dimensional pandas Series.

Index	weekday_name	riders_thousands	rain_mm	is_holiday
2024-01-01	Monday	5012	0.4	True
2024-01-02	Tuesday	4987	1.8	False
2024-01-06	Saturday	3041	3.2	False

Rows correspond to observations, and columns correspond to variables. Throughout this book the rows-as-observations convention is sacred: when the layout looks reversed, the first step is always to transpose.

A First Look at the Data

Loading a new dataset without inspecting it is the analytical equivalent of signing a contract without reading it. Four methods, run in sequence, give an honest first impression of any DataFrame. The cell below builds the ridership table introduced above and runs all four — the first five rows, the shape as a (rows, columns) tuple, the dtype of each column, and the full column index. Together they take less than a second to execute and answer a surprising number of practical questions before any analysis begins.

The first step of every analysis is to load the data and then ask three questions: what does it look like, how big is it, and what type is each column. The trio of .head(), .shape, and .dtypes answers all three. Skipping this ritual is the single most common source of bugs in applied data work.

Computing Summary Statistics

The eight rows that .describe() produces are not arbitrary — each one answers a specific diagnostic question. count reports the number of non-missing observations and is the first place to look for hidden missing data. mean and std together summarise centre and spread, the workhorses of any first-pass summary. min and max are the extremes — the cheapest possible outlier check. The quartiles at the 25th, 50th, and 75th percentiles describe the shape of the distribution, with the 50th percentile (the median) being a more robust measure of central tendency than the mean whenever the data is skewed.

Interpreting .describe() output: The output from this cell tells a rich story about the data’s distribution. count tells you immediately if there are missing values — if it is less than the total number of rows, some observations are NaN. The mean and std together let you check plausibility: for an MTR-style daily ridership series with a mix of weekdays and weekends, a mean near 4,400 thousand and a standard deviation near 900 thousand is entirely consistent with a weekday baseline of ~5 million riders and a weekend baseline of ~3 million. The 25th, 50th, and 75th percentiles reveal shape: a large gap between the 25th and 75th percentile signals a bimodal distribution — exactly what you expect when weekdays and weekends are pooled. The min and max are your outlier flags — if the minimum ridership is negative or the maximum is ten times the mean, something went wrong at data entry. Get in the habit of reading these numbers before writing a single line of analysis code.

Common pitfall: dtype inference

pd.read_csv() guesses column types automatically. A price column with one bad row containing "N/A" (as a string) will be read as object (text) instead of float64, causing every arithmetic operation to fail silently or raise an error. Always check df.dtypes after loading and explicitly cast suspicious columns: df['Price'] = pd.to_numeric(df['Price'], errors='coerce').

Exercise: Load and Explore

Exercise

Load the ridership data and answer:

How many rows and columns?
What are the column names?
What is the average daily ridership (in thousands)?
What is the maximum daily rainfall recorded?

Hint: df.shape, df.columns, df['riders_thousands'].mean(), df['rain_mm'].max()

In practice

At a transit authority or a logistics operator, the first thing an analyst does with any new data feed is run .describe() on every numeric column and .value_counts() on every categorical column. This “data quality pass” often takes 20–30 minutes and catches problems that would otherwise corrupt weeks of capacity-planning work. At MTR’s operations planning unit, data cleaning consumes more engineering time than model development. The same discipline applies to any analytics role — from e-commerce dashboards at Shopify to ridership reports at a metro operator.

Selecting and Filtering

Selecting Data

Background: selection as relational algebra

The theory behind selecting subsets of data was formalized by Codd in the same 1970 paper that gave us relational databases. He called the two fundamental operations projection (selecting columns) and selection (filtering rows). In SQL you write SELECT col1, col2 FROM table WHERE condition. In pandas the equivalent is df[['col1', 'col2']] for projection and df[df['col'] > threshold] for selection. The ideas are identical; the syntax differs.

Understanding this duality matters because it frames the why behind pandas syntax choices. df['riders_thousands'] with single brackets returns a Series — a single column vector — because you projected one dimension. df[['riders_thousands', 'rain_mm']] with double brackets returns a DataFrame — a two-dimensional table — because you projected multiple columns. The extra set of brackets is not a quirk; it is the distinction between extracting one attribute (a list) versus keeping a table structure intact.

Row selection comes in two flavors that correspond to different mental models. .loc[] (label-based) says “give me rows where the index label is X” — this is how humans naturally think about data: “give me January 2020 data,” “give me the row for Apple.” .iloc[] (integer-position-based) says “give me row number N” — this is how a computer thinks about data. When your index is a date or a company ticker, .loc[] produces readable code; .iloc[] is best reserved for positional slicing like train/test splits.

In real-world analytics, filtering rows is how you build customer cohorts (users in a certain age group, with above-median spending, who churned in Q3), event studies (market days with returns below −2%), and compliance reports (transactions above regulatory thresholds). Mastering boolean indexing is mastering the language of data segmentation.

Select One Column — Returns a Series

The simplest projection extracts a single column by name using square brackets. The result is a Series — a one-dimensional labeled array — not a DataFrame. This distinction will become important in a moment, because pandas treats Series and DataFrames differently in many subtle ways, and reaching for the wrong one is a frequent source of confusion. In business language, this is the operation behind a request like “show me only the Revenue column”: one named attribute, extracted in isolation, ready for further calculation.

Interpretation: When you extract a single column, you get a pandas Series — a one-dimensional labeled array. The output shows both the data values and the row index (integers by default). Notice that the dtype shown at the bottom is int64 — the column preserves its type even when extracted. This matters because all Series methods (.mean(), .std(), .plot()) now work directly on riders without referencing the original DataFrame. In production code, extracting the column you need early and working with it as a named Series makes your code more readable and your intent clearer to colleagues reviewing your work.

Select Multiple Columns — Returns a DataFrame

Asking for several columns at once uses double brackets — the outer brackets are pandas’ indexing operator, and the inner brackets form the list of column names being requested. The result preserves the two-dimensional structure: a DataFrame with the same row index but only the selected columns. The two-bracket syntax is not stylistic decoration; it is the difference between extracting a single attribute (a Series) and extracting a sub-table (a DataFrame). Single brackets around a string returns a Series, while double brackets around a list of strings returns a DataFrame.

Selecting Rows by Position

Row selection by position is the integer-indexed cousin of column selection. The convenience methods .head(n) and .tail(n) return the first or last n rows respectively, and .iloc[] accepts ordinary Python slice syntax — including negative indices, just like a list. Positional selection is the right tool when the row number itself is meaningful (the first hundred observations, the last three rows for inspection) or when the index is not informative. When the index carries semantic meaning, such as a date or a ticker, label-based selection introduced shortly is almost always more readable.

The DataFrame holds the integers 100 to 119. Before running, predict which riders_thousands values df.iloc[0:5] returns and which values df.iloc[-3:] returns.

Conditional Selection — Filtering Rows

The most common form of row selection in real work is conditional: keep only the rows where some predicate is true. Pandas implements this through boolean masking. The expression df['Return'] < -2 is evaluated row by row and produces a Series of True / False values — a mask. When that mask is fed back into the DataFrame’s indexer, only the rows where the mask is True survive. The two-step pattern reads naturally as “compute a per-row condition, then keep the rows where the condition holds.”

Interpretation: The drop-day filter implements exactly the kind of event study a transit-planning analyst runs routinely. Calling len(drop_days) tells you the frequency of unusual disruption events. If most weekday-to-weekday changes are within ±1%, a drop of more than 2% is genuinely unusual and worth investigating: a typhoon signal, a station closure, a major public event, or a recording error. This single filter is the starting point for questions like: “Do drop days cluster? Do they follow heavy rainfall? Does ridership recover the next day or stay depressed?”

Common pitfall: SettingWithCopyWarning

When you filter a DataFrame and then try to modify the result, pandas may issue a SettingWithCopyWarning. This happens because the filtered result might be a view of the original data, not an independent copy. To be safe, call .copy() explicitly: crash_days = df[df['Return'] < -2].copy(). After that, modifying crash_days will never affect df.

Multiple Conditions

Compound conditions combine simple boolean masks using the bitwise operators & for AND and | for OR. The choice of operator is deliberate: Python’s keyword and and or cannot be overloaded to behave element-wise on pandas objects, so pandas uses the bitwise operators instead. Each individual condition must be wrapped in parentheses, because the bitwise operators have higher precedence than the comparison operators, and omitting the parentheses silently produces the wrong mask. The cell below shows the two canonical patterns: an intersection (a sharp ridership dip on a rainy day) and a union (any unusually wet or unusually dry day).

Exercise

Find customers with income above median AND spending above $500.

Hint: df[(df['Income'] > df['Income'].median()) & (df['Spending'] > 500)]

Selecting Rows by Label

Label-based selection is provided by the .loc[] accessor. Where .iloc[] thinks in terms of row numbers, .loc[] thinks in terms of the labels that pandas attaches to each row. When the index is a DatetimeIndex, those labels are dates, and .loc[] accepts dates and date ranges directly. When the index is a list of company tickers or customer IDs, .loc[] accepts those instead. The accessor takes either a single argument (row labels only) or a pair separated by a comma (row labels and column labels), which together select an arbitrary rectangular subset of the table.

The two accessors look superficially similar but carry different mental models. .loc[] selects by label name; .iloc[] selects by integer position. When the index is a date, the call df.loc['2020-01':'2020-03'] reads as plain English and remains correct even if rows are added, dropped, or reordered. The positional equivalent df.iloc[0:65] is brittle and silently breaks the moment the underlying data changes shape.

Interpretation: This code block introduces the two-dimensional nature of .loc[] and .iloc[]. The key insight is that both accept [row_selector, column_selector] as a pair. With .loc[], df.loc['2020-01-02', 'riders_thousands'] reads as “row labeled 2020-01-02, column labeled riders_thousands” — it is self-documenting. With .iloc[], df.iloc[0, 2] means “first row, third column” — fast, but opaque. In production data pipelines, .loc[] is almost always preferred because the code remains meaningful even when the underlying data is reordered or filtered. The date-range slicing df.loc['2020-01-02':'2020-01-03'] is a particularly powerful pattern that becomes invaluable when working with years of operational, web, or sensor data.

In practice

At a transit-planning team, an analyst routinely writes summer_demand = ridership_df.loc['2023-06-01':'2023-08-31', 'riders_thousands'].mean() to extract exactly the seasonal window needed for capacity planning. Without a proper datetime index and .loc[], this would require explicit row-number arithmetic that breaks the moment the dataset is updated or extended. Date-labeled indexing is one of the most pragmatically valuable features in pandas for anyone working with operational, sensor, or any time-stamped business data.

Comparing .loc and .iloc

The two accessors disagree on one important detail: the treatment of slice endpoints. .loc[] is inclusive on both ends, so a slice from label 'b' to label 'd' returns the three rows b, c, and d. .iloc[] follows Python’s standard slicing convention and treats the upper bound as exclusive, so iloc[1:3] returns only the two rows at positions 1 and 2. The asymmetry is deliberate — labels do not have a natural notion of “one past the end,” so .loc[] matches what a human would mean by a closed range, while .iloc[] stays consistent with the rest of Python.

How many rows will df_demo.loc['b':'d'] print, and how many will df_demo.iloc[1:3] print? Commit to a number for each before running.

Interpretation: This asymmetry trips up almost every new pandas user at least once. The reasoning behind it is pragmatic: .loc[] uses index labels, and when you write a date range like '2020-01-01':'2020-12-31' you naturally mean including December 31st. .iloc[] inherits Python’s slice convention (a[1:3] gives positions 1 and 2) to stay consistent with the rest of the language. The practical rule: when in doubt, run the code on a small DataFrame like this one and count the rows returned. Debugging a fence-post error on toy data takes ten seconds; debugging it inside a 500-line pipeline can take hours.

A Worked Example: Shopee Sale-Day Baskets

Every analyst eventually loads a transactional dataset — purchases, clicks, page-views, support tickets. The mechanics are always the same: a wide table with one row per event, a handful of categorical columns, one or two numeric columns that matter, and a timestamp. To practise the DataFrame methods you have just seen on something culturally familiar, here is a simulated Shopee/Lazada Sale-Day “11.11” basket. The numbers are synthetic but the shape is what you will see when you join your first analytics team.

Inspect the top of the table:

Count baskets per category:

Mean basket value by category:

Average spend when a coupon was used:

Interpretation: Electronics carries the highest mean basket value, which is unsurprising — phones and laptops have high unit prices and are the headline categories of any 11.11 campaign. Beauty comes second, and notably the Beauty rows also concentrate the returning-customer flag and the coupon-used flag. Coupon-driven baskets are substantially larger than the overall average, suggesting that coupons are being redeemed on already high-intent customers rather than recruiting new ones. That is the kind of read an analyst writes up in a memo to the marketing director — which is exactly what the next section asks you to do.

Train/Test Split for Time Series

For time-series data we split chronologically: the earliest observations train the model, the most recent observations test it. The diagram below illustrates the 80/20 convention used throughout this course.

The rule for time-series data is absolute: never shuffle before splitting. The whole point of an out-of-sample test is to ask whether the model can predict the future from the past, and shuffling collapses that distinction by mixing future observations into the training set. A model trained on shuffled time-series data has effectively seen the future, and any performance number it produces is a function of that leakage rather than of genuine predictive skill.

Background: a brief history of the train/test split

The practice of holding out data for evaluation predates machine learning. Stone (1974) formalized cross-validation as a general method for model selection — the idea that you should assess a model on data it has never seen during fitting. In time-series settings, there is an additional constraint: the train and test sets must respect the arrow of time. If you train on data from 2015–2024 and test on data from 2010, your model has effectively seen the future — a form of data leakage that produces wildly optimistic performance estimates.

The 80/20 split is a rule of thumb. In practice, the split ratio depends on dataset size (more data → you can afford a larger test set), the stability of the underlying process (rapidly changing systems require more recent test data), and computational constraints. Cross-sectional data (companies, customers, products at a single point in time) has no temporal structure, so random shuffling before splitting is not only safe but required to avoid accidental stratification.

Common pitfall: time-series shuffle leakage

For time-series data, never use df.sample(frac=1) before splitting. If you shuffle daily ridership data and use 2024 rows in training while predicting 2023 rows in the test set, your model has “seen the future.” It will appear to predict perfectly — but only because it memorized outcomes from beyond the test period. In forecasting, this is called look-ahead bias and it is the most common cause of pilot models that look great on paper but fail when deployed.

Train/Test Split for Cross-Sectional Data

Cross-sectional data has no temporal structure: each row is an observation at a single point in time, and one row is not “before” another in any meaningful sense. The risk that motivated the no-shuffle rule for time series — leaking the future into the training set — simply does not apply. The opposite risk applies instead: if the rows happen to be sorted by some variable correlated with the outcome (alphabetical by name, ascending by size), an unshuffled split will produce a training set and a test set that look systematically different. The remedy is to shuffle before splitting and to fix the random_state so the shuffle is reproducible.

The two-line idiom df.sample(frac=1, random_state=42) followed by an .iloc[] split is the cross-sectional analogue of the chronological split used for time series. The frac=1 argument requests a full-length sample (every row, in a new random order), and the random_state argument seeds the pseudo-random generator so the same shuffle is produced every time the notebook runs — a non-negotiable property for reproducible analysis.

Transforming Data and Statistics

Creating Columns and Method Chaining

Background: feature engineering as the source of most ML wins

“Coming up with features is difficult, time-consuming, requires expert knowledge. Applied machine learning is basically feature engineering.” This observation — widely attributed to Andrew Ng, co-founder of Google Brain — captures something important: the quality of a model’s inputs matters more than the sophistication of the algorithm. A simple linear regression on well-engineered features frequently outperforms a deep neural network on raw inputs. The pandas operations in this section — creating computed columns, building categorical indicators, and chaining transformations — are the core tools of feature engineering.

In practice, most features are derived from existing data by combining columns arithmetically, binning continuous variables into categories, encoding time-based patterns, or applying domain-specific transformations. Examples from real pipelines: a retail company adds Margin = (Revenue - Cost) / Revenue * 100 to identify underperforming products; a bank adds Debt_to_Income = TotalDebt / AnnualIncome to predict default risk; a transit operator adds riders_pct_change_5d = riders_thousands.pct_change(5) to flag this week’s anomalies against last week. None of these features exist in the raw data — they are created by the analyst.

One subtle but important cost of in-place modification (df['col'] = value) is that it mutates the original DataFrame permanently within the notebook session. This is usually what you want, but in complex pipelines where the same DataFrame is used in multiple branches, it can introduce hard-to-trace bugs. A useful discipline is to build a transformation pipeline that always starts from the raw DataFrame and produces a clean, enriched copy, rather than accumulating mutations.

Method chaining, popularized in the R world by Hadley Wickham’s tidyverse (%>% pipe operator), was imported into pandas partly in response to user demand for more readable pipeline syntax. The pandas .pipe() method and the natural chaining of methods like .pct_change().dropna().sort_values().head() achieve the same goal: expressing a multi-step transformation as a single readable statement that mirrors the logical order of the operations.

Add a New Column

The fastest way to enrich a DataFrame is to write to a new column name. The assignment df['headway_minutes'] = df['gap_end'] - df['gap_start'] creates the column if it does not yet exist and broadcasts the arithmetic across all rows simultaneously. Because each column is a Series, every standard arithmetic operator works element-wise without any explicit loop, and the resulting Series is stored back into the parent DataFrame under the new name. The same pattern produces business features as readily as operational ones: a profit column from revenue minus cost, a margin column from profit divided by revenue, a debt-to-income ratio from two existing fields.

Interpretation: The daily_swing = peak_riders - offpeak_riders column quantifies how lumpy demand is on each day — how much the network has to ramp capacity up at peak vs. off-peak. A wide swing on a day with a modest overall total signals concentrated rush-hour pressure that smoothed out by evening — useful for crew scheduling and rolling-stock allocation. The avg_riders = (peak + offpeak) / 2 is a naive estimate of the average load across the operating day. Notice that both columns are computed in a single assignment without any loop — pandas broadcasts the arithmetic across all rows simultaneously, which is both faster and more readable than a Python for loop over rows.

In practice

In customer analytics at companies like Netflix or Spotify, feature engineering columns are created from raw event logs. Common examples: days_since_last_login = today - df['last_login_date'], avg_session_length = df['total_watch_time'] / df['session_count'], is_premium = df['plan_type'] == 'Premium'. These derived columns feed directly into churn prediction models, recommendation systems, and A/B test analyses.

Conditional Column

Categorical columns derived from numerical ones are usually produced with a list comprehension that walks down the source Series and emits a label for each element. The pattern reads naturally as a sentence: “for each day-over-day change, emit Up if positive, else Down.” Multi-class labels are produced by chaining if/else expressions, with the order of clauses matching the order of thresholds. This kind of derived column is the manual analogue of supervised labeling — the analyst encodes domain knowledge into a rule, and the resulting column is then either used directly in reporting or held out as the target variable for a downstream machine-learning model.

Interpretation: The service_alert column produced here is a four-class operational label: Surge / Normal Up / Soft Day / Disruption. In a supervised machine learning pipeline, this is exactly the kind of target variable you would construct from historical day-over-day ridership changes and then try to predict using features available before the day starts (weather forecast, day-of-week, calendar holidays, public-event schedule). The multi-level if-elif structure in the list comprehension implements a threshold-based rule — a baseline classifier against which more sophisticated ML models are compared. If your model can’t beat this simple rule, it isn’t learning anything useful.

Drop Columns and Rows

The .drop() method removes columns by name when called with columns=, accepting either a single string or a list. Critically, .drop() returns a new DataFrame and leaves the original untouched, which is the pandas default for almost every modifying operation. The optional inplace=True argument mutates the original instead, but its use is discouraged because it short-circuits the natural pipeline style — chained methods cannot follow an in-place call. The general convention in modern pandas is to assign the result back: df = df.drop(columns=[...]).

Interpretation: Dropping columns is housekeeping that makes downstream code faster and more readable. An operational dataset loaded from a data warehouse might have 80 columns; a particular analysis may use 5. Dropping the irrelevant 75 reduces memory usage and eliminates the risk of accidentally using a correlated feature you did not intend to include. Note that df.drop() returns a new DataFrame without modifying df — this is the pandas default (immutable operations). If you see code using inplace=True, be cautious: it mutates the original DataFrame, which can cause confusing bugs when the same DataFrame is referenced elsewhere in the notebook.

Common pitfall: df[col] == X vs df.query() vs df.loc[] performance

For large DataFrames (millions of rows), the three selection methods have different performance profiles. df[df['col'] == value] is the most common but builds a boolean array over the entire column. df.query("col == value") often runs faster because it uses numexpr under the hood for numeric expressions. df.loc[df['col'] == value] is essentially the same as the bracket form. For most course-scale datasets the difference is negligible, but in production with 10M+ rows, query() can be 2–5x faster on numeric conditions.

Method Chaining — Readable Pipelines

Almost every pandas method returns a Series or DataFrame, which means the next method can be called immediately on the result without ever assigning to a temporary variable. The chained form reads as a sentence describing the pipeline from left to right — “take ridership, compute day-over-day change, drop NaN, sort descending, show top five” — and the unchained form requires the reader to mentally reconstruct that sentence from a sequence of overwritten temporaries. Wrapping the chain in parentheses allows the expression to span multiple lines, which is the conventional style for any chain longer than two or three operations.

Key Statistical Methods

Background: what descriptive statistics actually tell you

Summary statistics are the first language of data. Before building any model, a skilled analyst spends time with the numbers: not just computing them mechanically, but asking what each one reveals about the underlying process that generated the data.

The mean is the balance point of a distribution — but it is only meaningful when the distribution is symmetric and has no extreme outliers. The mean income in a room with 99 average workers and one billionaire will suggest everyone is wealthy. The median (50th percentile) is far more robust: it is the middle value regardless of how extreme the tails are. When mean and median diverge significantly, the distribution is skewed — a signal that outliers are distorting the average, and that median-based statistics are more informative for that variable.

The standard deviation measures spread around the mean. In operations analytics, standard deviation of day-over-day demand is the primary measure of demand volatility — the predictability of an operation. A ridership series with a daily-change std of 2% is far more predictable than one with a std of 10%, even if both have the same average level. Demand volatility feeds directly into how much slack capacity a system needs: noisier demand requires larger safety buffers, just as a more volatile return series requires a larger risk capital reserve.

The percentiles (25th, 50th, 75th) together with min and max paint a picture of the full distribution. The interquartile range (IQR = Q3 − Q1) is a robust spread measure used to detect outliers: values more than 1.5 × IQR below Q1 or above Q3 are flagged as potential outliers by the standard box-plot rule.

Correlation measures linear co-movement between two variables. A value near +1 means they move together; near −1 means they move in opposite directions; near 0 means no linear relationship. Correlation does not imply causation — two variables can be perfectly correlated simply because both are driven by a third variable (confounding).

Column Statistics

Every numerical column in a DataFrame is a Series, and every Series has direct access to the standard summary methods — .mean(), .std(), .min(), .max(), .median(), .sum(). Calling them on a single column returns a scalar; calling them on a multi-column DataFrame returns a Series of one value per column. Sorting by a column with .sort_values() and then taking the head or tail is the canonical idiom for identifying the largest and smallest observations, which is almost always the first thing an analyst wants to see after computing the average.

Identifying the busiest and quietest days, or the largest and smallest customers, or the loudest and quietest observations, is one of the first things any analyst does when studying a new dataset. The sorted-head pattern is exactly that diagnostic in one line.

Interpretation: The .sort_values('riders_thousands', ascending=False).head(5) output shows the five busiest days in the simulated dataset. In real ridership data, the busiest single days often cluster around predictable events — the day after a typhoon (commuters making up missed trips), major holiday returns, or large public events at adjacent venues. This clustering pattern — that extreme days come in bunches — is a property of operational data with analogues in many domains, and it has consequences for capacity planning. A naive model that removes “outlier” days would discard exactly the observations that matter most for sizing peak capacity.

Understanding `axis` — Rows vs. Columns

The axis argument controls the direction in which an aggregation collapses a DataFrame. The default, axis=0, operates down the table, treating each column as the unit of aggregation and producing one number per column. The alternative, axis=1, operates across the table, treating each row as the unit and producing one number per row. The simplest way to remember the convention is that the axis being aggregated is the axis being collapsed: axis=0 collapses rows, leaving columns; axis=1 collapses columns, leaving rows.

A small picture clarifies the convention. With columns A and B holding the values 10, 30, 50 and 20, 40, 60 respectively, axis=0 produces A = 30, B = 40 (one mean per column), and axis=1 produces 15, 35, 55 (one mean per row).

`axis` in Action

In a realistic station-level table, the default axis behaviour answers the question “what is the typical ridership at each station across all days,” while the axis=1 variant answers “for each day, what is the average across the four stations.” The first is a summary across observations; the second is a per-row aggregation. Every reduction method — sum, std, min, max — accepts the same axis argument and follows the same convention. In practice, the default is what is wanted at least nine times out of ten, and the explicit axis=1 is the special case for per-row calculations such as row-wise averages, sums, or maxima across several columns.

Correlation — Which Variables Move Together?

The .corr() method computes the pairwise Pearson correlation between every numerical column in the DataFrame and returns a symmetric matrix whose diagonal is always 1 (every column is perfectly correlated with itself). Off-diagonal values range from −1 to +1: a value near +1 means the two variables move together, a value near −1 means they move in opposite directions, and a value near zero means there is no linear relationship. The matrix is the starting point for almost every multivariate analysis — from capacity planning (where the correlation matrix of station-level demand drives where to put extra trains) to feature selection (where two near-perfectly-correlated predictors carry redundant information and one should be dropped).

The same operation that produces this station-level correlation matrix can answer commercial questions of identical structure. Is advertising spend correlated with revenue? Is customer tenure correlated with order frequency? Is the unemployment rate correlated with mortgage default rate? In each case, the input is a DataFrame of two or more numerical columns, and the output is a correlation matrix that begins to answer the question while explicitly leaving open the more difficult question of causation.

Interpretation: In this simulated station-level dataset, the correlation between Central, Admiralty, Tsim Sha Tsui and Mong Kok is very high (near 0.99) — this is expected, because all four stations sit on the same network and see the same underlying day-of-week and holiday effects, with the within-day variation small relative to the across-day variation. A correlation of 0.99 between station columns is not informative; it just means the stations rise and fall together over time. However, a high correlation between ridership and rainfall would be much more interesting — it might indicate that rainy days push people onto the metro (a substitution-from-walking signal). The negative correlation between ridership and rainfall in this synthetic data is artificial and not representative of real systems. Always verify that simulated data matches the qualitative properties of the real phenomenon before drawing conclusions.

In practice

Correlation matrices are used routinely in capacity planning and demand forecasting. At a transit authority, the correlation matrix of station-level ridership tells planners which stations move together (and can share rolling stock and crew rosters) and which stations move independently (and need their own dedicated capacity). At a retailer, the correlation matrix of store-level sales drives inventory transfer decisions. Pandas’ .corr() is often the first step in such a pipeline, even if the production system uses more sophisticated estimators that account for non-linear dependence.

Cleaning, Plotting, and Standardization

Handling Missing Values

Background: the statistics of missingness (Rubin 1976)

Missing data is not just an inconvenience — it is a statistical problem with different implications depending on why the data is missing. Donald Rubin (1976) introduced a taxonomy that is still the foundation of missing-data theory:

MCAR (Missing Completely At Random): The probability of a value being missing is unrelated to any variable in the dataset — and unrelated to the missing value itself. Example: a sensor randomly drops 1% of readings due to a hardware glitch. MCAR data is the easiest to handle: you can drop the rows or impute with the mean and the resulting analysis will be unbiased.

MAR (Missing At Random): The probability of missingness depends on other observed variables, but not on the missing value itself. Example: older survey respondents are less likely to answer income questions, but conditional on age, the missingness is random. This is the most common real-world case. MAR data can be handled correctly with model-based imputation (e.g., predict the missing value from the other columns using a regression or k-NN).

MNAR (Missing Not At Random): The probability of missingness depends on the missing value itself. Example: high-income individuals are less likely to report income precisely because it is high. This is the hardest case — any simple imputation introduces bias. MNAR requires domain knowledge and specialized models.

Business implications of each type: In a credit risk model, if low-income applicants disproportionately skip the “assets” field (MNAR), imputing with the mean will overestimate the creditworthiness of the riskiest borrowers. In a compliance report at a bank, missing transaction amounts may indicate fraud (MNAR) rather than data entry errors (MCAR). Knowing why your data is missing changes what you should do about it.

Mean imputation pitfalls: Replacing missing values with the column mean is the most common approach — and also one of the most dangerous when used thoughtlessly. It reduces variance (all imputed values are the same, compressing the distribution), distorts correlations between columns (the imputed rows now have “average” values in one column but real values in another, which breaks the co-movement structure), and is only correct under MCAR. For large fractions of missing data (>10%), or for MNAR data, mean imputation can introduce more bias than simply dropping the rows.

Why Do We Have NaN?

Real-world data arrives with holes in it. Sensors fail, surveys are skipped, records are lost in transmission, fields are not yet populated, joins fail to find a match. Pandas represents each of these absences with a single sentinel value — NaN, for “not a number” — which propagates through arithmetic in a defined way and is detected by a small family of dedicated methods. Understanding how missing values flow through a computation, and choosing deliberately between filling them in and dropping the affected rows, is one of the most consequential skills in applied data work.

Interpretation: df.isna().sum() gives a per-column missing count, which is often the first diagnostic you run. If Sales shows 2 missing out of 5 rows (40%), that is a significant fraction — dropping those rows would lose nearly half the data. If Rating shows 1 missing out of 5 (20%), the choice between filling and dropping depends on how critical the Rating column is for the analysis. df.isna().sum().sum() gives the total NaN count across the entire DataFrame — a quick overall “health check.” A quick way to compute the missing percentage by column is df.isna().mean() * 100, which is often more interpretable than raw counts when tables have hundreds of rows.

Fill Missing Values

The .fillna() method replaces every NaN in a Series or DataFrame with a value of your choosing. The replacement may be a constant (zero is the obvious choice when missingness encodes absence — no orders, no clicks, no events), a measure of central tendency such as the mean or median computed from the non-missing values, or a value derived from neighbouring observations through forward-fill or back-fill. The choice is not cosmetic. Filling with zero in a survey response inflates the count of “low” answers; filling with the mean compresses the variance of the column; forward-filling a price series implicitly assumes the price stayed flat. Each choice encodes an assumption about why the data is missing, and the assumption is part of the analysis whether or not the analyst acknowledges it.

Drop Missing Values

When filling is not appropriate — because there is no defensible imputation, or because the missingness rate is low enough that throwing the rows away costs little — the alternative is to drop them. The .dropna() method removes rows that contain any NaN by default, which is the most aggressive policy. The subset= argument narrows the criterion: drop a row only when one of the listed columns is missing. The narrower form is almost always what is wanted, because it preserves rows whose missing value is in a column irrelevant to the analysis at hand.

Interpretation: df.dropna() with no arguments uses a conservative rule: if any value in a row is NaN, the entire row is removed. With the data above, this drops rows 1, 2, and 4 (indices 1, 2, 4) — three out of five rows, leaving only two clean observations. That is too aggressive here; we lose data that is valid in one column just because the other column is missing. The subset=['Sales'] version is more surgical: drop a row only if Sales specifically is NaN. This preserves row 2 (where Rating is NaN but Sales is valid). The general lesson: always use subset= to specify which columns matter for completeness, rather than applying the all-or-nothing default.

In practice

In regulated reporting — utility billing, transit safety records, healthcare claims — missing values must be handled according to documented guidelines, not analyst preference. At many operators, missing entity identifiers must be flagged and escalated, not imputed. Missing measured quantities may trigger automatic rejection of the record. The pandas tools here are the mechanical implementation; the business decision about what “missing” means is made by domain experts (compliance officers, operations leads), not by the data scientist alone.

The decision between filling and dropping is governed by three considerations. Fill when every row is needed downstream and a defensible estimate of the missing value is available. Drop when the missing rate is small (a rule of thumb is below five percent) and the missingness mechanism appears random. The one option that is never defensible is to ignore the problem — pandas will happily propagate NaN through arithmetic, but the resulting numbers are unreliable, and the downstream model trained on them will be quietly wrong.

`dropna()` — Rows vs. Columns with `axis`

.dropna() accepts the same axis argument as the reduction methods, with the same meaning. The default axis=0 drops rows; axis=1 drops columns. The latter is sometimes useful when a wide table contains columns that are almost entirely empty — a sparse field on a survey, a feature that was only collected for part of the sample. In those cases dropping the column is far less destructive than dropping every row that has a missing value in it. The subset= argument can also be combined with row-mode dropping to consult only specific columns when deciding whether a row is “complete enough” to keep.

Common pitfall: dropna() axis confusion

df.dropna(axis=1) drops columns that contain any NaN — not rows. This is almost never what you want when cleaning row-level records. A common mistake is accidentally dropping columns that have even one missing value in thousands of rows, permanently losing those variables from the analysis. If you want to drop columns that are mostly empty, use df.dropna(axis=1, thresh=int(0.9 * len(df))) — this keeps a column only if at least 90% of its values are present.

Plotting with Pandas

Background: exploratory vs. presentation plots

The discipline of data visualization has two distinct modes, a distinction made famous by John Tukey in his 1977 book Exploratory Data Analysis. Exploratory plots are made quickly, for yourself, to understand the data — they do not need to be beautiful. Presentation plots are made carefully, for an audience, to communicate a specific finding — they need to be clear, labeled, and honest.

Most of the time you spend plotting during analysis is exploratory. You want to know: is this distribution skewed? are there outliers? does this variable trend upward over time? does the relationship between X and Y look linear? Answering these questions takes a single line of pandas code and a glance. Spending thirty minutes polishing the aesthetics of an exploratory plot is wasted time.

When you have a finding worth communicating, the investment in presentation quality pays off. A clear chart with a meaningful title, labeled axes, a legend, and an appropriate scale can convey in three seconds what a table of numbers cannot convey in three minutes. Tukey’s observation — that “the greatest value of a picture is when it forces us to notice what we never expected to see” — applies equally to both modes.

When to use each chart type is a decision that trips up many beginners:

Line chart: one continuous variable over an ordered axis (time). Use for daily ridership, temperature, web traffic, sales trends.
Histogram: distribution of one continuous variable. Use to see shape, skewness, outliers. If day-over-day changes look bell-shaped or fat-tailed, a histogram reveals it.
Bar chart: comparing counts or aggregated values across discrete categories. Use for “sales by region,” “users by plan type,” “count of weekday vs weekend days.”
Box plot: comparing distributions across groups. Shows median, quartiles, and outliers simultaneously. Use when you want to compare the spread of a variable across categories.

Choosing the wrong chart type does not just make the visualization ugly — it can be misleading. A bar chart of means without error bars suppresses information about variance. A line chart connecting unordered categories implies a trend that does not exist.

One Method, Four Chart Types

Pandas ships with a unified plotting interface that wraps matplotlib and exposes most common chart types through a single method, .plot(), with a kind= argument. Calling .plot() on a Series with no arguments produces a line chart; kind='hist' produces a histogram; kind='bar' produces a bar chart from a count or aggregated Series; and kind='box' produces a box plot showing median, quartiles, and outliers. The same method also accepts the usual matplotlib decoration arguments — figsize, title, color, xlabel, ylabel — so a finished, presentation-quality chart can be produced in a single chained call.

Interpretation: These four chart types answer four different questions about the same dataset. The line chart of evening ridership shows the path demand took over time — you can see drift, anomalies, and recoveries. The histogram shows the distributional shape — is daily ridership symmetric, or do most days cluster in a narrow range? The bar chart of day_type (Weekday/Weekend) answers “how often does each day type appear?” — a simple frequency table turned visual. The box plot of morning vs. evening ridership side by side answers “does the distribution of morning peak demand differ from evening peak?” — comparing medians and spreads across two columns simultaneously. Each chart takes one line of code; together they give a more complete picture of the data than any single view.

In practice

At an e-commerce company running an A/B test, a data analyst would plot the distribution of purchase values in the control and treatment groups as histograms (to check for outliers before computing means), then plot the daily conversion rate as a line chart over the test period (to check for ramp-up effects or day-of-week patterns), and finally use a bar chart to compare the aggregate conversion rate across segments. These are not cosmetic additions — they are the checks that prevent reporting a statistically significant result that is actually driven by a data artifact.

Four Charts at a Glance

The four chart types covered above answer four different questions and a quick reference table is useful when deciding which to reach for. A line chart shows trends over an ordered axis, typically time. A histogram reveals the distribution of a single variable — its centre, spread, skewness, and tails. A bar chart compares aggregated values across discrete categories. A box plot summarises the spread and outlier behaviour of one or more variables side by side.

Chart	Code	Use for
Line	`df['riders_thousands'].plot()`	Trends over time
Histogram	`.plot(kind='hist')`	Distribution of one variable
Bar	`.plot(kind='bar')`	Comparing categories
Box	`.plot(kind='box')`	Spread, median, outliers

Exercise: Plot the Data

Exercise

Using the ridership data:

Plot daily ridership as a line chart
Plot the distribution of day-over-day ridership changes as a histogram
Count weekday vs weekend days and plot as a bar chart
Bonus: Which month had the highest average ridership?

Hint: Make sure the index is a DatetimeIndex, then use .resample('ME').mean()

Standardization

Background: why scale matters for distance-based methods

Many machine learning algorithms are distance-sensitive: they measure how similar two observations are by computing a distance (Euclidean, Manhattan, cosine, etc.) between their feature vectors. When features have wildly different scales — say, a “price” column with values in the thousands and a “number of employees” column with values in the millions — the larger-scale feature dominates every distance calculation simply because of its magnitude, not because it is more informative. The algorithm effectively ignores the small-scale features.

Examples of scale-sensitive algorithms: k-nearest neighbors, k-means clustering (Chapter 4), support vector machines, principal component analysis, and neural networks. Examples of scale-insensitive algorithms: decision trees, random forests, gradient boosting. This is why you will standardize before clustering in Chapter 4 but not before a decision tree.

Two standardization methods dominate in practice:

Z-score (standard scaling): Subtract the mean and divide by the standard deviation. The result has mean 0 and standard deviation 1. This preserves the shape of the original distribution (a normal distribution stays normal; a skewed distribution stays skewed) but puts all features on a common scale. Most appropriate when the data is roughly bell-shaped and when you want outliers to retain their relative position.

Min-Max scaling: Subtract the minimum and divide by the range. The result is bounded in [0, 1]. This is appropriate when you need bounded inputs (e.g., pixel values in image processing, activation functions in neural networks that expect inputs between 0 and 1). The weakness: it is extremely sensitive to outliers. If one observation has an anomalously large value, it compresses all other values toward 0.

In practice, a neural network’s input layer expects features scaled to a consistent range — typically [0, 1] with min-max or roughly [−3, 3] with z-score. Without scaling, gradients during backpropagation can be unstable, and the network converges slowly or not at all. This is why every production ML pipeline includes a scaling step.

Min-Max Standardization — Scale to $[0,1]$

Min-max scaling maps every value in a column into the closed interval $[0, 1]$ by a linear transformation. The smallest value becomes 0, the largest becomes 1, and every observation in between is placed proportionally to where it sits in the original range. The formula is direct: subtract the minimum, divide by the range.

\[x_{\text{scaled}} = \dfrac{x - \min}{\max - \min}\]

Min-max scaling is the right tool when downstream consumers require bounded inputs in $[0, 1]$ — neural network input layers, certain image-processing pipelines, and any algorithm whose internal arithmetic assumes inputs lie in the unit interval. It is also a convenient way to put variables with wildly different natural scales onto a common footing, so that a ridership figure in the thousands and a rainfall reading in single-digit millimetres can be compared on the same axis of a chart.

Interpretation: After min-max scaling, the output column riders_minmax contains values in [0, 1] where 0 corresponds to the lightest day observed and 1 corresponds to the busiest. A value of 0.75 means “this day’s ridership was 75% of the way from the minimum to the maximum observed ridership.” This interpretation is intuitive but fragile: if next month’s ridership exceeds the historical maximum, the scaled value will be greater than 1, breaking the [0, 1] guarantee. For this reason, the scaling parameters (min and max) should always be computed on the training data and applied to the test data without recomputing — exactly the protocol that scikit-learn’s MinMaxScaler enforces.

Z-Score Standardization

Z-score scaling centres a column on zero and rescales it to unit standard deviation. After the transformation, the new mean is exactly 0 and the new standard deviation is exactly 1; the shape of the distribution is preserved, but the scale is now expressed in standard-deviation units rather than in the original physical units.

\[x_{\text{std}} = \dfrac{x - \mu}{\sigma} \qquad \Rightarrow \qquad \mu_{\text{new}} = 0,\; \sigma_{\text{new}} = 1\]

The choice between the two scalers comes down to the downstream requirement. Use min-max when the distribution is unknown or when a bounded output is required by the consumer. Use z-score when the data is roughly bell-shaped, because it preserves relative distances faithfully and lends itself to direct interpretation in terms of standard deviations. Use neither when the original scale is itself meaningful — price ratios, percentages, and currency-denominated amounts should usually be left alone.

Interpretation: The z-score column has mean approximately 0 and standard deviation approximately 1. A value of +2.5 means “this observation is 2.5 standard deviations above average” — immediately interpretable in terms of how unusual it is. Under a normal distribution, only about 1.2% of observations exceed +2.5 standard deviations, so a z-score of +3 or higher is a genuine anomaly flag. In transit operations, z-scores are used to identify unusual ridership days or abnormal sensor readings. In fraud detection, transaction amounts with z-scores above a threshold (say, 4) are automatically flagged for review. The power of the z-score is precisely this: it translates raw numbers into a universal “how unusual is this?” scale.

In practice

In a customer segmentation pipeline at a company like Spotify or Netflix, features like “number of streams per month,” “average session duration,” and “days since last login” have completely different scales. Before running k-means clustering (Chapter 4), all features must be standardized, otherwise the clustering will be driven entirely by whichever variable has the largest raw variance. This is not a theoretical concern — in practice, skipping standardization before k-means typically produces clusters that correspond to the scale of one dominant variable rather than to meaningful customer segments.

Time Series Methods

Background: the special challenges of temporal data

Time series data breaks many assumptions that standard statistical methods rely on. Most regression and machine learning methods assume that observations are independent and identically distributed (i.i.d.) — each row is a random draw from the same population with no relationship to other rows. Time series data violates this assumption in at least two ways.

First, autocorrelation: today’s value is correlated with yesterday’s value. Ridership has very strong serial correlation — Mondays look like Mondays, and disruptions on one day spill over into the next. Sales data has weekly, monthly, and annual cycles. Web traffic has hour-of-day and day-of-week patterns. Ignoring autocorrelation means underestimating uncertainty in forecasts and producing invalid confidence intervals.

Second, non-stationarity: the statistical properties of the series (mean, variance) change over time. A retail sales series might trend upward over ten years (non-stationary mean), and its variance might increase with the level (non-stationary variance). Many statistical models require or implicitly assume stationarity. The most common transformation to induce stationarity in a level series is taking first differences or percentage changes — which is exactly what .pct_change() computes. This is why day-over-day changes (not raw levels) are almost always the unit of analysis in demand forecasting and operations analytics.

.pct_change() is one of the most-used pandas operations in business analytics. “What changed?” is almost always a more meaningful question than “what is the level?” A station that handled 5.00 million riders yesterday and 4.83 million today saw a 3.3% drop — that is the operationally meaningful quantity. The level 4.83 million depends on the size of the catchment area; the change −3.3% is comparable across stations, across cities, across weeks.

Rolling windows capture the idea of “recent history.” A 20-day rolling average asks: what was the average ridership over the most recent 20 days? The window slides forward one day at a time, producing a smoothed version of the original series that filters out day-to-day noise. In capacity planning, a 7-day or 28-day rolling mean is the standard way to compare this week’s demand against the recent baseline. In retail, a 30-day rolling sum of sales tracks recent demand in real time, which Walmart uses to trigger automatic reorders.

`pd.to_datetime()` — Parse Date Strings

A date stored as a string is, from pandas’ point of view, indistinguishable from any other piece of text. It cannot be subtracted from another date to obtain a duration; it cannot be queried for its year, month, or weekday; it cannot be sliced with a range expression. The remedy is a single call to pd.to_datetime(), which parses common formats and returns a proper DatetimeIndex (or Series of datetimes). Once that conversion has taken place, every time-aware operation in the rest of the chapter becomes available — date-range slicing, resampling, lag operators, and the calendar-based feature extractors dt.year, dt.month, dt.dayofweek.

Interpretation: The difference between object dtype (string) and DatetimeIndex is the difference between a label and a number. With string dates, pandas cannot compute the number of days between two observations, cannot extract the month, cannot resample from daily to monthly, and cannot do date-range slicing. After pd.to_datetime(), all of these operations become trivial. The df.index.month and df.index.dayofweek attributes allow you to add calendar-based features to your dataset — for example, “is this a Monday?” or “is this in Q4?” — features that are often powerful predictors in retail sales, web traffic, and consumer behavior models.

`.shift()` and `.pct_change()`

Two time-series operations are used so often in demand forecasting that they earn their own dedicated methods. The first, .shift(k), lags a Series by $k$ periods — the value that used to sit at row $t$ now sits at row $t+k$, and the first $k$ rows are filled with NaN. The second, .pct_change(k), computes the percentage change between an observation and its value $k$ periods earlier, which is exactly $(x_t - x_{t-k}) / x_{t-k}$. The two methods are intimately related: a one-period percentage change can be written by hand as (riders - riders.shift(1)) / riders.shift(1), and .pct_change() is simply the shortcut.

Interpretation: The pct_change column computed by .pct_change() is the daily percentage change in ridership. Looking at the first rows: riders_thousands moves from 100 → 102 → 99 → 105 → 98, which corresponds to changes of NaN, +2.0%, −2.9%, +6.1%, −6.7%. The first row is NaN because there is no “previous day” to compute a change from — this is expected and should be dropped before further analysis. The 5-day change pct_change_5d computes how much ridership changed over a week: for row 5 (index 5), it compares row 5 to row 0, giving the 5-day swing. This is a standard recent-trend feature: a system that has gained riders over the past 5 days is often coming back from a disruption, while one that has lost riders may be entering a holiday lull.

In practice

In retail demand forecasting (Walmart, Amazon, JD.com) and transit demand forecasting, .pct_change() and .shift() are used to engineer lag features for machine learning models: “what were sales 7 days ago,” “what was ridership the same day last week.” These lag features are often the most predictive variables in demand forecasting models. At Amazon, the demand forecasting system uses hundreds of lag and rolling features computed exactly this way to predict product-level demand days in advance and pre-position inventory in warehouses.

Rolling Statistics — Moving Windows

A rolling window captures the recent past at every point in a series. The expression riders.rolling(20).mean() does not return a single number; it returns a new Series whose value at row $t$ is the mean of the twenty most recent observations up to and including $t$. The window slides forward one step at a time, producing a smoothed version of the original series that filters out the day-to-day noise while preserving longer-term movement. Substituting .std() for .mean() produces a rolling standard deviation — the most common estimator of recent demand volatility — and substituting .sum() produces a rolling sum, which is the natural way to track recent demand or recent activity over a trailing window.

The rolling mean is the bedrock of trend smoothing — MA-7 for week-over-week context, MA-28 for monthly trend, and MA-90 for the seasonal baseline. The rolling standard deviation is the bedrock of demand-volatility monitoring. The rolling sum is the bedrock of retail demand tracking. The single method .rolling(n) followed by any reduction handles all three.

Cumulative Methods

Cumulative reductions walk through a Series from start to finish, carrying a running value that is updated at every step. .cumsum() accumulates a running total, .cumprod() accumulates a running product, .cummax() tracks the running maximum, and .cummin() tracks the running minimum. The four together are sufficient to compute most path-dependent quantities encountered in operations and analytics: compound growth from a series of percentage changes, peak-load tracking for capacity analysis, the all-time high or all-time low up to every date, and a running cumulative volume or revenue.

The compounded growth series (1 + chg).cumprod() - 1 is the workhorse output of any performance report: it tells you the cumulative change in demand from the start of the series. The shortfall series, defined as $(\text{level} - \text{running max}) / \text{running max}$, is its capacity-side counterpart: it tells you, at every point in time, how far ridership has fallen from its previous peak. The minimum of that series — shortfall.min() — is the largest peak-to-trough deficit ever experienced, a standard summary of demand drop-off used in any capacity-planning team.

Interpretation: The shortfall series is one of the most useful capacity metrics in operations planning. It answers: “if we had sized the network for the previous peak, how much spare capacity is sitting idle today?” A shortfall of −0.20 means ridership is 20% below the previous peak — the system is running at 80% of the load it was designed for. The deepest shortfall is shortfall.min() — the worst peak-to-trough demand drop over the entire history. This number tells planners something that the average level does not: how lumpy demand has been, and how much surge capacity needs to remain available.

The rolling MA plot overlaid on the level series produces the classic trend-and-noise chart. The MA-20 (20-day moving average) smooths short-term noise; when the raw series crosses above the MA-20, it is a leading indicator of a regime shift. However, it is important to note that simple crossover rules generate many false signals in noisy operational data and should be treated as exploratory cues, not automated triggers. The value of the MA visualization is primarily as a noise-reduction tool for visual analysis, not as a standalone decision signal.

In practice

The cumulative growth and shortfall series produced here are the two core outputs of any operations dashboard — at a transit authority, an airline, a hospital network, or a retailer. The pandas code here produces the same quantities that sophisticated operational systems compute — the production versions add benchmark comparisons (versus a budget, a target, or last year), and decomposition by segment, but they all start with the same .pct_change(), .cumprod(), and .cummax() building blocks.

Chapter Wrap-up

Takeaway — The DataFrame Workflow

Every analysis covered in this chapter fits into a six-stage pipeline whose stages recur in every subsequent project. Stage one is to load the data with read_csv() or one of its siblings. Stage two is to explore it with .head(), .shape, and .dtypes. Stage three is to select the rows and columns of interest using boolean masks, .loc[], and .iloc[]. Stage four is to modify the table by computing new columns. Stage five is to analyse the result with descriptive statistics, correlations, and time-series operators. Stage six is to plot. Every analytical workflow in finance, marketing, operations, and the sciences fits this template; mastering the six stages is the foundation on which every chapter that follows is built.

Chapter Summary

The DataFrame workflow as a pipeline

Every analytical task — whether it is a quarterly sales report, a churn prediction model, or a daily ridership forecast — follows the same six-stage pipeline: Load → Explore → Clean → Transform → Analyze → Visualize. This chapter covered each stage systematically, and the tools you learned map directly onto them:

Stage	Tools Covered
Load	`pd.read_csv()`
Explore	`.head()`, `.tail()`, `.shape`, `.dtypes`, `.describe()`
Select / Filter	`df['col']`, `df[['c1','c2']]`, `df[cond]`, `.loc[]`, `.iloc[]`
Transform	`df['new'] = ...`, list comprehension columns, `.drop()`, train/test split
Analyze	`.mean()`, `.std()`, `.corr()`, `.pct_change()`, `.rolling()`, `.cumprod()`
Visualize	`.plot()`, `kind='hist'`, `kind='bar'`, `kind='box'`

The top 10 pandas operations every analyst knows

df.describe() — summary statistics for all numeric columns
df.isna().sum() — count missing values per column
df[condition] — filter rows by boolean expression
df[['c1','c2']] — project specific columns
df.loc[row_label, col_label] — label-based selection
df['new'] = df['a'] + df['b'] — create a computed column
df.dropna(subset=['col']) — drop rows where a key column is missing
df['col'].fillna(df['col'].median()) — impute with median
df['col'].pct_change() — day-over-day percentage change from a level series
df['col'].rolling(20).mean() — 20-period moving average

Decision guide: which method to use when?

For missing values: - Missing < 5% and MCAR → dropna() - Missing 5–20% and MAR → fillna(mean) or fillna(median) - Time series → ffill() (carry forward last valid value) - Missing > 20% → investigate why before acting; consider dropping the column

For row selection: - You know the row label (name, date) → .loc[] - You know the row position (first 100, last 20) → .iloc[] - You want rows satisfying a condition → df[df['col'] > threshold]

For standardization: - Algorithm is distance-sensitive (k-means, kNN, SVM, neural net) → standardize first - Distribution is roughly normal → z-score - Need bounded [0, 1] output → min-max - Algorithm is tree-based (decision tree, random forest) → no standardization needed

For train/test split: - Time series data → sequential split (iloc[:train_size] / iloc[train_size:]), never shuffle - Cross-sectional data → df.sample(frac=1, random_state=42) then split

What’s next: Chapter 3 — Linear Regression

Chapter 3 builds directly on everything in this chapter. You will use .pct_change() to compute returns (in the CAPM example), .dropna() to clean the data, train/test splitting to evaluate your model, and .plot() to visualize fitted vs. actual values. The new tool is the linear regression model itself — but the pandas scaffolding around it is entirely what you learned here. Students who are solid on DataFrames find Chapter 3 straightforward; students who skipped practicing Chapter 2 find it confusing. There is no substitute for running the code cells yourself, on your own data, until the operations feel automatic.

Debug Yourself: Missing-Value Trap

The cell below averages a delivery hub’s net packages handled (inbound minus outbound) over a week. Two days were closed for holidays, so two of the seven entries are NaN. The code runs without an error and prints a clean-looking number — but the answer is wrong. A hidden test will tell you when you have fixed it.

The trap is the mismatch between numerator and denominator. net_packages.sum() silently skips NaN by default, so the numerator is correct: $120 - 45 + 80 + 200 + 60 = 415$. But len(net_packages) counts every entry in the Series, including the two NaN rows, so the denominator is 7 instead of 5 and the average is dragged down to $415/7 \approx 59.3$. The fix is to make the denominator agree with what .sum() actually counted. The cleanest one-liner is net_packages.mean(), which by default uses the count of non-null values; equivalently, net_packages.dropna().mean() or net_packages.sum() / net_packages.count(). Any of these returns the correct average $415/5 = 83.0$. The general lesson is broad: whenever you write an aggregation by hand, make sure the numerator and the denominator agree about which observations exist.

Interactive Explorer: Train/Test Split Ratio

The split ratio is one of the few hyperparameters a researcher chooses by hand, and the choice has real consequences for both training stability and the credibility of the out-of-sample test. Edit the two values at the top of the cell — first the fraction of rows reserved for training, then the window shown on the plot — click Run, and watch how the split date and the test-set size shift in response. Try 0.5, 0.7, 0.9 in turn; then zoom into the last 60 days to see how a more recent split affects what the model would have learned.

Working with an AI Copilot

An AI copilot is fast at producing data-cleaning code and slow at telling you what that code just did to your dataset. The danger is not wrong code — it is plausible-looking code that silently changes the shape of your table. A common failure mode: you ask the AI to “drop the duplicate rows” and it returns df.drop_duplicates() without mentioning that this just removed thirty per cent of the data because the duplicate key included a noisy timestamp column. The summary statistics you compute afterwards are still well-formed numbers; they are just answering a different question than the one you asked.

Three working rules:

For any cleaning step, demand a before and after row count, plus a one-line reason for any difference. If the AI cannot explain the gap, the step does not go into the pipeline.
Never let the AI fill missing values with 0 or mean without your explicit approval. Both choices silently bias downstream statistics, and the right answer depends on whether the value is missing-at-random or missing for a structural reason.
The AI cannot tell you whether a value of zero is a genuine zero or a missing reading coded as zero. That is domain knowledge — yours, not its.

Mistakes Library: Reinhart and Rogoff

Mistakes Library: Reinhart and Rogoff’s Excel Cell Error (2010)

In 2010, Harvard economists Carmen Reinhart and Kenneth Rogoff published Growth in a Time of Debt, arguing that countries whose public debt exceeded ninety per cent of GDP saw average real growth turn negative. The paper was cited by the IMF, the European Commission, the UK Treasury and several US senators to justify the austerity programmes that defined the post-crisis decade.

In 2013, Thomas Herndon, a graduate student at the University of Massachusetts Amherst, asked the authors for the underlying spreadsheet. Three problems surfaced. The headline one was a range error: the AVERAGE(...) formula that computed mean growth across high-debt countries stopped at row 49 instead of row 54, silently excluding Australia, Austria, Belgium, Canada and Denmark from the calculation. Re-including those five countries moved the average real growth in the high-debt bucket from roughly minus 0.1 per cent to plus 2.2 per cent. The qualitative claim — that high debt is associated with catastrophic contraction — did not survive the correction. Austerity was already in motion across Europe by the time the error was published.

The lesson for DataFrame work is operational, not moral. The row count of a groupby result, an iloc slice, a merge, or a dropna must be sanity-checked at every step. Print the shape. Compare it to what you expected. The AI copilot will not do this for you, and neither will Excel.

Decision Memo — Where Should Marketing Spend the Next 11.11 Coupon Budget?

The Shopee analysis earlier in this chapter is not an end in itself. It feeds a decision: where should the marketing team put the coupon budget for the next sale day? A junior analyst’s job is to compress the table into a one-page memo that a director can act on in two minutes. The format below is the working template — recommendation up top, evidence beneath, caveats acknowledged, next step concrete.

To: Marketing Director, Shopee SEA From: <Your name>, intern analyst Subject: Re-allocate 60% of the 12.12 coupon budget from Fashion → Beauty Date: 2026-05-15

Recommendation: Shift the lion’s share of the next sale’s coupon spend from Fashion to Beauty.

Evidence:

Beauty had the highest mean basket value with coupons used (≈ HK$X).

Coupon-driven baskets in Beauty are 2.3× larger than non-coupon ones.

Returning-customer share is highest in Beauty — coupon spend lifts loyalty.

Caveats:

Sample is one sale day only; weekly/seasonal effects unknown.

Causation versus selection: customers who use coupons may already be heavier spenders.

Next step: Re-run with three sale days of data; A/B test the budget shift on a randomised customer subset before full rollout.

Verify the “HK$X” placeholder against the value your own simulated DataFrame produced in the worked example above. A memo with the wrong number in it is worse than no memo.

Review Cards — Chapter 2

Seven flashcards covering the structural ideas of this chapter. Read each prompt, commit to an answer in your head, then click to reveal.

A DataFrame is rows × columns × index. Beginners notice the rows and columns immediately but underuse the index — the labels attached to each row. With a DatetimeIndex, df.loc['2020-01':'2020-03'] reads as plain English; without it, you are stuck with brittle positional code.

.loc is inclusive on the end label: rows a, b, c, AND d. .iloc[0:3] follows Python slice convention and excludes index 3, returning positions 0, 1, 2.

Shuffling lets future observations leak into the training set, so the model effectively sees the future. Any out-of-sample performance number it produces is a function of that leakage rather than genuine predictive skill. Time-series splits must respect the arrow of time: train on the past, test on the future.

df.head() returns the first 5 rows of the DataFrame (or df.head(n) for the first n). It is the cheapest possible sanity check on a freshly loaded dataset — column names, dtypes, and a few real values, all in one glance.

count (non-missing observations — the first place to spot hidden NaNs), mean and std (centre and spread), min and max (cheapest outlier check), and the 25th, 50th, 75th percentiles (shape of the distribution; the median is more robust than the mean when the data is skewed).

df['riders_thousands'] returns a Series (1-D, no column header in print output). df[['riders_thousands']] returns a single-column DataFrame (2-D, retains the column label). Many sklearn / statsmodels APIs expect a 2-D input, so the double-bracket form is the safer default when feeding a single column into a model.

After a filter such as crash = df[df['Return'] < -2], pandas does not know whether crash is a view of df or an independent copy. Modifying it could silently change df. The fix is to call .copy() explicitly: crash = df[df['Return'] < -2].copy(). After that, edits to crash never propagate back.

← PreviousPython Essentials Next →Linear Regression

A brief history: from relational databases to DataFrames

Why pandas conquered analytics

The data-to-insight pipeline

Why this chapter is weighted at 40% of the course

Loading and Exploring

Loading and Exploring Data

Background: where does business data live?

The DataFrame Object

A First Look at the Data

Computing Summary Statistics

Exercise: Load and Explore

Selecting and Filtering

Selecting Data

Background: selection as relational algebra

Select One Column — Returns a Series

Select Multiple Columns — Returns a DataFrame

Selecting Rows by Position

Conditional Selection — Filtering Rows

Multiple Conditions

Selecting Rows by Label

Comparing .loc and .iloc

A Worked Example: Shopee Sale-Day Baskets

Train/Test Split for Time Series

Background: a brief history of the train/test split

Train/Test Split for Cross-Sectional Data

Transforming Data and Statistics

Creating Columns and Method Chaining

Background: feature engineering as the source of most ML wins

Add a New Column

Conditional Column

Drop Columns and Rows

Method Chaining — Readable Pipelines

Key Statistical Methods

Background: what descriptive statistics actually tell you

Column Statistics

Understanding axis — Rows vs. Columns

axis in Action

Correlation — Which Variables Move Together?

Cleaning, Plotting, and Standardization

Handling Missing Values

Background: the statistics of missingness (Rubin 1976)

Why Do We Have NaN?

Fill Missing Values

Drop Missing Values

dropna() — Rows vs. Columns with axis

Plotting with Pandas

Background: exploratory vs. presentation plots

One Method, Four Chart Types

Four Charts at a Glance

Exercise: Plot the Data

Standardization

Background: why scale matters for distance-based methods

Min-Max Standardization — Scale to \([0,1]\)

Z-Score Standardization

Time Series Methods

Time Series Methods

Background: the special challenges of temporal data

pd.to_datetime() — Parse Date Strings

.shift() and .pct_change()

Rolling Statistics — Moving Windows

Cumulative Methods

Chapter Wrap-up

Takeaway — The DataFrame Workflow

Chapter Summary

The DataFrame workflow as a pipeline

The top 10 pandas operations every analyst knows

Decision guide: which method to use when?

What’s next: Chapter 3 — Linear Regression

Further reading

Debug Yourself: Missing-Value Trap

Interactive Explorer: Train/Test Split Ratio

Working with an AI Copilot

Mistakes Library: Reinhart and Rogoff

Decision Memo — Where Should Marketing Spend the Next 11.11 Coupon Budget?

Review Cards — Chapter 2

Understanding `axis` — Rows vs. Columns

`axis` in Action

`dropna()` — Rows vs. Columns with `axis`

`pd.to_datetime()` — Parse Date Strings

`.shift()` and `.pct_change()`