You are viewing a public preview
Sign in to save your progress, do assignments, join discussions and get feedback.
Now we will turn cleaned sales data into useful reporting variables. We will calculate revenue after discounts, create month and quarter labels, and divide transactions into value bands. Then we will build a regional summary and check that its totals match the detailed data. Our example uses four small transactions, so we can check every result by hand. The same steps also work with a larger table. By the end, you should be able to explain both the Python code and the business meaning of each new column.
By the end of this lesson you can:
pd.cut and explain which boundary each band includes.Key idea: Every new reporting variable needs a clear meaning, a unit and a check. Calculate from validated original columns, and make sure summary totals match the detailed data.
In the lecture example, one row is one transaction and each transaction ID appears once. Unit price is in Vietnamese dong; discount is a fraction, so 0.1 means 10%. T1 contains two units at 100,000 dong each. Earlier steps have already fixed dates, numeric types, duplicate IDs and region labels. If a real table had several product lines per transaction, the key and counting rule would need to change first.
revenue = quantity x unit_price x (1 - discount_rate)
For T1: 2 x 100,000 = 200,000 dong before discount; with a 10% discount, 180,000 dong. Use the original numeric columns; scaled values from Period 11 no longer have business units. Here revenue means sales after discount only. A real report must say whether tax, shipping and returns are included, and the formula must follow that definition.
The formula cannot protect you from bad inputs. A discount of 10 would mean 1,000%. Before calculating, check that required values are present, quantity is positive, unit price is non-negative and discount is between 0 and 1 inclusive. An assert stops execution if any row breaks these rules; in a larger workflow, put invalid rows in a review table. Do not guess that a missing discount is 0; that needs evidence from the business process.
Make sure the date column is a real datetime. The lecture's input uses year-month-day, and invalid dates raise an error. Then:
df["date"].dt.to_period("M") gives labels such as 2025-01;df["date"].dt.to_period("Q") gives calendar quarters such as 2025Q1.January and February fall in Q1; April falls in Q2. Both labels keep the year, which matters when the table covers several years: grouping by month number alone would merge January 2025 with January 2026. A company with a different financial year needs its own quarter definition.
pd.cut turns a numeric amount into categories. The lecture defines three bands with bins=[0, 180_000, 220_000, np.inf] and right=False:
| Band | Range (VND) |
|---|---|
| Low | 0 up to, but not including, 180,000 |
| Medium | 180,000 up to, but not including, 220,000 |
| High | 220,000 and above |
right=False includes the left boundary and excludes the right one, so exactly 180,000 is Medium and exactly 220,000 is High. These thresholds are teaching choices: in practice agree on them with report users and keep them fixed when comparing periods. (The course notebook uses different thresholds for the full dataset; the method is the same.)
groupby("region", dropna=False).agg(...) with named aggregation creates transactions (size), total revenue (sum) and avg_revenue (mean); reset_index() makes region an ordinary column. Keeping missing-region groups visible stops rows from silently disappearing. For monthly regional results, group by month and region together.
Validation links the report to the detail: every revenue value and band is present (a missing band can reveal a negative value or a gap in the bins), transaction counts add up to the input rows, and regional revenue reconciles with the detailed total. Use a small absolute tolerance for floating-point sums, with relative tolerance 0. The overall average is total revenue / total count; a simple average of group means equals it here only because both groups have two transactions.
T1 follows the lecture; the other inputs are illustrative values that give the lecture's totals (North 330,000, South 440,000).
import numpy as np
import pandas as pd
sales = pd.DataFrame({
"transaction_id": ["T1", "T2", "T3", "T4"],
"region": ["North", "North", "South", "South"],
"date": ["2025-01-15", "2025-02-03", "2025-04-10", "2025-01-28"],
"quantity": [2, 3, 4, 2],
"unit_price": [100_000, 50_000, 50_000, 150_000],
"discount_rate": [0.1, 0.0, 0.0, 0.2],
})
assert sales[["quantity", "unit_price", "discount_rate"]].notna().all().all()
assert (sales["quantity"] > 0).all() and (sales["unit_price"] >= 0).all()
assert sales["discount_rate"].between(0, 1).all()
sales["revenue"] = sales["quantity"] * sales["unit_price"] * (1 - sales["discount_rate"])
sales["date"] = pd.to_datetime(sales["date"], format="%Y-%m-%d")
sales["month"] = sales["date"].dt.to_period("M")
sales["quarter"] = sales["date"].dt.to_period("Q")
sales["value_band"] = pd.cut(sales["revenue"], bins=[0, 180_000, 220_000, np.inf],
right=False, labels=["Low", "Medium", "High"])
summary = sales.groupby("region", dropna=False).agg(
transactions=("transaction_id", "size"),
revenue=("revenue", "sum"),
avg_revenue=("revenue", "mean"),
).reset_index()
assert sales["value_band"].notna().all()
assert summary["transactions"].sum() == len(sales)
assert np.isclose(summary["revenue"].sum(), sales["revenue"].sum(), rtol=0, atol=0.5)
print(sales[["transaction_id", "revenue", "month", "quarter", "value_band"]])
print(summary)
Expected output: T1 has revenue 180,000 and band Medium; North has 2 transactions, revenue 330,000, average 165,000; South has 2 transactions, revenue 440,000, average 220,000; total 770,000 and overall average 192,500. T3 falls in Q2.
to_period("M") so the year stays in the label.right=False. Boundaries belong to the higher band: 180,000 is Medium, 220,000 is High.dt.to_period("M") and ("Q") create time labels that keep the year.pd.cut(..., right=False) includes left boundaries; agree thresholds with report users.Flashcards · Creating Reporting Variables
9 wordsQuantity times unit price times (1 - discount rate), excluding tax, shipping and returns unless stated.
Checking that required values are present and inside their allowed ranges before calculating.
Converts dates to period labels such as month or quarter, keeping the year.
Assigns numeric values to labelled intervals defined by bin edges.
Makes each interval include its left boundary and exclude its right boundary.
A category grouping transactions by amount, based on agreed thresholds.
Turns the group labels in the index back into an ordinary column.
Compares numbers within a tolerance, useful for floating-point totals.
Total revenue divided by total transactions, not the average of group means.