Tabular Data with pandas

pandas is a free Python library for working with tables. If you have ever used a spreadsheet, you already understand the shape of the data it holds: rows and columns, with named headers across the top. What pandas adds is programmability. A spreadsheet makes you point and click at cells; pandas lets you describe an operation once and apply it to an entire column, or the whole table, in a single line - and then run that same instruction on next month's file without changing anything.

That difference matters most exactly where design work hurts: repetition and scale. Filtering a 12-row sample by hand is trivial; filtering, summing and re-sorting a 900-line BOQ every time the quantities change is not. pandas does both with the same effort, which is the leverage this whole course keeps returning to.

pandas is not part of Python's standard library, so you install it once. If you followed Module 3, the pattern is familiar:

# in a terminal, once per machine
# pip install pandas openpyxl

import pandas as pd  # the near-universal nickname

print(pd.__version__)

The import pandas as pd line is a convention so strong that nearly every example online uses pd; follow it and your code will read like everyone else's. openpyxl is the helper pandas uses to read and write Excel files - we install it now so Excel just works later. With that one import, the whole toolbox this module builds on is available.

It is worth being honest about where pandas fits. It is not a replacement for design thinking, and it is not always the right tool - a quick edit to a dozen rows is faster in a spreadsheet, and you should just do that. What pandas gives you is a way to make the same data operation once and trust it to run identically on a hundred rows or a hundred thousand, today and next month. That trustworthy repetition is the whole reason this module exists, and everything else - cleaning, scheduling, charting - is built on the humble import you just typed.

pandas gives you two objects, and almost everything you do is one of them. A DataFrame is the whole table: rows and columns, with a name on every column and an index label on every row. A Series is a single column pulled out of that table - one named strip of values, still carrying the same row index. Picture a room schedule: the entire schedule is a DataFrame; the area_sqm column on its own is a Series.

You can build a small DataFrame by hand to see the shape, which is handy for testing before you touch a real file:

import pandas as pd

rooms = pd.DataFrame({
    "room":     ["living", "kitchen", "bedroom"],
    "area_sqm": [24.0, 12.5, 15.0],
    "finish":   ["oak", "tile", "carpet"],
})
print(rooms)

Each key in that dictionary becomes a column; the lists become the values down each column. pandas prints it as a neat grid with an automatic index (0, 1, 2) down the left. Two things distinguish this from a plain list of lists. First, columns have names, so you ask for data by meaning (rooms["area_sqm"]) rather than by counting positions. Second, columns have types - pandas knows area_sqm holds numbers and finish holds text, and it will do arithmetic on the numbers and refuse it on the text, catching mistakes early. A DataFrame is, in short, a table that knows what it contains.

The row index deserves a word too, because it quietly makes pandas powerful. Every row carries a label - by default the integers 0, 1, 2, but it could be a room code, a drawing number, or a date - and that label travels with the data through every operation. When you pull out a column as a Series, it keeps its index; when you filter, the surviving rows keep their original labels rather than being renumbered. This means two Series drawn from the same table stay aligned by label, so rooms["length_m"] * rooms["width_m"] multiplies the right length by the right width for each room automatically. You rarely think about the index directly, but it is the machinery that lets you treat a table as one coherent object rather than a loose bag of columns.

In real work you rarely type the data; you read it from a file someone sent you. pandas reads the common formats with one function each, and hands you back a DataFrame:

import pandas as pd

schedule = pd.read_csv("room_schedule.csv")     # a CSV export
# schedule = pd.read_excel("boq.xlsx", sheet_name="Rooms")  # an Excel sheet

That is genuinely the whole job of loading data - read_csv for comma-separated files, read_excel for spreadsheets, each returning a DataFrame you can work on. Before doing anything else, though, look at what you loaded. Real files surprise you: extra header rows, blank lines, a stray text value in a number column. Three methods are your first reflex every time:

schedule.head()      # the first 5 rows - a quick eyeball
schedule.shape       # (rows, columns) - e.g. (412, 6)
schedule.info()      # column names, types, and how many values are missing

head() shows the top of the table so you can confirm it read correctly. shape tells you how big it is - a fast sanity check that you got 412 rooms, not 4. info() is the most useful of the three: it lists every column, the type pandas inferred, and the non-null count, which is how you spot missing data and columns that came in as text when they should be numbers. Building the habit of head() then info() before you compute anything will save you from confidently running numbers on data that was never what you assumed.

Most of what you do with a table is pick out a piece of it. pandas gives you a small, learnable set of ways to select, and they are worth getting straight early. To grab one column as a Series, index it by name; for several columns, pass a list of names and you get a smaller DataFrame back:

areas = schedule["area_sqm"]                 # one column -> a Series
subset = schedule[["room", "area_sqm"]]      # two columns -> a DataFrame

To select rows, you use .loc (by label) and .iloc (by position). And the move you will reach for constantly is the boolean filter: write a condition, and pandas keeps only the rows where it is true.

schedule.iloc[0]                             # the first row, by position
big = schedule[schedule["area_sqm"] > 15]    # only rooms over 15 sqm
living = schedule.loc[schedule["room"] == "living", "area_sqm"]

Read schedule["area_sqm"] > 15 on its own and you get a column of True/False - one answer per row. Put it back inside the brackets and pandas returns just the True rows. This is the pandas way of thinking: you describe a condition over the whole column at once, rather than writing a loop that checks rooms one by one. The same idea filters a 12-row sample and a 12,000-row register identically. Between column selection, .loc/.iloc, and boolean filters, you can carve any piece you need out of a table - which is the foundation the next lesson builds cleaning and grouping on top of.

The quiet shift pandas asks of you is to stop thinking row-by-row and start thinking whole-column. A spreadsheet trains you to fill one cell, then drag the formula down. pandas lets you say schedule["area_sqm"] * schedule["rate"] and multiply two entire columns element-by-element in one stroke - no dragging, no loop, no off-by-one at the bottom of the range. This is called vectorised thinking, and it is faster to write, faster to run, and far less error-prone than doing the arithmetic by hand.

It also makes your work repeatable. A spreadsheet's logic lives in cells you have to rebuild every time the source changes; a pandas script is a written recipe. Point it at next month's export and the same steps run again, identically, in a second. For a designer that means a schedule or a cost roll-up you produce once and then regenerate for free every revision - the difference between a deliverable that costs you an afternoon each cycle and one that costs you a keystroke.

You do not need to memorise pandas' full vocabulary; it is large, and even daily users look things up. What matters now is the mental model: a table is one object, columns are Series, and you operate on whole columns at once. Hold that, and the rest of this module - cleaning messy data, turning it into schedules and BOQs, and charting it - is variations on the moves you have just met.