Schedules, BOQs and Spreadsheets

Start from a file that already exists: a room schedule someone keeps in Excel, with a room name, a length and a width per row. Reading it is one line, but a real workbook has quirks - the data is on a named sheet, maybe with a title row above the headers - so read_excel gives you arguments to cope:

import pandas as pd

rooms = pd.read_excel(
    "project.xlsx",
    sheet_name="Rooms",   # the sheet, by name
    header=0,             # which row holds the column names
)
print(rooms.head())
print(rooms.info())

The head() and info() reflex matters even more here than with a clean CSV, because Excel is where mixed types hide - a length column with one cell reading approx 3.5 comes in as text, and every area you compute from it will fail silently. info() shows you that length_m is object (text) rather than float64 (number) before you trust it. If a column that should be numeric is not, pd.to_numeric(rooms["length_m"], errors="coerce") converts what it can and marks the rest as missing, so you can find and fix the offending cells. Reading is easy; reading and verifying is the professional habit, because a schedule built on a mistyped column is worse than no schedule at all - it looks authoritative and is wrong.

Real workbooks bring a few other quirks worth naming so they do not surprise you. Merged cells - common in hand-built schedules where someone merged the header across two columns - come into pandas as a value in the top-left cell and NaN in the rest, so a merged title row usually needs a skiprows or a manual clean. Trailing empty rows and a totals row at the bottom will be read as data, so it is common to slice them off or filter them out after loading. And the column names themselves often arrive with trailing spaces or inconsistent capitalisation - Area (SqM) versus area_sqm - which is why renaming to clean, predictable names right after the read pays for itself across the rest of the script. None of this is hard; it is just the reality of files made by people, and a minute spent tidying the structure up front saves an hour of confusion later.

With clean columns in hand, the calculations are the whole-column arithmetic from the last lesson, now doing real work. A room schedule wants area per room and a running total; a BOQ wants cost per line and a grand total:

rooms["area_sqm"] = rooms["length_m"] * rooms["width_m"]
rooms["area_sqm"] = rooms["area_sqm"].round(2)

total_area = rooms["area_sqm"].sum()
print(f"Total area: {total_area:.2f} sqm")

Each line operates on the entire column, so it does not matter whether the schedule has eight rooms or eight hundred. round(2) keeps the areas to a sensible two decimals - a small courtesy that stops a schedule showing 23.999999 square metres. For a bill of quantities the same shape gives you cost, and groupby gives you the subtotals a BOQ is organised around:

boq = pd.read_excel("project.xlsx", sheet_name="BOQ")
boq["amount"] = (boq["qty"] * boq["rate"]).round(2)
by_trade = boq.groupby("trade")["amount"].sum().round(2)
grand_total = boq["amount"].sum().round(2)

That is a complete quantity roll-up: a per-line amount, a subtotal per trade, and a grand total - the three numbers every bill of quantities exists to state. The point is not that any one calculation is hard; it is that all of them are now written down, so the schedule and the BOQ are computed the same way every time, and a rate change propagates through every dependent number the instant you re-run.

This is also where a designer's judgement stays firmly in charge. The script computes carpet area, but which walls and voids to deduct, whether balconies count at half, how to treat a double-height space - those are decisions you encode, and encoding them in code makes them explicit and reviewable rather than buried in a spreadsheet formula nobody remembers writing. A useful habit is to keep the raw source columns and add your computed ones alongside, never overwriting the inputs, so anyone can trace a final number back to where it came from. When a QS or an authority queries a figure, you can point at the exact line of code that produced it - a level of traceability a hand-built spreadsheet rarely offers, and one that quietly builds trust in your numbers.

A calculation nobody can open is not a deliverable. The move that makes this lesson practical is writing your finished DataFrame back out to a file colleagues use. pandas does it with one method each way:

rooms.to_csv("room_schedule_out.csv", index=False)
rooms.to_excel("room_schedule_out.xlsx", sheet_name="Rooms", index=False)

index=False is the argument to remember: without it, pandas writes its 0,1,2 row index as an extra unlabelled column that confuses everyone who opens the sheet. To write several tables into one workbook - the schedule on one tab, the trade subtotals on another - use an ExcelWriter:

with pd.ExcelWriter("deliverable.xlsx") as writer:
    rooms.to_excel(writer, sheet_name="Rooms", index=False)
    by_trade.to_frame("amount").to_excel(writer, sheet_name="By trade")
    boq.to_excel(writer, sheet_name="BOQ", index=False)

The with block opens the file, writes each tab, and closes it cleanly. Underneath, pandas uses openpyxl to speak Excel's format - the helper you installed in lesson 4.1 - and for finer control (bold headers, column widths, number formats) you can reach into openpyxl directly on the same workbook. But for most deliverables, to_excel with sensible sheet names and index=False produces a clean, openable file. The full arc - read the source, compute, summarise, write the result - is now a single script, and that script is your schedule.

A couple of practical cautions make writing files safe. First, to_excel overwrites the target file without asking, so never write back onto the source workbook you read from - always write to a new, clearly-named output file so a bad run cannot destroy your input. Second, if the output file is already open in Excel when your script runs, the write will fail with a permission error; close it first. And when you want the styled, branded look a practice expects, the cleanest pattern is to keep a formatted template workbook - headers, fonts, column widths and a logo already set - and have the script fill only the data cells, leaving the presentation untouched. That separation keeps the calculation logic in pandas and the visual polish in a file a non-coder on your team can maintain.

Put the three moves together and you have a small program that turns a source file into a finished, multi-tab workbook. Written one clear step at a time, it reads like the process it automates:

import pandas as pd

# 1. read
rooms = pd.read_excel("project.xlsx", sheet_name="Rooms")
rooms["length_m"] = pd.to_numeric(rooms["length_m"], errors="coerce")
rooms = rooms.dropna(subset=["length_m", "width_m"])

# 2. compute
rooms["area_sqm"] = (rooms["length_m"] * rooms["width_m"]).round(2)
by_floor = rooms.groupby("floor")["area_sqm"].sum().round(2)

# 3. write
with pd.ExcelWriter("schedule_out.xlsx") as writer:
    rooms.to_excel(writer, sheet_name="Rooms", index=False)
    by_floor.to_frame("area_sqm").to_excel(writer, sheet_name="By floor")

print(f"Wrote {len(rooms)} rooms, total {rooms['area_sqm'].sum():.2f} sqm")

Run it and a clean workbook appears with a Rooms tab and a By-floor summary. Nothing here is new - it is read, clean, compute, group and write, the moves of this whole module assembled into one useful thing. What changed is the stance: you are no longer poking at data to learn, you are producing a document. The honest caveat is that not every sheet is worth scripting - a one-off, ten-row table is faster to type in Excel, and you should. The script earns its place when the sheet is large, recomputed often, or error-prone by hand. When it does, this pattern turns an evening of careful, fragile spreadsheet work into a command you run in a second, the same correct way every time.

There is a compounding benefit that only shows up over a project's life. The first time you script a schedule it may take longer than doing it by hand - you are debugging column names and chasing a mistyped cell. But a building project revises its schedules and quantities many times, and every revision after the first is nearly free: change the source, re-run, re-issue. Across a project that might mean twenty regenerations, the script wins many times over, and it wins on correctness as much as speed, because the twentieth revision is computed by exactly the same logic as the first, with no fresh chance for a dragged formula to break. The setup cost is paid once; the correctness is banked on every revision that follows, which is precisely the kind of steady, unglamorous return that makes scripting worth learning for real project work rather than for its own sake. That is the real argument for scripting your paperwork: not that it is clever, but that it makes a repeated, error-prone job both faster and more trustworthy every time it comes round again.