Thinking in Rows and Columns

By the end of this lesson you can perform the four operations that make up most data work, in the right order, on a batch of records, and you can read a DataFrame expression and say what it does even though you are writing plain Python.

The shortlist is now a table: rows of opportunities, columns of fields. Almost every question a reviewer asks about it is some combination of four operations, and the answers are wrong in predictable ways when the operations happen in the wrong order.

There is a practical note about tooling. Pandas is the standard Python library for this work, and any local project you build should use it. It is not available in this page's Python runtime, which ships the standard library only. That is a deliberate trade: a small, fast, offline runtime for short exercises instead of a large download on a phone. So the examples here are plain Python, and each one is paired with the DataFrame expression it corresponds to, which is what you would write on your own machine. The reasoning transfers exactly; only the notation differs.

Four operations, ordered so each step still has the data it needs:

1. Select the columns you need. Keep any columns still needed for filtering or sorting, or make the reduced-column view a separate branch from the original rows.
2. Filter the rows you care about. Do this before sorting: sorting rows you are about to discard is wasted work, and on a large table it is the expensive step.
3. Sort by the column that expresses "most interesting first".
4. Limit to the top few.

In plain Python those are a comprehension that builds smaller dictionaries, a comprehension with a condition, sorted(rows, key=..., reverse=True), and a slice.

sorted takes a key function that returns the value to order by, and it returns a new list. The list method rows.sort() rearranges in place and returns None, which is Module 3's in-place-versus-new distinction appearing again. Prefer sorted when you will want the original order for a different question later, which is more often than you expect.

The DataFrame vocabulary, for reading code you will meet elsewhere:

• A DataFrame is the whole table; a Series is one column.
• df[["notice_id", "amount"]] selects columns; the comprehension equivalent builds smaller dictionaries.
• df[df["state"] == "OR"] filters rows. The inner part produces a column of True/False, and the outer part keeps the rows where it is True. That is the piece that looks strange at first and is worth naming: it is a boolean mask, not a comparison to a string.
• df.sort_values("amount", ascending=False) sorts; df.head(2) limits.

Two habits that survive the change of tool. Keep the unfiltered batch available, because "how many did we start with" is nearly always the next question. And name intermediate results, oregon, by_value, top_two, rather than chaining six operations into one line; a chain that produces a surprising answer has to be taken apart before it can be understood, and named steps are already apart.

rows = [
    {"notice_id": "A-1", "state": "OR", "amount": 310000, "agency": "GSA"},
    {"notice_id": "A-2", "state": "WA", "amount": 720000, "agency": "DLA"},
    {"notice_id": "A-3", "state": "OR", "amount": 505000, "agency": "GSA"},
]

columns = ["notice_id", "amount"]
selected = [{key: row[key] for key in columns} for row in rows]

oregon = [row for row in rows if row["state"] == "OR"]
by_value = sorted(oregon, key=lambda row: row["amount"], reverse=True)
top_one = by_value[:1]

print(selected[0])
print(len(oregon), "of", len(rows))
print([row["notice_id"] for row in by_value])
print(top_one[0]["notice_id"], top_one[0]["amount"])

The selected line is a comprehension inside a comprehension: the inner one builds a smaller dictionary per row, the outer one runs it over every row. In pandas this is df[["notice_id", "amount"]].

key=lambda row: row["amount"] tells sorted which value to order by. A lambda is a small unnamed function; lambda row: row["amount"] is the same as a def that takes one argument and returns that field.

Each result has a name. selected is a separate reduced-column view of rows; oregon also starts from rows, which still has state. The ranking chain is rows to oregon to by_value to top_one, not selected to oregon.

Predict all four printed lines.

{'notice_id': 'A-1', 'amount': 310000}
2 of 3
['A-3', 'A-1']
A-3 505000

Remove reverse=True from the sorted call and predict lines 3 and 4.

['A-1', 'A-3']
A-1 310000

An assistant was asked for the three largest Oregon opportunities. It produced this and said it returns them largest first.

rows = [
    {"notice_id": "A-1", "state": "OR", "amount": 310000},
    {"notice_id": "A-2", "state": "WA", "amount": 720000},
    {"notice_id": "A-3", "state": "OR", "amount": 505000},
    {"notice_id": "A-4", "state": "OR", "amount": 128000},
]

top = rows.sort(key=lambda row: row["amount"], reverse=True)
top_three = top[:3]
oregon = [row for row in top_three if row["state"] == "OR"]

print([row["notice_id"] for row in oregon])

oregon = [row for row in rows if row["state"] == "OR"]
by_value = sorted(oregon, key=lambda row: row["amount"], reverse=True)
top_three = by_value[:3]

Five rows, one column to filter on, another to rank by. Report the two largest Oregon opportunities, largest first.

Loading this exercise…

Optional: add a second sort key so that ties are broken predictably, for example by amount descending and then by notice id ascending. A key returning a tuple sorts by the first element, then the second, which is one of the more useful things to know about sorted. Then say in one sentence why an unstable tie-break makes a report hard to trust across runs. Reports that reorder themselves for no visible reason quietly destroy a reviewer's confidence in everything else on the page.

Sign in to track your progress on this exercise.

1. Why filter before sorting rather than after?
2. What is the difference between sorted(rows, ...) and rows.sort(...)?
3. What does df[df["state"] == "OR"] do, and what is the inner expression?
4. Why is "top three, then filter" different from "filter, then top three"?

Sign in to track your progress on this exercise.

Retain fields needed by later operations, filter before the top-result limit, sort before slicing for rank, and keep a separate selected-column view when needed; sorted returns a new list while sort returns None; name the intermediate steps; and a ranked list means nothing without the filter that produced it. Next: grouping, where one batch becomes one row per agency and every number needs its denominator stated.