Grammar of Dataframes

The previous lesson brought the declarative expression down to a single statement and counted its price: a dictionary of four keys wrote four decisions and left five decisions to defaults. That dictionary had a second shortcoming. An analysis is not only a definition, it is an order: first these rows are discarded, then this column is derived, then it is grouped. A key-value dictionary never writes this order.

This lesson measures the form in between. The grammar of dataframes writes the transformation as a chain made of individual verbs: filter, add, group, summarize, sort, first. The measure is two numbers: with how many statements and how many intermediate variables the same transformation is written, and which intermediate result becomes invisible when the chain gets shorter.

• AE5. The dataset is inherited from Data Collection and Preparation and is fiction: 3,199 rows, 1,329 subscribers, 10/25/40 m³ tariff tiers. The seed is written in the code.
• AE6. The verb chain is modeled with the standard library. Frame is a small model: every verb returns a new frame, and no verb changes its own input. No tool is imitated.
• AE7. The measured transformation consists of seven verbs: filter the read rows, add the excess over 25 m³, filter the rows whose excess is positive, group by region, summarize the mean excess, sort in descending order, take the first three.
• AE8. Two numbers stand side by side. The gain is the intermediate variable and statement count. The hidden default has two headings: in the chain the row counts of the intermediate results are invisible, and even though the verb order is written in the code, the reasoning of the decision that chose the order is not written anywhere.

Seven Verbs, Three Writings

# grammar.py — MODEL: the 3,199-row fiction dataset of Data Collection and
# Preparation is regenerated from the same seed, then the verb-based chain is
# modeled with the standard library.
import ast
import inspect
import math

SEED, RAW, M32 = 20260218, 1400, 0xFFFFFFFF
PERIODS, TARIFF = ["2026-01", "2026-02", "2026-03"], [10, 25, 40]
REGION = [("north", 0.28, 21), ("south", 0.22, 17), ("east", 0.18, 26),
          ("west", 0.14, 14), ("central", 0.18, 23)]


def rng(t: int):
    x = ((t ^ (t >> 16)) * 2246822507) & M32
    x = ((x ^ (x >> 13)) * 3266489909) & M32
    s = [(x ^ (x >> 16)) & M32]

    def next_value() -> float:
        s[0] = (s[0] * 1664525 + 1013904223) & M32
        return s[0] / 4294967296
    return next_value


def pick(u: float, w: list[float]) -> int:
    t = 0.0
    for i, x in enumerate(w):
        t += x
        if u < t:
            return i
    return len(w) - 1


_subscribers, _dropped = [], []
for i in range(RAW):
    r = rng(SEED + i)
    b = REGION[pick(r(), [x[1] for x in REGION])]
    record = {"subscriber_id": 10001 + i, "region": b[0], "base": b[2],
              "household_size": pick(r(), [0.06, 0.24, 0.30, 0.24, 0.11, 0.05]),
              "satisfaction": pick(r(), [0.08, 0.14, 0.27, 0.34, 0.17]) + 1}
    (_dropped if r() < 0.046 else _subscribers).append(record)
_valid = {k["subscriber_id"] for k in _subscribers}
READINGS = []
for k in _subscribers + _dropped:
    r = rng(SEED + 7000 + k["subscriber_id"])
    for d in range(pick(r(), [0.05, 0.12, 0.21, 0.62])):
        t = 0.0 if r() < 0.038 else math.floor(
            k["base"] * math.exp((r() + r() + r() - 1.5) * 0.62) * (1 - d * 0.05) * 100
            + 0.5) / 100
        if k["subscriber_id"] not in _valid:
            r()
            continue
        READINGS.append({"subscriber_id": k["subscriber_id"], "region": k["region"],
                         "period": PERIODS[d], "consumption_m3": t,
                         "satisfaction": k["satisfaction"],
                         "tier": 1 if t < TARIFF[0] else 2 if t < TARIFF[1]
                         else 3 if t < TARIFF[2] else 4})
        r()


class Frame:
    """MODEL of the dataframe grammar: every verb returns a new frame."""

    def __init__(self, rows, key=None, trace=None):
        self.rows, self.key = rows, key
        self.trace = [] if trace is None else trace

    def filter(self, condition):
        return Frame([r for r in self.rows if condition(r)], self.key, self.trace)

    def add(self, name, f):
        return Frame([{**r, name: f(r)} for r in self.rows], self.key, self.trace)

    def group(self, name):
        return Frame(self.rows, name, self.trace)

    def summarize(self, name, f):
        bucket = {}
        for r in self.rows:
            bucket.setdefault(r[self.key], []).append(r)
        return Frame([{self.key: g, name: f(v)} for g, v in bucket.items()], None, self.trace)

    def sort(self, name, descending=True):
        return Frame(sorted(self.rows, key=lambda r: r[name], reverse=descending),
                     self.key, self.trace)

    def first(self, n):
        return Frame(self.rows[:n], self.key, self.trace)

    def count(self, label):
        self.trace.append((label, len(self.rows)))
        return Frame(self.rows, self.key, self.trace)


VERBS = ("filter", "add", "group", "summarize", "sort", "first")
# The function form of the same verbs, for the nested writing.
def filter(c, k): return c.filter(k)
def add(c, a, f): return c.add(a, f)
def group(c, a): return c.group(a)
def summarize(c, a, f): return c.summarize(a, f)
def sort(c, a): return c.sort(a)
def first(c, n): return c.first(n)
# The conditions and the measure of the transformation.
def was_read(r): return r["consumption_m3"] > 0
def excess(r): return r["consumption_m3"] - 25
def positive(r): return r["excess_m3"] > 0
def mean_excess(v): return sum(r["excess_m3"] for r in v) / len(v)


SOURCE = Frame(READINGS)


def with_intermediates(c):
    read_rows = c.filter(was_read)
    with_excess = read_rows.add("excess_m3", excess)
    over = with_excess.filter(positive)
    grouped = over.group("region")
    summary = grouped.summarize("mean_excess", mean_excess)
    ordered = summary.sort("mean_excess")
    return ordered.first(3)


def nested(c):
    return first(sort(summarize(group(filter(add(
        filter(c, was_read), "excess_m3", excess), positive), "region"),
        "mean_excess", mean_excess), "mean_excess"), 3)


def chained(c):
    return (c
            .filter(was_read)
            .add("excess_m3", excess)
            .filter(positive)
            .group("region")
            .summarize("mean_excess", mean_excess)
            .sort("mean_excess")
            .first(3))


def tree(f):
    return ast.parse(inspect.getsource(f)).body[0]


def verb_name(n):
    if not isinstance(n, ast.Call):
        return None
    name = n.func.attr if isinstance(n.func, ast.Attribute) else getattr(n.func, "id", None)
    return name if name in VERBS else None


# For every verb call: in which statement, how many verb calls it contains, where in the code.
def calls(f):
    g, out = tree(f), []
    for i, statement in enumerate(g.body):
        for n in ast.walk(statement):
            if verb_name(n):
                inner = sum(1 for m in ast.walk(n) if m is not n and verb_name(m))
                out.append({"statement": i, "inner": inner,
                            "position": (n.func.end_lineno, n.func.end_col_offset)})
    return out


def name_count(f):
    return len({n.id for n in ast.walk(tree(f))
                if isinstance(n, ast.Name) and isinstance(n.ctx, ast.Store)})


# Reading order is read from the position in the code, execution order from the nesting.
def overlap(f):
    c = calls(f)
    reading = sorted(range(len(c)), key=lambda j: c[j]["position"])
    execution = sorted(range(len(c)), key=lambda j: (c[j]["statement"], c[j]["inner"]))
    return sum(1 for a, b in zip(reading, execution) if a == b), len(c)


def align(w):
    return lambda h: "".join(str(v).ljust(w[0]) if j == 0 else str(v).rjust(w[j])
                             for j, v in enumerate(h))


FORMS = [("with intermediates", with_intermediates), ("nested call", nested),
         ("verb chain", chained)]
y = align([20, 11, 8, 23, 19, 27])
print(y(["form", "statement", "verb", "intermediate variable", "reading=execution",
         "named intermediate result"]))
for name, f in FORMS:
    n, t = overlap(f)
    print(y([name, len(tree(f).body), t, name_count(f), f"{n}/{t}", f"{name_count(f)}/{t - 1}"]))
results = [[(r["region"], round(r["mean_excess"], 3)) for r in f(SOURCE).rows]
           for _, f in FORMS]
print(f"\nfictional dataset {len(READINGS)} rows; the three writings give the same result: "
      f"{'yes' if all(s == results[0] for s in results) else 'no'}")
print("  " + ", ".join(f"{b} {v}" for b, v in results[0]))

form                  statement    verb  intermediate variable  reading=execution  named intermediate result
with intermediates            7       7                      6                7/7                        6/6
nested call                   1       7                      0                1/7                        0/6
verb chain                    1       7                      0                7/7                        0/6

fictional dataset 3199 rows; the three writings give the same result: yes
  east 7.301, central 6.532, north 4.986

The three writings call the same seven verbs and return the same three rows. Where they part is visible in the first three columns. The writing with intermediate variables binds every step to a name: seven statements, six intermediate variables. The nested call and the verb chain are both a single statement and bind no names.

That all three writings give the same result comes from a property of the model: no verb changes its own input, each returns a new frame. This is the precondition for the chain to be built at all. When a verb that changes its input in place is attached to a chain, a second expression looking at the same frame silently reads a different result; in the writing with intermediate variables this at least shows up as the overwriting of a name, in the chain it does not show up at all.

The fourth column separates these two. Reading order is read from the position in the code — which verb name is written earlier on the page. Execution order comes from the nesting — the innermost call runs first. In the writing with intermediate variables the two overlap in all seven steps, because each statement takes the result of the one before. In the verb chain they also overlap in seven steps: since the chain walks from left to right and from top to bottom, the order read is the order run. In the nested call the overlap falls to one in seven. The first that is read first on the page runs last, the filter that is read last runs first; only the group in the middle is fourth in both orders. This is the gain of the chain: the same single-statement writing removes reading in reverse.

The Chain That Leaves a Trace

The last column gives the price. In the writing with intermediate variables, six of the six intermediate results are bound to a name; the row count of any of them can be read the moment it is wanted. In the chain this number is zero. The chain runs as a whole and nothing stops in between; whether the filter left 3,000 rows or 3 rows cannot be known without looking at the result. This is the decision the grammar hides: how many rows were dropped in which step. In the model, the count verb gives this back.

traced = (Frame(READINGS, None, []).count("input")
          .filter(was_read).count("filter read")
          .add("excess_m3", excess).count("add excess_m3")
          .filter(positive).count("filter excess>0")
          .group("region").count("group region")
          .summarize("mean_excess", mean_excess).count("summarize mean")
          .sort("mean_excess").count("sort descending")
          .first(3).count("first 3"))
y2 = align([20, 10, 10])
print(y2(["stage", "rows", "change"]))
previous = None
for label, n in traced.trace:
    print(y2([label, n, "-" if previous is None else n - previous]))
    previous = n
print(f"\ntrace taken with {len(traced.trace)} count calls: verb 7 -> calls {7 + len(traced.trace)}, "
      f"visible intermediate result 0/6 -> 6/6")

stage                     rows    change
input                     3199         -
filter read               3080      -119
add excess_m3             3080         0
filter excess>0            750     -2330
group region               750         0
summarize mean               5      -745
sort descending              5         0
first 3                      3        -2

trace taken with 8 count calls: verb 7 -> calls 15, visible intermediate result 0/6 -> 6/6

The sequence tells what the chain does, row by row. The first filter drops 119 rows — the number of unread records, the same as in the previous lesson. add touches no row, it only adds a column. The second filter drops 2,330 rows, and the real narrowing is here: of 3,080 readings only 750 are above 25 m³. Summarizing melts 745 rows down to five regions, and the last verb cuts two of them.

The price is visible too: 7 verbs become 15 calls. The visibility of the intermediate result goes from 0/6 to 6/6, and its counterpart is eight extra calls. The readability of the chain and the visibility of the intermediate result are not obtained at the same time; one of the two is bought with eight calls. Said as a number: the price of visibility per verb is slightly more than one call, and in a chain that is not traced a drop of 2,330 rows passes without producing any warning. The trace has one more side gain: the zero changes at the group and sort stages show with numbers that those two verbs never touch the row count. Knowing which verbs of a chain produce or drop rows brings the places to look at when searching for an error from seven down to two.

The Order of the Verbs Is a Decision

The verbs of the chain are written in the code and their order is visible. What is not visible is why that order is that order. Below, the same six verbs are written in two different orders: once with the filter before summarizing, once with the filter after summarizing.

def filter_before(c):
    return (c.filter(was_read).add("excess_m3", excess).filter(positive)
            .group("region").summarize("mean_excess", mean_excess)
            .sort("mean_excess"))


def filter_after(c):
    return (c.filter(was_read).add("excess_m3", excess)
            .group("region").summarize("mean_excess", mean_excess)
            .filter(lambda r: r["mean_excess"] > 0).sort("mean_excess"))


ORDERS = [("filter before summarizing", filter_before), ("filter after summarizing", filter_after)]
y3 = align([26, 8, 14, 12, 12])
print(y3(["verb order", "verb", "output rows", "east m3", "total m3"]))
outcomes = []
for name, f in ORDERS:
    result_frame = f(Frame(READINGS, None, []))
    d = {r["region"]: r["mean_excess"] for r in result_frame.rows}
    outcomes.append((len(result_frame.rows), d.get("east", 0.0)))
    print(y3([name, len(calls(f)), len(result_frame.rows), f"{d.get('east', 0):.3f}",
              f"{sum(d.values()):.3f}"]))
print(f"\nthe same {len(calls(filter_before))} verbs, the position of a single verb changed: "
      f"reported region count {outcomes[0][0]} -> {outcomes[1][0]}, "
      f"east mean {outcomes[0][1]:.3f} -> {outcomes[1][1]:.3f} m3")

verb order                    verb   output rows     east m3    total m3
filter before summarizing        6             5       7.301      24.539
filter after summarizing         6             1       0.593       0.593

the same 6 verbs, the position of a single verb changed: reported region count 5 -> 1, east mean 7.301 -> 0.593 m3

The six verbs are the same, one changed place, and the report says something entirely different. In the first order the filter is applied to the rows: the region means of the 750 readings with an excess are computed, and 7.301 m³ comes out for east. In the second order the filter is applied to the summary: the region mean of all the readings is taken — negative excesses are counted too — and then the regions whose mean stays positive are kept. Of the five regions only east is left standing, and its number falls to 0.593 m³.

Neither number is wrong; they are answers to two different questions. The first is the answer to “how large is the mean excess of the readings that exceed”, the second to “which region’s average subscriber exceeds the tier”. What the grammar gives is the ability to write both with two lines of code; what it does not give is a record of which one was asked. The chain writes the order, it does not write the reasoning of the order; the rule of the course applies here as well — not a verb whose position is unwritten, but an order whose reasoning is unwritten counts as unmeasured.

Summary

• The same seven verbs give the same result in three writings: east 7.301 — central 6.532 — north 4.986 m³ mean excess.
• The writing with intermediate variables holds 7 statements and 6 intermediate variables, the nested call and the verb chain 1 statement and 0 intermediate variables.
• Reading order overlaps with execution order in 7/7 steps in the writing with intermediate variables, 7/7 in the chain and 1/7 in the nested call; the gain of the chain is that it removes reading in reverse.
• In the chain the row counts of 0 of 6 intermediate results are visible; when the tracing verb is added the calls rise from 7 to 15 and the sequence 3,199 → 3,080 → 750 → 5 → 3 becomes readable. The second filter alone drops 2,330 rows.
• When the position of one of the same six verbs changes, the number of reported regions falls from 5 to 1 and the east mean from 7.301 m³ to 0.593 m³.

Next Step

The transformation was built from verbs and each verb took the output of the one before. The same thought shows up in visualization as well: a chart is not a single call but is made of layers placed on top of one another — data, aesthetic, geometry, statistic, scale. The next lesson counts this: when the same chart is built with a convenience wrapper, how many independent arguments it takes; when it is built with layers, how many layers it holds; and when one more series is added on top, how many layers are reused. The hidden default stands beside it: the statistic a layer applies silently.