DataFrames become useful once they are summarized, grouped, joined, reshaped, and transformed. This lesson walks through the operations that show up in almost every pandas script: aggregations (sum, mean, agg), groupby for split-apply-combine, merge and concat for joining tables, pivot tables for reshaping, the apply/map family for custom transforms, the .str and .dt accessors for column-wide string and datetime operations, and rolling windows for time series. It closes with where pandas slows down and how to keep things vectorized.

An aggregation reduces a column (or DataFrame) to a single number, or a small set of numbers. The basics are sum, mean, min, max, count, median, std. They're all methods on a Series or a DataFrame.

orders["price"].sum() adds the price column and returns a single float. mean() is the average. shape[0] is the number of rows (the count). Calling these on the whole DataFrame returns a Series, one value per numeric column:

Each column got its own sum. Non-numeric columns (customer, product, category) would be ignored by sum() by default.

For multiple aggregations on the same column, agg() takes a list of function names:

The result is a Series indexed by the function names. agg() accepts strings (which name built-in aggregations) or custom callable functions. Custom aggregations get applied per group when used inside groupby, which is the next topic.

Aggregations on a Series are vectorized C-level operations: a sum over a million floats takes milliseconds. Looping in Python (total = 0; for x in series: total += x) is two orders of magnitude slower. Use the built-in aggregation first.

groupby is the main pandas tool for "compute X per Y" problems. The pattern has three steps: split the rows into groups by the values of a column, apply an aggregation (or transform, or filter) to each group, combine the results back into a DataFrame indexed by the group keys.

The split: rows are bucketed by their customer value. The apply: sum() runs over the price column inside each bucket. The combine: results are stitched into a Series indexed by the unique customer names. Three lines of work in one expression.

Group by multiple columns by passing a list. The result has a MultiIndex:

Each unique (category, customer) combination is a group. Electronics, Alice includes her two electronics orders (Mouse at 29.99 and another Mouse at 29.99, summing to 59.98).

For multiple aggregations on the same column or different columns at once, pass a dict (or use the newer named-aggregation syntax):

Named aggregation is the clearest pattern: each keyword argument names a new output column, with a tuple of (source_column, aggregation_function). The result is a regular DataFrame ready to print, plot, or merge.

agg() on the GroupBy object also accepts a list, which produces a wider table with a MultiIndex on the columns:

Cleaner output for ad-hoc analysis; named aggregation is better when downstream code expects specific column names.

groupby doesn't compute anything until an aggregation is called. It builds an internal grouping structure, then the aggregation pass is vectorized. The first call is the expensive one; subsequent aggregations on the same groupby object reuse the structure, so chaining gb = orders.groupby("customer"); gb.sum(); gb.mean() is cheaper than two separate groupby calls.

The diagram shows the split-apply-combine sequence. Pandas internally bucketizes the rows by the group key, applies the aggregation to each bucket's slice of data, then stitches the results into one output indexed by the group keys.

Real datasets live in multiple tables. merge() is the SQL-style join: combine two DataFrames on a shared key. concat() stacks DataFrames vertically (more rows) or horizontally (more columns).

Set up two related tables:

The reviews reference products by product_id. A join brings the product details into each review row.

This is an inner join: only rows that match on product_id in both tables make it through. The Hub and Webcam have no reviews and are dropped; the review for P005 references a product not in products and is also dropped.

Switch the join type with how=:

A left join keeps every row from the left DataFrame (products). Products without reviews get NaN in the review columns. The rating column changed dtype from int64 to float64 because integers can't represent NaN (in the default int dtype).

When the join keys have different names in the two tables, use left_on and right_on:

Both key columns are kept by default. To drop the duplicate, follow with .drop(columns=["sku"]).

join() is a convenience for joining on the index instead of a column; it's effectively merge(left_index=True, right_index=True) with shorter syntax. For most explicit joins, merge() is clearer.

concat() stacks DataFrames. By default it stacks vertically (axis=0):

ignore_index=True resets the row index to a clean 0, 1, 2, 3. Without it, the original indices (0, 1, 0, 1) would be concatenated as-is, producing a duplicated index that confuses later .loc lookups. concat(axis=1) stacks horizontally, aligning on the index instead.

merge() builds a hash on the join key for one side (usually the smaller side) and probes it from the other, giving roughly O(N + M) complexity. For wide tables and large joins, sorting and using merge_asof (for time-series-style joins) or pre-indexing both sides on the join key can be meaningfully faster.

A pivot table reshapes long-format data (one row per observation) into wide format (one row per group, one column per category value). It's a special case of groupby + reshape that comes up often enough to deserve its own name.

The index becomes the row labels (one row per customer). The columns becomes the column labels (one column per category). The values column is what gets aggregated, and aggfunc says how (here, "sum"). fill_value=0 replaces the NaNs that show up when a customer has no orders in a category. Alice has electronics totals from two orders (29.99 + 59.99 = 89.98), no audio purchases, and one cable purchase.

The opposite operation is melt(), which unpivots wide-format back into long:

Long format has one row per observation, which fits most pandas operations and plotting with libraries like seaborn. Wide format suits human reading and Excel-style reports.

When the built-in aggregations and vectorized arithmetic don't cover the operation, the apply/map family runs a Python function over the data. The trade-off is flexibility versus speed; vectorized operations are always faster, so use apply only when there's no vectorized way.

For string operations, the vectorized .str.upper() is faster and clearer than .apply(lambda x: x.upper()). apply fits when the function is complex or has no vectorized equivalent.

Series.map()

map() transforms values one at a time. It accepts a function, a dict (for value-to-value replacement), or a Series (also a lookup).

The dict version is a fast lookup: each value in category_code is looked up in code_to_name, and the result becomes the new column. Values not in the dict become NaN. With a callable, series.map(fn) calls fn(value) once per row.

Series.apply()

apply() on a Series runs a function on each value. It's similar to map() for callables but exists separately and has slightly different defaults around handling missing values.

DataFrame.apply()

On a DataFrame, apply() runs a function per column (axis=0, the default) or per row (axis=1). Per-row apply is the slowest of the family but also the most flexible.

This works but is the wrong pattern for the task. The vectorized form is faster, simpler, and produces the same result:

apply(..., axis=1) is iterating in Python and calling the lambda once per row. The vectorized form runs the multiplication in C across all rows at once. The performance difference matters: at 100,000 rows the vectorized form is roughly 50-100x faster.

applymap() (deprecated in pandas 2.1 in favor of DataFrame.map()) applies a function element-wise across an entire DataFrame. Useful for formatting all numeric cells, for example, but the vectorized form is preferred when one exists.

apply(axis=1) is the slowest pandas operation per row by a wide margin: each row crosses the Python-to-C boundary. For any operation that has a vectorized equivalent (*, +, np.where, .str.*, .dt.*), use the vectorized form. Reserve apply for irregular logic that can't be expressed vectorially.

Pandas exposes a .str namespace on Series of strings (or object dtype). Every string method on a Python str has a vectorized equivalent: .str.lower(), .str.upper(), .str.contains(), .str.startswith(), .str.replace(), .str.split(), and so on.

.str.lower() runs lowercase on every row in one vectorized pass. .str.contains() returns a Boolean Series, which can feed directly into a filter: products[products["name"].str.contains("Wireless")].

For splitting, expand=True turns the result into a DataFrame with one column per split piece:

n=1 means "at most one split per string." With expand=True, the result is a two-column DataFrame, which the multi-assign on the left side unpacks into the two new columns. Without expand=True, the result would be a Series of lists, which is less useful.

The mirror of .str for datetime columns. After converting a column with pd.to_datetime, .dt.year, .dt.month, .dt.dayofweek, .dt.hour, and many more are available.

Each .dt.* access extracts a piece of the timestamp. Combine with groupby to get monthly or hourly aggregates:

The group key is the month extracted on the fly. The resulting Series is indexed by month number.

For time-zone-aware columns, .dt.tz_convert() shifts to another time zone; .dt.tz_localize() attaches a time zone to a naive timestamp. Date arithmetic with pd.Timedelta and pd.DateOffset is also under this namespace.

Some aggregations run over a window of rows: a 7-day moving average, a cumulative sum, a 30-row max. Pandas exposes this as rolling, expanding, and ewm (exponentially weighted).

rolling(window=3).mean() computes the average over each 3-row window. The first two values are NaN because the window isn't full yet (pass min_periods=1 to change this). expanding().sum() is a cumulative sum, running over all previous rows. Both return a Series of the same length as the input.

For a time-based window (rather than a row-based one), pass a string like "7D":

A "7D" window includes all rows within the past 7 days regardless of how many rows that is. This matters for irregularly sampled data (some days missing, multiple events per day); the row-based window=7 would count rows, the time-based "7D" counts days.

Pandas is fast when operations are vectorized: NumPy or C code runs over the whole column at once. It's slow when operations cross back into Python, which happens in three common ways:

1. Row-wise `apply`. Each row crosses the Python boundary. For a million rows, that's a million Python function calls.

2. Looping over rows with `iterrows()` or `itertuples()`. Same problem: Python per row. Use it only when one row at a time is required and no vectorized alternative exists.

3. Repeated single-cell access in a Python loop. for i in range(len(df)): df.iloc[i, 2] += 1 is the slowest possible pattern. Each access has full pandas indexing overhead.

The vectorized fixes are usually one of:

A small benchmark makes the gap concrete:

On a typical machine, slow() takes around 1.5 seconds; fast() takes around 1 millisecond, two orders of magnitude apart. Both produce the same result.

For complex per-row logic that can't be vectorized (calling an external API, parsing JSON per row with branching), Python loops over itertuples() are usually the cleanest:

itertuples() is faster than iterrows() because the row is a named tuple instead of a Series. Both are still per-row Python, so use them only when no vectorized path exists.

A few habits that keep pandas fast:

Use the right dtype. A column of repeating strings (categories, statuses) is much smaller and faster as category than as object. A column of small integers fits in int8 instead of int64. Use df.memory_usage(deep=True) to see where the bytes go.

Avoid `inplace=True`. It rarely saves memory (pandas often makes the copy anyway internally) and breaks method chains. The performance argument for inplace is mostly mythology in modern pandas; the readability argument against it is real.

Filter early. When only 1% of the rows are needed, filter before grouping, joining, or computing. The downstream operations then run on the smaller frame. df[df["country"] == "US"].groupby(...).sum() is faster than df.groupby(...).sum() followed by filtering.

Avoid growing DataFrames in a loop. pd.concat inside a loop is O(N^2) because each concat copies the whole accumulated frame. Build a list of DataFrames and concatenate once at the end:

Pick the right merge strategy. For very large joins, ensure the smaller side fits in memory and the join keys have the right dtype (avoid joining int against object, which forces conversion). For sorted data, merge_asof is fast.

Use `query()` for complex Boolean masks on large frames. It can use NumExpr internally for some operations, which avoids materializing intermediate Boolean arrays.