bhylu.com

Advertisements

Pandas Reporting Project

Combine drop-offs with collection-point labels, then check that the report preserves the original mass. A readable table is useful only when its joins and totals have a clear contract.

Your brief

Create a unique lookup table and declare a many-to-one merge. Require every drop-off to find a label before grouping. Keep a source total and compare it with the grouped result. These checks detect different failures: cardinality catches duplicated lookup keys, coverage catches unknown keys, and reconciliation catches lost or multiplied measured mass. None of them proves that a real weighing device was accurate.

Run the code

Select Try in playground to load the complete editable source. Nothing executes until you choose Run. The browser uses Pyodide 0.27.7, Pandas 2.2.3 and NumPy 2.0.2. Each ordinary execution starts a fresh isolated runtime. The tables are fictional and do not use personal files.

For local work, use a separate virtual environment and run python report-project.py. Download the complete source. The reviewed local environment uses Python 3.12.14, Pandas 3.0.6 and NumPy 2.5.4; the browser uses Pyodide 0.27.7 with Pandas 2.2.3 and NumPy 2.0.2. Compatible examples are checked in both. See the environment setup. No Apache or database server is required.

Worked solution

Show the worked solution
report-project.py
Try in playground

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "drop_id": [101, 102, 103, 104],
    "point": ["north", "south", "north", "south"],
    "material": ["leaves", "food", "food", "leaves"],
    "kg": [12., 8., 6., 10.],
    "bags": pd.array([3, 2, 1, 2], dtype="Int64"),
    "day": ["2026-10-01", "2026-10-01", "2026-10-02", "2026-10-02"],
})

lookup = pd.DataFrame({"point":["north","south"], "label":["North gate","South gate"]})
joined = df.merge(lookup, on="point", how="left", validate="many_to_one", indicator=True)
assert joined["_merge"].eq("both").all()
report = joined.groupby("label", sort=True)["kg"].sum(min_count=1)
assert np.isclose(report.sum(), df["kg"].sum())
print(report.index.tolist())
print(report.tolist())
print(float(report.sum()))

Expected result

['North gate', 'South gate']
[18.0, 18.0]
36.0

The two points each contribute eighteen kilograms. The final thirty-six-kilogram total agrees with the source. Keeping validation next to the merge makes the assumption reviewable when somebody replaces the lookup later.

Extend and test

Introduce an unknown point and a duplicated lookup key separately. Confirm that each produces a useful failure before any report is delivered.

Use Pandas’s official reference for precise contracts, supported versions and numerical limitations.

Advertisements