Skip to content

3. Combine

Extend the cleaned invoices with a target for each city. First separate and recombine the two customer segments to model two incoming feeds, using the same CSV throughout. The complete script includes the earlier steps.

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")

The targets are tutorial planning inputs, not values supplied by the CSV.

A union stacks invoice rows. The two filters here are disjoint and cover the sample's Member and Normal customer types. ff.concat(..., how="diagonal_relaxed") places a native Union node; it matches columns by name and can accommodate different column sets.

A join adds columns by matching keys. This left join keeps the invoices and attaches the matching city's target. Each city appears once in targets, so each invoice matches exactly one target.

Check the lookup's grain

Two target rows for one city would match each invoice twice and inflate the later revenue sum. A left join also leaves a null target for any city missing from the lookup, which should be resolved before comparing revenue with targets.

Check it

enriched.flow_graph.print_tree()
result = enriched.select("invoice_id", "city", "customer_type", "revenue", "target").sort("invoice_id").collect()
print(result)
print(f"Invoices without a target: {result['target'].null_count()}")

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)
shape: (1_000, 5)
┌────────────┬───────────┬───────────────┬─────────┬─────────┐
│ invoice_id ┆ city      ┆ customer_type ┆ revenue ┆ target  │
│ ---        ┆ ---       ┆ ---           ┆ ---     ┆ ---     │
│ str        ┆ str       ┆ str           ┆ f64     ┆ f64     │
╞════════════╪═══════════╪═══════════════╪═════════╪═════════╡
│ INV-00001  ┆ Mandalay  ┆ Member        ┆ 40.41   ┆ 35000.0 │
│ INV-00002  ┆ Taunggyi  ┆ Normal        ┆ 638.9   ┆ 30000.0 │
│ INV-00003  ┆ Bago      ┆ Member        ┆ 391.59  ┆ 30000.0 │
│ INV-00004  ┆ Naypyitaw ┆ Member        ┆ 173.16  ┆ 25000.0 │
│ INV-00005  ┆ Bago      ┆ Normal        ┆ 448.96  ┆ 30000.0 │
│ …          ┆ …         ┆ …             ┆ …       ┆ …       │
│ INV-00996  ┆ Bago      ┆ Normal        ┆ 514.71  ┆ 30000.0 │
│ INV-00997  ┆ Taunggyi  ┆ Member        ┆ 536.1   ┆ 30000.0 │
│ INV-00998  ┆ Bago      ┆ Normal        ┆ 288.33  ┆ 30000.0 │
│ INV-00999  ┆ Taunggyi  ┆ Member        ┆ 846.9   ┆ 30000.0 │
│ INV-01000  ┆ Yangon    ┆ Normal        ┆ 451.2   ┆ 35000.0 │
└────────────┴───────────┴───────────────┴─────────┴─────────┘
Invoices without a target: 0

The two customer filters branch off the Formula and merge again at Union data; the targets enter through Manual input and meet the invoices at Join. The join keeps all 1,000 invoices, and every one has a target.

Complete script

Show the complete runnable script
tutorial_03.py
"""Combine the customer segments and attach a revenue target to every invoice."""

import flowfile as ff

# Chapters 1-2: read and clean 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"])

# Chapter 3: union the two segments, then join the city targets.
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")

enriched.flow_graph.print_tree()
result = enriched.select("invoice_id", "city", "customer_type", "revenue", "target").sort("invoice_id").collect()
print(result)
print(f"Invoices without a target: {result['target'].null_count()}")

Download tutorial_03.py

Recap

The pipeline now combines invoice feeds and enriches every invoice with its city target. Next, reduce those rows to one report row per city without accidentally summing the target once per invoice.

Next: Aggregate