Skip to content

4. Aggregate

Turn the enriched invoices into a city report for baskets with at least eight items. Keep invoice counts, revenue, and the city target together. The complete script repeats chapters 1–3 first, so it runs on its own.

bulk = enriched.filter(ff.col("quantity") >= 8)
summary = bulk.group_by("city").agg(
    ff.col("invoice_id").count().alias("orders"),
    ff.col("revenue").sum().alias("revenue"),
    ff.col("target").first().alias("target"),
)
report = summary.with_columns((ff.col("target") - ff.col("revenue")).alias("shortfall")).sort("city")

group_by("city").agg(...) reduces many invoice rows to one row per city. Revenue is additive; the target is repeated on every invoice, so take its first value. This is valid because chapter 3 established one target per city.

shortfall = target - revenue: positive means below target; negative means above it. These are planning exceptions, not evidence of bad transactions.

Grouping transforms data

group_by() chooses which rows contribute to each aggregate. It does not decide whether a later writer or subflow runs. A Gate makes that execution decision in chapter 6.

Check it

Print the graph, then collect the report:

report.flow_graph.print_tree()
print(report.collect())

The script prints:

Output
● Read data (11)  Supermarket invoices
│
● Drop duplicates (12)
│
● Filter data (13)
│
● Formula (14)
├─┐
● │ Filter data (15)
│ ● Filter data (16)
├─┘
● Union data (17)
│ ● Manual input (18)
├─┘
● Join (19)
│
● Filter data (20)
│
● Group by (21)
│
● Formula (23)
│
● Sort data (24)
shape: (5, 5)
┌───────────┬────────┬──────────┬─────────┬───────────┐
│ city      ┆ orders ┆ revenue  ┆ target  ┆ shortfall │
│ ---       ┆ ---    ┆ ---      ┆ ---     ┆ ---       │
│ str       ┆ u32    ┆ f64      ┆ f64     ┆ f64       │
╞═══════════╪════════╪══════════╪═════════╪═══════════╡
│ Bago      ┆ 59     ┆ 28592.7  ┆ 30000.0 ┆ 1407.3    │
│ Mandalay  ┆ 73     ┆ 36937.24 ┆ 35000.0 ┆ -1937.24  │
│ Naypyitaw ┆ 51     ┆ 23733.16 ┆ 25000.0 ┆ 1266.84   │
│ Taunggyi  ┆ 63     ┆ 32710.95 ┆ 30000.0 ┆ -2710.95  │
│ Yangon    ┆ 68     ┆ 34096.0  ┆ 35000.0 ┆ 904.0     │
└───────────┴────────┴──────────┴─────────┴───────────┘

The last four nodes are this chapter's additions: Filter data, Group by, the Formula that computes shortfall, and Sort data. In the report, Bago, Naypyitaw, and Yangon have a positive shortfall, so they are below target.

Complete script

Show the complete runnable script
tutorial_04.py
"""Aggregate the enriched invoices into a city revenue report."""

import flowfile as ff

# Chapters 1-3: read, clean, and enrich the invoices.
SALES = "https://raw.githubusercontent.com/edwardvaneechoud/flowfile/main/data/templates/supermarket_sales.csv"
sales = ff.read_csv(SALES, description="Supermarket invoices")

valid = sales.unique().filter(flowfile_formula="[quantity] > 0 and [unit_price] > 0")
orders = valid.with_columns(flowfile_formulas=["[unit_price] * [quantity]"], output_column_names=["revenue"])

members = orders.filter(ff.col("customer_type") == "Member")
regulars = orders.filter(ff.col("customer_type") == "Normal")
combined = ff.concat([members, regulars], how="diagonal_relaxed")

targets = ff.from_dict(
    {
        "city": ["Bago", "Mandalay", "Naypyitaw", "Taunggyi", "Yangon"],
        "target": [30000.0, 35000.0, 25000.0, 30000.0, 35000.0],
    }
)
enriched = combined.join(targets, on="city", how="left")

# Chapter 4: one report row per city.
bulk = enriched.filter(ff.col("quantity") >= 8)

summary = bulk.group_by("city").agg(
    ff.col("invoice_id").count().alias("orders"),
    ff.col("revenue").sum().alias("revenue"),
    ff.col("target").first().alias("target"),
)
report = summary.with_columns((ff.col("target") - ff.col("revenue")).alias("shortfall")).sort("city")

report.flow_graph.print_tree()
print(report.collect())

Download tutorial_04.py

Recap

You have five report rows from 314 qualifying invoices. Three cities fall below their tutorial targets. Next, make the basket threshold an input to the flow.

Next: Flow parameters