Generating Monthly Chargeback Reports with pandas

This page builds the report renderer for a database chargeback pipeline: take one closed month of validated cost records, group them by cost center, tenant, and service in pandas, and emit both a machine-readable per-cost-center CSV and a formatted PDF statement — with money kept in Decimal and row ordering fully deterministic.

Back to: Chargeback Reporting Automation

The inputs here are already trustworthy: they have passed schema validation for billing data, so every cost is a Decimal, every timestamp is UTC, and every row carries a canonical cost_center. What remains is a rendering problem with two hard constraints. First, the arithmetic must be exact — a chargeback statement whose service lines do not sum to its cost-center total is worthless, and pandas’ default numeric path runs through float64, which quietly reintroduces rounding error. Second, the output must be reproducible: the same input must produce byte-identical CSV every run, so two months of statements can be diffed and any figure can be regenerated on demand. Reports that split shared-pool spend depend on the apportionment done in allocating shared database cost with tenant keys; this page assumes that split has already happened and each record names a single tenant.

Prerequisites

  • Python: 3.10 or newer.

  • Libraries: pandas for grouping, and either reportlab or matplotlib for the PDF. This page uses reportlab for tabular statements because it lays out tables without a plotting detour.

    pip install "pandas>=2.1" "reportlab>=4.0"

  • Input: a list of validated records for one closed billing period. No cloud permissions are needed at this stage — the renderer reads from your already-validated store, not from a provider API. Keep the read credential scoped to the cost store per security and access control for cost data; the renderer never needs write access to anything but its own output directory.

  • Assumption: the period is closed. Rendering an open period produces a statement that changes after it ships.

A representative input row looks like this:

{
  "billing_period": "2026-06",
  "cost_center": "cc-payments",
  "tenant_id": "tenant-acme",
  "service": "aurora-postgres",
  "cost": "184.20",
  "currency": "USD"
}

Step-by-Step Implementation

The renderer is a straight line: load records into a Decimal-safe frame, group deterministically, write the CSV, then draw the PDF from the same grouped frame so the two artifacts can never disagree.

pandas report renderer flow: load records, group by keys, then emit CSV and PDF from one grouped frameValidated period records load into a DataFrame, which is grouped by cost center, tenant, and service; the single grouped frame then produces both a per-cost-center CSV and a formatted PDF statement.ValidatedrecordsGroup by cc /tenant / serviceframeCSVPDF

Step 1 — Load records into a Decimal-safe frame

The single most important line in this whole page is keeping cost out of float64. Constructing the frame normally and then mapping the column to Decimal leaves it as object dtype, which pandas will sum with Python’s Decimal.__add__ rather than IEEE-754 addition.

from decimal import Decimal
import pandas as pd

def load_period(records: list[dict], period: str) -> pd.DataFrame:
    """Load one closed period into a frame whose cost column holds Decimal."""
    df = pd.DataFrame.from_records(records)
    df = df[df["billing_period"] == period].copy()
    if df.empty:
        raise ValueError(f"no records for closed period {period!r}")
    # Decimal-from-str: never Decimal(float), which would inherit float error.
    df["cost"] = df["cost"].map(lambda v: Decimal(str(v)))
    return df

# Expected:
#   >>> df["cost"].dtype
#   dtype('O')                      # object, holding Decimal
#   >>> type(df["cost"].iloc[0])
#   <class 'decimal.Decimal'>

Step 2 — Group deterministically by cost center, tenant, and service

groupby with sort=True orders groups by key, and because cost is object/Decimal, .sum() stays exact. The result is a tidy long frame — one row per (cost_center, tenant, service) — which is the canonical shape for the CSV.

def summarize(df: pd.DataFrame) -> pd.DataFrame:
    """Exact per-(cost_center, tenant, service) totals, deterministically ordered."""
    grouped = (
        df.groupby(["cost_center", "tenant_id", "service"], sort=True, dropna=False)["cost"]
        .sum()                       # Decimal sum: exact, no float drift
        .reset_index()
    )
    # Belt-and-braces determinism: an explicit stable sort on the key columns.
    return grouped.sort_values(
        ["cost_center", "tenant_id", "service"], kind="stable"
    ).reset_index(drop=True)

# Expected (three services under one cost center):
#     cost_center     tenant_id           service      cost
#   0  cc-payments  tenant-acme     aurora-postgres    184.20
#   1  cc-payments  tenant-acme          rds-mysql      92.05
#   2  cc-payments  tenant-beta     aurora-postgres    143.77

For a wide, human-scannable layout — tenants down the side, services across the top — pivot_table is the right tool. Use aggfunc="sum" and fill missing cells with Decimal("0.00") so the grid has no NaN holes that would break later arithmetic.

def pivot_by_service(df: pd.DataFrame, cost_center: str) -> pd.DataFrame:
    """Tenant-by-service matrix for one cost center, Decimal-safe."""
    sub = df[df["cost_center"] == cost_center]
    grid = pd.pivot_table(
        sub, index="tenant_id", columns="service", values="cost",
        aggfunc="sum", fill_value=Decimal("0.00"), sort=True,
    )
    return grid

# Expected for cc-payments:
#   service       aurora-postgres  rds-mysql
#   tenant_id
#   tenant-acme            184.20      92.05
#   tenant-beta            143.77       0.00

Step 3 — Write the per-cost-center CSV

Each cost center gets its own CSV so a statement can be handed to exactly one owner. Writing the Decimal values through a quantizing formatter guarantees two decimal places in the file and a stable column order; lineterminator="\n" keeps the bytes identical across platforms.

from pathlib import Path
from decimal import ROUND_HALF_UP

CENT = Decimal("0.01")
COLUMNS = ["cost_center", "tenant_id", "service", "cost"]  # fixed projection

def write_cost_center_csvs(summary: pd.DataFrame, out_dir: Path, period: str) -> list[Path]:
    """One CSV per cost center; deterministic rows, quantized money."""
    out_dir.mkdir(parents=True, exist_ok=True)
    written: list[Path] = []
    for cc, block in summary.groupby("cost_center", sort=True):
        out = block[COLUMNS].copy()
        out["cost"] = out["cost"].map(lambda v: str(v.quantize(CENT, ROUND_HALF_UP)))
        path = out_dir / f"chargeback_{period}_{cc}.csv"
        out.to_csv(path, index=False, lineterminator="\n")
        written.append(path)
    return written

# Expected file contents of chargeback_2026-06_cc-payments.csv:
#   cost_center,tenant_id,service,cost
#   cc-payments,tenant-acme,aurora-postgres,184.20
#   cc-payments,tenant-acme,rds-mysql,92.05
#   cc-payments,tenant-beta,aurora-postgres,143.77

Step 4 — Render a formatted PDF statement

The PDF is drawn from the same summary frame, so it cannot diverge from the CSV. reportlab’s Table takes rows of strings; the total row is computed with a Decimal sum and quantized once at the end.

from reportlab.lib import colors
from reportlab.lib.pagesizes import LETTER
from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer
from reportlab.lib.styles import getSampleStyleSheet

def render_pdf(summary: pd.DataFrame, cost_center: str, period: str, out: Path) -> Path:
    """Formatted per-cost-center chargeback statement."""
    block = summary[summary["cost_center"] == cost_center]
    styles = getSampleStyleSheet()
    header = ["Tenant", "Service", "Cost (USD)"]
    rows = [
        [r.tenant_id, r.service, str(r.cost.quantize(CENT, ROUND_HALF_UP))]
        for r in block.itertuples()
    ]
    total = sum((r.cost for r in block.itertuples()), Decimal("0.00"))
    rows.append(["", "TOTAL", str(total.quantize(CENT, ROUND_HALF_UP))])

    doc = SimpleDocTemplate(str(out), pagesize=LETTER)
    table = Table([header] + rows, hAlign="LEFT")
    table.setStyle(TableStyle([
        ("BACKGROUND", (0, 0), (-1, 0), colors.HexColor("#1f2933")),
        ("TEXTCOLOR", (0, 0), (-1, 0), colors.white),
        ("ALIGN", (2, 0), (2, -1), "RIGHT"),
        ("FONTNAME", (0, -1), (-1, -1), "Helvetica-Bold"),
        ("LINEABOVE", (0, -1), (-1, -1), 1, colors.black),
        ("GRID", (0, 0), (-1, -2), 0.4, colors.HexColor("#cbd2d9")),
    ]))
    doc.build([
        Paragraph(f"Chargeback Statement — {cost_center} — {period}", styles["Title"]),
        Spacer(1, 12),
        table,
    ])
    return out

# Expected: a LETTER-size PDF whose TOTAL row equals the sum of the CSV cost
# column for the same cost center, e.g. 276.25 for cc-payments/tenant-acme.

Verification

Before any statement leaves the renderer, assert the two artifacts reconcile and that the report total matches the input total to the cent.

def verify(df: pd.DataFrame, summary: pd.DataFrame) -> None:
    """Report must reconcile exactly to the source records."""
    source_total = sum(df["cost"], Decimal("0.00"))
    report_total = sum(summary["cost"], Decimal("0.00"))
    assert source_total == report_total, (
        f"reconciliation failed: input {source_total} != report {report_total}"
    )
    # No cost may vanish into an unnamed group.
    assert summary["cost_center"].notna().all(), "record with null cost_center"

# Expected on a clean period:
#   (no output; both assertions pass)

A quick CLI reconciliation against the written CSV closes the loop — the summed cost column must equal the known period total:

python - <<'PY'
import csv, decimal, glob
total = sum(
    (decimal.Decimal(r["cost"]) for f in glob.glob("out/chargeback_2026-06_*.csv")
     for r in csv.DictReader(open(f))),
    decimal.Decimal("0.00"),
)
print("period total:", total)
PY

period total: 419.02

Gotchas & Edge Cases

  • float64 sneaks back in on read. pd.read_csv infers float64 for the cost column unless you pass dtype={"cost": str} and re-map to Decimal. Any round-trip through CSV without that guard silently reintroduces the error you worked to avoid.
  • groupby(...).sum() on an empty frame returns an empty result, not zero. A cost center with no records for the period produces no row, not a 0.00 row. If a stakeholder expects to see their zero, left-join the group result onto a full roster of expected cost centers.
  • NaN in a Decimal column poisons the sum. A single missing cost turns a Decimal column sum into NaN (float). Assert df["cost"].map(lambda v: isinstance(v, Decimal)).all() right after load, or a bad record silently voids the whole statement.
  • Locale-dependent formatting breaks reproducibility. Never format money through locale.currency or f-string thousands separators that read the host locale; quantize a Decimal and str() it, so a report generated in two regions is byte-identical.
  • Currency mixing. groupby will happily sum USD and EUR rows into one figure. Guard by asserting a single currency per cost center, or pivot with currency in the index — the canonical-currency conversion belongs upstream in multi-cloud cost normalization, not in the renderer.
  • Shared-pool rows arrive pre-split. This renderer assumes each row names one tenant. If a raw pool line reaches it, the pool total is charged to whatever placeholder tenant it carries — run the tenant-key allocation step first.

Frequently Asked Questions

Why keep cost as Decimal instead of just rounding floats at the end?

Because the error accumulates before the end. Summing thousands of sub-cent float line items drifts by cents well before you format, and a chargeback statement whose service lines do not add up to its total is rejected on sight. Decimal from string keeps every intermediate sum exact, so the final quantize is the only rounding that ever happens.

Should I use groupby or pivot_table?

Use groupby for the canonical long-format CSV — one row per key combination — because it is the shape downstream systems and diffs expect. Use pivot_table only for the human-facing wide grid inside a PDF or dashboard. They compute the same numbers; the difference is layout, and deriving both from one loaded frame keeps them consistent.

How do I make the CSV byte-identical between runs?

Sort explicitly with kind="stable" on all key columns, project a fixed column list, format money by quantizing Decimal to two places, and write with index=False, lineterminator="\n". With those four controls the output is a pure function of the input, which is what lets you diff June against July or regenerate a lost statement exactly.

matplotlib or reportlab for the PDF?

reportlab when the statement is fundamentally a table — it lays out rows, headers, and a total line directly. Reach for matplotlib only when the statement carries charts (a spend-trend line, a per-service bar). For a plain tabular chargeback statement, reportlab is less code and produces smaller files.

How does this connect to delivering the report?

The renderer’s only job is to produce reconciled files and stop. Shipping them to a stakeholder — with retry, backoff, and a guard against sending twice — is a separate step covered in delivering cost reports to Slack and email. Keeping generation and delivery apart means a delivery failure never forces a regeneration.

Back to: Chargeback Reporting Automation