Cleaning and Transforming Data

The first thing you usually do to a fresh table is narrow it and order it. You met the boolean filter last lesson; in real cleaning you often combine conditions, and the two rules to remember are that pandas uses & for and and | for or, and each condition needs its own parentheses:

import pandas as pd

df = pd.read_csv("ffe_list.csv")

joinery = df[(df["category"] == "joinery") & (df["qty"] > 0)]
flagged = df[(df["status"] == "TBC") | (df["cost"].isna())]

Forget a set of parentheses and Python raises an error, which is annoying but honest - it is telling you the condition was ambiguous. Once you have the rows you want, sorting puts the interesting ones on top:

df = df.sort_values("area_sqm", ascending=False)   # largest rooms first
df = df.sort_values(["floor", "area_sqm"])          # by floor, then area

sort_values takes one column or a list of columns, and ascending flips the direction. Sorting does not change the data, only the order you view it in, which makes it a safe, fast way to sanity-check a table: sort by area and the impossibly large or clearly-zero rooms jump to the ends where you can spot the errors. Filtering and sorting together are how you go from a wall of rows to the specific, ordered slice a question needs - the same two moves whether the table has twelve rows or twelve thousand.

Real tables have holes: a cost not yet quoted, an area left blank, a supplier unknown. pandas marks these as NaN (not a number), and the important thing is that a missing value is a decision, not just a nuisance - you have to choose what it means for your calculation. First, find them:

df.isna().sum()          # count of missing values per column

That one line is the fastest audit of a file's completeness you will find. Then you choose. Sometimes the right move is to drop the incomplete rows; sometimes it is to fill the gaps with a sensible default:

quoted = df.dropna(subset=["cost"])         # keep only rows that have a cost
df["cost"] = df["cost"].fillna(0)           # or treat missing cost as zero
df["supplier"] = df["supplier"].fillna("TBC")

Which is correct depends entirely on the question. If you are totalling committed cost, dropping unquoted items is honest; if you are showing a worst-case budget, filling with an estimate is better; filling a missing cost with zero is fine for a placeholder but dangerous in a total, because it silently understates the number. There is no universal right answer, and that is the point: the value of doing this in code is that your choice is written down and repeatable, so anyone (including future you) can see exactly how the gaps were handled rather than guessing at a spreadsheet full of blanks.

A subtle trap lives here. A column that looks numeric can secretly be text if even one cell holds something like TBC or n/a, and pandas will refuse to do arithmetic on it or will treat the whole column as strings. When that happens, pd.to_numeric(df["cost"], errors="coerce") is the reliable fix: it converts every value it can into a real number and turns the ones it cannot into NaN, which you then handle with the same drop-or-fill decision. So the honest workflow is often two steps - first coerce the column to numbers so the missing and the malformed both show up as NaN, then decide what those gaps mean. Doing it in that order means a stray TBC never silently poisons a total.

Most useful numbers are not in the file - they are one step away from it. A BOQ has quantity and rate but you need cost; a room list has length and width but you need area. Because pandas works on whole columns, you build a new column by writing the arithmetic once, over the entire Series:

df["cost"] = df["qty"] * df["rate"]
df["area_sqm"] = df["length_m"] * df["width_m"]
df["cost_per_sqm"] = df["cost"] / df["area_sqm"]

Each line multiplies or divides two columns element-by-element and stores the result as a brand-new column on the left. No loop, no dragging a formula down - one statement covers every row. When the logic is conditional rather than arithmetic, a common tool is numpy.where, which chooses per row:

import numpy as np
df["size_class"] = np.where(df["area_sqm"] > 15, "large", "small")

That reads almost like English: where the area is over 15, label it large, otherwise small. You can also standardise text while you are here - df["room"] = df["room"].str.lower().str.strip() collapses Kitchen, kitchen and kitchen into one consistent label, which matters enormously the moment you group. Computed columns are where a raw export becomes an analysis: you are no longer just holding the data someone gave you, you are deriving the quantities your deliverable is actually about.

Two more everyday transforms round this out. Renaming columns with df.rename(columns={"Area (SqM)": "area_sqm"}) gives you clean, code-friendly names to work with instead of whatever the source file happened to use, which matters because you will type these names constantly. And dropping duplicate rows with df.drop_duplicates() is the quiet fix for a table where the same line item was pasted in twice - a common and dangerous error in a BOQ, because a duplicated row inflates a total silently. None of these moves are glamorous, but together - rename, coerce types, fill or drop gaps, compute, de-duplicate - they are the difference between a number you can put your name to and one you are quietly hoping is right.

Here is the technique that earns its keep. A schedule has hundreds of line items, but a deliverable reports summaries: total area per floor, cost per category, count of items per supplier. `groupby` does exactly this - it splits the table into groups, applies a calculation to each, and combines the results back into a small table. The pattern is called split-apply-combine:

by_floor = df.groupby("floor")["area_sqm"].sum()
cost_by_cat = df.groupby("category")["cost"].sum().sort_values(ascending=False)
counts = df.groupby("supplier").size()

Read the first line as: group the rows by their floor value, take the area_sqm column, and sum it within each group. Out comes a tiny Series - one total per floor - from a table of hundreds of rooms. Swap sum for mean, max, count or size and you get the average area, the largest, the number of items. You can group by several columns at once (groupby(["floor", "category"])) and aggregate several columns together with .agg. This one method replaces the pivot table you would build by hand in a spreadsheet, and unlike the pivot table it re-runs itself the instant the source data changes. If you take one transforming move from this lesson into your work, make it groupby - it is the bridge from raw line items to the numbers a client actually reads.

One thing that trips up newcomers is what groupby hands back. Grouping by a single key and aggregating one column gives you a Series indexed by the group - the floors or trades become the index labels. That is often exactly what you want, but if you need it as a normal table again - to write it to Excel or chart it - .reset_index() turns the group labels back into an ordinary column, and .to_frame() turns a Series into a one-column DataFrame. When you want several summaries at once, .agg takes a dictionary: df.groupby("trade").agg({"cost": "sum", "qty": "sum", "item": "count"}) gives total cost, total quantity and a line count per trade in a single, readable call - the whole summary block of a BOQ in one statement.

In practice these moves are not separate - you chain them into a short pipeline that takes the raw file in at the top and hands clean, summarised data out the bottom. Written plainly, one step per line, it stays readable:

import pandas as pd

df = pd.read_csv("boq_raw.csv")
df["item"] = df["item"].str.lower().str.strip()   # standardise labels
df = df.dropna(subset=["qty", "rate"])            # drop incomplete lines
df["cost"] = df["qty"] * df["rate"]               # compute cost
totals = df.groupby("trade")["cost"].sum()        # summarise by trade
print(totals)

Five lines take a messy export to a cost-by-trade summary. The order matters - you standardise labels before grouping so RCC and rcc do not become two trades, and you compute cost after dropping the rows that cannot have one. Because it is written down, this is not a one-time clean-up; it is a recipe. When the quantities change next week you run the same five lines and get the updated totals, with every cleaning decision applied identically. That reproducibility is the real prize of doing this in code rather than by hand: not that any single clean is faster, but that you never have to remember how you did it, because the script is the memory.