Reporting Cicd Delivery Pipelines

Generating Weekly Margin Reports with pandas

This page walks a food-tech developer through the exact code to produce a weekly per-location margin report that an operator receives every Monday and can reconcile against the ledger to the cent. It is the concrete implementation companion to batch cost reporting automation; read that first for the frozen-snapshot contract and the pure-function design principle, then follow the numbered steps here to stand up a runnable weekly job.

The task is narrow but easy to get subtly wrong: aggregate a pinned variance snapshot to the location grain, add a trend column comparing this week to last, and render it — all while keeping money in Decimal so the printed food-cost percentage matches finance. Every step below assumes the report never recomputes cost; it only reshapes and formats numbers the variance mapping methodologies layer already produced.

Prerequisites and Data Contract

  • Python 3.11+, pandas 2.x, jinja2 3.x.
  • Read access to a variance_snapshot table with columns run_version, location_id, menu_item_id, units_sold, actual_cost, net_revenue, where cost columns are NUMERIC.
  • A registry mapping each fiscal week to the run_version that closed it — so “week 28” always resolves to one immutable snapshot. Producing and refreshing that snapshot is the job of refreshing materialized cost views on a schedule.

Money is carried as Decimal end to end; percentages are quantized once. Weeks are identified by an ISO year-week string (2026-W28) so sorting and joining are lexicographic and unambiguous.

Weekly margin report data flow Two pinned snapshots — this week and last week — each aggregate to per-location totals. The two aggregates join on location_id to produce a week-over-week delta column. The joined frame renders into a per-location HTML margin report. This week snapshot run_version W28 Last week snapshot run_version W27 Aggregate Aggregate Join + delta on location_id HTML report per location

Step-by-Step Implementation

Step 1 — Resolve the week to a pinned snapshot

Never pass a date range to the report. Resolve the ISO week to the single run_version that closed it, so the report is reproducible.

from __future__ import annotations

import pandas as pd


def resolve_run_version(conn, iso_week: str) -> str:
    row = pd.read_sql(
        "SELECT run_version FROM weekly_close WHERE iso_week = %(w)s",
        conn, params={"w": iso_week},
    )
    if row.empty:
        raise LookupError(f"no closed snapshot for {iso_week}")
    return row["run_version"].iloc[0]

Step 2 — Aggregate one snapshot to the location grain

from decimal import Decimal

import pandas as pd


def weekly_location_totals(conn, run_version: str) -> pd.DataFrame:
    df = pd.read_sql(
        "SELECT location_id, actual_cost, net_revenue"
        "  FROM variance_snapshot WHERE run_version = %(rv)s",
        conn, params={"rv": run_version},
    )
    for col in ("actual_cost", "net_revenue"):
        df[col] = df[col].map(lambda v: Decimal(str(v)))
    return df.groupby("location_id", as_index=False).agg(
        actual_cost=("actual_cost", "sum"),
        net_revenue=("net_revenue", "sum"),
    )

Step 3 — Join two weeks and compute a Decimal delta

Compute the food-cost percentage for each week, then the week-over-week change in percentage points. A left join from this week keeps a newly-opened location even if it has no prior week.

from decimal import Decimal

import pandas as pd

Q = Decimal("0.0001")


def add_trend(this_week: pd.DataFrame, last_week: pd.DataFrame) -> pd.DataFrame:
    def fcp(df: pd.DataFrame) -> pd.Series:
        return df.apply(
            lambda r: (r["actual_cost"] / r["net_revenue"]).quantize(Q)
            if r["net_revenue"] > 0 else Decimal("0"),
            axis=1,
        )

    this_week = this_week.assign(food_cost_pct=fcp(this_week))
    last_week = last_week.assign(prior_food_cost_pct=fcp(last_week))

    merged = this_week.merge(
        last_week[["location_id", "prior_food_cost_pct"]],
        on="location_id", how="left",
    )
    merged["wow_delta_pts"] = merged.apply(
        lambda r: (r["food_cost_pct"] - r["prior_food_cost_pct"]) * Decimal("100")
        if pd.notna(r["prior_food_cost_pct"]) else None,
        axis=1,
    )
    return merged.sort_values("food_cost_pct", ascending=False)

Step 4 — Render to HTML

Formatting is the only place presentation rounding happens.

import pandas as pd
from jinja2 import Environment, PackageLoader, select_autoescape


def render_weekly(merged: pd.DataFrame, iso_week: str) -> str:
    env = Environment(loader=PackageLoader("reports"), autoescape=select_autoescape())
    view = merged.copy()
    view["food_cost_pct"] = (view["food_cost_pct"] * 100).map(lambda d: f"{d:.2f}%")
    view["wow_delta_pts"] = view["wow_delta_pts"].map(
        lambda d: "—" if d is None else f"{d:+.2f} pts"
    )
    return env.get_template("weekly_margin.html").render(
        iso_week=iso_week, rows=view.to_dict("records")
    )

Verification and Validation

  • Reconcile to the ledger. Sum actual_cost across the rendered rows and compare to SELECT SUM(actual_cost) FROM variance_snapshot WHERE run_version = :rv. They must be exactly equal — a mismatch means float coercion crept in somewhere; find where Decimal(str(...)) was skipped.
  • Reproducibility. Generate the same week twice and diff the HTML. Identical bytes confirm the report is a pure function of the pinned snapshot.
  • Trend sanity. For a location present both weeks, hand-check that wow_delta_pts equals (this_fcp - last_fcp) * 100. A new location should show —, not 0.00 pts.
  • No silent zero. Confirm any zero-revenue location renders a held footnote, not a 0.00% row.

Gotchas and Edge Cases

  • Float sneaking in via read_sql. Database drivers often return NUMERIC as float. Always remap cost columns with Decimal(str(v)) immediately after load, before any arithmetic, or the reconciliation in verification will drift by fractions of a cent.
  • New or closed locations across weeks. An inner join drops a location that opened this week or closed last week. Use a left join from the current week and render — for a missing prior value; never impute a zero, which would show a spurious full-percentage improvement.
  • ISO week boundaries. A “week” that spans a year boundary (2026-W01) must use ISO year-week, not calendar month logic, or December sales leak into January’s report. Resolve the week to a run_version and let the snapshot define membership.
  • Divide-by-zero on revenue. A location with zero net revenue divides into an undefined food-cost percentage. Guard the division and route the row to the held set, matching the parent guide’s completeness contract.

For library specifics, see the official pandas documentation on merging and group-by.