pandas: Series & DataFrames

The data model: Index, Series, DataFrame

In M48 you met the NumPy ndarray: a block of same-typed values with a shape. It is fast, but its rows and columns are only numbered. Real tables have names: a column called price, a row for order A-1007, a date. pandas adds labels on top of arrays.

• An Index is an immutable sequence of labels. It is how pandas finds rows (and columns). It can hold strings, integers, dates, or combinations of several levels (a MultiIndex).
• A Series is one column: a one-dimensional array of values plus an Index of row labels, a dtype (data type), and an optional name.
• A DataFrame is a table: several Series that share one row Index, stored under a second Index of column labels. Each column has its own dtype, so a DataFrame can mix text, numbers, and dates.

Index alignment: the rule that surprises everyone once

When you combine two Series, pandas matches values by label, not by position. Labels present in only one operand produce a missing value (NaN, "not a number"). This is the single most important behavior to internalize: it is why df["total"] = df["qty"] * df["price"] is safe even after you sort one of them, and why adding two Series with different indexes silently produces holes.

Remember: an Index holds labels, a Series is labelled values with one dtype, and a DataFrame is columns of Series sharing a row index. Operations align on labels.

dtypes and the pandas 3.0 defaults

Every column has a dtype that decides how values are stored and which operations are valid. The common ones:

The default string dtype

Before pandas 3.0, text columns were stored with the generic object dtype: an array of pointers to arbitrary Python objects, so pandas could not even promise the column contained strings. pandas 3.0 infers a dedicated string dtype for text by default. It prints as str (its full name is StringDtype(na_value=nan)), it uses NaN as its missing-value marker so it behaves like the other default dtypes, and it is backed by PyArrow when PyArrow is installed (falling back to a Python-object implementation when it is not). In practice this means text columns use less memory, string methods are faster, and assigning a non-string into a str column raises an error instead of silently mixing types.

Copy-on-Write

In pandas 3.0, Copy-on-Write (CoW) is always on. The rule is simple: any object you get from indexing or from a method behaves as an independent copy. pandas still shares memory behind the scenes for speed, and only copies at the moment one side is modified. Two consequences:

• Modifying a Series you pulled out of a DataFrame never changes the DataFrame.
• Chained assignment such as df["qty"][0] = 10 or df[df.qty > 1]["price"] = 0 never updates df. pandas emits a ChainedAssignmentError warning to tell you. Assign in one step with df.loc[row_selector, "col"] = value, or build a new frame with assign.

This replaced the old, confusing SettingWithCopyWarning, where the answer to "did my change stick?" depended on whether pandas happened to return a view or a copy.

Remember: check dtypes early (df.dtypes, df.info()); text is str in pandas 3.0; with Copy-on-Write, update data with a single .loc[...] = or with assign, never with chained brackets.

Reading data and first inspection

pandas readers turn files into DataFrames: pd.read_csv, pd.read_json, pd.read_parquet, pd.read_excel, pd.read_sql, and more. Each accepts a path, a URL, or a file-like object such as io.StringIO (which is how this page feeds them data, since the browser has no files).

read_csv guesses types by default. Guessing is convenient for exploration but dangerous in a pipeline: a ZIP code like 02134 loses its leading zero as an integer, and one bad value turns a numeric column into text. Production code states its expectations:

df = pd.read_csv(
    "orders.csv",
    usecols=["order_id", "date", "region", "amount"],   # read only what you need
    dtype={"order_id": "str", "region": "category", "amount": "float64"},
    parse_dates=["date"],                                # turn text into datetime64
    na_values=["", "n/a", "-"],                          # extra spellings of "missing"
)

Read the output like a detective. info() shows qty has 5 non-null values out of 6 and became float64 (because of the missing value). value_counts() reveals inconsistent casing in region. describe() gives quartiles that hint at outliers. These three calls are the first thing to run on any new dataset.

JSON and Parquet

JSON from APIs is usually a list of records: pd.read_json(io.StringIO(text)) handles that, and pd.json_normalize(records) flattens nested objects into dotted column names. Parquet is a binary, columnar, compressed file format that stores dtypes with the data, so nothing has to be guessed and reading a few columns skips the rest of the file. It is the standard format for analytical data; M52 explains how it works inside.

Remember: pass dtype, parse_dates, usecols, and na_values explicitly in pipelines; then run head, info, describe, and value_counts before trusting anything.

Selecting rows and columns

pandas has several selection tools. Learn them precisely, because the differences matter:

Boolean masks

A comparison on a Series produces a mask: a Series of True/False with the same index. Indexing with the mask keeps the True rows. Combine masks with & (and), | (or), and ~ (not), and always parenthesize each comparison, because & binds more tightly than >. Python's and/ or do not work on Series: they ask "is this whole Series true?", which is ambiguous, and raise ValueError.

Remember: loc is labels (inclusive slices), iloc is positions (exclusive slices); masks use & | ~ with parentheses; query reads well for long filters.

Cleaning: missing values, types, duplicates, strings, categories

Data cleaning is most of the work in real analysis. Each fix should be a deliberate, documented rule, because every rule changes the answer. The main tools:

Notice what errors="coerce" did: 2024-02-30 and not known became NaT instead of crashing the whole load. That is usually what you want, as long as you count the coerced values afterwards. Silent coercion that turns half a column into NaN is a common way to produce confident, wrong results.

Categorical data

A column with a few distinct values repeated many times (region, status, size) can be stored as category: pandas keeps one small table of the distinct values and an integer code per row. It saves memory, speeds up groupby, and lets you declare a meaningful order.

Remember: coerce bad values to missing, then count them; normalize strings with .str; define duplicates by the columns that identify a record; use category for repeated labels.

Creating columns: vectorize first

New columns come from expressions over existing ones. pandas operations on whole columns are vectorized: the loop runs in compiled code over the underlying arrays, just like NumPy. The alternatives, in rough order of speed:

1. Vectorized arithmetic and methods: df["qty"] * df["price"], .str, .dt, np.where(cond, a, b), np.select, pd.cut. Fastest; use these whenever possible.
2. Series.map(dict_or_func): element-wise lookup; fine for mapping codes to labels.
3. apply(func): calls a Python function per element (or per row with axis=1). Flexible but runs at Python speed: often 50-500x slower than vectorized code.
4. iterrows() loops: slowest and easiest to get wrong. Avoid for computation.

df.assign(name=expr, ...) returns a new frame with added or replaced columns. Inside assign, a lambda d: ... receives the frame as it is at that point, so later columns can use earlier ones.

Remember: express column logic as whole-column operations; reach for apply only when no vectorized form exists, and measure when in doubt.

groupby: split, apply, combine

"Revenue per region", "average delay per airline", "first purchase per customer": most summaries follow one pattern, named split-apply-combine by Hadley Wickham. Split the rows into groups by a key, apply a function to each group, combine the results into a new table.

Here is the same idea in plain Python, which is exactly what pandas does for you, but in compiled code. Step through it to see the dictionary of groups grow:

Three kinds of "apply"

Named aggregations make summary tables readable: each keyword becomes an output column, given as (input_column, function).

Remember: agg reduces each group to a row, transform broadcasts a group result back to every row, filter keeps or drops whole groups. Named aggregations make output columns self-documenting.

Combining and reshaping tables

merge: SQL-style joins

pd.merge(left, right, on="key", how=...) matches rows whose key values are equal. The how argument decides what happens to rows without a partner:

Unmatched columns are filled with missing values. The silent danger is duplicate keys: if a customer ID appears twice in the customer table, every one of that customer's orders is duplicated after the join, and revenue totals inflate without any error. Defend against it with validate=:

• validate="one_to_one": keys unique on both sides.
• validate="many_to_one": keys unique on the right (orders to customers: the usual case).
• validate="one_to_many", "many_to_many" (the last checks nothing).

indicator=True adds a _merge column saying whether each row came from left_only, right_only, or both: a quick audit of what did not match.

concat: stacking tables

pd.concat([df1, df2]) stacks frames vertically (same columns, more rows), aligning on column names. ignore_index=True renumbers rows; keys=["jan", "feb"] labels where each piece came from. With axis=1 it places frames side by side, aligning on the row index. Collect pieces in a list and concat once; concatenating inside a loop copies everything each time (quadratic cost).

Reshaping: wide and long

The same data can be long (one row per observation: date, city, temperature) or wide (one row per date, one column per city). Long format is best for groupby and plotting libraries; wide format is best for humans reading a table and for computing differences between columns.

• pivot_table(index=, columns=, values=, aggfunc=): long to wide, aggregating duplicates.
• melt(id_vars=, var_name=, value_name=): wide to long.
• stack() / unstack(): move the innermost column level into the row index and back.

Remember: pick how deliberately, always validate= joins, audit with indicator=True; concat a list once; long format for computing, wide format for reading.

Time series

When the row index is a DatetimeIndex, pandas gains a whole toolkit for time. Start by parsing text into real timestamps with pd.to_datetime, preferably with an explicit format= (faster and unambiguous: is 03/04/2025 March 4 or April 3?).

• The .dt accessor exposes parts: .dt.year, .dt.month, .dt.day_name(), .dt.hour, .dt.to_period("M").
• resample(rule) is groupby for time buckets: "D" daily, "W" weekly, "MS" month start, "h" hourly. Follow it with an aggregation.
• rolling(window) computes moving statistics over the last n rows (or a time span like "7D"), smoothing out noise to reveal a trend.
• shift(1) and pct_change() compare each period with the previous one.
• Time zones: tz_localize("America/New_York") declares which zone naive timestamps are in; tz_convert("UTC") converts an aware timestamp to another zone. Store and compute in UTC; convert for display.

Remember: parse with an explicit format, use resample for calendar buckets and rolling for moving windows, and keep timestamps in UTC internally.

Method chaining and performance

Because most pandas methods return a new object, you can write an analysis as one readable chain, top to bottom, each line one step. Wrap the chain in parentheses so each method sits on its own line. Use assign for new columns, query or loc[lambda d: ...] for filters, and pipe(func) to drop your own functions into the chain.

Performance rules of thumb

1. Vectorize. Whole-column operations beat apply, which beats loops.
2. Read less. usecols=, the right dtype=, and Parquet with columns= avoid loading what you will not use.
3. Use compact dtypes. category for repeated labels; str (Arrow-backed) instead of object; downcast with pd.to_numeric(s, downcast="integer") when it matters.
4. Build once. Append rows to a Python list and create the DataFrame once; concat a list of frames once. Growing a frame row by row is quadratic.
5. Measure with df.memory_usage(deep=True) and time.perf_counter(). When data no longer fits comfortably in memory, or you need multi-core speed, look at Polars and DuckDB (M52 and M61).

Remember: chains read like a recipe and avoid stale intermediate variables; performance comes from vectorization, reading less, and compact dtypes.

Practice

Each exercise runs in your browser with pandas (the first run downloads it). Data is built inline.

import pandas as pd

def top_products(df, n):
    revenue = df.assign(revenue=df["qty"] * df["unit_price"])
    return revenue.groupby("product")["revenue"].sum().nlargest(n)

import pandas as pd

def clean_prices(s):
    text = s.astype("str").str.strip().str.replace(r"[$,]", "", regex=True)
    return pd.to_numeric(text, errors="coerce").astype("float64")

import pandas as pd

def add_share(df):
    totals = df.groupby("team")["amount"].transform("sum")
    return df.assign(share=df["amount"] / totals)

import pandas as pd

def monthly_revenue(df):
    dates = pd.to_datetime(df["date"], format="%Y-%m-%d", errors="coerce")
    valid = df.assign(month=dates.dt.strftime("%Y-%m"))[dates.notna()]
    return valid.groupby("month")["amount"].sum().sort_index()

import pandas as pd

def revenue_by_region(orders, customers):
    joined = pd.merge(orders, customers, on="cust_id", how="left", validate="many_to_one")
    joined = joined.assign(region=joined["region"].fillna("Unknown"))
    return {str(k): float(v) for k, v in joined.groupby("region")["amount"].sum().items()}

Mini-project: clean and analyze a messy sales export

pixi init sales-cleanup && cd sales-cleanup
pixi add python=3.14 pandas numpy pyarrow
pixi run python make_data.py      # writes sales_raw.csv
pixi run python analyze.py        # cleans and summarizes

Quiz

Recap

• A DataFrame is labelled, typed columns sharing a row Index; operations align on labels.
• pandas 3.0: Copy-on-Write is always on (no chained assignment) and text defaults to the str dtype.
• Load with explicit dtypes; inspect with head, info, describe, value_counts.
• Select with loc (labels, inclusive), iloc (positions), masks, and query.
• Clean with coercion plus counting, .str methods, deliberate duplicate rules, and categories.
• Summarize with groupby: agg (named), transform, filter.
• Combine with validated merges and one-shot concat; reshape with pivot_table/melt.
• Time series: to_datetime, resample, rolling, UTC internally.

Going further

• pandas User Guide (especially "Copy-on-Write" and "Group by")
• pandas release notes for the 3.0 changes
• Python for Data Analysis, 3rd ed. by Wes McKinney (free online)
• Tidy Data by Hadley Wickham: the theory behind long vs wide
• Next: M52 Polars, Arrow & Columnar Data