Story Opening

Arjun’s seven parallel NumPy arrays went into the bin. In their place, Priya wrote a single object:

import pandas as pd


txns = pd.DataFrame({
    "txn_id":   ["T1", "T2", "T3", "T4"],
    "merchant": ["AcmeMart", "ZipFuel", "AcmeMart", "BookNook"],
    "amount":   [120.0, 45.5, 3000.0, 80.0],
})


print(txns.groupby("merchant")["amount"].sum().to_dict())
# -> {'AcmeMart': 3120.0, 'BookNook': 80.0, 'ZipFuel': 45.5}

“It’s a table,” she said. “Columns have names and types. Rows have labels. You can filter, group, join and window it. Every column is a NumPy array underneath, so it’s fast — as long as you keep thinking in columns, not rows.”

Arjun had written SQL for fifteen years. That, it turned out, was the best possible preparation for pandas.

SQL → pandas: The Rosetta Stone

SQLpandas
SELECT a, b FROM tt[["a", "b"]]
WHERE amount > 100t[t["amount"] > 100] or t.query("amount > 100")
WHERE m IN ('A', 'B')t[t["m"].isin(["A", "B"])]
ORDER BY amount DESC LIMIT 5t.nlargest(5, "amount") / t.sort_values("amount", ascending=False).head(5)
SELECT m, SUM(amount) ... GROUP BY mt.groupby("m")["amount"].sum()
HAVING COUNT(*) > 2.filter(lambda g: len(g) > 2) or filter the aggregated result
SUM(x) OVER (PARTITION BY c)t.groupby("c")["x"].transform("sum")
ROW_NUMBER() OVER (PARTITION BY c ORDER BY ts)t.sort_values("ts").groupby("c").cumcount() + 1
LEFT JOIN u ON t.k = u.kt.merge(u, on="k", how="left")
UNION ALLpd.concat([t1, t2])
CASE WHEN ... ENDnp.where(...), np.select(...), pd.cut(...)
COALESCE(x, 0)t["x"].fillna(0)
DISTINCTt.drop_duplicates() / t["m"].unique()

The Sample Data

Every block in this part rebuilds the same small transaction table so you can run it on its own. It’s eight rows on purpose: small enough to verify by eye.

import pandas as pd


txns = pd.DataFrame({
    "txn_id":      ["T1", "T2", "T3", "T4", "T5", "T6", "T7", "T8"],
    "customer_id": ["C1", "C2", "C1", "C3", "C2", "C1", "C3", "C2"],
    "merchant":    ["AcmeMart", "ZipFuel", "AcmeMart", "BookNook",
                    "AcmeMart", "ZipFuel", "BookNook", "ZipFuel"],
    "amount":      [120.0, 45.5, 3000.0, 80.0, 15000.0, 60.0, 95.0, 22.0],
    "ts": pd.to_datetime([
        "2026-09-01 09:15", "2026-09-01 10:02", "2026-09-01 23:40", "2026-09-02 08:30",
        "2026-09-02 02:11", "2026-09-02 12:00", "2026-09-03 18:45", "2026-09-03 19:05",
    ]),
    "is_fraud":    [0, 0, 1, 0, 1, 0, 0, 0],
})


print(txns.shape)                     # -> (8, 6)
print(txns.head(3))
#   txn_id customer_id  merchant  amount                  ts  is_fraud
# 0     T1          C1  AcmeMart   120.0 2026-09-01 09:15:00         0
# 1     T2          C2   ZipFuel    45.5 2026-09-01 10:02:00         0
# 2     T3          C1  AcmeMart  3000.0 2026-09-01 23:40:00         1

In real life the first line is usually txns = pd.read_csv("txns.csv", parse_dates=["ts"]) or pd.read_parquet(...).

Series, DataFrame and the Index

  • A Series is a 1-D labelled array: a NumPy array plus an index of labels.
  • A DataFrame is a dict of Series sharing one index: a table whose columns may have different dtypes.
  • The Index labels the rows. By default it’s 0..n-1 (a RangeIndex), but it can be anything — IDs, timestamps.
import pandas as pd


amounts = pd.Series([120.0, 45.5, 3000.0], index=["T1", "T2", "T3"], name="amount")


print(amounts["T2"])               # -> 45.5        (by label)
print(amounts.iloc[0])             # -> 120.0       (by position)
print(amounts.values.dtype)        # -> float64     (a NumPy array underneath)
print((amounts * 2).tolist())      # -> [240.0, 91.0, 6000.0]   (vectorised, like NumPy)
print(amounts[amounts > 100].index.tolist())   # -> ['T1', 'T3']

Inspecting a DataFrame

import pandas as pd


txns = pd.DataFrame({
    "merchant": ["AcmeMart", "ZipFuel", "AcmeMart", "BookNook"],
    "amount":   [120.0, 45.5, 3000.0, 80.0],
    "is_fraud": [0, 0, 1, 0],
})


print(txns.dtypes.astype(str).to_dict())   # -> {'merchant': 'str', 'amount': 'float64', 'is_fraud': 'int64'}
print(txns.columns.tolist())      # -> ['merchant', 'amount', 'is_fraud']
print(txns["merchant"].value_counts().to_dict())   # -> {'AcmeMart': 2, 'ZipFuel': 1, 'BookNook': 1}
print(txns["amount"].describe().round(1).to_dict())
# -> {'count': 4.0, 'mean': 811.4, 'std': 1459.4, 'min': 45.5, '25%': 71.4, '50%': 100.0, '75%': 840.0, 'max': 3000.0}
txns.info()                       # column dtypes, non-null counts, memory usage

Tip — Your first three commands on any new dataset: df.info() (types and nulls), df.describe() (distributions), df.head() (eyeball it). Most data bugs are visible right there.

Selecting Data: [], loc and iloc

import pandas as pd


txns = pd.DataFrame({
    "txn_id":   ["T1", "T2", "T3", "T4"],
    "merchant": ["AcmeMart", "ZipFuel", "AcmeMart", "BookNook"],
    "amount":   [120.0, 45.5, 3000.0, 80.0],
}).set_index("txn_id")              # use txn_id as the row labels


# Columns
print(type(txns["amount"]).__name__)        # -> Series      (single column)
print(type(txns[["amount"]]).__name__)      # -> DataFrame   (list of columns)


# loc: LABEL-based  -> loc[row_labels, column_labels]   (slices INCLUDE the end label!)
print(txns.loc["T3", "amount"])             # -> 3000.0
print(txns.loc["T1":"T2", "merchant"].tolist())   # -> ['AcmeMart', 'ZipFuel']


# iloc: POSITION-based -> iloc[row_positions, column_positions] (end EXCLUDED, like Python)
print(txns.iloc[0, 1])                      # -> 120.0
print(txns.iloc[-2:].index.tolist())        # -> ['T3', 'T4']


# Boolean filtering: the WHERE clause
big_acme = txns[(txns["amount"] > 100) & (txns["merchant"] == "AcmeMart")]
print(big_acme.index.tolist())              # -> ['T1', 'T3']


# loc accepts a boolean mask for rows AND a column selection — the cleanest form
print(txns.loc[txns["amount"] < 100, "merchant"].tolist())   # -> ['ZipFuel', 'BookNook']


# query(): SQL-flavoured string expressions; @ references Python variables
limit = 100
print(txns.query("amount > @limit and merchant != 'ZipFuel'").index.tolist())  # -> ['T1', 'T3']


# Handy predicates
print(txns[txns["merchant"].isin(["ZipFuel", "BookNook"])].index.tolist())  # -> ['T2', 'T4']
print(txns[txns["amount"].between(50, 500)].index.tolist())                 # -> ['T1', 'T4']

Gotcha — loc slices are inclusive. df.loc["T1":"T2"] includes T2, while df.iloc[0:2] excludes position 2. Labels have no “next” value to stop before, so pandas includes the endpoint.

Deep Dive: Index Alignment

This is the pandas behaviour that most surprises engineers from other languages. Arithmetic between Series aligns on index labels, not positions. Labels that don’t match produce NaN.

import pandas as pd


yesterday = pd.Series({"AcmeMart": 100.0, "ZipFuel": 50.0, "BookNook": 30.0})
today = pd.Series({"ZipFuel": 70.0, "AcmeMart": 130.0, "CoffeeCo": 12.0})   # different order and keys


change = today - yesterday
print(change.to_dict())
# -> {'AcmeMart': 30.0, 'BookNook': nan, 'CoffeeCo': nan, 'ZipFuel': 20.0}


# Fill missing labels instead of producing NaN:
print(today.sub(yesterday, fill_value=0).to_dict())
# -> {'AcmeMart': 30.0, 'BookNook': -30.0, 'CoffeeCo': 12.0, 'ZipFuel': 20.0}

Alignment is a feature: you can combine results computed separately (per-merchant totals from two days) without sorting or joining by hand. It’s also a trap: after filtering or sorting, a DataFrame keeps its original index labels, and assigning a Series computed elsewhere can line up rows you didn’t expect. When you genuinely want positional behaviour, use .to_numpy() or reset_index(drop=True).

import pandas as pd


df = pd.DataFrame({"amount": [10.0, 20.0, 30.0]})
filtered = df[df["amount"] > 15]                   # index is now [1, 2], not [0, 1]
print(filtered.index.tolist())                     # -> [1, 2]


scores = pd.Series([0.9, 0.1])                     # index [0, 1]
filtered = filtered.assign(score=scores)           # aligns on labels 1 and 2!
print(filtered["score"].tolist())                  # -> [0.1, nan]   (surprise)


filtered = filtered.assign(score=scores.to_numpy())   # positional
print(filtered["score"].tolist())                  # -> [0.9, 0.1]

Creating Columns

import numpy as np
import pandas as pd


txns = pd.DataFrame({
    "merchant": ["AcmeMart", "ZipFuel", "AcmeMart", "BookNook"],
    "amount":   [120.0, 45.5, 3000.0, 80.0],
    "ts": pd.to_datetime(["2026-09-01 09:15", "2026-09-01 10:02",
                          "2026-09-01 23:40", "2026-09-02 08:30"]),
})


# Vectorised arithmetic — never loop over rows to do this.
txns["amount_log"] = np.log1p(txns["amount"]).round(3)


# CASE WHEN with np.where (two branches) and np.select (many branches)
txns["size"] = np.where(txns["amount"] > 1000, "large", "small")
txns["band"] = np.select(
    [txns["amount"] < 50, txns["amount"] < 500],
    ["low", "mid"],
    default="high",
)


# Binning continuous values: pd.cut
txns["bucket"] = pd.cut(txns["amount"], bins=[0, 100, 1000, np.inf], labels=["<100", "100-1k", "1k+"])


# .dt accessor for datetime columns, .str accessor for strings
txns["hour"] = txns["ts"].dt.hour
txns["is_night"] = txns["hour"].between(0, 5) | (txns["hour"] >= 23)
txns["merchant_lc"] = txns["merchant"].str.lower().str.replace("mart", "_mart")


print(txns[["amount", "band", "bucket", "hour", "is_night"]].to_string(index=False))
#  amount band bucket  hour  is_night
#   120.0  mid 100-1k     9     False
#    45.5  low   <100    10     False
#  3000.0 high    1k+    23      True
#    80.0  mid   <100     8     False


# assign(): returns a NEW DataFrame — ideal for method chains
enriched = txns.assign(amount_usd=lambda d: d["amount"] / 83.0)
print("amount_usd" in txns.columns, "amount_usd" in enriched.columns)   # -> False True

Deep Dive: groupby — GROUP BY and Window Functions

groupby follows the split–apply–combine pattern: split the rows into groups by key, apply a function to each group, combine the results. What you get back depends on the kind of function you apply:

MethodReturnsSQL analogue
.agg(...) / .sum(), .mean() …One row per groupGROUP BY
.transform(...)One value per original rowWindow function OVER (PARTITION BY ...)
.filter(...)The original rows of groups that pass a testHAVING, but keeping detail rows
.apply(...)Anything — flexible but slow(a stored procedure per group)

Aggregation: one row per group

import pandas as pd


txns = pd.DataFrame({
    "customer_id": ["C1", "C2", "C1", "C3", "C2", "C1", "C3", "C2"],
    "merchant":    ["AcmeMart", "ZipFuel", "AcmeMart", "BookNook",
                    "AcmeMart", "ZipFuel", "BookNook", "ZipFuel"],
    "amount":      [120.0, 45.5, 3000.0, 80.0, 15000.0, 60.0, 95.0, 22.0],
    "is_fraud":    [0, 0, 1, 0, 1, 0, 0, 0],
})


# SELECT customer_id, COUNT(*), SUM(amount), MAX(amount), AVG(is_fraud) GROUP BY customer_id
per_customer = txns.groupby("customer_id").agg(
    n_txns=("amount", "size"),          # named aggregation: new_name=(column, function)
    total=("amount", "sum"),
    largest=("amount", "max"),
    fraud_rate=("is_fraud", "mean"),
)
print(per_customer)
#              n_txns    total  largest  fraud_rate
# customer_id
# C1                3   3180.0   3000.0    0.333333
# C2                3  15067.5  15000.0    0.333333
# C3                2    175.0     95.0    0.000000


# Group keys become the index; reset_index() turns them back into a column (like SQL output).
print(per_customer.reset_index().columns.tolist())
# -> ['customer_id', 'n_txns', 'total', 'largest', 'fraud_rate']


# Multiple keys -> a MultiIndex; unstack() pivots the inner key into columns.
spend = txns.groupby(["customer_id", "merchant"])["amount"].sum()
print(spend.unstack(fill_value=0))
# merchant     AcmeMart  BookNook  ZipFuel
# customer_id
# C1             3120.0       0.0     60.0
# C2            15000.0       0.0     67.5
# C3                0.0     175.0      0.0

transform: the window function — the feature-engineering workhorse

For ML features you usually want each transaction compared with its customer’s behaviour. That’s a window function, and in pandas it’s transform: the result has the same length and index as the input, so it can be assigned straight back as a column.

import pandas as pd


txns = pd.DataFrame({
    "customer_id": ["C1", "C2", "C1", "C3", "C2", "C1", "C3", "C2"],
    "amount":      [120.0, 45.5, 3000.0, 80.0, 15000.0, 60.0, 95.0, 22.0],
})


g = txns.groupby("customer_id")["amount"]


# AVG(amount) OVER (PARTITION BY customer_id)
txns["cust_mean"] = g.transform("mean").round(1)
# How unusual is this amount for THIS customer?
txns["amount_vs_mean"] = (txns["amount"] / txns["cust_mean"]).round(2)
# Share of the customer's total spend
txns["share"] = (txns["amount"] / g.transform("sum")).round(3)


print(txns)
#   customer_id   amount  cust_mean  amount_vs_mean  share
# 0          C1    120.0     1060.0            0.11  0.038
# 1          C2     45.5     5022.5            0.01  0.003
# 2          C1   3000.0     1060.0            2.83  0.943
# 3          C3     80.0       87.5            0.91  0.457
# 4          C2  15000.0     5022.5            2.99  0.996
# 5          C1     60.0     1060.0            0.06  0.019
# 6          C3     95.0       87.5            1.09  0.543
# 7          C2     22.0     5022.5            0.00  0.001

Gotcha — data leakage hides here. transform("mean") uses all of a customer’s transactions, including ones that happen after the row in question. For a real-time fraud model that’s cheating: at scoring time, the future isn’t available. Time-aware features need expanding or rolling windows ordered by timestamp — shown next — and Part 9 returns to leakage in detail.

Time Series: Ordering, Expanding and Rolling Windows

import pandas as pd


txns = pd.DataFrame({
    "customer_id": ["C1", "C2", "C1", "C3", "C2", "C1", "C3", "C2"],
    "amount":      [120.0, 45.5, 3000.0, 80.0, 15000.0, 60.0, 95.0, 22.0],
    "ts": pd.to_datetime([
        "2026-09-01 09:15", "2026-09-01 10:02", "2026-09-01 23:40", "2026-09-02 08:30",
        "2026-09-02 02:11", "2026-09-02 12:00", "2026-09-03 18:45", "2026-09-03 19:05",
    ]),
})


txns = txns.sort_values(["customer_id", "ts"]).reset_index(drop=True)
g = txns.groupby("customer_id")


# ROW_NUMBER() OVER (PARTITION BY customer ORDER BY ts)
txns["nth_txn"] = g.cumcount() + 1


# Mean of the customer's PREVIOUS transactions only (no peeking at the current or future rows)
txns["prev_mean"] = g["amount"].transform(lambda s: s.shift(1).expanding().mean())


# Time since the customer's previous transaction, in hours
txns["hours_since_prev"] = (g["ts"].diff().dt.total_seconds() / 3600).round(1)


print(txns[["customer_id", "ts", "amount", "nth_txn", "prev_mean", "hours_since_prev"]].to_string(index=False))
# customer_id                  ts  amount  nth_txn  prev_mean  hours_since_prev
#          C1 2026-09-01 09:15:00   120.0        1        NaN               NaN
#          C1 2026-09-01 23:40:00  3000.0        2     120.00              14.4
#          C1 2026-09-02 12:00:00    60.0        3    1560.00              12.3
#          C2 2026-09-01 10:02:00    45.5        1        NaN               NaN
#          C2 2026-09-02 02:11:00 15000.0        2      45.50              16.2
#          C2 2026-09-03 19:05:00    22.0        3    7522.75              40.9
#          C3 2026-09-02 08:30:00    80.0        1        NaN               NaN
#          C3 2026-09-03 18:45:00    95.0        2      80.00              34.2


# Resampling: bucket a time-indexed series into fixed intervals (daily totals here).
daily = txns.set_index("ts")["amount"].resample("D").sum()
print(daily.to_dict())
# -> {Timestamp('2026-09-01 00:00:00'): 3165.5, Timestamp('2026-09-02 00:00:00'): 15140.0, Timestamp('2026-09-03 00:00:00'): 117.0}


# Time-based rolling window: total spend in the trailing 24 hours, per customer.
rolling_24h = (
    txns.set_index("ts")
        .groupby("customer_id")["amount"]
        .rolling("24h")
        .sum()
)
print(rolling_24h.loc["C1"].tolist())       # -> [120.0, 3120.0, 3060.0]

Tip — velocity features (“how many transactions in the last hour?”, “how much spent in 24 hours?”) are among the strongest fraud signals, and groupby(...).rolling("1h") computes them for millions of rows in seconds.

Joins: merge

import pandas as pd


txns = pd.DataFrame({
    "txn_id":      ["T1", "T2", "T3", "T4"],
    "merchant_id": ["M1", "M2", "M1", "M9"],       # M9 has no reference data
    "amount":      [120.0, 45.5, 3000.0, 80.0],
})
merchants = pd.DataFrame({
    "merchant_id": ["M1", "M2", "M3"],
    "name":        ["AcmeMart", "ZipFuel", "BookNook"],
    "mcc":         ["5411", "5541", "5942"],
})


# INNER JOIN (default how="inner")
inner = txns.merge(merchants, on="merchant_id")
print(inner["txn_id"].tolist())             # -> ['T1', 'T2', 'T3']


# LEFT JOIN with an indicator column showing where each row matched
left = txns.merge(merchants, on="merchant_id", how="left", indicator=True)
print(left[["txn_id", "name", "_merge"]].to_string(index=False))
# txn_id     name    _merge
#     T1 AcmeMart      both
#     T2  ZipFuel      both
#     T3 AcmeMart      both
#     T4      NaN left_only


# validate= asserts the join cardinality — catches duplicate keys that would multiply rows.
checked = txns.merge(merchants, on="merchant_id", how="left", validate="many_to_one")
print(len(checked) == len(txns))            # -> True


# Different key names: left_on / right_on. UNION ALL: pd.concat.
more = pd.DataFrame({"txn_id": ["T5"], "merchant_id": ["M3"], "amount": [9.99]})
print(len(pd.concat([txns, more], ignore_index=True)))   # -> 5

Gotcha — silent row explosion. If the right table has duplicate keys, a merge multiplies rows — exactly like SQL, but without a DBA to notice. Always pass validate="many_to_one" (or "one_to_one") when you expect a lookup join.

Missing Data

import numpy as np
import pandas as pd


df = pd.DataFrame({
    "amount":  [120.0, np.nan, 80.0, 45.0],
    "country": ["IN", None, "US", "IN"],
    "age_days": pd.array([400, None, 12, 900], dtype="Int64"),   # nullable integer dtype
})


print(df.isna().sum().to_dict())             # -> {'amount': 1, 'country': 1, 'age_days': 1}
print(df["amount"].mean())                   # -> 81.66666666666667   (NaN skipped by default)


filled = df.fillna({"amount": df["amount"].median(), "country": "UNKNOWN"})
print(filled["amount"].tolist())             # -> [120.0, 80.0, 80.0, 45.0]
print(filled["country"].tolist())            # -> ['IN', 'UNKNOWN', 'US', 'IN']


print(len(df.dropna()))                      # -> 3   (drop rows with ANY missing value)
print(df["age_days"].dtype)                  # -> Int64   (capital I: stays integer despite NA)


# A "was missing" flag is often a strong feature in itself — keep it before imputing.
df["amount_missing"] = df["amount"].isna().astype(int)
print(df["amount_missing"].tolist())         # -> [0, 1, 0, 0]

Gotcha — integers with a missing value become floats in the classic NumPy-backed dtypes: an int64 column with one NaN turns into float64. Use nullable dtypes ("Int64", "boolean", "string") when that matters, e.g. for IDs.

Deep Dive: Copy-on-Write and the Chained-Assignment Trap

Older pandas tutorials are full of SettingWithCopyWarning. The underlying problem: does df[mask] return a view or a copy? It depended on internal details, so writing through it sometimes changed the original and sometimes didn’t.

pandas 3.0 settled this with Copy-on-Write (CoW): every indexing result behaves as a copy. Data is shared lazily under the hood and copied only when someone writes. The rule is now simple — modifying a derived object never modifies its parent — and it means “chained assignment” never works:

import pandas as pd


df = pd.DataFrame({"merchant": ["AcmeMart", "ZipFuel"], "amount": [120.0, 15000.0]})


# WRONG: chained assignment. df[...] returns a new object; setting on it is lost.
# pandas 3 emits ChainedAssignmentError (a warning) and df is NOT modified.
df[df["amount"] > 10_000]["amount"] = 10_000
print(df["amount"].tolist())        # -> [120.0, 15000.0]


# RIGHT: one .loc call with row selector AND column selector.
df.loc[df["amount"] > 10_000, "amount"] = 10_000
print(df["amount"].tolist())        # -> [120.0, 10000.0]


# Derived objects are independent: modifying 'sub' never touches 'df'.
sub = df[df["merchant"] == "AcmeMart"]
sub.loc[:, "amount"] = 0.0
print(df["amount"].tolist())        # -> [120.0, 10000.0]

Tip — If you’re on pandas 2.x, opt in with pd.options.mode.copy_on_write = True to get the same semantics and future-proof your code. The rule of thumb is identical in both: assign with a single df.loc[rows, cols] = value, never df[...][...] = value.

Vectorise, Don’t apply (and Never iterrows)

df.apply(f, axis=1) calls a Python function once per row. iterrows() is worse: it builds a Series object per row. Both bring back the interpreter overhead NumPy freed you from.

import time
import numpy as np
import pandas as pd


rng = np.random.default_rng(0)
df = pd.DataFrame({"amount": rng.lognormal(4, 1, 200_000), "fx": rng.uniform(1, 90, 200_000)})


start = time.perf_counter()
slow = df.apply(lambda row: row["amount"] * row["fx"], axis=1)      # Python call per row
apply_s = time.perf_counter() - start


start = time.perf_counter()
fast = df["amount"] * df["fx"]                                       # one vectorised op
vec_s = time.perf_counter() - start


print(np.allclose(slow, fast))              # -> True
print(f"apply: {apply_s:.2f}s, vectorised: {vec_s * 1000:.1f}ms")   # often 100x+ apart
NeedUse instead of apply
Arithmetic across columnsColumn expressions: df.a * df.b
If/elsenp.where, np.select, .mask, .where
Lookup / mapping.map(dict), merge
String ops.str.* accessor
Date parts.dt.* accessor
Per-group logicgroupby(...).agg / transform with built-in names

Method Chaining: SQL-Style Readability

Idiomatic modern pandas reads top to bottom like a query, with each step returning a new DataFrame. It avoids a graveyard of intermediate variables (df2, df_final, df_final_v2).

import pandas as pd


txns = pd.DataFrame({
    "customer_id": ["C1", "C2", "C1", "C3", "C2", "C1", "C3", "C2"],
    "merchant":    ["AcmeMart", "ZipFuel", "AcmeMart", "BookNook",
                    "AcmeMart", "ZipFuel", "BookNook", "ZipFuel"],
    "amount":      [120.0, 45.5, 3000.0, 80.0, 15000.0, 60.0, 95.0, 22.0],
    "is_fraud":    [0, 0, 1, 0, 1, 0, 0, 0],
})


report = (
    txns
    .query("amount >= 50")                                   # WHERE
    .assign(amount_k=lambda d: d["amount"] / 1000)           # computed column
    .groupby("merchant", as_index=False)                     # GROUP BY
    .agg(txns=("amount", "size"),
         spend_k=("amount_k", "sum"),
         frauds=("is_fraud", "sum"))
    .query("txns >= 2")                                      # HAVING
    .sort_values("spend_k", ascending=False)                 # ORDER BY
    .round(2)
)
print(report.to_string(index=False))
# merchant  txns  spend_k  frauds
# AcmeMart     3    18.12       2
# BookNook     2     0.18       0

Building Sentinel’s Feature Table

Putting it together: a realistic, leakage-aware feature table built from raw transactions — the input to Part 9’s model. This uses a synthetic dataset of 20,000 transactions so you can run it anywhere.

import numpy as np
import pandas as pd


rng = np.random.default_rng(42)
n = 20_000


# --- synthetic raw data -------------------------------------------------------
raw = pd.DataFrame({
    "customer_id": rng.integers(1, 1_001, n).astype(str),
    "merchant": rng.choice(["AcmeMart", "ZipFuel", "BookNook", "CoffeeCo", "GadgetHub"], n),
    "amount": rng.lognormal(mean=4.0, sigma=1.0, size=n).round(2),
    "ts": pd.Timestamp("2026-06-01") + pd.to_timedelta(rng.integers(0, 90 * 24 * 3600, n), unit="s"),
})


# --- feature engineering, as one chain ---------------------------------------
features = (
    raw
    .sort_values(["customer_id", "ts"])
    .assign(
        hour=lambda d: d["ts"].dt.hour,
        is_night=lambda d: d["ts"].dt.hour.isin([0, 1, 2, 3, 4, 5]).astype(int),
        log_amount=lambda d: np.log1p(d["amount"]),
        # history-only features: shift(1) excludes the current transaction
        cust_prev_mean=lambda d: d.groupby("customer_id")["amount"]
                                  .transform(lambda s: s.shift(1).expanding().mean()),
        cust_txn_count=lambda d: d.groupby("customer_id").cumcount(),
        mins_since_prev=lambda d: d.groupby("customer_id")["ts"].diff().dt.total_seconds() / 60,
    )
    .assign(
        amount_vs_history=lambda d: d["amount"] / d["cust_prev_mean"],
        merchant=lambda d: d["merchant"].astype("category"),   # compact + model-friendly
    )
)


print(features.shape)                                      # -> (20000, 11)
print(features["cust_prev_mean"].isna().sum() <= 1_000)    # -> True   (first txn per customer has no history)
print(features["merchant"].dtype)                          # -> category
print(features.memory_usage(deep=True).sum() < 3_000_000)  # -> True

Tips, Tricks & Gotchas

Tip — Parquet over CSV. df.to_parquet("txns.parquet") stores types, compresses well, and loads 5–20× faster than CSV. CSVs lose dtypes (dates become strings, IDs lose leading zeros). Use CSV for exchange, Parquet for everything else.

Tip — category dtype for low-cardinality strings (merchant, country, channel) cuts memory dramatically and speeds up groupby.

Gotcha — IDs read as numbers. read_csv turns "00123" into 123. Pass dtype={"customer_id": "string"}.

Tip — Too big for memory? pd.read_csv(path, chunksize=500_000) streams DataFrames (Part 5’s lazy idea, vectorised). For larger-than-memory analytics, look at Polars (a Rust DataFrame library with a lazy query optimiser) or DuckDB (run SQL directly on Parquet files and DataFrames). Both interoperate with pandas.

Gotcha — inplace=True rarely saves memory and breaks method chains. Prefer reassignment: df = df.dropna().

Key Takeaways

ConceptRemember
StructuresSeries = labelled 1-D array; DataFrame = dict of Series with a shared index
Selectionloc = labels (inclusive slices), iloc = positions; filter with boolean masks
AlignmentOperations align on index labels — mismatches become NaN
groupbyagg = GROUP BY, transform = window function, filter = HAVING
Joinsmerge(how=..., validate=...); concat for UNION ALL
Time.dt, shift, expanding, rolling("24h"), resample
Missing dataisna, fillna, dropna; nullable dtypes for ints and strings
Copy-on-WriteDerived objects never modify parents; assign with one .loc[...]
PerformanceVectorise; avoid apply(axis=1) and iterrows

Story Closing

Arjun’s feature pipeline shrank to a single readable chain — about sixty lines that ran over three years of transactions in under two minutes. He caught one leakage bug himself: a “customer average” feature that had quietly included each transaction’s own amount. Priya noticed he’d started writing validate="many_to_one" on every merge without being asked.

“So,” she said. “You have features. Shall we see if they predict anything?”

The fraud team had labelled ninety days of transactions: 0.8% confirmed fraud. Arjun’s first instinct was to train a model and report its accuracy. Priya’s expression suggested that would be a mistake.

In Part 9, Arjun trains Sentinel’s first model with scikit-learn — and learns why a 99.2% accurate fraud detector can be completely useless.

This is Part 8 of a 10-part series: “Python for Java Developers: From Streams to Tensors.”