Đại học Kinh tế Quốc dân – National Economics UniversityMICRO4ASIA
🏆 Arena
Sign inSign up
MICRO4ASIAThe six micro-credentials

Courses are delivered on the Limio learning platform. Limio

← Data Cleaning and Preprocessing with Python

You are viewing a public preview

Sign in to save your progress, do assignments, join discussions and get feedback.

Sign in
Module 3 — Prepare, Report and Reproduce

Period 12 · Creating Reporting Variables

Print / Save as PDF
Lecture video — Period 12: Creating Reporting Variables

Revenue after discount, month and quarter labels, value bands, a regional summary and validating its totals.

Lesson overview

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.

Study notes

By the end of this lesson you can:

  • Calculate revenue after discount and state exactly what it includes.
  • Validate inputs before calculating (present, positive quantity, non-negative price, discount 0-1).
  • Create month and quarter labels that keep the year.
  • Assign value bands with pd.cut and explain which boundary each band includes.
  • Build a regional summary with named aggregation and reconcile it with the detailed total.

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.

What one row means

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 after discount

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.

Month and quarter

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.

Value bands

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:

BandRange (VND)
Low0 up to, but not including, 180,000
Medium180,000 up to, but not including, 220,000
High220,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.)

Regional summary and reconciliation

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.

Worked example

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.

Common mistakes

  • Calculating revenue from scaled columns. Only the original quantity and price carry dong values.
  • Grouping by month number. Use to_period("M") so the year stays in the label.
  • Misreading right=False. Boundaries belong to the higher band: 180,000 is Medium, 220,000 is High.
  • Averaging group means. Divide total revenue by total count unless groups are equal in size.

Summary

  • Revenue = quantity x unit price x (1 - discount), in VND, after discount only; state what is excluded.
  • Validate inputs with assertions before calculating; never assume a missing discount is 0.
  • 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.
  • Reconcile counts and totals between the summary and the detailed table.

Flashcards · Creating Reporting Variables

9 words
WordMeaningExample
  • Revenue after discount

    Quantity times unit price times (1 - discount rate), excluding tax, shipping and returns unless stated.

    2 x 100,000 x 0.9 = 180,000 dong

  • Input validation

    Checking that required values are present and inside their allowed ranges before calculating.

    `assert sales["discount_rate"].between(0, 1).all()`

  • dt.to_period()

    Converts dates to period labels such as month or quarter, keeping the year.

    `sales["date"].dt.to_period("Q")` gives 2025Q1.

  • pd.cut()

    Assigns numeric values to labelled intervals defined by bin edges.

    `pd.cut(revenue, bins=[0, 180_000, 220_000, np.inf], labels=[...])`

  • right=False

    Makes each interval include its left boundary and exclude its right boundary.

    Exactly 180,000 falls in Medium, not Low.

  • Value band

    A category grouping transactions by amount, based on agreed thresholds.

    Low, Medium, High revenue bands

  • reset_index()

    Turns the group labels in the index back into an ordinary column.

    `summary = sales.groupby("region").agg(...).reset_index()`

  • np.isclose()

    Compares numbers within a tolerance, useful for floating-point totals.

    `np.isclose(a, b, rtol=0, atol=0.5)`

  • Overall mean

    Total revenue divided by total transactions, not the average of group means.

    770,000 / 4 = 192,500 dong