Data & App Building · Chapter B1

Numerical Computing & Data Analysis with NumPy and Pandas

Q: What is the "SettingWithCopy" trap, and how do you correctly assign to a filtered subset?

Chained assignment like df[df.x > 0]["y"] = 1 modifies a throwaway copy and silently fails; assign through a single .loc instead: df.loc[df.x > 0, "y"] = 1.

Before an LLM ever sees your data, you have to load it, clean it, and understand it — and that's Pandas, riding on NumPy. This chapter is the data-analysis workhorse: the array/DataFrame model, the vectorized mindset, and the load→clean→transform→aggregate loop that precedes every ML or LLM pipeline.

⏱️ ~65 min🧪 4 labs🎯 Beginner→Expert

🌱 Start here — from zero Data & app building, from scratch — the data-wrangling and app skills that turn a model into something people can use.

AI apps need two everyday skills this section teaches: working with data (loading, cleaning, shaping it with NumPy/Pandas) and building interfaces (an API with FastAPI, a UI with Streamlit/Gradio). These are the glue between a model and a real, usable product.

The words you'll hear (in plain terms):

Term	What it actually means
NumPy / Pandas	Python libraries for numeric arrays and tables (dataframes).
dataframe	a table of data you can filter, group, and transform in code.
API (FastAPI)	a program that serves your app's functionality over HTTP for others to call.
UI (Streamlit/Gradio)	tools to build a simple web interface for your app in pure Python.
endpoint	one callable URL of your API (e.g. /ask).

What you need before starting:

Python basics (the Python data chapter is a gentle lead-in).
pip install numpy pandas fastapi streamlit gradio for the labs.
No web-development background assumed.

New to the topic? Read this box, then take the chapters in order — each section is tagged essential → expert so you always know the depth you're at.

Learning objectives

Explain why NumPy arrays and Pandas DataFrames beat Python lists/dicts for data.
Adopt the vectorized mindset — operate on whole columns, not with loops.
Run the core Pandas loop: load → inspect → clean → transform → aggregate.
Select and filter rows/columns correctly (and avoid the classic gotchas).
Prepare a messy dataset into clean records ready for an LLM or a chart.

How this relates to A4 and P2The A4 page covers NumPy for tensors and model math (broadcasting, dtypes, cosine similarity). This chapter is the data-analysis use: NumPy as the engine under Pandas, which is what you actually reach for to wrangle CSVs, logs, and tables. Python data structures are in P2. We build on both; here the star is the DataFrame.

Why not just lists and dicts? essential

You could hold a dataset in a list of dicts. For anything past a few rows it's slow, memory-hungry, and painful to query. NumPy and Pandas exist because columnar, typed, contiguous data is faster to compute on and far nicer to work with.

Python lists/dicts	NumPy / Pandas
Each element is a boxed Python object	Contiguous typed memory — compact & cache-friendly
Loops in Python (slow)	Vectorized ops in C (fast)
Manual filtering/grouping code	One-liner select/filter/group
No column types or labels	Named, typed columns + an index

The one mental shift: think in columns, not rowsThe single habit that makes you productive here is the vectorized mindset: instead of looping over rows, you apply an operation to a whole column at once. df["price"] * 1.1 beats a for loop — shorter, and often 10–100× faster because the work happens in compiled code. If you catch yourself writing for row in ..., stop and ask "what's the column operation?"

NumPy: the array engine essential

NumPy's core is the ndarray — a typed, N-dimensional array. Pandas is built on it, so a little NumPy fluency goes a long way.

Example code for learning — review, test, and adapt it before running against real or production systems. Commands can create, change, or delete resources. See the Terms & Disclaimer.

Lab B1.1

numpy_basics.pyimport numpy as np

a = np.array([1, 2, 3, 4])
print(a * 10)              # [10 20 30 40] — vectorized, no loop
print(a.mean(), a.sum())    # 2.5 10 — fast aggregates
print(a[a > 2])            # [3 4] — boolean mask selection

m = np.array([[1, 2], [3, 4]])
print(m.shape)              # (2, 2)
print(m.mean(axis=0))       # [2. 3.] — mean down each column

▶ How this works

This lab shows the one thing that makes NumPy fast and pleasant: you operate on a whole array at once instead of looping over items one by one. An array (an ndarray) is like a Python list, but all the same type and stored in one tight block of memory, so the math runs in compiled C code.

np.array([1, 2, 3, 4]) builds a 1-D array a. a * 10 multiplies every element by 10 in a single step — this is vectorization. No for loop, and it is far faster than one.
a.mean() and a.sum() are aggregates — they reduce the whole array to one number (the average, the total).
a[a > 2] is a boolean mask: a > 2 first makes an array of True/False ([F, F, T, T]), then indexing with it keeps only the True positions. This exact idea becomes row-filtering in Pandas.
m is a 2-D array (a grid of rows and columns). m.shape reports its size as (rows, cols). m.mean(axis=0) averages down each column; axis=1 would average across each row.

What the output means: The comments on each line are the printed results: [10 20 30 40], then 2.5 10, then [3 4], then (2, 2) and [2. 3.]. The 2. with a dot means it is a float.

Try this: Change a[a > 2] to a[a > 5] and predict the result before running — the mask is now all False, so you get an empty array []. Then try m.mean(axis=1) and see the per-row averages instead of per-column.

Two ideas you'll reuse constantlyBoolean masks (a[a > 2]) select elements by a condition — the foundation of filtering in Pandas too. Axis says which direction to reduce: axis=0 collapses rows (per-column result), axis=1 collapses columns (per-row result). Broadcasting and dtypes are covered in depth in A4 — here you just need the array feel.

Pandas: the DataFrame essential

A DataFrame is a labeled 2-D table: named, typed columns (each a Series) and a row index. It's a spreadsheet you drive with code. This is where you'll spend your data time.

Labeled, typed, columnar. Each column has a name and a dtype; the index labels rows. You select columns by name, filter rows by condition, and aggregate by group — all vectorized. This structure is why "load a CSV and understand it" is a handful of lines, not a script.

🗺️ How to read this diagram

This picture is the mental model for a DataFrame — the table you will spend most of your Pandas time in. Read it as a spreadsheet that you drive with code instead of a mouse.

The leftmost skinny column is the index (0, 1, 2) — the label for each row. It is not data; it is how you refer to rows.
Each of the other blocks is a column with a name and a type (dtype): name holds text (str), age holds whole numbers (int), spend holds decimals (float). Everything in one column is the same type.
The caption's key phrase: a single column is a Series; the whole table is a DataFrame. When you write df["age"] you get back one Series.
Because data is stored by column (columnar) and typed, Pandas can select, filter, and aggregate whole columns at once — the same vectorized speed you saw in NumPy.

In short: Think of it as a spreadsheet with a row-number margin: pick a column by its header name, pick rows by their index or by a condition. That is 90% of Pandas.

Lab B1.2 · The core loop: load → inspect → clean → transform → aggregate intermediate

Almost all data work is this loop. Learn it once and every dataset yields to the same moves.

Lab B1.2

pipeline.pyimport pandas as pd

# 1 · LOAD
df = pd.read_csv("sales.csv")

# 2 · INSPECT — always look before you leap
df.head()          # first rows
df.info()          # columns, dtypes, non-null counts
df.describe()      # numeric summary stats
df.isna().sum()    # missing values per column

# 3 · CLEAN
df = df.dropna(subset=["price"])           # drop rows missing a price
df["region"] = df["region"].fillna("unknown")   # fill missing categories
df["date"] = pd.to_datetime(df["date"])  # fix a dtype

# 4 · TRANSFORM — vectorized, no loop
df["total"] = df["price"] * df["qty"]

# 5 · AGGREGATE
by_region = df.groupby("region")["total"].sum().sort_values(ascending=False)
print(by_region)

▶ How this works

This is the core loop of almost all data work, in five numbered stages: LOAD, INSPECT, CLEAN, TRANSFORM, AGGREGATE. Learn this order once and every dataset yields to the same moves. The comments (the # 1, # 2 …) mark each stage.

1 · LOAD — pd.read_csv("sales.csv") reads a comma-separated file straight into a DataFrame called df. One line, and you have a table.
2 · INSPECT — look before you leap. df.head() shows the first rows, df.info() lists columns and their types, df.describe() gives summary stats, and df.isna().sum() counts missing values per column.
3 · CLEAN — dropna(subset=["price"]) deletes rows with no price; fillna("unknown") replaces missing regions with a placeholder; pd.to_datetime(...) fixes a column that was text into real dates.
4 · TRANSFORM — df["total"] = df["price"] * df["qty"] makes a new column by multiplying two columns element-by-element. Vectorized: no loop.
5 · AGGREGATE — groupby("region")["total"].sum() totals total within each region, and sort_values(ascending=False) orders the result biggest-first.

What the output means: print(by_region) shows one line per region with its summed total, highest first — e.g. the region that sold the most money appears at the top.

Try this: Swap .sum() for .mean() to get the average sale per region instead of the total. The rest of the pipeline does not change — that is the point of the loop.

Inspect first — every timeThe most common mistake is transforming before understanding. head(), info(), describe(), and isna().sum() take ten seconds and tell you the shape, the dtypes, and where the dirt is. Skipping them is how you divide by a column that's secretly a string, or average over rows that are silently null.

Lab B1.3 · Selecting & filtering (and the gotchas) intermediate

Getting the right rows and columns is the operation you'll do most. There are a few correct patterns and a few classic traps.

Lab B1.3

select.pydf["price"]                 # one column → Series
df[["name", "price"]]      # several columns → DataFrame

# boolean-mask filtering (the NumPy idea, on rows)
df[df["price"] > 100]                          # rows where price > 100
df[(df["price"] > 100) & (df["region"] == "EU")]  # combine with & / | and parens

# label- vs position-based access
df.loc[df["region"] == "EU", "total"]   # .loc = labels/conditions
df.iloc[0:5]                             # .iloc = integer positions

▶ How this works

Getting the exact rows and columns you want is the operation you will do most. This lab collects the correct patterns — plus the classic traps that catch every beginner.

df["price"] (single name, single brackets) returns one column as a Series. df[["name", "price"]] (a list of names, double brackets) returns a smaller DataFrame with just those columns.
Filtering rows uses a boolean mask, exactly like NumPy: df[df["price"] > 100] keeps rows where price is over 100.
To combine conditions you must use & (and) / | (or) and wrap each condition in parentheses: df[(df["price"] > 100) & (df["region"] == "EU")]. Plain and/or will error here.
Label vs position: .loc selects by labels or a condition (df.loc[df["region"] == "EU", "total"] = the total column for EU rows), while .iloc selects by integer position (df.iloc[0:5] = the first five rows, regardless of their labels).

Try this: The biggest trap is chained assignment: df[df.x > 0]["y"] = 1 changes a throwaway copy and silently does nothing. Always write it as one .loc: df.loc[df.x > 0, "y"] = 1. This is the "SettingWithCopy" warning the warning box below is about.

Gotcha	Fix
`and`/`or` in a mask → error	Use `&`/`\|` and wrap each condition in parentheses
Chained assignment (`df[m]["col"] = ...`) silently fails	Assign with `df.loc[mask, "col"] = ...`
Confusing `.loc` and `.iloc`	`.loc` = labels/conditions; `.iloc` = integer positions
Looping to compute a column	Vectorize: `df["c"] = df["a"] * df["b"]`

The SettingWithCopy trapWriting df[df.x > 0]["y"] = 1 modifies a temporary copy, not df — Pandas even warns you. Always assign through a single .loc: df.loc[df.x > 0, "y"] = 1. This bug is subtle because it fails silently — your change just doesn't stick.

Group-by: the analytical workhorse intermediate

The move that answers most business questions is split-apply-combine: split rows into groups, apply an aggregate to each, combine into a result. It's the SQL GROUP BY (T5) in Pandas.

Split → apply → combine. df.groupby("region")["total"].sum() splits rows by region, sums total in each, and combines into one Series indexed by region. Swap .sum() for .mean(), .count(), or .agg([...]) for multiple stats at once. This one pattern answers "per X, what's the Y?" for any X and Y.

🗺️ How to read this diagram

This diagram explains group-by, the single move that answers most "per X, what's the Y?" questions. Its formal name is split → apply → combine, and the picture walks left to right through those three steps.

Split (left): the box all rows fans out into groups — here one group per region: EU, US, APAC. Rows with the same region land together.
Apply (middle): the same aggregate, sum(total), runs on each group separately — one total per region.
Combine (right): the per-group answers are stitched into one small result — a Series indexed by region.
In code this whole flow is one line: df.groupby("region")["total"].sum(). Swap .sum() for .mean(), .count(), or .agg([...]) to ask a different question of the same groups.

In short: Read it as a sentence: "for each region, sum the total." Change the group column or the aggregate and you have answered a brand-new business question with the same shape.

Lab B1.4 · From messy data to LLM-ready records advanced

Where this connects to the rest of the course: an LLM (or a chart, B2) needs clean, structured input. Pandas is how you get there — and how you turn a table into the records or JSON your prompt/pipeline consumes.

Lab B1.4

Illustrative fragment — defines demo values / files are needed before this runs standalone.

to_llm.py# clean + shape a table, then hand rows to an LLM step
clean = (df
    .dropna(subset=["review"])
    .assign(review=lambda d: d["review"].str.strip())
    .loc[lambda d: d["review"].str.len() > 0]
)

records = clean[["id", "review"]].to_dict(orient="records")
# records = [{"id": 1, "review": "..."}, ...] — ready to batch through a classifier (Ch 2)

# and the reverse: model results back into a DataFrame for analysis / a chart
results = pd.DataFrame([classify(r["review"]) for r in records])
merged = clean.reset_index(drop=True).join(results)

▶ How this works

This ties Pandas to the rest of the course: an LLM (or a chart) needs clean, structured input. Here you clean a text column, turn the rows into a list of little dictionaries a prompt can consume, then collect the model's answers back into a table. In and out — both ways.

The clean = (df.dropna(...).assign(...).loc[...]) block is a method chain: each step feeds the next. dropna(subset=["review"]) drops rows with no review, .assign(review=lambda d: d["review"].str.strip()) trims whitespace, and the .loc[lambda d: d["review"].str.len() > 0] keeps only non-empty reviews.
to_dict(orient="records") converts the two chosen columns into a list of dictionaries — one {"id": ..., "review": ...} per row. That shape is exactly what you loop over to send to a classifier (Chapter 2).
pd.DataFrame([classify(r["review"]) for r in records]) runs each review through the model and packs the structured results into a new DataFrame.
clean.reset_index(drop=True).join(results) glues the model's answers back onto the cleaned rows, side by side, so you can analyze or chart them.

What the output means: Nothing prints here — this is a shaping fragment. The payoff is merged: your original clean rows with the model's predicted labels attached, ready to groupby and count or feed to a chart.

Try this: This exact in→out loop is the data half of the Data Analyst Agent project (P5). Try printing records[:2] to see the little dictionaries before they go to the model — that list is the hand-off point between Pandas and the LLM.

Pandas is the on-ramp and the off-ramp for LLM dataOn the way in, Pandas cleans and shapes raw data into the tidy records a prompt or batch job consumes (Chapter 2's classifier, or the Data Analyst project). On the way out, it collects the model's structured results back into a table you can aggregate and chart (B2). The Data Analyst Agent project (P5) is exactly this loop wrapped in an agent.

Common pitfalls advanced

Pitfall	Fix
Looping over rows to compute values	Vectorize — operate on whole columns
Transforming before inspecting	`head`/`info`/`describe`/`isna().sum()` first
Chained assignment (SettingWithCopy)	Assign through a single `.loc[mask, col]`
`and`/`or` in boolean masks	Use `&`/`\|` with parentheses
Wrong dtype (numbers as strings)	Check `info()`; cast with `astype`/`to_datetime`/`to_numeric`
Silently averaging over NaNs	Know your missing data; decide drop vs fill deliberately

Exercises advanced

Exercise B1.1 — Vectorize a loop

Context: The fastest way to feel why arrays beat loops is to time both on the same data. Once you see the gap on a few thousand rows, vectorization stops being advice and becomes a habit.

Your task: Take a list-of-dicts dataset and a Python for loop that computes a derived value per row, rewrite it as a single vectorized Pandas column assignment, and time both on a few thousand rows.

Requirements:

Start from the row-by-row loop version and reproduce its result
Replace it with one vectorized column assignment on a DataFrame
Time both approaches over a few thousand rows
Confirm the two produce identical values
Note the speed difference you observed

💡 Hint: A column-to-column arithmetic expression on a DataFrame is the vectorized equivalent of the per-row loop — no apply or iteration needed.

Exercise B1.2 — The full loop on a real CSV

Context: The real skill isn't any one step — it's running load → inspect → clean → transform → aggregate end to end and letting the inspect step surprise you before you trust an answer.

Your task: Grab any CSV and run the full loop — load, inspect, clean, transform, aggregate — ending in a groupby that answers one real question, and write down what the inspection revealed.

Requirements:

Load a real CSV and inspect it with something like info() and isna().sum()
Fix at least one issue the inspection surfaced (wrong dtype, nulls, or a typo category variant)
End with a groupby answering a concrete question (e.g. total spend per region)
Clean before aggregating so the answer is correct, not plausible-but-wrong
Record what isna().sum() showed and how you handled it

💡 Hint: The inspect step almost always exposes something — a wrong dtype or a category like "EU" vs "eu" — and catching it before the aggregate is the whole point.

Show what to look for

The inspect step almost always surprises you — a column that's the wrong dtype, unexpected nulls, or a category with a typo variant ("EU" vs "eu"). Handling those before aggregating is the difference between a correct answer and a plausible-but-wrong one.

Exercise B1.3 — Table → LLM → table

Context: This is the data half of the Data Analyst project: clean text, run it through a classifier, and fold the predictions back into the table so you can count them.

Your task: Clean a free-text column, turn the rows into records, run them through the Chapter 2 classifier, merge the predicted labels back into the DataFrame, and group by the label to count each class.

Requirements:

Clean the free-text column (reviews or tickets) before classifying
Convert the rows into records the classifier can consume
Merge the returned labels back onto the original DataFrame
groupby the predicted label to get per-class counts
End with a table showing the count of each predicted class

💡 Hint: Reuse the DataFrame-to-records bridge from the ladder, classify the records, then join the labels back on a stable row key before the final group-by.

🪜 Practice ladder beginner → industry

Six graded exercises, easy to real-world. Try each before opening its solution.

Exercise 1 · Vectorize a loop with NumPyBeginner

Context: Row-at-a-time Python loops are the first thing that buckles when a dataset grows from hundreds to millions of rows. NumPy pushes the same arithmetic down into optimized C, so the code reads closer to the math and runs far faster.

Your task: Convert a list of USD prices to EUR at a fixed rate using a NumPy array and a single vectorized multiply, then show the plain Python loop version for contrast.

Requirements:

Build a np.array of the prices and multiply by the scalar rate — no for loop over elements
The result is element-wise: every price is converted in one operation
Round the output for display (e.g. np.round(..., 2))
Also write the equivalent list-comprehension loop so the difference is visible
Confirm both produce the same numbers

💡 Hint: The whole conversion is one expression: array times scalar broadcasts across every element at once.

Show solution

NumPy applies the multiply element-wise across the whole array at once:

import numpy as np

usd = np.array([10.0, 25.5, 100.0, 3.75])
rate = 0.92                      # USD -> EUR
eur = usd * rate                 # vectorized: no Python loop
print(np.round(eur, 2))          # [ 9.2  23.46 92.   3.45]

# the loop version does the same thing, slower and noisier:
eur_loop = [p * rate for p in usd]
print([round(x, 2) for x in eur_loop])

Vectorized ops run in optimized C under the hood, so they’re faster and read closer to the math.

Exercise 2 · Clean a messy DataFrame columnIntermediate

Context: Real-world spreadsheet columns arrive as strings like "$1,200" with stray symbols and gaps. Turning that into trustworthy numbers is the unglamorous step that decides whether every downstream total is right or quietly wrong.

Your task: Given a DataFrame amount column of dollar-formatted strings with some missing values, parse it into a clean numeric column, treat missing as 0, and report the total.

Requirements:

Strip the $ and comma characters before converting
Coerce to numbers with errors="coerce" so bad/None values become NaN instead of raising
Fill the resulting NaN with 0, then cast to an integer column
Store the cleaned values in a new column (leave the raw one intact)
Print the DataFrame and the summed total

💡 Hint: Chain the steps on the string column: replace, then pd.to_numeric, then fillna, then astype — coercion is what keeps unparseable values from blowing up the pipeline.

Show solution

import pandas as pd
import numpy as np

df = pd.DataFrame({"amount": ["$1,200", "$350", None, "$45"]})

# strip $ and commas, coerce to number, fill missing, cast to int
df["amount_num"] = (
    df["amount"]
      .str.replace(r"[$,]", "", regex=True)   # "$1,200" -> "1200"
      .pipe(pd.to_numeric, errors="coerce")   # bad/None -> NaN
      .fillna(0)
      .astype(int)
)
print(df)
print("total:", df["amount_num"].sum())       # total: 1595

errors="coerce" turns unparseable values into NaN instead of raising, then fillna(0) makes the missing explicit — a safe cleaning pattern.

Exercise 3 · Group-by: the analytical workhorseAdvanced

Context: Nearly every analytical question — spend per region, latency per endpoint — is a group-by underneath. Split-apply-combine is the single most reused pattern in data work, and naming your aggregates keeps the output readable.

Your task: Given order rows with a region and a revenue column, compute the total and mean revenue (and order count) per region, sorted by total descending, and state the split-apply-combine idea in one line.

Requirements:

Group by region and aggregate revenue in a single pass
Produce named outputs — total, mean, and count — as clean column names
Sort the summary by total revenue, highest region first
One-line explanation: split rows into groups, apply an aggregation, combine into one table
Print the resulting summary table

💡 Hint: groupby("region")["revenue"].agg(...) with keyword arguments gives you named columns in one call; sort the result afterward.

Show solution

Split-apply-combine: split rows into groups (by region), apply an aggregation to each, combine into one result table.

import pandas as pd

df = pd.DataFrame({
    "region":  ["US", "EU", "US", "APAC", "EU", "US"],
    "revenue": [100, 80, 120, 60, 40, 30],
})

summary = (
    df.groupby("region")["revenue"]
      .agg(total="sum", mean="mean", orders="count")
      .sort_values("total", ascending=False)
)
print(summary)
# region  total  mean  orders
# US        250  83.3       3
# EU        120  60.0       2
# APAC       60  60.0       1

.agg with named outputs gives clean column names in one pass.

Exercise 4 · Join two tables and handle the missesExpert

Context: Joining a fact table to a lookup table is routine — until a key has no match and a silent NaN hides a data-quality problem. A left join plus an explicit orphan flag turns that silent gap into something you can see and act on.

Your task: Left-join a customers name table onto an orders table by customer_id so no order is dropped, and flag any order whose customer is missing rather than letting it vanish.

Requirements:

Use a left join keyed on customer_id so every order survives
Fill missing names with a sentinel like <UNKNOWN>
Add a boolean orphan column that is true where the name was missing
Assert the number of orphaned orders matches expectation
Print the merged table so the unmatched id is visible

💡 Hint: A left join keeps all left rows and inserts NaN for unmatched right columns; convert that NaN into a named sentinel and a boolean flag.

Show solution

import pandas as pd

orders = pd.DataFrame({"order_id": [1, 2, 3], "customer_id": [10, 11, 99]})
customers = pd.DataFrame({"customer_id": [10, 11], "name": ["Ana", "Ben"]})

merged = orders.merge(customers, on="customer_id", how="left")   # keep all orders
merged["name"] = merged["name"].fillna("<UNKNOWN>")
merged["orphan"] = merged["name"] == "<UNKNOWN>"
print(merged)
#    order_id  customer_id      name  orphan
# 0         1           10       Ana   False
# 1         2           11       Ben   False
# 2         3           99  <UNKNOWN>    True
assert merged["orphan"].sum() == 1

A left join keeps every order; the orphan flag surfaces the unmatched customer_id (99) instead of letting a NaN hide a data-quality problem.

Exercise 5 · Turn a DataFrame into LLM-ready recordsProfessional

Context: An LLM or tool-calling step wants clean JSON records, not a DataFrame. Selecting, renaming to snake_case, and dropping rows that are missing a required field is the bridge between tabular data and a well-grounded prompt.

Your task: Turn a DataFrame with human-friendly column names into a list of JSON-serializable dicts: rename columns to snake_case, drop rows missing the required field, and emit the records.

Requirements:

Rename the display columns (e.g. Ticket ID) to snake_case keys
Drop rows where the required field (e.g. subject) is missing
Convert to a list of dicts with to_dict(orient="records")
Serialize the records to JSON to prove they are clean
Assert every emitted record actually has a non-empty required field

💡 Hint: to_dict(orient="records") is the tabular-to-JSON hop; do the dropna(subset=[...]) first so no ungrounded row reaches the model.

Show solution

import pandas as pd
import json

df = pd.DataFrame({
    "Ticket ID": [1, 2, 3],
    "Subject":   ["Login fails", "Refund?", None],
    "Priority":  ["high", "low", "high"],
})

clean = (
    df.rename(columns={"Ticket ID": "id", "Subject": "subject", "Priority": "priority"})
      .dropna(subset=["subject"])          # subject is required for the LLM
)
records = clean.to_dict(orient="records")  # list[dict], one per row
print(json.dumps(records, indent=2))
# [
#   {"id": 1, "subject": "Login fails", "priority": "high"},
#   {"id": 2, "subject": "Refund?",     "priority": "low"}
# ]
assert all("subject" in r and r["subject"] for r in records)

to_dict(orient="records") is the bridge from tabular data to the JSON most LLM/tool calls expect; dropping rows missing the required field prevents ungrounded/empty prompts downstream.

Exercise 6 · A reusable, tested cleaning pipelineIndustry scenario

Context: In a data pipeline the cleaning step is a function other stages depend on, so it needs a contract, defensive parsing, and a test that fails loudly when the shape breaks. This is how a one-off notebook cell becomes a shippable pipeline stage.

Your task: Wrap the ingest→clean→aggregate flow into a function that takes raw order dicts and returns per-region revenue totals as a dict, skipping any malformed rows.

Requirements:

The function has a docstring stating its input contract and output
Coerce revenue defensively (values may be strings or None)
Drop rows missing region or a valid revenue before aggregating
Handle the empty-input case by returning an empty dict, not crashing
Return {region: total} and include an assert that acts as a test

💡 Hint: Build a DataFrame from the rows, coerce and drop the bad ones, then groupby("region")["revenue"].sum().to_dict() — the assert doubles as your regression check.

Show solution

import pandas as pd

def region_totals(rows):
    # rows: list of dicts with 'region' and 'revenue' (revenue may be str/None).
    # Returns {region: total_revenue}, skipping rows we can't clean.
    df = pd.DataFrame(rows)
    if df.empty:
        return {}
    df["revenue"] = pd.to_numeric(df.get("revenue"), errors="coerce")
    df = df.dropna(subset=["region", "revenue"])          # drop malformed
    out = df.groupby("region")["revenue"].sum().astype(float)
    return out.to_dict()

data = [
    {"region": "US", "revenue": "100"},
    {"region": "US", "revenue": 50},
    {"region": "EU", "revenue": None},     # dropped
    {"region": None, "revenue": 10},       # dropped
    {"region": "EU", "revenue": "80"},
]
result = region_totals(data)
assert result == {"US": 150.0, "EU": 80.0}, result
print(result)   # {'US': 150.0, 'EU': 80.0}

Docstring (contract), defensive coercion, dropping malformed rows, and an assert that doubles as a test — the shape a pipeline step ships in.

✓ Checkpoint — you can move on when you can…

Say why arrays/DataFrames beat lists/dicts for data work.
Rewrite a row loop as a vectorized column operation.
Run load → inspect → clean → transform → aggregate on a real CSV.
Select/filter correctly with masks and .loc/.iloc, avoiding SettingWithCopy.
Shape a table into LLM-ready records and merge results back.

🏗️ Toward the capstone & projectsThe Data Analyst Agent (P5) turns plain-English questions into Pandas/SQL over a dataset — this chapter is the data engine underneath it. And any capstone metric ("deploy failure rate this week", "cost per incident") is a groupby away once the data's in a DataFrame. Clean data in, structured records out — then B2 charts it and B3/B4 serve it. See the Data Analyst Agent project →

Knowledge check check yourself

✓ Knowledge check

What is the "vectorized mindset" in Pandas/NumPy, and why is df["price"] * 1.1 better than a for loop over rows?

Show answer

It means operating on a whole column at once instead of looping over rows; the column operation is shorter and often 10–100× faster because the work runs in compiled C code rather than a Python loop.

✓ Knowledge check

What is the "SettingWithCopy" trap, and how do you correctly assign to a filtered subset?

Show answer

Chained assignment like df[df.x > 0]["y"] = 1 modifies a throwaway copy and silently fails; assign through a single .loc instead: df.loc[df.x > 0, "y"] = 1.

Educational content, provided as-is and without warranty. Code samples are examples — review, test, and adapt them before using in production. See the Terms of Use & Disclaimer. Use at your own risk.

Learning objectives

Why not just lists and dicts? essential

NumPy: the array engine essential

Pandas: the DataFrame essential

Lab B1.2 · The core loop: load → inspect → clean → transform → aggregate intermediate

Lab B1.3 · Selecting & filtering (and the gotchas) intermediate

Group-by: the analytical workhorse intermediate

Lab B1.4 · From messy data to LLM-ready records advanced

Common pitfalls advanced

Exercises advanced

Exercise B1.1 — Vectorize a loop

Exercise B1.2 — The full loop on a real CSV

Exercise B1.3 — Table → LLM → table

🪜 Practice ladder beginner → industry

✓ Checkpoint — you can move on when you can…

Knowledge check check yourself

More in Data & app building